MySQL Master-Slave Replication Practice-Log Point-Based Replication

Log-point-based replication

1. Establishment of special duplicate accounts on master and slave Libraries

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

   Note that passwords in production must be in accordance with the relevant specifications to achieve a certain password strength, and stipulate that access to the master library can only be achieved on a specific network segment on the slave library.

2. Granting replication privileges on master and slave Libraries

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

3. Configure main library

   Note that enabling binary logging requires restarting the service, and server_id is a dynamic parameter that combines command line and configuration file to achieve restart-free persistent configuration. Note that server_id is unique 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's a good habit to separate logs from data, preferably in different data partitions

4. Configuration slave Library

   The option log_slave_update determines whether relay_log is stored in the local binlog. If link replication is configured, this option must be filled in. Note that server_id is unique 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 data from the slave database

   Here, mysqldump is used to backup the main library. In production, it is recommended that you use xtrabackup for unlocked 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 Volume Volume Volume Volume Volume and import it into the slave Library

   mysql -u root -p < backup.sql

6. Start replication link

   The existing master@172.20.0.2 and slave@172.20.0.3 have synchronized data to slave library through mysqldump. Now the replication link is configured on slave server.

   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 replication links from libraries

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

7. Check slave status from the library

   Slave_IO_Running and Slave_SQL_Running must be YES. If an error occurs, read the Last_IO_Error or Last_SQL_Error prompts in detail.

   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 if 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 on row 2 is started, proving that the replication thread has been started successfully.

9. summary

   • Advantage

     1. Mature technology, relatively few BUG s
     2. There are no restrictions on SQL queries, such as not all SQL can be used for GTID-based replication

   • shortcoming

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

     In a master-slave environment, if a new master is elected in the cluster after the outage of the old master, the other slave libraries need to re-synchronize the new master. Because each DB binlog exists independently, it is difficult to find the log point to start synchronization.