Is it possible to replicate data changes of a database with triggers using binary logs?

Question

I'm tasked to minimize the downtime of the database update. Unfortunately, I cannot use pt-online-schema-change toolkit because some tables in the current database has MySQL triggers that does further modification of data of other summary tables that may use past data.

I'm trying to research if this methodology is possible:

1.) Create a separate database for containing all new tables.
2.) Turn on binary logs
3.) Copy all data from old tables to new tables.
4.) Stop operation.
5.) Parse the binary logs and replicate the data changes to the new tables.
6.) Replace the old tables with the new tables
7.) Resume operation.

I tried enabling binary logs and set its format to STATEMENT. Unfortunately, it does not log the SQL statements executed by the triggers. Is there a way to log the SQL statements triggered in the binary logs?

• Rick James: A trigger cannot reach into the binlogs.

Answer 1

It possible in theory with ROW binary logs, in this case each changes of tables will be write to the Log.

at this moment several companies declare this solution (plus many on GitHub):

• MariaDB MaxScale CDC (Kafka) - http://126kr.com/article/5btzr54y53h

• StreamSets - https://streamsets.com/products/sdc/ and https://streamsets.com/documentation/datacollector/latest/help/#Origins/MySQLBinaryLog.html#task_qbt_kyh_xx

• Yelp - https://engineeringblog.yelp.com/2016/08/streaming-mysql-tables-in-real-time-to-kafka.html

You can search some other. Unfortunately at least my experience show - this products far from "ready to use" state. But this is possible direction for feature research.