Xtrabackup installation + (full + incremental + binary log) backup, you deserve to have.
The purpose of this paper is to give force directly and simply.
1. Configure the network yum source (ensure that virtual machines are connected to the Internet)
Rm-rf / etc/yum.repos.d/*
Wget-P / home/backup/ http://mirrors.163.com/.help/CentOS6-Base-163.repo
Mv CentOS6-Base-163.repo / etc/yum.repos.d/CentOS-Base.repo
Cd / etc/yum.repos.d/
Mv CentOS6-Base-163.repo CentOS6-Base.repo
Yum clean all
Yum makecache
2. Install Xtrabackup tools.
Wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/tarball/percona-xtrabackup-2.4.4-Linux-x86_64.tar.gz
Tar zxvf percona-xtrabackup-2.4.4-Linux-x86_64.tar.gz-C / usr/src/
Cd / usr/src/
Cd percona-xtrabackup-2.4.4-Linux-x86_64/
Cp bin/* / usr/bin/
Cd / usr/src/
Wget https://www.percona.com/downloads/percona-toolkit/2.2.19/RPM/percona-toolkit-2.2.19-1.noarch.rpm
Yum install-y perl*
Rpm-ivh percona-toolkit-2.2.19-1.noarch.rpm
3. Full backup + twice incremental backup + binary log backup.
Mkdir-pv / opt/mysqlbackup/ {full,inc,binlog}
Mysql-uroot-p123456
Mysql > createdatabase xingjun
Mysql > createtable shengong (id int (10), name varchar (20))
Mysql > insertinto shengong values (1)
Mysql > select* from shengong
Mysql > exit
Complete:
Innobackupex--user=root-password=123456 / opt/mysqlbackup/full/
Add data again:
Mysql-uroot-p123456
Mysql > usexingjun
Mysql > insertinto shengong values (2)
Mysql > select* from shengong
Mysql > exit
First incremental backup:
Innobackupex--user=root-- password='123456'-- incremental / opt/mysqlbackup/inc/--incremental-basedir=/opt/mysqlbackup/full/2017-07-09 / 10-50-00 /
Note: 2017-07-09 10-50-00 this document is complete.
Mysql-uroot-p123456
Mysql > usexingjun
Mysql > insertinto shengong values (3)
Mysql > select* from shengong
Mysql > exit
Second incremental backup:
Innobackupex--user=root-- password='123456'-- incremental / opt/mysqlbackup/inc/--incremental-basedir=/opt/mysqlbackup/inc/2017-07-09 / 10-50-46 /
Note: 2017-07-09 10-50-46 this file is the first incremental backup.
Mysql-uroot-p123456
Mysql > usexingjun
Mysql > insertinto shengong values (4)
Mysql > select* from shengong
Mysql > exit
Binary file backup:
Cp/usr/local/mysql/data/mysql-bin.000001 / opt/mysqlbackup/binlog/
4. The simulation data is lost.
Servicemysqld stop
Rm-rf / usr/local/mysql/data/*
5. Dangdang, the key point is coming, and recovering the data.
Prepare before recovery:
Innobackupex--apply-log-- redo-only / opt/mysqlbackup/full/2017-07-09 / 10-50-00 /
Note: 2017-07-09 10-50-00 this is complete.
Innobackupex--apply-log-- redo-only / opt/mysqlbackup/full/2017-07-09 / 10-50-00/--incremental-dir=/opt/mysqlbackup/inc/2017-07-09 / 10-50-46 /
Note: 2017-07-09 10-50-46 this is the first incremental backup.
Innobackupex--apply-log-- redo-only / opt/mysqlbackup/full/2017-07-09 / 10-50-00/--incremental-dir=/opt/mysqlbackup/inc/2017-07-09 / 10-54-58 /
Note: 2017-07-09 10-54-58 this is the second incremental backup.
Innobackupex--apply-log / opt/mysqlbackup/full/2017-07-09 / 10-50-00 /
Note: 2017-07-09 10-50-00 this is complete.
Start recovering data:
Innobackupex--copy-back / opt/mysqlbackup/full/2017-07-09 / 10-50-00 /
Note: 2017-07-09 10-50-00 this is complete.
6. Verification.
Chown-R mysql:mysql / usr/local/mysql/data/
Mysql-uroot-p123456
Mysql > usexingjun
Mysql > select* from shengong
Mysql > exit
After checking that there are three pieces of data in the database, complete + two incremental backups are completed.
7. Recover the final data through the binary log.
Mysqlbinlog/usr/local//mysql/data/mysql-bin.000001 > / opt/mysqlbackup/binlog/ `date +% F`.sql
View the binary log information and position information for the second incremental backup:
Cat/opt/mysqlbackup/inc/2017-07-09 10-54-58/xtrabackup_info
Note: the line message in xtrabackup_info is: binlog_pos = filename'mysql-bin.000001', position '1040'
Vi/opt/mysqlbackup/binlog/2017-07-09.sql
Delete from # at # # to # at 1040, including # at 1040.
Note: the following is the 2017-07-09.sql file information.
# at 205
# 17070910:55:53 server id 1 end_log_pos 320 CRC32 0x07b5d487 Query thread_id=2exec_time=1380 error_code=0
Use`xingjun`
SETTIMESTAMP=1499612153
Insertinto shengong values (10d`)
Then save the exit.
Start recovery:
Mysql-uroot-p123456
Mysql > set sql_log_bin=0
Mysql > usexingjun
Mysql > source/opt/mysqlbackup/2017-07-09.sql
Mysql > select* from shengong
Mysql > set sql_log_bin=1
Mysql > exit
After checking that there are four pieces of data in the database, complete + two incremental backups + binary log backups are completed.
