MySQL8 二进制日志详解

在本章中，我们将介绍以下配方：

二进制日志包含对数据库的所有更改的记录，包括数据和结构。二进制日志不用于不修改数据的语句，如SELECT或SHOW。在启用二进制日志记录的情况下运行服务器会对性能产生轻微影响。二进制日志是安全的。仅记录或读回完整的事件或事务。

为什么要使用二进制日志？

• 复制：使用二进制日志将对服务器所做的更改流式传输到另一台服务器。从属服务器充当镜像副本，可用于分配负载。接受写入的服务器称为主服务器，镜像复制服务器称为从服务器。
• 时间点恢复：假设您在周日 00:00 进行备份，而您的数据库在周日 8:00 崩溃。使用备份，您可以恢复到周日 00:00。使用二进制日志，您可以回复它们，恢复到 08:00。

要启用binlog，您必须设置log_bin和server_id并重新启动服务器。您可以在log_bin中提及路径和基名称。例如，log_bin设置为/data/mysql/binlogs/server1，二进制日志以server1.000001、server1.000002等名称存储在/data/mysql/binlogs文件夹中。服务器每次启动或刷新日志或当前日志大小达到max_binlog_size时，都会在该系列中创建一个新文件。它维护server1.index文件，其中包含每个二进制日志的位置。

让我们看看如何使用日志。我相信你会喜欢了解他们的。

1. 启用二进制日志记录并设置server_id。在您喜爱的编辑器中打开 MySQLconfig文件，并附加以下行。选择server_id以便它对基础架构中的每个 MySQL 服务器都是唯一的。 您也可以简单地将log_bin变量放入my.cnf中，不带任何值。在这种情况下，二进制日志在data directory目录中创建，并使用hostname作为其名称。

shell> sudo vi /etc/my.cnf
[mysqld]
log_bin = /data/mysql/binlogs/server1
server_id = 100

2. 重新启动 MySQL 服务器：

shell> sudo systemctl restart mysql

3. 验证是否已创建二进制日志：

mysql> SHOW VARIABLES LIKE 'log_bin%';
+---------------------------------+-----------------------------------+
| Variable_name                   | Value                             |
+---------------------------------+-----------------------------------+
| log_bin                         | ON                                |
| log_bin_basename                | /data/mysql/binlogs/server1       |
| log_bin_index                   | /data/mysql/binlogs/server1.index |
| log_bin_trust_function_creators | OFF                               |
| log_bin_use_v1_row_events       | OFF                               |
+---------------------------------+-----------------------------------+
5 rows in set (0.00 sec)

mysql> SHOW MASTER LOGS;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| server1.000001 | 154       |
+----------------+-----------+
1 row in set (0.00 sec)

shell> sudo ls -lhtr /data/mysql/binlogs
total 8.0K
-rw-r----- 1 mysql mysql 34 Aug 15 05:01 server1.index
-rw-r----- 1 mysql mysql 154 Aug 15 05:01 server1.000001

4. 执行SHOW BINARY LOGS;或SHOW MASTER LOGS;显示服务器的所有二进制日志。
5. 执行SHOW MASTER STATUS;命令获取当前二进制日志位置：

mysql> SHOW MASTER STATUS;
+----------------+----------+--------------+------------------+------------------+++++++++++++++++++++++++++++++++++++-+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| server1.000002 |     3273 |              |                  |                   |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

一旦server1.000001达到max_binlog_size（默认为 1GB），将创建一个新文件server1.000002，并将其添加到server1.index。您可以配置为使用SET @@global.max_binlog_size=536870912动态设置max_binlog_size。

可能存在不希望将语句复制到其他服务器的情况。为此，可以使用以下命令禁用该会话的二进制日志记录：

mysql> SET SQL_LOG_BIN = 0;

执行上一条语句后记录的所有 SQL 语句都不会记录到二进制日志中。这仅适用于该会话。

要启用，可以执行以下操作：

mysql> SET SQL_LOG_BIN = 1;

您可以使用FLUSH LOGS命令关闭当前二进制日志并打开新日志：

mysql> SHOW BINARY LOGS;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| server1.000001 |       154 |
+----------------+-----------+
1 row in set (0.00 sec)

mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW BINARY LOGS;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| server1.000001 |  198      |
| server1.000002 |  154      |
+----------------+-----------+
2 rows in set (0.00 sec)

二进制日志根据写入次数消耗大量空间。让它们保持原样可以很快填满磁盘。必须对其进行清理：

1. 使用binlog_expire_logs_seconds和expire_logs_days设置日志的到期时间。 如果要设置以天为单位的有效期，请设置expire_logs_days。例如，如果要删除所有超过两天的二进制日志，SET @@global.expire_logs_days=2。将该值设置为0将禁用自动到期。 如果您想获得更高的粒度，可以使用binlog_expire_logs_seconds变量，该变量以秒为单位设置二进制日志过期时间。 该变量和expire_logs_days的影响是累积的。例如，如果expire_logs_days为1且binlog_expire_logs_seconds为43200，则每 1.5 天清除一次二进制日志。这将产生与将binlog_expire_logs_seconds设置为129600并将expire_logs_days设置为 0 相同的结果。在 MySQL 8.0 中，binlog_expire_logs_seconds和expire_logs_days都必须设置为 0 才能禁用二进制日志的自动清除。

2. 要手动清除日志，请执行PURGE BINARY LOGS TO '<file_name>'。例如，存在server1.000001、server1.000002、server1.000003、server1.000004等文件，如果执行PURGE BINARY LOGS TO 'server1.000004'，则删除server1.000003之前的所有文件，且不会触动server1.000004：

mysql> SHOW BINARY LOGS;
+----------------+-----------+
| Log_name      | File_size |
+----------------+-----------+
| server1.000001 |       198 |
| server1.000002 |       198 |
| server1.000003 |       198 |
| server1.000004 |       154 |
+----------------+-----------+
4 rows in set (0.00 sec)

mysql> PURGE BINARY LOGS TO 'server1.000004';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW BINARY LOGS;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| server1.000004 |       154 |
+----------------+-----------+
1 row in set (0.00 sec)

您也可以执行命令PURGE BINARY LOGS BEFORE '2017-08-03 15:45:00'，而不是指定日志文件。你也可以用MASTER代替BINARY。 mysql> PURGE MASTER LOGS TO 'server1.000004'也有效。

3. 要删除所有二进制日志并重新从头开始，请执行RESET MASTER：

mysql> SHOW BINARY LOGS;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------|
| server1.000004 |       154 |
+----------------+-----------+
1 row in set (0.00 sec)

mysql> RESET MASTER;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW BINARY LOGS;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| server1.000001 |       154 |
+----------------+-----------+
1 row in set (0.00 sec)

Purging or expiring logs is a very unsafe method if you are using replication. The safe way to purge binary logs is to use the mysqlbinlogpurge script, which will be covered in Chapter 12, Managing Logs.

二进制日志可以用三种格式编写：

1. STATEMENT：记录实际的 SQL 语句。
2. ROW：记录对每行所做的更改。 例如，update 语句更新 10 行，所有 10 行的更新信息都写入日志。而在基于语句的复制中，只写入 update 语句。默认格式为ROW。
3. MIXED：MySQL 根据需要从STATEMENT切换到ROW。

有些语句在不同的服务器上执行时可能会导致不同的结果。例如，UUID()功能的输出因服务器而异。这些语句称为非确定性语句，对于基于语句的复制是不安全的。在这些情况下，当您设置MIXED格式时，MySQL 服务器会切换到基于行的格式。

参见https://dev.mysql.com/doc/refman/8.0/en/binary-log-mixed.html 了解更多关于不安全语句以及何时发生切换的信息。

您可以使用动态变量binlog_format设置格式，该变量具有全局和会话范围。将其设置为全局级别会使所有客户端使用指定的格式：

mysql> SET GLOBAL binlog_format = 'STATEMENT';

或：

mysql> SET GLOBAL binlog_format = 'ROW'; 

参见https://dev.mysql.com/doc/refman/8.0/en/replication-sbr-rbr.html 了解各种格式的优缺点。

1. MySQL 8.0 使用版本 2 的二进制日志行事件，MySQL 5.6.6 之前的 MySQL 服务器版本无法读取这些事件。将log-bin-use-v1-row-events设置为1使用版本 1，以便 MySQL 5.6.6 之前的版本可以读取。默认值为0。

mysql> SET @@GLOBAL.log_bin_use_v1_row_events=0;

2. 创建存储函数时，必须声明它是确定性的，或者不修改数据。否则，二进制日志记录可能不安全。默认情况下，要接受CREATE FUNCTION语句，必须明确指定DETERMINISTIC、NO SQL或READS SQL DATA中的至少一个。否则会发生错误：

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

您可以在例程中写入非确定性语句，但仍然声明为DETERMINISTIC（这不是一个好的做法），如果您想复制未声明为DETERMINISTIC的例程，可以设置log_bin_trust_function_creators变量： mysql> SET GLOBAL log_bin_trust_function_creators = 1;

参见https://dev.mysql.com/doc/refman/8.0/en/stored-programs-logging.html 了解存储程序的复制方式。

您可以使用mysqlbinlog实用程序（与 MySQL 一起提供）从二进制日志中提取内容，并将其应用于其他服务器。

使用各种二进制格式执行一些语句。当您将binlog_format设置为GLOBAL级别时，您必须断开并重新连接才能获得更改。如果要连接，请设置为SESSION级别。

更改为基于语句的复制（SBR：

mysql> SET @@GLOBAL.BINLOG_FORMAT='STATEMENT';
Query OK, 0 rows affected (0.00 sec)

更新几行：

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE salaries SET salary=salary*2 WHERE emp_no<10002;
Query OK, 18 rows affected (0.00 sec)
Rows matched: 18  Changed: 18  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

更改为基于行的复制（RBR）：

mysql> SET @@GLOBAL.BINLOG_FORMAT='ROW';
Query OK, 0 rows affected (0.00 sec)

更新几行：

mysql> BEGIN;Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE salaries SET salary=salary/2 WHERE emp_no<10002;Query OK, 18 rows affected (0.00 sec)
Rows matched: 18  Changed: 18  Warnings: 0

mysql> COMMIT;Query OK, 0 rows affected (0.00 sec)

更改为MIXED格式：

mysql> SET @@GLOBAL.BINLOG_FORMAT='MIXED';
Query OK, 0 rows affected (0.00 sec)

更新几行：

mysql> BEGIN;Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE salaries SET salary=salary*2 WHERE emp_no<10002;Query OK, 18 rows affected (0.00 sec)
Rows matched: 18  Changed: 18  Warnings: 0

mysql> INSERT INTO departments VALUES('d010',UUID());Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;Query OK, 0 rows affected (0.00 sec)

要显示server1.000001的内容，请执行以下操作：

shell> sudo mysqlbinlog /data/mysql/binlogs/server1.000001

您将获得类似以下内容的输出：

# at 226
#170815 12:49:24 server id 200  end_log_pos 312 CRC32 0x9197bf88  Query thread_id=5 exec_time=0 error_code=0
BINLOG '
~
~

在第一行中，# at后面的数字表示二进制日志文件中事件的起始位置（文件偏移量）。第二行包含在服务器上启动语句的时间戳。时间戳后接server id、end_log_pos、thread_id、exec_time和error_code。

• server id：事件发生服务器的server_id值（本例为200。
• end_log_pos：下一个事件的开始位置。
• thread_id：表示哪个线程执行了事件。
• exec_time：在主服务器上执行事件所花费的时间。在从机上，它是从机上的结束执行时间减去主机上的开始执行时间之差。这一差异可作为复制落后于主机的程度的指标。
• error_code：表示执行事件的结果。零表示没有发生错误。

1. 您在基于语句的复制中执行了UPDATE语句，该语句记录在二进制日志中。除了服务器，会话变量也保存在二进制日志中，以在从属服务器上复制相同的行为：

# at 226
#170815 13:28:38 server id 200  end_log_pos 324 CRC32 0x9d27fc78  Query thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1502803718/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,
@@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;

BEGIN
/*!*/;
# at 324
#170815 13:28:38 server id 200  end_log_pos 471 CRC32 0x35c2ba45  Query thread_id=8 exec_time=0 error_code=0
use `employees`/*!*/;
SET TIMESTAMP=1502803718/*!*/;
UPDATE salaries SET salary=salary*2 WHERE emp_no<10002 /*!*/;
# at 471
#170815 13:28:40 server id 200  end_log_pos 502 CRC32 0xb84cfeda  Xid = 53
COMMIT/*!*/;

2. 当使用基于行的复制时，将保存ROW，而不是语句，它是二进制格式，您无法读取。此外，您还可以观察长度，单个 update 语句生成了如此多的数据。检查提取行事件显示部分，说明如何查看二进制格式。

BEGIN
/*!*/;
# at 660
#170815 13:29:02 server id 200  end_log_pos 722 CRC32 0xe0a2ec74  Table_map:`employees`.`salaries` mapped to number 165
# at 722
#170815 13:29:02 server id 200  end_log_pos 1298 CRC32 0xf0ef8b05  Update_rows: table id 165 flags: STMT_END_F

BINLOG '
HveSWRPIAAAAPgAAANICAAAAAKUAAAAAAAEACWVtcGxveWVlcwAIc2FsYXJpZXMABAMDCgoAAAEBAHTsouA=HveSWR/IAAAAQAIAABIFAAAAAKUAAAAAAAEAAgAE///wEScAAFSrAwDahA/ahg/wEScAAKrVAQDahA/ahg/wEScAAFjKAwDahg/ZiA/wEScAACzlAQDahg/ZiA/wEScAAGgIBADZiA/Zig/wEScAADQEAgDZiA/Zig/wEScAAJAQBADZig/ZjA/wEScAAEgIAgDZig/ZjA/wEScAAEQWBADZjA/Zjg/wEScAACILAgDZjA/Zjg/wEScAABhWBADZjg/YkA/wEScAAAwrAgDZjg/YkA/wEScAAHSJBADYkA/Ykg/wEScAALpEAgDYkA/Ykg/wEScAAFiYBADYkg/YlA/wEScAACxMAgDYkg/YlA/wEScAAGijBADYlA/Ylg/wEScAALRRAgDYlA/Ylg/wEScAAFCxBADYlg/XmA/wEScAAKhYAgDYlg/XmA/wEScAADTiBADXmA/Xmg/wEScAABpxAgDXmA/Xmg/wEScAAATyBADXmg/XnA/wEScAAAJ5AgDXmg/XnA/wEScAACTzBADXnA/Xng/wEScAAJJ5AgDXnA/Xng/wEScAANQuBQDXng/WoA/wEScAAGqXAgDXng/WoA/wEScAAOAxBQDWoA/Wog/wEScAAPCYAgDWoA/Wog/wEScAAKQxBQDWog/WpA/wEScAANKYAgDWog/WpA/wEScAAIAaBgDWpA8hHk7wEScAAEANAwDWpA8hHk7wEScAAIAaBgDSwg8hHk7wEScAAEANAwDSwg8hHk4Fi+/w '/*!*/;
# at 1298
#170815 13:29:02 server id 200  end_log_pos 1329 CRC32 0xa6dac5dc  Xid = 56
COMMIT/*!*/;

3. 当使用MIXED格式时，UPDATE语句记录为 SQL，而INSERT语句记录为基于行的格式，因为INSERT有一个不确定的UUID()函数：

BEGIN
/*!*/;
# at 1499
#170815 13:29:27 server id 200  end_log_pos 1646 CRC32 0xc73d68fb  Query thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1502803767/*!*/;
UPDATE salaries SET salary=salary*2 WHERE emp_no<10002 /*!*/;
# at 1646
#170815 13:29:50 server id 200  end_log_pos 1715 CRC32 0x03ae0f7e  Table_map: `employees`.`departments` mapped to number 166
# at 1715
#170815 13:29:50 server id 200  end_log_pos 1793 CRC32 0xa43c5dac  Write_rows: table id 166 flags: STMT_END_F
BINLOG 'TveSWRPIAAAARQAAALMGAAAAAKYAAAAAAAMACWVtcGxveWVlcwALZGVwYXJ0bWVudHMAAv4PBP4QoAAAAgP8/wB+D64DTveSWR7IAAAATgAAAAEHAAAAAKYAAAAAAAEAAgAC//wEZDAxMSRkMDNhMjQwZS04MWJkLTExZTctODQxMC00MjAxMGE5NDAwMDKsXTyk '/*!*/;
# at 1793
#170815 13:29:50 server id 200  end_log_pos 1824 CRC32 0x4f63aa2e  Xid = 59
COMMIT/*!*/;

提取的日志可以通过管道传输到 MySQL 以重播事件。最好在重放 binlog 时使用 force 选项，因为如果它在某一点被卡住，它不会停止执行。稍后，您可以找出错误并手动修复数据。

shell> sudo mysqlbinlog /data/mysql/binlogs/server1.000001 | mysql -f -h <remote_host> -u <username> -p

或者，您可以保存到文件中，稍后再执行：

shell> sudo mysqlbinlog /data/mysql/binlogs/server1.000001 > server1.binlog_extract
shell> cat server1.binlog_extract | mysql -h <remote_host> -u <username> -p

通过指定位置，可以从二进制日志中提取部分数据。假设您要执行时间点恢复。假设在2017-08-19 12:18:00执行了一个DROP DATABASE命令，并且最新的可用备份是2017-08-19 12:00:00，您已经恢复了该备份。现在，您需要将数据从12:00:01恢复到2017-08-19 12:17:00。请记住，如果您提取完整日志，它还将包含DROP DATABASE命令，并将再次擦除您的数据。

您可以通过--start-datetime和--stop-datatime选项指定时间窗口来提取数据。

shell> sudo mysqlbinlog /data/mysql/binlogs/server1.000001 --start-datetime="2017-08-19 00:00:01"  --stop-datetime="2017-08-19 12:17:00" > binlog_extract

使用时间窗口的缺点是，您将错过灾难发生时发生的事务。为了避免这种情况，必须在二进制日志文件中使用事件的文件偏移量。

一致备份会保存 binlog 文件偏移量，直到它已备份为止。恢复备份后，必须从备份提供的偏移量中提取 binlog。在下一章中，您将了解有关备份的更多信息。

假设备份已给出偏移量471且DROP DATABASE命令在偏移量1793处执行。您可以使用--start-position和--stop-position选项在偏移之间提取日志：

shell> sudo mysqlbinlog /data/mysql/binlogs/server1.000001 --start-position=471  --stop-position=1793 > binlog_extract

确保DROP DATABASE命令不会再次出现在提取的 binlog 中。

使用--database选项，可以过滤特定数据库的事件。如果您多次给出此选项，则只会考虑最后一个选项。这对于基于行的复制非常有效。但对于基于语句的复制和MIXED而言，只有在选择默认数据库时才会给出输出。

以下命令从员工数据库中提取事件：

shell> sudo mysqlbinlog /data/mysql/binlogs/server1.000001 --database=employees > binlog_extract

如 MySQL 8 参考手册所述，假设二进制日志是通过使用基于语句的日志记录执行以下语句创建的：

mysql>
INSERT INTO test.t1 (i) VALUES(100);
INSERT INTO db2.t2 (j)  VALUES(200);

USE test;
INSERT INTO test.t1 (i) VALUES(101);
INSERT INTO t1 (i) VALUES(102);
INSERT INTO db2.t2 (j) VALUES(201);

USE db2;
INSERT INTO test.t1 (i) VALUES(103);
INSERT INTO db2.t2 (j) VALUES(202);
INSERT INTO t2 (j) VALUES(203);

mysqlbinlog --database=test没有输出前两条INSERT语句，因为没有默认数据库。

输出USE test之后的三条INSERT语句，但不输出USE db2之后的三条INSERT语句。

mysqlbinlog --database=db2没有输出前两条INSERT语句，因为没有默认数据库。

不输出USE测试后的三条INSERT语句，但输出USE db2测试后的三条INSERT语句。

在基于行的复制中，默认情况下显示二进制格式。要查看ROW信息，您必须将--verbose或-v选项传递给mysqlbinlog。行事件的二进制格式显示为注释形式的伪 SQL语句，行以###开头。您可以看到，对于每一行，单个UPDATE语句被重写为UPDATE语句：

shell>  mysqlbinlog /data/mysql/binlogs/server1.000001 --start-position=660 --stop-position=1298 --verbose
~
~
# at 660
#170815 13:29:02 server id 200  end_log_pos 722 CRC32 0xe0a2ec74     Table_map: `employees`.`salaries` mapped to number 165
# at 722
#170815 13:29:02 server id 200  end_log_pos 1298 CRC32 0xf0ef8b05     Update_rows: table id 165 flags: STMT_END_F

BINLOG '
HveSWRPIAAAAPgAAANICAAAAAKUAAAAAAAEACWVtcGxveWVlcwAIc2FsYXJpZXMABAMDCgoAAAEB
AHTsouA=
~
~
'/*!*/;
### UPDATE `employees`.`salaries`
### WHERE
###   @1=10001
###   @2=240468
###   @3='1986:06:26'
###   @4='1987:06:26'
### SET
###   @1=10001
###   @2=120234
###   @3='1986:06:26'
###   @4='1987:06:26'
~
~
### UPDATE `employees`.`salaries`
### WHERE
###   @1=10001
###   @2=400000
###   @3='2017:06:18'
###   @4='9999:01:01'
### SET
###   @1=10001
###   @2=200000
###   @3='2017:06:18'
###   @4='9999:01:01'
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*/;

如果只想查看没有二进制行信息的伪 SQL，请指定--base64-output="decode-rows"和--verbose：

shell>  sudo mysqlbinlog /data/mysql/binlogs/server1.000001 --start-position=660 --stop-position=1298 --verbose --base64-output="decode-rows"
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 660
#170815 13:29:02 server id 200  end_log_pos 722 CRC32 0xe0a2ec74     Table_map: `employees`.`salaries` mapped to number 165
# at 722
#170815 13:29:02 server id 200  end_log_pos 1298 CRC32 0xf0ef8b05     Update_rows: table id 165 flags: STMT_END_F
### UPDATE `employees`.`salaries`
### WHERE
###   @1=10001
###   @2=240468
###   @3='1986:06:26'
###   @4='1987:06:26'
### SET
###   @1=10001
###   @2=120234
###   @3='1986:06:26'
###   @4='1987:06:26'
~

假设您希望将生产服务器上的employees数据库的二进制日志恢复为开发服务器上的employees_dev。您可以使用--rewrite-db='from_name->to_name'选项。这将把所有出现的from_name重写为to_name。

要转换多个数据库，请多次指定该选项：

shell> sudo mysqlbinlog /data/mysql/binlogs/server1.000001 --start-position=1499 --stop-position=1646 --rewrite-db='employees->employees_dev'
~
# at 1499
#170815 13:29:27 server id 200  end_log_pos 1646 CRC32 0xc73d68fb     Query    thread_id=8    exec_time=0    error_code=0
use `employees_dev`/*!*/;
~
~
UPDATE salaries SET salary=salary*2 WHERE emp_no<10002
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
~

您可以看到使用了语句useemployees_dev/*!*/;。因此，在恢复时，所有更改都将应用于employees_dev database。

如 MySQL 参考手册所述：

When used together with the --database option, the --rewrite-db option is applied first, then the --database option is applied using the rewritten database name. The order in which the options are provided makes no difference in this regard. This means that, for example, if mysqlbinlog is started with --rewrite-db='mydb->yourdb' --database=yourdb, then all updates to any tables in databases mydb and yourdb are included in the output. On the other hand, if it is started with --rewrite-db='mydb->yourdb' --database=mydb, then mysqlbinlog outputs no statements at all: since all updates to mydb are first rewritten as updates to yourdb before applying the --database option, there remain no updates that match --database=mydb.

恢复二进制日志时，如果您不希望mysqlbinlog进程创建二进制日志，可以使用--disable-log-bin选项，这样就不会写入二进制日志：

shell> sudo mysqlbinlog /data/mysql/binlogs/server1.000001 --start-position=660 --stop-position=1298 --disable-log-bin > binlog_restore

您可以看到，SQL_LOG_BIN=0被写入binlog还原文件，这将阻止创建 binlog。

/*!32316 SET @OLD_SQL_LOG_BIN=@@SQL_LOG_BIN, SQL_LOG_BIN=0*/;

除了使用mysqlbinlog之外，您还可以使用SHOW BINLOG EVENTS命令来显示事件。

以下命令将在server1.000008二进制日志中显示事件。如果未指定LIMIT，则显示所有事件：

mysql> SHOW BINLOG EVENTS IN 'server1.000008' LIMIT 10; +----------------+-----+----------------+-----------+-------------+------------------------------------------+
| Log_name       | Pos | Event_type     | Server_id | End_log_pos | Info                                     |
+----------------+-----+----------------+-----------+-------------+------------------------------------------+
| server1.000008 |   4 | Format_desc    |       200 |         123 | Server ver: 8.0.3-rc-log, Binlog ver: 4 |
| server1.000008 | 123 | Previous_gtids |       200 |         154 |                                          |
| server1.000008 | 154 | Anonymous_Gtid |       200 |         226 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'     |
| server1.000008 | 226 | Query          |       200 |         336 | drop database company /* xid=4134 */     |
| server1.000008 | 336 | Anonymous_Gtid |       200 |         408 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'     |
| server1.000008 | 408 | Query          |       200 |         485 | BEGIN                                    |
| server1.000008 | 485 | Table_map      |       200 |         549 | table_id: 975 (employees.emp_details)    |
| server1.000008 | 549 | Write_rows     |       200 |         804 | table_id: 975 flags: STMT_END_F          |
| server1.000008 | 804 | Xid            |       200 |         835 | COMMIT /* xid=9751 */                    |
| server1.000008 | 835 | Anonymous_Gtid |       200 |         907 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'     |
+----------------+-----+----------------+-----------+-------------+------------------------------------------+
10 rows in set (0.00 sec)

还可以指定位置和偏移：

mysql> SHOW BINLOG EVENTS IN 'server1.000008' FROM 123 LIMIT 2,1; +----------------+-----+------------+-----------+-------------+--------------------------------------+
| Log_name       | Pos | Event_type | Server_id | End_log_pos | Info                                 |
+----------------+-----+------------+-----------+-------------+--------------------------------------+
| server1.000008 | 226 | Query      |       200 |         336 | drop database company /* xid=4134 */ |
+----------------+-----+------------+-----------+-------------+--------------------------------------+
1 row in set (0.00 sec)

您可以通过在my.cnf中指定--binlog-do-db=db_name选项来选择哪些数据库应该写入二进制日志。要指定多个数据库，您必须使用此选项的多个实例。由于数据库名称可以包含逗号，因此如果提供逗号分隔的列表，则该列表将被视为单个数据库的名称。您需要重新启动 MySQL 服务器以实现更改。

打开my.cnf并添加以下行：

shell> sudo vi /etc/my.cnf
[mysqld]
binlog_do_db=db1
binlog_do_db=db2

binlog-do-db上的行为从基于语句的日志记录更改为基于行的日志记录，就像mysqlbinlog实用程序中的--database选项一样。

在基于语句的日志记录中，只有那些语句被写入二进制日志，其中默认数据库（即，USE选择的数据库）被写入二进制日志。在使用binlog-do-db选项时应该非常小心，因为它在使用基于语句的日志记录时可能无法正常工作。浏览参考手册中提到的以下示例。

如果服务器以--binlog-do-db=sales启动，并且您发出以下语句，则不会记录UPDATE语句：

mysql> USE prices;
mysql> UPDATE sales.january SET amount=amount+1000;

这种只是检查默认数据库行为的主要原因是，仅从语句很难知道是否应该复制它。如果不需要，只检查默认数据库比检查所有数据库更快。

如果服务器以--binlog-do-db=sales启动，即使在设置--binlog-do-db时没有包含价格，也会记录以下UPDATE语句：

mysql> USE sales;
mysql> UPDATE prices.discounts SET percentage = percentage + 10;

因为UPDATE语句发出时，sales 是默认数据库，UPDATE被记录。

在基于行的日志记录中，仅限于数据库db_name。只记录属于db_name的表的更改；默认数据库对此没有影响。

与基于行的日志记录相比，基于语句的日志记录的--binlog-do-db处理方式的另一个重要区别在于引用多个数据库的语句。假设服务器以--binlog-do-db=db1启动，执行以下语句：

mysql> USE db1;
mysql> UPDATE db1.table1 SET col1 = 10, db2.table2 SET col2 = 20;

如果您使用的是基于语句的日志记录，那么对这两个表的更新都会写入二进制日志。但是，当使用基于行的格式时，只记录对table1的更改；table2在不同的数据库中，因此UPDATE不会对其进行更改。

类似地，您可以使用--binlog-ignore-db=db_name选项忽略数据库写入二进制日志。

有关更多信息，请参阅手册，网址为：https://dev.mysql.com/doc/refman/8.0/en/replication-rules.html 。

由于二进制日志占用更多空间，有时您可能希望更改二进制日志的位置，因此以下过程将有所帮助。仅更改log_bin是不够的，您必须移动所有二进制日志并使用新位置更新索引文件。mysqlbinlogmove实用程序通过自动化这些任务简化了您的工作。

应安装 MySQL 实用程序以使用mysqlbinlogmove脚本。安装步骤参见第 1 章、MySQL 8.0-安装升级。

1. 停止 MySQL 服务器：

shell> sudo systemctl stop mysql

2. 启动mysqlbinlogmove实用程序。如果要将二进制日志从/data/mysql/binlogs更改为/binlogs，应使用以下命令。如果您的基本名称不是默认名称，您必须通过--bin-log-base name选项提及您的基本名称：

shell> sudo mysqlbinlogmove --bin-log-base name=server1  --binlog-dir=/data/mysql/binlogs /binlogs
#
# Moving bin-log files...
# - server1.000001
# - server1.000002
# - server1.000003
# - server1.000004
# - server1.000005
#
#...done.
#

3. 编辑my.cnf文件并更新log_bin的新位置：

shell> sudo vi /etc/my.cnf
[mysqld]
log_bin=/binlogs

4. 启动 MySQL 服务器：

shell> sudo systemctl start mysql

The new location is updated in AppArmor or SELinux.

如果存在大量 BINLOG，服务器的停机时间将很高。为了避免这种情况，您可以使用--server选项重新定位所有二进制日志，但当前正在使用的日志除外（序列号较高）。然后停止服务器，使用前面的方法，重新定位最后一个二进制日志，这会更快，因为只有一个文件。然后您可以更改my.cnf并启动服务器。

例如：

shell> sudo mysqlbinlogmove --server=root:pass@host1:3306 /new/location