In this article, you will learn to use xtrabackup to perform hot backup and incremental backup of MySQL innodb tables.
1. MySQL backup method
Needless to say about the importance of database backup, in order to prevent data loss and damage in various ways, the database must be backed up regularly.
Simple Application Server
USD1.00 New User Coupon
* Only 3,000 coupons available.
* Each new user can only get one coupon(except users from distributors).
* The coupon is valid for 30 days from the date of receipt.
First consider the impact on database business during backup
- Hot backup: backup in a state where both read and write operations can be performed
- Warm backup: backup in readable but not writable state
- Cold backup: backup made in a state where neither read or write operations can be performed
Furthermore, if you perform regular backups, and if you perform full backups every time, obviously part of the data is duplicated, which wastes a lot of disk space
- Full backup
- Differential backup: only back up the data that has changed since the last full backup
- Incremental backup: only back up the part of data that has changed since the last full backup or incremental backup
Differential backup is based on the backup data at the moment of full backup.
Incremental backup is based on the backup data at the moment of the last backup as a starting point for backup
Differential backups consume more space than incremental backups, but incremental backups have more restoration steps and need to be restored in order.
Two ways to backup data
- Physical backup: copy data files for backup
- Logical backup: export data from the database to a file
Comparing the two methods, physical backup is more efficient and has a lower impact on database operation; the logical backup method can select more granular backup data.
2. Backup tool-xtrabackup
Logical backup uses mysqldump, which is simple to use and will not be detailed here;
The preferred physical backup method is the xtrabackup open source tool. This tool is developed by percona, which can perform hot backup and incremental backup for tables using Innodb engine. These features are not supported for MyISAM engine.
2.1 Installation
The environment of this article: CentOS7.2, percona-xtrabackup-2.3.2-1
Official download and delivery
The installation is complete, rpm -ql percona-xtrabackup
Check the installation file, the main program is xtrabackup, and there is a scripted packaging tool innobackupex, which is mainly used.
2.2 Backup
Usage:
innobackupex [--defaults-file=] --user= --password= --host= /PATH/TO/BACKUP_DIR
Example:
When the database is running normally, we can back up, allowing remote backup
Authorized least privilege backup
mysql >grant reload,lock tables,replication client on. to'user'@'host';
#Make a full database backup, the tool automatically finds the data path according to the mysql configuration
innobackupex --user=root --password=cutemysql /tmp/backup
When you see the OK! logo, the backup is successfully completed, and the data storage path can also be seen
Pack and compress, copy the data to a safe place!
2.3 Recovery
If the database data is lost one day, fortunately, we have a backup to restore the data calmly
Usage:
innobackupex --apply-log /PATH/TO/BACKUP_DIR #Prepare to restore data
innobackupex --copy-back /PATH/TO/BACKUP_DIR #Officially restore data
Example:
When recovering, the database service is required to be shut down and the operation is performed locally. The database data path is empty
And clearly specify the size of the current database transaction log in /etc/my.cnf
innodb_log_file_size=5242880
First prepare the backup data
$ innobackupex --apply-log /tmp/backup/2016-11-22-11-22-20
See OK! Sign is success
After the data recovery preparation is completed, the data can be officially recovered
$ innobackupex --copy-back /tmp/backup/2016-11-22_11-22-20
After the data has been restored under the database data path and the file owner is changed, the database can be started normally!
$ chown -R mysql.mysql /var/lib/mysql
In the hot backup mode, all transactions generated in the database during the backup process will be recorded by xtrabackup, which is the log sequence number (LSN). After executing the above statement of preparing to restore data, the data in the innodb table has been rolled forward to the moment the backup ends. If you want to restore the data that has changed after the startup backup ends, you need to combine the binary log to further restore
So where do you start to restore the binary log? In the backup data directory, the xtrabackup_binlog_info file has records
2.4 Incremental backup and recovery
2.4.1 Incremental backup
Before incremental backup, a full backup is required. The full backup directory is 2016-11-22_15-59-24
#First incremental backup
innobackupex --user=root --password=cutemysql --incremental ./ --incremental-basedir=./2016-11-22_15-59-24
#The second incremental backup, in order to see the difference, submit a transaction by yourself
innobackupex --user=root --password=cutemysql --incremental ./ --incremental-basedir=./2016-11-22_17-08-42
Check the lsn recorded in the two directories, it can be seen that the increment is recorded by lsn
2.4.2 Incremental recovery
Need to pay attention to during the preparation phase of incremental recovery
- Backup synthesis in chronological order
- When adding incremental data except for the last time, you must bring the --redo-only option
- incremental-dir use absolute path
#Prepare for full data
innobackupex --apply-log --redo-only ./2016-11-22_15-59-24
#Append the first incremental data
innobackupex --apply-log --redo-only ./2016-11-22_15-59-24 --incremental-dir=/tmp/backup/2016-11-22_17-08-42
#Append the second incremental data, also the last time, without --redo-only
innobackupex --apply-log ./2016-11-22_15-59-24 --incremental-dir=/tmp/backup/2016-11-22_17-20-25
#Data recovery
innobackupex --copy-back fullbak
chown -R mysql.mysql /var/lib/mysql
That's it!
2.5 Backup compression
xtrabackup supports stream compression
#Enable stream option compression during backup
innobackupex --stream=tar ./ | gzip -> backup.tar.gz
innobackupex --stream=tar ./ | bzip2 -> backup.tar.bz2
#Pay attention to the -i option when decompressing
tar -xizf backup.tar.gz -C backup
