MySQL動(dòng)態(tài)修改復(fù)制過(guò)濾器

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

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

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

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

    場(chǎng)景就是這么個(gè)場(chǎng)景，DBA想要解決報(bào)警這個(gè)問(wèn)題，業(yè)務(wù)方想要保證擁有完整的數(shù)據(jù)。請(qǐng)問(wèn)，怎么解決？

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

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

2、先備份再刪除再還原。rds主庫(kù)上提前備份日表數(shù)據(jù)，然后刪除數(shù)據(jù)，此時(shí)從庫(kù)會(huì)同步刪除數(shù)據(jù)，然后再將第一步備份的數(shù)據(jù)還原到從庫(kù)上。這個(gè)辦法從可行性上來(lái)講是可以的，因?yàn)楸ＷC了沒(méi)有數(shù)據(jù)丟失。但是操作起來(lái)比較麻煩，手續(xù)太多，不夠方便。

3、使用replicate-ignore-table參數(shù)進(jìn)行對(duì)于指定的表進(jìn)行過(guò)濾。設(shè)置了這個(gè)參數(shù)，可以讓你過(guò)濾指定數(shù)據(jù)表的所有操作。我們看看官方文檔對(duì)這個(gè)參數(shù)的描述，給個(gè)鏈接：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,

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

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

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

主庫(kù)上操作：

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)

從庫(kù)上查看：

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)同步過(guò)來(lái)了。此時(shí)是處于主從同步狀態(tài)，如果現(xiàn)在我們?cè)谥鲙?kù)上刪除表，那么從庫(kù)上的表一定會(huì)刪除，這不是我們想要的結(jié)果。

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

首先查看當(dāng)前的復(fù)制狀態(tài)：

雙Yes狀態(tài)，說(shuō)明復(fù)制關(guān)系沒(méi)有問(wèn)題

主庫(kù)進(jì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)

從庫(kù)上進(jìn)行查看：

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

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

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

起作用了。此時(shí)，如果我們?cè)谥鲙?kù)上創(chuàng)建一個(gè)表：

`主庫(kù)`
mysql :test_ignore  >>create table aaf(id int);
Query OK, 0 rows affected (0.00 sec)

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

  發(fā)現(xiàn)從庫(kù)并沒(méi)有同步主庫(kù)的表aaf，因?yàn)閍af也匹配了test_ignore.aa%這條規(guī)則。

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

   這個(gè)過(guò)程肯定是要解決的，怎么解決呢？能不能找到不停機(jī)就能修改復(fù)制過(guò)濾器的方法？找找官方文檔。

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

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

我去，這是個(gè)啥語(yǔ)句，表示從來(lái)沒(méi)有用過(guò)，可以通過(guò)在線變更復(fù)制過(guò)濾器的方法來(lái)對(duì)過(guò)濾器進(jìn)行修改，看看官方文檔中的介紹：

看到了一個(gè)神奇的語(yǔ)句，趕緊來(lái)試試：

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)

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

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

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

   但這里，方案就出來(lái)了，我們知道，日表一般是YYYYMMDD這種形式的，我們只要過(guò)濾YYYYMM%這種格式的日表，然后在主庫(kù)上對(duì)它進(jìn)行刪除，這個(gè)操作將不會(huì)被同步到從庫(kù)，那么這個(gè)問(wèn)題就可以順利解決了。

   當(dāng)然，除了這個(gè)方案之外，還有一些方案，例如：

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

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

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

以上就是mysql 如何動(dòng)態(tài)修改復(fù)制過(guò)濾器的詳細(xì)內(nèi)容，更多關(guān)于MySQL動(dòng)態(tài)修改復(fù)制過(guò)濾器的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論