MySQL Binary Log

binary log包含用来描述数据库改变比如表创建操作或表数据改变的事件信息。除非使用基于行的日志，它还可能包含潜在可能发生改变的语句(例如，delete没有匹配的行记录)。binary log也包含关于每个语句更新数据所花费的时间。binary log有两个重要的目的:
.用于复制，主复制服务器上的binary log提供了被发往从服务器的数据改变的记录。主服务器将binary log中包含的事件发送给它的从服务器，从服务器执行这些事件使用相同的数据改变应用到从服务器。

.用于需要使用binary log来执行的特定恢复操作。在一个备份还原之后，在binary log中的事件记录了在备份之后所发生的改变，使用binary log来重新执行这些改变。这些事件从备份时间点起记录了数据库的更新。

当启用binary log后会使用服务器的性能稍稍有所下降。然而，binary log的好处就是能让你配置复制并且对于恢复操作来说比起这稍稍的性能下降更重要。

binary log通常对于意外停机具有弹性因为只有完成的事务会被记录或读回。写入binary log的语句中的密码由服务器重写，不会以明文形式出现。

为了启用binary log，使用–log-bin[=base_name]选项来启动服务器。如果base_name没有指定，默认名字是pid-file选项的值(它的默认名字是主机名)后面跟着-bin。如果给定了基本名，则服务器将文件写入数据目录中，除非给定的基本名包含一个前导绝对路径名以指定另一个目录。建议您显式地指定一个基本名称，而不是使用默认的主机名。

如果在日志名称中提供扩展名(例如，——log-bin=base_name.extension)，扩展名将被静默删除并忽略。

mysqld将数字扩展追加到二进制日志基名以生成二进制日志文件名。每次服务器创建一个新的日志文件时，这个数字都会增加，从而创建一个有序的文件序列。服务器每次启动或刷新日志时，都会在这个系列中创建一个新文件。当当前日志的大小达到max_binlog_size时，服务器还会自动创建一个新的二进制日志文件。如果使用大事务因为一个事务是以一个片段写入文件而不会跨文件写入所以binary log文件可能会超过max_binlog_size的大小。

为了保持对那些被使用的binary log文件的跟踪，mysqld也会创建一个binary log索引文件它包含所有被使用的binary log文件。默认情况下这里使用基本名作为binary 日志文件名并带有’.index’扩展名。你可以使用–log-bin-index[=file_name]选项来改变binary log索引文件名。在mysqld正在运行时不能手动编辑这个文件。

术语"binary log file"通常表示包含数据库事件的单独编号的日志文件。术语句"binary log"集体表示一组binary log文件和索引文件。

有super权限的客户端可以通过使用set sql_log_bin=0来对它的语句禁用binary log。

默认情况下，服务器会记录事件的长度以及事件本身，并使用它来验证事件是否被正确写入。也可以通过设置binlog_checksum系统变量让服务器对事件写checksums。当从binary log加读时，主服务器默认会使用事件长度，但如果启用了master_verify_checksum系统变量也会使用checksums。从服务器IO线和也会验证从主服务器所接收到的事件。如果启用了slave_sql_verify_checksum系统变量并可用可以使用从SQL线程使用chechsums。

服务器评估–binlog-do-db和–binlog-ignore-db选项的方式与–replicate-do-db和–replicate-ignore-db选项。

一个复制从服务器默认情况下不会将从复制主服务器所接收到的任何数据修改写入它的binary log。为了记录这些修改，可以在启动从服务器时使用–log-slave-updates选项外加–log-bin选项。在链式复制中，当一个从属节点也充当其他从属节点的主节点时，将执行此操作。

可以使用reset master语句或purge binary logs来删除所有binary log文件。

如果你将使用复制，直到你确定没有从服务器仍然需要使用它们之前你将不应该删除旧的binary log文件。例如如果你的从服务器同步没有落后三天，你可以在主服务器上执行mysqladmin flush-logs并且删除任何超过三天的日志。可以手动删了日志文件，但最好使用purgebinary logs，它能为你安全地更新binary log索引文件。

在一个语句或事务完成后但在任何锁被释放或任何提交完成前会立即写入binary log。这可以确保日志以提交顺序被记录。

对非事务性表的更新在执行后立即存储在二进制日志中。

在未提交事务中，对事务表比如InnoDB表的所有更新(update,delete或insert)会被缓存直到服务器收到commit语句为止。在这时，mysqld会在commit执行之前将整个事务写入binary log。

对于非事务表的修改不能被回滚。如果一个被回滚的事务包含对非事务表的修改，整个事务在最后用ROLLBACK语句记录，以确保复制了对这些表的修改。

当处理事务的线程开始时，它将为binlog_cache_size分配一个buffer来缓存语句。如果语句大于这个buffer，线程会打开一个临时文件来存储事务。当线程结束时临时文件会被删除。

binlog_cache_use状态变量显示了使用这个缓冲区(可能还有一个临时文件)来存储语句的事务数。binlog_cache_disk_use状态变量显示了这些事务中有多少事务实际上使用了临时文件。这两个变量可以被用来调整binlog_cache_size的大小使其足够大避免使用临时文件。

mysql> show variables like ‘binlog_cache_size’;
±------------------±--------+
| Variable_name | Value |
±------------------±--------+
| binlog_cache_size | 4194304 |
±------------------±--------+
1 row in set (0.00 sec)
max_binlog_cache_size系统变量(默认值为4GB，这也是最大值)可以被用来限制用于缓存多语句事务的总大小。如果事务大于这么多字节，则会失败并回滚。最小值是4096。

如果将使用binary log和基于行日志，并发插入比如create … select或insert … select语句会被转换为正常插入。这样做是为了确保您可以通过在备份操作期间应用日志来重新创建表的精确副本。如果使用基于语句的日志记录，则将原始语句写入日志。

默认情况下每次写入(sync_binlog=1)时binary log会被同步到磁盘。如果sync_binlog没有启用，操作系统或机器(不仅仅是MySQL服务器)崩溃， 二进制日志的最后一条语句可能会丢失。为了防止这个问题，启用sync_binlog系统变量来在每N个提交组之后同步二进制日志到磁盘。sync_binlog最安全的值是1(默认值)，但这也是最慢的。

例如，如果您使用InnoDB表，MySQL服务器处理一个COMMIT语句，它会按顺序将许多准备好的事务写到二进制日志中，同步二进制日志，然后将这个事务提交到InnoDB中。如果服务器在这两个操作之间崩溃，事务会在重启时由InnoDB回滚，但仍然存在于二进制日志中。假设——innodb_support_xa设置为1 为默认值,这个问题已经解决了，。虽然这个选项与InnoDB中XA事务的支持有关，但它也确保了二进制日志和InnoDB数据文件是同步的。为了提供更大程度的安全性，MySQL服务器还应该配置为在提交事务之前将二进制日志和InnoDB日志同步到磁盘。默认情况下，InnoDB日志是同步的，sync_binlog=1可用于同步二进制日志。这个选项的效果是，在崩溃后重新启动时，在执行事务回滚之后，MySQL服务器扫描最新的二进制日志文件来收集事务xid值，并计算二进制日志文件中的最后一个有效位置。然后，MySQL服务器告诉InnoDB完成所有已成功写入二进制日志的事务，并将二进制日志截断到最后一个有效位置。这确保了二进制日志反映了InnoDB表的准确数据，因此从数据库与主数据库保持同步，因为它没有收到一条回滚的语句。

如果MySQL服务器在崩溃恢复时发现二进制日志比应该的短，那么它至少缺少一个成功提交的InnoDB事务。如果sync_binlog=1和磁盘/文件系统在被请求时执行实际的同步(有些没有)，则不会发生这种情况，因此服务器将打印一条错误消息The binary log file_name is shorter than its expected size。在这种情况下，这个二进制日志不正确，应该从主数据的新快照重新启动复制。

以下系统变量的会话值被写入二进制日志，并在解析二进制日志时由复制从属服务器执行:

.sql_mode(除了NO_DIR_IN_CREATE模式不被复制)
.foreign_key_checks
.unique_checks
.character_set_client
.collation_connection
.collation_database
.collation_server
.sql_auto_is_null
binary log格式
服务器使用几种日志格式来记录二进制日志中的信息。所使用的确切格式取决于所使用的MySQL版本。有三种日志格式:
.MySQL中的复制功能最初是基于从主到从的SQL语句传播。这称为基于语句的日志记录。通过使用——binlog-format=STATEMENT启动服务器，可以使用这种格式。
.在基于行的日志记录中，主进程将事件写入二进制日志，以指示各个表行是如何受到影响的。因此，表总是使用主键来确保有效地标识行，这一点很重要。通过使用——binlogformat=ROW启动服务器，可以使它使用基于行的日志记录。
.还有第三个选项:混合日志记录。对于混合日志记录，默认使用基于语句的日志记录，但是在某些情况下，日志记录模式会自动切换到基于行，如下所述。通过使用选项——binlogformat=MIXED启动mysqld，可以使MySQL显式地使用混合日志记录好坏参半。日志格式也可以由所使用的存储引擎设置或限制。这有助于消除在使用不同存储引擎的主从之间复制某些语句时出现的问题。

对于基于语句的复制，复制不确定语句可能会有问题。在决定给定的语句对于基于语句的复制是否安全时，MySQL决定是否可以保证使用基于语句的日志来复制语句。如果MySQL不能做到这一点，它会将该语句标记为可能不可靠，并发出警告。

Statement may not be safe to log in statement format.
可以使用MySQL的基于行的复制来避免这些问题。

设置Binary log格式
可以在启动MySQL服务器时使用–binlog-format=type来显式选项binary log格式，type支持以下取值:
.STATEMENT基于语句记录日志
.ROW基于行记录记录日志
.MIXED使用混合格式记录日志

日志格式也可以在运行时切换。设置binlog_format系统变量的全局值，以指定更改之后连接的客户端的日志格式

mysql> select @@binlog_format;
±----------------+
| @@binlog_format |
±----------------+
| MIXED |
±----------------+
1 row in set (0.00 sec)
mysql> SET GLOBAL binlog_format = ‘STATEMENT’;
Query OK, 0 rows affected (0.00 sec)
[mysql@localhost ~]$ mysql -uroot -pxxzx7817600 mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.26-log Source distribution
Copyright © 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> select @@binlog_format;
±----------------+
| @@binlog_format |
±----------------+
| STATEMENT |
±----------------+
1 row in set (0.00 sec)
mysql> SET GLOBAL binlog_format = ‘ROW’;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@binlog_format;
±----------------+
| @@binlog_format |
±----------------+
| MIXED |
±----------------+
1 row in set (0.00 sec)
[mysql@localhost ~]$ mysql -uroot -pxxzx7817600 mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.26-log Source distribution
Copyright © 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> select @@binlog_format;
±----------------+
| @@binlog_format |
±----------------+
| ROW |
±----------------+
1 row in set (0.00 sec)
mysql> SET GLOBAL binlog_format = ‘MIXED’;
Query OK, 0 rows affected (0.00 sec)
[mysql@localhost ~]$ mysql -uroot -pxxzx7817600 mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.7.26-log Source distribution
Copyright © 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> select @@binlog_format;
±----------------+
| @@binlog_format |
±----------------+
| MIXED |
±----------------+
1 row in set (0.00 sec)
单个客户端通过设置binlog_format会话值可以控制自己语句的日志格式:

mysql> SET SESSION binlog_format = ‘STATEMENT’;
mysql> SET SESSION binlog_format = ‘ROW’;
mysql> SET SESSION binlog_format = ‘MIXED’;
每个MySQL服务器都可以设置自己的并且只有自己的二进制日志格式(无论binlog_format是用全局还是会话范围设置的)。这意味着更改复制主服务器上的日志格式并不会导致从服务器更改日志格式以匹配。(使用语句模式时，不复制binlog_format系统变量;当使用混合或行日志记录模式时，它被复制，但被从服务器忽略)。在复制正在进行时更改主服务器上的二进制日志格式，或者不更改从服务器上的日志格式，都可能导致复制失败，出现错误比如:

Error executing row event: ‘Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT= STATEMENT.’
为了修改全局或会话级binlog_format值，必须要有super权限。客户端可能希望在每个会话的基础上设置二进制日志记录的原因有几个:
.对数据库进行许多小更改的会话可能希望使用基于行的日志记录
.执行与WHERE子句中的许多行匹配的更新的会话可能希望使用基于语句的日志记录，因为记录几个语句比记录许多行更有效
.有些语句需要在主服务器上执行大量时间，但是只修改了几行。因此，使用基于行的日志记录来复制它们可能是有益的

在出现以下情况时您无法在运行时切换复制格式时：
.从存储的函数或触发器中
.如果启用了NDB存储引擎
.如果会话当前处于基于行的复制模式，并且有打开的临时表

尝试在这些情况中切换格式会导致错误.

如果当前正在使用InnoDB表并且事务隔离级别为read committed或read uncommitted时，只能使用基于行的日志记录。它可以修改日志格式为基于语句的日志，但是在运行时这样做会导致很快就会出错因为InnoDB不能再执行插入。

当存在任何临时表时，不建议在运行时切换复制格式，因为仅在使用基于语句的复制时才记录临时表，而使用基于行的复制时则不记录临时表。对于混合复制，通常会记录临时表;除非使用用户定义函数(udf)和UUID()函数。

将二进制日志格式设置为ROW后，使用基于行的格式将许多更改写入二进制日志。但是，有些更改仍然使用基于语句的格式。例如包括所有DDL(数据定义语言)语句，如CREATE TABLE、ALTER TABLE或DROP TABLE。

对于能够进行基于行的复制的服务器，可以使用——binlog-row-event-max-size选项。行以块的形式存储到二进制日志中，块的大小(以字节为单位)不超过此选项的值。该值必须是256的倍数。默认值是8192。

在使用基于语句的日志记录进行复制时，如果语句设计为数据修改是不确定的，则主从上的数据可能会有所不同;也就是说，它由查询优化器决定。通常，即使在复制之外，这也不是一个好的实践。

Mixed Binary Logging Format
当以混合日志格式运行时，服务器在以下条件下自动从基于语句的日志切换到基于行的日志:
.当一个函数包含UUID()。
.当一个或多个表使用AUTO_INCREMENT列被更新并且有一个触发器或存储函数被调用时，像其它不安全语句一样，如果binlog_format=STATEMENT会生成警告。
.当视图主体需要基于行的复制时，创建视图的语句也会使用它。例如，当创建视图的语句使用UUID()函数时，就会发生这种情况
.当涉及到对UDF的调用时
.如果一个语句是按行记录的，并且执行该语句的会话有任何临时表，则按行记录将用于所有后续语句(访问临时表的语句除外)，直到该会话使用的所有临时表都被删除为止。不管是否实际记录了任何临时表，his都是正确的。无法使用基于行的格式记录临时表;因此，一旦使用了基于行的日志记录，使用该表的所有后续语句都是不安全的。服务器通过将会话期间执行的所有语句视为不安全的，直到会话不再持有任何临时表，从而近似于这种情况。
.使用FOUND_ROWS()或ROW_COUNT()时
.当使用USER()、CURRENT_USER()或CURRENT_USER时
.当一个语句引用一个或多个系统变量时

例外:以下系统变量在与会话范围(仅)一起使用时，不会导致日志格式切换

auto_increment_increment
auto_increment_offset
character_set_client
character_set_connection
character_set_database
character_set_server
collation_connection
collation_database
collation_server
foreign_key_checks
identity
last_insert_id
lc_time_names
pseudo_thread_id
sql_auto_is_null
time_zone
timestamp
unique_checks
.当其中一个表是mysql数据库中的日志表时
.使用LOAD_FILE()函数时

注意:
如果您试图使用基于语句的日志记录来执行应该使用基于行的日志记录的语句，则会生成一个警告。该警告在客户机中(在SHOW WARNINGS的输出中)和通过mysqld错误日志显示。每次执行这样的语句时，都会向SHOW WARNINGS表添加一个警告。但是，只有为每个客户端会话生成警告的第一个语句被写入错误日志，以防止日志泛滥。

除了上面的决策之外，各个引擎还可以确定在更新表中的信息时使用的日志格式。单个引擎的日志功能可以定义如下:
.如果一个引擎支持基于行的日志记录，则该引擎被认为是支持行日志记录的。
.如果一个引擎支持基于语句的日志记录，那么该引擎就被称为支持语句日志记录

给定的存储引擎可以支持日志格式中的一种或两种。下表列出了每个引擎支持的格式:

存储引擎 支持基于行日志记录 支持基于语句日志记录
ARCHIVE Yes Yes
BLACKHOLE Yes Yes
CSV Yes Yes
EXAMPLE Yes NO
FEDERATED Yes Yes
HEAP Yes Yes
InnoDB Yes 当事务隔离级别为REPEATABLE READ或SERIALIZABLE时为Yes
否则为No
MyISAM Yes Yes
MERGE Yes Yes
NDB Yes No
要记录语句和使用的日志模式是根据语句的类型(安全的、不安全的或二进制注入的)、二进制日志格式(语句、行或混合的)和存储引擎的日志功能(语句支持、行支持、两者都支持或两者都不支持)来确定的。(二进制注入指的是记录必须使用行格式记录的更改。)

语句可能被记录，也可能没有警告;失败的语句不会被记录，但会在日志中生成错误。这在下面的决策表中显示，其中SLC表示“支持语句日志记录”，RLC表示“支持行日志记录”。

当确定产生一个警告时，就会产生一个标准的MySQL警告(可以使用SHOW WARNINGS)。信息也被写入mysqld错误日志。对于每个客户端连接的每个错误实例，只记录一个错误，以防止日志泛滥。日志消息包括尝试的SQL语句。

如果从服务器启动时使用log_error_verbosity设置来显示警告，从服务器会打印消息到错误日志中来提供状态信息，比如例如二进制日志和中继日志坐标，它在何处开始工作，何时切换到另一个中继日志，何时在断开连接后重新连接，对于基于语句的日志记录不安全的语句，等等。

改变mysql数据库表的日志格式
mysql数据库授权表中的内容可以直接(使用insert或delete)或间接(使用grant或create user)修改。影响mysql数据库表的语句会使用以下规则写入binary log:
.根据设置的binlog_format系统变量对mysql数据库表进行数据修改的维护语句直接被记录。这与语句有关比如insert,update,delete,replace,do,load data infile,select和truncate table。

.对mysql数据库进行修改的语句会间接地作为语句被记录而不管binlog_format的设置。这与语句有关比如grant,revoke,set password,rename user,create(除了create table … select之外的所有形式),alter(所有形式)和drop(所有形式)。

create table … select它是由数据定义和数据维护语句组成的。create table部分使用语句格式被记录而select部分根据binlog_format设置情况来记录。