There are many ways of MySQL master-slave replication. This paper mainly demonstrates the master-slave replication based on binlog.

Principle of MySQL master-slave replication (also known as A/B replication)

• The Master records the data changes in the binary log, that is, the file specified in the configuration file log bin,
  These records are called binary log events;

• Slave reads binary log events in the Master through the I/O thread and writes them to its relay log;

• Slave redoes the events in the relay log and executes the event information in the relay log one by one locally
  Into the local storage of data, so as to reflect the changes to its own data (data replay).

Points needing attention in master-slave configuration

• The version and number of bits of the operating system of the master-slave server are consistent;

• The versions of Master and Slave databases shall be consistent;

• The data in the Master and Slave databases shall be consistent;

• The Master starts the binary log, and the Master and Slave servers_ ID must be unique in LAN;

Brief steps of master-slave configuration

Configuration on Master

1. Install database;
2. Modify the database configuration file to indicate the server_id, start binary log bin;
3. Start the database and check the current log and position number;
4. Log in to the database and authorize the data replication user (the IP address is the slave IP address. If it is a two-way master-slave, 1. Here, you also need to authorize the local IP address. At this time, your own IP address is the slave IP address);
5. Back up the database (remember to lock and unlock);
6. Transfer backup data to Slave;
7. Start the database;

Configuration on Slave

1. Install database;
2. Modify the database configuration file to indicate the server_id (if you are building a two-way master-slave, you should also turn on the binary
   Log bin);
3. Start the database and restore the backup;
4. Check the current log and position number (one-way master-slave is not required, two-way master-slave is required);
5. Specify the address, user, password and other information of the Master;
6. Turn on synchronization and check the status.

1. Master point configuration

Modify the Master configuration file / etc / my cnf

vi /etc/my.cnf

In my Add the following configuration contents to CNF file

[mysqld]
log-bin=mysql-bin
server-id=1

2. Slave configuration

Modify the Slave configuration file / etc / my cnf

vi /etc/my.cnf

In my Add the following configuration contents to CNF file

[mysqld]
server-id=2

3. Create a user for the copy operation (main library operation - root user)

mysql5.7. Previous operation mode:

grant replication slave on *.* to 'user_name'@'192.168.0.226' identified by 'ahaii';

mysql8.0 operation mode:
Create a user repl in the master node, which is used when linking the master node from the node.

mysql> CREATE USER 'repl'@'192.168.199.198' IDENTIFIED WITH mysql_native_password BY 'Ron_master_1';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.199.198';

Refresh authorization table information

mysql> flush privileges;

4. Get the current binary log file name and position of the primary node (Primary Library Operation - root user)

mysql> SHOW MASTER STATUS;

mysql> SHOW MASTER STATUS\G;
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 155
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

5. Set the master node parameters on the Slave node (Slave library operation - root user)

mysql> CHANGE MASTER TO
MASTER_HOST='192.168.199.149',
MASTER_USER='repl',
MASTER_PASSWORD='Ron_master_1',
MASTER_LOG_FILE='binlog.000006',
MASTER_LOG_POS=856;

After configuring the above, restart the mysql service from the server.

6. View synchronization status (from library operation - root user)

mysql> show slave status\G;

7. Enable master-slave synchronization (slave library operation - root user)

mysql> start slave;

8. Then view the master-slave synchronization status (slave library operation - root user)

mysql> show slave status\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.225
                  Master_User: user_name
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1114
               Relay_Log_File: mysqld-relay-bin.000004
                Relay_Log_Pos: 1260
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: test.%
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1114
              Relay_Log_Space: 1563
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
row in set (0.00 sec)

Including: Slave_IO_Running and slave_ SQL_ The status of running is YES, indicating that the synchronization is successfully started.

Now you can go to the test database on the main server to create a table and start test synchronization.