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)