Database Backup and Restoration Solutions

Alibaba Cloud ApsaraDB is an on-demand database service that helps you cope with constantly changing business requirements, and frees you up from database administrative and management tasks.

Hive

Abstract

As your business progresses, database requirements will inevitably change and grow. Alibaba Cloud ApsaraDB is an on-demand database service that helps you cope with constantly changing business requirements, and frees you up from database administrative and management tasks. In addition to its flexibility, Alibaba Cloud ApsaraDB supports convenient back up and restoration to ensure stable business operations. ApsaraDB provides a stable and efficient data backup mechanism, requiring as little as one second for a single operation. In the next section, I will summarize how to use the restoration tools of ApsaraDB for RDS, in hopes of providing some guidance to those who run into similar issues. Because this tutorial is based on my own practical experience, the actual implementation may vary depending on your device and operating environment.

Operating environment

Operating system OS X 10.10.4
Server environment: CentOS 6.5
Local database environment: Server version: 5.6.21-log MySQL Community Server (GPL)
Production database environment: MySQL5.5

Tools used

Terminal tool: iTerm (Mac)
Data compression and extract tool: rds_backup_extract (applicable to RDS only)
Database restoration tool: Percona XtraBackup 2.2 Release 2.2.12

Procedure

1.Download the RDS official extract tool kit: rds_backup_extract.

An RDS backup file is a compressed package. It must be extracted first. Download the extract tool here rds_backup_extract (sh file). Currently, the tool kit only supports 64-bit Linux systems.

bash rds_backup_extract -f backup.tar.gz/back.xb.gz -C directory

2.Install the backup restoration tool: Percona XtraBackup 2.2.

Download the official Percona-XtraBackup Download address: https://www.percona.com/downloads/XtraBackup/ . The official website offers installers for various platforms. RDS backup uses Version 2.2.9, so we recommend you download Version 2.2.9 or a later version. For more information about the working principles of the tool, refer to the official documentation.

3.Extract backup files

Extract the backup files by following the instructions in the help documentation:

bash rds_backup_extract -f 
/home/mysql/backup/hins575175_xtra_20150429091224.tar.gz -C 
/home/mysql/data

4.Restore data using the backup

Install Percona-XtraBackup before extracting the backup file. Run the following command:
innobackupex --defaults-file=/home/mysql/data/backup-my.cnf --apply-log /home/mysql/data
This step will output the following. If it displays "completed OK", the operations was executed successfully.

5.Configure the environment

Due to version issues, comment out innodb_fast_checksum, innodb_page_size and innodb_log_block_size in backup-my.cnf in the extracted files, as shown in the figure below:

6.Modify the file owner

Modify the file owner and confirm that the file belongs to the mysql user.
chown -R mysql:mysql /home/mysql/data

7.Start the mysqld process and log on for verification

Start the mysqld process and verify that the startup is successful.

mysqld_safe --defaults-file=/home/mysql/data/backup-my.cnf --user=mysql --datadir=/home/mysql/data &

06_1

06_2

Note:

After the restoration, the mysql.user table does not contain users created in the RDS. You need to create new users.
Prior to creating new users, execute the following SQL statements:

delete from mysql.db where user<>'root' and char_length(user)>0;
delete from mysql.tables_priv where user<>'root' and char_length(user)>0;
flush privileges;

That's it! You have successfully restored your ApsaraDB for RDS database.

Community

Database Backup and Restoration Solutions

Abstract

Operating environment

Tools used

Procedure

Read previous post:

Read next post:

Alibaba Clouder

You may also like

Comments

Alibaba Clouder

Related Products

Database Security Solutions

Oracle Database Migration Solution

Database Migration Solution

ADAM(Advanced Database & Application Migration)