Of Stack

Detailed explanation of MySQL master slave replication replication based on log points

  • 2021-07-22 11:44:11
  • OfStack

Log point-based replication

1. Establish a dedicated replication account on the master library and the slave library 


MariaDB [employees]> create user 'repl'@'172.%' identified by '123456';

Note that the password in production must be in accordance with the relevant specifications to achieve a certain password strength, and it is stipulated that the master library can only be accessed on a specific network segment on the slave library

2. Grant replication permissions on master and slave libraries 


MariaDB [employees]> grant replication slave on *.* to 'repl'@'172.%';

3. Configure the main library

Note that enabling binary logging requires a restart of the service, and server_id is a dynamic parameter that can be combined with a command line and configuration file to achieve a restart-free persistent configuration. Note that server_id is only 1 in the cluster. 


[mysqld]
log_bin = /var/log/mysql/mariadb-bin
log_bin_index = /var/log/mysql/mariadb-bin.index
binlog_format = row
server_id = 101

NOTE: It is a good habit to separate logs from data, preferably in different data partitions

4. Configure the slave library

The option log_slave_update determines whether to store the relay log relay_log to the native binlog, which is required if link replication is configured. Note that server_id is only 1 in the cluster. 


[mysqld]
# replication
log_bin = /var/log/mysql/mariadb-bin
log_bin_index = /var/log/mysql/mariadb-bin.index
server_id = 102
# slaves
relay_log    = /var/log/mysql/relay-bin
relay_log_index  = /var/log/mysql/relay-bin.index
relay_log_info_file  = /var/log/mysql/relay-bin.info
log_slave_updates = ON
read_only

5. Initialize the data from the library

Here, mysqldump is used for backup on the main library. In production, it is recommended to use xtrabackup for lock-free hot standby (based on innodb engine).

Back up the data of employees database on the main library 



mysqldump --single-transaction --master-data=1 --triggers --routines --databases employees -u root -p >> backup.sql

Mount the backup file backup. sql on the slave server through the scp or docker volume volume and import it into the slave library 


mysql -u root -p < backup.sql

6. Start the replication link

master @ 172.20. 0.2 and slave @ 172.20. 0.3 exist, and data has been synchronized to slave library slave through mysqldump. Replication links are now configured on slave server slave 


MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='master', MASTER_USER='repl', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mariadb-bin.000029', MASTER_LOG_POS=516;
Query OK, 0 rows affected (0.02 sec)

Start the replication link on the slave library 


MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)

7. Check the slave status on the slave library

Slave_IO_Running and Slave_SQL_Running must be YES, read Last_IO_Error or Last_SQL_Error in detail if errors occur 


MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
        Slave_IO_State: Waiting for master to send event
         Master_Host: master
         Master_User: repl
         Master_Port: 3306
        Connect_Retry: 60
       Master_Log_File: mariadb-bin.000029
     Read_Master_Log_Pos: 516
        Relay_Log_File: relay-bin.000002
        Relay_Log_Pos: 539
    Relay_Master_Log_File: mariadb-bin.000029
       Slave_IO_Running: Yes
      Slave_SQL_Running: Yes
       Replicate_Do_DB:
     Replicate_Ignore_DB:
      Replicate_Do_Table:
    Replicate_Ignore_Table:
   Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
          Last_Errno: 0
          Last_Error:
         Skip_Counter: 0
     Exec_Master_Log_Pos: 516
       Relay_Log_Space: 831
       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: 101
        Master_SSL_Crl:
      Master_SSL_Crlpath:
          Using_Gtid: No
         Gtid_IO_Pos:
   Replicate_Do_Domain_Ids:
 Replicate_Ignore_Domain_Ids:
        Parallel_Mode: conservative
1 row in set (0.00 sec)

8. Check dump threads in the main library

Check whether the binlog dump thread has been started correctly 


MariaDB [(none)]> show processlist \G
*************************** 1. row ***************************
   Id: 7
  User: root
  Host: 172.20.0.1:41868
   db: employees
 Command: Sleep
  Time: 56
  State:
  Info: NULL
Progress: 0.000
*************************** 2. row ***************************
   Id: 10
  User: repl
  Host: 172.20.0.3:45974
   db: NULL
 Command: Binlog Dump
  Time: 246
  State: Master has sent all binlog to slave; waiting for binlog to be updated
  Info: NULL
Progress: 0.000

You can see that the command Command is Binlog Dump is started on row 2, which proves that the replication thread has been successfully started

9. Summary

Advantages

The technology is mature, and BUG is relatively few There are no restrictions on SQL queries, such as not all SQL can be used when replicating based on GTID

Disadvantages

It is difficult to retrieve the log offset of the new master during failover

In a one-master multi-slave environment, if a new master is selected in the cluster after the old master goes down, other slave libraries need to resynchronize the new master. Because each DB's binlog exists independently, it is difficult to find the log point to start synchronization

Related articles: