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

1.开启mysql日志

(1)配置my.cnf配置文件

先把my.cnf配置文件中的log-bin参数打开，并将log-bin的设为mysqlbin_linzhongniao，这样在mysql数据库中更新数据时就会记录到这个日志文件中。这样有一个缺点会导致日志文件很大，但是并没关系，系统可以自动分割我们还可以手动分割。查看mysql日志文件用mysqlbinlog

[root@linzhongniao ~]# grep "log-bin" /data/3306/my.cnf
log-bin = /data/3306/mysqlbin_linzhongniao

(2)配置完my.cnf重启mysql

[root@linzhongniao ~]# /data/3306/mysql restart
Restarting MySQL...
Stoping MySQL....
Starting MySQL......

(3)重启之后就可以在mysql的数据文件下看见mysql日志文件。

它记录了对数据有更改操作的语句，之前如果有就不用配置log-bin。

[root@linzhongniao ~]# ls /data/3306/mysqlbin_linzhongniao.*
/data/3306/mysqlbin_linzhongniao.000001  /data/3306/mysqlbin_linzhongniao.index
/data/3306/mysqlbin_linzhongniao.000002

2.模拟增量恢复不停止数据库方法

（1）查看表数据

mysql> use linzhongniao;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select database();
+--------------+
| database()   |
+--------------+
| linzhongniao |
+--------------+
1 row in set (0.00 sec)

mysql> show tables;
+------------------------+
| Tables_in_linzhongniao |
+------------------------+
| test   |
+------------------------+
1 row in set (0.01 sec)

mysql> select * from test;
+----+-------------+
| id | name|
+----+-------------+
|  1 | linzhogniao |
|  2 | wwn1314 |
|  3 | lisi|
|  4 | woshishei   |
|  5 | xiaozhang   |
+----+-------------+
5 rows in set (0.00 sec)

（2）将id为1的值改为nishishei

mysql> update test set name='nishishei' where id='1'; 
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test;  
+----+-----------+
| id | name  |
+----+-----------+
|  1 | nishishei |
|  2 | wwn1314   |
|  3 | lisi  |
|  4 | woshishei |
|  5 | xiaozhang |
+----+-----------+
5 rows in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

（3）然后退出mysql用mysqlbinlog查看一下mysqlbin_linzhongniao.000002日志文件

[root@linzhongniao ~]# mysqlbinlog /data/3306/mysqlbin_linzhongniao.000002
# at 183
#181018 20:24:08 server id 1  end_log_pos 299   Query   thread_id=1 exec_time=0 error_code=0
use `linzhongniao`/*!*/;
SET TIMESTAMP=1539865448/*!*/;
update test set name='nishishei' where id='1'
/*!*/;

看update test set name='nishishei' where id='1这条语句，在恢复的时候要把这条语句删掉，再把数据导进去。因为恢复数据的时候还会往里写数据导致数据缺失，所以先把之前的日志文件备份出来。恢复数据,好的方法就是停库，不能停止数据库可以进行手动切割日志文件这样就会生成一个新的日志文件来存储数据

（4）备份有误操作的日志文件

[root@linzhongniao data]# cp mysqlbin_linzhongniao.000002 /opt/ 

（5）切割日志文件

[root@linzhongniao ~]# mysqladmin -uroot -p123456 -S /data/3306/mysql.sock flush-log[root@linzhongniao ~]# ll /data/3306/mysqlbin_linzhongniao.00000*
 -rw-rw----. 1 mysql mysql 126 Oct 18 20:15 /data/3306/mysqlbin_linzhongniao.000001
 -rw-rw----. 1 mysql mysql 468 Oct 18 20:41 /data/3306/mysqlbin_linzhongniao.000002
 -rw-rw----. 1 mysql mysql 107 Oct 18 20:41 /data/3306/mysqlbin_linzhongniao.000003

我们看切割完了就出现了mysqlbin_linzhongniao.000003，切割完之后写入数据就往mysqlbin_linzhongniao.000003里面写了，现在我们要处理的就是将mysqlbin_linzhongniao.000002日志文件记录的数据重新导入到数据库中

（6）生成bin.sql文件

在工作中bin-log日志文件中记录了多个库的数据，有多个表和库，在恢复数据的时候只对有误操作的库和表进行操作即可。因为在恢复数据的时候有多个库和表会导致主键重复而不能插入数据，所以在做恢复的时候要指定库和表。备份指定库用mysqlbinlog的-d参数。

[root@linzhongniao ~]# mysqlbinlog -d linzhongniao /opt/mysqlbin_linzhongniao.000002 >bin.sql

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

[root@linzhongniao ~]# grep -i "update" bin.sql 
update test set name='nishishei' where id='1'

（7）开始增量恢复

[root@linzhongniao data]# mysql -uroot -p123456 -S /data/3306/mysql.sock linzhongniao <bin.sql
[root@linzhongniao data]# mysql -uroot -p123456 -e "select * from linzhongniao.test";
 +----+-------------+
| id | name|
 +----+-------------+
|  1 | linzhogniao |
|  2 | wwn1314 |
|  3 | lisi|
|  4 | woshishei   |
|  5 | xiaozhang   |
 +----+-------------+
5 rows in set (0.00 sec)