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

Disabled values ​​that cannot be entered cannot be passed to the action layer

If I want to make the form non-input-capable, I se...

Pure CSS free implementation code for websites to have dark mode switching function

Preface The concept of dark mode originated from ...

Which is faster among MySQL full-text index, joint index, like query, and json query?

Table of contents Query Background 1. Like query ...

Detailed steps for completely uninstalling and reinstalling MySQL under Windows 10

I believe that everyone needs to reinstall MySQL ...

Detailed tutorial on how to create a user in mysql and grant user permissions

Table of contents User Management Create a new us...

MySQL InnoDB tablespace encryption example detailed explanation

Preface Starting from MySQL 5.7.11, MySQL support...

Detailed tutorial on compiling and installing python3.6 on linux

1. First go to the official website https://www.p...

How to import Chinese data into csv in Navicat for SQLite

This article shares with you the specific method ...

uniapp Sample code for implementing global sharing of WeChat mini-programs

Table of contents Create a global shared content ...

Docker enables seamless calling of shell commands between container and host

As shown below: nsenter -t 1 -m -u -n -i sh -c &q...

Use of kubernetes YAML files

Table of contents 01 Introduction to YAML files Y...

The difference between char and varchar in MYSQL

CHAR and VARCHAR types are similar, differing pri...

Detailed explanation of the process of building Prometheus+Grafana based on docker

1. Introduction to Prometheus Prometheus is an op...

Specific use of node.js global variables

Global Object All modules can be called global: r...

Example code for using text-align and margin: 0 auto to center in CSS

Use text-align, margin: 0 auto to center in CSS W...