Help us understand the problem. What is going on with this article?

RDS→Auroraのレプリケーションをする

More than 3 years have passed since last update.

RDS→Auroraの移行案件ではなるべくメンテナンス時間を短くしたい。
なので、RDS→Auroraのレプリケーションを設定してみる。

レプリケーションを設定する

まずRDS側でSHOW BINLOG EVENTSを実行してみる。

rds> show binlog events;
+----------------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+
| Log_name                   | Pos | Event_type  | Server_id | End_log_pos | Info                                                        |
+----------------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+
| mysql-bin-changelog.000002 |   4 | Format_desc | 950083943 |         120 | Server ver: 5.6.27-log, Binlog ver: 4                       |
| mysql-bin-changelog.000002 | 120 | Query       | 950083943 |         196 | BEGIN                                                       |
| mysql-bin-changelog.000002 | 196 | Query       | 950083943 |         326 | DELETE FROM mysql.rds_sysinfo where name = 'innodb_txn_key' |
| mysql-bin-changelog.000002 | 326 | Xid         | 950083943 |         357 | COMMIT /* xid=9999 */                                       |
| mysql-bin-changelog.000002 | 357 | Rotate      | 950083943 |         414 | mysql-bin-changelog.000003;pos=4                            |
+----------------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+
5 rows in set (0.01 sec)

これを素直にAuroraに設定してみるとなんでかエラーになる。 (要調査)

▼▼▼追記▼▼▼
直接レプリケーションできるっぽいんですが、なぜか失敗しました
* Nice to meet you Aurora!! // Speaker Deck
* Amazon Auroraへの移行 - inFablic
▲▲▲追記▲▲▲

▼▼▼追記2▼▼▼
リードレプリカのスナップショットからAuroraを作って、RDS→Auroraのレプリケーションを直接したらうまくいきました!
▲▲▲追記2▲▲▲

aurora> call mysql.rds_set_external_master('rds.crjsnk9anf5c.ap-northeast-1.rds.amazonaws.com', 3306, 'root', 'rootroot', 'mysql-bin-changelog.000002', 357, 0);
Query OK, 0 rows affected (0.03 sec)

aurora> call mysql.rds_start_replication;
+-------------------------+
| Message                 |
+-------------------------+
| Slave running normally. |
+-------------------------+
1 row in set (1.05 sec)

Query OK, 0 rows affected (1.05 sec)

aurora> show slave status \G
...
        Seconds_Behind_Master: NULL
                Last_IO_Error: Relay log write failure: could not queue event from master
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

なので、EC2のMySQLを挟む。
my.cnfは以下のような感じ。

server-id=1234567890
log-bin
log-slave-updates
binlog-format=row
replicate-do-db=employees

RDSで再度binlogのポジションを確認してから、EC2のMySQLをRDSのスレーブにする。

ec2> change master to master_host = 'rds.crjsnk9anf5c.ap-northeast-1.rds.amazonaws.com', master_user = 'root', master_password = 'rootroot', master_log_file = 'mysql-bin-changelog.000004', master_log_pos = 422;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

ec2> start slave;
Query OK, 0 rows affected (0.00 sec)

ec2> show slave status \G
...
        Relay_Master_Log_File: mysql-bin-changelog.000006
          Exec_Master_Log_Pos: 120
        Seconds_Behind_Master: 0
...
1 row in set (0.00 sec)

ec2> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysqld-bin.000002 |      120 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

次にAuroraをEC2のMySQLのスレーブにする。

aurora> call mysql.rds_reset_external_master;                                                        +----------------------+
| message              |
+----------------------+
| Slave has been reset |
+----------------------+
1 row in set (3.08 sec)

Query OK, 0 rows affected (3.09 sec)

aurora> call mysql.rds_set_external_master('10.0.227.237', 3306, 'repl', 'repl', 'mysqld-bin.000002', 120, 0);
Query OK, 0 rows affected (0.01 sec)

aurora> call mysql.rds_start_replication;
+-------------------------+
| Message                 |
+-------------------------+
| Slave running normally. |
+-------------------------+
1 row in set (1.02 sec)

Query OK, 0 rows affected (1.02 sec)

aurora> show slave status \G
...
        Relay_Master_Log_File: mysqld-bin.000002
          Exec_Master_Log_Pos: 315
        Seconds_Behind_Master: 0

RDSからデータを流してみる。

$ mysql -uroot -h rds.crjsnk9anf5c.ap-northeast-1.rds.amazonaws.com -t < employees.sql

Auroraにデータが流れていることを確認。

aurora> show slave status \G
...
        Relay_Master_Log_File: mysqld-bin.000002
          Exec_Master_Log_Pos: 66366960
        Seconds_Behind_Master: 0
...
1 row in set (0.01 sec)

aurora> \u employees
Database changed
aurora> show tables;
+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+
6 rows in set (0.06 sec)

aurora> select count(1) from salaries;
+----------+
| count(1) |
+----------+
|  2844047 |
+----------+
1 row in set (0.50 sec)

RDSのbinlogのポジションを固定する

稼働中のRDSから移行する場合、データのスナップショットをAuroraにマイグレーションする際、mysqldumpでポジションを固定しようとするとエラーになってしまう。

$ mysqldump -uroot --master-data=2 --single-transaction -h rds.crjsnk9anf5c.ap-northeast-1.rds.amazonaws.com employees
...
mysqldump: Couldn't execute 'FLUSH TABLES WITH READ LOCK': Access denied for user 'root'@'%' (using password: YES) (1045)

なので、RDSのリードレプリカを作って、そこからAuroraにマイグレーションするようにする。

リードレプリカでmysql.rds_stop_replicationを実行して、ポジションを固定。

mysql> call mysql.rds_stop_replication;
+---------------------------+
| Message                   |
+---------------------------+
| Slave is down or disabled |
+---------------------------+
1 row in set (1.12 sec)

Query OK, 0 rows affected (1.12 sec)

mysql> show slave status \G
...
        Relay_Master_Log_File: mysql-bin-changelog.000013
          Exec_Master_Log_Pos: 422
        Seconds_Behind_Master: NULL

その時点のマスタのポジションを記録。
そしてリードレプリカのスナップショット、またはダンプデータからAuroraを構築するようにする。

その他

call mysql.rds_set_configuration('binlog retention hours', ...)でbinlogの保持期間を長めに設定しておいた方がよさそう

参考資料

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away