mysql master-slave synchronization simple configuration

1. Synchronous configuration configuration environment

  1. Both virtual machines have centos7 operating systems. The ip address is 192.168.0.91 (master)
    192.168.0.92 (slave)
  2. Both virtual machines have installed the mysql database, the database version is 5.7
  3. The main database (192.168.0.91) already has a root user and can be accessed remotely
  4. Note: All the following SQL operations are performed in Navicat

2. Main library settings
1. Configure my.cnf file
vi /etc/my.cnf. Open my.cnf, do the following configuration
, add under the [mysqld] section: #Server
unique identifier, generally take the ip of the server

server-id = 91 #Start
MySQL binary log
log_bin = master-bin
log_bin_index = master-bin.index #Specify
the database that records the binary log
binlog_do_db = test #Specify the database
that does not record the binary log
binlog_ignore_db = mysql

Insert picture description here


2. Restart the mysql service
service mysqld restart

3. Query the status of the master

Insert picture description here

3. From the library settings
1. Modify the my.conf file, as shown in the figure below

Insert picture description here


2. Restart the mysql service
service mysqld restart

3. Configure the slave server

Execute the following statement on the slave server:
change master to master_host='192.168.0.91',master_port=3306,master_user='root',master_password='123456',master_log_file='master-bin.000001',master_log_pos=5727;

Be careful not to break in the middle of the statement, master_port is the mysql server port number (without quotes), master_user is the database account that performs the synchronization operation, "5727" without single quotes (5727 here is the value of position seen in show master status, Here master-bin.000001 is the value corresponding to file), which is the picture of the third step operation in the main library settings

4. Start the replication function
from the server, execute start slave from the server;

5. Check the status of the replication function
from the server. Perform show slave status on the slave server.

Insert picture description here

The two red icons in the figure below are both Yes, indicating that the configuration is successful.

4. Test
Note: Be sure to ensure that the table in the main library exists in the slave library, and the name and table name must be the same.
Readers can create an identical table in the main library and the slave library. Operate in the main library and look at the slave library. Whether to follow changes. I won’t demonstrate here

5. Exception handling
There are two key processes on the slave machine from the library, one of them will not work, one is slave_sql_running, the other is Slave_IO_Running, the other is responsible for io communication with the host, and the other is responsible for its own slave mysql process.
If it is Slave_SQL_Running: no, the solution is as follows:
stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
start slave;
show slave status;

If it is slave_io_running: no, the solution is as follows:
first confirm whether the file of the master library is consistent with the Master_Log_File of the slave library, as follows:
master library:

Insert picture description here

slave library:

Insert picture description here

confirm whether the places marked in red by the author in the above two pictures are the same. The slave library is changed as follows:
slave stop;
CHANGE MASTER TO MASTER_LOG_FILE='file name of the master library', MASTER_LOG_POS=0;
slave start;
show slave status