Mysql binlog日志功能使用，简单易懂

一、简单了解binlog

MySQL的二进制日志binlog可以说是MySQL最重要的日志，它记录了所有的DDL和DML语句（除了数据查询语句select）。因此binlog日志文件我们用cat等查看文件的命令是打不开的，但是mysql提供了专门看binlog文件的命令mysqlbinlog。

binlog日志有两个最重要的使用场景：

a、mysql主从复制：mysql replication在master端开启binlog,master把它的二进制日志传递给slaves来达到master-slave数据一致的目的。

b、数据恢复：通过mysqlbinlog工具来恢复数据。

binlog日志包括两类文件：

　1)、二进制日志索引文件(文件名后缀为.index)用于记录所有的二进制文件。

　2)、二进制日志文件(文件名后缀为.00000*)记录数据库所有的DDL和DML(除了数据查询语句select)语句事件。

二、开启mysql支持binlog

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

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

三、简单命令操作

• 查看是否开启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对应的value值为on，意为开启

• 查看所有的binlog文件，文件地址默认存储在数据文件路径

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

• 查看master状态，即最后（最新）一个binlog日志的编号名称，及其最后一个操作事件pos结束点(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日志，自此刻开始产生一个新编号的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日志文件内容

第一种使用自带的mysqlbinlog命令

./mysqlbinlog /data/mysql/mysql-bin.000001 
 
# 下面截取一部分日志内容
/*!*/;
# 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*/;

通过binlog恢复数据原理：通过日志文件我们可以看到这里面记录我们操作数据库时的sql语句，当我们误操作删除某条数据，便可以通过该记录找出删除的数据，并通过对应sql进行重新插入，这样数据就恢复了。但是实际生产中，文件内容非常多，而且出现事故往往不会是一条两条的数据，这时在用自带的命令去看就非常麻烦，因此还有第二种查看日志的方法。

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

参数解释：

a、IN 'log_name':指定要查询的binlog文件名（不指定就是第一个binlog文件）

b、FROM pos:指定从哪个pos起始点开始查起（不指定就是从整个文件首个pos点开始算）

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

d、row_count :查询总条数（不指定就是所有行）

通过这种方式我们在大量数据中可以一步一步确定发生问题数据的部分，从而进行数据恢复。万变不离其宗，掌握恢复数据的思路，具体问题具体分析！！！