Master slave synchronization

• Note: the code in this article may involve misuse of Chinese characters, such as quotation marks and commas. Ask the paster to debug and modify the self code.

1. Open the primary server mysql database configuration file

• Find my. In the mysql installation directory Ini configuration file, add under [mysqld]

server-id=1    
log-bin=master-bin
log-bin-index=master-bin.index

Note: if it is obtained as an installation package or from others, check whether the configuration of basedir and datadir is the local installation directory
Example:

basedir=F:\mysql\mysql-5.7.27-winx64
datadir=F:\mysql\mysql-5.7.27-winx64/data

1. Check the configuration effect, enter the main database and execute

• Open the cmd operation console, start the mysql service, and enter the mysql operation permission.

mysql>mysql -u root -p
mysql>Enter your own database password
mysql> SHOW MASTER STATUS;

1. Open Binary log from two configuration files of the server and add it under [mysqld]

• Find my. In the mysql installation directory ini and my (server). ini configuration files are added under [mysqld]. The id here can be customized (if there are multiple slave servers, the server id cannot be repeated).

server-id=2    
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
 Restart after modifying the configuration file mysql service  --  service mysql restart
 perhaps (1) net stop mysql;      (2) net start mysql ;

1. Next, configure the association between the two databases

• First, we establish a database user that operates master-slave synchronization, and switch to the master database for execution

1. create user x;  
2. GRANT REPLICATION SLAVE ON *.* TO 'x'@'xxx.xxx.xxx.xx' IDENTIFIED BY 'xxxxxxxxx';
stay x Fill in the user name where you want to create the operation master database.
stay xxx.xxx.xxx.xx Fill in your own from the server IP The address can be used%A sign in place of means for all ip to open up.
stay xxxxxxxxx Fill in the password required for the connection at the location.
3. Reload permission table
flush privileges;
4. View the of the current master server File Value of Position Value.
show master status；
remember File Value of Position The value to be used for subsequent configuration from the server is very important.

1. Switch to configuration from database

change master to master host='Primary server ip address',
master port=Open port of master server,
master user='The connection user name configured on the primary server',
master password='The connection password configured on the primary server',
Master_log_file='On the primary server File name',
master_log_pos=On the primary server Position value;

• After the above steps are completed, execute the start slave startup configuration

start slave;
show slave status \G ;

• Pay attention to the view of slave IO running and slave_ SQL_ Is the running value yes
• If all are no, it means there is no start slave
• If one of them is not yes:
• You can go to the data directory of the mysql file to view the suffix err error log file

Start troubleshooting

• Err: if slave IO running is No:

1. Go to the mysql installation directory and find the data folder, where auto Change the server UUID in the CNF file, for example, replace the last bit with a character or number.

• Err: if Slave_SQL_Running is No:
  It indicates that:

change master to master host='Primary server ip address',
master port=Open port of master server,
master user='The connection user name configured on the primary server',
master password='The connection password configured on the master server',
Master_log_file='On the primary server File name',
master_log_pos=On the primary server Position value;
Problems in filling in this code 

1. Re in the primary server mysql first

SHOW MASTER STATUS;

1. Check whether the file value and position value are the same as the master in the slave server_ log_ file&master_ log_ POS values correspond one to one
   If not, execute the following code from the server:

Stop slave;
Reset master;
Reset slave;

Then re-enter:
change master to master host='Primary server ip address',
master port=Open port of master server,
master user='The connection user name configured on the primary server',
master password='The connection password configured on the primary server',
Master_log_file='On the primary server File name',
master_log_pos=On the primary server Position value;

start slave ;
show slave status \G;

• Review slave IO running and slave_ SQL_ Is the running value yes
  If yes, it indicates that the master-slave server data has been synchronized!!
  Try to modify the data on the master server and check whether there are changes on the slave server!!
  If the test is correct, it means that the mysql database of the master-slave server has been synchronized successfully!!
  If there are any problems, repeat the previous debugging steps.