1，關于MySQL Group Replication
基于組的復制（group-basedreplication）是一種被使用在容錯系統中的技術。replication-group（復制組）是由能夠相互通信的多個服務器（節點）組成的。
在通信層，Groupreplication實現了一系列的機制：比如原子消息（atomicmessage delivery）和全序化消息（totalorderingof messages）。
這些原子化，抽象化的機制，為實現更先進的數據庫復制方案提供了強有力的支持。
MySQL Group Replication正是基于這些技術和概念，實現了一種多主全更新的復制協議。
簡而言之，一個Replication-group就是一組節點，每個節點都可以獨立執行事務，而讀寫事務則會在于group內的其他節點進行協調之后再commit。
因此，當一個事務準備提交時，會自動在group內進行原子性的廣播，告知其他節點變更了什么內容/執行了什么事務。
這種原子廣播的方式，使得這個事務在每一個節點上都保持著同樣順序。
這意味著每一個節點都以同樣的順序，接收到了同樣的事務日志，所以每一個節點以同樣的順序重演了這些事務日志，最終整個group保持了完全一致的狀態。
?
然而，不同的節點上執行的事務之間有可能存在資源爭用。這種現象容易出現在兩個不同的并發事務上。
假設在不同的節點上有兩個并發事務，更新了同一行數據，那么就會發生資源爭用。
面對這種情況，GroupReplication判定先提交的事務為有效事務，會在整個group里面重演，后提交的事務會直接中斷，或者回滾，最后丟棄掉。
?
因此，這也是一個無共享的復制方案，每一個節點都保存了完整的數據副本。看如下圖片01.png，描述了具體的工作流程，能夠簡潔的和其他方案進行對比。這個復制方案，在某種程度上，和數據庫狀態機（DBSM）的Replication方法比較類似。
?![詳細介紹MySQL Group Replication[Single-Primary Mode]的搭建部署過程](https://img.php.cn/upload/article/000/000/194/9d8bd992d31c5c5374a87a66782d6105-0.png "詳細介紹MySQL Group Replication[Single-Primary Mode]的搭建部署過程")
?
2，安裝mysql5.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上都設置
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
在db1/db2/db3上建立復制賬號：
| @@######@@ |
4，安裝group replication插件
在db1、db2、db3上依次安裝group replication插件
GRANT?REPLICATION?SLAVE?ON?*.*?TO??'repl'@'192.168.%'?IDENTIFIED?BY?'rlpbright_1927@ys';
plugin-load=group_replication或者直接在狀態my.cnf中配置：
查看group replication組件
mysql>?INSTALL?PLUGIN?group_replication?SONAME?'group_replication.so';???????????????????????? Query?OK,?0?rows?affected?(0.01?sec) ? mysql>
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 配置文件
5.1在線添加
配置：
|
(1)???? ?配置命令如下，具體到某一個db節點會有所調整，大部分參數是一致的： @@######@@ ? (2)???? ?在db1執行過程如下： @@######@@ ? ? (3)???? ?在db2執行過程如下： @@######@@ ? (4)???? ?在db3執行過程如下： @@######@@ |
?
5.2配置文件配置
|
（1） db1上的my.cnf配置： @@######@@ ? （2）db2上的my.cnf配置： @@######@@ ? （3）db3上的my.cnf配置： @@######@@ |
?
配置完后，重啟3個db上的mysql服務，本次案例，我們選擇5.2 配置文件配置方式實現。
6，啟動mgr集群
開始構建group replication集群，通常操作命令
mysql>?show?variables?like?'binlog_format';???????????????????????????????????????????? +---------------+-------+ |?Variable_name?|?Value?| +---------------+-------+ |?binlog_format?|?ROW???| +---------------+-------+ 1?row?in?set?(0.00?sec) ? mysql>
Db1上建立基本主庫master庫：
set??@@global.transaction_write_set_extraction?=?XXHASH64; ?set?@@global.group_replication_start_on_boot??=?OFF; ?set??@@global.group_replication_bootstrap_group?=?OFF; ?set?@@global.group_replication_group_name?=??"0c6d3e5f-90e2-11e6-802e-842b2b5909d6";?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ?set?@@global.group_replication_local_address??=?'db1:6606'; ?set?@@global.group_replication_group_seeds?=??'db2:6607,db3:6608';
Db2上啟動group_replication：?
mysql>?set??@@global.transaction_write_set_extraction?=?XXHASH64; Query?OK,?0?rows?affected?(0.00?sec) ? mysql>?set??@@global.group_replication_start_on_boot?=?OFF; Query?OK,?0?rows?affected?(0.00?sec) ? mysql>?set??@@global.group_replication_bootstrap_group?=?OFF; Query?OK,?0?rows?affected?(0.00?sec) ? mysql> mysql>set?@@global.group_replication_group_name??=?"0c6d3e5f-90e2-11e6-802e-842b2b5909d7";????????????????????????????? Query?OK,?0?rows?affected?(0.00?sec) ? mysql>?set??@@global.group_replication_local_address?=?'db1:6606'; Query?OK,?0?rows?affected?(0.00?sec) ? mysql>?set??@@global.group_replication_group_seeds?=?'db2:6607,db3:6608'; Query?OK,?0?rows?affected?(0.00?sec) ? mysql>
Db3上啟動group_replication：
mysql>???set?@@global.transaction_write_set_extraction?=?XXHASH64; Query?OK,?0?rows?affected?(0.00?sec) ? mysql>???set?@@global.group_replication_start_on_boot?=?OFF; Query?OK,?0?rows?affected?(0.00?sec) ? mysql>???set?@@global.group_replication_bootstrap_group?=?OFF; Query?OK,?0?rows?affected?(0.01?sec) ? mysql>???set?@@global.group_replication_group_name?=??"0c6d3e5f-90e2-11e6-802e-842b2b5909d6"; Query?OK,?0?rows?affected?(0.00?sec) ? mysql>???set??@@global.group_replication_local_address?=?'db2:6607'; Query?OK,?0?rows?affected?(0.00?sec) ? mysql>??set?@@global.group_replication_group_seeds?=??'db1:6606,db3:6608'; Query?OK,?0?rows?affected?(0.01?sec) ? mysql>
最后查看集群狀態，都為ONLINE就表示OK：
mysql>???set?@@global.transaction_write_set_extraction?=?XXHASH64?; Query?OK,?0?rows?affected?(0.00?sec) ? mysql>???set?@@global.group_replication_start_on_boot?=?OFF; Query?OK,?0?rows?affected?(0.00?sec) ? mysql>???set?@@global.group_replication_bootstrap_group?=?OFF?; Query?OK,?0?rows?affected?(0.00?sec) ? mysql>???set?@@global.group_replication_group_name?=??"0c6d3e5f-90e2-11e6-802e-842b2b5909d6"; Query?OK,?0?rows?affected?(0.00?sec) ? mysql>???set??@@global.group_replication_local_address?=?'db3:6608'??; Query?OK,?0?rows?affected?(0.00?sec) ? mysql>???set?@@global.group_replication_group_seeds??=?'db1:6606,db2:6607'?; Query?OK,?0?rows?affected?(0.00?sec) ? mysql>
7，驗證集群復制功能
測試，在master庫db1上建立測試庫db1，測試表t1，錄入一條數據
server-id=12001 transaction_write_set_extraction?=??XXHASH64 loose-group_replication_group_name?=??"5f847ff2-d701-11e6-819c-b8ca3af6e36c"?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? loose-group_replication_start_on_boot?=??off loose-group_replication_local_address??=?"db1:23306" loose-group_replication_group_seeds?=??"db1:23306,db2:23307,db3:23308" loose-group_replication_bootstrap_group?=??off loose-group_replication_single_primary_mode??=?true loose-group_replication_enforce_update_everywhere_checks??=?false
去db2/db3上可以看到數據已經同步過去
server-id=12002 transaction_write_set_extraction?=?XXHASH64 loose-group_replication_group_name?=??"5f847ff2-d701-11e6-819c-b8ca3af6e36c" loose-group_replication_start_on_boot?=??off loose-group_replication_local_address??=?"db2:23307" loose-group_replication_group_seeds?=??"db1:23306,db2:23307,db3:23308" loose-group_replication_bootstrap_group?=??off loose-group_replication_single_primary_mode??=?true loose-group_replication_enforce_update_everywhere_checks??=?false
然后在db2/db3上執行inert操作，則拒絕，因為db2、db3為readonly
server-id=12003 transaction_write_set_extraction?=??XXHASH64 loose-group_replication_group_name?=??"5f847ff2-d701-11e6-819c-b8ca3af6e36c" loose-group_replication_start_on_boot?=??off loose-group_replication_local_address??=?"db3:23308" loose-group_replication_group_seeds?=??"db1:23306,db2:23307,db3:23308" loose-group_replication_bootstrap_group?=??off loose-group_replication_single_primary_mode??=?true loose-group_replication_enforce_update_everywhere_checks??=?false
8，問題記錄
8.1問題記錄一
MySQL窗口報錯：
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>
?
【解決辦法】：
根據提示打開group_replication_allow_local_disjoint_gtids_join選項，mysql命令行執行：
#?設置group_replication_bootstrap_group為ON是為了標示以后加入集群的服務器以這臺服務器為基準，以后加入的就不需要設置。 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>?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??| +---------------------------+--------------------------------------+----------------------+-------------+--------------+ 1?row?in?set?(0.00?sec) ? mysql>
再執行開啟組復制：
Db2上mysql命令行上執行啟動： mysql>??START?GROUP_REPLICATION; Query?OK,?0?rows?affected?(1.02?sec) ? mysql> ? db1上后臺error?log顯示： 2017-01-10T07:37:39.946919Z?0?[Note]?Plugin?group_replication?reported:?'getstart?group_id?41e28b21' 2017-01-10T07:58:47.624090Z?0?[Note]?Plugin?group_replication?reported:?'getstart?group_id?41e28b21' 2017-01-10T07:58:53.116957Z?0?[Note]?Plugin?group_replication?reported:?'Marking?group?replication?view?change?with?view_id?14840330835325176:6' ? 再去master庫db1上，查看group_replication成員，會有db2的顯示 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?|?fdf2b02e-d66f-11e6-98a8-18a99b76310d?|?bpe_service??????????|????????3317?|?ONLINE???????| +---------------------------+--------------------------------------+----------------------+-------------+--------------+ 2?rows?in?set?(0.00?sec) ? mysql>
8.2 問題記錄二RECOVERING??
在db1上狀態集群組成員
--?Db3命令行上執行： 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> ? --?再去master庫db1上，查看group_replication成員，會有db3的顯示，而且已經是ONLINE了 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.01?sec) ? mysql> ? --?db1上后臺error?log顯示： 2017-01-10T08:00:28.866356Z?0?[Note]?Plugin?group_replication?reported:?'getstart?group_id?41e28b21' 2017-01-10T08:00:54.699130Z?0?[Note]?Plugin?group_replication?reported:?'getstart?group_id?41e28b21' 2017-01-10T08:00:56.567427Z?0?[Note]?Plugin?group_replication?reported:?'Marking?group?replication?view?change?with?view_id?14840330835325176:9'
再查看后臺error日志，
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>
【解決辦法】：
看報錯[ERROR] Slave I/O for channel’group_replication_recovery’: error connecting to master ‘repl@hch_test_dbm2_121_71:3317’- retry-time: 60? retries: 1, Error_code:2005，連接master庫不上，所以問題在這里，我們賦予的復制賬號是ip的repl@’192.168.%’，所以還需要做一個hostname(hch_test_dbm2_121_71)和db1的ip地址192.168.121.71的映射關系。
?
建立hostname和ip映射
mysql>?create?database?db1; Query?OK,?1?row?affected?(0.00?sec) ? mysql>?create?table?db1.t1(id?int,cnvarchar(32)); Query?OK,?0?rows?affected?(0.02?sec) ? mysql> mysql>?insert?into?t1?select?1,'a'; ERROR?3098?(HY000):?The?table?does?notcomply?with?the?requirements?by?an?external?plugin. mysql> mysql>?insert?into?t1(id,cn)values(1,'a'); ERROR?3098?(HY000):?The?table?does?notcomply?with?the?requirements?by?an?external?plugin. mysql> mysql> --?#?這里原因是group_replaction環境下面，表必須有主鍵不然不允許往里insert值。所以修改表t1，將id字段設置程主鍵即可。 mysql>?alter?table?t1?modify?id?intprimary?key; Query?OK,?0?rows?affected?(0.02?sec) Records:?0?Duplicates:?0??Warnings:?0 ? mysql>?insert?into?t1?select?1,'a'; Query?OK,?1?row?affected?(0.01?sec) Records:?1?Duplicates:?0??Warnings:?0 ? mysql>
然后在db2上執行如下命令后重新開啟group_replication即可。
mysql>?select?*?from?db1.t1; +----+------+ |?id?|?cn??| +----+------+ |??1|?a????| +----+------+ 1?row?in?set?(0.00?sec) ? mysql>
再去master庫db1上，查看group_replication成員，會有db2的顯示
mysql>?insert?into?t1?select?2,'b'; ERROR?1290?(HY000):?The?MySQL?server?isrunning?with?the?--super-read-only?option?so?it?cannot?execute?this?statement mysql>
8.3問題記錄三
操作問題
ERROR?3092?(HY000):?The?server?is?notconfigured?properly?to?be?an?active?member?of?the?group.?Please?see?moredetails?on?error?log. 后臺ERROR?LOG報錯： [ERROR]?Plugin?group_replication?reported:'This?member?has?more?executed?transactions?than?those?present?in?the?group. Local?transactions:?f16f7f74-c283-11e6-ae37-fa163ee40410:1?>?Grouptransactions:?3c992270-c282-11e6-93bf-fa163ee40410:1, ?aaaaaa:1-5' ?[ERROR]Plugin?group_replication?reported:?'The?member?contains?transactions?notpresent?in?the?group.?The?member?will?now?exit?the?group.' ?[Note]?Plugin?group_replication?reported:?'Toforce?this?member?into?the?group?you?can?use?the?group_replication_allow_local_disjoint_gtids_joinoption'
?
【解決辦法】：
mysql>?set?globalgroup_replication_allow_local_disjoint_gtids_join=ON;
mysql>?start?group_replication; Query?OK,?0?rows?affected?(7.89?sec) ? mysql>
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?|?RECOVERING???| |?group_replication_applier?|?fdf2b02e-d66f-11e6-98a8-18a99b76310d?|?bpe_service????????|????3317?|?RECOVERING???| +---------------------------+--------------------------------------+----------------------+-------------+--------------+ 3?rows?in?set?(0.00?sec) ? mysql>
2017-01-10T09:17:39.449488Z?146?[ERROR]?Slave?I/O?for?channel?'group_replication_recovery':? error?connecting?to?master?'repl@hch_test_dbm2_121_71:3317'?-?retry-time:?60??retries:?1,?Error_code:?2003 2017-01-10T09:17:39.450289Z?146?[Note]?Slave?I/O?thread?for?channel?'group_replication_recovery'?killed?while?connecting?to?master 2017-01-10T09:17:39.450449Z?146?[Note]?Slave?I/O?thread?exiting?for?channel?'group_replication_recovery',?read?up?to?log?'FIRST',?position?4 2017-01-10T09:17:39.451579Z?144?[ERROR]?Plugin?group_replication?reported:?'There?was?an?error?when?connecting?to?the?donor?server.? Check?group?replication?recovery's?connection?credentials.' 2017-01-10T09:17:39.452341Z?144?[Note]?Plugin?group_replication?reported:?'Retrying?group?recovery?connection?with?another?donor.?Attempt?2/10' 2017-01-10T09:17:39.457834Z?0?[Note]?Plugin?group_replication?reported:?'Marking?group?replication?view?change?with?view_id?14840330835325176:25' 2017-01-10T09:18:39.456629Z?144?[Note]?'CHANGE?MASTER?TO?FOR?CHANNEL?'group_replication_recovery'?executed'.? Previous?state?master_host='hch_test_dbm2_121_71',?master_port=?3317,?master_log_file='',?master_log_pos=?4,?master_bind=''.? New?state?master_host='hch_test_dbm2_121_71',?master_port=?3317,?master_log_file='',?master_log_pos=?4,?master_bind=''. 2017-01-10T09:18:39.485250Z?144?[Note]?Plugin?group_replication?reported:?'Establishing? connection?to?a?group?replication?recovery?donor?3d872c2e-d670-11e6-ac1f-b8ca3af6e36c?at?hch_test_dbm2_121_71?port:?3317.' 2017-01-10T09:18:39.489356Z?150?[Warning]?Storing?MySQL?user?name?or? password?information?in?the?master?info?repository?is?not?secure?and?is?therefore?not?recommended.?Please?consider?using?the?USER?and? PASSWORD?connection?options?for?START?SLAVE;?see?the?'START?SLAVE?Syntax'?in?the?MySQL?Manual?for?more?information. 2017-01-10T09:18:39.493511Z?150?[ERROR]?Slave?I/O?for?channel?'group_replication_recovery':? error?connecting?to?master?'repl@hch_test_dbm2_121_71:3317'?-?retry-time:?60??retries:?1,?Error_code:?2005 2017-01-10T09:18:39.493912Z?150?[Note]?Slave?I/O?thread?for?channel?'group_replication_recovery'?killed?while?connecting?to?master 2017-01-10T09:18:39.494069Z?150?[Note]?Slave?I/O?thread?exiting?for?channel?'group_replication_recovery',?read?up?to?log?'FIRST',?position?4 2017-01-10T09:18:39.495155Z?144?[ERROR]?Plugin?group_replication?reported:? 'There?was?an?error?when?connecting?to?the?donor?server.?Check?group?replication?recovery's?connection?credentials.' 2017-01-10T09:18:39.496838Z?144?[Note]?Plugin?group_replication?reported:?'Retrying?group?recovery?connection?with?another?donor.?Attempt?3/10'
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
mysql>?stop?group_replication; Query?OK,?0?rows?affected?(0.02?sec)????????????????????????????????????????????????? ? mysql>?start?group_replication; Query?OK,?0?rows?affected?(5.68?sec) ? mysql>
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?|?fdf2b02e-d66f-11e6-98a8-18a99b76310d?|?bpe_service??????????|????????3317?|?ONLINE???????| +---------------------------+--------------------------------------+----------------------+-------------+--------------+ 2?rows?in?set?(0.00?sec) ? mysql>
mysql> mysql>?START?GROUP_REPLICATION; ERROR?3092?(HY000):?The?server?is?notconfigured?properly?to?be?an?active?member?of?the?group.?Please?see?moredetails?on?error?log.
mysql>?SET?GLOBALgroup_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>
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
