mysql多源復(fù)制詳解

大家應(yīng)該知道，MySQL比起其他數(shù)據(jù)庫，最大的特色是就是主從復(fù)制，不過5.7之前的版本最多就只支持一主多從的復(fù)制方式，對(duì)于一些統(tǒng)計(jì)類的需求，就需要跨庫，這是比較麻煩的事情，以往只能交由數(shù)據(jù)庫中間件（mycat等）去做這種事情，還有備份操作，也只能一個(gè)個(gè)庫的用腳本去輪詢或并發(fā)備份，不可謂不費(fèi)時(shí)費(fèi)力。然后，多源復(fù)制的概念出來了。

創(chuàng)新互聯(lián)建站主營(yíng)惠民網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營(yíng)網(wǎng)站建設(shè)方案,成都App定制開發(fā),惠民h5微信小程序搭建,惠民網(wǎng)站營(yíng)銷推廣歡迎惠民等地區(qū)企業(yè)咨詢

多源復(fù)制的概念最早是mariadb社區(qū)提出的，后來mysql官方積極引入到5.7的版本中來（5.6最新版也是沒有這個(gè)功能的），percona就不說了，也是隨后推出。這個(gè)功能很好的解決了上面說的問題，最直接的當(dāng)然是省事省錢了，如果把并發(fā)復(fù)制也開啟，在內(nèi)網(wǎng)環(huán)境下，延時(shí)幾乎是可以忽略的。

個(gè)人建議，多源復(fù)制不宜做唯一的從庫，而是做第二/三從庫或?qū)蛹?jí)從庫比較好，主要目的應(yīng)該是統(tǒng)計(jì)庫或備份庫。因?yàn)橥ǔ碚f，多源復(fù)制為了避免某些數(shù)據(jù)沖突是會(huì)忽略某些數(shù)據(jù)庫和表的復(fù)制的，而為了提高數(shù)據(jù)庫高可用，是需要整庫同步的，例如MHA和PXC環(huán)境幾乎就是要主從是一致的，這樣就有沖突而導(dǎo)致不能做高可用了，所以，做第二/三從庫或?qū)蛹?jí)從庫才可以避免。

前置環(huán)境說明

現(xiàn)在測(cè)試的架構(gòu)是主庫1的work1和主庫2的work2，進(jìn)行多源復(fù)制到從庫3中，而且忽略系統(tǒng)庫mysql庫，這么簡(jiǎn)單我就不打算畫圖了，估計(jì)網(wǎng)上也有很多，因?yàn)楹竺鎯?nèi)容不少，我也不想占版面了，所以隨便說一下就算了。

主庫1：mysql版本：阿里云rdsmysql5.6.34，ip地址：10.2.0.5

主庫2：mysql版本：自建percona-server5.7.18，ip地址：10.2.0.6

從庫3：mysql版本：自建percona-server5.7.18，ip地址：10.2.0.7

目的就是：

主庫1---work1\

                       從庫3

主庫2---work2/

怎么搭建mysql數(shù)據(jù)庫我就不說了，這點(diǎn)各位自行研究，我想要看這篇文章的人估計(jì)在搭建方面至少是沒壓力的，同時(shí)也假設(shè)是有一定業(yè)務(wù)的數(shù)據(jù)庫存在，其實(shí)沒有也可以，模擬就行。

然后，看下面的操作。

導(dǎo)出與導(dǎo)入數(shù)據(jù)庫

就算不是做多源復(fù)制，普通的主從復(fù)制環(huán)境都需要把數(shù)據(jù)導(dǎo)出和導(dǎo)入數(shù)據(jù)，畢竟binlog不是一直都有記錄的（保留策略問題），數(shù)據(jù)量多起來的話，讓他從新跑也是不現(xiàn)實(shí)的。

而由于我們多源復(fù)制的原意是只復(fù)制有需求的數(shù)據(jù)庫，如果使用xtrabackup就不太合適了，因?yàn)槟J(rèn)也是會(huì)備份mysql庫的，這個(gè)時(shí)候用mysqldump就合適一些了，除非前面一個(gè)庫用xtrabackup，下一個(gè)用mysqldump就可以。我覺得mysqlpump和mydumper也是可以嘗試一下。

下面來看看操作

#從主庫導(dǎo)出數(shù)據(jù)庫
mysqldump -uroot -p'******' -h20.2.0.5 -P3306 --triggers -R --single-transaction \
--no-autocommit --master-data=2 -q -e --databases work1 >work1.sql
mysqldump -uroot -p'******' -h20.2.0.6 -P3306 --triggers -R --single-transaction \
--no-autocommit --master-data=2 -q -e --databases work2 >work2.sql

可能導(dǎo)出的時(shí)候會(huì)有個(gè)warnings的警告，說會(huì)導(dǎo)出設(shè)定GTID的sql，不過這個(gè)正是我們后面需要的東西，所以可以忽略。

然后，就導(dǎo)入到目的從庫環(huán)境吧，只要數(shù)據(jù)不沖突，都可以導(dǎo)進(jìn)去。理論上，多源復(fù)制要禁止一切同名數(shù)據(jù)庫的存在，不然就不是多源復(fù)制了。不過，有一種情況我覺得可以嘗試下，我個(gè)人還沒試過，就是同一個(gè)庫名，多源復(fù)制不同的數(shù)據(jù)表，各位有興趣可以自行嘗試，這里不展開。

#導(dǎo)入數(shù)據(jù)庫到從庫
mysql -uroot -p'******' -h20.2.0.7 -P3306
>create database work1
>use work1 
>source work1.sql
>create database work2
>use work2 
>source work2.sql
#也可以這樣
mysql -uroot -p'******' -h20.2.0.7 -P3306 -e "create database work1;use work1;source work1.sql;"
mysql -uroot -p'******' -h20.2.0.7 -P3306 -e "create database work2;use work2;source work2.sql;"

導(dǎo)完之后，如果嚴(yán)謹(jǐn)一些，就看看數(shù)據(jù)表是不是全都導(dǎo)進(jìn)去了，其他就等下一步開始配置了。

配置多源復(fù)制環(huán)境

以下操作，大部分都是在從庫執(zhí)行的，主庫最多就是授權(quán)一下，如果已經(jīng)做過了主從架構(gòu)，一般這個(gè)也是早就授權(quán)過的了，直接拿來用就可以的了。

如果配過主從復(fù)制環(huán)境，應(yīng)該知道舊模式是要確定pos位置，新模式則是要設(shè)置GTID的編號(hào)。至于怎么確定，直接more看一下就知道了。

#看以下sql的備份文件
more work1.sql
    .
    .
    .
-- GTID state at the beginning of the backup 
--
SET @@GLOBAL.GTID_PURGED='3edae34c-6299-11e6-95cd-8038bc0c67be:1-6758,
4cdc2a74-6299-11e6-95ce-008cfaf595bc:1-38813008';
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.001284', MASTER_LOG_POS=3954096;
    .
    .
    .

這里不打算詳細(xì)解析，有興趣的可以看我另一篇關(guān)于搭建普通主從架構(gòu)的文章，里面有詳細(xì)解析?，F(xiàn)在這里可以看到gtid和pos值，后面我們拿來用就可以了。

然后，進(jìn)入正題：

首先，我們要把從庫配置文件改一下，添加些東西。

#在從庫的my.cnf配置文件中添加配置項(xiàng)，需要重啟mysql實(shí)例
vim my.cnf
[mysqld]
master_info_repository=TABLE
relay_log_info_repository=TABLE
replicate_wild_do_table=work1.%
replicate_wild_do_table=work2.%
read-only
#5.6以上參數(shù)，并行復(fù)制線程數(shù)
slave_parallel_workers = 4
#5.7新參數(shù)，并行復(fù)制模式選擇，5.6沒用
#slave_parallel_type = LOGICAL_CLOCK
#當(dāng)然也可以在線實(shí)現(xiàn)設(shè)置，目的就是把主從配置信息改為記錄到表
mysql>SET GLOBAL master_info_repository = 'TABLE';
mysql>SET GLOBAL relay_log_info_repository = 'TABLE';
mysql>change replication filter REPLICATE_IGNORE_DB=(mysql) ;

需要特別注意replicate_wild_do_table這個(gè)參數(shù)，這個(gè)參數(shù)的意思是只執(zhí)行某個(gè)庫或某個(gè)表的同步語句,其他庫和表都不處理，從而達(dá)到選擇性復(fù)制業(yè)務(wù)庫的目的，這就不會(huì)有無用的數(shù)據(jù)了，也不會(huì)互相干擾。官方文檔解析只能一個(gè)參數(shù)標(biāo)記一個(gè)庫，而且這個(gè)參數(shù)是全局通用，也就是說多源復(fù)制下，所有源頻道都通用這個(gè)配置。

開啟只讀read-only就不用多說了，多源復(fù)制一般就只有讀的需求，就不要讓他們有寫庫的可能了。

并發(fā)復(fù)制多線程slave_parallel_workers是5.6，5.7新出的功能，能有效加快復(fù)制的效率，特別5.7是支持事務(wù)并發(fā)復(fù)制的，速度相當(dāng)可觀，這里設(shè)置了每個(gè)復(fù)制連接有4個(gè)并發(fā)線程。

而slave_parallel_type是選擇并發(fā)復(fù)制方式，默認(rèn)是為了兼容5.6以庫模式的并發(fā)復(fù)制，這里是改成5.7新出的以組提交事務(wù)的方式并發(fā)復(fù)制，并發(fā)效果更佳，但是要特別注意如果主從服務(wù)器數(shù)據(jù)庫版本不一致的話，最好還是不要改，因?yàn)橹挥衜ysql5.7是默認(rèn)開啟組提交功能的。

--------------------------------------------------------------------

然后，在主庫授權(quán)復(fù)制的賬號(hào)，已做過同類賬戶授權(quán)的可以忽略

#在主庫授權(quán)，已做過的可以忽略
mysql -uroot -p'******' -h20.2.0.5 -P3306
grant replication slave on *.* to 'rep'@'%' identified by '123123'；
mysql -uroot -p'******' -h20.2.0.6 -P3306
grant replication slave on *.* to 'rep'@'%' identified by '123123'；

---------------------------------------------------------------------

好了，開始正式配置多源復(fù)制，這個(gè)GTID模式其實(shí)比起傳統(tǒng)pos的模式更復(fù)雜一些，不過，未來都是用GTID多的了，所以我就拿GTID模式來做演示為主。

#登錄進(jìn)從庫數(shù)據(jù)庫
mysql -uroot -p'******' -h20.2.0.7 -P3306
#先清空所有主從結(jié)構(gòu)的記錄
reset slave all；
#配置阿里云的數(shù)據(jù)庫，這是GTID模式
change master to
master_host='10.2.0.5',
master_user='rep',
master_password='123123',
master_port=3306,
MASTER_AUTO_POSITION = 1
FOR CHANNEL 'al_RDS';
#配置自建的數(shù)據(jù)庫，這是GTID模式
change master to
master_host='10.2.0.6',
master_user='rep',
master_password='123123',
master_port=3306,
MASTER_AUTO_POSITION = 1
FOR CHANNEL 'me_mysql';
#重置GTID值
reset master；
#設(shè)置GTID值
SET @@GLOBAL.GTID_PURGED='09cb91bf-2669-11e7-8b70-00163e0835ff:1-486646,3edae34c-6299-11e6-95cd-8038bc0c67be:1-6758,
4cdc2a74-6299-11e6-95ce-008cfaf595bc:1-38813008';

需要特別注意的兩點(diǎn)是，多源復(fù)制提供了channel的標(biāo)識(shí)，區(qū)分了不同的源頻道，所以配置的時(shí)候就需要加上指定頻道名稱FOR CHANNEL 'al_RDS';這樣。而GTID的值和replicate_wild_do_table參數(shù)一樣，默認(rèn)是全局的配置，讓源頻道各取所需，所以，我們的gtid值，應(yīng)該是所有*.sql文件的gtid值的合集，用‘，’號(hào)分隔，最終就出現(xiàn)我這個(gè)要設(shè)置這么多GTID的情況。

然后都配置完成了，就可以啟動(dòng)了，啟動(dòng)和關(guān)閉都可以指定特定的源頻道，相當(dāng)方便，下面列舉一下命令。

#啟動(dòng)/關(guān)閉所有源頻道
start/stop slave；
#啟動(dòng)/關(guān)閉單一的源頻道
start/stop slave for channel 'al_RDS';
#當(dāng)然重置也是可以單獨(dú)頻道的
#RESET SLAVE FOR CHANNEL 'al_RDS';
#查看狀態(tài)也是可以單獨(dú)的，不指定就是全部了
#show slave status for channel 'al_RDS';

然后，看看狀態(tài)，show slave status看看。

#在從庫上執(zhí)行命令
mysql -uroot -p'******' -h20.2.0.7 -P3306
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.2.0.5
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.001297
          Read_Master_Log_Pos: 5607291
               Relay_Log_File: beifen1-relay-bin-al_rds.000030
                Relay_Log_Pos: 5607464
        Relay_Master_Log_File: mysql-bin.001297
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: work1.%,work2.%
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 5607291
              Relay_Log_Space: 5607767
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2721321239
                  Master_UUID: 4cdc2a74-6299-11e6-95ce-008cfaf595bc
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 4cdc2a74-6299-11e6-95ce-008cfaf595bc:38888940-39258544
            Executed_Gtid_Set: 09cb91bf-2669-11e7-8b70-00163e0835ff:1-640645,
1db4cb1b-5e00-11e7-89eb-00163e046b4a:1-8,
3edae34c-6299-11e6-95cd-8038bc0c67be:1-6758,
4cdc2a74-6299-11e6-95ce-008cfaf595bc:1-39258544
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: al_rds
           Master_TLS_Version: 
*************************** 2. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.2.0.6
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000013
          Read_Master_Log_Pos: 246854093
               Relay_Log_File: beifen1-relay-bin-me_mysql.000004
                Relay_Log_Pos: 155502415
        Relay_Master_Log_File: mysql-bin.000013
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: work1.%,work2.%
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 246854093
              Relay_Log_Space: 155502632
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 253241
                  Master_UUID: 817498dc-2676-11e7-a673-00163e024674
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 09cb91bf-2669-11e7-8b70-00163e0835ff:514003-640645
            Executed_Gtid_Set: 09cb91bf-2669-11e7-8b70-00163e0835ff:1-640645,
1db4cb1b-5e00-11e7-89eb-00163e046b4a:1-8,
3edae34c-6299-11e6-95cd-8038bc0c67be:1-6758,
4cdc2a74-6299-11e6-95ce-008cfaf595bc:1-39258544
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: me_mysql
           Master_TLS_Version: 
2 rows in set (0.00 sec)

很長(zhǎng)，我們看到有兩條記錄，其中Replicate_Wild_Do_Table，Executed_Gtid_Set是全局通用，兩邊都有，印證了我說的參數(shù)是全局的。然后各自的Retrieved_Gtid_Set是不同的，他們都自行選擇了，相當(dāng)智能。再看到Channel_Name就是他們不同的頻道名字了。然后可以看到，Slave_IO_Running和Slave_SQL_Running的雙yes，Master_Log_File=Relay_Master_Log_File，Read_Master_Log_Pos=Exec_Master_Log_Pos，Seconds_Behind_Master為0，所以現(xiàn)在是同步的。

--------------------------------------------------------------------------

如果是傳統(tǒng)的模式，反而更簡(jiǎn)單，不用設(shè)置GTID的值，類似下面這樣指定log文件名和pos即可，不用設(shè)置SET @@GLOBAL.GTID_PURGED就可以啟動(dòng)。

#設(shè)置頻道一
change master to
master_host='10.2.0.5',
master_user='rep',
master_password='123123',
master_port=3306,
MASTER_LOG_FILE='mysql-bin.001284',
MASTER_LOG_POS=3954096
FOR CHANNEL 'al_RDS';
#設(shè)置頻道二
change master to
master_host='10.2.0.6',
master_user='rep',
master_password='123123',
master_port=3306,
MASTER_LOG_FILE='mysql-bin.000014',
MASTER_LOG_POS=67456
FOR CHANNEL 'me_mysql';
#啟動(dòng)
start slave；

--------------------------------------------------------------------------

問題匯總

1.在停止多元復(fù)制環(huán)境時(shí)要注意并行復(fù)制的進(jìn)度，例如出現(xiàn)下面這種情況，就先等一等再停止。

#請(qǐng)關(guān)注Executed_Gtid_Set:項(xiàng)
show slave status\G
    .
    .
    .
             Master_Server_Id: 2721321239
                  Master_UUID: 4cdc2a74-6299-11e6-95ce-008cfaf595bc
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Reading event from the relay log
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 4cdc2a74-6299-11e6-95ce-008cfaf595bc:50007036-50049107
            Executed_Gtid_Set: 09cb91bf-2669-11e7-8b70-00163e0835ff:1-47551250,
3edae34c-6299-11e6-95cd-8038bc0c67be:1-6758,
4cdc2a74-6299-11e6-95ce-008cfaf595bc:1-50010063:50010080-50010093:50010099-50010101:50010108:50010130-50010139:50010145-50010148:50010158:50010179-50010184:50010190-50010200:50010207:50010215-50010221:50010227-50010236:50010243:50010276-50010285:50010291-50010293:50010300:50010308-50010312:50010326-50010328:50010371-50010373:50010391-50010393:50010403-50010405:50010427-50010429:50010464-50010466:50010480-50010482:50010490-50010496:50010518-50010520:50010538-50010540:50010551-50010553:50010574
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: al_rds
           Master_TLS_Version: 
*************************** 2. row ***************************
               Slave_IO_State: Waiting for master to send event
    .
    .
    .

那是因?yàn)槟氵@么一停止，并行復(fù)制就中途停止了，就有可能出現(xiàn)有些數(shù)據(jù)回滾不了，或者有些數(shù)據(jù)復(fù)制錯(cuò)誤，然后后續(xù)你還想把他起來就很大可能會(huì)報(bào)錯(cuò)了，所以寧愿先等一等，再停止。

當(dāng)然了，如果是線上環(huán)境，究竟要等到什么時(shí)候？所以最好就是數(shù)據(jù)庫不繁忙的時(shí)候再做。要么，你就是準(zhǔn)備好重做的心態(tài)了，那就來吧。

分享題目：mysql多源復(fù)制詳解
轉(zhuǎn)載來于：http://muchs.cn/article18/gdspdp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供微信小程序、網(wǎng)站設(shè)計(jì)公司、外貿(mào)建站、網(wǎng)站內(nèi)鏈、網(wǎng)站排名、響應(yīng)式網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)

外貿(mào)網(wǎng)站制作