The method described herein is based using an Alibaba Cloud ECS server. Alibaba Cloud RDS uses a DNS address to provide services through port 3306. This makes the master and slave RDS backend nodes invisible. In that case, how can we synchronize RDS data to a local database? The following figure shows the structure of the nodes:
A Server Load Balancer (SLB) must get connected to the RDS and traffic directed through the SLB to the master node. If the account used to connect with RDS is assigned the REPLICATION SLAVE and REPLICATION CLIENT permissions, it can be used to synchronize the binary logs generated by the master node to the local database.
However, using binary log files and positions to synchronize RDS data to a local database may easily be interrupted. This is because the positions of the RDS binary logs to which the local database points change (binary log positions differ between the master and slave nodes) when a failover, restart, or inter-node migration occurs on RDS.
The RDS 5.6 version employs the new synchronization method, i.e., GTID. The master and slave nodes of RDS have the same GTID. Whenever a failover, reboot, or migration occurs, GTID remains unchanged. Therefore the synchronization link between ECS and RDS remains uninterrupted. Consequently, you should update the RDS to version 5.6 if you need to synchronize data from RDS to ECS.
1.Install MySQL on the ECS server.
Pay attention to the following points:
master-info-repository=file### Required for slave node configuration
relay-log-info_repository=file### Required for slave node configuration
binlog-format=ROW### Required for slave node configuration
gtid-mode=on###Required for enabling GTID
enforce-gtid-consistency=true###Required for enabling GTID
innodb_data_file_path=ibdata1:200M:autoextend###Using parameter backup-my.cnf in RDS physical backups
innodb_log_files_in_group=2###Using parameter backup-my.cnf in RDS physical backups
innodb_log_file_size=524288000###Using parameter backup-my.cnf in RDS physical backups
2.After MySQL gets installed, use the physical backup files offered by RDS to restore data to the local MySQL database. For details, refer to: https://www.alibabacloud.com/help/doc-detail/41817.htm
Add the following three parameters in the backup-my.cnf file generated during backup decompression to the startup file:
3.As the local database gets started, you can set the synchronization relationship between the local database and RDS.
mysql> reset slave;
ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. You must at least set server-id to allow either a master or a slave. Added error messages may be found in the MySQL error log.
The error is due to the RDS backup file including the master/slave synchronization relationship of RDS. The relationship must get removed as follows:
truncate table slave_relay_log_info;
truncate table mysql.slave_master_info;
truncate table mysql.slave_worker_info;
Open the compressed backup file, and you will find the xtrabackup_slave_info file. The first line indicates the GTID that the RDS used when the backup finished, and the command in the line must get executed in the local MySQLdatabase.
Set the synchronization relationship between the local MySQL database and RDS. The account qianyi is added using the RDS control system. (Note: A synchronization account cannot start with repl.)
4.You can show slave statusG on the local MySQL database to check the synchronization relationship status. You can also insert test data inside RDS or restart the instance to check synchronization.
mysql> show slave statusG;
Slave_IO_State: Queueing master event to the relay log
Slave_SQL_Running_State: Reading event from the relay log
5.Implement fine monitoring. Because we use the native MySQL synchronization mechanism, synchronization between the local MySQL database and RDS may be interrupted. You can regularly verify that the values of Slave_IO_Running and Slave_SQL_Running are yes and check the delay of synchronization between the local MySQL database and RDS using the Seconds_Behind_Master command.
We discussed the data synchronization process from ApsaraDB RDS for MySQL to a local user-defined database using an Alibaba Cloud ECS server as an example. We hope that this article clears some confusion as this query has been raised by our RDS users for a long time. We have also represented the master-slave architecture to explain the whole process.
Alibaba Clouder - November 8, 2017
Alibaba Clouder - January 10, 2018
Alibaba Cloud MaxCompute - December 17, 2018
Alibaba Clouder - January 5, 2018
Alibaba Clouder - August 1, 2017
Alibaba Clouder - February 26, 2019
Mitigate the scalability problem of single machine relational databases for large-scale online databases.Learn More
A reliable, cost-efficient backup service for continuous data protection.Learn More
Provides an easy-to-use BaaS solution that protects your data on premises and on the cloud.Learn More
An on-demand database hosting service for MySQL with automated monitoring, backup and disaster recovery capabilitiesLearn More
More Posts by Alibaba Clouder