AWS: Create external Aurora replication host by CLI
One of the big advantages of using AWS is that it’s able to use reliable managed database services like RDS, Aurora. It’s also very easy to increase or decrease replicas. However sometimes, it’s necessary to create a replica externally to avoid bad effects on the production environment. I found the official document and I created an Aurora’s replica following the document. So, let me share the procedure.
First, it needs to modify the Aurora’s default settings as follows.
- Set binlog_format parameter to MIXED
- Set binlog_checksum parameter to NONE
Then, it needs to reboot to apply the above parameters. So, it will happen 10 or 20 seconds down time.
After rebooting, it should make sure those parameters are applied correctly.
1mysql> SHOW VARIABLES LIKE 'binlog_format';
2+---------------+-------+
3| Variable_name | Value |
4+---------------+-------+
5| binlog_format | MIXED |
6+---------------+-------+
71 row in set (0.01 sec)
8
9mysql> show binary logs;
10+----------------------------+-----------+
11| Log_name | File_size |
12+----------------------------+-----------+
13| mysql-bin-changelog.000001 | 120 |
14| mysql-bin-changelog.000002 | 120 |
15+----------------------------+-----------+
162 rows in set (0.00 sec)
Then, make the retention term of the binlog file longer.
1mysql> CALL mysql.rds_set_configuration('binlog retention hours', 144);
2Query OK, 0 rows affected (0.00 sec)
And, create a replication user.
1mysql> grant replication slave on *.- to 'repl'@'%' identified by 'fuga';
2Query OK, 0 rows affected (0.01 sec)
Set some common variables.
1db_cluster_identifier='temp-db-cluster'
2db_instance_identifier='temp-db-instance'
3db_instance_class='db.t2.small'
4db_engine='aurora-mysql'
5cluster_parameter_group=’my-cluster-param’
6db_parameter_group_name=’my-db-param’
7db_name=’targetdatabase’
Get the latest snapshot ID.
1snapshot_identifier=`aws rds describe-db-cluster-snapshots \
2 | jq -r '.DBClusterSnapshots
3 | sort_by(.SnapshotCreateTime)
4 | reverse
5 | .[0].DBClusterSnapshotIdentifier'`
Restore a new cluster from the above snapshot.
1aws rds restore-db-cluster-from-snapshot \
2--engine $db_engine \
3--db-cluster-identifier $db_cluster_identifier \
4--snapshot-identifier $snapshot_identifier \
5--db-cluster-parameter-group-name $cluster_parameter_group \
6--no-deletion-protection
Wait for creating the above process. After it’s completed, add a new instance to the above cluster.
1aws rds create-db-instance \
2--engine $db_engine \
3--db-instance-identifier $db_instance_identifier \
4--db-instance-class $db_instance_class \
5--db-parameter-group-name $db_parameter_group_name \
6--db-cluster-identifier $db_cluster_identifier
Wait for adding the instance process. Then, get the binlog’s position info from its events.
1aws rds describe-events \
2--source-identifier $db_instance_identifier \
3--source-type db-instance \
4| jq -r '.Events[] | .Message | select (. | test("Binlog"))'
5
6# Output is like this
7# Binlog position from crash recovery is mysql-bin-changelog.000007 91466190
Get dump data from the above temporary cluster.
1MYSQL_PWD=’pass’
2mysqldump -h $db_endpoint \
3 --user=user \
4 --quote-names \
5 --flush-logs \
6 --master-data=2 \
7 --single-transaction \
8 --hex-blob \
9 --routines \
10 --triggers \
11 --events \
12 --default-character-set=utf8 \
13 $db_name \
14 > dump_${db_cluster_identifier}.sql
Create a MySQL server out of the Aurora cluster. At this time, I used docker-compose on a server.
1version: '3.7'
2services:
3 local-replica:
4 image: mysql:5.7
5 restart: always
6 ports:
7 - "13306:3306"
8 volumes:
9 - ./mysql:/var/lib/mysql
10 - ./repl.cnf:/etc/mysql/conf.d/repl.cnf
11 env_file:
12 - .env_mysql
13 user: mysql
The above repl.cnf file is like this.
1[mysqld]
2server-id=100
3replicate-ignore-db=mysql
And make the "mysql" directory to be mounted for data.
1mkdir mysql
Then, run the container with the official MySQL image.
1docker-compose up -d
Import the dump file.
1mysql \
2--protocol tcp \
3-P 13306 \
4-u root \
5-p \
6< dump_${db_cluster_identifier}.sql
Login and connect to the above DB and execute CHANGE MASTER command to modify the replication master info with the above binlog position info.
1mysql> CHANGE MASTER TO
2 MASTER_HOST = 'my-master-host',
3 MASTER_PORT = 3306,
4 MASTER_USER = 'repl',
5 MASTER_PASSWORD =’fuga’,
6 MASTER_LOG_FILE = 'mysql-bin-changelog.000007',
7 MASTER_LOG_POS = 91466190;
Then, add grants on the local replica.
1mysql> GRANT SELECT, RELOAD, PROCESS, REFERENCES, INDEX, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, SHOW VIEW, EVENT, TRIGGER ON *.- TO 'hoge'@'%' IDENTIFIED BY PASSWORD '*AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' WITH GRANT OPTION
Start slave.
1mysql> start slave;
2
3mysql> show slave status\G;
4**************************- 1. row ***************************
5 Slave_IO_State: Waiting for master to send event
6 Master_Host: my-master-host
7 Master_User: repl
8 Master_Port: 3306
9 Connect_Retry: 60
10 Master_Log_File: mysql-bin-changelog.000002
11 Read_Master_Log_Pos: 4656141
12 Relay_Log_File: 80a8d68c1881-relay-bin.000002
13 Relay_Log_Pos: 351215
14 Relay_Master_Log_File: mysql-bin-changelog.000002
15 Slave_IO_Running: Yes
16 Slave_SQL_Running: Yes
17 :
18 :
191 row in set (0.00 sec)
20
21ERROR:
22No query specified
The temporary cluster is unnecessary anymore. So, it should be deleted. First, delete the instance.
1aws rds delete-db-instance \
2--db-instance-identifier $db_instance_identifier \
3--skip-final-snapshot
Then, delete the cluster.
1aws rds delete-db-cluster \
2--db-cluster-identifier $db_cluster_identifier \
3--skip-final-snapshot