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?

  • A trigger cannot reach into the binlogs. – Rick James Jan 11 '17 at 22:22

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):

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.