Aurora MySQLクラスター間のレプリケーションにGTIDを利用してみた
中山（順）です
この記事では、タイトルの通りAurora MySQLのクラスター間でレプリケーションを行う手段としてGTIDを利用してみたので、その手順を紹介します。
MySQL 5.7 互換の Amazon Aurora、GTID ベースの複製をサポート
GTID(Global Transaction Identifiers)とは？
GTIDは、トランザクションに付与される一意のIDです。 一意である範囲はそのサーバー内だけでなく、文字通りグローバルに一意です。 MySQLのレプリケーションを開始する際にスレーブがどの位置からレプリケーションを開始するかを特定するためなどに利用することができます。
詳細はMySQLの公式ドキュメントなどをご確認ください。
16.1.3 Replication with Global Transaction Identifiers
やってみた
必要な手順は主に以下のドキュメントに記載されています。
Using GTID-Based Replication for Aurora MySQL
Replication Between Aurora and MySQL or Between Aurora and Another Aurora DB Cluster
前提条件
以下の条件で実施してみました。
- MasterおよびTarget共に東京リージョンの同じVPCに作成する
- Aurora MySQL 2.07.1
- 2020年2月28日時点での最新バージョン
作業の流れ
作業の流れは以下の通りです。
- DBクラスターグループの設定
- (Replica Master)マスターとなるAurora Clusterの作成
- (Replica Master) バナリログ保持期間の設定
- (Replica Master) サンプルデータのロード
- (Replica Master) スナップショットの作成
- (Replica Target) スナップショットからクラスターを復元
- (Replica Master) レプリケーション用のユーザー作成および権限の付与
- (Replica Master) 【動作確認】レコードの挿入（レプリケーション開始前）
- (Replica Target) レプリケーション設定
- (Replica Target) レプリケーション開始
- (Replica Master) 【動作確認】レコードの挿入（レプリケーション開始後）
DBクラスターグループの設定
マスターとなるAurora ClusterのDBクラスターパラメーターグループで以下のパラメーターを設定します。
| Parameter | Value |
|---|---|
| gtid-mode | ON |
| enforce_gtid_consistency | ON |
| binlog_format | MIXED |
gtid-modeを有効化することで、GTIDを利用できるようにします。
https://dev.mysql.com/doc/refman/5.7/en/replication-options-gtids.html#sysvar_gtid_mode
enforce_gtid_consistencyを有効化することで、GTIDの一貫性に影響を与えるような操作を防止します。
enforce_gtid_consistencyを有効化したときに制限される操作は、以下のドキュメントで確認することができます。
16.1.3.6 Restrictions on Replication with GTIDs
binlog_formatを設定してバイナリログの取得を有効化します。 GTIDはバイナリログに記録されます。
これらのパラメーターは、クラスターパラメーターグループで設定できます。
aws rds create-db-cluster-parameter-group \
--db-cluster-parameter-group-name gtid-clusterparametergroup \
--db-parameter-group-family aurora-mysql5.7 \
--description "Cluster parameter group for replication by GTID"
{
"DBClusterParameterGroup": {
"DBClusterParameterGroupName": "gtid-clusterparametergroup",
"DBParameterGroupFamily": "aurora-mysql5.7",
"Description": "Cluster parameter group for replication by GTID",
"DBClusterParameterGroupArn": "arn:aws:rds:ap-northeast-1:XXXXXXXXXXXX:cluster-pg:gtid-clusterparametergroup"
}
}
aws rds modify-db-cluster-parameter-group \
--db-cluster-parameter-group-name gtid-clusterparametergroup \
--parameters "ParameterName=binlog_format,ParameterValue=MIXED,ApplyMethod=pending-reboot" "ParameterName=gtid-mode,ParameterValue=ON,ApplyMethod=pending-reboot" "ParameterName=enforce_gtid_consistency,ParameterValue=ON,ApplyMethod=pending-reboot"
{
"DBClusterParameterGroupName": "gtid-clusterparametergroup"
}
(Replica Master)マスターとなるAurora Clusterの作成
マスターとなるAurora Clusterを作成します。 VPCはデフォルトVPCを利用します。 サブネットグループおよびセキュリティグループは予め適切に設定されたものを利用します。
aws rds create-db-cluster \
--db-cluster-identifier master-cluster \
--engine aurora-mysql \
--engine-version 5.7.mysql_aurora.2.07.1 \
--master-username master \
--master-user-password Passw0rd \
--db-subnet-group-name default-vpc-44200c20 \
--vpc-security-group-ids sg-7f146119 \
--db-cluster-parameter-group-name gtid-clusterparametergroup
{
"DBCluster": {
"MasterUsername": "master",
"ReaderEndpoint": "master-cluster.cluster-ro-cd8id7gqyjw5.ap-northeast-1.rds.amazonaws.com",
"HttpEndpointEnabled": false,
"ReadReplicaIdentifiers": [],
"VpcSecurityGroups": [
{
"Status": "active",
"VpcSecurityGroupId": "sg-7f146119"
}
],
"CopyTagsToSnapshot": false,
"HostedZoneId": "Z24O6O9L7SGTNB",
"EngineMode": "provisioned",
"Status": "creating",
"MultiAZ": false,
"DomainMemberships": [],
"PreferredBackupWindow": "15:03-15:33",
"DBSubnetGroup": "default-vpc-44200c20",
"AllocatedStorage": 1,
"BackupRetentionPeriod": 1,
"PreferredMaintenanceWindow": "sun:13:01-sun:13:31",
"Engine": "aurora-mysql",
"Endpoint": "master-cluster.cluster-cd8id7gqyjw5.ap-northeast-1.rds.amazonaws.com",
"CrossAccountClone": false,
"IAMDatabaseAuthenticationEnabled": false,
"ClusterCreateTime": "2020-03-01T09:02:47.704Z",
"EngineVersion": "5.7.mysql_aurora.2.07.1",
"DeletionProtection": false,
"DBClusterIdentifier": "master-cluster",
"DbClusterResourceId": "cluster-FREB3HIS5SRMWMVZVEDZOJHVVM",
"DBClusterMembers": [],
"DBClusterArn": "arn:aws:rds:ap-northeast-1:XXXXXXXXXXXX:cluster:master-cluster",
"StorageEncrypted": false,
"AssociatedRoles": [],
"DBClusterParameterGroup": "gtid-clusterparametergroup",
"AvailabilityZones": [
"ap-northeast-1d",
"ap-northeast-1c",
"ap-northeast-1a"
],
"Port": 3306
}
}
今回は、書き込みインスタンスのみ作成します。
aws rds create-db-instance \
--db-instance-identifier master-instance \
--db-cluster-identifier master-cluster \
--engine aurora-mysql \
--db-instance-class db.r5.large
{
"DBInstance": {
"PubliclyAccessible": false,
"MasterUsername": "master",
"MonitoringInterval": 0,
"LicenseModel": "general-public-license",
"VpcSecurityGroups": [
{
"Status": "active",
"VpcSecurityGroupId": "sg-7f146119"
}
],
"CopyTagsToSnapshot": false,
"OptionGroupMemberships": [
{
"Status": "in-sync",
"OptionGroupName": "default:aurora-mysql-5-7"
}
],
"PendingModifiedValues": {},
"Engine": "aurora-mysql",
"MultiAZ": false,
"DBSecurityGroups": [],
"DBParameterGroups": [
{
"DBParameterGroupName": "default.aurora-mysql5.7",
"ParameterApplyStatus": "in-sync"
}
],
"PerformanceInsightsEnabled": false,
"AutoMinorVersionUpgrade": true,
"PreferredBackupWindow": "15:03-15:33",
"PromotionTier": 1,
"DBSubnetGroup": {
"Subnets": [
{
"SubnetStatus": "Active",
"SubnetIdentifier": "subnet-71dc8259",
"SubnetAvailabilityZone": {
"Name": "ap-northeast-1d"
}
},
{
"SubnetStatus": "Active",
"SubnetIdentifier": "subnet-87af54ce",
"SubnetAvailabilityZone": {
"Name": "ap-northeast-1a"
}
},
{
"SubnetStatus": "Active",
"SubnetIdentifier": "subnet-2edde176",
"SubnetAvailabilityZone": {
"Name": "ap-northeast-1c"
}
}
],
"DBSubnetGroupName": "default-vpc-44200c20",
"VpcId": "vpc-44200c20",
"DBSubnetGroupDescription": "Created from the RDS Management Console",
"SubnetGroupStatus": "Complete"
},
"ReadReplicaDBInstanceIdentifiers": [],
"AllocatedStorage": 1,
"DBInstanceArn": "arn:aws:rds:ap-northeast-1:XXXXXXXXXXXX:db:master-instance",
"BackupRetentionPeriod": 1,
"PreferredMaintenanceWindow": "sat:19:33-sat:20:03",
"DBInstanceStatus": "creating",
"IAMDatabaseAuthenticationEnabled": false,
"EngineVersion": "5.7.mysql_aurora.2.07.1",
"DeletionProtection": false,
"DomainMemberships": [],
"DBClusterIdentifier": "master-cluster",
"StorageType": "aurora",
"DbiResourceId": "db-NADM6OWU3ESBG4P4NGHBSMN4UU",
"CACertificateIdentifier": "rds-ca-2019",
"StorageEncrypted": false,
"AssociatedRoles": [],
"DBInstanceClass": "db.r5.large",
"DbInstancePort": 0,
"DBInstanceIdentifier": "master-instance"
}
}
(Replica Master) バナリログ保持期間の設定
マスターとなるAurora Clusterの書き込みインスタンスにログインし、バイナリログの保持期間を設定します。
mysql -h master-cluster.cluster-cd8id7gqyjw5.ap-northeast-1.rds.amazonaws.com -u master -pPassw0rd
CALL mysql.rds_set_configuration('binlog retention hours', 144);
(Replica Master) サンプルデータのロード
マスターにサンプルデータをロードします。 今回は、DMSの検証を想定して提供されているサンプルデータを利用します。 1時間半程度で完了しました。
aws-samples / aws-database-migration-samples
$ git clone https://github.com/aws-samples/aws-database-migration-samples.git $ cd aws-database-migration-samples/mysql/sampledb/v1/ $ mysql -h master-cluster.cluster-cd8id7gqyjw5.ap-northeast-1.rds.amazonaws.com -u master -pPassw0rd
source install-rds.sql
(Replica Master) スナップショットの作成
移行元となるAurora Clusterのスナップショットを作成します。 このスナップショットからレプリカターゲットを作成します。
aws rds create-db-cluster-snapshot \
--db-cluster-snapshot-identifier gtid-snapshot \
--db-cluster-identifier master-cluster
{
"DBClusterSnapshot": {
"Engine": "aurora-mysql",
"SnapshotCreateTime": "2020-03-01T10:28:03.924Z",
"VpcId": "vpc-44200c20",
"DBClusterIdentifier": "master-cluster",
"DBClusterSnapshotArn": "arn:aws:rds:ap-northeast-1:XXXXXXXXXXXX:cluster-snapshot:gtid-snapshot",
"MasterUsername": "master",
"LicenseModel": "aurora-mysql",
"Status": "creating",
"PercentProgress": 0,
"DBClusterSnapshotIdentifier": "gtid-snapshot",
"IAMDatabaseAuthenticationEnabled": false,
"ClusterCreateTime": "2020-03-01T09:02:47.704Z",
"StorageEncrypted": false,
"AllocatedStorage": 1,
"EngineVersion": "5.7.mysql_aurora.2.07.1",
"SnapshotType": "manual",
"AvailabilityZones": [
"ap-northeast-1a",
"ap-northeast-1c",
"ap-northeast-1d"
],
"Port": 0
}
}
(Replica Target) スナップショットからクラスターを復元
スナップショットからレプリカターゲットを作成します。
aws rds restore-db-cluster-from-snapshot \
--snapshot-identifier gtid-snapshot \
--db-cluster-identifier replica-cluster \
--engine aurora-mysql \
--engine-version 5.7.mysql_aurora.2.07.1 \
--db-subnet-group-name default-vpc-44200c20 \
--vpc-security-group-ids sg-7f146119 \
--db-cluster-parameter-group-name gtid-clusterparametergroup
{
"DBCluster": {
"MasterUsername": "master",
"ReaderEndpoint": "replica-cluster.cluster-ro-cd8id7gqyjw5.ap-northeast-1.rds.amazonaws.com",
"HttpEndpointEnabled": false,
"ReadReplicaIdentifiers": [],
"VpcSecurityGroups": [
{
"Status": "active",
"VpcSecurityGroupId": "sg-7f146119"
}
],
"CopyTagsToSnapshot": false,
"HostedZoneId": "Z24O6O9L7SGTNB",
"EngineMode": "provisioned",
"Status": "creating",
"MultiAZ": false,
"DomainMemberships": [],
"PreferredBackupWindow": "15:03-15:33",
"DBSubnetGroup": "default-vpc-44200c20",
"AllocatedStorage": 1,
"BackupRetentionPeriod": 1,
"PreferredMaintenanceWindow": "sun:13:01-sun:13:31",
"Engine": "aurora-mysql",
"Endpoint": "replica-cluster.cluster-cd8id7gqyjw5.ap-northeast-1.rds.amazonaws.com",
"CrossAccountClone": false,
"IAMDatabaseAuthenticationEnabled": false,
"ClusterCreateTime": "2020-03-01T10:31:11.011Z",
"EngineVersion": "5.7.mysql_aurora.2.07.1",
"DeletionProtection": false,
"DBClusterIdentifier": "replica-cluster",
"DbClusterResourceId": "cluster-S74SRVSAMYBJ7ELZ7BZRCMSKAY",
"DBClusterMembers": [],
"DBClusterArn": "arn:aws:rds:ap-northeast-1:XXXXXXXXXXXX:cluster:replica-cluster",
"StorageEncrypted": false,
"AssociatedRoles": [],
"DBClusterParameterGroup": "gtid-clusterparametergroup",
"AvailabilityZones": [
"ap-northeast-1d",
"ap-northeast-1c",
"ap-northeast-1a"
],
"Port": 3306
}
}
併せて、インスタンスも作成します。
aws rds create-db-instance \
--db-instance-identifier replica-instance \
--db-cluster-identifier replica-cluster \
--engine aurora-mysql \
--db-instance-class db.r5.large
{
"DBInstance": {
"PubliclyAccessible": false,
"MasterUsername": "master",
"MonitoringInterval": 0,
"LicenseModel": "general-public-license",
"VpcSecurityGroups": [
{
"Status": "active",
"VpcSecurityGroupId": "sg-7f146119"
}
],
"CopyTagsToSnapshot": false,
"OptionGroupMemberships": [
{
"Status": "in-sync",
"OptionGroupName": "default:aurora-mysql-5-7"
}
],
"PendingModifiedValues": {},
"Engine": "aurora-mysql",
"MultiAZ": false,
"DBSecurityGroups": [],
"DBParameterGroups": [
{
"DBParameterGroupName": "default.aurora-mysql5.7",
"ParameterApplyStatus": "in-sync"
}
],
"PerformanceInsightsEnabled": false,
"AutoMinorVersionUpgrade": true,
"PreferredBackupWindow": "15:03-15:33",
"PromotionTier": 1,
"DBSubnetGroup": {
"Subnets": [
{
"SubnetStatus": "Active",
"SubnetIdentifier": "subnet-71dc8259",
"SubnetAvailabilityZone": {
"Name": "ap-northeast-1d"
}
},
{
"SubnetStatus": "Active",
"SubnetIdentifier": "subnet-87af54ce",
"SubnetAvailabilityZone": {
"Name": "ap-northeast-1a"
}
},
{
"SubnetStatus": "Active",
"SubnetIdentifier": "subnet-2edde176",
"SubnetAvailabilityZone": {
"Name": "ap-northeast-1c"
}
}
],
"DBSubnetGroupName": "default-vpc-44200c20",
"VpcId": "vpc-44200c20",
"DBSubnetGroupDescription": "Created from the RDS Management Console",
"SubnetGroupStatus": "Complete"
},
"ReadReplicaDBInstanceIdentifiers": [],
"AllocatedStorage": 1,
"DBInstanceArn": "arn:aws:rds:ap-northeast-1:XXXXXXXXXXXX:db:replica-instance",
"BackupRetentionPeriod": 1,
"PreferredMaintenanceWindow": "sun:19:21-sun:19:51",
"DBInstanceStatus": "creating",
"IAMDatabaseAuthenticationEnabled": false,
"EngineVersion": "5.7.mysql_aurora.2.07.1",
"DeletionProtection": false,
"DomainMemberships": [],
"DBClusterIdentifier": "replica-cluster",
"StorageType": "aurora",
"DbiResourceId": "db-E2BM5QITTBEPRZEROTBMZDB7NE",
"CACertificateIdentifier": "rds-ca-2019",
"StorageEncrypted": false,
"AssociatedRoles": [],
"DBInstanceClass": "db.r5.large",
"DbInstancePort": 0,
"DBInstanceIdentifier": "replica-instance"
}
}
インスタンス作成後、インスタンスのイベントを確認します。 その中に、リカバリーに利用したスナップショットを取得した際のバイナリログファイル名およびポジションを確認できます。 これは、レプリケーションを開始する際の設定に利用します。 ここ重要です。
aws rds describe-events \
--source-identifier replica-instance \
--source-type db-instance
{
"Events": [
{
"EventCategories": [],
"SourceType": "db-instance",
"SourceArn": "arn:aws:rds:ap-northeast-1:521539043457:db:replica-instance",
"Date": "2020-03-01T10:53:39.493Z",
"Message": "Binlog position from crash recovery is mysql-bin-changelog.000018 83195195",
"SourceIdentifier": "replica-instance"
},
{
"EventCategories": [
"creation"
],
"SourceType": "db-instance",
"SourceArn": "arn:aws:rds:ap-northeast-1:521539043457:db:replica-instance",
"Date": "2020-03-01T10:54:39.546Z",
"Message": "DB instance created",
"SourceIdentifier": "replica-instance"
},
{
"EventCategories": [
"availability"
],
"SourceType": "db-instance",
"SourceArn": "arn:aws:rds:ap-northeast-1:521539043457:db:replica-instance",
"Date": "2020-03-01T10:54:44.289Z",
"Message": "DB instance shutdown",
"SourceIdentifier": "replica-instance"
},
{
"EventCategories": [
"availability"
],
"SourceType": "db-instance",
"SourceArn": "arn:aws:rds:ap-northeast-1:521539043457:db:replica-instance",
"Date": "2020-03-01T10:54:48.320Z",
"Message": "DB instance restarted",
"SourceIdentifier": "replica-instance"
}
]
}
(Replica Master) レプリケーション用のユーザー作成および権限の付与
レプリカターゲットからレプリカマスターに接続してレプリケーションを実行するためのユーザーを作成します。
CREATE USER 'rpl'@'%' IDENTIFIED BY 'Passw0rd';
レプリケーションの必要な権限を付与します。
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'rpl'@'%';
(Replica Master) 【動作確認】レコードの挿入（レプリケーション開始前）
現時点ではマスターとレプリカのテーブルが同じ状態のため、動作確認のためマスター側に変更を加えます。 以下のテーブルにレコードを挿入します。
select * from nfl_stadium_data;
+-------------------------------------+------------------+-----------------------------+------------------------------------------------------+-------------+----------------------+--------+-------------------+ | stadium | seating_capacity | location | surface | roof | team | opened | sport_location_id | +-------------------------------------+------------------+-----------------------------+------------------------------------------------------+-------------+----------------------+--------+-------------------+ | Los Angeles Memorial Coliseum | 93607 | Los Angeles, California | Natural grass | Open | Los Angeles Rams | 1923 | 31 | | MetLife Stadium | 82500 | East Rutherford, New Jersey | UBU Speed Series S5-M Synthetic Turf | Open | New York Giants | 2010 | 32 | | MetLife Stadium | 82500 | East Rutherford, New Jersey | UBU Speed Series S5-M Synthetic Turf | Open | New York Jets | 2010 | 32 | | FedExField | 82000 | Landover, Maryland | Latitude 36 Bermuda Grass | Open | Washington Redskins | 1997 | 34 | | Lambeau Field | 81435 | Green Bay, Wisconsin | Hybrid Grass-Synthetic | Open | Green Bay Packers | 1957 | 35 | | AT&T Stadium | 80000 | Arlington, Texas | Matrix RealGrass artificial turf | Retractable | Dallas Cowboys | 2009 | 36 | | Arrowhead Stadium | 76416 | Kansas City, Missouri | Latitude 36 Bermuda Grass | Open | Kansas City Chiefs | 1972 | 37 | | Sports Authority Field at Mile High | 76125 | Denver, Colorado | Kentucky Bluegrass | Open | Denver Broncos | 2001 | 38 | | Bank of America Stadium | 75419 | Charlotte, North Carolina | Voyager Bermuda Grass | Open | Carolina Panthers | 1996 | 39 | | Mercedes-Benz Superdome | 73000 | New Orleans, Louisiana | UBU Turf (artificial) | Fixed | New Orleans Saints | 1975 | 40 | | NRG Stadium | 72220 | Houston, Texas | AstroTurf GameDay Grass 3D | Retractable | Houston Texans | 2002 | 41 | | New Era Field | 71870 | Orchard Park, New York | A-Turf Titan 50 (artificial) | Open | Buffalo Bills | 1973 | 42 | | Georgia Dome | 71250 | Atlanta, Georgia | FieldTurf Classic HD | Fixed | Atlanta Falcons | 1992 | 43 | | M&T Bank Stadium | 71008 | Baltimore, Maryland | Latitude 36 Bermuda Grass | Open | Baltimore Ravens | 1998 | 44 | | Qualcomm Stadium | 70561 | San Diego, California | Bandera Bermuda Grass | Open | San Diego Chargers | 1967 | 45 | | Lincoln Financial Field | 69596 | Philadelphia, Pennsylvania | Desso GrassMaster | Open | Philadelphia Eagles | 2003 | 46 | | Nissan Stadium | 69143 | Nashville, Tennessee | TifSport Bermuda Grass | Open | Tennessee Titans | 1999 | 47 | | Levi's Stadium | 68500 | Santa Clara, California | Tifway II Bermuda Grass / Perennial Ryegrass mixture | Open | San Francisco 49ers | 2014 | 48 | | Heinz Field | 68400 | Pittsburgh, Pennsylvania | Kentucky Bluegrass | Open | Pittsburgh Steelers | 2001 | 49 | | CenturyLink Field | 68000 | Seattle, Washington | FieldTurf Revolution | Open | Seattle Seahawks | 2002 | 50 | | FirstEnergy Stadium | 67431 | Cleveland, Ohio | Kentucky Bluegrass | Open | Cleveland Browns | 1999 | 51 | | EverBank Field | 67246 | Jacksonville, Florida | Tifway 419 Bermuda Grass | Open | Jacksonville Jaguars | 1995 | 52 | | Lucas Oil Stadium | 67000 | Indianapolis, Indiana | FieldTurf Classic HD | Retractable | Indianapolis Colts | 2008 | 53 | | Gillette Stadium | 66829 | Foxborough, Massachusetts | FieldTurf Revolution | Open | New England Patriots | 2002 | 54 | | U.S. Bank Stadium | 66200 | Minneapolis, Minnesota | UBU Speed Series S5-M Synthetic Turf | Fixed | Minnesota Vikings | 2016 | 55 | | Raymond James Stadium | 65890 | Tampa, Florida | Tifway 419 Bermuda Grass | Open | Tampa Bay Buccaneers | 1998 | 56 | | Paul Brown Stadium | 65515 | Cincinnati, Ohio | UBU Speed Series S5-M Synthetic Turf | Open | Cincinnati Bengals | 2000 | 57 | | Hard Rock Stadium | 65326 | Miami Gardens, Florida | Platinum TE Paspalum | Open | Miami Dolphins | 1987 | 58 | | Ford Field | 65000 | Detroit, Michigan | FieldTurf Classic HD | Fixed | Detroit Lions | 2002 | 59 | | University of Phoenix Stadium | 63400 | Glendale, Arizona | Tifway 419 Bermuda Grass | Retractable | Arizona Cardinals | 2006 | 60 | | Soldier Field | 61500 | Chicago, Illinois | Kentucky Bluegrass | Open | Chicago Bears | 1924 | 61 | | Oakland Alameda Coliseum | 56063 | Oakland, California | Tifway II Bermuda Grass | Open | Oakland Raiders | 1966 | 62 | +-------------------------------------+------------------+-----------------------------+------------------------------------------------------+-------------+----------------------+--------+-------------------+ 32 rows in set (0.03 sec)
INSERT INTO nfl_stadium_data (stadium, seating_capacity, location, surface, roof, team, opened, sport_location_id) VALUES ("Classmethod Stadium", 50000, "Los Angeles, California", "Natural grass", "Open", "Los Angeles Rams", 2000, 63);
(Replica Target) レプリケーション設定
「バイナリログファイル名とポジションを確認」で確認したファイル名とポジションを利用してレプリケーションの設定を実施します。
mysql -h replica-cluster.cluster-cd8id7gqyjw5.ap-northeast-1.rds.amazonaws.com -u master -pPassw0rd
CALL mysql.rds_set_external_master ('master-cluster.cluster-cd8id7gqyjw5.ap-northeast-1.rds.amazonaws.com', 3306, 'rpl', 'Passw0rd', 'mysql-bin-changelog.000018', 83197316, 0);
(Replica Target) レプリケーション開始
レプリケーションを開始します。
CALL mysql.rds_start_replication;
レプリケーションを開始したら、スレーブとしてのステータスを確認します。
SHOW SLAVE STATUS;
+----------------------------------+----------------------------------------------------------------------+-------------+-------------+---------------+----------------------------+---------------------+-----------------+---------------+----------------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------------------------------------------------------------------------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------------------------------------+----------------------------------------------------------+---------------+----------------------+--------------+--------------------+ | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version | +----------------------------------+----------------------------------------------------------------------+-------------+-------------+---------------+----------------------------+---------------------+-----------------+---------------+----------------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------------------------------------------------------------------------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------------------------------------+----------------------------------------------------------+---------------+----------------------+--------------+--------------------+ | Waiting for master to send event | master-cluster.cluster-cd8id7gqyjw5.ap-northeast-1.rds.amazonaws.com | rpl | 3306 | 60 | mysql-bin-changelog.000018 | 83197944 | relaylog.000002 | 958 | mysql-bin-changelog.000018 | Yes | Yes | | | | mysql.rds_replication_status,mysql.rds_monitor,mysql.rds_sysinfo,mysql.rds_configuration,mysql.rds_history | | | 0 | | 0 | 83197944 | 1158 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 2025415487 | e13ee999-1c5a-33c2-8b1f-cef713e7ce1f | mysql.slave_master_info | 0 | NULL | Slave has read all relay log; waiting for more updates | 86400 | | | | | | e13ee999-1c5a-33c2-8b1f-cef713e7ce1f:21879-21880 | e13ee999-1c5a-33c2-8b1f-cef713e7ce1f:1-21876:21879-21880 | 0 | | | | +----------------------------------+----------------------------------------------------------------------+-------------+-------------+---------------+----------------------------+---------------------+-----------------+---------------+----------------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------------------------------------------------------------------------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------------------------------------+----------------------------------------------------------+---------------+----------------------+--------------+--------------------+ 1 row in set (0.00 sec)
Seconds_Behind_Masterが0のとき、レプリカがイベントを処理していない（同じ状態）であることを示しています。 詳細は以下のドキュメントを確認してください。
13.7.5.34 SHOW SLAVE STATUS Statement
レプリケーションを開始したら、マスター側で加えた変更がレプリカ側にも反映されていることを確認します。
select * from nfl_stadium_data;
+-------------------------------------+------------------+-----------------------------+------------------------------------------------------+-------------+----------------------+--------+-------------------+ | stadium | seating_capacity | location | surface | roof | team | opened | sport_location_id | +-------------------------------------+------------------+-----------------------------+------------------------------------------------------+-------------+----------------------+--------+-------------------+ | Los Angeles Memorial Coliseum | 93607 | Los Angeles, California | Natural grass | Open | Los Angeles Rams | 1923 | 31 | | MetLife Stadium | 82500 | East Rutherford, New Jersey | UBU Speed Series S5-M Synthetic Turf | Open | New York Giants | 2010 | 32 | | MetLife Stadium | 82500 | East Rutherford, New Jersey | UBU Speed Series S5-M Synthetic Turf | Open | New York Jets | 2010 | 32 | | FedExField | 82000 | Landover, Maryland | Latitude 36 Bermuda Grass | Open | Washington Redskins | 1997 | 34 | | Lambeau Field | 81435 | Green Bay, Wisconsin | Hybrid Grass-Synthetic | Open | Green Bay Packers | 1957 | 35 | | AT&T Stadium | 80000 | Arlington, Texas | Matrix RealGrass artificial turf | Retractable | Dallas Cowboys | 2009 | 36 | | Arrowhead Stadium | 76416 | Kansas City, Missouri | Latitude 36 Bermuda Grass | Open | Kansas City Chiefs | 1972 | 37 | | Sports Authority Field at Mile High | 76125 | Denver, Colorado | Kentucky Bluegrass | Open | Denver Broncos | 2001 | 38 | | Bank of America Stadium | 75419 | Charlotte, North Carolina | Voyager Bermuda Grass | Open | Carolina Panthers | 1996 | 39 | | Mercedes-Benz Superdome | 73000 | New Orleans, Louisiana | UBU Turf (artificial) | Fixed | New Orleans Saints | 1975 | 40 | | NRG Stadium | 72220 | Houston, Texas | AstroTurf GameDay Grass 3D | Retractable | Houston Texans | 2002 | 41 | | New Era Field | 71870 | Orchard Park, New York | A-Turf Titan 50 (artificial) | Open | Buffalo Bills | 1973 | 42 | | Georgia Dome | 71250 | Atlanta, Georgia | FieldTurf Classic HD | Fixed | Atlanta Falcons | 1992 | 43 | | M&T Bank Stadium | 71008 | Baltimore, Maryland | Latitude 36 Bermuda Grass | Open | Baltimore Ravens | 1998 | 44 | | Qualcomm Stadium | 70561 | San Diego, California | Bandera Bermuda Grass | Open | San Diego Chargers | 1967 | 45 | | Lincoln Financial Field | 69596 | Philadelphia, Pennsylvania | Desso GrassMaster | Open | Philadelphia Eagles | 2003 | 46 | | Nissan Stadium | 69143 | Nashville, Tennessee | TifSport Bermuda Grass | Open | Tennessee Titans | 1999 | 47 | | Levi's Stadium | 68500 | Santa Clara, California | Tifway II Bermuda Grass / Perennial Ryegrass mixture | Open | San Francisco 49ers | 2014 | 48 | | Heinz Field | 68400 | Pittsburgh, Pennsylvania | Kentucky Bluegrass | Open | Pittsburgh Steelers | 2001 | 49 | | CenturyLink Field | 68000 | Seattle, Washington | FieldTurf Revolution | Open | Seattle Seahawks | 2002 | 50 | | FirstEnergy Stadium | 67431 | Cleveland, Ohio | Kentucky Bluegrass | Open | Cleveland Browns | 1999 | 51 | | EverBank Field | 67246 | Jacksonville, Florida | Tifway 419 Bermuda Grass | Open | Jacksonville Jaguars | 1995 | 52 | | Lucas Oil Stadium | 67000 | Indianapolis, Indiana | FieldTurf Classic HD | Retractable | Indianapolis Colts | 2008 | 53 | | Gillette Stadium | 66829 | Foxborough, Massachusetts | FieldTurf Revolution | Open | New England Patriots | 2002 | 54 | | U.S. Bank Stadium | 66200 | Minneapolis, Minnesota | UBU Speed Series S5-M Synthetic Turf | Fixed | Minnesota Vikings | 2016 | 55 | | Raymond James Stadium | 65890 | Tampa, Florida | Tifway 419 Bermuda Grass | Open | Tampa Bay Buccaneers | 1998 | 56 | | Paul Brown Stadium | 65515 | Cincinnati, Ohio | UBU Speed Series S5-M Synthetic Turf | Open | Cincinnati Bengals | 2000 | 57 | | Hard Rock Stadium | 65326 | Miami Gardens, Florida | Platinum TE Paspalum | Open | Miami Dolphins | 1987 | 58 | | Ford Field | 65000 | Detroit, Michigan | FieldTurf Classic HD | Fixed | Detroit Lions | 2002 | 59 | | University of Phoenix Stadium | 63400 | Glendale, Arizona | Tifway 419 Bermuda Grass | Retractable | Arizona Cardinals | 2006 | 60 | | Soldier Field | 61500 | Chicago, Illinois | Kentucky Bluegrass | Open | Chicago Bears | 1924 | 61 | | Oakland Alameda Coliseum | 56063 | Oakland, California | Tifway II Bermuda Grass | Open | Oakland Raiders | 1966 | 62 | | Classmethod Stadium | 50000 | Los Angeles, California | Natural grass | Open | Los Angeles Rams | 2000 | 63 | +-------------------------------------+------------------+-----------------------------+------------------------------------------------------+-------------+----------------------+--------+-------------------+ 33 rows in set (0.02 sec)
(Replica Master) 【動作確認】レコードの挿入（レプリケーション開始後）
手順と結果は割愛しますが、レプリケーション開始後にマスター側に加えた変更も反映されていることを確認しました。
まとめ
以上の手順でAuroraクラスター間でのレプリケーションを行ってみました。 MySQLの機能を利用した方法ですので、外部のMySQL Serverとレプリケーションすることも可能です。 必要があれば試してみてください。
![[アップデート] Amazon Aurora のモニタリング機能に「CloudWatch Database Insights」が登場しました](https://images.ctfassets.net/ct0aopd36mqt/3nibOl2sZ0LKJueI0FHFi5/14de7bd294f7916a28b789105c60d450/reinvent-2024-newservice-jp.jpg)
