Mysql innobackupex physical backup
I. innobackupex installation
This environment is centos6. Download the installation package at http://www.percona.com/downloads/xtrapackup/
Download and install this experiment
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.4/binary/redhat/6/x86_64/Percona-XtraBackup-2.3.4-re80c779-el6-x86_64-bundle.tar
After downloading, install as follows: root@mysqlslave soft]# rpm -ivh percona-xtrabackup-2.3.4-1.el6.x86_64.rpm
warning: percona-xtrabackup-2.3.4-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
error: Failed dependencies:
libev.so.4()(64bit) is needed by percona-xtrabackup-2.3.4-1.el6.x86_64
Missing dependency package found...
Solution:
yum install libev -y
Install again:
[root@mysqlslave soft]# rpm -ivh percona-xtrabackup-2.3.4-1.el6.x86_64.rpm
warning: percona-xtrabackup-2.3.4-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing... ########################################### [100%]
1:percona-xtrabackup ########################################### [100%]
Install successfully and check version
[root@mysqlslave soft]# innobackupex -version
innobackupex version 2.3.4 Linux (x86_64) (revision id: e80c779)
I. Backup 1.1. Creating a Full Backup Set
innobackupex --defaults-file=/etc/my.cnf --host 10.20.20.13 --port=3306 --user=root --password=123456 --socket=/usr/local/mysql/mysql.sock /data/backup/full
Parameter explanation:
--defaults-file parameter file to start mysql
--host Host name can back up remote servers
--port mysql port number
--user Backup username
--password Backup user password
--socket Launch MySQL socket file
Here is a script for mysql innobackupex to physically backup and backup logs
#!/ bin/bash
cd /data/backup/full/
c=`pwd`
innobackupex --defaults-file=/etc/my.cnf --host 10.20.20.13 --port=3306 --user=root --password=123456 --socket=/usr/local/mysql/mysql.sock $c
a=`echo $?`
echo $a
b=`ls -l | grep '^d'|awk -F ":" '{print$2}'|awk -F " " '{print$2}'`
echo $b
cd $c
if [ $a == 0 ];then
innobackupex --use-memory=500m --apply-log $b
echo -e "\033[32m ################################################ \033[0m"
echo -e "\033[32m #### BackUp Sucessed ##### \033[0m"
echo -e "\033[32m ################################################ \033[0m"
tar -zcvf $b.tar $b
rm -rf $b
else
echo -e "\033[41;37m ############################################# \033[0m"
echo -e "\033[41;37m #### BackUp Failed #### \033[0m"
echo -e "\033[41;37m ############################################# \033[0m"
echo "backup failed"
fi
1.2 incremental backup
An incremental backup also requires a full backup first.
Examples:
For example:
Monday Tuesday Wednesday
full reserve increment increment
Full backup Monday backup Tuesday
Difference from Tuesday Difference from Wednesday
Backup subdirectories need to be created manually.
mkdir /data/backup/{mon,tue,wed}
Full Backup (Monday): A Level 0 backup is required on Monday, i.e. a full backup
innobackupex --defaults-file=/etc/my.cnf --host 10.20.20.13 --user=root --password=123456 /data/backup/mon
Incremental backup (Tuesday): Do an incremental backup on Tuesday that differs from a level 0 backup
innobackupex --defaults-file=/etc/my.cnf --host 10.20.20.13 --user=root --password=123456 --incremental /data/backup/tue/ --incremental-basedir=/data/backup/mon/2016-04-20_19-50-34
Incremental backup (Wednesday): Incremental backup that differs between Wednesday and Tuesday
innobackupex --defaults-file=/etc/my.cnf --host 10.20.20.13 --user=root --password=123456 --incremental /data/backup/wed/ --incremental-basedir=/data/backup/tue/2016-04-20_19-53-41/
1.3 View backup information
Backup information can be viewed in the following files.
[root@drbd-01 backup]# cat /data/backup/full/2015-07-28_12-10-45/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 3194902
last_lsn = 3194902
compact = 0
[root@drbd-01 backup]# cat /data/backup/001/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 3194902
to_lsn = 3198776
last_lsn = 3198776
compact = 0
[root@drbd-01 backup]# cat /data/backup/002/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 3198776
to_lsn = 3202726
last_lsn = 3202726
compact = 0
As you can see above, the three backup set relationships can be seen through the lsn.
second restoring
Delete the old mysql data directory/data/mysql and create a new database directory to simulate database corruption.
[root@drbd-01 data]#rm -rf /data/mysql
[root@drbd-01 data]# cd /data/
[root@drbd-01 data]# mkdir mysql
[root@drbd-01 data]# chown mysql.mysql mysql
Complete recovery;
perform a restore operation
[root@drbd-01 ~]# innobackupex --defaults-file=/etc/my.cnf --copy-back /data/backup/full/2015-07-28_12-10-45/
Modify permissions and owners:
chown -R mysql:mysql /data/mysql
Incremental recovery:
1.1 Prepare full backup set (application log)[root@mysqlslave mysql]#innobackupex --defaults-file=/etc/my.cnf --apply-log --redo-only /data/backup/yi/2016-04-20_18-12-50 --use-memory=500m --user=root --password=123456
2.2 Prepare incremental backup set 2.2.1 Merge first incremental backup into full backup Tuesday incremental backup appended to Monday backup (Level 0 backup)
[root@mysqlslave mysql]# innobackupex --defaults-file=/etc/my.cnf --apply-log --redo-only /data/backup/yi/2016-04-20_19-50-34/ --incremental-dir=/data/backup/er/2016-04-20_19-53-41/ --user=root --password=123456
Wednesday incremental backup appended to Monday backup (Level 0 backup)
[root@mysqlslave mysql]# innobackupex --defaults-file=/etc/my.cnf --apply-log --redo-only /data/backup/yi/2016-04-20_19-50-34/ --incremental-dir=/data/backup/san/2016-04-20_19-53-55/ --user=root --password=123456
Append the entire backup to Monday and prepare Monday's backup
[root@mysqlslave mysql]# innobackupex --apply-log /data/backup/yi/2016-04-20_19-50-34/ --use-memory=500m --user=root --password=123456
Recovery operation:
The final recovery operation is the same as completing the recovery
[root@mysqlslave mysql]# innobackupex --defaults-file=/etc/my.cnf --copy-back /data/backup/yi/2016-04-20_19-50-34
Modify permissions:
chown mysql:mysql -R *
Verify data recovery
