MySQL - binlog日志简介及设置

基本概念

binlog是Mysql sever层维护的一种二进制日志，与innodb引擎中的redo/undo log是完全不同的日志；其主要是用来记录对mysql数据更新或潜在发生更新的SQL语句，记录了所有的DDL和DML(除了数据查询语句)语句，并以事务的形式保存在磁盘中，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。

一般来说开启二进制日志大概会有1%的性能损耗(参见MySQL官方中文手册 5.1.24版)。

作用主要有：

• 复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves并回放来达到master-slave数据一致的目的
• 数据恢复：通过mysqlbinlog工具恢复数据
• 增量备份

二进制日志包括两类文件：二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件，二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML(除了数据查询语句)语句事件。

日志管理

开启binlog：

修改配置文件 my.cnf

配置 log-bin 和 log-bin-index 的值，如果没有则自行加上去。

log-bin=mysql-bin
log-bin-index=mysql-bin.index

这里的 log-bin 是指以后生成各 Binlog 文件的前缀，比如上述使用master-bin，那么文件就将会是master-bin.000001、master-bin.000002 等。

log-bin-index 则指 binlog index 文件的名称，这里我们设置为master-bin.index，可以不配置。

命令查看配置：

binlog开启后，可以在配置文件中查看其位置信息，也可以在myslq命令行中查看：

mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------------------+
| Variable_name                   | Value                                       |
+---------------------------------+---------------------------------------------+
| log_bin                         | ON                                          |
| log_bin_basename                | D:\Program Files\MySQL\data\mysql-bin       |
| log_bin_index                   | D:\Program Files\MySQL\data\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.07 sec)

查看binlog文件列表

mysql>  show binary logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |       202 | No        |
| mysql-bin.000002 |      2062 | No        |
+------------------+-----------+-----------+
2 rows in set (0.07 sec)

binlog文件开启binlog后，会在数据目录（默认）生产host-bin.n（具体binlog信息）文件及host-bin.index索引文件（记录binlog文件列表）。当binlog日志写满(binlog大小max_binlog_size，默认1G),或者数据库重启才会生产新文件，但是也可通过手工进行切换让其重新生成新的文件（flush logs）；另外，如果正使用大的事务，由于一个事务不能横跨两个文件，因此也可能在binlog文件未满的情况下刷新文件。

查看日志状态

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     2062 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.08 sec)

显示正在写入的二进制文件，及当前position

刷新日志

mysql> flush logs;
Query OK, 0 rows affected (0.12 sec)

mysql>  show binary logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |       202 | No        |
| mysql-bin.000002 |      2109 | No        |
| mysql-bin.000003 |       155 | No        |
+------------------+-----------+-----------+
3 rows in set (0.07 sec)

自此刻开始产生一个新编号的binlog日志文件

每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；

重置(清空)所有binlog日志

mysql> reset master;

常用命令

mysqlbinlog查看日志

D:\Program Files\MySQL
$ bin\mysqlbinlog data\mysql-bin.000002

在MySQL5.5以下版本使用mysqlbinlog命令时如果报错，就加上 “--no-defaults”选项

• mysqlbinlog是mysql官方提供的一个binlog查看工具，
  • 也可使用–read-from-remote-server从远程服务器读取二进制日志，
  • 还可使用--start-position --stop-position、--start-time= --stop-time精确解析binlog日志

内容：

BINLOG '
K3L4XBMBAAAARQAAAHEGAAAAAJoCAAAAAAEACmxvbmdodWJhbmcABXRoZW1lAAUDDwUREQWWAAgA
AAABAQACASGhIgQL
K3L4XB4BAAAAPQAAAK4GAAAAAJoCAAAAAAEAAgAF/wA0AQAABGFhYWEAAAAAAMBYQFz4citc+HIr
sXjMIA==
'/*!*/;
# at 1710
#190606  9:53:47 server id 1  end_log_pos 1741 CRC32 0xddb08f33         Xid = 216
COMMIT/*!*/;
# at 1741
#190606  9:53:47 server id 1  end_log_pos 1820 CRC32 0x166b4128         Anonymous_GTID  last_committed=5        sequence_number=6       rbr_only=yes    original_committed_timestamp=1559786027387679   immediate_commit_timestamp=15597860273
        transaction_length=321
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1559786027387679 (2019-06-06 09:53:47.387679 ?D1ú±ê×?ê±??)
# immediate_commit_timestamp=1559786027387679 (2019-06-06 09:53:47.387679 ?D1ú±ê×?ê±??)
/*!80001 SET @@session.original_commit_timestamp=1559786027387679*//*!*/;
/*!80014 SET @@session.original_server_version=80016*//*!*/;
/*!80014 SET @@session.immediate_server_version=80016*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1820
#190606  9:53:47 server id 1  end_log_pos 1901 CRC32 0x47def222         Query   thread_id=10    exec_time=0     error_code=0
SET TIMESTAMP=1559786027/*!*/;
BEGIN
/*!*/;
# at 1901
#190606  9:53:47 server id 1  end_log_pos 1970 CRC32 0x5a235198         Table_map: `longhubang`.`theme` mapped to number 666
# at 1970
#190606  9:53:47 server id 1  end_log_pos 2031 CRC32 0x62dc1928         Write_rows: table id 666 flags: STMT_END_F

show binlog events查看binlog日志

A.查询第一个(最早)的binlog日志：
  mysql> show binlog events; 

B.指定查询 mysql-bin.000021 这个文件：
  mysql> show binlog events in 'mysql-bin.000021';

C.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起：
  mysql> show binlog events in 'mysql-bin.000021' from 8224;

D.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，查询10条
  mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 10;

E.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，偏移2行，查询10条
  mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 2,10;

内容：

mysql> show binlog events  in 'mysql-bin.000002' from 1710 limit 10;
+------------------+------+----------------+-----------+-------------+--------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                 |
+------------------+------+----------------+-----------+-------------+--------------------------------------+
| mysql-bin.000002 | 1710 | Xid            |         1 |        1741 | COMMIT /* xid=216 */                 |
| mysql-bin.000002 | 1741 | Anonymous_Gtid |         1 |        1820 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 1820 | Query          |         1 |        1901 | BEGIN                                |
| mysql-bin.000002 | 1901 | Table_map      |         1 |        1970 | table_id: 666 (longhubang.theme)     |
| mysql-bin.000002 | 1970 | Write_rows     |         1 |        2031 | table_id: 666 flags: STMT_END_F      |
| mysql-bin.000002 | 2031 | Xid            |         1 |        2062 | COMMIT /* xid=223 */                 |
| mysql-bin.000002 | 2062 | Rotate         |         1 |        2109 | mysql-bin.000003;pos=4               |
+------------------+------+----------------+-----------+-------------+--------------------------------------+
7 rows in set (0.14 sec)

数据恢复

完全备份：

D:\Program Files\MySQL
$ bin\mysqldump  -h127.0.0.1 -p3306 -uroot -phongda$123456 longhubang >D:\data\backup\longhubang.dump
mysqldump: [Warning] Using a password on the command line interface can be insecure.

注意要创建好D:\data\backup文件夹。

参考：

MySQL的binlog日志

腾讯工程师带你深入解析 MySQL binlog

初探 MySQL 的 Binlog

超级有用的15个mysqlbinlog命令

MySQL - binlog日志简介及设置

原文：https://www.cnblogs.com/hongdada/p/10983768.html