mysql backup tool innobackupex data migration and recovery

Applicable scenarios:

The data volume is relatively large, usually measured in G. Regular SQL import/export of large data is prone to errors. To migrate mysql from other places to our company's elastic cloud server, you can consider using innobackupex for backup and migration.

Prerequisites:

A linux system server; the original database server supports ssh login and has control and management permissions. Both the original database server and the current server need innobackupex installed.

Note:

The following examples come from the internet. The paths are all examples; the actual paths shall prevail. Please fully understand and have relevant backups before operating.

Installation:

wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.21/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.21-1.el7.x86_64.rpm
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL  #Install dependency packages
rpm -ivh percona-xtrabackup-24-2.4.21-1.el7.x86_64.rpm --nodeps --force

Common options for innobackupex:  

   --host     Specify host

   --user     Specify username

   --password    Specify password

   --port     Specify port

   --databases     Specify database

   --incremental    Create incremental backup

   --incremental-basedir   Specify the directory containing the full backup

   --incremental-dir      Specify the directory containing the incremental backup, incremental backup directory   

   --apply-log        Perform preprocessing operations on the backup. Generally, after the backup is completed, the data cannot yet be used for recovery operations, because the backed-up data may contain uncommitted transactions or transactions that have been committed but not yet synchronized to the data files. Therefore, at this time the data files are still in an inconsistent state. The main function of "prepare" is to roll back uncommitted transactions and synchronize committed transactions to the data files, thereby making the data files consistent.

   --redo-only      Do not roll back uncommitted transactions

   --copy-back     Restore backup directory

Some other commonly used optional parameters:

--lock-wait-query-type   This parameter allows the user to specify which type of SQL statements need to wait for Flush table with read lock

--lock-wait-threshold    Only queries running longer than this time are considered long queries

--lock-wait-timeout     How long to wait for long queries before attempting FTWRL. Once Flush table with read lock is blocked beyond the predetermined time, XtraBackup returns an error and exits. The parameter allows the user to specify that queries exceeding this threshold time will be killed, and also allows the user to specify Kill

Full backup:

innobackupex --user=root --password=xxxx --datadir=/var/lib/mysql  /mysql_bkup/   

#datadir is the mysql data storage path, which can be viewed in my.cnf. mysql_bkup is the backup storage path. By default, the backup directory is named by time.

#Merge data to put the data files into a consistent state:

innobackupex --apply-log   /mysql_bkup/2022-08-03_21-14-37/  

Preparation for cross-machine recovery:

1. Use the scp command to transfer /mysql_bkup/2022-08-03_21-14-37/ to the database on the target new server.

2. Stop the mysql service on the new server. Delete the data under the datadir data storage directory. It is recommended to rename it here, and then create a new one (the datadir path can be viewed in the my.cnf configuration file).

Restore data:

After completing the recovery preparation as above, use the command to restore.

innobackupex --copy-back  /mysql_bkup/2022-08-03_21-14-37/ 

Or:

innobackupex --copy-back --datadir=/var/lib/mysql /mysql_bkup/2022-08-03_21-14-37/

After recovery, remember to use chown to reset the directory permissions, and then restart the mysql service.

If the database migration process is running and data is updated, incremental backup is required.

Incremental backup:

--incremental Create incremental backup

--incremental-basedir Specify the directory containing the full backup

--incremental-dir Specify the directory containing the incremental backup

#Merge the full backup data directory to ensure data consistency

innobackupex --apply-log --redo-only /mysql_bkup/2022-08-03_21-14-37/ 

#Create incremental backup

innobackupex --user=root --password=xxx --incremental /mysql_bkup_add/ --incremental-basedir=/mysql_bkup/2022-08-03_21-14-37/ 

#Merge incremental backup data into the full backup data directory

innobackupex --apply-log  /mysql_bkup/2022-08-03_21-14-37/ --incremental-dir=/mysql_bkup_add/incremental_backup/

#Data consistency

innobackupex --apply-log /mysql_bkup/2022-08-03_21-14-37

#Restore data:

innobackupex --copy-back /mysql_bkup/2022-08-03_21-14-37 

#To restore to a different location, you also need to use scp to migrate to the new server. As described above, stop the service first, delete the data, and then operate.

Incremental backup can be performed multiple times. The following is an example of multiple incremental backups:

#Perform the first full backup

[root@ebs~]#innobackupex --datadir=/var/lib/mysql --user=root --password=xxx ./bkup

#Perform the first incremental backup, based on the previous full backup

[root@ebs ~]#innobackupex --datadir=/var/lib/mysql --user=root --password=xxx --incremental ./bkup/incer1 --incremental-basedir=./bkup/2022-04-23_11-39-31

#Perform the second full backup, based on the first incremental backup

[root@ebs ~]#innobackupex --datadir=/var/lib/mysql --user=root --password=xxx --incremental ./bkup/incer2 --incremental-basedir=./bkup/incer1/2022-04-23_11-47-10

--incremental Create incremental backup

--incremental-basedir Specify the directory containing the full backup

--incremental-dir Specify the directory containing the incremental backup

##########################Data integration during recovery####################################################

#Apply logs to the starting backup set of the integration—the full backup set—and specify "--redo-only" to indicate the beginning of log appending

innobackupex --apply-log --redo-only ./bkup/2022-04-23_11-39-31/

#Perform "prepare" on the first incremental backup set and append it to the full backup set

innobackupex --apply-log --redo-only ./bkup/2022-04-23_11-39-31/ --incremental-dir=./bkup/incer1/2022-04-23_11-47-10/

#Perform "prepare" on the second incremental backup set and append it to the full backup set, but no longer apply "--redo-only", indicating the end point of integration

innobackupex --apply-log ./bkup/2022-04-23_11-39-31/ --incremental-dir=./bkup/incer2/2022-04-23_11-53-19/

#Perform an overall "prepare" on the fully integrated full backup set

innobackupex --apply-log  ./bkup/2022-04-23_11-39-31/

###################Restore data#############################

innobackupex --copy-back --datadir=/var/lib/mysql ./bkup/2022-04-23_11-39-31/ 

#If restoring to a different location, you need to use scp to migrate it over and then execute.

###############Modify directory permissions and start mysql###########

chown mysql:mysql -R /usr/local/mysql/data && systemctl start mysqld

Recovery steps given in the official documentation:

innobackupex --apply-log --redo-only BASE-DIR

innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1

innobackupex --apply-log BASE-DIR --incremental-dir=INCREMENTAL-DIR-2

innobackupex --apply-log BASE-DIR

innobackupex --copy-back BASE-DIR