1

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?

2 Answers 2

0

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

3
  • RDS doesn't give you that option. The snapshots are taken automatically and the binlog position isn't recorded when it's done. It's certainly possible to set up replication manually without having the information, since we can usually infer it. The issue is writing rules to do it for 100% of cases.
    – Swechsler
    Oct 26 at 7:46
  • If RDS cannot provide a usable snapshot, then you should file a serious bug -- or abandon their service.
    – Rick James
    Oct 26 at 17:42
  • As you can see by my answer, I figured out how to do it.
    – Swechsler
    Oct 26 at 23:27
0

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.

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.