Shulou(Shulou.com)05/31 Report--

How to realize the master-slave synchronization mechanism in MySQL? I believe that many inexperienced people don't know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

The most intuitive manifestations are:

Mysql > show slave status\ G; / / status 1 Seconds_Behind_Master: NULL / / status 2 Seconds_Behind_Master: 0 / / status 3 Seconds_Behind_Master: 79

Continuous query, most of the time the attribute value = 0, occasional delay values such as Null or 79. The monitoring which causes the master-slave synchronization delay to give a continuous alarm.

Cause of failure and solution

The server-id of more than one slave is the same, so that the host cannot connect with a slave for a long time, and then cannot synchronize normally.

After modifying the server-id, restart the database to recover.

Master-slave synchronization mechanism

MySQL's master-slave synchronization, also known as replication, is a built-in high-availability, high-performance cluster solution with the following main features:

Data distribution: synchronization does not require much bandwidth, and data can be replicated in multiple data centers.

Read load balancer: through the server cluster, you can reduce the read pressure on the master server by using GSLB (global load balancing) methods such as DNS polling and Linux LVS.

Database backup: replication is part of the backup, but it is not a substitute for backup. It also needs to be combined with snapshots.

High availability and failover: the slave server can be quickly switched to the master server, reducing downtime and recovery time for failures.

Master-slave synchronization is divided into three steps:

The master server (master) records data changes to the binary log (binlog).

The slave server (slave) copies the binary log of the master server to its own relay log (relay log).

Redo the log in the relay log from the server and apply the changes to your own database to achieve data consistency.

Master-slave synchronization is an asynchronous real-time synchronization, which will be transmitted in real time, but there is a delay in execution. If there is a lot of pressure on the master server, the delay will expand accordingly.

From the figure above, you can see that a total of three threads are required:

Log shipping thread of the primary server: responsible for incremental delivery of binary logs to the standby

I / O thread of the slave server: responsible for reading the binary log of the master server and saving it as a relay log

From the SQL thread of the server, responsible for executing the relay log

View MySQL threads

We can use the show full processlist; command to see the status of MySQL:

Status of the host:

Status of standby:

As you can see, my cluster architecture consists of 1 host and 4 standby machines, so there are 4 synchronous threads in the host (all binlog data has been sent to the standby, waiting for binlog log updates) and 1 view command thread (show full processlist). In the standby, there is a view command thread, an I / O thread (waiting for the host to send synchronous data events), and a SQL thread (all relay logs have been read, waiting for the I / O thread to update it).

View synchronization status

Because master-slave synchronization is asynchronous and real-time, that is, there will be a delay, we can check the synchronization delay on the slave through show slave status;:

Some attributes that we need to pay attention to in master-slave synchronization have been marked red:

Slave_IO_State: the status of the current Imax O thread

Master_Log_File: the binaries of the currently synchronized master server

Read_Master_Log_Pos: the offset of the binary file of the currently synchronized master server in bytes, as shown in the figure for the content that has been synchronized with 12.9m (13630580max 1024mp 1024).

Relay_Master_Log_File: the binary file synchronized by the current relay log

Slave_IO_Running: from the server, the running status of the YES O thread is normal.

Slave_SQL_Running: SQL the running state of the thread from the server. YES is running normally.

Exec_Master_Log_Pos: indicates the binary log offset of the primary server that is synchronized

Seconds_Behind_Master: indicates how long data from the slave server lags behind the master server

You can also view the running status of the primary server through the show master status; command:

Master-slave synchronization status of normal operation:

Slave_IO_Running: YES

Slave_SQL_Running: YES

Seconds_Behind_Master: 0

Problem troubleshooting

After understanding the mechanism of master-slave synchronization, let's take a look at the problems encountered today. By looking at the standby status, we observe several key attribute values in three states:

Mysql > show slave status\ Gbot # status 1: Slave_IO_State: Reconnecting after a failed master event read Slave_IO_Running: No Slave_SQL_Running: Yes Seconds_Behind_Master: NULL# status 2: Slave_IO_State: Waiting for master to send event Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: status 3: Slave_IO_State: Queueing master event to the relay log Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 636

Through the state transition of the MySQL master-slave replication thread, we can see the different meanings of the three states:

# status one # Thread is trying to reconnect to the primary server, and when the connection is re-established, the state changes to Waiting for master to send event. The Reconnecting after a failed master event read# status 2 # thread has connected to the primary server and is waiting for the binary log event to arrive. If the primary server is idle, it will last for a long time. If the wait lasts for slave_read_timeout seconds, a timeout occurs. At this point, the thread thinks the connection is broken and attempts to reconnect. The Waiting for master to send event# status 3 # thread has read an event and is copying it to the relay log for processing by the SQL thread. Queueing master event to the relay log

Here, we can guess that, for some reason, the slave server is constantly disconnected from the master server and tries to reconnect, and then disconnects again after a successful reconnection.

Let's take a look at the operation of the host:

It is found that the problem lies in 10.144.63.* and 10.144.68.*. Let's check the error log of one of them:

190214 11:33:20 [Note] Slave: received end packet from server, apparent master shutdown:

190214 11:33:20 [Note] Slave I amp O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.005682' at postion 13628070

Search for the keywords Slave: received end packet from server, apparent master shutdown: Google, and you can see the duplicate server-id of the two standby machines in the article Confusing MySQL Replication Error Message.

One day it happen to me, and took me almost an hour to find that out.

Moving foward I always use a base my.cnf to I copy to any other server and the first thing is to increase the server-id.

Could MySQL just use the servername intead of a numeric value?

Problem repair

After locating the problem, we confirm whether it is duplicated, and find that the field of the two slave devices is indeed the same:

After reading the above, the server-id=177230069sync_binlog=1 caused by the same random number vim my.cnf#replicationlog-bin=mysql-bin#, have you mastered how to implement the master-slave synchronization mechanism in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.