MYSLQ增量恢复学习及实践

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

创新互联公司主营黄冈网站建设的网络公司,主营网站建设方案,app软件开发,黄冈h5重庆小程序开发公司搭建,黄冈网站营销推广欢迎黄冈等地区企业咨询

什么情况需要增量恢复？

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

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

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 查看一库，到全备之前的数据已经恢复了
[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查看表 到误操作之前的数据都找回来了。
[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            
            
                            

                当前题目：MYSLQ增量恢复学习及实践                

                链接分享：http://scyanting.com/article/picsho.html

MYSLQ增量恢复学习及实践

其他资讯