MySQL - Detailed explanation of Replication's capacity, troubleshooting, and multi-threaded binary log transmission

1. Replication capacity

Pause Replication for a period of time (M), then restart it, and observe how long it takes for the Slave to reach consistency with the Master (N).
Replication capacity=N:M
It is recommended to keep the capacity at more than 3 times, that is, 1:3

2. Troubleshoot Replication faults

1. Log errors

1) Most replication errors are caused by log errors.
2) Both the main log and the relay log may be wrong. How to identify them:

#mysqlbinlog MASTER_BINLOG_FILE > /dev/null
#mysqlbinlog SLAVE_BINLOG_FILE > /dev/null

2. Skip log errors

1) Many problems may cause log errors. Sometimes there may be no errors in the log itself, but errors occur during SQL parsing (for example, the master-slave data is inherently inconsistent). You can manually skip log errors, but doing so may cause inconsistency between master and slave data.
2) If it is a main log error, it can be executed on the Slave (if there are multiple errors, it may be executed multiple times).

mysql> stop slave;
mysql> set global sql_slave_skip_counter=1;
mysql> start slave;

3) If there is an error in the relay log, you can check the Replication status through the show slave status \G command on the Slave, and skip the error log based on the log information:

mysql> stop slave;
mysql> charge master to
	-> master_log_file=&#39;<Relay_Master_Log_file>&#39;,
	-> master_log_pos=<Exec_Master_Log_Pos>;
mysql> start slave;

4) If Replcation works in GTIDs mode, you need

mysql> stop slave;
mysql> set gtid_next='uuid:nexti_d';
mysql> begin;
mysql> commit;
mysql> set gtid_next='automatic';
mysql> start slave;

Note: uuid:nextid for example: '0470a4fd-93d1-11e3-86bf-4ec905bea80f:17'.

3. Multi-threaded binary log transmission

1. MySQL5.6 begins to support multi-threaded binary log transmission.
2. Can only work in GTIDs mode.
3. Only operations performed on different libraries can use multi-threaded transmission. Operations on different tables under the same library can still only be transmitted in a single thread.

#vi /etc/my.cnf
[mysqld]
slave_parallel-workers=N (默认值为0,不开启)

Other commands:

mysql> set sql_log_bin=OFF; (关闭二进制日志)
mysql> set sql_log_bin=ON; (开启二进制日志)

The above is the detailed content of MySQL - Detailed explanation of Replication's capacity, troubleshooting, and multi-threaded binary log transmission. For more information, please follow other related articles on the PHP Chinese website!