I want to see if there are any tools that around around of tailing the MySQL binary log to replay the binary log.
The use case i have is migrating a colo MySQL instance into RDS and having the “downtime/cutover” period very small by doing ‘replication’ to RDS.
Its straight forward to dump/restore into the RDS instance though I want to see if there is a tool that does the ‘replication’ in MySQL client user space (by tailing the binary log and then being able to pipe that into a mysql client on the RDS instance).
I want to see if there is a tool out there - I have yet to find one on google…
There is no tool that I know that does that. Since RDS does not allow replication from outside of RDS, so traditional MySQL replication would not work. What you could do though is the following:
Take a dump using mysqldump --master-data=2 > dump.sql. This will record the binlog coordinates in the dump file, you can then pipe the dump file to the RDS instance.
Now the next thing to do would be to use the binlog coordinates stored in the dump.sql file, so that you can pipe binlog events after that to the amazon RDS instance. Suppose the binlog coordinates are from the dump file are stored in variables $start_binlog_file and $start_binlog_pos. You can do that as follows:
a. Check current binlog on master using SHOW MASTER STATUS; look at the value in the column File. Suppose you store this value in $last_binlog_file
b. Execute FLUSH LOGS; so that MySQL opens a new binlog for writing.
c. Now you can using mysqlbinlog to read the events from the binlog coordinates stored in the dump.sql file up to the binlog file $last_binlog_file and pipe them to Amazon RDS:
mysqlbinlog --start-position=$start_binlog_pos $start_binlog_file … $last_binlog_file | mysql -hsome_host -usome_user -psome_pass
Replace … above with the name of all the binlog files that lie btw $start_binlog_file and $last_binlog_file in ascending order.
Step 2 can be repeated multiple times till the date of the failover, with some modifications. Since $last_binlog_file stores the last file you have replayed events from. So the next file to play binlog events would be the one after it. Furthermore, you should execute steps 2a and 2b in the same way.
However, note that there would still be some downtime when doing the cutover. Of course the downtime length would be reduced, but you would still need to stop all writes on current master using FLUSH TABLES WITH READ LOCK, and then replay the remaining binlog events using mysqlbinlog.
I was hoping there was a tool that automated step 2 by watching the binary log file on the filesystem and then ‘semi regularly’ pipe the contents into the RDS host. It should be possible to write something to achieve this task - i was hoping i wouldnt need to :D.