一、簡(jiǎn)單了解binlog

MySQL的二進(jìn)制日志binlog可以說(shuō)是MySQL最重要的日志，它記錄了所有的DDL和DML語(yǔ)句（除了數(shù)據(jù)查詢(xún)語(yǔ)句select）。因此binlog日志文件我們用cat等查看文件的命令是打不開(kāi)的，但是mysql提供了專(zhuān)門(mén)看binlog文件的命令mysqlbinlog。

binlog日志有兩個(gè)最重要的使用場(chǎng)景：

a、mysql主從復(fù)制：mysql replication在master端開(kāi)啟binlog,master把它的二進(jìn)制日志傳遞給slaves來(lái)達(dá)到master-slave數(shù)據(jù)一致的目的。

b、數(shù)據(jù)恢復(fù)：通過(guò)mysqlbinlog工具來(lái)恢復(fù)數(shù)據(jù)。

binlog日志包括兩類(lèi)文件：

　1)、二進(jìn)制日志索引文件(文件名后綴為.index)用于記錄所有的二進(jìn)制文件。

　2)、二進(jìn)制日志文件(文件名后綴為.00000*)記錄數(shù)據(jù)庫(kù)所有的DDL和DML(除了數(shù)據(jù)查詢(xún)語(yǔ)句select)語(yǔ)句事件。

二、開(kāi)啟mysql支持binlog

我們只需在mysql配置文件（my.cnf）中添加以下配置：

#binlog
log-bin=mysql-bin
server-id=1
binlog-format=MIXED

三、簡(jiǎn)單命令操作

• 查看是否開(kāi)啟binlog

mysql> show variables like 'log_%';
+----------------------------------------+-----------------------------+
| Variable_name                          | Value                       |
+----------------------------------------+-----------------------------+
| log_bin                                | ON                          |
| log_bin_basename                       | /data/mysql/mysql-bin       |
| log_bin_index                          | /data/mysql/mysql-bin.index |
| log_bin_trust_function_creators        | OFF                         |
| log_bin_use_v1_row_events              | OFF                         |
| log_builtin_as_identified_by_password  | OFF                         |
| log_error                              | /data/mysql/mysql.err       |
| log_error_verbosity                    | 3                           |
| log_output                             | FILE                        |
| log_queries_not_using_indexes          | OFF                         |
| log_slave_updates                      | OFF                         |
| log_slow_admin_statements              | OFF                         |
| log_slow_slave_statements              | OFF                         |
| log_statements_unsafe_for_binlog       | ON                          |
| log_syslog                             | OFF                         |
| log_syslog_facility                    | daemon                      |
| log_syslog_include_pid                 | ON                          |
| log_syslog_tag                         |                             |
| log_throttle_queries_not_using_indexes | 0                           |
| log_timestamps                         | UTC                         |
| log_warnings                           | 2                           |
+----------------------------------------+-----------------------------+
21 rows in set (0.00 sec)

lon_bin對(duì)應(yīng)的value值為on，意為開(kāi)啟

• 查看所有的binlog文件，文件地址默認(rèn)存儲(chǔ)在數(shù)據(jù)文件路徑

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+
1 row in set (0.00 sec)

• 查看master狀態(tài)，即最后（最新）一個(gè)binlog日志的編號(hào)名稱(chēng)，及其最后一個(gè)操作事件pos結(jié)束點(diǎn)(Position)值。

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      793 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

• flush 刷新log日志，自此刻開(kāi)始產(chǎn)生一個(gè)新編號(hào)的binlog日志文件;

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       840 |
| mysql-bin.000002 |       154 |
+------------------+-----------+
2 rows in set (0.00 sec)

• 重置（清空）所有binlog日志

mysql> reset master;
Query OK, 0 rows affected (0.01 sec)mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+
1 row in set (0.00 sec)

四、查看binlog日志文件內(nèi)容

第一種使用自帶的mysqlbinlog命令

./mysqlbinlog /data/mysql/mysql-bin.000001 # 下面截取一部分日志內(nèi)容
/*!*/;
# at 316
#231007 14:43:04 server id 1  end_log_pos 474 CRC32 0xff3b1884     Query    thread_id=10    exec_time=0    error_code=0
use `qinglvjizhang`/*!*/;
SET TIMESTAMP=1696660984/*!*/;
INSERT INTO `testdb`.`test`(`id`, `name`) VALUES (2, '111')
/*!*/;
# at 474
#231007 14:43:04 server id 1  end_log_pos 505 CRC32 0x122dcfc5     Xid = 66
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

通過(guò)binlog恢復(fù)數(shù)據(jù)原理：通過(guò)日志文件我們可以看到這里面記錄我們操作數(shù)據(jù)庫(kù)時(shí)的sql語(yǔ)句，當(dāng)我們誤操作刪除某條數(shù)據(jù)，便可以通過(guò)該記錄找出刪除的數(shù)據(jù)，并通過(guò)對(duì)應(yīng)sql進(jìn)行重新插入，這樣數(shù)據(jù)就恢復(fù)了。但是實(shí)際生產(chǎn)中，文件內(nèi)容非常多，而且出現(xiàn)事故往往不會(huì)是一條兩條的數(shù)據(jù)，這時(shí)在用自帶的命令去看就非常麻煩，因此還有第二種查看日志的方法。

第二種使用命令：mysql>?show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

參數(shù)解釋：

a、IN 'log_name':指定要查詢(xún)的binlog文件名（不指定就是第一個(gè)binlog文件）

b、FROM pos:指定從哪個(gè)pos起始點(diǎn)開(kāi)始查起（不指定就是從整個(gè)文件首個(gè)pos點(diǎn)開(kāi)始算）

c、LIMIT【offset】：偏移量(不指定就是0)

d、row_count :查詢(xún)總條數(shù)（不指定就是所有行）

通過(guò)這種方式我們?cè)诖罅繑?shù)據(jù)中可以一步一步確定發(fā)生問(wèn)題數(shù)據(jù)的部分，從而進(jìn)行數(shù)據(jù)恢復(fù)。萬(wàn)變不離其宗，掌握恢復(fù)數(shù)據(jù)的思路，具體問(wèn)題具體分析！！！