[译]MySQL 5.7 Reference Manual::The Binary Log

最新推荐文章于 2024-04-18 16:17:49 发布

weixin_34195142

最新推荐文章于 2024-04-18 16:17:49 发布

阅读量105

点赞数

文章标签：数据库 python 操作系统

原文链接：https://my.oschina.net/ij5IYLKW/blog/818672

版权

2019独角兽企业重金招聘Python工程师标准>>>

6.4.4 The Binary Log（二进制日志）

6.4.4.1 Binary Logging Formats（二进制日志格式 Row/Statement/Mixed）

6.4.4.2 Setting The Binary Log Format（如果设置二进制日志格式）

6.4.4.3 Mixed Binary Logging Format（Mixed的二进制格式，主要讲Mysql什么情况会选择Row/Statement）

6.4.4.4 Logging Format for Changes to mysql Database Tables（数据库表变化与二进制日志格式，主要讲DDL和DML等修改在二进制日志中以什么样的格式记录）

The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data. The binary log has two important purposes:

翻译：二进制日志内容包括描述数据库变更的事件（非变更的查询不记录），譬如：创建表，改变表数据的操作。除非二进制日志的格式是ROW（为ROW格式的日志文件会包含哪些行被修改了，所以如果是0行的话就不会记录），否则它甚至包括哪些可能会对数据库进行修改的表达式(举个例子，一个返回受影响行为0的删除语句)。二进制日志文件还包含了执行修改数据所花的时间。二进制日志文件有两个重要的目的：

For replication, the binary log on a master replication server provides a record of the data changes to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master. See Section 18.2, “Replication Implementation”.
翻译：为主从复制，二进制日志文件提供在主服务器上受影响的记录发送到从服务器上。主服务器通过二进制日志发送它发生的事件给从服务器，从服务器接收二进制日志执行我们在主服务器上做的操作。详细请看Section 18.2, “Replication Implementation”.
Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup. See Section 8.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”.
翻译：某些数据的修复操作需要二进制日志。在备份还原后，可以根据记录事件的二级制日志文件对备份后的数据进行重做。这些可以让数据库从备份点到最新状态。详细请看Section 8.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”.

The binary log is not used for statements such as SELECT or SHOW that do not modify data. To log all statements (for example, to identify a problem query), use the general query log. See Section 6.4.3, “The General Query Log”.

翻译：二进制日志不用来记录那些非变更的操作，譬如：SELECT或SHOW，如果要记录所有的表达式（举个例子，查询一个错误的查询），请使用一般的查询日志（Mysql的查询日志、慢查询日志等），详细请看Section 6.4.3, “The General Query Log”.

Running a server with binary logging enabled makes performance slightly slower. However, the benefits of the binary log in enabling you to set up replication and for restore operations generally outweigh this minor performance decrement.

翻译：开启二进制日志会对性能有稍微的影响。但是它可以给你带来主从复制、恢复一般的操作等好处，相比之下一些性能的影响是可以背接手的。

The binary log is generally resilient to unexpected halts because only complete transactions are logged or read back. See Section 18.3.2, “Handling an Unexpected Halt of a Replication Slave” for more information.

翻译：二进制文件一般能修复一个意外的主从复制的终止，因为仅当一个完整的事务才会被记录二进制日志或重读(如果事务不完整就直接回滚了，也不需要再发送slave)。详细请看 Section 18.3.2, “Handling an Unexpected Halt of a Replication Slave”

Passwords in statements written to the binary log are rewritten by the server not to occur literally in plain text. See also Section 7.1.2.3, “Passwords and Logging”.

翻译：关于密码的表达式写入二进制日志文件的时候会被Mysql重写，而不会直接以文本方式写入，详细请看Section 7.1.2.3, “Passwords and Logging”.

The following discussion describes some of the server options and variables that affect the operation of binary logging. For a complete list, see Section 18.1.6.4, “Binary Logging Options and Variables”.

翻译：一些关于Mysql选项或参数会影响二进制日志文件记录的完整讨论请细看：Section 18.1.6.4, “Binary Logging Options and Variables”.

To enable the binary log, start the server with the --log-bin[=base_name] option. If no base_name value is given, the default name is the value of the pid-file option (which by default is the name of host machine) followed by -bin. If the base name is given, the server writes the file in the data directory unless the base name is given with a leading absolute path name to specify a different directory. It is recommended that you specify a base name explicitly rather than using the default of the host name; see Section B.5.7, “Known Issues in MySQL”, for the reason.

翻译：开启二进制日志只需要添加 --log-bin[=base_name] 参数，如果不给 base_name 值，默认的名字是 pid-file 的 -bin.选项（它默认的名字是机器host）。如果给了base name，除非base name设置绝对路径，否则Mysql会在Mysql安装目录的data文件夹下写入二进制日志。这里推荐的是你明确的定义 base name，这会比默认的使用host name 要好一点，理由请看Section B.5.7, “Known Issues in MySQL”。

If you supply an extension in the log name (for example, --log-bin=base_name.extension), the extension is silently removed and ignored.

翻译：如果你提供一个带有扩展名的 log name（比如：--log-bin=base_name.extension），那么.extension后缀会被移除和忽略。

mysqld appends a numeric extension to the binary log base name to generate binary log file names. The number increases each time the server creates a new log file, thus creating an ordered series of files. The server creates a new file in the series each time it starts or flushes the logs. The server also creates a new binary log file automatically after the current log's size reaches max_binlog_size. A binary log file may become larger than max_binlog_size if you are using large transactions because a transaction is written to the file in one piece, never split between files.

翻译：Mysqld会给生成的二进制日志文件追加一个数字格式的扩展名，每次创建新的二进制日志文件的时候此数格式的后缀都会自增，这样就创建连续的文件集。Mysql在开始或Flush Log的时候创建一个新的连续日志文件。Mysql也会在一个 max_binlog_size 大小的日志文件快写满的时候自动创建新日志文件。如果你正在操作一个快超过 max_binlog_size 的大事务也会创建一个日志文件，因为事务的二进制日志写入始终会在一个文件，不会分割在两个文件中。

To keep track of which binary log files have been used, mysqld also creates a binary log index file that contains the names of all used binary log files. By default, this has the same base name as the binary log file, with the extension '.index'. You can change the name of the binary log index file with the --log-bin-index[=file_name] option. You should not manually edit this file while mysqld is running; doing so would confuse mysqld.

翻译：为了追踪哪个二进制日志文件正在被使用，mysqld会创建一个二进制日志文件的索引文件，索引文件包括所有使用过的二进制日志文件名称。默认的他的名字和二进制的 base name一样，但会以 '.index'后缀结尾。你可以通过参数 --log-bin-index[=file_name] 设置。但不能在mysqld运行的时候手动的编辑这个文件，mysqld会拒绝你这么做。

The term “binary log file” generally denotes an individual numbered file containing database events. The term “binary log” collectively denotes the set of numbered binary log files plus the index file.

翻译：通常说的“binary log file”一般指的是一个记录着数据库事件且带编号的文件。而“binary log” 一般指的是多个“binary log file“再加上“index file“的集合。

A client that has the SUPER privilege can disable binary logging of its own statements by using a SET sql_log_bin=0 statement. See Section 6.1.5, “Server System Variables”.

翻译：如果我们使用有权限的用户执行了 SET sql_log_bin=0 表达式就可以把binary log 禁止掉。详细请看Section 6.1.5, “Server System Variables”

By default, the server logs the length of the event as well as the event itself and uses this to verify that the event was written correctly. You can also cause the server to write checksums for the events by setting the binlog_checksum system variable. When reading back from the binary log, the master uses the event length by default, but can be made to use checksums if available by enabling the master_verify_checksum system variable. The slave I/O thread also verifies events received from the master. You can cause the slave SQL thread to use checksums if available when reading from the relay log by enabling the slave_sql_verify_checksum system variable.

翻译：默认情况下，Mysql记录事件的长度以及事件的本身信息，并且可以通过这些信息验证事件是否被记录正确。你也可以使用 binlog_checksum 参数让Mysql记录下事件的checksums。当你从二进制日志中重读的时候Master服务器默认使用事件长度去校验，但是如果你开启了记录checksums的话，你也可以使用master_verify_checksum 参数让Master服务器使用checksums校验。Slave服务器的I/O线程也可以从Master服务器接收/验证事件。如果你开启了上面的步骤并且配置了slave_sql_verify_checksum 参数，那么你也可以在Slave服务器上使用checksums读取/校验Slave上的relay log。

The format of the events recorded in the binary log is dependent on the binary logging format. Three format types are supported, row-based logging, statement-based logging and mixed-base logging. The binary logging format used depends on the MySQL version. For general descriptions of the logging formats, see Section 6.4.4.1, “Binary Logging Formats”. For detailed information about the format of the binary log, see MySQL Internals: The Binary Log.

翻译：事件记录的格式由二进制日志格式决定。三种格式被支持，它们分别是 Row、Statement、Mixed。这三种二进制日志的格式的使用依赖与Mysql的版本。简单的了解日志格式请看Section 6.4.4.1, “Binary Logging Formats”. 详细的日志格式信息请看 MySQL Internals: The Binary Log.

The server evaluates the --binlog-do-db and --binlog-ignore-db options in the same way as it does the --replicate-do-db and --replicate-ignore-db options. For information about how this is done, see Section 18.2.5.1, “Evaluation of Database-Level Replication and Binary Logging Options”.

翻译：--binlog-do-db 、--binlog-ignore-db 和 --replicate-do-db 、--replicate-ignore-db 在一个地方进行讲解，详细请看Section 18.2.5.1, “Evaluation of Database-Level Replication and Binary Logging Options”.

A replication slave server by default does not write to its own binary log any data modifications that are received from the replication master. To log these modifications, start the slave with the --log-slave-updates option in addition to the --log-binoption (see Section 18.1.6.3, “Replication Slave Options and Variables”). This is done when a slave is also to act as a master to other slaves in chained replication.

翻译：默认情况下Slave服务器不会把从Master服务器复制过来的binlog写进自己的binlog。如果你要这样做，请在Slave服务器上面使用--log-slave-updates 参数，并且添加 --log-bin参数（详细Section 18.1.6.3, “Replication Slave Options and Variables”）。这个步骤一般在同为Slave服务器又是Master服务器的时候使用。

You can delete all binary log files with the RESET MASTER statement, or a subset of them with PURGE BINARY LOGS. See Section 14.7.6.6, “RESET Syntax”, and Section 14.4.1.1, “PURGE BINARY LOGS Syntax”.

翻译：你可以使用 RESET MASTER 参数删除所有二进制日志文件，或者使用 PURGE BINARY LOGS 删除某些二进制日志，详细请看Section 14.7.6.6, “RESET Syntax”, and Section 14.4.1.1, “PURGE BINARY LOGS Syntax”.

If you are using replication, you should not delete old binary log files on the master until you are sure that no slave still needs to use them. For example, if your slaves never run more than three days behind, once a day you can execute mysqladmin flush-logs on the master and then remove any logs that are more than three days old. You can remove the files manually, but it is preferable to use PURGE BINARY LOGS, which also safely updates the binary log index file for you (and which can take a date argument). See Section 14.4.1.1, “PURGE BINARY LOGS Syntax”.

翻译：当你使用binlog复制的时候，你可以在确定从服务器不需要某些binlog的情况下删除某些老的binlog文件。举个例子，如果你的Slave服务器只运行不超过三天，那么你可以每天在Master执行一次mysqladmin flush-logs 然后移除超过三天的日志。你可以手动的删除文件，但是更好的做法是使用 PURGE BINARY LOGS,它能够为你更安全的更新二进制日志索引文件，详细请看Section 14.4.1.1, “PURGE BINARY LOGS Syntax”.

You can display the contents of binary log files with the mysqlbinlog utility. This can be useful when you want to reprocess statements in the log for a recovery operation. For example, you can update a MySQL server from the binary log as follows:

翻译：你可以用mysqlbinlog工具查看Binlog文件的内容。当你需要重新执行表达式来恢复数据的时候它是非常有用的。举个例子，你可以像下面一样通过Binlog 更新Mysql服务：

shell> mysqlbinlog log_file | mysql -h server_name

mysqlbinlog also can be used to display replication slave relay log file contents because they are written using the same format as binary log files. For more information on the mysqlbinlog utility and how to use it, see Section 5.6.7, “mysqlbinlog — Utility for Processing Binary Log Files”. For more information about the binary log and recovery operations, see Section 8.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”.

翻译：你也可以用mysqlbinlog 来查看你复制到Slave服务器的Relay log文件内容，因为它们的格式都是一样的。查看更多如何使用mysqlbinlog 工具，请看Section 5.6.7, “mysqlbinlog — Utility for Processing Binary Log Files”.查看更多关于如何使用bin log恢复数据，请看Section 8.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”.

Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done. This ensures that the log is logged in commit order.

翻译：二进制日志在锁释放或任何事物提交完成之前就理科写入。这样可以确保日志的记录和事物提交按次序来的。

Updates to nontransactional tables are stored in the binary log immediately after execution.

翻译：对于那些不支持事物的表（如MyIsam引擎等）的时候会在执行修改之后立刻记录Binlog（所以说二进制日志文件的记录与具体引擎无关，所有引擎就会记录）

Within an uncommitted transaction, all updates (UPDATE, DELETE, or INSERT) that change transactional tables such as InnoDB tables are cached until a COMMIT statement is received by the server. At that point, mysqld writes the entire transaction to the binary log before the COMMIT is executed.

翻译：对于一个未提交的事务表（如InnoDB），所有修改(UPDATE, DELETE, or INSERT)都会被缓存下来，直到Mysql接受到事务Commit的指令。在这点上mysqld 会在Commit之前把整个事务都写进Bin log（通常指的是开事务和提交事务的时间相差比较长的时候）

Modifications to nontransactional tables cannot be rolled back. If a transaction that is rolled back includes modifications to nontransactional tables, the entire transaction is logged with a ROLLBACK statement at the end to ensure that the modifications to those tables are replicated.

翻译：修改一个不支持事务的表将不会被回滚。如果将roll back的事务中包含修改了一个不支持事务的表，那么在回滚之前请确认这些修改是否已经被复制到Slave。

When a thread that handles the transaction starts, it allocates a buffer of binlog_cache_size to buffer statements. If a statement is bigger than this, the thread opens a temporary file to store the transaction. The temporary file is deleted when the thread ends.

翻译：在一个线程开始处理一个事务的时候，它会分配到一份缓存用来缓存事务的表达式，使用binlog_cache_size 控制缓存的大小。如果一个事务的表达式比binlog_cache_size 还大，那么线程会开启一个临时文件用来存储这个事务，临时文件在线程结束时会删掉。

The Binlog_cache_use status variable shows the number of transactions that used this buffer (and possibly a temporary file) for storing statements. The Binlog_cache_disk_use status variable shows how many of those transactions actually had to use a temporary file. These two variables can be used for tuning binlog_cache_size to a large enough value that avoids the use of temporary files.

翻译：Binlog_cache_use 的参数可以看多少大事务为了记录使用了缓存（和可能存在临时文件）。而Binlog_cache_disk_use 可以查看实际上多少事务必须使用临时文件。这两个参数可以帮助我们知道该对binlog_cache_size 设置多大的值以避免使用临时文件。

The max_binlog_cache_size system variable (default 4GB, which is also the maximum) can be used to restrict the total size used to cache a multiple-statement transaction. If a transaction is larger than this many bytes, it fails and rolls back. The minimum value is 4096.

翻译： max_binlog_cache_size 系统参数（默认是4GB，也是最大值）控制多个表达式事务能使用缓存的总和。如果一个事务比这个值还大，那么它将失败并且被回滚。这个最小值是4096.

If you are using the binary log and row based logging, concurrent inserts are converted to normal inserts for CREATE ... SELECT or INSERT ... SELECT statements. This is done to ensure that you can re-create an exact copy of your tables by applying the log during a backup operation. If you are using statement-based logging, the original statement is written to the log.

翻译：如果使用ROW格式的二进制日志文件，并发的插入会被转换成正常的insert、create表达式（就是会记录你在Master生成的值，包括主键）。这样做是为了能确保在Slave库中可以创建精确的副本。而如果使用Statement的格式记录Binlog，只会写入原始sql语句。（之所以Statement可能不是精确的副本，是因为如果主键是自增的情况下，并发下可能导致复制到Slave库的数据主键和Master中不一致）

The binary log format has some known limitations that can affect recovery from backups. See Section 18.4.1, “Replication Features and Issues”.

翻译：一些关于二进制日志的格式会导致从备份中恢复数据的错误或影响，请看Section 18.4.1, “Replication Features and Issues”.

Binary logging for stored programs is done as described in Section 22.7, “Binary Logging of Stored Programs”.

翻译：一些关于二进制日志为完整的存储程序的描述请看 Section 22.7, “Binary Logging of Stored Programs”.

Note that the binary log format differs in MySQL 5.7 from previous versions of MySQL, due to enhancements in replication. See Section 18.4.2, “Replication Compatibility Between MySQL Versions”.

翻译：请注意二进制日志格式在Mysql5.7与之前版本的不同点，这些不同点都是为了增强Mysql的复制功能，请看Section 18.4.2, “Replication Compatibility Between MySQL Versions”.

Writes to the binary log file and binary log index file are handled in the same way as writes to MyISAM tables. See Section B.5.3.4, “How MySQL Handles a Full Disk”.

翻译：在MyISAM 引擎表中以同样的方式写入Bin log以及Bin index log。详细请看Section B.5.3.4, “How MySQL Handles a Full Disk”.

As of MySQL 5.7.7, the binary log is synchronized to disk at each write by default (sync_binlog=1). Prior to MySQL 5.7.7, it is not (sync_binlog=0). So, prior to MySQL 5.7.7, if the operating system or machine (not only the MySQL server) crashes, there is a chance that the last statements of the binary log are lost. To prevent this, use the sync_binlog system variable to synchronize the binary log to disk after every N commit groups. See Section 6.1.5, “Server System Variables”. The safest value forsync_binlog is 1, but this is also the slowest. Even with sync_binlog set to 1, there is still the chance of inconsistency between the table content and binary log content in case of a crash.

翻译：在MySQL 5.7.7中二进制日志文件默认以同步的写入磁盘（sync_binlog=1），在MySQL 5.7.7之前它都是以异步方式（sync_binlog=0）。所以如果在MySQL 5.7.7之前操作系统或机器崩溃是有可能丢失最后执行语句的Bin log的。为了解决这个问题，可以使用sync_binlog 系统参数在每N个事务的提交组（前面说过为了提高吞吐量Mysql以组的方式提交binlog）中同步的刷入磁盘，详细请看Section 6.1.5, “Server System Variables”. 这个安全的sync_binlog 值是1，但是这样做对性能也会有影响。尽管设置sync_binlog 为1，当宕机时二进制日志和表内容之前仍然是有可能不一致的。

For example, if you are using InnoDB tables and the MySQL server processes a COMMIT statement, it writes many prepared transactions to the binary log in sequence, synchronizes the binary log, and then commits this transaction into InnoDB. If the server crashes between those two operations, the transaction is rolled back by InnoDB at restart but still exists in the binary log. Such an issue is resolved assuming --innodb_support_xa is set to 1, the default. Although this option is related to the support of XA transactions in InnoDB, it also ensures that the binary log and InnoDB data files are synchronized. For this option to provide a greater degree of safety, the MySQL server should also be configured to synchronize the binary log and the InnoDB logs to disk before committing the transaction. The InnoDB logs are synchronized by default, and sync_binlog=1 can be used to synchronize the binary log. The effect of this option is that at restart after a crash, after doing a rollback of transactions, the MySQL server removes rolled back InnoDB transactions from the binary log. This ensures that the binary log reflects the exact data of InnoDB tables, and therefore the slave remains in synchrony with the master because it does not receive a statement which has been rolled back.

翻译：举个例子，当你使用InnoDB引擎的表和Mysql执行commit语句的时候，Mysql会为预执行的事务写入许多二进制日志序列并同步这些二进制日志，然后提交在InnoDB中提交这些事务。那么如果Mysql在这两个操作之间宕机了，那么InnoDB将会在重启的时候回滚这些事务，但是这个时候事务的Binlog已经存在了。默认情况下针对这个问题的解决办法是设置--innodb_support_xa 为1。虽然这个跟XA 事务相关的选项在InnoDB中已经得到了很好的支持，他也的确能够同步好Bin log和InnoDB的数据。但针对这个问题可以提供更安全的方法，需要把Mysql配置成同步提交Binlog（sync_binlog=1）并且在提交事务之前同步。InnoDB的日志文件（Redo Log）默认是同步的，那么设置sync_binlog=1可以用来把Binlog同步的。这个选项在Mysql宕机充值之前起作用，当InnoDB回滚事务之后，Mysql服务会从Bin log中删除已经回滚的事务。这个可以确保Binlog与InnoDB的表内容保持一致，并且Slave和Master之间仍然是保持同步的，因为Slave不会收到这些重启将回滚的表达式（常指sql语句）

Note

innodb_support_xa is deprecated and will be removed in a future release. InnoDB support for two-phase commit in XA transactions is always enabled as of MySQL 5.7.10.

注意

innodb_support_xa 参数将在未来的release版本中废弃并且移除。Mysql5.7.10版本后InnoDB支持两阶段提交的XA事务会一直开启.

If the MySQL server discovers at crash recovery that the binary log is shorter than it should have been, it lacks at least one successfully committed InnoDB transaction. This should not happen if sync_binlog=1 and the disk/file system do an actual sync when they are requested to (some do not), so the server prints an error message The binary log file_name is shorter than its expected size. In this case, this binary log is not correct and replication should be restarted from a fresh snapshot of the master's data.

翻译：如果Slave在宕机恢复中发现Binlog比实际发生的事务要短，那么它至少有一个未成功提交成功的InnoDB事务。这种情况不应该发生在已经设置sync_binlog=1 和在同步的情况下。所以如果当Slave打印出错误信息 The binary log file_name is shorter than its expected size。这种情况下二进制日志是不正确的，需要重启从Master服务中取得新Binlog快照进行复制。

The session values of the following system variables are written to the binary log and honored by the replication slave when parsing the binary log:

翻译：以下这些系统会话参数会被写入二进制日志中并且被复制到Slave并解析