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方法比較類似。
?
?
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是關閉的
開始構建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,則成功
然后再db3上執行insert操作,則成功
在db1、db2、db3分部錄入的數據,再分表去db1、db2、db3上查看t1表的數據,數據都已經復制同步過來了,已經實現了group里面多成員member多處寫操作。
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;
?
查詢replication協議的其它數據:select * from performance_schema.replication_group_member_statsG
?@@##@@ |
?
查看后臺進程2個:
@@######@@
@@##@@ |
組復制成員狀態表:select * from performance_schema.replication_group_member_stats;查看的是當前的狀態:
performance_schema.replication_applier_status
10,問題記錄
10.1問題記錄一
@@######@@ |
?
10.2問題記錄二
Sql界面顯示狀態為ERROR:
?
Errorlog顯示:
@@######@@ |
?
【解決辦法】:
@@######@@ |
?
?
?
?
?
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>?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>?stop?group_replication; Query?OK,?0?rows?affected?(0.01?sec) ? mysql>?start?group_replication; Query?OK,?0?rows?affected?(2.20?sec) ? mysql> 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>