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

...

JavaScript implements AI tic-tac-toe game through the maximum and minimum algorithm

Without further ado, let’s run the screenshot dir...

Detailed explanation of the function and usage of DOCTYPE declaration

1. Browser rendering mode and doctype Some web pa...

js+canvas realizes code rain effect

This article shares the specific code of js+canva...

Summary of react basics

Table of contents Preface start React Lifecycle R...

Analyze the duration of TIME_WAIT from the Linux source code

Table of contents 1. Introduction 2. First, let&#...

Detailed explanation of Javascript basics

Table of contents variable Data Types Extension P...

MySQL 8.0.16 installation and configuration tutorial under Windows 10

This article shares with you the graphic tutorial...

There are text and pictures in the a tag. How to hide the text and only show the picture?

Often you will encounter a style of <a> tag...

Vue Page Stack Manager Details

Table of contents 2. Tried methods 2.1 keep-alive...

Detailed explanation of MLSQL compile-time permission control example

Preface The simple understanding of MySQL permiss...

Button is stretched on both sides in IE

When you write buttons (input, button), you will f...

Solve the problem of using linuxdeployqt to package Qt programs in Ubuntu

I wrote some Qt interface programs, but found it ...

Using react-beautiful-dnd to implement drag and drop between lists

Table of contents Why choose react-beautiful-dnd ...

Instructions for using the --rm option of docker run

When the Docker container exits, the file system ...