Mysql DBA 高級運(yùn)維學(xué)習(xí)之路-初步增量恢復(fù)mysql數(shù)據(jù)庫

1.開啟mysql日志

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

先把my.cnf配置文件中的log-bin參數(shù)打開，并將log-bin的設(shè)為mysqlbin_linzhongniao，這樣在mysql數(shù)據(jù)庫中更新數(shù)據(jù)時(shí)就會記錄到這個日志文件中。這樣有一個缺點(diǎn)會導(dǎo)致日志文件很大，但是并沒關(guān)系，系統(tǒng)可以自動分割我們還可以手動分割。查看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的數(shù)據(jù)文件下看見mysql日志文件。

它記錄了對數(shù)據(jù)有更改操作的語句，之前如果有就不用配置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.模擬增量恢復(fù)不停止數(shù)據(jù)庫方法

（1）查看表數(shù)據(jù)

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這條語句，在恢復(fù)的時(shí)候要把這條語句刪掉，再把數(shù)據(jù)導(dǎo)進(jìn)去。因?yàn)榛謴?fù)數(shù)據(jù)的時(shí)候還會往里寫數(shù)據(jù)導(dǎo)致數(shù)據(jù)缺失，所以先把之前的日志文件備份出來。恢復(fù)數(shù)據(jù),好的方法就是停庫，不能停止數(shù)據(jù)庫可以進(jìn)行手動切割日志文件這樣就會生成一個新的日志文件來存儲數(shù)據(jù)

（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

我們看切割完了就出現(xiàn)了mysqlbin_linzhongniao.000003，切割完之后寫入數(shù)據(jù)就往mysqlbin_linzhongniao.000003里面寫了，現(xiàn)在我們要處理的就是將mysqlbin_linzhongniao.000002日志文件記錄的數(shù)據(jù)重新導(dǎo)入到數(shù)據(jù)庫中

（6）生成bin.sql文件

在工作中bin-log日志文件中記錄了多個庫的數(shù)據(jù)，有多個表和庫，在恢復(fù)數(shù)據(jù)的時(shí)候只對有誤操作的庫和表進(jìn)行操作即可。因?yàn)樵诨謴?fù)數(shù)據(jù)的時(shí)候有多個庫和表會導(dǎo)致主鍵重復(fù)而不能插入數(shù)據(jù)，所以在做恢復(fù)的時(shí)候要指定庫和表。備份指定庫用mysqlbinlog的-d參數(shù)。

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

編輯bin.sql可以發(fā)現(xiàn)在什么時(shí)候操作數(shù)據(jù)庫導(dǎo)致數(shù)據(jù)丟失，我們找到update這條語句，將update這條語句刪掉。

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

（7）開始增量恢復(fù)

[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)