The master/slave mechanism based on LAN can usually meet the requirement of ' real time ' backup. If the delay is large, first identify the following factors:
1. Network Latency
2. Master Load
3. Slave load
The general practice is to use more than one slave to allocate read requests, and then take a dedicated server from these slave, only as a backup, without any other operations, can be relatively maximum to achieve ' real-time ' requirements
In addition, 2 parameters that can reduce latency are described again
–slave-net-timeout=seconds
Parameter meaning: When slave reads log data from the primary database fails, wait how long to re-establish the connection and get the data
Slave_net_timeout unit is seconds default set to 3,600 seconds
| Slave_net_timeout | 3600
–master-connect-retry=seconds
Parameter meaning: When the master-slave connection is reset, if the connection establishment fails, the interval will be retried.
Master-connect-retry unit is seconds default set to 60 seconds
Typically configuring the above 2 parameters can reduce the delay of master-slave data synchronization caused by network problems
Errors for general network problems are:
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′p Osition 115817861