How to dynamically modify the replication filter in mysql

MySQL dynamically modify replication filters

Let me talk about the problem I encountered today. Today I was dealing with a business demand, which was quite abnormal. Let me describe it briefly:

1. There is a game log library on the online Alibaba Cloud RDS. The tables in it are in the form of daily tables. The amount of data is relatively large. Every time a backup is performed, the online RDS will cause an alarm, and the alarm content is that the IO resources are occupied too much.

2. There is a local ECS read-only slave database on this RDS. This read-only slave database will synchronize the data in the online RDS database in real time. This read-only slave database is used by the business side for query

3. The business side said that all these data are still useful. The data on the read-only slave database must be available, and the data on the online RDS can be deleted and only needs to be retained for two weeks.

The scenario is just like this: the DBA wants to solve the alarm problem, and the business side wants to ensure that they have complete data. How to solve this problem?

When I saw this question, I wanted to curse. This requirement is unreasonable. How can you delete one database and keep it in another one? Besides, they are all log data. Why not just make a cold backup and then delete the online ones? However, there was no room for easing the situation, so I started thinking about how to solve this problem. The solutions I thought of are:

1. Expand capacity and improve performance. The amount of data is large, so expand the disk. The IO utilization rate is high, so improve the performance. This is the most direct solution, but also the most expensive solution, and is the first to be cut off.

2. Back up first, then delete and then restore. Back up the daily table data in advance on the RDS master database, and then delete the data. At this time, the slave database will delete the data synchronously, and then restore the data backed up in the first step to the slave database. This method is feasible because it ensures that no data is lost. However, the operation is rather complicated, with too many procedures and is not convenient enough.

3. Use the replicate-ignore-table parameter to filter the specified table. Setting this parameter allows you to filter all operations on the specified data table. Let's take a look at the official documentation for the description of this parameter. Here is a link: https://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html#option_mysqld_replicate-wild-ignore-table

The description is as follows:

Creates a replication filter which keeps the slave thread from replicating a statement in which any table matches the given wildcard pattern. To specify more than one table to ignore, use this option multiple times,

The above means that you can use this parameter to create a filter to filter out operations on specific tables that match the rules you set (sounds confusing). That is, you can set a filtering rule and add table a to the rule, then the operations on table a will not be synchronized to the slave database.

This is in line with our needs. That is, if we set the table to be filtered, then when we delete the table, the table will not be deleted from the database, and the desired result will be achieved. Let’s test this functionality:

First we create the database test_ignore and then create a table in it:

Operation on the main database:

mysql:test_ignore >>show tables;
Empty set (0.00 sec)

mysql:test_ignore >>create table aaa (id int not null);
Query OK, 0 rows affected (0.19 sec)

mysql:test_ignore >>create table aab (id int not null);
 Query OK, 0 rows affected (0.01 sec)

mysql:test_ignore >>create table aac (id int not null);
 Query OK, 0 rows affected (0.00 sec)

mysql:test_ignore >>create table aad (id int not null);
 Query OK, 0 rows affected (0.01 sec)

mysql:test_ignore >>create table aae (id int not null);
 Query OK, 0 rows affected (0.01 sec)

View from the library:

mysql:test_ignore >>show tables;
+-----------------------+
| Tables_in_test_ignore |
+-----------------------+
|aaa|
| aab |
| aac |
| aad |
| aae |
+-----------------------+
5 rows in set (0.00 sec)

Found that it has been synchronized. At this time, the master-slave synchronization state is in place. If we delete the table on the master database now, the table on the slave database will definitely be deleted, which is not the result we want.

Obviously, the next step is to configure the replicate-wild-ignore-table parameter. Generally, we need to configure the my.cnf file by stopping the slave service. If we want to configure multiple tables, we need to write multiple wildcard records in the my.cnf file. For example, in this example, the value of the parameter needs to be configured as test_ignore.aa%, where % represents a wildcard. That is to say, table operations in the test_ignore database in the format of aa% will be filtered out. The tables aaa, aab, aac, aad, and aae we created are all in this shape, so operations on these tables will definitely not be synchronized to the slave database. Let's test it:

First check the current replication status:

Double Yes status means there is no problem with the replication relationship

The main library operates:

mysql :test_ignore >>drop table aaa;
Query OK, 0 rows affected (0.01 sec)

mysql :test_ignore >>drop table aab;
Query OK, 0 rows affected (0.00 sec)

View from the library:

mysql:test_ignore >>show tables;
+-----------------------+
| Tables_in_test_ignore |
+-----------------------+
|aaa|
| aab |
| aac |
| aad |
| aae |
+-----------------------+
5 rows in set (0.00 sec)

The table on the slave database is still there, indicating that the operations on the master database have not been synchronized to the slave database. The parameters we configured

replicate-wild-ignore-table=test_ignore.aa%

It worked. At this point, if we create a table on the master database:

`Main library`
mysql:test_ignore >>create table aaf(id int);
Query OK, 0 rows affected (0.00 sec)

`From library`
mysql:test_ignore >>show tables;
+-----------------------+
| Tables_in_test_ignore |
+-----------------------+
|aaa|
| aab |
| aac |
| aad |
| aae |
+-----------------------+
5 rows in set (0.00 sec)

It is found that the slave database does not synchronize the table aaf of the master database, because aaf also matches the rule test_ignore.aa%.

By using this feature, we can solve this business scenario very well, that is, the main database is deleted and the slave database retains the data. However, this method has a serious problem, which is that the slave database needs to be restarted every time. If we need to configure the second rule and the third rule, we need to restart the slave database 2 or 3 times. During this process, the slave database is invisible to the business party. If it cannot be accessed, it is likely to cause a program error, which is unbearable.

This process must be resolved, how to solve it? Is there a way to modify the replication filter without downtime? Look for official documentation.

Sure enough, it is impossible to shut down the machine, not even in this lifetime. There is such a sentence in the official document:

You can also create such a filter by issuing a CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE statement.

What the hell is this statement? It means it has never been used. You can modify the filter by changing the copy filter online. See the introduction in the official document:

I saw a magical sentence, let’s try it:

mysql:test_ignore >>change replication filter replicate_wild_ignore_table=('test_ig%.aa%');
ERROR 3017 (HY000): This operation cannot be performed with a running slave sql thread; run STOP SLAVE SQL_THREAD first

mysql:test_ignore >>stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql:test_ignore >>change replication filter replicate_wild_ignore_table=('test_ig%.aa%');
Query OK, 0 rows affected (0.00 sec)

mysql:test_ignore >>start slave;
Query OK, 0 rows affected (0.01 sec)

When used directly, it prompts that slave sql_thread needs to be stopped. After thinking about it, I can understand that it seems a bit inappropriate to modify the replication rules without stopping replication. I simply stop the entire replication, and then modify the replication filter again. It works. It is successfully executed and replication is enabled. The operation is smooth.

Let's take a look at the status of the replication relationship:

The ignored table rule has become test_ig%.aa%, that is, all operations on tables starting with aa in the database starting with test_ig will not be synchronized to the slave database, including alter, drop, and create operations on the table.

But here, the solution comes out. We know that the daily table is generally in the format of YYYYMMDD. We only need to filter the daily table in the format of YYYYMM% and then delete it on the master database. This operation will not be synchronized to the slave database, so this problem can be solved smoothly.

Of course, in addition to this solution, there are some other solutions, such as:

If the business tolerates partial data loss, we can also use the method of closing binlog---deleting the table---opening binlog to prevent the slave database from synchronizing the drop operation of the master database;

All online daily table operations are configured as ignore, and then triggers are used to synchronize updates in the daily table to the slave database;

This series of operations does not actually solve the problem fundamentally. It is essentially a business design problem. There are too many dot logs in the daily table. These dot logs can be appropriately reduced. For dot logs, the retention period needs to be determined. Expired logs need to be cleaned up in time to ensure the server's indicators and performance.

The above is the details of how to dynamically modify the replication filter in MySQL. For more information about dynamically modifying the replication filter in MySQL, please pay attention to other related articles on 123WORDPRESS.COM!