binlog日志非常關鍵，它記錄了所有的DDL和DML的資料增删改操作，使用binlog日志可以恢複資料，做主從的複制。是以，無論是開發環境還是測試環境，一定都要開啟binlog功能。
下面進入主題，在本地環境模拟生産資料庫故障，利用全量備份和binlog日志恢複資料庫的過程。
1、測試環境資訊：資料庫hse_whj,測試表whj_a,表初始資料量是10條。
MariaDB [hse_whj]> show tables;
+-------------------+
| Tables_in_hse_whj |
+-------------------+
| t_pm_factory |
| whj_a |
+-------------------+
2 rows in set (0.00 sec)
MariaDB [hse_whj]> select count(1) from whj_a;
+----------+
| count(1) |
+----------+
| 10 |
+----------+
1 row in set (0.03 sec)
MariaDB [hse_whj]>
2、對hse_whj資料庫做一個全量備份，備份工具使用mysqldump。備份檔案存放路徑是根目錄下的backup_whj。
[[email protected] /]# mysqldump -uroot -p --single-transaction --flush-logs --master_data=2 -E -R -B hse_whj > ./backup_whj/hse_whj_full.sql
Enter password:
[[email protected] /]# cd backup_whj
[[email protected] backup_whj]# ls
hse_whj_full.sql
[[email protected] backup_whj]#
mysqldump參數說明：（1）--single-transaction 擷取備份的一緻性，隻對innodb引擎有效，但不能隔離DDL操作
（2）--flush-logs 開始備份前重新整理日志
（3）--master_data 把binlog的位置和檔案名追加到輸出檔案中。如果為1就會輸出CHANGE MASTER 指令；如果為2就會輸出的CHANGE MASTER指令前添加注釋資訊
（4）-E 指定備份事件
（5）-R 指定備份存儲過程及自定義函數
（6）-B 指定備份的資料庫
3、向whj_a表裡新增1條資料，這時表資料量是11條，而且這條資料是在備份結束之後新增的。
MariaDB [hse_whj]> INSERT INTO whj_a
-> VALUES(11);
Query OK, 1 row affected (0.10 sec)
MariaDB [hse_whj]> select count(1) from whj_a;
+----------+
| count(1) |
+----------+
| 11 |
+----------+
1 row in set (0.01 sec)
MariaDB [hse_whj]>
4、模拟事故，删除資料hse_whj。
MariaDB [hse_whj]> drop database hse_whj;
Query OK, 2 rows affected (0.32 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| hse01 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.03 sec)
MariaDB [(none)]>
檢視備份後新增的binlog檔案
[[email protected] backup_whj]# ls
hse_whj_full.sql
[[email protected] backup_whj]# grep CHANGE hse_whj_full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='master-log.000038', MASTER_LOG_POS=403;
[[email protected] backup_whj]#
5、這時候不要慌張，先檢視備份後新增的和目前的binlog日志，記錄事故pos點。
MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000038 | 708 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> show binlog events in 'master-log.000038'\G
*************************** 1. row ***************************
Log_name: master-log.000038
Pos: 4
Event_type: Format_desc
Server_id: 7
End_log_pos: 256
Info: Server ver: 10.2.14-MariaDB-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: master-log.000038
Pos: 256
Event_type: Gtid_list
Server_id: 7
End_log_pos: 315
Info: [0-8-3,0-7-80]
*************************** 3. row ***************************
Log_name: master-log.000038
Pos: 315
Event_type: Binlog_checkpoint
Server_id: 7
End_log_pos: 359
Info: master-log.000037
*************************** 4. row ***************************
Log_name: master-log.000038
Pos: 359
Event_type: Binlog_checkpoint
Server_id: 7
End_log_pos: 403
Info: master-log.000038
*************************** 5. row ***************************
Log_name: master-log.000038
Pos: 403
Event_type: Gtid
Server_id: 7
End_log_pos: 445
Info: BEGIN GTID 0-7-81
*************************** 6. row ***************************
Log_name: master-log.000038
Pos: 445
Event_type: Query
Server_id: 7
End_log_pos: 544
Info: use `hse_whj`; INSERT INTO whj_a
VALUES(11)
*************************** 7. row ***************************
Log_name: master-log.000038
Pos: 544
Event_type: Xid
Server_id: 7
End_log_pos: 575
Info: COMMIT /* xid=1083 */
*************************** 8. row ***************************
Log_name: master-log.000038
Pos: 575
Event_type: Gtid
Server_id: 7
End_log_pos: 617
Info: GTID 0-7-82
*************************** 9. row ***************************
Log_name: master-log.000038
Pos: 617
Event_type: Query
Server_id: 7
End_log_pos: 708
Info: drop database hse_whj
9 rows in set (0.01 sec)
MariaDB [(none)]>
通過讀取日志發現出現問題的pos是617到708之間。語句在info行可以看到。
6、移動備份後新産生的binlog日志，并轉成可以編輯的sql檔案，編輯sql檔案去除事故語句drop database hse_whj
[[email protected] /]# cp ./var/lib/mysql/master-log.000038 ./backup_whj/master-log.000038
[[email protected] /]# cd backup_whj
[[email protected] backup_whj]# ls
hse_whj_full.sql master-log.000038
[[email protected] backup_whj]# mysqlbinlog -d hse_whj master-log.000038 > master-log.sql
[[email protected] backup_whj]# ls
hse_whj_full.sql master-log.000038 master-log.sql
[[email protected] backup_whj]# vi master-log.sql
7、先恢複全量備份，在恢複全量備份之前要停止記錄binlog,可以修改配置檔案，重新開機資料庫服務。避免恢複過程記錄到binlog，對日志造成幹擾。
[[email protected] backup_whj]# ls
hse_whj_full.sql master-log.000038 master-log.sql
[[email protected] backup_whj]# mysql -uroot -p < hse_whj_full.sql
Enter password:
[[email protected] backup_whj]#
8、binlog恢複
[[email protected] backup_whj]# ls
hse_whj_full.sql master-log.000038 master-log.sql
[[email protected] backup_whj]# mysql -uroot -p < master-log.sql
Enter password:
[[email protected] backup_whj]#
9、驗證
+--------------------+
| Database |
+--------------------+
| hse01 |
| hse_whj |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.02 sec)
MariaDB [(none)]> use hse_whj;
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
MariaDB [hse_whj]> select count(1) from whj_a;
+----------+
| count(1) |
+----------+
| 11 |
+----------+
1 row in set (0.01 sec)
MariaDB [hse_whj]>
通過驗證發現hse_whj表資料已經是11條了，恢複了之前的資料。不要忘記再開啟二進制日志。
![win10安裝MariaDB資料庫[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)