はじめに

RDS for MySQL からオンプレミスの仮想マシンに準備した MySQL へレプリケーションを組む手順を備忘録として残しておきます。

前提

レプリケーション元 : RDS 5.7.31
レプリケーション先 : オンプレミス(Oracle Cloudで準備) : MySQL 8.0.23 : CentOS 7
VPC とオンプレミスは、site-to-site VPN で接続済み

Replica : MySQL の構成

次の URL を参考に、適当に MySQL をインストールします。この記事では、8.0.23 を使っています。
https://qiita.com/sugimount/items/bf44904db8201d377536

Replica : GTID Mode を有効

8.0.23 の Default では、gtid_mode が OFF になっている。Replication のために、ON へ変更

mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | OFF       |
| gtid_executed                    |           |
| gtid_executed_compression_period | 0         |
| gtid_mode                        | OFF       |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
9 rows in set (0.01 sec)

mysql>

編集

sudo vim /etc/my.cnf

追記

[mysqld]
...
省略
...
log-bin
server-id=100
log-slave-updates
gtid-mode=ON
enforce-gtid-consistency=true

再起動

sudo systemctl restart mysqld

有効化されている

mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed                    |           |
| gtid_executed_compression_period | 0         |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
9 rows in set (0.00 sec)

mysql>

Source : レプリケーションユーザーの設定

ユーザー作成

CREATE USER rpluser@'%' IDENTIFIED BY 'Rpl001#!';

REPLICATION SLAVE の権限付与

GRANT REPLICATION SLAVE on *.* to rpluser@'%';

Source : Dump ファイルを Export

RDS に MySQL Shell を使って接続

mysqlsh admin@rds-source01.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com

dumpInstance を使って、Oracle Cloud の Object Storage にダンプファイルを出力します。出力先は、ローカルのファイルシステム上にも指定可能です。

util.dumpInstance("RDStoOnpremis4", {osBucketName: "mysqldump", osNamespace: "nrefeaanptgn", threads: 4, ocimds: true, compatibility: ["strip_restricted_grants"]})

実行例

 MySQL  rds-source01.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com:3306 ssl  JS > util.dumpInstance("RDStoMDS1", {osBucketName: "mysqldump", osNamespace: "nrefeaanptgn", threads: 4, ocimds: true, compatibility: ["strip_restricted_grants"]})
Acquiring global read lock
NOTE: Error acquiring global read lock: MySQL Error 1045 (28000): Access denied for user 'admin'@'%' (using password: YES)
WARNING: The current user lacks privileges to acquire a global read lock using 'FLUSH TABLES WITH READ LOCK'. Falling back to LOCK TABLES...
Locking instance for backup
NOTE: Backup lock is not supported in MySQL 5.7 and DDL changes will not be blocked. The dump may fail with an error or not be completely consistent if schema changes are made while dumping.
Table locks acquired
Gathering information - done
All transactions have been started
WARNING: The dumped value of gtid_executed is not guaranteed to be consistent
Global read lock has been released
Checking for compatibility with MySQL Database Service 8.0.23
NOTE: MySQL Server 5.7 detected, please consider upgrading to 8.0 first. You can check for potential upgrade issues using util.checkForServerUpgrade().
NOTE: User 'admin'@'%' had restricted privilege (RELOAD) removed
NOTE: User 'rdsadmin'@'localhost' had restricted privileges (CREATE TABLESPACE, FILE, RELOAD, SHUTDOWN, SUPER) removed
Compatibility issues with MySQL Database Service 8.0.23 were found and repaired. Please review the changes made before loading them.
Writing global DDL files
Writing users DDL
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing DDL for schema `innodb`
Writing DDL for schema `sugitest`
?% (0 rows / ?), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
Duration: 00:00:01s
Schemas dumped: 2
Tables dumped: 0
Uncompressed data size: 0 bytes
Compressed data size: 0 bytes
Compression ratio: 0.0
Rows written: 0
Bytes written: 0 bytes
Average uncompressed throughput: 0.00 B/s
Average compressed throughput: 0.00 B/s
 MySQL  rds-source01.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com:3306 ssl  JS >

Object Storageに出力されている

Replica : Dumpfile を Import

Replica 先で local_infile が ON である必要あり

mysql> show variables like '%LOCAL_INFILE%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

編集

sudo vim /etc/my.cnf

編集

[mysqld]
省略
local_infile=1

再起動

sudo systemctl restart mysqld

確認

mysql> show variables like '%LOCAL_INFILE%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

MySQL Shell で接続

mysqlsh sugi01@10.0.0.5

Dumpfile を Import

util.loadDump("RDStoOnpremis4", {osBucketName: "mysqldump", osNamespace: "nrefeaanptgn", threads: 4, ignoreVersion: true})

実行例

 MySQL  10.0.0.3:33060+ ssl  JS > util.loadDump("RDStoOnpremis1", {osBucketName: "mysqldump", osNamespace: "nrefeaanptgn", threads: 4, ignoreVersion: true})
Loading DDL and Data from OCI ObjectStorage bucket=mysqldump, prefix='RDStoOnpremis1' using 4 threads.
Opening dump...
Target is MySQL 8.0.23. Dump was produced from MySQL 5.7.31-log
WARNING: Destination MySQL version is newer than the one where the dump was created. Loading dumps from different major MySQL versions is not fully supported and may not work. The 'ignoreVersion' option is enabled, so loading anyway.
Fetching dump data from remote location...
Fetching 0 table metadata files for schema `innodb`...
Fetching 0 table metadata files for schema `sugitest`...
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL script for schema `innodb`
Executing DDL script for schema `sugitest`
Executing common postamble SQL

No data loaded.
0 warnings were reported during the load.
 MySQL  10.0.0.3:33060+ ssl  JS >

GTID の状態を確認

mysql> show global variables like 'GTID%';
+----------------------------------+------------------------------------------+
| Variable_name                    | Value                                    |
+----------------------------------+------------------------------------------+
| gtid_executed                    | b6efa169-5f17-11eb-9d5b-02001700c965:1-4 |
| gtid_executed_compression_period | 0                                        |
| gtid_mode                        | ON                                       |
| gtid_owned                       |                                          |
| gtid_purged                      |                                          |
+----------------------------------+------------------------------------------+
5 rows in set (0.01 sec)

mysql>

Source 側で実行済みの GTID を、Replica 側に反映していく。現状は何もなし。

mysql> select @@gtid_purged;
+---------------+
| @@gtid_purged |
+---------------+
|               |
+---------------+
1 row in set (0.00 sec)

mysql>

Object Stoarge に出力された Dumpfile 群に、@.json のメタデータあり

JSON ファイルの中身はこんな感じです。gtidExecuted をメモっておきます。

{
    "dumper": "mysqlsh Ver 8.0.23 for Linux on x86_64 - for MySQL 8.0.23 (MySQL Community Server (GPL))",
    "version": "1.0.2",
    "origin": "dumpInstance",
    "schemas": [
        "sugitest",
        "innodb"
    ],
    "basenames": {
        "sugitest": "sugitest",
        "innodb": "innodb"
    },
    "users": [
        "'admin'@'%'",
        "'rdsadmin'@'localhost'",
        "'rpluser001'@'%'",
        "'rpluser002'@'%'"
    ],
    "defaultCharacterSet": "utf8mb4",
    "tzUtc": true,
    "bytesPerChunk": 64000000,
    "user": "admin",
    "hostname": "onprem-server02",
    "server": "ip-172-23-2-162",
    "serverVersion": "5.7.31-log",
    "gtidExecuted": "90710e1c-f699-11ea-85c0-0ec6a6bed381:1-99",
    "gtidExecutedInconsistent": true,
    "consistent": true,
    "mdsCompatibility": true,
    "begin": "2021-01-25 14:17:39"
}

gtid_purged で、先ほどメモった値を入れます

SET global gtid_purged='90710e1c-f699-11ea-85c0-0ec6a6bed381:1-99';

確認

mysql> select @@gtid_purged;
+-------------------------------------------+
| @@gtid_purged                             |
+-------------------------------------------+
| 90710e1c-f699-11ea-85c0-0ec6a6bed381:1-99 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql>

Replica : レプリケーションの設定

RDS特有テーブルも、オンプレミス側の MySQL へ作成しないとエラーになります

CREATE TABLE mysql.rds_heartbeat2 ( `id` int(11) NOT NULL, `value` bigint(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

RDS を Master として定義します

CHANGE MASTER TO
 MASTER_HOST='rds-source01.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com',
 MASTER_PORT=3306,
 MASTER_USER='rpluser',
 MASTER_PASSWORD='Rpl001#!';

レプリケーション開始

mysql> START SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

memo : 停止

STOP SLAVE;
RESET MASTER;

確認

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: rds-source01.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com
                  Master_User: rpluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-changelog.000090
          Read_Master_Log_Pos: 991
               Relay_Log_File: rds-onpremis-replica01-relay-bin.000006
                Relay_Log_Pos: 1226
        Relay_Master_Log_File: mysql-bin-changelog.000090
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 991
              Relay_Log_Space: 1549
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1329226068
                  Master_UUID: 90710e1c-f699-11ea-85c0-0ec6a6bed381
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 90710e1c-f699-11ea-85c0-0ec6a6bed381:97-102
            Executed_Gtid_Set: 90710e1c-f699-11ea-85c0-0ec6a6bed381:1-102,
b6efa169-5f17-11eb-9d5b-02001700c965:1-5
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set, 1 warning (0.00 sec)

mysql>

レプリケーションの確認

Source で Table を作ります。

use sugitest;

CREATE TABLE sample(
 id INT(11) NOT NULL AUTO_INCREMENT,
 value INT(5) NOT NULL DEFAULT 0,
 PRIMARY KEY (id)
);

INSERT INTO sample(value) VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

Replica 側で確認。無事にレプリケーションされています。

mysql> select * from sugitest.sample;
+----+-------+
| id | value |
+----+-------+
|  1 |     1 |
|  2 |     2 |
|  3 |     3 |
|  4 |     4 |
|  5 |     5 |
|  6 |     6 |
|  7 |     7 |
|  8 |     8 |
|  9 |     9 |
| 10 |    10 |
+----+-------+
10 rows in set (0.00 sec)

mysql>

参考URL

Amazon RDS for MySQL のアクティブ DB インスタンスのバイナリログを使用して、オンプレミスのスタンバイインスタンスにレプリケートするにはどうすればよいですか?
https://aws.amazon.com/jp/premiumsupport/knowledge-center/replicate-amazon-rds-mysql-on-premises/

NAT越しにMySQLでレプリケーションすると接続が切れる
http://sarahetmoi.take-uma.net/mysql/nat%E8%B6%8A%E3%81%97%E3%81%ABmysql%E3%81%A7%E3%83%AC%E3%83%97%E3%83%AA%E3%82%B1%E3%83%BC%E3%82%B7%E3%83%A7%E3%83%B3%E3%81%99%E3%82%8B%E3%81%A8%E6%8E%A5%E7%B6%9A%E3%81%8C%E5%88%87%E3%82%8C%E3%82%8B

MySQL 5.7 GTID レプリケーション設定メモ
https://blog.apar.jp/linux/6725/#toc8