MySQL master-slave replication practice-detailed explanation of replication code examples based on log points

This article mainly introduces the detailed explanationMySQL Master-slave replication practice - replication based on log points, which has certain reference value. Interested friends can refer to it.

Replication based on log points

1. Establish dedicated replication accounts on the main database and slave database

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

Pay attention to the password in production Relevant specifications must be followed to achieve a certain password strength, and it is stipulated that the master database can only be accessed on a specific network segment on the slave database

2. Grant replication permissions on the master database and the slave database

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

3. Configure the main library

Note that enabling binary logs requires restarting the service, and server_id is a dynamic parameter that can be combined with the command line and Configuration file to achieve persistent configuration without restarting. Note 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 is a good habit to separate logs and data. It is best to put them in different data partitions

4. Configure slave database

The option log_slave_update determines whether to store the relay log relay_log in the local binlog. If link replication is configured, this option is required. Note that the 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 the data from the slave library

Mysqldump is used here to back up the main library. In production, it is recommended that you use xtrabackup for lock-free hot backup (based on the innodb engine).

Back up the data of the employees database on the main library

The code is as follows:

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

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

mysql -u root -p < backup.sql

6. Start the replication link

Existing master@172.20.0.2 and slave@172.20.0.3, and the data has been synchronized to the slave database slave through mysqldump. Now on the slave server Configure the replication link on the 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. If an error occurs, you must read the prompt information of Last_IO_Error or Last_SQL_Error 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 the dump thread 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 On row 2, the Command for Binlog Dump is started, which proves that the replication thread has been successfully started.

9. Summary

Advantages

1. The technology is mature and there are relatively few BUGs

2. There are no restrictions on SQLquery, such as not all when copying based on GTID SQL can be used
   

Disadvantages

1. Reobtain the log offset of the new primary during failover It is more difficult

In a one-master and multiple-slave environment, if the old master goes down and a new master is elected in the cluster, other slave libraries must resynchronize the new master. Since the binlog of each DB exists independently, it is difficult to find the log point to start synchronization

The above is the detailed content of MySQL master-slave replication practice-detailed explanation of replication code examples based on log points. For more information, please follow other related articles on the PHP Chinese website!