How to implement XtraBackup installation backup of MySQL
This article mainly introduces MySQL XtraBackup installation backup how to achieve, the content of the article is carefully selected and edited by the author, with a certain pertinence, for your reference significance is still relatively great, the following with the author to understand the MySQL XtraBackup installation backup how to achieve it.
Note: install XtraBackup first
Download address: https://www.percona.com/downloads/XtraBackup/LATEST/
My version here is: percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm
Local installation: yum localinstall percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm
View
OK, installation complete
I. complete and recovery of XtraBackup
1. Create a backup user and authorize
Create user' bkuser'@'localhost' identified by '123456'
Grant reload,lock tables,process,replication client on *. * to 'bkuser'@'localhost'
Flush privileges
two。 Start backup
Innobackupex-user=bkuser--password=123456 / data/backup/
Except for errors when backing up, the reason is that the socket file was not found.
Backup completed, successful!
Backed up files:
3. Backup and recovery
Suppose we delete the previous data file and restore it directly (stop the MySQL service first)
First innobackupex-- apply-log / data/backup/2017-04-18 20-35-51 /
Then innobackupex-- copy-back / data/backup/2017-04-18 20-35-51 /
The restore was successful. Restart MySQL
two. Incremental backup and recovery
1. Full backup, but do not use apply-log first
2. Perform incremental backup on the basis of full backup
Add a record first
Start the first incremental backup innobackupex-user=bkuser-password=123456-socket=/tmp/mysql.sock-incremental / data/backup/db-incre-20170418-1-incremental-basedir=/data/backup/20170418full
Backup succeeded
Insert a piece of data again for a second incremental backup
Innobackupex-user=bkuser-password=123456-socket=/tmp/mysql.sock-no-timestamp-incremental / data/backup/db-incre-20170418-2-incremental-basedir=/data/backup/db-incre-20170418-1
The second incremental backup is complete
3. Restore backup data
1) full recovery for the first time + incremental recovery for the first time (under full backup and first incremental backup)
Innobackupex-apply-log-redo-only / data/backup/20170418full
Innobackupex-apply-log-redo-only / data/backup/20170418full-incremental-dir=/data/backup/db-incre-20170418-1
Innobackupex-apply-log / data/backup/20170418full
Theoretically, the recovered data is eight.
Innobackupex-copy-back / data/backup/20170418full
Similarly, stop MySQL and delete the previous data, create a data folder and authorize
2) restore full and two incremental backups
Innobackupex-apply-log-redo-only / data/backup/20170418full
Innobackupex-apply-log-redo-only / data/backup/20170418full-incremental-dir=/data/backup/db-incre-20170418-1
Innobackupex-apply-log-redo-only / data/backup/20170418full-incremental-dir=/data/backup/db-incre-20170418-2
Innobackupex-apply-log / data/backup/20170418full
Turn off MySQL and delete data
Restore data innobackupex-copy-back / data/backup/20170418full
Restart MySQL to see the data
Incremental backup restore complete!
After reading the above about MySQL XtraBackup installation backup how to achieve, many readers must have some understanding, if you need to get more industry knowledge and information, you can continue to follow our industry information column.
