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

logsmigrationMySQLreplication

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?

Best Answer

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.

Related Solutions

Postgresql – Best practices for schema changes and data migrations to a live database without downtime

You almost have your answer already:

Create the new structure in parallel
Start writing to both structures
Migrate old data to the new structure
Only write and read new structure
Delete old columns

As for step 3, use something like this (in one transaction):

Insert what is not there yet:

INSERT INTO new_tbl (old_id, data)
SELECT old_id, data
FROM   old_tbl
WHERE  NOT EXISTS (SELECT * FROM new_tbl WHERE new_tbl.old_id = old_tbl.old_id);

Update what has changed in the meantime:

UPDATE new_tbl
SET    data  = old.data
USING  old_tbl
WHERE  new_tbl.old_id = old_tbl.old_id
AND    new_tbl.data IS DISTINCT FROM old_tbl.data;

New data will not be touched, because it is identical in both places.

Mysql – Abnormally high MySQL writes and larger than normal Binary Log files. How to determine what caused this

Don't use --short-form. You are suppressing much of what you want to see.

--short-form

Display only the statements contained in the log, without any extra information or row-based events. This is for testing only, and should not be used in production systems.

^{— http://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_short-form}

That is a largely pointless option, since it returns only the statements contained in the log. If you have a binlog that large, it's unlikely that it's full of statements -- it's full of row events.

--base64-output=decode-rows --verbose are the options you are looking for.

http://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog-row-events.html

You will, at the very least, not be disappointed by the quantity of data returned... it will be much larger than the actual binlogs themselves, which are stored in a relatively tightly-packed format.

Best Answer

Related Solutions

Postgresql – Best practices for schema changes and data migrations to a live database without downtime

Mysql – Abnormally high MySQL writes and larger than normal Binary Log files. How to determine what caused this

Related Question