Mysql备份系列(4)--lvm-snapshot备份mysql数据(全量+增量)操作记录

时间:2021-08-29 16:01:11

Mysql最常用的三种备份工具分别是mysqldump、Xtrabackup(innobackupex工具)、lvm-snapshot快照。
前面分别介绍了:
Mysql备份系列(1)--备份方案总结性梳理
Mysql备份系列(2)--mysqldump备份(全量+增量)方案操作记录
Mysql备份系列(3)--innobackupex备份mysql大数据(全量+增量)操作记录

lvm-snapshot:基于LVM快照的备份
1.关于快照:
1)事务日志跟数据文件必须在同一个卷上;
2)刚刚创立的快照卷,里面没有任何数据,所有数据均来源于原卷
3)一旦原卷数据发生修改,修改的数据将复制到快照卷中,此时访问数据一部分来自于快照卷,一部分来自于原卷
4)当快照使用过程中,如果修改的数据量大于快照卷容量,则会导致快照卷崩溃。
5)快照卷本身不是备份,只是提供一个时间一致性的访问目录。

2.基于快照备份几乎为热备:
1)创建快照卷之前,要请求MySQL的全局锁;在快照创建完成之后释放锁;
2)如果是Inoodb引擎, 当flush tables 后会有一部分保存在事务日志中,却不在文件中。 因此恢复时候,需要事务日志和数据文件
但释放锁以后,事务日志的内容会同步数据文件中,因此备份内容并不绝对是锁释放时刻的内容,由于有些为完成的事务已经完成,但在备份数据中因为没完成而回滚。 因此需要借助二进制日志往后走一段

3.基于快照备份注意事项:
1)事务日志跟数据文件必须在同一个卷上;
2)创建快照卷之前,要请求MySQL的全局锁;在快照创建完成之后释放锁;
3)请求全局锁完成之后,做一次日志滚动;做二进制日志文件及位置标记(手动进行);

4.为什么基于MySQL快照的备份很好?
原因如下几点:
1)几乎是热备 在大多数情况下,可以在应用程序仍在运行的时候执行备份。无需关机,只需设置为只读或者类似只读的限制。
2)支持所有基于本地磁盘的存储引擎 它支持MyISAM, Innodb, BDB,还支持 Solid, PrimeXT 和 Falcon。
3)快速备份 只需拷贝二进制格式的文件,在速度方面无以匹敌。
4)低开销 只是文件拷贝,因此对服务器的开销很细微。
5)容易保持完整性 想要压缩备份文件吗?把它们备份到磁带上,FTP或者网络备份软件 -- 十分简单,因为只需要拷贝文件即可。
6)快速恢复 恢复的时间和标准的MySQL崩溃恢复或数据拷贝回去那么快,甚至可能更快,将来会更快。
7)免费 无需额外的商业软件,只需Innodb热备工具来执行备份。

快照备份mysql的缺点:
1)需要兼容快照 -- 这是明显的。
2)需要超级用户(root) 在某些组织,DBA和系统管理员来自不同部门不同的人,因此权限各不一样。
3)停工时间无法预计,这个方法通常指热备,但是谁也无法预料到底是不是热备 -- FLUSH TABLES WITH READ LOCK 可能会需要执行很长时间才能完成。
4)多卷上的数据问题 如果你把日志放在独立的设备上或者你的数据库分布在多个卷上,这就比较麻烦了,因为无法得到全部数据库的一致性快照。不过有些系统可能能自动做到多卷快照。

下面即是使用lvm-snapshot快照方式备份mysql的操作记录,仅依据本人实验中使用而述.

操作记录:
如下环境,本机是在openstack上开的云主机,在openstack上创建一个30G的云硬盘挂载到本机,然后制作lvm逻辑卷。

一、准备LVM卷,并将mysql数据恢复(或者说迁移)到LVM卷上:
1) 创建一个分区或保存到另一块硬盘上面
2) 创建PV、VG、LVM
3) 格式化 LV0
4) 挂载LV到临时目录
5) 确认服务处于stop状态
6) 将数据迁移到LV0
7) 重新挂载LV0到mysql数据库的主目录/data/mysql/data
8) 审核权限并启动服务
[root@test-huanqiu ~]# fdisk -l
.........
Disk /dev/vdc: 32.2 GB, 32212254720 bytes
16 heads, 63 sectors/track, 62415 cylinders
Units = cylinders of 1008 * 512 = 516096 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000

[root@test-huanqiu ~]# fdisk /dev/vdc                            //依次输入p->n->p->1->回车->回车->w
.........
Command (m for help): p

Disk /dev/vdc: 32.2 GB, 32212254720 bytes
16 heads, 63 sectors/track, 62415 cylinders
Units = cylinders of 1008 * 512 = 516096 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x343250e4

Device Boot Start End Blocks Id System

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-62415, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-62415, default 62415):
Using default value 62415

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

[root@test-huanqiu ~]# fdisk /dev/vdc

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
switch off the mode (command 'c') and change display units to
sectors (command 'u').

Command (m for help): p

Disk /dev/vdc: 32.2 GB, 32212254720 bytes
16 heads, 63 sectors/track, 62415 cylinders
Units = cylinders of 1008 * 512 = 516096 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x343250e4

Device Boot Start End Blocks Id System
/dev/vdc1 1 62415 31457128+ 5 Extended

Command (m for help):

[root@test-huanqiu ~]# pvcreate /dev/vdc1
Device /dev/vdc1 not found (or ignored by filtering).
[root@test-huanqiu ~]# vgcreate vg0 /dev/vdc1
Volume group "vg0" successfully created
[root@test-huanqiu ~]# lvcreate -L +3G -n lv0 vg0
Logical volume "lv0" created.
[root@test-huanqiu ~]# mkfs.ext4 /dev/vg0/lv0
[root@test-huanqiu ~]# mkdir /var/lv0/
[root@test-huanqiu ~]# mount /dev/vg0/lv0 /var/lv0/
[root@test-huanqiu ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
8.1G 6.0G 1.7G 79% /
tmpfs 1.9G 0 1.9G 0% /dev/shm
/dev/vda1 190M 37M 143M 21% /boot
/dev/mapper/vg0-lv0 2.9G 4.5M 2.8G 1% /var/lv0

[root@test-huanqiu ~]# lvs
LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert
LogVol00 VolGroup00 -wi-ao---- 8.28g
LogVol01 VolGroup00 -wi-ao---- 1.50g
lv0 vg0 -wi-a----- 3.00g

----------------------------------------------------------------------------------------------------
如果要想删除这个lvs,操作如下:
[root@test-huanqiu ~]# umount /data/mysql/data/            //先卸载掉这个lvs的挂载关系
[root@test-huanqiu ~]# lvremove /dev/vg0/lv0
[root@test-huanqiu ~]# vgremove vg0
[root@test-huanqiu ~]# pvremove /dev/vdc1
[root@test-huanqiu ~]# lvs
LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert
LogVol00 VolGroup00 -wi-ao---- 8.28g
LogVol01 VolGroup00 -wi-ao---- 1.50g
----------------------------------------------------------------------------------------------------

mysql的数据目录是/data/mysql/data,密码是123456
[root@test-huanqiu ~]# ps -ef|grep mysql
mysql 2066 1286 0 07:33 ? 00:00:06 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql/ --datadir=/data/mysql/data --plugin-dir=/usr/local/mysql//lib/plugin --user=mysql --log-error=/data/mysql/data/mysql-error.log --pid-file=/data/mysql/data/mysql.pid --socket=/usr/local/mysql/var/mysql.sock --port=3306
root 2523 2471 0 07:55 pts/1 00:00:00 grep mysql
[root@test-huanqiu ~]# /etc/init.d/mysql stop
Shutting down MySQL.... SUCCESS!

[root@test-huanqiu ~]# cd /data/mysql/data/
[root@test-huanqiu data]# tar -cf - . | tar xf - -C /var/lv0/

[root@test-huanqiu data]# umount /var/lv0/

[root@test-huanqiu data]# mount /dev/vg0/lv0 /data/mysql/data
[root@test-huanqiu data]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
8.1G 6.0G 1.7G 79% /
tmpfs 1.9G 0 1.9G 0% /dev/shm
/dev/vda1 190M 37M 143M 21% /boot
/dev/mapper/vg0-lv0 2.9G 164M 2.6G 6% /data/mysql/data

删除挂载后产生的lost+found目录
[root@test-huanqiu data]# rm -rf lost+found

[root@test-huanqiu data]# ll -d /data/mysql/data
[root@test-huanqiu data]# ll -Z /data/mysql/data
[root@test-huanqiu data]# ll -Zd /data/mysql/data

需要注意的是:
当SElinux功能开启情况下,mysql数据库重启会失败,所以必须执行下面命令,恢复SElinux安全上下文.
[root@test-huanqiu data]# restorecon -R /data/mysql/data/
[root@test-huanqiu data]# /etc/init.d/mysql start
Starting MySQL... SUCCESS!

二、备份: (生产环境下一般都是整个数据库备份)
1)锁表
2)查看position号并记录,便于后期恢复
3)创建snapshot快照
4)解表
5)挂载snapshot
6)拷贝snapshot数据,进行备份。备份整个数据库之前,要关闭mysql服务(保护ibdata1文件)
7)移除快照

设置此变量为1,让每个事件尽可能同步到二进制日志文件里,以消耗IO来尽可能确保数据一致性。
mysql> SET GLOBAL sync_binlog=1;

查看二进制日志和position,以备后续进行binlog日志恢复增量数据(记住这个position节点记录,对后面的增量数据备份很重要) 
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 1434 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

创建存放binlog日志的position节点记录的目录
所有的position节点记录都放在这同一个binlog.pos文件下(后面就使用>>符号追加到这个文件下)
[root@test-huanqiu ~]# mkdir /backup/mysql/binlog
[root@test-huanqiu ~]# mysql -p123456 -e "SHOW MASTER STATUS;" > /backup/mysql/binlog/binlog.pos
[root@test-huanqiu snap1]# cat /backup/mysql/binlog/binlog.pos
File Position Binlog_Do_DB Binlog_Ignore_DB Executed_Gtid_Set
mysql-bin.000004 1434

刷新日志,产生新的binlog日志,保证日志信息不会再写入到上面的mysql-bin.000004日志内。
mysql> FLUSH LOGS;

全局读锁,读锁请求到后不要关闭此mysql交互界面
mysql> FLUSH TABLES WITH READ LOCK;

在innodb表中,即使是请求到了读锁,但InnoDB在后台依然可能会有事务在进行读写操作,
可用"mysql> SHOW ENGINE INNODB STATUS;"查看后台进程的状态,等没有写请求后再做备份。

创建快照,以只读的方式(--permission r)创建一个3GB大小的快照卷snap1
-s:相当于--snapshot
[root@test-huanqiu ~]# mkdir /var/snap1
[root@test-huanqiu ~]# lvcreate -s -L 2G -n snap1 /dev/vg0/lv0 --permission r
Logical volume "snap1" created.

查看快照卷的详情(快照卷也是LV):
[root@test-huanqiu ~]# lvdisplay

解除锁定
回到锁定表的mysql交互式界面,解锁:
mysql> UNLOCK TABLES;

此参数可以根据服务器磁盘IO的负载来调整
mysql> SET GLOBAL sync_binlog=0;

[root@test-huanqiu ~]# mount /dev/vg0/snap1 /var/snap1                //挂载快照卷
[root@test-huanqiu snap1]# df -h
Filesystem             Size   Used  Avail  Use%  Mounted on
/dev/mapper/VolGroup00-LogVol00
                            8.1G  5.8G  1.9G  76%    /
tmpfs                    1.9G  0       1.9G  0%      /dev/shm
/dev/vda1              190M 37M   143M 21%    /boot
/dev/mapper/vg0-lv0 2.9G 115M 2.7G 5% /data/mysql/data
/dev/mapper/vg0-snap1
                               2.9G 115M 2.7G 5% /var/snap1

[root@test-huanqiu ~]# cd /var/snap1/ && ll /var/snap1
[root@test-huanqiu snap1]# mkdir -p /backup/mysql/data/               //创建备份目录
total 0

对本机的数据库进行备份,备份整个数据库。

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.01 sec) mysql> create database beijing;
Query OK, 1 row affected (0.00 sec) mysql> use beijing;
Database changed mysql> create table people(id int(5),name varchar(20));
Query OK, 0 rows affected (0.03 sec) mysql> insert into people values("1","wangshibo");
Query OK, 1 row affected (0.00 sec) mysql> insert into people values("2","guohuihui");
Query OK, 1 row affected (0.01 sec) mysql> insert into people values("3","wuxiang");
Query OK, 1 row affected (0.01 sec) mysql> select * from people;
+------+-----------+
| id | name |
+------+-----------+
| 1 | wangshibo |
| 2 | guohuihui |
| 3 | wuxiang |
+------+-----------+
3 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| beijing |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.01 sec)

--------------------------------------------------------------------------------------------------------------------------
需要注意的是:
innodb表,一般会打开独立表空间模式(innodb_file_per_table)。
由于InnoDB默认会将所有的数据库InnoDB引擎的表数据存储在一个共享空间中:ibdata1文件。
增删数据库的时候,ibdata1文件不会自动收缩,这对单个或部分数据库的备份也将成为问题(如果不是整个数据库备份的情况下,ibdata1文件就不能备份,否则会影响全部数据库的数据)。
所以若是对单个数据库或部分数据库进行快照备份:
1)若是直接误删除mysql数据目录下备份库目录,可以直接将快照备份数据解压就能恢复
2)若是使用drop或delete误删除的数据,那么在使用快照备份数据恢复时,就会出问题!因为单库备份时ibdata1文件不能单独备份,恢复时会导致这个文件损坏!

所以正确的做法是:
要对整个数据库进行备份,并且一定要在mysql服务关闭的情况下(这样是为了保护ibdata1文件)。
因为mysql是采用缓冲方式来将数据写入到ibdata1文件中的,这正是fflush()函数存在的理由。当mysql在运行时,对ibdata1进行拷贝肯定会导致ibdata1文件中的数据出错,这样在数据恢复时,也就肯定会出现“ERROR 1146 (42S02): Table '****' doesn't exist“的报错!

在对启用innodb引擎的mysql数据库进行迁移的时候也是同理:
在对innodb数据库进行数据迁移的时候,即将msyql(innodb引擎)服务从一台服务器迁移到另一台服务器时,在对数据库目录进行整体拷贝的时候(当然就包括了对ibdata1文件拷贝),一定要在关闭对方mysql服务的情况下进行拷贝!

ibdata1用来储存文件的数据,而库名的文件夹里面的那些表文件只是结构而已,由于新版的mysql默认试innodb,所以ibdata1文件默认就存在了,少了这个文件有的数据表就会出错。要知道:数据库目录下的.frm文件是数据库中很多的表的结构描述文件;而ibdata1文件才是数据库的真实数据存放文件。

-------------------------------------------innodb_file_per_table参数说明------------------------------------------
线上环境的话,一般都建议打开这个独立表空间模式。
因为ibdata1文件会不断的增大,不会减少,无法向OS回收空间,容易导致线上出现过大的共享表空间文件,致使当前空间爆满。
并且ibdata1文件大到一定程序会影响insert、update的速度;并且
另外如果删表频繁的话,共享表空间产生的碎片会比较多。打开独立表空间,方便进行innodb表的碎片整理

使用MyISAM表引擎的数据库会分别创建三个文件:表结构、表索引、表数据空间。
可以将某个数据库目录直接迁移到其他数据库也可以正常工作。

然而当使用InnoDB的时候,一切都变了。
InnoDB默认会将所有的数据库InnoDB引擎的表数据存储在一个共享空间中:ibdata1文件。
增删数据库的时候,ibdata1文件不会自动收缩,单个数据库的备份也将成为问题。
通常只能将数据使用mysqldump 导出,然后再导入解决这个问题。

在MySQL的配置文件[mysqld]部分,增加innodb_file_per_table参数。
可以修改InnoDB为独立表空间模式,每个数据库的每个表都会生成一个数据空间。

它的优点:
1)每个表都有自已独立的表空间。
2)每个表的数据和索引都会存在自已的表空间中。
3)可以实现单表在不同的数据库中移动。
4)空间可以回收(除drop table操作处,表空不能自已回收)

Drop table操作自动回收表空间,如果对于统计分析或是日值表,删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。
对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。
对于使用独立表空间的表,不管怎么删除,表空间的碎片不会太严重的影响性能,而且还有机会处理。

它的缺点:
单表增加过大,如超过100个G。

结论:
共享表空间在Insert操作上少有优势。其它都没独立表空间表现好。当启用独立表空间时,请合理调整一下:innodb_open_files。
InnoDB Hot Backup(冷备)的表空间cp不会面对很多无用的copy了。而且利用innodb hot backup及表空间的管理命令可以实。

1)innodb_file_per_table设置.设置为1,表示打开了独立的表空间模式。 如果设置为0,表示关闭独立表空间模式,开启方法如下:
在my.cnf中[mysqld]下设置
innodb_file_per_table=1

2)查看是否开启:
mysql> show variables like "%per_table%";
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)

3)关闭独享表空间
innodb_file_per_table=0关闭独立的表空间
mysql> show variables like ‘%per_table%’;
-------------------------------------------innodb_file_per_table参数说明------------------------------------------
--------------------------------------------------------------------------------------------------------------------------

备份前,一定要关闭mysql数据库!因为里面会涉及到ibdata1文件备份,不关闭mysql的话,ibdata1文件备份后会损坏,从而导致恢复数据失败!
[root@test-huanqiu snap1]# /etc/init.d/mysql stop
Shutting down MySQL.... SUCCESS!
[root@test-huanqiu data]# lsof -i:3306
[root@test-huanqiu data]#

现在备份整个数据库
[root@test-huanqiu snap1]# tar -zvcf /backup/mysql/data/`date +%Y-%m-%d`dbbackup.tar.gz ./
[root@test-huanqiu snap1]# ll /backup/mysql/data/
total 384
-rw-r--r--. 1 root root 392328 Dec 5 22:15 2016-12-05dbbackup.tar.gz

释放快照卷,每次备份之后,应该删除快照,减少IO操作
先卸载,再删除
[root@test-huanqiu ~]# umount /var/snap1/
[root@test-huanqiu ~]# df -h                //确认上面的挂载关系已经没了
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
8.1G 5.8G 1.9G 76% /
tmpfs 1.9G 0 1.9G 0% /dev/shm
/dev/vda1 190M 37M 143M 21% /boot
/dev/mapper/vg0-lv0 2.9G 115M 2.7G 5% /data/mysql/data
[root@test-huanqiu ~]# lvremove /dev/vg0/snap1
Do you really want to remove active logical volume snap1? [y/n]: y
Logical volume "snap1" successfully removed

数据被快照备份后,可以启动数据库
[root@test-huanqiu ~]# /etc/init.d/mysql start
Starting MySQL.. SUCCESS!
[root@test-huanqiu ~]# lsof -i:3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 15943 mysql 16u IPv4 93348 0t0 TCP *:mysql (LISTEN)
[root@test-huanqiu ~]#

现在再进行新的数据写入:

mysql> use beijing;
Database changed
mysql> insert into people values("4","liumengnan");
Query OK, 1 row affected (0.02 sec) mysql> insert into people values("5","zhangjuanjuan");
Query OK, 1 row affected (0.00 sec) mysql> select * from people;
+------+---------------+
| id | name |
+------+---------------+
| 1 | wangshibo |
| 2 | guohuihui |
| 3 | wuxiang |
| 4 | liumengnan |
| 5 | zhangjuanjuan |
+------+---------------+
5 rows in set (0.00 sec) mysql> create table heihei(name varchar(20),age varchar(20));
Query OK, 0 rows affected (0.02 sec) mysql> insert into heihei values("jiujiujiu","nan");
Query OK, 1 row affected (0.00 sec) mysql> select * from heihei;
+-----------+------+
| name | age |
+-----------+------+
| jiujiujiu | nan |
+-----------+------+
1 row in set (0.00 sec) mysql> create database shanghai;
Query OK, 1 row affected (0.01 sec) mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| beijing |
| mysql |
| performance_schema |
| shanghai |
| test |
+--------------------+
6 rows in set (0.00 sec)

假设一不小心误操作删除beijing和shanghai库

mysql> drop database beijing;
Query OK, 2 rows affected (0.03 sec) mysql> drop database shanghai;
Query OK, 0 rows affected (0.00 sec) mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)

莫慌!接下来就说下数据恢复操作~~
三、恢复流程如下:
0)由于涉及到增量数据备份,所以提前将最近一次的binlog日志从mysql数据目录复制到别的路径下
1)在mysql数据库中执行flush logs命令,产生新的binlog日志,让日志信息写入到新的这个binlog日志中
1)关闭数据库,一定要关闭
2)删除数据目录下的文件
3)快照数据拷贝回来,position节点记录回放
4)增量数据就利用mysqlbinlog命令将上面提前拷贝的binlog日志文件导出为sql文件,并剔除其中的drop语句,然后进行恢复。
5)重启数据

先将最新一次的binlog日志备份到别处,用作增量数据备份。
比如mysql-bin.000006是最新一次的binlog日志
[root@test-huanqiu data]# cp mysql-bin.000006 /backup/mysql/data/

产生新的binlog日志,确保日志写入到这个新的binlog日志内,而不再写入到上面备份的binlog日志里。
mysql> flush logs;

[root@test-huanqiu data]# ll mysql-bin.000007
-rw-rw----. 1 mysql mysql 120 Dec 5 23:19 mysql-bin.000007

[root@test-huanqiu data]# /etc/init.d/mysql stop
Shutting down MySQL.... SUCCESS!
[root@test-huanqiu data]# lsof -i:3306
[root@test-huanqiu data]# pwd
/data/mysql/data
[root@test-huanqiu data]# rm -rf ./*
[root@test-huanqiu data]# tar -zvxf /backup/mysql/data/2016-12-05dbbackup.tar.gz ./

[root@test-huanqiu data]# /etc/init.d/mysql start
Starting MySQL SUCCESS!
[root@test-huanqiu data]# cat /backup/mysql/binlog/binlog.pos
File Position Binlog_Do_DB Binlog_Ignore_DB Executed_Gtid_Set
mysql-bin.000004 1434
[root@test-huanqiu data]# mysqlbinlog --start-position=1434 /data/mysql/data/mysql-bin.000004 | mysql -p123456

登陆数据库查看,发现这只是恢复到快照备份阶段的数据:

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| beijing |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec) mysql> select * from beijing.people;
+------+-----------+
| id | name |
+------+-----------+
| 1 | wangshibo |
| 2 | guohuihui |
| 3 | wuxiang |
+------+-----------+
3 rows in set (0.00 sec) mysql>

快照备份之后写入的数据要利用mysqlbinlog命令将上面拷贝的mysql-bin000006文件导出为sql文件,并剔除其中的drop语句,然后进行恢复。
[root@test-huanqiu ~]# cd /backup/mysql/data/
[root@test-huanqiu data]# ll
total 388
-rw-r--r--. 1 root root 392328 Dec 5 22:15 2016-12-05dbbackup.tar.gz
-rw-r-----. 1 root root 1274 Dec 5 23:19 mysql-bin.000006
[root@test-huanqiu data]# mysqlbinlog mysql-bin.000006 >000006bin.sql

剔除其中的drop语句
[root@test-huanqiu data]# vim 000006bin.sql          //手动删除sql语句中的drop语句

然后在mysql中使用source命令恢复数据
mysql> source /backup/mysql/data/000006bin.sql;

再次查看下,发现增量部分的数据也已经恢复回来了

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| beijing |
| mysql |
| performance_schema |
| shanghai |
| test |
+--------------------+
6 rows in set (0.00 sec) mysql> use beijing;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_beijing |
+-------------------+
| heihei |
| people |
+-------------------+
2 rows in set (0.00 sec) mysql> select * from people;
+------+---------------+
| id | name |
+------+---------------+
| 1 | wangshibo |
| 2 | guohuihui |
| 3 | wuxiang |
| 4 | liumengnan |
| 5 | zhangjuanjuan |
+------+---------------+
5 rows in set (0.00 sec) mysql> select * from heihei;
+-----------+------+
| name | age |
+-----------+------+
| jiujiujiu | nan |
+-----------+------+
1 row in set (0.00 sec)

-----------------------------------------------------------------------------------------------------------------
思路:
1)全库的快照备份只需要在开始时备份一份即可,这相当于全量备份。
2)后续只需要每天备份一次最新的binlog日志(备份后立即flush logs产生新的binlog日志),这相当于增量备份了。
3)利用快照备份恢复全量数据,利用备份的binlog日志进行增量数据恢复
4)crontab计划任务,每天定时备份最近一次的binlog日志即可。
----------------------------------------------------------------------------------------------------------------