はじめに
前の記事では、Compute Instance と MySQL Database Service 間のレプリケーションを試してみました。今回の記事は、MySQL Database Service 同時のレプリケーションを試してみます。
Document には明記されていない構成ですが、技術的にできるかどうか試してみました。手順を備忘録的に残しておきます。
MDS 2台用意
MySQL Database Service で、MySQL インスタンスを2台用意します
Source : テストデータ作成
作った Source の MySQL にテストデータを作成します
sugitest スキーマ作成
mysql> create database sugitest;
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sugitest |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql>
sample テーブルの作成
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);
Source : レプリケーションユーザーの設定
ユーザー作成
CREATE USER rpluser001@'%' IDENTIFIED BY 'Rpl001#!' REQUIRE SSL;
REPLICATION SLAVE の権限付与
GRANT REPLICATION SLAVE on *.* to rpluser001@'%';
Source : Dump ファイルを Export
MDS を作成した時に指定したユーザーで、MySQL Shell を使って接続
mysqlsh admin@mdssource01.mysqlsubnet01.vcn.oraclevcn.com
何も考えず、MDS インスタンス全体の Dump を取得するとエラーになります
MySQL 10.0.12.41:3306 ssl JS > util.dumpInstance("MDStoMDS1", {osBucketName: "mysqldump", osNamespace: "orasejapan", threads: 4, ocimds: true, compatibility: ["strip_restricted_grants"]})
Acquiring global read lock
NOTE: Error acquiring global read lock: MySQL Error 1227 (42000): Access denied; you need (at least one of) the RELOAD or FLUSH_TABLES privilege(s) for this operation
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
ERROR: Could not acquire backup lock: MySQL Error 1227 (42000): Access denied; you need (at least one of) the BACKUP_ADMIN privilege(s) for this operation
ERROR: Unable to acquire global read lock neither table read locks.
Global read lock has been released
Util.dumpInstance: Unable to lock tables: MySQL Error 1227 (42000): Access denied; you need (at least one of) the BACKUP_ADMIN privilege(s) for this operation (RuntimeError)
MySQL 10.0.12.41:3306 ssl JS >
BACKUP_ADMIN 権限を付与しろ言われますが、MDS では付与出来ません
mysql> grant BACKUP_ADMIN on sugitest.* to sugi01@'%';
ERROR 3619 (HY000): Illegal privilege level specified for BACKUP_ADMIN
mysql>
回避策は、"consistent":"false" を指定して一貫性のないバックアップを取得します。一貫性のないバックアップなので、本番稼働中の MDS から直接指定するのは非推奨です。本番稼働している場合は、Snapshot から新たな MDS を作成して、ワークロードが動いていない状態で一貫性のない dumpInstance をするといいでしょう。
なお、dumpInstance は、MDS 新規作成時に指定したユーザーでないと、エラーになるので注意してください
util.dumpInstance("MDStoMDS1", {osBucketName: "mysqldump", osNamespace: "orasejapan", threads: 4, ocimds: true, compatibility: ["strip_restricted_grants"], "consistent":"false"})
実行例
MySQL 10.0.12.41:3306 ssl JS > util.dumpInstance("MDStoMDS1", {osBucketName: "mysqldump", osNamespace: "orasejapan", threads: 4, ocimds: true, compatibility: ["strip_restricted_grants"], "consistent":"false"})
Gathering information - done
WARNING: The dumped value of gtid_executed is not guaranteed to be consistent
Checking for compatibility with MySQL Database Service 8.0.23
NOTE: User 'admin'@'%' had restricted privilege (PROXY) removed
NOTE: User 'ociadmin'@'127.0.0.1' had restricted privileges (AUDIT_ADMIN, BACKUP_ADMIN, BINLOG_ADMIN, BINLOG_ENCRYPTION_ADMIN, CLONE_ADMIN, CREATE TABLESPACE, ENCRYPTION_KEY_ADMIN, FILE, FLUSH_OPTIMIZER_COSTS, FLUSH_STATUS, FLUSH_TABLES, FLUSH_USER_RESOURCES, GROUP_REPLICATION_ADMIN, INNODB_REDO_LOG_ARCHIVE, INNODB_REDO_LOG_ENABLE, PERSIST_RO_VARIABLES_ADMIN, PROXY, RELOAD, REPLICATION_SLAVE_ADMIN, ROLE_ADMIN, SERVICE_CONNECTION_ADMIN, SESSION_VARIABLES_ADMIN, SET_USER_ID, SHOW_ROUTINE, SHUTDOWN, SUPER, SYSTEM_USER, SYSTEM_VARIABLES_ADMIN, TABLE_ENCRYPTION_ADMIN) removed
NOTE: User 'ocirpl'@'%' had restricted privileges (GROUP_REPLICATION_ADMIN, RELOAD, REPLICATION_SLAVE_ADMIN, SERVICE_CONNECTION_ADMIN, SYSTEM_USER, SYSTEM_VARIABLES_ADMIN) removed
NOTE: User 'sugi01'@'%' had restricted privilege (ALL PRIVILEGES) removed
NOTE: Database sugitest had unsupported ENCRYPTION option commented out
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
Preparing data dump for table `sugitest`.`sample`
Writing DDL for schema `sugitest`
Data dump for table `sugitest`.`sample` will be chunked using column `id`
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing DDL for table `sugitest`.`sample`
Data dump for table `sugitest`.`sample` will be written to 1 file
1 thds dumping - 100% (10 rows / ~10 rows), 5.00 rows/s, 22.00 B/s uncompressed, 0.00 B/s compressed
Duration: 00:00:01s
Schemas dumped: 1
Tables dumped: 1
Uncompressed data size: 42 bytes
Compressed data size: 0 bytes
Compression ratio: 42.0
Rows written: 10
Bytes written: 0 bytes
Average uncompressed throughput: 22.08 B/s
Average compressed throughput: 0.00 B/s
MySQL 10.0.12.41:3306 ssl JS >
Replica : Dumpfile を Import
Replica 用 MDS に Dumpfile を Import します。MySQL Shell で接続
mysqlsh admin@mdsreplica01.mysqlsubnet01.vcn.oraclevcn.com
Dumpfile を Import
util.loadDump("MDStoMDS1", {osBucketName: "mysqldump", osNamespace: "orasejapan", threads: 4})
実行例
MySQL mdsreplica01.mysqlsubnet01.vcn.oraclevcn.com:3306 ssl JS > util.loadDump("MDStoMDS1", {osBucketName: "mysqldump", osNamespace: "orasejapan", threads: 4})
Loading DDL and Data from OCI ObjectStorage bucket=mysqldump, prefix='MDStoMDS1' using 4 threads.
Opening dump...
Target is MySQL 8.0.23-cloud (MySQL Database Service). Dump was produced from MySQL 8.0.23-cloud
Fetching dump data from remote location...
Fetching 1 table metadata files for schema `sugitest`...
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL script for schema `sugitest`
[Worker000] Executing DDL script for `sugitest`.`sample`
[Worker001] sugitest@sample@@0.tsv.zst: Records: 10 Deleted: 0 Skipped: 0 Warnings: 0
Executing common postamble SQL
1 chunks (10 rows, 42 bytes) for 1 tables in 1 schemas were loaded in 1 sec (avg throughput 42.00 B/s)
0 warnings were reported during the load.
MySQL mdsreplica01.mysqlsubnet01.vcn.oraclevcn.com:3306 ssl JS >
GTID ベースのレプリケーションでは、どこまでトランザクションをレプリケーションしたかを、GTID で管理しています。MySQL Shell のユーティリティでは、Replica 側で、GTID 関連の設定が必要です。
Object Storage 内の json メタデータから、gtidExecutedを取得してメモします。
Object Storage の@.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"
],
"basenames": {
"sugitest": "sugitest"
},
"users": [
"'admin'@'%'",
"'administrator'@'%'",
"'ociadmin'@'127.0.0.1'",
"'ocirpl'@'%'",
"'rpluser001'@'%'",
"'sugi01'@'%'"
],
"defaultCharacterSet": "utf8mb4",
"tzUtc": true,
"bytesPerChunk": 64000000,
"user": "admin",
"hostname": "mysql-client01",
"server": "pzrhc2uodjtnf8yi",
"serverVersion": "8.0.23-cloud",
"gtidExecuted": "e37d8e20-5d61-11eb-9d40-0200170045b9:1-10",
"gtidExecutedInconsistent": true,
"consistent": false,
"mdsCompatibility": true,
"begin": "2021-01-23 11:38:01"
}
メモった値を使って、gtid_purged を行います。Replica に MySQL Client (MySQL Shell じゃないよ) を使って接続します。次にメモった値を使ってコマンドを発行します
mysql> call sys.set_gtid_purged("e37d8e20-5d61-11eb-9d40-0200170045b9:1-10");
Query OK, 0 rows affected (0.00 sec)
mysql>
確認
mysql> select @@gtid_purged;
+-------------------------------------------+
| @@gtid_purged |
+-------------------------------------------+
| e37d8e20-5d61-11eb-9d40-0200170045b9:1-10 |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql>
memo : エラーになったとき
mysql> call sys.set_gtid_purged("0e1a6a53-5cac-11eb-8d54-02001701e97e:1-10");
ERROR 3546 (HY000): @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED
memo : 次の gtid_purged が空白じゃないとエラーになる
mysql> show global variables like 'GTID%';
+----------------------------------+------------------------------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------+------------------------------------------------------------------------------------+
| gtid_executed | 0e1a6a53-5cac-11eb-8d54-02001701e97e:1-5,
cbcefcc6-5cb0-11eb-8761-020017004a1e:1-7 |
| gtid_executed_compression_period | 0 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | 0e1a6a53-5cac-11eb-8d54-02001701e97e:1-3 |
+----------------------------------+------------------------------------------------------------------------------------+
5 rows in set (0.01 sec)
mysql>
Replica : Channel の作成
OCI Console 上で、Create Channel を行います
Source で作成したレプリケーション用のユーザーを指定します
Create Channel
Creating になりますが、一定時間後 Active になります
レプリケーション確認
レプリケーションされているか確認するために、Source にテストデータを入れてみます
mysql -h mdssource01.mysqlsubnet01.vcn.oraclevcn.com -u admin -p
現在のデータ
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>
データのインサート
INSERT INTO sugitest.sample(value) VALUES (11);
replica 先
mysql -h mdsreplica01.mysqlsubnet01.vcn.oraclevcn.com -u admin -p
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 |
| 11 | 11 |
+----+-------+
11 rows in set (0.00 sec)
mysql>