本文介绍了MySQL二进制日志(binlog)的重要配置选项，包括binlog格式、数据库过滤、同步设置等，帮助读者理解如何正确配置MySQL复制环境。
[color=red]binlog-format[/color]可以取值: STATEMENT (default)，ROW，MIXED。必须有SUPER才可以更改。不推荐replication正在进行时，修改binlog-format值。
[color=red]binlog-do-db[/color]（[color=red]binlog-ignore-db[/color]、[color=red]replicate-do-db[/color]、[color=red]replicate-ignore-db[/color]）对于多个数据库，必须用多行。因为数据库名字可以包含逗号，所以用逗号分隔多个数据库名字，会被认为是一个包含了逗号的数据库的名字。
无论binlog-format是什么，象create table和alter table之类的语句，总是以statement的格式记录在二进制日志中的。
binlog-do-db（binlog-ignore-db、replicate-do-db、replicate-ignore-db）生效与binlog-format有关：
binlog-format为STATEMENT时，生效的数据库为default database。
binlog-format为ROW时，生效的为表的所在的数据，与default database无关。
[color=red]log-slave-updates[/color] 是否记录复制的更新。当用于级联复制时，很有必要。而在双主模式中，应当取FALSE。
[color=red]auto_increment_increment[/color]
[color=red]auto_increment_offset[/color]
取值为： auto_increment_offset + N*auto_increment_increment
[color=red]sync-binlog[/color] 取值为0时，由操作系统的文件系统决定何时同步二进制日志。大于0时，为N个commit group后，同步到二进制日志。取值为1，最安全，但性能较低。
[color=red]slave-ignore-errors[/color] 忽略复制的错误。不建议取值为all。正常情况下，不应该会有错；如果出错，应当解决错误，而不是忽略错误。
[color=red]log-error[/color] 日志记录文件，记录MySQL服务器启动、关闭等信息。Windows平台注意路径要用“/”分隔，而不是“\”。
[color=red]log-output[/color] （522页）
日志的记录位置：FILE TABLE NONE
[color=red]sync_binlog[/color] (default 0, page 1849)
If the value of this variable is greater than 0, the MySQL server synchronizes its binary log to disk (using
fdatasync()) after sync_binlog[2129]commit groups are written to the binary log. The default
value of sync_binlog[2129]is 0, which does no synchronizing to disk—in this case, the server relies
on the operating system to flush the binary log's contents from to time as for any other file. A value of 1
is the safest choice because in the event of a crash you lose at most one commit group from the binary
log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes
synchronization very fast).
[color=red]innodb_flush_log_at_timeout[/color] (default 1, page 1810)
Write and flush the logs every Nseconds. This setting has an effect only when
innodb_flush_log_at_trx_commit[1811]has a value of 2.
[color=red]innodb_flush_log_at_trx_commit[/color] (default 1, page 1811)
Controls the balance between strict ACIDcompliance for commitoperations, and higher performance
that is possible when commit-related I/O operations are rearranged and done in batches. You can
achieve better performance by changing the default value, but then you can lose up to one second worth
of transactionsin a crash.
• The default value of 1 is required for full ACID compliance. With this value, the log bufferis written out
to the log fileat each transaction commit and the flushto disk operation is performed on the log file.
• With a value of 0, any mysqldprocess crash can erase the last second of transactions. The log buffer
is written out to the log file once per second and the flush to disk operation is performed on the log file,
but no writes are done at a transaction commit.
• With a value of 2, only an operating system crash or a power outage can erase the last second of
transactions. The log buffer is written out to the file at each commit, but the flush to disk operation
is not performed on it. Before MySQL 5.6.6, the flushing on the log file takes place once per
second. Note that the once-per-second flushing is not 100% guaranteed to happen every second,
due to process scheduling issues. As of MySQL 5.6.6, flushing frequency is is controlled by
innodb_flush_log_at_timeout[1810]instead.
• InnoDB's crash recoveryworks regardless of the value. Transactions are either applied entirely or
erased entirely.
For the greatest possible durability and consistency in a replication setup using InnoDBwith
transactions, use innodb_flush_log_at_trx_commit=1and sync_binlog=1in your master
server my.cnffile.
[color=red]thread_cache_size[/color] (default 8 + (max_connections / 100), page 580)
How many threads the server should cache for reuse. When a client disconnects, the client's threads
are put in the cache if there are fewer than thread_cache_size[580]threads there. Requests
for threads are satisfied by reusing threads taken from the cache if possible, and only when the cache
is empty is a new thread created. This variable can be increased to improve performance if you
have a lot of new connections. Normally, this does not provide a notable performance improvement
if you have a good thread implementation. However, if your server sees hundreds of connections
per second you should normally set thread_cache_size[580]high enough so that most new
connections use cached threads. By examining the difference between the Connections[618]and
Threads_created[632]status variables, you can see how efficient the thread cache is. For details,
see Section 5.1.6, “Server Status Variables”.
The default value is based on the following formula, capped to a limit of 100:
8 + (max_connections / 100)
[color=red]binlog-do-db[/color]（[color=red]binlog-ignore-db[/color]、[color=red]replicate-do-db[/color]、[color=red]replicate-ignore-db[/color]）对于多个数据库，必须用多行。因为数据库名字可以包含逗号，所以用逗号分隔多个数据库名字，会被认为是一个包含了逗号的数据库的名字。
无论binlog-format是什么，象create table和alter table之类的语句，总是以statement的格式记录在二进制日志中的。
binlog-do-db（binlog-ignore-db、replicate-do-db、replicate-ignore-db）生效与binlog-format有关：
binlog-format为STATEMENT时，生效的数据库为default database。
binlog-format为ROW时，生效的为表的所在的数据，与default database无关。
[color=red]log-slave-updates[/color] 是否记录复制的更新。当用于级联复制时，很有必要。而在双主模式中，应当取FALSE。
[color=red]auto_increment_increment[/color]
[color=red]auto_increment_offset[/color]
取值为： auto_increment_offset + N*auto_increment_increment
[color=red]sync-binlog[/color] 取值为0时，由操作系统的文件系统决定何时同步二进制日志。大于0时，为N个commit group后，同步到二进制日志。取值为1，最安全，但性能较低。
[color=red]slave-ignore-errors[/color] 忽略复制的错误。不建议取值为all。正常情况下，不应该会有错；如果出错，应当解决错误，而不是忽略错误。
[color=red]log-error[/color] 日志记录文件，记录MySQL服务器启动、关闭等信息。Windows平台注意路径要用“/”分隔，而不是“\”。
[color=red]log-output[/color] （522页）
日志的记录位置：FILE TABLE NONE
[color=red]sync_binlog[/color] (default 0, page 1849)
If the value of this variable is greater than 0, the MySQL server synchronizes its binary log to disk (using
fdatasync()) after sync_binlog[2129]commit groups are written to the binary log. The default
value of sync_binlog[2129]is 0, which does no synchronizing to disk—in this case, the server relies
on the operating system to flush the binary log's contents from to time as for any other file. A value of 1
is the safest choice because in the event of a crash you lose at most one commit group from the binary
log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes
synchronization very fast).
[color=red]innodb_flush_log_at_timeout[/color] (default 1, page 1810)
Write and flush the logs every Nseconds. This setting has an effect only when
innodb_flush_log_at_trx_commit[1811]has a value of 2.
[color=red]innodb_flush_log_at_trx_commit[/color] (default 1, page 1811)
Controls the balance between strict ACIDcompliance for commitoperations, and higher performance
that is possible when commit-related I/O operations are rearranged and done in batches. You can
achieve better performance by changing the default value, but then you can lose up to one second worth
of transactionsin a crash.
• The default value of 1 is required for full ACID compliance. With this value, the log bufferis written out
to the log fileat each transaction commit and the flushto disk operation is performed on the log file.
• With a value of 0, any mysqldprocess crash can erase the last second of transactions. The log buffer
is written out to the log file once per second and the flush to disk operation is performed on the log file,
but no writes are done at a transaction commit.
• With a value of 2, only an operating system crash or a power outage can erase the last second of
transactions. The log buffer is written out to the file at each commit, but the flush to disk operation
is not performed on it. Before MySQL 5.6.6, the flushing on the log file takes place once per
second. Note that the once-per-second flushing is not 100% guaranteed to happen every second,
due to process scheduling issues. As of MySQL 5.6.6, flushing frequency is is controlled by
innodb_flush_log_at_timeout[1810]instead.
• InnoDB's crash recoveryworks regardless of the value. Transactions are either applied entirely or
erased entirely.
For the greatest possible durability and consistency in a replication setup using InnoDBwith
transactions, use innodb_flush_log_at_trx_commit=1and sync_binlog=1in your master
server my.cnffile.
[color=red]thread_cache_size[/color] (default 8 + (max_connections / 100), page 580)
How many threads the server should cache for reuse. When a client disconnects, the client's threads
are put in the cache if there are fewer than thread_cache_size[580]threads there. Requests
for threads are satisfied by reusing threads taken from the cache if possible, and only when the cache
is empty is a new thread created. This variable can be increased to improve performance if you
have a lot of new connections. Normally, this does not provide a notable performance improvement
if you have a good thread implementation. However, if your server sees hundreds of connections
per second you should normally set thread_cache_size[580]high enough so that most new
connections use cached threads. By examining the difference between the Connections[618]and
Threads_created[632]status variables, you can see how efficient the thread cache is. For details,
see Section 5.1.6, “Server Status Variables”.
The default value is based on the following formula, capped to a limit of 100:
8 + (max_connections / 100)
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
