How to modify the master-slave replication options in MySQL online

How to modify the master-slave replication options in MySQL online

Preface:

The most commonly used architecture of MySQL is master-slave replication. In fact, there are many options for master-slave replication, especially on the slave side. We can set replication filters, such as ignoring a table or a database. These filter options can be modified online without restarting. I didn’t know much about this before, but I recently read some relevant information and I personally think this function is very convenient. This article will share this content with you.

1. Introduction to copy filter parameters

First we need to understand the different parameters for setting replication filters. Replication filtering is set on the slave database side. You can copy only certain databases or tables, or ignore copying certain databases or tables. These are all controlled by different parameters. The following is a brief introduction to the functions of different parameters.

  • REPLICATE_DO_DB: Specifies to synchronize only the data of a certain database
  • REPLICATE_IGNORE_DB: Ignore the synchronization of a database
  • REPLICATE_DO_TABLE: Specifies to synchronize a table
  • REPLICATE_IGNORE_TABLE: Ignore the synchronization of a table
  • REPLICATE_WILD_DO_TABLE: Specifies to synchronize certain tables, you can use wildcards
  • REPLICATE_WILD_IGNORE_TABLE: Ignore the synchronization of certain tables, you can use wildcards
  • REPLICATE_REWRITE_DB: Replace the database name from the database side

These replication filter parameters are easy to understand. Just by looking at the name, you can roughly understand the function of the parameter. By default, these parameters are not set. After master-slave replication is enabled, the slave database will synchronize all data sent from the master database by default.

2. Modify the copy filter options

When we want to temporarily adjust the replication strategy of the slave library, we can set the above parameters. We can write the filtering parameters into the configuration file and then restart the slave library to apply it, but this method requires restarting the instance and is not recommended. MySQL version 5.7 can set up replication filtering online. However, replication still needs to be stopped, but there is no need to restart the instance, which makes it convenient for temporary adjustments. The CHANGE REPLICATION FILTER statement is mainly used. Let's do a simple test:

# Replication filtering is not set by defaultmysql> show slave status\G
*************************** 1. row ***************************
        Slave_IO_State: Waiting for master to send event
         Master_Host: 10.0.3.16
         Master_User: repl
         Master_Port: 3306
        Connect_Retry: 60
       Master_Log_File: binlog.000004
     Read_Master_Log_Pos: 35198
        Relay_Log_File: relay-bin.000002
        Relay_Log_Pos: 910
    Relay_Master_Log_File: binlog.000004
       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: 35198
     
 # Set to ignore the replication of the db1 library mysql> STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB = (db1);
Query OK, 0 rows affected (0.00 sec)
mysql> START SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
        Slave_IO_State: Waiting for master to send event
         Master_Host: 10.0.3.16
         Master_User: repl
         Master_Port: 3306
        Connect_Retry: 60
       Master_Log_File: binlog.000004
     Read_Master_Log_Pos: 35198
        Relay_Log_File: relay-bin.000002
        Relay_Log_Pos: 910
    Relay_Master_Log_File: binlog.000004
       Slave_IO_Running: Yes
      Slave_SQL_Running: Yes
       Replicate_Do_DB: 
     Replicate_Ignore_DB: db1
      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: 35198
 
# Create db1 in the master database to test whether the slave database is synchronized mysql> CREATE DATABASE `db1` DEFAULT CHARACTER SET utf8;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|db1|
|mysql |
| performance_schema |
|sys|
|testdb|
+--------------------+
6 rows in set (0.00 sec)
# View the status of the slave databasemysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|mysql |
| performance_schema |
|sys|
|testdb|
+--------------------+
5 rows in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
        Slave_IO_State: Waiting for master to send event
         Master_Host: 10.0.3.16
         Master_User: repl
         Master_Port: 33061
        Connect_Retry: 60
       Master_Log_File: binlog.000004
     Read_Master_Log_Pos: 35383
        Relay_Log_File: relay-bin.000002
        Relay_Log_Pos: 1095
    Relay_Master_Log_File: binlog.000004
       Slave_IO_Running: Yes
      Slave_SQL_Running: Yes
       Replicate_Do_DB: 
     Replicate_Ignore_DB: db1
      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: 35383
     
# Cancel the replication filter parameter mysql> STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.01 sec)
mysql> CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB = ();
Query OK, 0 rows affected (0.00 sec)
mysql> START SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
        Slave_IO_State: Waiting for master to send event
         Master_Host: 10.0.3.16
         Master_User: repl
         Master_Port: 3306
        Connect_Retry: 60
       Master_Log_File: binlog.000004
     Read_Master_Log_Pos: 35383
        Relay_Log_File: relay-bin.000002
        Relay_Log_Pos: 1095
    Relay_Master_Log_File: binlog.000004
       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: 35383

We have briefly demonstrated how to modify replication filter options online using the CHANGE REPLICATION FILTER statement. All filter parameters listed can be modified using this statement, but please note that some options are mutually exclusive. Appropriate parameters should be set according to actual needs. The following is a sample syntax from the official documentation:

CHANGE REPLICATION FILTER filter[, filter][, ...]
filter:
  REPLICATE_DO_DB = (db_list)
 | REPLICATE_IGNORE_DB = (db_list)
 | REPLICATE_DO_TABLE = (tbl_list)
 | REPLICATE_IGNORE_TABLE = (tbl_list)
 | REPLICATE_WILD_DO_TABLE = (wild_tbl_list)
 | REPLICATE_WILD_IGNORE_TABLE = (wild_tbl_list)
 | REPLICATE_REWRITE_DB = (db_pair_list)
db_list:
  db_name[, db_name][, ...]
tbl_list:
  db_name.table_name[, db_table_name][, ...]
wild_tbl_list:
  'db_pattern.table_pattern'[, 'db_pattern.table_pattern'][, ...]
db_pair_list:
  (db_pair)[, (db_pair)][, ...]
db_pair:
  from_db, to_db

Summarize:

This article introduces how to change replication filter options online. Different filter parameters have different uses. If you really need to set filter parameters, it is recommended to conduct a comprehensive test. Some parameter settings may affect the replication of other database tables. If you want it to take effect permanently, you can modify it online and then add it to the configuration file, so that it will still take effect after the slave library is restarted.

The above is the details of how to modify the master-slave replication options of MySQL online. For more information about modifying the master-slave replication of MySQL, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:
  • Tutorial on building a master-slave replication architecture for MySQL 5.7 Docker
  • Summary of several replication methods for MySQL master-slave replication
  • Detailed analysis of MySQL master-slave replication
  • MySQL master-slave replication principle and practice detailed explanation
  • How to configure MySQL master-slave replication under Windows
  • Implementation steps for building a MySQL master-slave replication environment based on Docker
  • Detailed explanation of the role and working principle of MySQL master-slave replication
  • Solution to the long delay of MySQL database master-slave replication
  • MySQL 4 common master-slave replication architectures

<<:  Ubuntu installs multiple versions of CUDA and switches at any time

>>:  TypeScript learning notes: type narrowing

Recommend

How to Choose the Perfect Aloe Vera Gel? Perfect Aloe Vera Gel How to Identify Authenticity and Fakeness

The latest Perfect Aloe Vera Gel packaging box ha...

Detailed explanation of the new array methods in JavaScript es6

Table of contents 1. forEach() 2. arr.filter() 3....

Commonly used English fonts for web page creation

Arial Arial is a sans-serif TrueType font distribu...

Vue local component data sharing Vue.observable() usage

As components become more detailed, you will enco...

Detailed explanation of identifying files with the same content on Linux

Preface Sometimes file copies amount to a huge wa...

Do you know how to use mock in vue project?

Table of contents first step: The second step is ...

Dynamically edit data in Layui table row

Table of contents Preface Style Function Descript...

How to install Docker on Windows 10 Home Edition

I recently used Docker to upgrade a project. I ha...

Detailed explanation of the pitfalls of Apache domain name configuration

I have never used apache. After I started working...

Detailed installation process and basic usage of MySQL under Windows

Table of contents 1. Download MySQL 2. Install My...

Summary of the deployment of Tomcat cluster and Nginx load balancing based on Docker

Table of contents Written in front 1. Ngixn image...

How to connect JDBC to MySQL 5.7

1. First prepare the MySQL and Eclipse environmen...

How to quickly create tens of millions of test data in MySQL

Remark: The amount of data in this article is 1 m...

mysql creates root users and ordinary users and modify and delete functions

Method 1: Use the SET PASSWORD command mysql -u r...

How to use CSS to write different styles according to sub-elements

The effect we need to achieve: What is needed The...