MySQL binary log 配置及使用簡介

Mysql的binlog日志作用是用來記錄mysql内部增删改查等對mysql資料庫有更新的内容的記錄（對資料庫的改動），對資料庫的查詢select或show等不會被binlog日志記錄;主要用于資料庫的主從複制以及增量恢複。本文關于binlog的講解基于ubuntu16.04安裝的MySQL 5.7.25 。

配置binlog

在mysql 5.7之後，已經沒有my.cnf的模闆。具體關于binlog的配置請自行搜尋。

配置binlog的主要參數如下：

[mysqld]

server-id = 1

log_bin = mysql-bin           

檢查binlog是否已經開啟

登入mysql後，使用如下指令：

mysql> show variables like '%log_bin%';           

結果如下:

+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
6 rows in set (0.01 sec)           

檢視binlog名稱

檢視binlog名稱的指令如下：

mysql> show binary logs;           

結果如下：

+------------------+-----------+

| Log_name         | File_size |

+------------------+-----------+

| mysql-bin.000001 |       177 |

| mysql-bin.000002 |       462 |

+------------------+-----------+

2 rows in set (0.00 sec)           

檢視binlog具體内容

檢視binlog具體内容的指令如下：

mysql> show binlog events in 'mysql-bin.000001';           

+------------------+-----+----------------+-----------+-------------+---------------------------------------+

| Log\_name         | Pos | Event\_type     | Server\_id | End\_log\_pos | Info                                  |

+------------------+-----+----------------+-----------+-------------+---------------------------------------+

| mysql-bin.000001 |   4 | Format\_desc    |         1 |         123 | Server ver: 5.7.25-log, Binlog ver: 4 |

| mysql-bin.000001 | 123 | Previous\_gtids |         1 |         154 |                                       |

| mysql-bin.000001 | 154 | Stop           |         1 |         177 |                                       |

+------------------+-----+----------------+-----------+-------------+---------------------------------------+

3 rows in set (0.00 sec)