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

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

Logpoint-based replication

1. Create a dedicated replication account on the master and slave databases

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

Note that the password in production must meet the relevant specifications to achieve a certain password strength, and it is required that the master database can only be accessed on a specific network segment on the slave database.

2. Grant replication permissions on the master and slave databases

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

3. Configure the main database

Note that enabling binary logging requires restarting the service, and server_id is a dynamic parameter that can be combined with command lines and configuration files 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 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 in the local binlog. If link replication is configured, this option is required. 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 the data from the library

Here we use mysqldump to back up the master database. In production, we recommend 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 database

Copy the code 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 via scp or docker volume, and import it into the slave library

mysql -u root -p < backup.sql

6. Start the replication link

There are [email protected] and [email protected], and the data has been synchronized to the slave through mysqldump. Now configure the replication link on the 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 the replication link on the slave

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, 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 that the command Binlog Dump is started on row 2, which proves that the replication thread has been successfully started.

9. Summary

advantage

  1. The technology is mature and there are relatively few bugs
  2. There are no restrictions on SQL queries, for example, not all SQL can be used when replicating based on GTID

shortcoming

  1. It is difficult to re-obtain the log offset of the new master during a failover.

In a one-master-multiple-slave environment, if the old master crashes and a new master is elected in the cluster, the other slaves need to resynchronize with 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 full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.

You may also be interested in:
  • Detailed explanation of using pt-heartbeat to monitor MySQL replication delay
  • Detailed explanation of MySQL master-slave replication read-write separation construction
  • An example of changing traditional replication to GTID replication without stopping business in MySQL 5.7
  • Detailed explanation of MySQL master-slave replication practice - GTID-based replication
  • Detailed explanation of how to configure multi-threaded master-slave replication from MySQL 5.7 slave nodes
  • Detailed analysis of replication in Mysql

<<:  10 reasons why Linux is becoming more and more popular

>>:  Manually implement the two-way data binding principle of Vue2.0

Recommend

Vue.js implements simple folding panel

This article example shares the specific code of ...

Mysql Chinese sorting rules description

When using MySQL, we often sort and query a field...

Difference between MySQL update set and and

Table of contents Problem Description Cause Analy...

Pure js to achieve typewriter effect

This article example shares the specific code of ...

Detailed graphic explanation of MySql5.7.18 character set configuration

Background: A long time ago (2017.6.5, the articl...

JavaScript data structure bidirectional linked list

A singly linked list can only be traversed from t...

Detailed steps for Python script self-start and scheduled start under Linux

1. Python automatically runs at startup Suppose t...

NodeJs high memory usage troubleshooting actual combat record

Preface This is an investigation caused by the ex...

js version to realize calculator function

This article example shares the specific code of ...

Solution to docker suddenly not being accessible from the external network

According to the methods of the masters, the caus...

Detailed explanation of the use of redux in native WeChat applet development

premise In complex scenarios, a lot of data needs...

How to use Dayjs to calculate common dates in Vue

When using vue to develop projects, the front end...