MySQL master-slave synchronization pit avoidance version tutorial

Due to the data synchronization requirements on the project , I've seen a lot recently mysql Master slave synchronized posts , Also followed many posts to build it again , Finally, I found many places that are easy to step on the pit . Let's talk about it from beginning to end

1. The master-slave synchronization has strict requirements on the version , Master and slave mysql The version must be consistent
2. Please make sure the modified my.ini/my.cnf The file you installed mysql The program uses

Make sure that after two points above , So let's start building master-slave synchronization .

The first is the main end configuration

This part must pay attention to server-id It cannot be the same as the slave configured below .

meanwhile show master status after ,file And position If the parameter is empty , There are two possible situations

1. Modified my.ini file error , Not used by the current service

2. After modifying the file , No service restart

After the primary end is configured successfully , Now let's configure the slave

There are also places where it is easy to step on pits

If the port number of the primary end is not the default 3306, Additional port settings are required . Other settings that need to be changed , You can use commands show slave status\G To view the parameter list

master_log_file and master_log_pos The values of are before running on the host show master status Recorded when File and Position If the result is wrong , say slave The thread is running , Cannot set , In this case , perform mysql>stop slave; stop it slave Threads , Then set the parameters of the connection ; After setting up , perform mysql> start slave; Turn on slave Threads ; perform mysql> show slave status\G   ( There's no semicolon ). If appear

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Represents successful configuration

After the master-slave setting is successful , Under normal circumstances , Synchronization ok , But when the slave network is interrupted or the network fluctuates , because mysql Its own setting problem , After returning to normal from the end network , need 30 Minutes to 60 Minutes to synchronize （ This is also a pit ） If you need to speed up synchronization , Need modification mysql Related parameters

For the specific principle, please refer to https://blog.51cto.com/wwdhks/2103885 This elder brother's post .

To add   Individuals found in the tests The slave end is set -slave-net-timeout after , Need to put slave close It won't take effect until you open it again

At the same time, the master-slave mode can also be master-slave to each other , Avoid operating the same table when they are master-slave .