MySQL Binlog User Guide

MySQL Binlog Detailed

1. Preface
The log is to record every change in the database into a dedicated file, which is called a log file. MySQL only opens the error log by default, because too many logs will affect the processing performance of the system.
Text format and binary format are supported before 5.0, and only binary format is supported after 5.0, because binary logs have more advantages in performance and information processing.

2. Basic knowledge

2.1. Enable binary logging
The binary log is enabled by the Log-bin option for the configuration file, MySQL server will create two new files in the data root directory xxx-bin.001 and Xxx-bin.index, if the configuration option does not give the file name, MySQL will use the host name to name the two files, where the. index file contains a list of all log files.
MySQL will record the user's changes to the contents and structure of all the databases into the XXX-BIN.N file, anddo not record selectAnd no actual
2.2. Updated UPDATE statement.
Extension of the log file
When stopped or restarted, the server logs the log file into the next log file, and MySQL generates a new log file at reboot, the file sequence is incremented, and a new log file is generated if the log file exceeds the upper limit of the MAX_BINLOG_SIZE system variable configuration.
2.3. log File View
MySQL provides the mysqlbinlog command to view log files, such as Mysqlbinlog xxx-bin.001 | More When recording each change log, the log file will record the current time for database recovery.
2.4. log file Deactivation
can useSET sql_log_bin=0command to stop using the log file and thenSET sql_log_bin=1command to enable.
2.5. Using logs for database recovery
If you encounter a disaster, you should restore the database with the last completed backup, and then use the log after the backup
The file restores the database to the closest available state.
When using the log for recovery, you need to proceed sequentially, that is, the first log file to be generated is restored first:
Mysqlbinlog xxx-bin.00001 | Mysql-u root-p
Mysqlbinlog xxx-bin.00002 | Mysql-u root-p

3. Log and change policy
Use the index to loop through the file, looping to the next index in the following conditions

A. Server restart
B. Server is updated
C. log reaches maximum log length max_binlog_size, specified in general configuration file
D. Log is refreshed mysql> flush logs; Manual Refresh

Strong style= "margin:0px;padding:0px;" >4. log format
  from the official website document, the previous MySQL has been only statement-based replication mode, Until the 5.1.5 version of MySQL started to support row level replication. Starting with 5.0, MySQL replication has resolved issues that are not correctly replicated in a large number of older versions. However, due to the advent of stored procedures, MySQL replication replication has brought more new challenges. In addition, the official documentation says that, starting with version 5.1.8, MySQL provides a third replication mode except for the statement level and row level: Mixed, which actually combines the first two modes. In mixed mode, MySQL distinguishes between the log forms of treated records based on each specific SQL statement executed, that is, choosing between statement and row. The statement level in the new version is still the same as before, just record the executed statement. The new version of MySQL in the row level mode is also optimized, not all changes will be at the row level to record, like when the table structure changes in the statement mode to record, If the SQL statement is really a statement that modifies data such as update or delete, then all rows are changed.

--SQL statement-based replication (statement-based replication,sbr),
--row-based replication (row-based REPLICATION,RBR),
--Mixed Mode replication (mixed-based replication,mbr).

For the pros and cons of three formats, please refer to:

http://douya.blog.51cto.com/6173221/1605114  

Static setting Binlog Format:--Permanent effect

Log-bin = Mysql-bin #binlog_format = "STATEMENT" #binlog_format = "ROW" Binlog_format = "MIXED"

Dynamically modifying the Binlog format:--Restart failure

mysql> SET SESSION binlog_format = ' STATEMENT ';  mysql> SET SESSION binlog_format = ' ROW ';   mysql> SET SESSION binlog_format = ' MIXED ';  mysql> SET GLOBAL binlog_format = ' STATEMENT ';  mysql> SET GLOBAL binlog_format = ' ROW '; mysql> SET GLOBAL binlog_format = ' MIXED ';

5.binary log related variables and parameters

5.1. Command line Parameters

--log-bin [=file_name]

Setting this parameter means that the Binlog feature is enabled and the name of the path is not written, the default is the host name

--log-bin-index[=file]

Setting this parameter is the path and name of the specified binary index file, which, by default in DataDir, can no longer be configured in the configuration file

--max_binlog_size Recommended 500M

Binlog Maximum, the maximum and default value is 1GB, and when the Binlog log reaches this maximum value , a new log is automatically updated.

This setting does not strictly control the size of the binlog, especially when the Binlog is closer to the maximum and encounters a larger transaction,

In order to guarantee the integrity of the transaction, it is impossible to switch the log, only all the SQL of the transaction is logged into the current log until the end of the transaction.

--binlog-do-db=db_name

This parameter indicates that only the binary log of the specified database is logged

--binlog-ignore-db=db_name

This parameter indicates that the binary log of the specified database is not logged

--Expire_logs_days =n recommended 7 days for general retention

This parameter represents the retention of n-days of Binlog, which will be automatically removed over this time. It is recommended to use this parameter to purge expired logs instead of manually

5.2. System Variables

Log_bin

Binlog_cache_size

This parameter represents the memory size used by Binlog, which can be helped by state variables Binlog_cache_use and Binlog_cache_disk_use.

Max_binlog_cache_size

This parameter represents the maximum size of memory used by Binlog

Binlog_cache_use

number of transactions using the 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.

Sync_binlog

This parameter directly affects MySQL's performance and integrity.

Sync_binlog=0: It is recommended to use the default parameter 0

when the transaction commits, MySQL simply writes the data in the Binlog_cache to the Binlog file, but does not execute a disk such as Fsync, and the synchronization instruction notifies the filesystem to flush the cache to disk, and let filesystem decide when to synchronize. This is the best performance.

Sync_binlog=n, after an n-transaction commit, MySQL performs a disk synchronization instruction such as Fsync, notifying the file system to flush the Binlog file cache to disk.

The default setting in MySQL is sync_binlog=0, that is, without any mandatory disk refresh instructions, performance is the best, but the risk is greatest. Once the system crash, all binlog information in the file system cache is lost.

6. Frequently Asked Questions

6.1 Examples:

> purge master logs to  ' mysql-bin.010 ';   //clear MySQL-bin.010 Log >  purge master logs before  ' 2008-06-22 13:00:00 ';    //clear 2008-06-22  13:00:00 before Binlog log > purge master logs before date_sub ( now ( ),  Interval 3 day);   //cleared 3 days ago Binlog log before, the date argument of a variable can be in the ' yyyy-mm-dd hh:mm:ss ' format. 

6.2. When Binlog is cleared, the effect on MySQL
If you have an active secondary server that is currently reading one of the logs that you are trying to delete, this statement will not work, but will fail with an error. However, if the secondary server is inactive and you happen to have cleaned one of the logs that it wants to read, the secondary server cannot replicate after it is started. This statement is safe to run when the secondary server is replicating. You do not need to stop them.

--or use the command:
RESET MASTER operates with caution

Delete all previous binlog, and regenerate the new binlog, with the suffix starting from 000001.

Note: If you have an active secondary server that is currently reading one of the logs that you are trying to delete, this statement will not work, but fail with an error.

However, if the secondary server is inactive and you happen to have cleaned one of the logs that it wants to read, the secondary server cannot replicate after it is started.

This statement is safe to run when the secondary server is replicating. You do not need to stop them.

6.3, the binary log is not accurate processing

By default, binary logs are not synchronized with the hard disk each time it is written. Therefore, if the operating system or machine (not just the MySQL server) crashes, it is possible that the last statement in the binary log is lost. To prevent this, you can use the Sync_binlog global variable (1 is the safest value, but also the slowest), so that the binary logs are synchronized with the hard disk after each n binary log write. Even if Sync_binlog is set to 1, there is a possibility of inconsistency between the contents of the table and the contents of the binary log when there is a crash.

If the MySQL server discovers that the binary log is shortened when the crash is resumed (that is, at least one INNODB transaction is missing), if Sync_binlog = 1 and the hard disk/file system does synchronize as needed (some unwanted), the output error message ("Binary log < name > smaller than expected "). In this case, the binary log is inaccurate and replication should begin with the data snapshot of the primary server.

In a general online environment, MySQL Binlog can use the following configuration files

#binloglog-bin = $dir/mysql-binbinlog_format = "STATEMENT" max_binlog_size = 500mbinlog_cache_size = 64mexpire_logs_ Days = 7 sync_binlog=0:

This article is from the "Crazy_sir" blog, make sure to keep this source http://douya.blog.51cto.com/6173221/1788730

MySQL Binlog User Guide