MySQL binlog之数据恢复

一、恢复方案
1、数据量不是特别大，可以将mysqldump命令备份的数据使用mysql客户端命令或者source命令完成数据的恢复；
2、使用xtrabackup完成数据库的物理备份恢复，期间需要重启数据库服务；
3、使用lvm快照卷完成数据库物理备份恢复，期间需要重启数据库服务；

二、使用mysqlbinlog进行时间点恢复
1、介绍
mysqlbinlog是一个从二进制日志中读取语句的工具，在mysql安装完成之后自带的。

2、二进制日志恢复原理
当使用mysqldump对数据库进行备份时，生成的备份文件中包含了数据库dml操作时的时间点以及备份时的二进制日志位置信息，如果单库，可以从某个时间点开始，进行时间点恢复；如果是主从架构，可以根据备份时的--master-data=2和--single-transaction，完成根据时间点或者位置点的恢复。

3、二进制日志恢复示例
（1）单库恢复示例
创建数据库，并插入测试数据

mysql> show create database test_db;
mysql> create table `student` (
  `id` int(11) not null auto_increment,
  `name` varchar(20) not null,
  `age` tinyint(4) default null,
  primary key (`id`)
) engine=innodb auto_increment=5 default charset=utf8;
mysql> insert into student (name,age) values('jack',23),('tomcat',24),('xiaohong',22),('zhangfei',29);

使用mysqldump进行全量备份，备份时滚动日志，同时记住二进制日志文件名称和日志的位置点

[root@wb-blog ~]# mysqldump -uroot -proot -h127.0.0.1 -p3306 --databases test_db --single-transaction --triggers --routines --flush-logs --events > /tmp/test_db.sql
[root@wb-blog ~]# mysql -e "show binary logs" > bin_pos_`date +%f`.out

此时查看二进制日志文件名称和日志点位置如下

mysql> show binary logs;
+------------------+-----------+
| log_name         | file_size |
+------------------+-----------+
| mysql-bin.000001 |      1497 |
| mysql-bin.000002 |       397 |
+------------------+-----------+
2 rows in set (0.00 sec)

使用了一段时间，不小心误操作，执行了如下的语句，将数据库中的数据全部修改了

mysql> update student set name = 'admin';

过了一段时间，可能是几分钟，也可能是几个小时，有人反映网站登录有问题了，查看发现好多数据被误修改，而这段时间内，还一直有写入操作，如又新增了如下的记录

mysql> insert into student(name,age) values('hbase',23),('blackhole',30);

此时需要恢复数据，首先为了防止数据继续写入，可以先锁表，暂停写入业务，通知用户系统维护，然后执行如下操作：

#登录数据库，锁表，此时表只能读，不能写
mysql> use test_db;
mysql> lock table student read;

#然后重新（注意是重新打开）打开一个session窗口，否则会话处出之后，锁就会释放。然后压缩备份现有数据和二进制日志文件
[root@wb-blog mysql_logs]# tar -zcvf mysql_data.tar.gz /mysql_data/*
[root@wb-blog mysql_logs]# tar -zcvf mysql_bin.tar.gz /mysql_logs/*
#导入最近备份的一次全备数据
[root@wb-blog ~]# mysql -uroot -proot -h127.0.0.1 -p3306 < /tmp/test_db.sql 

#查看全备时的二进制日志文件和日志点
[root@wb-blog ~]# cat bin_pos_2018-06-24.out 
    log_name        file_size
    mysql-bin.000001      1497
    mysql-bin.000002       397
#将861这个点之后的二进制日志文件转换为一个sql文件
[root@wb-blog bin]# ./mysqlbinlog /mysql_logs/mysql-bin.000002 --start-position=397 > /tmp/tmp.sql
#使用vim编辑器编辑这个sql文件，找到其中的未加条件的update语句，然后将其删掉，然后将删掉update语句之后的sql脚本内容导入到数据库中
[root@wb-blog bin]# vim /tmp/tmp.sql
    use `test_db`/*!*/;
    set timestamp=1522088753/*!*/;
    update student set name = 'admin'  #删掉这一句
[root@wb-blog bin]# mysql -uroot -proot -h127.0.0.1 -p3306 < /tmp/tmp.sql

#登录数据库查询数据是否恢复，可以查看被误修改的数据是否还原，然后对表执行解锁，再次全备数据
mysql> unlock tables;

（2）主从架构数据恢复示例
环境
主库：192.168.199.10(node01)
从库：192.168.199.11(node02)

首先停止从库的sql线程，然后在从库上全备数据，并输入"show slave status"信息到备份文件中，"show slave status"的输出信息中记录了当前应用到了主库的哪个位置点的信息

#登录从库，然后关闭sql线程
mysql> stop slave sql_thread;

#然后记录从库中当前应用的主库的二进制日志文件信息
[root@node02 mysql_data]# mysql -e "show slave status \g" > slave_`date +%f`.info
[root@node02 mysql_data]# mysqldump -uroot -proot -h127.0.0.1 -p3306 --databases test_db --routines --triggers --single-transaction > /tmp/mysql_test_db_`date +%f`.sql

在从库上备份完成之后，重新启动从库的sql线程

mysql> start slave sql_thread;

启动sql线程之后，备份这段时间内在主库上的dml操作会重新同步到从库上。假如在主库上发生了一个误操作，没加条件更新了student表中的所有数据，导致了表中所有数据被修改，此时由于同步操作，从库也被修改了

#登录主库，修改数据库的对外用户，使其暂不提供服务，然后滚动日志
mysql> update mysql.user set host = '127.0.0.1' where user='tomcat';
query ok, 1 rows affected (0.00 sec)
#刷新权限表
mysql> flush privileges;
query ok, 0 rows affected (0.00 sec)
#滚动日志
mysql> flush logs;
query ok, 0 rows affected (0.01 sec)

#将从库备份的数据及备份时刻的从库slave信息传到主库上
[root@node02 mysql_data]# scp /tmp/mysql_test_db_2018-06-24.sql 192.168.199.10:/root/
[root@node02 mysql_data]# scp slave_2018-06-24.info node01:/root/

备份主库的数据目录和二进制日志文件目录

[root@node01 mysql_logs]# tar -zcvf mysql_master_data.tar.gz /mysql_data/*
[root@node01 mysql_logs]# tar -zcvf mysql_logs.tar.gz /mysql_logs/*    

导入从库最近一次备份的数据

[root@node01 mysql_logs]# mysql -uroot -proot -h127.0.0.1 -p3306 < /root/mysql_test_db_2018-03-26.sql 
#注意：上述的操作不能锁主库的表，否则全备数据无法导入。

查看备份时刻的从库中应用到的主库二进制日志文件名称及位置点

[root@node01 mysql_logs]# cat /root/slave_2018-03-26.info
    master_log_file: master-bin.000002  #备份时所应用的主库二进制日志文件名称
    read_master_log_pos: 395   #备份时所应用的主库二进制日志文件的位置

从该日志文件及日志点开始，将395日志点之后的日志文件转换为sql脚本，如果有多个二进制日志文件可以同时转换为sql脚本，如下所示

[root@node01 mysql_logs]# mysqlbinlog /mysql_logs/master-bin.000002 --start-position=395 > /tmp/tmp.sql
#将master-bin.000003,master-bin.000004,master-bin.000005合并到/tmp.sql文件中
[root@node01 mysql_logs]# mysqlbinlog /mysql_logs/master-bin.00000{3,4,5} --start-position=395 > /tmp/tmp.sql

找到误操作的update语句，然后删除该语句，并将增量的sql脚本导入数据库

[root@node01 mysql_logs]# vim /tmp/tmp.sql
    use `test_db`/*!*/;
    update student set name = 'admin'  #删掉这一句
[root@node01 mysql_logs]# mysql -uroot -proot -h127.0.0.1 -p3306 < /tmp/tmp.sql 

登录数据库，查看数据是否正常，被误修改的数据是否已经恢复，如果恢复，则在主库上全备数据，然后传到从库，完成从库恢复

[root@node01 mysql_data]# mysqldump -uroot -proot -h127.0.0.1 -p3306 --databases test_db --routines --triggers --single-transaction --master-date=1 > /tmp/master_test_db_`date +%f`.sql
[root@node01 mysql_data]# scp /tmp/master_test_db_2018-06-24.sql node01:/root/

#如果从库设置了只读，需要先去掉只读限制
mysql> set global read_only = off;

#将数据导入从库
[root@node02 mysql_logs]# mysql -uroot -proot -h127.0.0.1 -p3306 < /root/master_test_db_2018-06-24.sql

#开启从库的只读
mysql> set global read_only = on;

由于在主库上备份时添加了--master-date=1参数，所以从库导入之后，不需要重新执行change master操作。

登录从库，查看show slave status信息是否正常，如果正常，登录主库，重新修改授权表，然后对外提供服务

mysql> update mysql.user set host = '192.168.0.%' where user = 'tomcat';
mysql> flush privileges;
query ok, 0 rows affected (0.00 sec)

执行完成之后，主从数据恢复完毕。

至此，数据恢复介绍完毕，上述介绍了使用全备加二进制日志实现单实例数据库和主从数据库的数据恢复过程

reference link:

 

 

全量备份参考:  mysqldump -u root -p -b -f -r -x --master-data=2 test_db|gzip >/opt/backup/test_db_$(date +%f).sql.gz

参数说明：
-b：指定数据库
-f：刷新日志
-r：备份存储过程等
-x：锁表
--master-data=2表示在dump过程中记录主库的binlog和pos点，并在dump文件中注释掉这一行；
--master-data=1表示在dump过程中记录主库的binlog和pos点，并在dump文件中不注释掉这一行，即恢复时会执行；
--dump-slave=2表示在dump过程中，在从库dump，mysqldump进程也要在从库执行，记录当时主库的binlog和pos点，并在dump文件中注释掉这一行；
--dump-slave=1表示在dump过程中，在从库dump，mysqldump进程也要在从库执行，记录当时主库的binlog和pos点，并在dump文件中不注释掉这一行；

注意:在从库上执行备份时，即--dump-slave=2，这时整个dump过程都是stop io_thread的状态。

mysqldump导出数据时，当这个参数（master-data）的值为1的时候，mysqldump出来的文件就会包括change master to这个语句，change master to后面紧接着就是file和position的记录，在slave上导入数据时就会执行这个语句，salve就会根据指定这个文件位置从master端复制binlog。默认情况下这个值是1

当这个值是2的时候，chang master to也是会写到dump文件里面去的，但是这个语句是被注释的状态。

--single-transaction：从5.1.13开始mysqldump指定--single-transaction备份时使用start transaction /*!40100 with consistent snapshot */ 来代替begin 开启一个事物，这样就能在备份的时候产生一个一致的快照

--flush-logs：同 -f

--events：包含事件

--routines：包含常规

--triggers：包含触发器

 

mysqlbinlog参数说明：

常用参数选项解释：

--no-defaults ？
--start-position=875 起始pos点
--stop-position=954 结束pos点
--start-datetime="2016-9-25 22:01:08" 起始时间点
--stop-datetime="2019-9-25 22:09:46" 结束时间点
--database=zyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库，只限本地log日志)
--------------------------------------------------------
不常用选项：
-u --user=name 连接到远程主机的用户名
-p --password[=name] 连接到远程主机的密码
-h --host=name 从远程主机上获取binlog日志
--read-from-remote-server 从某个mysql服务器上读取binlog日志