Implementing Effective MySQL Database Backup Mechanisms

Data reliability is crucial for the stability of business systems. Despite the advancements in data reliability technologies, enterprises should not ignore the importance of backing up data. Alibaba Cloud ApsaraDB for RDS for MySQL helps you easily configure and implement effective backup mechanism with restoration capability. This document covers a variety of ways for you to synchronize data, focusing on restoring data from ApsaraDB for RDS for MySQL to a local self-built database.

1.If there are no add, delete and edit operations to data on the RDS, you can generate a physical backup or logical backup, and then import the physical backup or logical backup data to your self-built database through Xtrabackup or mysqldump.

2.If there are add, delete and edit operations to the database after the physical backup has been generated, you need to append the subsequent binlog file after restoring the physical backup. You can get the binlog file through the API/console to append the incremental data.

Note that apart from the data and index files, the extracted physical backup files also include the following files shown in the figure below.

Since binlog is applied on the physical backup, you need to record the position (pos point) of the binlog. All RDS databases use the primary-secondary architecture to ensure high availability. To ensure that the primary database can continue providing external services, the backup operations are performed on the secondary databases. However, the binlog files may be generated on either the primary database or the secondary database (the pos points of the two binlog files are inconsistent). Either one of them may be provided to the user. Therefore, two pos points of binlog files are provided in the physical backup file, as shown in the figure below:

You should test both pos points before applying the binlog. Be sure to use the pos point that can be found in the binlog.

3.You can use the data subscription feature of Data Transmission Service if you want to synchronize the incremental data on Apsara RDS to the binlog of the self-built database. You can consume incremental data through the SDK and then apply the data on your own. The operation interface is shown below. In the example below, you can consume the incremental data from 2015-9-18 16:38:11 to 2015-09-19 17:37:44.