MySQL binary log optimization

The specific parameters are as follows:

1.server-id=id

Unique ID of the service

2.log_bin=/mydata/binlog/mysql-bin

Location and naming of binary logs

3.binlog_format={row| Statement| MIXD}

Row format: Records changes for each row of data updates. When you encounter a statement that alter,update the entire field is a value, it makes the binary log file extremely large. It affects the IO performance of the system. But the consistency of the data is guaranteed.

Statement format: Records only update statements that result in data changes. However, it is possible to cause inconsistent data.

MIXD: A mixture of two formats.

4.binlog_cache_size

Global variables

The cache size that is used to hold the SQL statement during the transaction. The cache with binary logs assumes that the MySQL server has a transaction-enabled storage engine and that the Log_bin log is turned on. This parameter allocates binlog_cache_size cache size for each client connection.

If the user frequently uses multi-statement transactions, the size of the binlog_cache_size can be increased.

See if Binlog_cache_size is reasonable

Mysql> Show status like ' binlog% '; +----------------------------+-------+| variable_name | Value |+----------------------------+-------+| Binlog_cache_disk_use | 1 | | Binlog_cache_use | |+----------------------------+-------+

Binlog_cache_use: Number of transactions using binary log cache

Binlog_cache_disk_use: The number of transactions using the binary log cache but exceeding the Binlog_cache_size value and using temporary files to hold statements in the transaction

Two cases:

(1) There is no big business ()

binlog_cache_size=1m

(2) There is no small business (2m-4m)

Binlog_cache_size=2m

5.sync_binlog={0|n}

0: Indicates that the data in Binlog_cache will not be flushed to disk immediately after the transaction commits. The refresh time is updated by the file system itself, or the binlog_cache is saturated before the data in the cache is brushed to disk.

N: Indicates that the data in Binlog_cache will not be flushed to disk until the transaction commits n times. This value is set to 1 as the safest, because the worst case scenario, the value loses a transaction.

6.max_binlog_size

The maximum capacity of the binary log file. 512M or 1G generally.

7.binlog_do_db

Set which databases need to be logged to Binlog. Configured only on Master.

8.binlog_ignore_db

Which databases do not require logging Binlog. configured on Master only.

9.replicate_do_db

Set which databases need to be synchronized binlog, if you have multiple databases, separate them with commas (,), and configure them only on slave.

10.replicate_ignore_db

Set which databases do not require synchronization binlog, and if there are multiple databases, separate them with commas (,). configured only on slave.

11.expire_logs_days

Maximum time for binary log cache

This article from the "Do not ask for the best, only better" blog, please be sure to keep this source http://yujianglei.blog.51cto.com/7215578/1727973

MySQL binary log optimization