Mysql DBA 高级运维学习之路-初步增量恢复mysql数据库

xywuyiba6

9.10.3 初步增量恢复MySQL数据实战

a.配置my.cnf配置文件
　　先把my.cnf配置文件中的log-bin参数打开重命名为mysqlbin_zbf，这样在mysql数据库中更新一个东西就会在这个日志里更新。这样会导致这个日志很大，没关系系统可以自动分割还可以手动分割。
　　

[root@localhost var]# vim /etc/my.cnf　　
…省略……
　　
# Uncomment the following if you want to log updates
　　
log-bin=mysqlbin_zbf
　　
# binary logging format - mixed recommended
　　
……省略…
　　

b.配置完my.cnf重启mysql
　　

[root@localhost var]# /etc/init.d/mysqld restart　　
Shutting down MySQL. SUCCESS!
　　
Starting MySQL. SUCCESS!
　　

c.重启之后就可以在mysql的数据文件下看见mysqlbin_zbf.000001日志文件。
　　它记录了对数据有更改操作的语句，之前如果有就不用配置log-bin。
　　

[root@mysql data]# ls　　
ibdata1  ib_logfile1  mysqlbin_zbf.000001  mysql.err  performance_schema
　　
ib_logfile0  mysqlmysqlbin_zbf.index   mysql.pid  zbf
　　

d.重启完登录mysql，将数据库数据破坏模拟增量恢复。
　　（1）查看表数据
　　

mysql> select * from student;　　
+----+-----------+-----+--------+

　　
|>　　
+----+-----------+-----+--------+
　　
|  1 | zbf666|  29 | linux  |
　　
|  2 | lisi  |  28 | mysql  |
　　
|  3 | zhangsan  |  21 | python |
　　
|  4 | woshishei |  24 | java   |
　　
+----+-----------+-----+--------+
　　

　　（2）将name字段的值全部修改为luhan
　　

mysql> update student set name='luhan';　　
Query OK, 4 rows affected (0.03 sec)
　　
Rows matched: 4  Changed: 4  Warnings: 0
　　

　　（3）再查看表数据
　　

mysql> select * from student;　　
+----+-------+-----+--------+

　　
|>　　
+----+-------+-----+--------+
　　
|  1 | luhan |  29 | linux  |
　　
|  2 | luhan |  28 | mysql  |
　　
|  3 | luhan |  21 | python |
　　
|  4 | luhan |  24 | java   |
　　
+----+-------+-----+--------+
　　
4 rows in set (0.00 sec)
　　

　　（4）然后退出mysql用mysqlbinlog查看一下mysqlbin_zbf.000001日志文件
　　

[root@mysql ~]# mysqlbinlog /usr/local/mysql/data/mysqlbin_zbf.000001　　
……省略……..
　　
use `zbf`/*!*/;
　　
SET TIMESTAMP=1517061765/*!*/;
　　
update student set name='luhan'
　　
/*!*/;
　　
# at 267
　　
……省略….
　　

　　我们看到update student set name='luhan'这条语句，在恢复的时候我们要把这条语句删掉，再把数据导进去。因为恢复数据的时候还会往里写数据导致数据缺失，所以先把之前的日志文件备份出来.恢复数据,最好的方法就是停库，不能停的话最好的办法就是切割。
　　

[root@mysql data]# cp mysqlbin_zbf.000001 /opt/ 　　

　　（5）模拟不能停止数据库的思路，停止数据库是最好的。切割成另外一个。
　　

[root@mysql data]# mysqladmin -uroot -p123456 flush-log　　
[root@mysql data]# ll
　　
总用量 28708
　　
-rw-rw----. 1 mysql mysql 18874368 1月  27 22:18 ibdata1
　　
-rw-rw----. 1 mysql mysql  5242880 1月  27 22:18 ib_logfile0
　　
-rw-rw----. 1 mysql mysql  5242880 1月  11 20:30 ib_logfile1
　　
drwx------. 2 mysql root  4096 1月  11 20:08 mysql
　　
-rw-rw----. 1 mysql mysql  547 1月  27 22:29 mysqlbin_zbf.000001
　　
-rw-rw----. 1 mysql mysql  107 1月  27 22:29 mysqlbin_zbf.000002
　　
-rw-rw----. 1 mysql mysql   44 1月  27 22:29 mysqlbin_zbf.index
　　
-rw-r-----. 1 mysql root  7937 1月  27 21:57 mysql.err
　　
-rw-rw----. 1 mysql mysql5 1月  27 21:57 mysql.pid
　　
drwx------. 2 mysql mysql 4096 1月  11 20:08 performance_schema
　　
drwx------. 2 mysql mysql 4096 1月  27 21:32 zbf
　　

　　我们看切割完了就出现了mysqlbin_zbf.000002，切割完之后写入数据就往mysqlbin_zbf.000002里面写了，现在我们要处理的就是mysqlbin_zbf.000001了。
　　（6）生成bin.sql文件
　　在企业中如果有多个bin-log就把所有的做恢复，同时也会有很多库和表，bin.sql里面写入的是所有库和表修改过得数据，恢复bin.sql里面的数据时就会导致主键重复，不能插入数据。所以要指定库和表。
　　

[root@mysql data]# mysqlbinlog -d zbf mysqlbin_zbf.000001 >bin.sql　　

　　编辑bin.sql可以发现在什么时候操作数据库导致数据丢失，我们找到update这条语句，将update这条语句删掉。
　　

[root@mysql data]# vim bin.sql　　
……省略……

　　
#180127 23:29:00 server>　　
SET TIMESTAMP=1517066940/*!*/;
　　
update student set name='luhan'
　　
/*!*/;
　　
……省略…
　　
# at 767
　　

　　（7）开始增量恢复
　　

[root@mysql data]# mysql -uroot -p123456 zbf 　　
+----+-----------+-----+--------+
　　
|  1 | zbf666|  29 | linux  |
　　
|  2 | lisi  |  28 | mysql  |
　　
|  3 | zhangsan  |  21 | python |
　　
|  4 | woshishei |  24 | java   |
　　
+----+-----------+-----+--------+