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

Summary of various methods for Vue to achieve dynamic styles

Table of contents 1. Ternary operator judgment 2....

MySQL 8.0.12 Quick Installation Tutorial

The installation of MySQL 8.0.12 took two days an...

Example of using docker compose to build a consul cluster environment

Basic concepts of consul Server mode and client m...

【HTML element】How to embed images

The img element allows us to embed images in HTML...

VPS builds offline download server (post-network disk era)

motivation Due to learning needs, I purchased a v...

How to use watch listeners in Vue2 and Vue3

watch : listen for data changes (change events of...

Implementation of waterfall layout + dynamic rendering

Table of contents Typical waterfall website Water...

Realize three-level linkage of year, month and day based on JavaScript

This article shares the specific code for JavaScr...

Detailed explanation of three solutions to the website footer sinking effect

Background Many website designs generally consist...

Sample code for changing the color of a png image through a CSS3 filter

This method uses the drop-shadow filter in CSS3 t...

How to modify Flash SWF files in web pages

I think this is a problem that many people have en...

Steps to deploy ingress-nginx on k8s

Table of contents Preface 1. Deployment and Confi...

A graphic tutorial on how to install redhat 8.0 system (a must-have for beginners)

Table of contents 1. Introduction 2. Installation...

Form submission page refresh does not jump

1. Design source code Copy code The code is as fol...

JS realizes the effect of Baidu News navigation bar

This article shares the specific code of JS to ac...