MySQL動態(tài)修改復制過濾器

說說今天遇到的問題吧，今天在處理一個業(yè)務方的需求，比較變態(tài)，我大概描述一下：

1、線上的阿里云rds上面有個游戲的日志庫，里面的表都是日表的形式，數(shù)據(jù)量比較大了，每次備份的時候，都會導致線上的rds報警，報警內(nèi)容是IO資源占用過多。

2、這個rds上有一個本地的ECS只讀從庫，這個只讀從庫會實時同步線上的rds數(shù)據(jù)庫中的數(shù)據(jù)，這個只讀從庫供業(yè)務方查詢使用

3、業(yè)務方說這些數(shù)據(jù)都還有用，只讀從庫上的數(shù)據(jù)必須有，線上rds上的數(shù)據(jù)可以刪除，保留兩個星期即可。

場景就是這么個場景，DBA想要解決報警這個問題，業(yè)務方想要保證擁有完整的數(shù)據(jù)。請問，怎么解決？

當時看到這個問題，我想罵人，這需求一看就不合理，哪兒有刪除一個庫，另外一個庫上還保留的道理，況且都是些日志數(shù)據(jù)，不直接搞個冷備份，然后刪除線上，搞這么一出干啥啊。但是啊，怎么說也沒有緩和的余地，于是就開始思考這個問題應該怎么解決。我想到的解決辦法有以下幾個：

1、擴容，提升性能。數(shù)據(jù)量大，擴磁盤唄，IO使用率高，提升性能么，這是最直接的解決辦法，也是最貴的解決辦法，首先被砍掉。

2、先備份再刪除再還原。rds主庫上提前備份日表數(shù)據(jù)，然后刪除數(shù)據(jù)，此時從庫會同步刪除數(shù)據(jù)，然后再將第一步備份的數(shù)據(jù)還原到從庫上。這個辦法從可行性上來講是可以的，因為保證了沒有數(shù)據(jù)丟失。但是操作起來比較麻煩，手續(xù)太多，不夠方便。

3、使用replicate-ignore-table參數(shù)進行對于指定的表進行過濾。設(shè)置了這個參數(shù)，可以讓你過濾指定數(shù)據(jù)表的所有操作。我們看看官方文檔對這個參數(shù)的描述，給個鏈接：https://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html#option_mysqld_replicate-wild-ignore-table

描述如下：

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,

上面的意思是你可以使用這個參數(shù)創(chuàng)建一個過濾器，從而過濾掉匹配你制定的規(guī)則的特定表的操作（聽著很繞口），就是說你可以制定過濾規(guī)則，加入規(guī)則中制定了表a，那么表a的操作就不會同步到從庫中了。

這和我們的需求符合，也就是我們?nèi)绻O(shè)置了要過濾的表，那么當我們進行刪除表操作的時候，從庫中不會對表進行刪除，就實現(xiàn)了我們想要的結(jié)果。測試一下這個功能吧：

首先我們創(chuàng)建數(shù)據(jù)庫test_ignore，然后在其中創(chuàng)建表：

主庫上操作：

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)

從庫上查看：

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

發(fā)現(xiàn)已經(jīng)同步過來了。此時是處于主從同步狀態(tài)，如果現(xiàn)在我們在主庫上刪除表，那么從庫上的表一定會刪除，這不是我們想要的結(jié)果。

很顯然，接下來的一步是配置replicate-wild-ignore-table這個參數(shù)了，一般情況下，我們需要通過停止從庫的服務進行my.cnf文件的配置，如果我們要配置多個表，則需要在my.cnf文件中寫多條通配的記錄。例如，在本例子中，需要配置該參數(shù)的值為test_ignore.aa%,其中%代表通配符，也就是說，test_ignore數(shù)據(jù)庫中形如aa%這種格式的表操作都會被過濾掉。而我們創(chuàng)建的表aaa、aab、aac、aad、aae都是形如這種的，所以針對這幾個表的操作一定不會同步到從庫了，我們測試一下：

首先查看當前的復制狀態(tài)：

雙Yes狀態(tài)，說明復制關(guān)系沒有問題

主庫進行操作：

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)

從庫上進行查看：

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

從庫上的表還在，說明主庫上的操作沒有被同步到從庫，我們配置的參數(shù)

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

起作用了。此時，如果我們在主庫上創(chuàng)建一個表：

`主庫`mysql :test_ignore >>create table aaf(id int);Query OK, 0 rows affected (0.00 sec)`從庫`mysql :test_ignore >>show tables;+-----------------------+| Tables_in_test_ignore |+-----------------------+| aaa || aab || aac || aad || aae |+-----------------------+5 rows in set (0.00 sec)

發(fā)現(xiàn)從庫并沒有同步主庫的表aaf，因為aaf也匹配了test_ignore.aa%這條規(guī)則。

利用這個特性，我們能夠很好的解決這個業(yè)務場景，也就是主庫刪除，從庫保留數(shù)據(jù)。但是，這里要說但是了，這個方法有一個比較嚴重的問題，就是每次都需要重啟從庫，如果我們需要配置第二條規(guī)則，第三條規(guī)則，則需要重啟從庫2次，3次，這個過程中，從庫對于業(yè)務方是不可見的，如果無法訪問，很可能造成程序報錯，這是我們不能忍受的。

這個過程肯定是要解決的，怎么解決呢？能不能找到不停機就能修改復制過濾器的方法？找找官方文檔。

果然，停機是不可能停機的，這輩子都不可能停機。官方文檔中有這么一句話：

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

我去，這是個啥語句，表示從來沒有用過，可以通過在線變更復制過濾器的方法來對過濾器進行修改，看看官方文檔中的介紹：

看到了一個神奇的語句，趕緊來試試：

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 firstmysql :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)

直接使用，提示需要stop slave sql_thread，想想也能理解，不停止復制直接修改復制的規(guī)則好像有點不妥，索性停止了整個復制，然后重新修改復制過濾器，妥了，成功執(zhí)行，開啟復制，一套操作行云流水。

再來看看復制關(guān)系中的狀態(tài)：

忽略的表規(guī)則已經(jīng)變成了test_ig%.aa%，也就是說，以test_ig開頭的數(shù)據(jù)庫中以aa開頭的表的操作，都不會被同步到從庫，包括對表的alter和drop以及create操作。

但這里，方案就出來了，我們知道，日表一般是YYYYMMDD這種形式的，我們只要過濾YYYYMM%這種格式的日表，然后在主庫上對它進行刪除，這個操作將不會被同步到從庫，那么這個問題就可以順利解決了。

當然，除了這個方案之外，還有一些方案，例如：

如果業(yè)務容忍部分數(shù)據(jù)丟失，我們還可以使用關(guān)閉binlog---刪表---打開binlog的方式使得從庫不會同步主庫的drop操作；

線上所有的日表操作都配置成ignore，然后利用觸發(fā)器將日表中的更新同步到從庫中；

這一系列的操作，其實不是從本質(zhì)上解決問題，本質(zhì)上還是業(yè)務設(shè)計的問題，日表中的打點日志太多，可以適當減少這些打點日志，對于打點日志，需要確定保留周期，過期的日志，需要及時清理，保證服務器的指標和性能。

以上就是mysql 如何動態(tài)修改復制過濾器的詳細內(nèi)容，更多關(guān)于MySQL動態(tài)修改復制過濾器的資料請關(guān)注好吧啦網(wǎng)其它相關(guān)文章！