RDS Data Synchronization, Backup and Restoration
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.
Key Steps to Synchronizing Data from RDS to a Local Database
- Install MySQL on the ECS server.
- 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
- As the local database gets started, you can set the synchronization relationship between the local database and RDS.
- 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.
- 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.
This article mainly introduces the principles of MySQL database backup and restoration, so that you can better understand the ApsaraDB for RDS backup and restoration mechanisms. ApsaraDB for RDS uses mysqldump to logically back up data to the MySQL database. Xtrabackup can be used for a full physical backup at the instance level.
1. Logical Backup Using Mysqldump
Mysqldump is a powerful and important MySQL backup tool that that performs logical backups. It is worthwhile to fully familiarize yourself with the various backup parameters and restoration policies of mysqldump.
2. Restoration from Physical Backup Using Xtrabackup
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.” Xtrabackup can be used to perform incremental or full physical backups for MySQL databases.
3. Database Backup Using innobackupex
innobackupex –user=root -p /home/backup/
Files after the backup:
During the backup, a directory named according to the current data and time will be created under the backup directory to store the backup files.
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.
RDS for MySQL data synchronization mode
RDS for MySQL 5.1 synchronizes data between the primary and standby databases asynchronously. This mode boasts high performance but with a certain possibility of data inconsistency between the primary and standby databases.
RDS for MySQL 5.5 adopts a semi-synchronous mode for data synchronization between the primary and standby databases. This mode decreases the write performance but greatly lowers the possibility of data inconsistency. If you have high requirements on data reliability, such as for finance applications, we recommend RDS for MySQL Version 5.5 or above.
RDS for MySQL 5.6 adopts GTID for data synchronization between the primary and standby databases. This feature guarantees both the performance and data consistency.
Related Market Products
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.
MySQL is a relational database management system, a relational database to store data in different tables, rather than all the data in a large warehouse, which increases the speed and flexibility.
mysqldump can be used to migrate MySQL data. The disadvantage of mysqldump is that the service downtime is long. Use mysqldump if the data volume is small or if a long service downtime is allowed.
This section explains how to use Data Transmission Service (DTS) to quickly create a real-time data synchronization task from an RDS for MySQL instance to an Alibaba Cloud Elasticsearch (ES) instance. DTS uses this synchronization feature to synchronize RDS for MySQL data to ES instances and query data in real time.
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 (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.
The security of the cloud databases are critical, they are directly affecting the security and stable operation of the cloud based applications. Only by understanding the principles, methods, and operation methods of backup and recovery of commonly used cloud databases, the cloud database administrators can better protect them. Through this course, you can not only understand the backup and recovery principles, types and methods of databases on the cloud, but also understand the backup and recovery methods related to Alibaba Cloud RDS.