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 countdown on front-end web page

Use native JavaScript to simply implement the cou...

Configure Java development environment in Ubuntu 20.04 LTS

Download the Java Development Kit jdk The downloa...

Detailed explanation of the idea of ​​MySQL trigger detecting a statement in real time for backup and deletion

Problem description: The user has a requirement t...

Solve the problem that the CentOS7 virtual machine cannot access the Internet and set up the CentOS7 virtual machine to use a static IP to access the Internet

I was playing with CentOS in a VMware virtual mac...

Use Docker to build a Redis master-slave replication cluster

In a cluster with master-slave replication mode, ...

5 VueUse libraries that can speed up development (summary)

Table of contents What utilities does VueUse have...

Share some tips on using JavaScript operators

Table of contents 1. Optional chaining operator [...

Detailed explanation of inline-block layout implementation of CSS display attribute

CSS display property Note: If !DOCTYPE is specifi...

How to maintain a long connection when using nginx reverse proxy

· 【Scene description】 After HTTP1.1, the HTTP pro...

Use pure CSS to create a pulsating loader effect source code

Effect Preview Press the "Click to Preview&q...

About the layout method of content overflow in table

What is content overflow? In fact, when there is ...

Summary of commonly used tags in HTML (must read)

Content Detail Tags: <h1>~<h6>Title T...

Detailed explanation of the calculation method of flex-grow and flex-shrink in flex layout

Flex(彈性布局) in CSS can flexibly control the layout...

How to fix some content in a fixed position when scrolling HTML page

This article mainly introduces how some content i...

Example of horizontal and vertical centering of div sub-element using CSS

Div basic layout <div class="main"&g...