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. Specifically, we will be exploring how to create physical and logical backups on ApsaraDB for RDS using mysqldump, Percona XtraBackup, and innobackupex.
If you are not familiar with these two types of backups, then you should definitely read up about them, as these two backups can be crucial to maintaining stable business operations. Basically, physical backup is just a physical copy stored in another location by merely copying files. This can be done through a hot backup or a cold backup. Logical backup on the other hand focuses storing data elements, and can be useful when it comes to more granular recovery. Instead of focusing on the differences of these two backups, you should focus on integrating them to provide a more robust backup solution.
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.
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.
Backing up a single database or a specified table in a single database:
mysqldump [OPTIONS] database [tb1] [tb2]…
Backing up multiple databases:
mysqldump [OPTIONS] –databases [OPTIONS] DB1 [DB2 DB3...]
Backing up all databases:
mysqldump [OPTIONS] –all-databases [OPTIONS]
Restoration from Physical Backup Using XtraBackup
XtraBackup is a MySQL database backup tool provided by Percona. XtraBackup can be used to perform incremental or full physical backups for MySQL databases.
According to the official manual, you are recommend to use the innobackupex wrapper script and let innobackupex execute xtrabackup for you. If a mode is not specified at startup, innobackupex will start in backup mode by default.
This script starts xtrabackup with the -suspend-at-end option after which Xtrabackup starts copying the InnoDB data files. When xtrabackup is finished, innobackupex will find that xtrabackup has created the xtrabackupsuspended2 file and then execute the FLUSH TABLES WITH READ LOCK operation. This statement adds read locks to all database tables and then begins copying other types of files.
If -ibbackup is not specified, innobackupex will automatically try to determine the xtrabackup binary to be used. The logic for determining the binary is as follows: first, determine whether the xtrabackup_binary file in the backup directory exists. If it exists, this script will determine the xtrabackup binary to use based on this file. Otherwise, the script will try to connect to the database server and determine the binary based on the server version. If the connection fails, xtrabackup will fail and you need to specify the binary file manually.
After the binary is determined, innobackupex will check whether the connection to the database server can be established. The execution logic is: establish a connection, execute a query, and close the connection. If everything runs normally, xtrabackup will start as a child process.
The FLUSH TABLES WITH READ LOCK statement serves to back up MyISAM and other non-InnoDB tables. This statement is executed after xtrabackup has backed up InnoDB data and log files. After that, the .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV, .par, and .opt files will be backed up.
After all of the above files are backed up, the innobackupex script will resume the execution of xtrabackup and wait for the transaction log files generated during its backup of the above logic. In the next step, tables are unlocked, slave nodes are started, and the connection to the server is disconnected. Then the script will delete the xtrabackupsuspended2 file, allowing the xtrabackup process to exit.
Database Backup Using innobackupex
Full backup:
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.
Descriptions of files:
(1) backup-my.cnf — Options and information for configurations used during backup
(2) ibdata — The tablespace files backed up
(3) xtrabackup_binary — The xtrabackup executable file used in backup
(4) xtrabackup_binlog_info — The binary log file that the MySQL server is currently using and the location of the binary log event as of the moment of the backup
(5) xtrabackup_checkpoints — Backup type (such as full or incremental), backup status (whether it is already in the prepared state), and LSN (log sequence number) range information
(6) xtrabackup_logfile — The redo log file for the backup
When using innobackupex for backup, you can also use the -no-timestamp option to block the command from automatically creating a directory named by time. This way the innobackupex command will create a BACKUP-DIR directory to store the backup data.
Preparing a Full Backup
In general, the data cannot be used for restoration operations yet upon the completion of the backup, because the backup data may contain transactions that have not yet been committed or transactions that have been committed but are not yet synchronized to the data file. Therefore, data files are still inconsistent at this moment. The main role of "prepare" is to make data files consistent by rolling back uncommitted transactions and synchronizing committed transactions to data files.
The above function can be executed using the -apply-log option for the innobackupex command.
innobackupex –apply-log /home/backup/2014-05-03_17-21-11/
The result of a successful execution is shown below:
In the preparation process, innobackupex usually also uses the -use-memory option to specify the size of the memory available. The default value is usually 100 MB. If there is enough memory available, you can allocate more memory to the prepare process to speed it up.
Note: The innobackupex program is a symlink to the xtrabackup C program. It is now deprecated and will be removed in next major release. Syntax for new features will not be added to the innobackupex, only to the xtrabackup.
XtraBackup on ApsaraDB for RDS
Alibaba Cloud ApsaraDB for RDS (Relational Database Service) is a stable and reliable online database service that supports MySQL, SQL Server, and PostgreSQL engines. To learn more about database restoration solutions, check out this blog article.
