|
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
对本机的数据库进行备份，备份整个数据库。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | 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 ~]#
现在再进行新的数据写入：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | 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库
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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
登陆数据库查看，发现这只是恢复到快照备份阶段的数据：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | 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;
再次查看下，发现增量部分的数据也已经恢复回来了
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | 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日志即可。
----------------------------------------------------------------------------------------------------------------
|
