Mysql-binary log-understanding and application

Mysql-binary log-understanding and application

1. Two Functions of binary logs
Used for restoration;
Used for replication;

2. Enable and disable binary logs
Configure the following options:
Log_bin [= on | file_name]
Server_id = 1

3. Other configuration options
3.1 max_binlog_size

Sets the maximum value of a single binary log file. The default value is 1 GB.

3.2 binlog_cache_size

Set the buffer size of the binary log. This value is based on the session and cannot be set to too large, but cannot be set to too small. Otherwise, binary logs are written using temporary files;
View binlog_cache_use and binlog_cache_disk_use in status.
Show global status like 'binlog _ cache % ';
+ ----------------------- + ------- +
| Variable_name | value |
+ ----------------------- + ------- +
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 34 |
+ ----------------------- + ------- +
When the binlog_cache_disk_use ratio is large, it indicates that the binlog_cache_size setting is a little small;

3.3 sync_binlog
Set the method for synchronizing binary logs to the disk, which can be understood as buffer write. The value is N, indicating the number of times each write buffer is synchronized to the disk;
The default value 0 indicates that no buffer is used. This will slightly affect the performance. It is recommended to set it to 0 on the master server or for high availability.
1 indicates that binary logs are written in synchronous mode to Disks without the Operating System Buffer mechanism. A possible problem occurs when binary logs are written to the disk before the transaction is committed, if the transaction fails to be committed, the transaction cannot be rolled back at the next startup (because the log has recorded the transaction record). To solve this problem, set innodb_support_xa = 1

3.4 BINLOG-do-DB and BINLOG-ignore-DB
Set the database logs to be written or ignored.

3.5 log-slave-Update
Sets whether the slave server writes its own binary log;

3.6 binlog_format
Set the binary file format
Value Options: Statement, row, and mixed.
In mixed mode, the statement format is used by default. In some special cases, the row format is automatically used. We recommend that you set it to mixed mode to ensure the real-time and correctness of replication;
The row mode is the most complete mode for replication, but the problem is that operations that affect a large number of rows will cause a rapid growth of binary log files, this affects the network transmission of replication;