×
Community Blog RDS Data Synchronization to a Local Database

RDS Data Synchronization to a Local Database

In this article, you will get some information on how to synchronize the data from ApsaraDB RDS for MySQL to a local user-defined database.

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. But the RDS 5.6 version employs the new synchronization method, i.e., GTID which can help to synchronize data between ECS and RDS without interruption whenever a failover, reboot, or migration occurs.

Key Steps to Synchronizing Data from RDS to a Local Database

1. Install MySQL on the ECS server.

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

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.

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.

For details, you can go to this tutorial.

Related Blog Posts

Using XtraBackup for Physical Backup and Restoration

Xtrabackup is a MySQL database backup tool provided by Percona. According to the official introduction, "Percona XtraBackup is the world's only open-source, free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases."

Master-Slave Data Synchronization for MySQL

MySQL is the world's most popular open-source database. As an important part of LAMP, a combination of open-source software (Linux + Apache + MySQL + Perl/PHP/Python), MySQL is widely used in various applications. Chinese Internet forum system Discuz! and blogging platform WordPress, which swept the Internet in the Web 2.0 era, are both built based on a MySQL-based underlying architecture. In the Web 3.0 era, leading Internet companies such as Alibaba, Facebook, and Google have all built mature and large database clusters based on the more flexible MySQL.

Related Market Products

MySQL5.6 (Windows2008)

Websoft9 MySQL is a pre-configured, ready to run image for running MySQL on Alibaba Cloud.MySQL is the world's most popular open source database.

MySQL5.5 (CentOS7.4)

Websoft9 MySQL is a pre-configured, ready to run image for running MySQL on Alibaba Cloud.MySQL is the world's most popular open source database.

Related Documentation

Migrate RDS for MySQL data to a local MySQL database

RDS for MySQL supports migration of cloud data to local databases using physical and logical backup files.

Due to software restrictions, data recovery is supported only in Linux currently. If you want to recover data to Windows, first you need recover data to Linux and then migrate the data to Windows

Migrate data from on-premises MySQL to RDS for MySQL

DTS can migrate data from on-premises MySQL databases to RDS for MySQL databases without server outage. This document describes how to configure a data migration task from on-premises MySQL to RDS for MySQL.

Related Products

ApsaraDB RDS for MySQL

MySQL is one of the most popular open-source databases in the world. As a key component of the open-source software bundle LAMP (Linux, Apache, MySQL, and Perl/PHP/Python), MySQL has been widely applied to different scenarios.

Data Transmission Service

Data Transmission Service (DTS) helps you migrate data between data storage types, such as relational database, NoSQL, and OLAP. The service supports homogeneous migrations as well as heterogeneous migration between different data storage types.

Related Course

Migration of Cloud Database from Third-party Platforms to Alibaba Cloud

This course aims to help users and engineers who want to migrate databases from third-party cloud platforms to Alibaba Cloud. By learning this course, you can get a comprehensive understanding of what cross-platform database migration is, the DTS, a common tool of database migration on Alibaba Cloud, and the scenarios for migrating cloud databases from third-party cloud platforms to Alibaba Cloud ApsaraDB for RDS, so as to provide a reference for database migration across cloud platforms.

0 0 0
Share on

Alibaba Clouder

1,779 posts | 321 followers

You may also like

Comments