也就是
–start-position=’298’ –stop-position=’460’ #只恢复某个具体的binlog内容
或则
–stop-posionion=’460’ #恢复460日志位置之前的binlog的内容
1 2
[root@master01 data]# mysqlbinlog -v --start-position='298' --stop-position='460' mysql-bin.000046 | mysql -u root -p Enter password:
Xtrabackup是一个对Mysql做数据备份的工具，支持在线热备份（备份时不影响数据读写）,Xtrabackup有两个主要的工具：xtrabackup、 innobackupex，其中innobackupex已经逐渐被xtrabackup取代
特点：
(1)备份过程快速、可靠；
(2)备份过程不会打断正在执行的事务；
(3)能够基于压缩等功能节约磁盘空间和流量；
(4)自动实现备份检验；
(5)还原速度快
下载地址：
https://www.percona.com/downloads/XtraBackup/LATEST/
安装：
1 2 3 4 5
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/RedHat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm yum -y install libev yum -y install perl-Digest-* rpm -ivh percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
全量备份：
1 2
mkdir -p /data/backups/mysql [root@master01 mysql]# xtrabackup --backup --target-dir=/data/backups/mysql/ -u root -p -P 3306 --host=10.0.0.201
恢复过程中首先要执行prepare，将所有的数据文件都准备到同一个时间点，因为在备份过程中所有的数据文件备份都是在不同的时间点，如果直接启动可能会导致冲突
1 2 3 4 5 6 7
/etc/init.d/mysql stop mv /usr/local/mysql/data /usr/local/mysql/data_bak mkdir /usr/local/mysql/data xtrabackup --prepare --target-dir=/data/backups/mysql xtrabackup --copy-back --target-dir=/data/backups/mysql --datadir=/usr/local/mysql/data chown -R mysql:mysql /usr/local/mysql/data /etc/init.d/mysql start
增量备份：
创建一个初始完整备份
1 2 3
rm -rf /data/backups/mysql/* #完整备份 [root@master01 mysql]# xtrabackup --backup --target-dir=/data/backups/mysql/base -u root -p123456 -P 3306 --host=127.0.0.1
进行模拟数据增加，进行增量备份
1 2 3 4 5 6 7
mysql -u root -p use course insert into students values(11,'aa',1,1,current_timestamp); insert into students values(12,'bb',2,2,current_timestamp); exit xtrabackup --backup --target-dir=/data/backups/mysql/inc1 --incremental-basedir=/data/backups/mysql/base -u root -p123456 --host=127.0.0.1
在加数据，进行第二次增量备份
1 2 3 4 5 6
mysql -u root -p use course; insert into students values(13,'cc',3,3,current_timestamp); insert into students values(14,'dd',4,3,current_timestamp); exit xtrabackup --backup --target-dir=/data/backups/mysql/inc2 --incremental-basedir=/data/backups/mysql/inc1 -u root -p123456 -P 3306 --host=127.0.0.1
恢复第一次插入的数据
1 2 3 4 5 6 7 8 9 10 11
pkill mysql rm -rf /usr/local/mysql/data/ #准备上一次完整备份时间点 xtrabackup --prepare --apply-log-only --target-dir=/data/backups/mysql/base --datadir=/usr/local/mysql/data #准备恢复第一次插入的数据 xtrabackup --prepare --target-dir=/data/backups/mysql/base --incremental-dir=/data/backups/mysql/inc1 --datadir=/usr/local/mysql/data xtrabackup --copy-back --target-dir=/data/backups/mysql/base --datadir=/usr/local/mysql/data chown -R mysql.mysql /usr/local/mysql/data/ /etc/init.d/mysql start mysql -u root -p
恢复第二次插入的数据
1 2 3 4 5 6 7 8 9 10
pkill mysql rm -rf /usr/local/mysql/data xtrabackup --prepare --apply-log-only --target-dir=/data/backups/mysql/base --incrementaldir=/data/backups/mysql/inc1 --datadir=/usr/local/mysql/data xtrabackup --prepare --target-dir=/data/backups/mysql/base --incremental-dir=/data/backups/mysql/inc2 --datadir=/usr/local/mysql/data xtrabackup --copy-back --target-dir=/data/backups/mysql/base --datadir=/usr/local/mysql/data chown -R mysql.mysql /usr/local/mysql/data /etc/init.d/mysql start mysql -u root -p use course; select * from students;
