Reduce the latency of MySQL Master/Slave Data Synchronization

Generally, the masterslave Mechanism Based on LAN can meet the requirements of rsquo, real-time rsquo, and backup. If the latency is high, confirm the following factors:

Generally, the LAN-based master/slave mechanism can meet the requirements of rsquo, real-time rsquo, and backup. If the latency is high, confirm the following factors:

Generally, the master/slave Mechanism Based on LAN can meet the requirements of 'realtime' backup. If the latency is high, first confirm the following factors:

1. network latency
2. master Load
3. slave Load
The general practice is to use multiple slave to allocate read requests, and then retrieve a dedicated server from these slave for backup purposes only, without any other operations, it can meet the 'realtime' requirement to the maximum extent.

In addition, we will introduce two parameters that can reduce latency.

-Slave-net-timeout = seconds
Parameter description: When slave fails to read log data from the master database, how long will it take to re-establish a connection and obtain data?

Slave_net_timeout is measured in seconds. The default value is 3600 seconds.
| Slave_net_timeout | 3600

-Master-connect-retry = seconds
Parameter description: When a master-slave connection is re-established, if the connection fails to be established, retry after the interval.

The unit of master-connect-retry is seconds. The default value is 60 seconds.

Generally, the above two parameters can reduce the Master/Slave Data Synchronization latency caused by network problems.

The common network error is:

070401 16:16:55 [ERROR] Error reading packet from server: Lost connection to MySQL server during query (server_errno = 2013)
070401 16:16:55 [ERROR] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql _ master-bin.000134 'position 115817861