Example of how to implement MySQL cascading replication

Example of how to implement MySQL cascading replication

The so-called cascading replication is that the master server only synchronizes data to one slave server, and then the slave server synchronizes data to all slave servers in the back end, reducing the write pressure of the master server and the network IO of the replicated data.

1. Configure the master server

1. Modify the main configuration file

vim /etc/my.cnf

Add the following two lines of configuration under the [mysql] configuration block

[mysql]
log_bin # Enable binary logging server_id=1 # Set a globally unique ID for the current node

2. Restart the MySQL service to make the configuration effective

systemctl restart mairadb

3. Create a user account with copy permissions

GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'HOST' IDENTIFIED BY 'replpass';

Command analysis:

  • 'repluser'@'HOST': Set the username, which is the host IP or network segment. The network segment is represented by %, for example 10.0.0.%
  • IDENTIFIED BY: Set password
  • *.* : indicates all databases and all tables
  • GRANT REPLCATION SLAVE: allows the user to replicate data

The purpose of this command is to authorize repluser to copy all the contents of the database

2. Relay slave server configuration

1. Modify the main configuration file

vim /etc/my.cnf

Add the following two lines of configuration in the [mysql] configuration block

[mysqld]  
 log_bin
server_id=2 #Set a globally unique ID number for the current node read_only=ON #Limit the slave server to read-only. "Note: This restriction is invalid for users with SUPER privileges"
log_slave_updates #The purpose of this item is to record the binary log of the master server to the local machine, and then copy the binary log to other slave servers in the backend

2. Restart the MySQL service to make the configuration effective

systemctl restart mariadb

3. Use a user account with replication permissions to connect to the primary server and start the replication thread

   CHANGE MASTER TO 
   MASTER_HOST='host', #Specify the master host IP
   MASTER_USER='repluser', #Specify the authorized user name of the master MASTER_PASSWORD='replpass', #Specify the authorized user password MASTER_LOG_FILE='mysql-bin.xxxxx', #Specify the binary log from which the master server is copied MASTER_LOG_POS=#; #Binary log position, you can execute this command on the master server to view it, show master logs;

   Start the replication threads IO_THREAD and SQL_THREAD
   START SLAVE;

4. Check the status of the relay slave server

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

  MariaDB [(none)]> show slave status\G
  *************************** 1. row ***************************
          Slave_IO_State: Waiting for master to send event
           Master_Host: 192.168.68.7
           Master_User: repluser
           Master_Port: 3306
          Connect_Retry: 60
         Master_Log_File: mariadb-bin.000001
       Read_Master_Log_Pos: 557
          Relay_Log_File: mariadb-relay-bin.000002
          Relay_Log_Pos: 843
      Relay_Master_Log_File: mariadb-bin.000001
         Slave_IO_Running: Yes "Pay special attention if it is NO it means the thread is not up"
        Slave_SQL_Running: Yes "Pay special attention to if it is NO, it means the thread is not up"
         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: 557
         Relay_Log_Space: 1139
         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 "This item indicates the synchronization time. 0 means synchronization immediately."
  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

3. Backend slave configuration

1. Modify the configuration file

vim /etc/my.cnf

Add the following two lines of configuration in the [mysql] configuration block

[mysqld]  
server_id=3 #Set a globally unique ID number for the current node read_only=ON #Limit the slave server to read-only. "Note: This restriction is invalid for users with SUPER privileges"

2. Restart the MySQL service to make the configuration effective

systemctl restart mariadb

3. Use a user account with replication permissions to connect to the primary server and start the replication thread

CHANGE MASTER TO 
   MASTER_HOST='relay host', #Specify the relay slave host IP
   MASTER_USER='repluser', #Specify the authorized user name of the master MASTER_PASSWORD='replpass', #Specify the authorized user password MASTER_LOG_FILE='mysql-bin.xxxxx', #Specify the binary log from which the relay slave server will start copying MASTER_LOG_POS=#; #Binary log position, you can execute this command on the slave server to view it, show master logs;

   Start the replication threads IO_THREAD and SQL_THREAD
   START SLAVE;

4. Check the slave server status

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

  MariaDB [(none)]> show slave status\G
  *************************** 1. row ***************************
          Slave_IO_State: Waiting for master to send event
           Master_Host: 192.168.68.17
           Master_User: repluser
           Master_Port: 3306
          Connect_Retry: 60
         Master_Log_File: mariadb-bin.000001
       Read_Master_Log_Pos: 557
          Relay_Log_File: mariadb-relay-bin.000002
          Relay_Log_Pos: 843
      Relay_Master_Log_File: mariadb-bin.000001
         Slave_IO_Running: Yes "Pay special attention if it is NO it means the thread is not up"
        Slave_SQL_Running: Yes "Pay special attention to if it is NO, it means the thread is not up"
         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: 557
         Relay_Log_Space: 1139
         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 "This item indicates the synchronization time. 0 means synchronization immediately."
  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

5. Finally, create a database test on the master server to check whether it is synchronized

Cascading replication features

  • Reduce the pressure on the master server and network io pressure
  • However, data inconsistency may occur.

Summarize

  • The relay slave needs to open the binary log, and the log_slave_updates configuration item must be added
  • Note the effect of read_only=ON, which limits the slave server to read-only. "Note: This restriction is not valid for users with SUPER privileges."

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:
  • Summary of MYSQL full backup, master-slave replication, cascading replication, and semi-synchronization
  • Mysql method to copy a column of data in one table to a column in another table
  • Copy fields between different tables in MySQL
  • In-depth understanding of MySQL master-slave replication thread state transition
  • Explanation of the precautions for Mysql master-slave replication
  • MySQL replication mechanism principle explanation

<<:  Complete example of vue polling request solution

>>:  Detailed tutorial for installing nginx on centos8 (picture and text)

Recommend

Looping methods and various traversal methods in js

Table of contents for loop While Loop do-while lo...

Service management of source package installation under Linux

Table of contents 1. Startup management of source...

Implementation of docker-compose deployment project based on MySQL8

1. First, create the corresponding folder accordi...

How to create a table by month in MySQL stored procedure

Without going into details, let's go straight...

Linux CentOS6.5 yum install mysql5.6

This article shares the simple process of install...

Commonplace talk about MySQL event scheduler (must read)

Overview MySQL also has its own event scheduler, ...

In-depth analysis of the Identifier Case Sensitivity problem in MySQL

In MySQL, you may encounter the problem of case s...

Implementation of mounting NFS shared directory in Docker container

Previously, https://www.jb51.net/article/205922.h...

Let's talk in detail about the props attributes of components in Vue

Table of contents Question 1: How are props used ...

Detailed explanation of Nginx installation, SSL configuration and common commands under Centos7.x

1. Installation Install using yum ##Automatically...

How to use file writing to debug a Linux application

In Linux, everything is a file, so the Android sy...

HTML table markup tutorial (6): dark border color attribute BORDERCOLORDARK

In a table, you can define the color of the lower...

Briefly describe the difference between Redis and MySQL

We know that MySQL is a persistent storage, store...

Font references and transition effects outside the system

Copy code The code is as follows: <span style=...

Detailed tutorial on jdk installation and environment variable configuration under Win10

Table of contents Preface 1. Preparation 2. Insta...