Automating MySQL replication using snapshots on AWS RDS (or Linux)

Question

I'm currently working on a BASH script to build a new Aurora cluster based on the latest snapshot of an existing cluster, then set up MySQL replication from the old one to the new. In the past I've automated replication using mysqldump and mydumper, both of which give you the most recent binlog and position. However, it's not as straightforward with snapshots (the question would presumably be the same using standard Linux snapshots rather than Aurora, although with Linux, you can get to the binary log index files before the instance is started. You can't read those with RDS).

Since a snapshot is an exact copy of an existing instance, you would think you could just take the current binlog and position from the new instance and use those for the starting position in the change master (or its RDS equivalent) statement. However, that's not the case, as you'll see (this example is from a dev instance so doesn't have much traffic, but a prod instance could easily roll over several binary logs between the time of the snapshot creation and restore):

Old instance:

MySQL [(none)]> show master status;
+----------------------------+----------+--------------+------------------+-------------------------------------------------+
| File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
+----------------------------+----------+--------------+------------------+-------------------------------------------------+
| mysql-bin-changelog.000510 | 98957391 |              |                  | 0e332788-51c9-3440-b8e5-a6edc29aba7e:1-10746050 |
+----------------------------+----------+--------------+------------------+-------------------------------------------------+
MySQL [(none)]> show binary logs;
+----------------------------+-----------+
| Log_name                   | File_size |
+----------------------------+-----------+
| mysql-bin-changelog.000498 | 134301760 |
| mysql-bin-changelog.000499 | 134284404 |
| mysql-bin-changelog.000500 | 134647151 |
| mysql-bin-changelog.000501 | 134236349 |
| mysql-bin-changelog.000502 | 134236200 |
| mysql-bin-changelog.000503 | 134238768 |
| mysql-bin-changelog.000504 | 134321441 |
| mysql-bin-changelog.000505 | 134223686 |
| mysql-bin-changelog.000506 | 134275221 |
| mysql-bin-changelog.000507 | 134221341 |
| mysql-bin-changelog.000508 | 134219161 |
| mysql-bin-changelog.000509 | 134222780 |
| mysql-bin-changelog.000510 |  98926253 |
+----------------------------+-----------+
13 rows in set (0.00 sec)

New instance:

MySQL [(none)]> show master status;
+----------------------------+----------+--------------+------------------+-------------------------------------------------+
| File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
+----------------------------+----------+--------------+------------------+-------------------------------------------------+
| mysql-bin-changelog.000513 |      194 |              |                  | 0e332788-51c9-3440-b8e5-a6edc29aba7e:1-10742576 |
+----------------------------+----------+--------------+------------------+-------------------------------------------------+
MySQL [(none)]> show binary logs;
+----------------------------+-----------+
| Log_name                   | File_size |
+----------------------------+-----------+
| mysql-bin-changelog.000498 | 134301760 |
| mysql-bin-changelog.000499 | 134284404 |
| mysql-bin-changelog.000500 | 134647151 |
| mysql-bin-changelog.000501 | 134236349 |
| mysql-bin-changelog.000502 | 134236200 |
| mysql-bin-changelog.000503 | 134238768 |
| mysql-bin-changelog.000504 | 134321441 |
| mysql-bin-changelog.000505 | 134223686 |
| mysql-bin-changelog.000506 | 134275221 |
| mysql-bin-changelog.000507 | 134221341 |
| mysql-bin-changelog.000508 | 134219161 |
| mysql-bin-changelog.000509 | 134222780 |
| mysql-bin-changelog.000510 |  88104589 |
| mysql-bin-changelog.000511 |       194 |
| mysql-bin-changelog.000512 |       194 |
| mysql-bin-changelog.000513 |       194 |
+----------------------------+-----------+
16 rows in set (0.01 sec)

As you can see, there are three additional binary logs that don't exist on the old one, and there have been additional transactions on the old instance that we don't want to lose. So my next thought was to compare the binary logs of the two instances and take the first one where the size on the master instance was larger than that of the replica; or, if they're all the same, then take the last one that exists on both; this will handle the case where no new data has been written to the master. However, as you can see, the new instance has additional binary logs (presumably created during the restarts as it was built) that have no real data, but will wreak havoc during comparisons of old and new. Another thought was to work backwards from the last binlog to the first on the replica, but I can't think of a reliable way to determine programmatically that it should be skipped. Skipping files with exactly 194 bytes seems very sketchy.

I'm not sure how to resolve this conflict. Any thoughts?

Answer 1

The binlogs files are irrelevant.

If you snapshot a Replica to make a new Replica, then the only thing to change is the server_id in my.cnf.

If you snapshot the Primary, you don't need the binlogs, but you will need the position in the binlog to do the CHANGE MASTER on the new Replica. That position must be taken while the Primary is paused to take the snapshot. (I may be missing some subtleties.)

Answer 2

OK, I think I've figured out how to do this:

1. Reverse loop through all binary logs (show binary logs) on the replica (i.e. starting with the newest)
2. For each log, run show binlog events ... limit 10 and pipe through egrep -v 'Format_desc|Previous_gtids' (this may vary based on the version of MySQL)
3. The first log that gives any output from that statement is the last log written by the master before the snapshot was taken.
4. Pass that log's name and the size to change master as log file and position.