binlog

开启 binlog

[mysqld]
log-bin=mysql-bin
server-id=1
binlog_format=ROW
expire_logs_days=1

查看 binlog 配置

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 文件列表

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |   3086022 |
| mysql-bin.000003 |     18277 |
+------------------+-----------+
3 rows in set (0.00 sec)

查看 binlog 状态

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

清空 binlog (没试)

reset master

查看 binlog

$ mysqlbinlog mysql-bin.000003
# 解码 base64
$ mysqlbinlog mysql-bin.000003 --base64-output=decode-rows -v

mysql> show binlog events in 'mysql-bin.000003' from 18087 limit 2\G

查看 binlog 格式

mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)