How to Synchronize RDS Data to a Local User-Defined Database

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
简介: Here we will discuss how to synchronize RDS data to a local database. We will also explore the critical steps involving the data synchronization from RDS to a local database.

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:

1

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.

2

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.

Key Steps to Synchronizing Data from RDS to a Local Database

1.Install MySQL on the ECS server.

Pay attention to the following points:

  • The version of the database needs to be 5.6.16 or above
  • Set the following key parameters in my.cnf:
    server-id ###Required for slave node configuration

    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

Note:

Add the following three parameters in the backup-my.cnf file generated during backup decompression to the startup file:

innodb_checksum_algorithm=innodb

innodb_data_file_path=ibdata1:200M:autoextend

innodb_log_files_in_group=2

3.As the local database gets started, you can set the synchronization relationship between the local database and RDS.

  • reset slave;####Used to reset the synchronization relationship of the local MySQL database. An error may occur during this step.

    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;

    Restart MySQL.


  • SET @@GLOBAL.GTID_PURGED

    =’818795a2-8aa8-11e5-95b1:1-289,8da7b8ab-8aa8-11e5-95b1:1-75′;

    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.

Change master to

master_host=’gtid1.mysql.rds.aliyuncs.com’,

master_user=’qianyi’,master_port=3306,master_password=’qianyi’,

master_auto_position=1;

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

Master_Host: gtid1.mysql.rds.aliyuncs.com

Master_User: qianyi

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000007

Read_Master_Log_Pos: 625757

Relay_Log_File: slave-relay.000002

Relay_Log_Pos: 2793

Relay_Master_Log_File: mysql-bin.000007

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Exec_Master_Log_Pos: 612921

Relay_Log_Space: 15829

Seconds_Behind_Master: 57133

Master_SSL_Verify_Server_Cert: No

Master_Server_Id: 2319282016

Master_UUID: 818795a2-8aa8-11e5-95b1-6c92bf20cfcf

Master_Info_File: /data/work/mysql/data3001/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Reading event from the relay log

Master_Retry_Count: 86400

818795a2-8aa8-11e5-95b1-6c92bf20cfcf:17754-17811

Executed_Gtid_Set: 818795a2-8aa8-11e5-95b1-6c92bf20cfcf:1-17761

Auto_Position: 1

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.

Conclusion

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.

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2月前
|
存储 容灾 安全
在阿里云RDS(Relational Database Service)迁移前准备目标区域选择
在阿里云RDS(Relational Database Service)迁移前准备目标区域选择
47 3
|
2月前
|
存储 关系型数据库 数据库
进行RDS(Relational Database Service)迁移时,资源需求分析
进行RDS(Relational Database Service)迁移时,资源需求分析
43 3
|
2月前
|
SQL 运维 关系型数据库
在阿里云RDS(Relational Database Service)进行跨区域迁移
在阿里云RDS(Relational Database Service)进行跨区域迁移
81 2
|
2月前
|
存储 运维 容灾
在规划阿里云RDS(Relational Database Service)跨区迁移
在规划阿里云RDS(Relational Database Service)跨区迁移
57 1
|
8天前
|
存储 NoSQL 关系型数据库
RDS(Relational Database Service)和Redis
【7月更文挑战第12天】
6 0
|
8天前
|
关系型数据库 MySQL 数据库
Relational Database Service RDS 各个版本
【7月更文挑战第12天】
7 0
|
1月前
|
SQL 关系型数据库 MySQL
MySQL Online DDL(Data Definition Language)
MySQL Online DDL(Data Definition Language)
32 1
|
2月前
|
存储 监控 关系型数据库
在规划和准备阿里云RDS(Relational Database Service)跨区域迁移
在规划和准备阿里云RDS(Relational Database Service)跨区域迁移
41 1
|
1月前
|
SQL 关系型数据库 MySQL
MySQL数据库——视图-检查选项(cascaded、local)
MySQL数据库——视图-检查选项(cascaded、local)
19 0
|
2月前
|
监控 安全 应用服务中间件
SAP ABAP STRUST 事务码里的错误消息 - Local PSE does not match database original
SAP ABAP STRUST 事务码里的错误消息 - Local PSE does not match database original

推荐镜像

更多