MYSLQ 增量恢复学习及实践

MYSQL的架构一般都是一主多从 或是双主高可用模式，物理故障不需要增量恢复

什么情况需要增量恢复？

一般是由人为引起的误操作才需要增量恢复。

增量恢复的必需要满足的条件

1）开启MYSQL log-bin 日志功能

2）存在一份全备加上全备之后的时刻到出问题时刻的所有增量binlog 文件备份。

增量恢复的思路：

先恢复全量，然后把全备时刻点以后的增量日志，按顺序恢复成SQL文件，然后把文件中有问题的SQL语句删除（也可通过时间和位置点），再恢复到数据库。

下面模拟认为误操作删除数据库后 通过增量恢复的过程

创建一个测试库 WWW 和 test  测试表

create database www character set utf8 collate utf8_general_ci;use www;CREATE TABLE `test` (  `id` int(4) NOT NULL,  `name` varchar(16) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

插入数据

insert test values(1,'xiaoming'),(2,'xiaozhang'),(3,'www'),(4,'koala');

select * from test;+----+-----------+| id | name      |+----+-----------+|  1 | xiaoming  ||  2 | xiaozhang ||  3 | www       ||  4 | koala     |+----+-----------+4 rows in set (0.00 sec)

 此时是全备时表里的数据

修改系统时间  到0点 刷新BINLOG后对www库进行全备

mysqldump -uroot -p123456 -S /data/3306/mysql.sock -B -F -x -R --master-data=2 www|gzip >/server/backup/www_$(date +%F).sql.gz

查看是否生产全备文件

[root@db03 backup]# lltotal 8drwxr-xr-x 2 root root 4096 Jul 13 02:18 tp-rw-r--r-- 1 root root  878 Jul 15 00:01 www_2016-07-15.sql.gz

将系统时间调整到10点后 再插入几条数据

mysql> insert test values(5,'hahaha'),(6,'changsha'),(7,'bbs');Query OK, 3 rows affected (0.00 sec)Records: 3  Duplicates: 0  Warnings: 0

此时test表数据为

mysql> select * from test;+----+-----------+| id | name      |+----+-----------+|  1 | xiaoming  ||  2 | xiaozhang ||  3 | www       ||  4 | koala     ||  5 | hahaha    ||  6 | changsha  ||  7 | bbs       |+----+-----------+7 rows in set (0.00 sec)

开始模拟人为误操作将www 库删除

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || blog               || mysql              || oldboy             || oldboy_gbk         || oldgril            || performance_schema || xiaowan            |+--------------------+8 rows in set (0.00 sec)

进行恢复操作

1、首先查看BINLOG位置 将全备之后的binlog 文件全部备份出来 

如果不先备份 全备恢复后 又会写入LOG BIN 文件

-rw-r--r-- 1 root root  878 Jul 15 00:01 www_2016-07-15.sql.gz[root@db03 backup]# gzip -d www_2016-07-15.sql.gz [root@db03 backup]# grep CHANGE www_2016-07-15.sql-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000137', MASTER_LOG_POS=107;-rw-r----- 1 root root  150 Jul 15 00:01 mysql-bin.000135-rw-r----- 1 root root  150 Jul 15 10:10 mysql-bin.000136-rw-r----- 1 root root  397 Jul 15 10:09 mysql-bin.000137

2、对bin-log进行解析 找出误操作的那条语句 将该条语句进行删除

mysqlbinlog -d www mysql-bin.000135 mysql-bin.000136 mysql-bin.000137 >>wwwbin.sql

[root@db03 backup]# vim wwwbin.sql 

# at 318

#160715 10:06:12 server id 1  end_log_pos 397   Query   thread_id=17    exec_time=0     error_code=0

SET TIMESTAMP=1468548372/*!*/;

drop database www    #如不删除恢复的时候误操作会被一同恢复到数据库中 那就又回到原点白忙活了！

/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

3、恢复全备数据

[root@db03 backup]# mysql -uroot -p123456 -S /data/3306/mysql.sock <www_2016-07-13.sql

查看一库，到全备之前的数据已经恢复了

[root@db03 backup]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "use www;select * from test;"+----+-----------+| id | name      |+----+-----------+|  1 | xiaoming  ||  2 | xiaozhang ||  3 | www       ||  4 | koala     |+----+-----------+

4、恢复增量数据 也就是通过bin-log 文件恢复 0点 到出问题之前的数据

将 解析成SQL文件的bin-log 导入数据库

[root@db03 backup]# mysql -uroot -p123456 -S /data/3306/mysql.sock www<wwwbin.sql

查看表 到误操作之前的数据都找回来了。

[root@db03 backup]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "use www;select * from test;"+----+-----------+| id | name      |+----+-----------+|  1 | xiaoming  ||  2 | xiaozhang ||  3 | www       ||  4 | koala     ||  5 | hahaha    ||  6 | changsha  ||  7 | bbs       |+----+-----------+

要注意的问题

1、数据库里如有多个库  -d 指定库

mysqlbinlog -d www mysql-bin.000135 mysql-bin.000136 mysql-bin.000137 >>wwwbin.sql

2、如果是重要的库出问题，那么最好停库或禁止库被应用服务写入，然后在恢复（iptables处理）。如果通过host解析的，注释解析文件记录，用户中心（接口停掉）。

3、多个binlog 文件要按顺序恢复。

mysqlbinlog -d www 01 02 03 04>bin.sql 或者for 循环读取顺序很重要。

4、如果不是drop，而是updata破坏数据，解决起来就复杂，一般需要停或禁止库被应用服务写入，然后再恢复。

如果是删除了某个库的某张表 则需要从bin-log 中分离出表的SQL语句的思路：

a.把原来指定oldboy库导出表结构，恢复好测试库，然后把oldboy_bin.sql语句恢复到测试库，然后再用mysqkdump导出需要的单个表，恢复到已经恢复了全备的正式库上。

b.www_bin.sql 最小按库分，那么可以grep”表名”把old_bin.sql所有对于该表的记录过滤出来。恢复到已经恢复了全备的正式库

mysqlbinlog -d oldboy >bin.sql 后，

grep 要恢复的表 bin.sql >a.sql