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

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

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

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

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

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

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

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

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

3、使用replicate-ignore-table參數(shù)進(jìn)行對(duì)于指定的表進(jìn)行過濾。設(shè)置了這個(gè)參數(shù)，可以讓你過濾指定數(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ī)則中制定了表a，那么表a的操作就不會(huì)同步到從庫(kù)中了。

這和我們的需求符合，也就是我們?nèi)绻O(shè)置了要過濾的表，那么當(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)同步過來了。此時(shí)是處于主從同步狀態(tài)，如果現(xiàn)在我們?cè)谥鲙?kù)上刪除表，那么從庫(kù)上的表一定會(huì)刪除，這不是我們想要的結(jié)果。

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

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

雙Yes狀態(tài)，說明復(fù)制關(guā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ù)上的表還在，說明主庫(kù)上的操作沒有被同步到從庫(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ù)并沒有同步主庫(kù)的表aaf，因?yàn)閍af也匹配了test_ignore.aa%這條規(guī)則。

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

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

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

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

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

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

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，想想也能理解，不停止復(fù)制直接修改復(fù)制的規(guī)則好像有點(diǎn)不妥，索性停止了整個(gè)復(fù)制，然后重新修改復(fù)制過濾器，妥了，成功執(zhí)行，開啟復(fù)制，一套操作行云流水。

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

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

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

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

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

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

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

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