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

A Guide to Optimizing High-Performance Websites

Golden Rules of Performance: Only 10% to 20% of e...

Vue elementUI form nested table and verification of each row detailed explanation

Table of contents Effect display Code Link Key Co...

MySQL multi-instance configuration solution

1.1 What is MySQL multi-instance? Simply put, MyS...

Example code for developing h5 form page based on react hooks and zarm component library configuration

Recently, I have used React Hooks in combination ...

Hide div in HTML Hide table TABLE or DIV content css style

I solved a problem tonight that has been botherin...

JS+CSS to realize dynamic clock

This article example shares the specific code of ...

Vue Element-ui implements tree control node adding icon detailed explanation

Table of contents 1. Rendering 2. Bind data and a...

Analysis of the principle of Rabbitmq heartbea heartbeat detection mechanism

Preface When using RabbitMQ, if there is no traff...

Detailed explanation of screen command usage in Linux

GUN Screen: Official website: http://www.gnu.org/...

How to modify the MySQL character set

1. Check the character set of MySQL show variable...

Detailed steps to build a file server in Windows Server 2012

The file server is one of the most commonly used ...

MySQL 8.0.21 free installation version configuration method graphic tutorial

Six steps to install MySQL (only the installation...

Detailed tutorial on uploading and configuring jdk and tomcat on linux

Preparation 1. Start the virtual machine 2. git t...

js canvas realizes circular water animation

This article example shares the specific code of ...

Detailed explanation of the entry-level use of MySql stored procedure parameters

Use of stored procedure in parameters IN paramete...