詳細介紹MySQL Group Replication[Multi-Primary Mode]的搭建部署過程（圖文）

3010

1，關于MySQL Group Replication

基于組的復制（group-basedreplication）是一種被使用在容錯系統中的技術。replication-group（復制組）是由能夠相互通信的多個服務器（節點）組成的。

在通信層，Groupreplication實現了一系列的機制：比如原子消息（atomicmessage delivery）和全序化消息（totalorderingof messages）。這些原子化，抽象化的機制，為實現更先進的數據庫復制方案提供了強有力的支持。

MySQL Group Replication正是基于這些技術和概念，實現了一種多主全更新的復制協議。

簡而言之，一個Replication-group就是一組節點，每個節點都可以獨立執行事務，而讀寫事務則會在于group內的其他節點進行協調之后再commit。

因此，當一個事務準備提交時，會自動在group內進行原子性的廣播，告知其他節點變更了什么內容/執行了什么事務。

這種原子廣播的方式，使得這個事務在每一個節點上都保持著同樣順序。

這意味著每一個節點都以同樣的順序，接收到了同樣的事務日志，所以每一個節點以同樣的順序重演了這些事務日志，最終整個group保持了完全一致的MySQL。

?

然而，不同的節點上執行的事務之間有可能存在資源爭用。這種現象容易出現在兩個不同的并發事務上。

假設在不同的節點上有兩個并發事務，更新了同一行數據，那么就會發生資源爭用。

面對這種情況，GroupReplication判定先提交的事務為有效事務，會在整個group里面重演，后提交的事務會直接中斷，或者回滾，最后丟棄掉。

?

因此，這也是一個無共享的復制方案，每一個節點都保存了完整的數據副本。看如下圖片01.png，描述了具體的工作流程，能夠簡潔的和其他方案進行對比。這個復制方案，在某種程度上，和數據庫狀態機（DBSM）的Replication方法比較類似。

?詳細介紹MySQL Group Replication[Multi-Primary Mode]的搭建部署過程（圖文）

?

2，安裝mysql 5.7.17

官方下載，不過官方只保留最新的version，5.7.17這個url地址不一定長期有效，所以，需要的不一定有，我這里在百度云盤保留了下來，版本是5.7.17，可以隨時去下載使用，

?在三臺db服務器上面設置/etc/hosts映射，如下：

192.168.121.71????db1????????????????????????????????????????????????????????????????????????????????????????????????????????????  192.168.121.111????db2  192.168.121.24????db3

安裝的數據庫服務器：

數據庫服務器地址???????

端口

數據目錄

Server-id?

192.168.121.71（db1）

3317

/data/mysql/data

12001????????

192.168.121.111（db2）

3317

/data/mysql/data

12002

192.168.121.24（db3）

3317

/data/mysql/data

12003


3，創建復制環境

設置hostname和ip映射

在db1、db2、db3上都設置

@@######@@

在db1/db2/db3上建立復制賬號：

vim?/etc/hosts  192.168.121.71?db1?hch_test_dbm2_121_71???????????????????????????????????????????  192.168.121.111?db2?bpe_service  192.168.121.24?db3?hch_test_web_1_24

4，安裝group replication插件

在db1、db2、db3上依次安裝group replication插件

mysql>?SET?SQL_LOG_BIN=0;  Query?OK,?0?rows?affected?(0.00?sec)  ?  mysql>  mysql>?GRANT?REPLICATION?SLAVE?ON?*.*??TO?'repl'@'192.168.%'?IDENTIFIED?BY?'rlpbright_1927@ys';  Query?OK,?0?rows?affected,?1?warning??(0.00?sec)  ?  mysql>??  mysql>?SET?SQL_LOG_BIN=1;  Query?OK,?0?rows?affected?(0.00?sec)  ?  mysql>

plugin-load=group_replication或者直接在MySQLmy.cnf中配置：

查看group replication組件

mysql>?INSTALL?PLUGIN?group_replication??SONAME?'group_replication.so';?????????????????????????  Query?OK,?0?rows?affected?(0.01?sec)  ?  mysql>

看到有group_replication ACTIVE，表示插group_replication插件安裝成功。?

5，配置group replication參數

確保binlog_format是row格式。

mysql>?show?plugins;????????????????????????????????????????????????????????????????????????????????????????????????????????  +----------------------------+----------+--------------------+----------------------+---------+  |?Name???????????????????????|?Status???|?Type???????????????|?Library??????????????|?License?|  +----------------------------+----------+--------------------+----------------------+---------+  |?binlog?????????????????????|?ACTIVE???|?STORAGE?ENGINE?????|?NULL?????????????????|?GPL?????|  |?mysql_native_password??????|?ACTIVE???|?AUTHENTICATION?????|?NULL?????????????????|?GPL?????|  |?sha256_password????????????|?ACTIVE???|?AUTHENTICATION?????|?NULL?????????????????|?GPL?????|  |?MyISAM?????????????????????|?ACTIVE???|?STORAGE?ENGINE?????|?NULL?????????????????|?GPL?????|  |?PERFORMANCE_SCHEMA?????????|?ACTIVE???|?STORAGE?ENGINE?????|?NULL?????????????????|?GPL?????|  |?InnoDB?????????????????????|?ACTIVE???|?STORAGE?ENGINE?????|?NULL?????????????????|?GPL?????|  |?INNODB_TRX?????????????????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|  |?INNODB_LOCKS???????????????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|  |?INNODB_LOCK_WAITS??????????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|  |?INNODB_CMP?????????????????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|  |?INNODB_CMP_RESET???????????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|  |?INNODB_CMPMEM??????????????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|  |?INNODB_CMPMEM_RESET????????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|  |?INNODB_CMP_PER_INDEX???????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|  |?INNODB_CMP_PER_INDEX_RESET?|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|  |?INNODB_BUFFER_PAGE?????????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|  |?INNODB_BUFFER_PAGE_LRU?????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|  |?INNODB_BUFFER_POOL_STATS???|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|  |?INNODB_TEMP_TABLE_INFO?????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|  |?INNODB_METRICS?????????????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|  |?INNODB_FT_DEFAULT_STOPWORD?|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|  |?INNODB_FT_DELETED??????????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|  |?INNODB_FT_BEING_DELETED????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|  |?INNODB_FT_CONFIG???????????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|  |?INNODB_FT_INDEX_CACHE??????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|  |?INNODB_FT_INDEX_TABLE??????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|  |?INNODB_SYS_TABLES??????????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|  |?INNODB_SYS_TABLESTATS??????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|  |?INNODB_SYS_INDEXES?????????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|  |?INNODB_SYS_COLUMNS?????????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|  |?INNODB_SYS_FIELDS??????????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|  |?INNODB_SYS_FOREIGN?????????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|  |?INNODB_SYS_FOREIGN_COLS????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|  |?INNODB_SYS_TABLESPACES?????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|  |?INNODB_SYS_DATAFILES???????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|  |?INNODB_SYS_VIRTUAL?????????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|  |?MRG_MYISAM?????????????????|?ACTIVE???|?STORAGE?ENGINE?????|?NULL?????????????????|?GPL?????|  |?MEMORY?????????????????????|?ACTIVE???|?STORAGE?ENGINE?????|?NULL?????????????????|?GPL?????|  |?CSV????????????????????????|?ACTIVE???|?STORAGE?ENGINE?????|?NULL?????????????????|?GPL?????|  |?BLACKHOLE??????????????????|?ACTIVE???|?STORAGE?ENGINE?????|?NULL?????????????????|?GPL?????|  |?partition??????????????????|?ACTIVE???|?STORAGE?ENGINE?????|?NULL?????????????????|?GPL?????|  |?FEDERATED??????????????????|?DISABLED?|?STORAGE?ENGINE?????|?NULL?????????????????|?GPL?????|  |?ARCHIVE????????????????????|?ACTIVE???|?STORAGE?ENGINE?????|?NULL?????????????????|?GPL?????|  |?ngram??????????????????????|?ACTIVE???|?FTPARSER???????????|?NULL?????????????????|?GPL?????|  |?group_replication??????????|?ACTIVE???|?GROUP?REPLICATION??|?group_replication.so?|?GPL?????|  +----------------------------+----------+--------------------+----------------------+---------+  45?rows?in?set?(0.00?sec)  ?  mysql>

兩種配置方式，在線添加 OR 配置文件

mysql>?show?variables?like?'binlog_format';?????????????????????????????????????????????  +---------------+-------+  |?Variable_name?|?Value?|  +---------------+-------+  |?binlog_format?|?ROW???|  +---------------+-------+  1?row?in?set?(0.00?sec)  ?  mysql>

配置完后，重啟3個db上的mysql服務

Db1啟動：

（1）?db1上的my.cnf配置：  #?replication?config  server_id=12001  gtid_mode=ON  enforce_gtid_consistency=ON  master_info_repository=TABLE  relay_log_info_repository=TABLE  binlog_checksum=NONE  log_slave_updates=ON  log_bin=binlog  binlog_format=ROW  ?  #?group?replication?config  transaction_write_set_extraction=XXHASH64  loose-group_replication_group_name="e4668cea-d7ca-11e6-86b5-18a99b76310d"????????????????????????  loose-group_replication_start_on_boot=off  loose-group_replication_local_address=?"db1:24901"  loose-group_replication_group_seeds=??"db1:24901,db2:24902,db3:24903"  loose-group_replication_bootstrap_group=??off  loose-group_replication_single_primary_mode=FALSE  loose-group_replication_enforce_update_everywhere_checks=??TRUE  ?  （2）db2上的my.cnf配置：  #?replication?config  server_id=12002  gtid_mode=ON  enforce_gtid_consistency=ON  master_info_repository=TABLE  relay_log_info_repository=TABLE  binlog_checksum=NONE  log_slave_updates=ON  log_bin=binlog  binlog_format=ROW  ?  #?group?replication?config  transaction_write_set_extraction=XXHASH64  loose-group_replication_group_name="e4668cea-d7ca-11e6-86b5-18a99b76310d"  loose-group_replication_start_on_boot=off  loose-group_replication_local_address=?"db2:24902"  loose-group_replication_group_seeds=??"db1:24901,db2:24902,db3:24903"  loose-group_replication_bootstrap_group=??off  loose-group_replication_single_primary_mode=FALSE  loose-group_replication_enforce_update_everywhere_checks=??TRUE  ?  ?  （3）db3上的my.cnf配置：  #?replication?config  server_id=12003  gtid_mode=ON  enforce_gtid_consistency=ON  master_info_repository=TABLE  relay_log_info_repository=TABLE  binlog_checksum=NONE  log_slave_updates=ON  log_bin=binlog  binlog_format=ROW  ?  #?group?replication?config  transaction_write_set_extraction=XXHASH64  loose-group_replication_group_name="e4668cea-d7ca-11e6-86b5-18a99b76310d"  loose-group_replication_start_on_boot=off  loose-group_replication_local_address=?"db3:24903"  loose-group_replication_group_seeds=??"db1:24901,db2:24902,db3:24903"  loose-group_replication_bootstrap_group=??off  loose-group_replication_single_primary_mode=FALSE  loose-group_replication_enforce_update_everywhere_checks=??TRUE

Db2啟動：?

[root@hch_test_dbm2_121_71?~]#?service??mysqld?start?????????????????????????????????????  Starting?MySQL..???????????????????????????????????????????[確定]  [root@hch_test_dbm2_121_71?~]#

Db3啟動：?

[root@bpe_service?~]#?service?mysqld??start  Starting?MySQL..???????????????????????????????????????????[確定]?????????????????????????????????????????????????????  [root@bpe_service?~]#

6，啟動mgr集群

構建之前，保證db1、db2、db3的read_only是關閉的

詳細介紹MySQL Group Replication[Multi-Primary Mode]的搭建部署過程（圖文）

開始構建group replication集群，通常操作命令

[root@hch_test_web_1_24?~]#?service??mysqld?start????????????????????????????????????????????  Starting?MySQL..???????????????????????????????????????????[確定]  [root@hch_test_web_1_24?~]#

db1上建立基本主庫master庫：

#?設置group_replication_bootstrap_group為ON是為了標示以后加入集群的服務器以這臺服務器為基準，以后加入的就不需要設置。

mysql>???CHANGE?MASTER?TO?MASTER_USER='repl',??MASTER_PASSWORD='rlpbright_1927@ys'?FOR?CHANNEL?'group_replication_recovery';  Query?OK,?0?rows?affected,?2?warnings??(0.02?sec)  ?  mysql>

Db2上啟動group_replication：?

Db2上mysql命令行上執行啟動：

mysql>?SET?GLOBAL?group_replication_bootstrap_group?=?ON;  Query?OK,?0?rows?affected?(0.00?sec)  ?  mysql>??START?GROUP_REPLICATION;  Query?OK,?0?rows?affected?(1.03?sec)  ?  mysql>?SET?GLOBAL?group_replication_bootstrap_group=OFF;  Query?OK,?0?rows?affected?(0.00?sec)  ?  mysql>  mysql>??SELECT?*?FROM?performance_schema.replication_group_members;  +---------------------------+--------------------------------------+----------------------+-------------+--------------+  |?CHANNEL_NAME??????????????|?MEMBER_ID????|?MEMBER_HOST??????????|?MEMBER_PORT?|?MEMBER_STATE?|  +---------------------------+--------------------------------------+----------------------+-------------+--------------+  |?group_replication_applier?|?3381d155-d7d1-11e6-94f7-b8ca3af6e36c?|?hch_test_dbm2_121_71?|????????3317?|?ONLINE??????|  +---------------------------+--------------------------------------+----------------------+-------------+--------------+  1?row?in?set?(0.00?sec)  ?  mysql>

db1后臺error log日志顯示，看到db2加入進來了，進了group：

mysql>???START?GROUP_REPLICATION;  Query?OK,?0?rows?affected?(1.02?sec)  ?  mysql>

再去master庫db1上，查看group_replication成員，會有db2的顯示，看到MEMBER_STATE都是ONLINE，表示都是MASTER：

2017-01-11T07:51:40.623093Z?0?[Note]??Plugin?group_replication?reported:?'Marking?group?replication?view?change??with?view_id?14841207424144277:8'  2017-01-11T07:51:40.650962Z?21?[Note]??Start?binlog_dump?to?master_thread_id(21)?slave_server(12002),?pos(,?4)  2017-01-11T07:51:40.687441Z?0?[Note]??Plugin?group_replication?reported:?'The?member?with?address?bpe_service:3317??was?  declared?online?within?the?replication?group'  2017-01-11T07:52:40.651481Z?21?[Note]??Aborted?connection?21?to?db:?'unconnected'?user:?'repl'?host:??'192.168.121.111'?(failed?on?flush_net())


db3上啟動group_replication：?

Db3命令行上執行：

mysql>??SELECT?*?FROM?performance_schema.replication_group_members;  +---------------------------+--------------------------------------+----------------------+-------------+--------------+  |?CHANNEL_NAME??????????????|?MEMBER_ID???????|?MEMBER_HOST??????????|?MEMBER_PORT?|?MEMBER_STATE?|  +---------------------------+--------------------------------------+----------------------+-------------+--------------+  |?group_replication_applier?|?3381d155-d7d1-11e6-94f7-b8ca3af6e36c?|?hch_test_dbm2_121_71?|????????3317?|?ONLINE???????|  |?group_replication_applier?|?84dba8ff-d7d2-11e6-aa9a-18a99b76310d?|?bpe_service??????????|????????3317?|?ONLINE???????|  +---------------------------+--------------------------------------+----------------------+-------------+--------------+  2?rows?in?set?(0.00?sec)  ?  mysql>

再去master庫db1上，查看group_replication成員，會有db3的顯示，而且已經是ONLINE了

mysql>?set?global??group_replication_allow_local_disjoint_gtids_join=ON;  Query?OK,?0?rows?affected?(0.00?sec)  ?  mysql>?start?group_replication;  Query?OK,?0?rows?affected?(1.99?sec)  ?  mysql>

db1上后臺error log顯示：

mysql>?SELECT?*?FROM?performance_schema.replication_group_members;  +---------------------------+--------------------------------------+----------------------+-------------+--------------+  |?CHANNEL_NAME??????????????|?MEMBER_ID????????????????????????????|?MEMBER_HOST??????????|?MEMBER_PORT?|?MEMBER_STATE?|  +---------------------------+--------------------------------------+----------------------+-------------+--------------+  |?group_replication_applier?|?21750571-d7d3-11e6-91e2-18a99b763071?|?hch_test_web_1_24????|????????3317?|?ONLINE???????|  |?group_replication_applier?|?3381d155-d7d1-11e6-94f7-b8ca3af6e36c?|?hch_test_dbm2_121_71?|????????3317?|?ONLINE???????|  |?group_replication_applier?|?84dba8ff-d7d2-11e6-aa9a-18a99b76310d?|?bpe_service??????????|????????3317?|?ONLINE???????|  +---------------------------+--------------------------------------+----------------------+-------------+--------------+  3?rows?in?set?(0.00?sec)  ?  mysql>

最后查看集群狀態：

2017-01-11T08:13:05.972276Z?0?[Note]??Plugin?group_replication?reported:?'getstart?group_id?8da193f6'  2017-01-11T08:13:08.146144Z?0?[Note]??Plugin?group_replication?reported:?'Marking?group?replication?view?change??with?view_id?14841207424144277:11'  2017-01-11T08:13:08.174808Z?25?[Note]??Start?binlog_dump?to?master_thread_id(25)?slave_server(12003),?pos(,?4)  2017-01-11T08:13:08.218338Z?0?[Note]??Plugin?group_replication?reported:?'The?member?with?address??  hch_test_web_1_24:3317?was?declared?online?within?the?replication?group'  2017-01-11T08:14:08.175308Z?25?[Note]??Aborted?connection?25?to?db:?'unconnected'?user:?'repl'?host:??'192.168.121.24'?(failed?on?flush_net())

7，驗證集群復制功能

測試，在master庫db1上建立測試庫db1，測試表t1，錄入一條數據

@@######@@

?@@##@@

?

然后在db2上執行MySQL，則成功

詳細介紹MySQL Group Replication[Multi-Primary Mode]的搭建部署過程（圖文）

然后再db3上執行insert操作，則成功

詳細介紹MySQL Group Replication[Multi-Primary Mode]的搭建部署過程（圖文）

在db1、db2、db3分部錄入的數據，再分表去db1、db2、db3上查看t1表的數據，數據都已經復制同步過來了，已經實現了group里面多成員member多處寫操作。

詳細介紹MySQL Group Replication[Multi-Primary Mode]的搭建部署過程（圖文）

8 添加新的成員

簡要步驟：

（1）??????安裝mysql實例

（2）??????準備my.cnf

@@######@@

?

?

（3）??????啟動mysql實例，service mysqld start;

（4）??????開啟group replication

@@######@@

（5）驗證組成員，SELECT * FROM performance_schema.replication_group_members;

9，管理操作命令?

查看group狀態信息：SELECT * FROM performance_schema.replication_group_members;

?詳細介紹MySQL Group Replication[Multi-Primary Mode]的搭建部署過程（圖文）

查詢replication協議的其它數據：select * from performance_schema.replication_group_member_statsG

?@@##@@

?

查看后臺進程2個：

@@######@@

@@##@@

組復制成員狀態表：select * from performance_schema.replication_group_member_stats;查看的是當前的狀態：

詳細介紹MySQL Group Replication[Multi-Primary Mode]的搭建部署過程（圖文）

performance_schema.replication_applier_status

詳細介紹MySQL Group Replication[Multi-Primary Mode]的搭建部署過程（圖文）

10，問題記錄

10.1問題記錄一

@@######@@

?

10.2問題記錄二

Sql界面顯示狀態為ERROR：

詳細介紹MySQL Group Replication[Multi-Primary Mode]的搭建部署過程（圖文）

?

Errorlog顯示：

@@######@@

?

【解決辦法】：

@@######@@

?

?

?

?

?

詳細介紹MySQL Group Replication[Multi-Primary Mode]的搭建部署過程（圖文）詳細介紹MySQL Group Replication[Multi-Primary Mode]的搭建部署過程（圖文）詳細介紹MySQL Group Replication[Multi-Primary Mode]的搭建部署過程（圖文）

mysql>?SELECT?*?FROM?performance_schema.replication_group_members;  +---------------------------+--------------------------------------+----------------------+-------------+--------------+  |?CHANNEL_NAME??????????????|?MEMBER_ID????????????????????????????|?MEMBER_HOST??????????|?MEMBER_PORT?|?MEMBER_STATE?|  +---------------------------+--------------------------------------+----------------------+-------------+--------------+  |?group_replication_applier?|?3d872c2e-d670-11e6-ac1f-b8ca3af6e36c?|?hch_test_dbm2_121_71?|????????3317?|?ONLINE???????|  |?group_replication_applier?|?ef8ac2de-d671-11e6-9ba4-18a99b763071?|?hch_test_web_1_24????|????????3317?|?ONLINE???????|  |?group_replication_applier?|?fdf2b02e-d66f-11e6-98a8-18a99b76310d?|?bpe_service??????????|????????3317?|?ONLINE???????|  +---------------------------+--------------------------------------+----------------------+-------------+--------------+  3?rows?in?set?(0.00?sec)  ?  mysql>
mysql>?create?database?db1;  Query?OK,?1?row?affected?(0.00?sec)  ?  mysql>?create?table?db1.t1(id?int,cn??varchar(32));  Query?OK,?0?rows?affected?(0.02?sec)  ?  mysql>  mysql>?insert?into?t1(id,cn)values(??1,'a');  ERROR?3098?(HY000):?The?table?does?not??comply?with?the?requirements?by?an?external?plugin.  mysql>  mysql>  #?這里原因是group_replaction環境下面，表必須有主鍵不然不允許往里insert值。所以修改表t1，將id字段設置程主鍵即可。  mysql>?alter?table?t1?modify?id?int??primary?key;  Query?OK,?0?rows?affected?(0.02?sec)  Records:?0??Duplicates:?0??Warnings:?0
#replication  server_id=12001?#?這個是隨著新服務器的添加會變化  gtid_mode=ON  enforce_gtid_consistency=ON  master_info_repository=TABLE  relay_log_info_repository=TABLE  binlog_checksum=NONE  log_slave_updates=ON  log_bin=binlog  binlog_format=ROW  ?  #group?replication  transaction_write_set_extraction=XXHASH64  loose-group_replication_group_name="e4668cea-d7ca-11e6-86b5-18a99b76310d"  loose-group_replication_start_on_boot=off  loose-group_replication_local_address=??"db1:24901"?#?這個是隨著新服務器的添加會變化  loose-group_replication_group_seeds=??"db1:24901,db2:24902,db3:24903"#?這個是隨著新服務器的添加會變化  loose-group_replication_bootstrap_group=??off  loose-group_replication_single_primary_mode=FALSE  loose-group_replication_enforce_update_everywhere_checks=??TRUE
SET?SQL_LOG_BIN=0;??#?在建立復制賬號的時候，不需要進入binlog日志  GRANT?REPLICATION?SLAVE?ON?*.*?TO?'repl'@'192.168.%'?IDENTIFIED?BY??'rlpbright_1927@ys';  SET?SQL_LOG_BIN=1;  CHANGE?MASTER?TO?MASTER_USER='repl',??MASTER_PASSWORD='rlpbright_1927@ys'?FOR?CHANNEL?'group_replication_recovery';  START?group_replication;  SELECT?*?FROM?performance_schema.replication_group_members;
[root@hch_test_dbm2_121_71?soft]#?netstat??-ntpl|grep?mysql  tcp?????????0??????0?0.0.0.0:24901????0.0.0.0:*??LISTEN??????3665/mysqld????????  tcp?????????0??????0?:::3317???????:::*??LISTEN??????3665/mysqld????????  [root@hch_test_dbm2_121_71?soft]#
Db2的mysql窗口報錯：  mysql>?START?GROUP_REPLICATION;  ERROR?3092?(HY000):?The?server?is?not??configured?properly?to?be?an?active?member?of?the?group.?Please?see?more??details?on?error?log.  mysql>  ?  db2的error?log信息如下：  2017-01-11T07:57:41.161360Z?0?[ERROR]??Plugin?group_replication?reported:?'This?member?has?more?executed??  transactions?than?those?present?in?the?group.?Local?transactions:??84dba8ff-d7d2-11e6-aa9a-18a99b76310d:1?>?  Group?transactions:??3381d155-d7d1-11e6-94f7-b8ca3af6e36c:1,  e4668cea-d7ca-11e6-86b5-18a99b76310d:1-3'  2017-01-11T07:57:41.161430Z?0?[ERROR]??Plugin?group_replication?reported:?'The?member?contains?transactions?not??  present?in?the?group.?The?member?will?now?exit?the?group.'  2017-01-11T07:57:41.161445Z?0?[Note]??Plugin?group_replication?reported:?'To?force?this?member?into?the?group?you??  can?use?the?group_replication_allow_local_disjoint_gtids_join?option'  2017-01-11T07:57:41.161589Z?3?[Note]??Plugin?group_replication?reported:?'Going?to?wait?for?view?modification'  2017-01-11T07:57:41.162126Z?0?[Note]??Plugin?group_replication?reported:?'getstart?group_id?8da193f6'  2017-01-11T07:57:44.508236Z?0?[Note]??Plugin?group_replication?reported:?'state?4330?action?xa_terminate'  2017-01-11T07:57:44.508429Z?0?[Note]??Plugin?group_replication?reported:?'new?state?x_start'  2017-01-11T07:57:44.508561Z?0?[Note]??Plugin?group_replication?reported:?'state?4257?action?xa_exit'  2017-01-11T07:57:44.508720Z?0?[Note]??Plugin?group_replication?reported:?'Exiting?xcom?thread'  2017-01-11T07:57:44.508746Z?0?[Note]??Plugin?group_replication?reported:?'new?state?x_start'  2017-01-11T07:57:49.549137Z?3?[Note]??Plugin?group_replication?reported:?'auto_increment_increment?is?reset?to?1'  2017-01-11T07:57:49.549192Z?3?[Note]??Plugin?group_replication?reported:?'auto_increment_offset?is?reset?to?1'  2017-01-11T07:57:49.549472Z?23?[Note]??Error?reading?relay?log?event?for?channel?'group_replication_applier':?  slave??SQL?thread?was?killed  2017-01-11T07:57:49.550037Z?20?[Note]?Plugin??group_replication?reported:?'The?group?replication?applier?thread?was?killed'  ?  【解決辦法】設置group_replication_allow_local_disjoint_gtids_join為ON  mysql>?set?global??group_replication_allow_local_disjoint_gtids_join=ON;  Query?OK,?0?rows?affected?(0.00?sec)  ?  mysql>?START?GROUP_REPLICATION;  Query?OK,?0?rows?affected?(5.52?sec)  ?  mysql>
2017-01-11T08:07:34.587378Z?12?[Note]??'CHANGE?MASTER?TO?FOR?CHANNEL?'group_replication_recovery'?executed'.??  Previous?state?master_host='bpe_service',?master_port=?3317,??master_log_file='',?master_log_pos=?4,?master_bind=''.?New?state??  master_host='<null>',?master_port=?0,?master_log_file='',??master_log_pos=?4,?master_bind=''.  2017-01-11T08:07:34.591679Z?12?[ERROR]??Plugin?group_replication?reported:?'Fatal?error?during?the?Recovery?process??of?Group?Replication.?  The?server?will?leave?the?group.'  2017-01-11T08:07:34.592447Z?0?[Note]??Plugin?group_replication?reported:?'getstart?group_id?8da193f6'  2017-01-11T08:07:37.943536Z?0?[Note]??Plugin?group_replication?reported:?'state?4330?action?xa_terminate'  2017-01-11T08:07:37.943852Z?0?[Note]??Plugin?group_replication?reported:?'new?state?x_start'  2017-01-11T08:07:37.943879Z?0?[Note]??Plugin?group_replication?reported:?'state?4257?action?xa_exit'  2017-01-11T08:07:37.943981Z?0?[Note]??Plugin?group_replication?reported:?'Exiting?xcom?thread'  2017-01-11T08:07:37.943999Z?0?[Note]??Plugin?group_replication?reported:?'new?state?x_start'</null>
mysql&gt;?CHANGE?MASTER?TO?MASTER_USER='repl',?MASTER_PASSWORD='rlpbright_1927@ys'?FOR?CHANNEL?'group_replication_recovery';  Query?OK,?0?rows?affected,?2?warnings?(0.01?sec)  ?  mysql&gt;?stop?group_replication;  Query?OK,?0?rows?affected?(0.01?sec)  ?  mysql&gt;?start?group_replication;  Query?OK,?0?rows?affected?(2.20?sec)  ?  mysql&gt;  mysql&gt;?SELECT?*?FROM?performance_schema.replication_group_members;  +---------------------------+--------------------------------------+----------------------+-------------+--------------+  |?CHANNEL_NAME??????????????|?MEMBER_ID??????|?MEMBER_HOST??????????|?MEMBER_PORT?|?MEMBER_STATE?|  +---------------------------+--------------------------------------+----------------------+-------------+--------------+  |?group_replication_applier?|?21750571-d7d3-11e6-91e2-18a99b763071?|?hch_test_web_1_24????|????3317?|?ONLINE???????|  |?group_replication_applier?|?3381d155-d7d1-11e6-94f7-b8ca3af6e36c?|?hch_test_dbm2_121_71?|????????3317?|?ONLINE???????|  |?group_replication_applier?|?84dba8ff-d7d2-11e6-aa9a-18a99b76310d?|?bpe_service?????|?????3317?|?ONLINE???????|  +---------------------------+--------------------------------------+----------------------+-------------+--------------+  3?rows?in?set?(0.00?sec)  ?  mysql&gt;

? 版權聲明
THE END
數據庫
# 安裝mysql
喜歡就支持一下吧
點贊10 分享