はじめに
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