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

2022-09-24 07:37:13

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

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

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日志即可。
----------------------------------------------------------------------------------------------------------------

码农公寓

相关文章