簡(jiǎn)介

創(chuàng)新互聯(lián)建站是一家專業(yè)提供善左企業(yè)網(wǎng)站建設(shè),專注與成都網(wǎng)站建設(shè)、成都網(wǎng)站制作、H5建站、小程序制作等業(yè)務(wù)。10年已為善左眾多企業(yè)、政府機(jī)構(gòu)等服務(wù)。創(chuàng)新互聯(lián)專業(yè)網(wǎng)站建設(shè)公司優(yōu)惠進(jìn)行中。

MySQL Group Replication（簡(jiǎn)稱MGR）字面意思是mysql組復(fù)制的意思,但其實(shí)他是一個(gè)高可用的集群架構(gòu),暫時(shí)只支持mysql5.7和mysql8.0版本.

是MySQL官方于2016年12月推出的一個(gè)全新的高可用與高擴(kuò)展的解決方案,提供了高可用、高擴(kuò)展、高可靠的MySQL集群服務(wù).

也是mysql官方基于組復(fù)制概念并充分參考MariaDB Galera Cluster和Percona XtraDB Cluster結(jié)合而來(lái)的新的高可用集群架構(gòu).

MySQL Group Replication是建立在基于Paxos的XCom之上的，正因?yàn)橛辛薠Com基礎(chǔ)設(shè)施，保證數(shù)據(jù)庫(kù)狀態(tài)機(jī)在節(jié)點(diǎn)間的事務(wù)一致性，才能在理論和實(shí)踐中保證數(shù)據(jù)庫(kù)系統(tǒng)在不同節(jié)點(diǎn)間的事務(wù)一致性。

由一般主從復(fù)制概念擴(kuò)展,多個(gè)節(jié)點(diǎn)共同組成一個(gè)數(shù)據(jù)庫(kù)集群，事務(wù)的提交必須經(jīng)過(guò)半數(shù)以上節(jié)點(diǎn)同意方可提交,在集群中每個(gè)節(jié)點(diǎn)上都維護(hù)一個(gè)數(shù)據(jù)庫(kù)狀態(tài)機(jī)，保證節(jié)點(diǎn)間事務(wù)的一致性。

優(yōu)點(diǎn):

????高一致性，基于原生復(fù)制及paxos協(xié)議的組復(fù)制技術(shù).

????高容錯(cuò)性，有自動(dòng)檢測(cè)機(jī)制，當(dāng)出現(xiàn)宕機(jī)后,會(huì)自動(dòng)剔除問(wèn)題節(jié)點(diǎn),其他節(jié)點(diǎn)可以正常使用(類似zk集群),當(dāng)不同節(jié)點(diǎn)產(chǎn)生資源爭(zhēng)用沖突時(shí),會(huì)按照先到先得處理,并且內(nèi)置了自動(dòng)化腦裂防護(hù)機(jī)制.

????高擴(kuò)展性，可隨時(shí)在線新增和移除節(jié)點(diǎn),會(huì)自動(dòng)同步所有節(jié)點(diǎn)上狀態(tài)，直到新節(jié)點(diǎn)和其他節(jié)點(diǎn)保持一致，自動(dòng)維護(hù)新的組信息.

????高靈活性，直接插件形式安裝(5.7.17后自帶.so插件),有單主模式和多主模式，單主模式下，只有主庫(kù)可以讀寫(xiě),其他從庫(kù)會(huì)加上super_read_only狀態(tài),只能讀取不可寫(xiě)入,出現(xiàn)故障會(huì)自動(dòng)選主.

缺點(diǎn):

????還是太新,不太穩(wěn)定,暫時(shí)性能還略差于PXC,對(duì)網(wǎng)絡(luò)穩(wěn)定性要求很高,至少是同機(jī)房做.

安裝

1.服務(wù)環(huán)境設(shè)定規(guī)劃

多主模式下最好有三臺(tái)以上的節(jié)點(diǎn),單主模式則視實(shí)際情況而定,不過(guò)同個(gè)Group最多節(jié)點(diǎn)數(shù)為9.

需要特別注意,mysql數(shù)據(jù)庫(kù)的服務(wù)端口號(hào)和MGR的服務(wù)端口不是一回事,需要區(qū)分開(kāi)來(lái).

而server-id要區(qū)分開(kāi)來(lái)是必須的,單純做主從復(fù)制也要滿足這一點(diǎn)了.

2.安裝部署

怎么安裝mysql8.0就不多說(shuō)了,本系列第一篇已經(jīng)說(shuō)過(guò)了,所以默認(rèn)就當(dāng)裝好了.

直接就說(shuō)怎么安裝MGR了,上面也說(shuō)了,MGR在mysql5.7.17版本之后就都是自帶插件了,只是沒(méi)有安裝上而已,和半同步插件一個(gè)套路,所以默認(rèn)是沒(méi)有選項(xiàng).

所有集群內(nèi)的服務(wù)器都必須安裝MGR插件才能正常使用該功能.

我們可以看到,一開(kāi)始是沒(méi)有裝的

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?????|
|?caching_sha2_password??????|?ACTIVE???|?AUTHENTICATION?????|?NULL?????????????????|?GPL?????|
|?sha2_cache_cleaner?????????|?ACTIVE???|?AUDIT??????????????|?NULL?????????????????|?GPL?????|
|?PERFORMANCE_SCHEMA?????????|?ACTIVE???|?STORAGE?ENGINE?????|?NULL?????????????????|?GPL?????|
|?MRG_MYISAM?????????????????|?ACTIVE???|?STORAGE?ENGINE?????|?NULL?????????????????|?GPL?????|
|?MEMORY?????????????????????|?ACTIVE???|?STORAGE?ENGINE?????|?NULL?????????????????|?GPL?????|
|?TempTable??????????????????|?ACTIVE???|?STORAGE?ENGINE?????|?NULL?????????????????|?GPL?????|
|?InnoDB?????????????????????|?ACTIVE???|?STORAGE?ENGINE?????|?NULL?????????????????|?GPL?????|
|?INNODB_TRX?????????????????|?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_TABLES??????????????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|
|?INNODB_TABLESTATS??????????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|
|?INNODB_INDEXES?????????????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|
|?INNODB_TABLESPACES?????????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|
|?INNODB_COLUMNS?????????????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|
|?INNODB_VIRTUAL?????????????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|
|?INNODB_CACHED_INDEXES??????|?ACTIVE???|?INFORMATION?SCHEMA?|?NULL?????????????????|?GPL?????|
|?CSV????????????????????????|?ACTIVE???|?STORAGE?ENGINE?????|?NULL?????????????????|?GPL?????|
|?MyISAM?????????????????????|?ACTIVE???|?STORAGE?ENGINE?????|?NULL?????????????????|?GPL?????|
|?ARCHIVE????????????????????|?ACTIVE???|?STORAGE?ENGINE?????|?NULL?????????????????|?GPL?????|
|?BLACKHOLE??????????????????|?ACTIVE???|?STORAGE?ENGINE?????|?NULL?????????????????|?GPL?????|
|?FEDERATED??????????????????|?DISABLED?|?STORAGE?ENGINE?????|?NULL?????????????????|?GPL?????|
|?ngram??????????????????????|?ACTIVE???|?FTPARSER???????????|?NULL?????????????????|?GPL?????|
|?mysqlx?????????????????????|?ACTIVE???|?DAEMON?????????????|?NULL?????????????????|?GPL?????|
|?mysqlx_cache_cleaner???????|?ACTIVE???|?AUDIT??????????????|?NULL?????????????????|?GPL?????|
+----------------------------+----------+--------------------+----------------------+---------+

MGR相關(guān)參數(shù)也是沒(méi)有加載的,只有一個(gè)其他相關(guān)的參數(shù)

mysql>?show?variables?like?'group%';
+----------------------+-------+
|?Variable_name????????|?Value?|
+----------------------+-------+
|?group_concat_max_len?|?1024??|
+----------------------+-------+
1?row?in?set

然后,先看看當(dāng)前插件的目錄

mysql>?show?variables?like?'plugin_dir';
+---------------+--------------------------------+
|?Variable_name?|?Value??????????????????????????|
+---------------+--------------------------------+
|?plugin_dir????|?/usr/local/mysql80/lib/plugin/?|
+---------------+--------------------------------+
1?row?in?set?(0.00?sec)

再搜索一下我們需要的MGR插件,是否存在

ll?/usr/local/mysql80/lib/plugin/?|grep?group_replication
-rwxr-xr-x?1?7161?31415?21947376?Apr??8?16:16?group_replication.so*

最后,從新進(jìn)入mysql服務(wù),進(jìn)行安裝

mysql>install?PLUGIN?group_replication?SONAME?'group_replication.so';

這個(gè)時(shí)候,就有了

mysql>?show?plugins;
+----------------------------+----------+--------------------+----------------------+---------+
|?Name???????????????????????|?Status???|?Type???????????????|?Library??????????????|?License?|
+----------------------------+----------+--------------------+----------------------+---------+
????.
????.
????.
|?group_replication??????????|?ACTIVE???|?GROUP?REPLICATION??|?group_replication.so?|?GPL?????|
+----------------------------+----------+--------------------+----------------------+---------+

再去看MGR相關(guān)的參數(shù),就有很多了

mysql>?show?variables?like?'group%';
+-----------------------------------------------------+---------------------------------------------------------------------+
|?Variable_name???????????????????????????????????????|?Value???????????????????????????????????????????????????????????????|
+-----------------------------------------------------+---------------------------------------------------------------------+
|?group_concat_max_len????????????????????????????????|?1024????????????????????????????????????????????????????????????????|
|?group_replication_allow_local_lower_version_join????|?OFF?????????????????????????????????????????????????????????????????|
|?group_replication_auto_increment_increment??????????|?7???????????????????????????????????????????????????????????????????|
|?group_replication_bootstrap_group???????????????????|?OFF?????????????????????????????????????????????????????????????????|
|?group_replication_communication_debug_options???????|?GCS_DEBUG_NONE??????????????????????????????????????????????????????|
|?group_replication_components_stop_timeout???????????|?31536000????????????????????????????????????????????????????????????|
|?group_replication_compression_threshold?????????????|?1000000?????????????????????????????????????????????????????????????|
|?group_replication_enforce_update_everywhere_checks??|?ON??????????????????????????????????????????????????????????????????|
|?group_replication_flow_control_applier_threshold????|?25000???????????????????????????????????????????????????????????????|
|?group_replication_flow_control_certifier_threshold??|?25000???????????????????????????????????????????????????????????????|
|?group_replication_flow_control_hold_percent?????????|?10??????????????????????????????????????????????????????????????????|
|?group_replication_flow_control_max_quota????????????|?0???????????????????????????????????????????????????????????????????|
|?group_replication_flow_control_member_quota_percent?|?0???????????????????????????????????????????????????????????????????|
|?group_replication_flow_control_min_quota????????????|?0???????????????????????????????????????????????????????????????????|
|?group_replication_flow_control_min_recovery_quota???|?0???????????????????????????????????????????????????????????????????|
|?group_replication_flow_control_mode?????????????????|?QUOTA???????????????????????????????????????????????????????????????|
|?group_replication_flow_control_period???????????????|?1???????????????????????????????????????????????????????????????????|
|?group_replication_flow_control_release_percent??????|?50??????????????????????????????????????????????????????????????????|
|?group_replication_force_members?????????????????????|?????????????????????????????????????????????????????????????????????|
|?group_replication_group_name????????????????????????|?cc5e2627-2285-451f-86e6-0be21581539f????????????????????????????????|
|?group_replication_group_seeds???????????????????????|?10.0.2.5:33081,10.0.2.6:33081,10.0.2.7:33081????????????????????????|
|?group_replication_gtid_assignment_block_size????????|?1000000?????????????????????????????????????????????????????????????|
|?group_replication_ip_whitelist??????????????????????|?127.0.0.1/32,10.0.0.0/8,172.16.0.0/12,192.168.0.0/16,192.168.1.0/24?|
|?group_replication_local_address?????????????????????|?10.0.2.6:33081??????????????????????????????????????????????????????|
|?group_replication_member_weight?????????????????????|?50??????????????????????????????????????????????????????????????????|
|?group_replication_poll_spin_loops???????????????????|?0???????????????????????????????????????????????????????????????????|
|?group_replication_recovery_complete_at??????????????|?TRANSACTIONS_APPLIED????????????????????????????????????????????????|
|?group_replication_recovery_get_public_key???????????|?OFF?????????????????????????????????????????????????????????????????|
|?group_replication_recovery_public_key_path??????????|?????????????????????????????????????????????????????????????????????|
|?group_replication_recovery_reconnect_interval???????|?60??????????????????????????????????????????????????????????????????|
|?group_replication_recovery_retry_count??????????????|?10??????????????????????????????????????????????????????????????????|
|?group_replication_recovery_ssl_ca???????????????????|?????????????????????????????????????????????????????????????????????|
|?group_replication_recovery_ssl_capath???????????????|?????????????????????????????????????????????????????????????????????|
|?group_replication_recovery_ssl_cert?????????????????|?????????????????????????????????????????????????????????????????????|
|?group_replication_recovery_ssl_cipher???????????????|?????????????????????????????????????????????????????????????????????|
|?group_replication_recovery_ssl_crl??????????????????|?????????????????????????????????????????????????????????????????????|
|?group_replication_recovery_ssl_crlpath??????????????|?????????????????????????????????????????????????????????????????????|
|?group_replication_recovery_ssl_key??????????????????|?????????????????????????????????????????????????????????????????????|
|?group_replication_recovery_ssl_verify_server_cert???|?OFF?????????????????????????????????????????????????????????????????|
|?group_replication_recovery_use_ssl??????????????????|?OFF?????????????????????????????????????????????????????????????????|
|?group_replication_single_primary_mode???????????????|?OFF?????????????????????????????????????????????????????????????????|
|?group_replication_ssl_mode??????????????????????????|?DISABLED????????????????????????????????????????????????????????????|
|?group_replication_start_on_boot?????????????????????|?OFF?????????????????????????????????????????????????????????????????|
|?group_replication_transaction_size_limit????????????|?150000000???????????????????????????????????????????????????????????|
|?group_replication_unreachable_majority_timeout??????|?0???????????????????????????????????????????????????????????????????|
+-----------------------------------------------------+---------------------------------------------------------------------+
45?rows?in?set?(0.00?sec)

上面有些配置是我預(yù)先配置好的,后面會(huì)詳細(xì)解析.

3.配置MGR環(huán)境

熟悉mysql的人都知道,mysql支持set global的全局在線配置方式,所以并不局限于配置文件,這里直接解析參數(shù)和給出命令.

假設(shè)我們先寫(xiě)到配置文件my.cnf:

首先,MGR是一定要用GTID的,所以,GTID就必須要開(kāi),新版本的mysql可以在線切換,但是建議直接重啟生效吧,方便快捷,這個(gè)各位要注意一下,

#開(kāi)啟GTID,必須開(kāi)啟
gtid_mode=on
#強(qiáng)制GTID的一致性
enforce-gtid-consistency=on

然后,列舉一些公共參數(shù)的修改

#binlog格式,MGR要求必須是ROW,不過(guò)就算不是MGR,也最好用row
binlog_format=row
#server-id必須是唯一的
server-id?=?258011
#MGR使用樂(lè)觀鎖,所以官網(wǎng)建議隔離級(jí)別是RC,減少鎖粒度
transaction_isolation?=?READ-COMMITTED
#因?yàn)榧簳?huì)在故障恢復(fù)時(shí)互相檢查binlog的數(shù)據(jù),
#所以需要記錄下集群內(nèi)其他服務(wù)器發(fā)過(guò)來(lái)已經(jīng)執(zhí)行過(guò)的binlog,按GTID來(lái)區(qū)分是否執(zhí)行過(guò).
log-slave-updates=1
#binlog校驗(yàn)規(guī)則,5.6之后的高版本是CRC32,低版本都是NONE,但是MGR要求使用NONE
binlog_checksum=NONE
#基于安全的考慮,MGR集群要求復(fù)制模式要改成slave記錄記錄到表中,不然就報(bào)錯(cuò)
master_info_repository=TABLE
#同上配套
relay_log_info_repository=TABLE

最后就是MGR自身的獨(dú)有配置參數(shù)了.

#記錄事務(wù)的算法,官網(wǎng)建議設(shè)置該參數(shù)使用?XXHASH64?算法
transaction_write_set_extraction?=?XXHASH64
#相當(dāng)于此GROUP的名字,是UUID值,不能和集群內(nèi)其他GTID值的UUID混用,可用uuidgen來(lái)生成一個(gè)新的,
#主要是用來(lái)區(qū)分整個(gè)內(nèi)網(wǎng)里邊的各個(gè)不同的GROUP,而且也是這個(gè)group內(nèi)的GTID值的UUID
loose-group_replication_group_name?=?'cc5e2627-2285-451f-86e6-0be21581539f'
#IP地址白名單,默認(rèn)只添加127.0.0.1,不會(huì)允許來(lái)自外部主機(jī)的連接,按需安全設(shè)置
loose-group_replication_ip_whitelist?=?'127.0.0.1/8,192.168.1.0/24,10.0.0.0/8,10.18.89.49/22'
#是否隨服務(wù)器啟動(dòng)而自動(dòng)啟動(dòng)組復(fù)制,不建議直接啟動(dòng),怕故障恢復(fù)時(shí)有擾亂數(shù)據(jù)準(zhǔn)確性的特殊情況
loose-group_replication_start_on_boot?=?OFF
#本地MGR的IP地址和端口，host:port,是MGR的端口,不是數(shù)據(jù)庫(kù)的端口
loose-group_replication_local_address?=?'10.0.2.5:33081'
#需要接受本MGR實(shí)例控制的服務(wù)器IP地址和端口,是MGR的端口,不是數(shù)據(jù)庫(kù)的端口
loose-group_replication_group_seeds?=?'10.0.2.5:33081,10.0.2.6:33081,10.0.2.7:33081'
#開(kāi)啟引導(dǎo)模式,添加組成員，用于第一次搭建MGR或重建MGR的時(shí)候使用,只需要在集群內(nèi)的其中一臺(tái)開(kāi)啟,
loose-group_replication_bootstrap_group?=?OFF
#是否啟動(dòng)單主模式，如果啟動(dòng)，則本實(shí)例是主庫(kù)，提供讀寫(xiě)，其他實(shí)例僅提供讀,如果為off就是多主模式了
loose-group_replication_single_primary_mode?=?off
#多主模式下,強(qiáng)制檢查每一個(gè)實(shí)例是否允許該操作,如果不是多主,可以關(guān)閉
loose-group_replication_enforce_update_everywhere_checks?=?on

重點(diǎn)來(lái)解析幾個(gè)參數(shù):

group_replication_group_name:????這個(gè)必須是獨(dú)立的UUID值,不能和集群里面其他的數(shù)據(jù)庫(kù)的GTID的UUID值一樣,在linux系統(tǒng)下可以用uuidgen來(lái)生成一個(gè)新的UUID

group_replication_ip_whitelist:????關(guān)于IP白名單來(lái)說(shuō),本來(lái)是安全設(shè)置,如果全內(nèi)網(wǎng)涵蓋是不太適合的,我這樣設(shè)置只是為了方便,這個(gè)參數(shù)可以set global動(dòng)態(tài)修改,還是比較方便的

group_replication_start_on_boot:????不建議隨系統(tǒng)啟動(dòng)的原因有兩個(gè),第一個(gè)就是怕故障恢復(fù)時(shí)的極端情況下影響數(shù)據(jù)準(zhǔn)確性,第二個(gè)就是怕一些添加或移除節(jié)點(diǎn)的操作被這個(gè)參數(shù)影響到

group_replication_local_address:????特別注意的是這個(gè)端口并不是數(shù)據(jù)庫(kù)服務(wù)端口,是MGR的服務(wù)端口,而且要保證這個(gè)端口沒(méi)有被使用,是MGR互相通信使用的端口.

group_replication_group_seeds:????接受本group控制的IP地址和端口號(hào),這個(gè)端口也是MGR的服務(wù)端口,可以用set global動(dòng)態(tài)修改,用以添加和移動(dòng)節(jié)點(diǎn).

group_replication_bootstrap_group:????需要特別注意,引導(dǎo)的服務(wù)器只需要一臺(tái),所以集群內(nèi)其他服務(wù)器都不需要開(kāi)啟這個(gè)參數(shù),默認(rèn)off就好了,有需要再set global來(lái)開(kāi)啟就足夠了.

group_replication_single_primary_mode:????取決于想用的是多主模式還是單主模式,如果是單主模式,就類似于半同步復(fù)制,但是比半同步要求更高,因?yàn)樾枰簝?nèi)過(guò)半數(shù)的服務(wù)器寫(xiě)入成功后,主庫(kù)才會(huì)返回寫(xiě)入成功,數(shù)據(jù)一致性也更高,通常金融服務(wù)也更推薦這種使用方法.如果是多主模式,看上去性能更高,但是事務(wù)沖突的幾率也更高,雖然MGR內(nèi)部有先到先得原則,但是這些還是不能忽略,對(duì)于高并發(fā)環(huán)境,更加可能是致命的,所以一般多主模式也是建議分開(kāi)來(lái)使用,一個(gè)地址鏈接一個(gè)庫(kù),從邏輯操作上區(qū)分開(kāi)來(lái),避免沖突的可能.
group_replication_enforce_update_everywhere_checks:????如果是單主模式,因?yàn)椴淮嬖诙嘀魍瑫r(shí)操作的可能,這個(gè)強(qiáng)制檢查是可以關(guān)閉,因?yàn)橐呀?jīng)不存在這樣的操作,多主是必須要開(kāi)的,不開(kāi)的話數(shù)據(jù)就可能出現(xiàn)錯(cuò)亂了.

如果用set global方式動(dòng)態(tài)開(kāi)啟的話就如下了:

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=?'cc5e2627-2285-451f-86e6-0be21581539f';
set?global?group_replication_local_address='10.0.2.5:33081';
set?global?group_replication_group_seeds='10.0.2.5:33081,10.0.2.6:33081,10.0.2.7:33081';
set?global?group_replication_ip_whitelist?=?'127.0.0.1/8,192.168.1.0/24,10.0.0.1/8,10.18.89.49/22';
set?global?group_replication_single_primary_mode=off;
set?global?group_replication_enforce_update_everywhere_checks=on;

需要特別注意的是,同一集群group內(nèi)的數(shù)據(jù)庫(kù)服務(wù)器的配置,都必須保持一致,不然是會(huì)報(bào)錯(cuò)的,或者是造成一些奇葩事情.當(dāng)然了,server-id和本機(jī)的IP地址端口要注意區(qū)分.

配置好了,就可以準(zhǔn)備啟動(dòng)了,但是啟動(dòng)有順序要求,需要特別注意.

4.啟動(dòng)MGR集群

就如上面說(shuō)的,啟動(dòng)MGR是要注意順序的,因?yàn)樾枰衅渲幸慌_(tái)數(shù)據(jù)庫(kù)做引導(dǎo),其他數(shù)據(jù)庫(kù)才可以順利加入進(jìn)來(lái).

如果是單主模式,那么主庫(kù)就一定要先啟動(dòng)并做引導(dǎo),不然就不是主了.

當(dāng)出現(xiàn)異常時(shí),應(yīng)該要去查看mysql報(bào)錯(cuò)文件mysql.err,一般都有相應(yīng)的error日志提示.

好了,轉(zhuǎn)回正題,現(xiàn)在假設(shè)用10.0.2.6這臺(tái)服務(wù)器做引導(dǎo),先登進(jìn)本地mysql服務(wù)端:

#啟動(dòng)引導(dǎo),注意,只有這套開(kāi)啟引導(dǎo),其他兩臺(tái)都請(qǐng)忽略這一步
mysql>?SET?GLOBAL?group_replication_bootstrap_group=ON;
#創(chuàng)建一個(gè)用戶來(lái)做同步的用戶,并授權(quán),所有集群內(nèi)的服務(wù)器都需要做
mysql>?create?user?'sroot'@'%'?identified?by?'123123';
mysql>?grant?REPLICATION?SLAVE?on?*.*?to?'sroot'@'%'?with?grant?option;
#清空所有舊的GTID信息,避免沖突
mysql>?reset?master;
#創(chuàng)建同步規(guī)則認(rèn)證信息,就是剛才授權(quán)的那個(gè)用戶,和一般的主從規(guī)則寫(xiě)法不太一樣
mysql>?CHANGE?MASTER?TO?MASTER_USER='sroot',?MASTER_PASSWORD='123123'?FOR?CHANNEL?'group_replication_recovery';
#啟動(dòng)MGR
mysql>?start?group_replication;
#查看是否啟動(dòng)成功,看到online就是成功了
mysql>?SELECT?*?FROM?performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
|?CHANNEL_NAME??????????????|?MEMBER_ID????????????????????????????|?MEMBER_HOST?|?MEMBER_PORT?|?MEMBER_STATE?|?MEMBER_ROLE?|?MEMBER_VERSION?|
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
|?group_replication_applier?|?a29a1b91-4908-11e8-848b-08002778eea7?|?ubuntu??????|????????3308?|?ONLINE???????|?PRIMARY?????|?8.0.11?????????|
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
1?row?in?set?(0.02?sec)
#這個(gè)時(shí)候,就可以先關(guān)閉引導(dǎo)了
mysql>?SET?GLOBAL?group_replication_bootstrap_group=OFF;

然后,就到另外兩臺(tái)服務(wù)器10.0.2.5和10.0.2.7了,也是要登進(jìn)本地mysql服務(wù)端:

#不需要啟動(dòng)引導(dǎo)了,下面大致是類似的
#用戶授權(quán)還是要做的
mysql>?create?user?'sroot'@'%'?identified?by?'123123';
mysql>?grant?REPLICATION?SLAVE?on?*.*?to?'sroot'@'%'?with?grant?option;
#清空所有舊的GTID信息,避免沖突
mysql>?reset?master;
#創(chuàng)建同步規(guī)則認(rèn)證信息,就是剛才授權(quán)的那個(gè)用戶,和一般的主從規(guī)則寫(xiě)法不太一樣
mysql>?CHANGE?MASTER?TO?MASTER_USER='sroot',?MASTER_PASSWORD='123123'?FOR?CHANNEL?'group_replication_recovery';
#啟動(dòng)MGR
mysql>?start?group_replication;
#查看是否啟動(dòng)成功,看到online就是成功了
mysql>?SELECT?*?FROM?performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
|?CHANNEL_NAME??????????????|?MEMBER_ID????????????????????????????|?MEMBER_HOST?|?MEMBER_PORT?|?MEMBER_STATE?|?MEMBER_ROLE?|?MEMBER_VERSION?|
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
|?group_replication_applier?|?a29a1b91-4908-11e8-848b-08002778eea7?|?ubuntu??????|????????3308?|?ONLINE???????|?PRIMARY?????|?8.0.11?????????|
|?group_replication_applier?|?d058176a-51cf-11e8-8c95-080027e7b723?|?ubuntu??????|????????3308?|?ONLINE???????|?PRIMARY?????|?8.0.11?????????|
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
2?rows?in?set?(0.00?sec)

如此類推,在10.0.2.7上就應(yīng)該是下面這樣了

mysql>?SELECT?*?FROM?performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
|?CHANNEL_NAME??????????????|?MEMBER_ID????????????????????????????|?MEMBER_HOST?|?MEMBER_PORT?|?MEMBER_STATE?|?MEMBER_ROLE?|?MEMBER_VERSION?|
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
|?group_replication_applier?|?a29a1b91-4908-11e8-848b-08002778eea7?|?ubuntu??????|????????3308?|?ONLINE???????|?PRIMARY?????|?8.0.11?????????|
|?group_replication_applier?|?af892b6e-49ca-11e8-9c9e-080027b04376?|?ubuntu??????|????????3308?|?ONLINE???????|?PRIMARY?????|?8.0.11?????????|
|?group_replication_applier?|?d058176a-51cf-11e8-8c95-080027e7b723?|?ubuntu??????|????????3308?|?ONLINE???????|?PRIMARY?????|?8.0.11?????????|
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3?rows?in?set?(0.00?sec)

看到MEMBER_STATE全部都是online就是成功連接上了,不過(guò)如果出現(xiàn)故障,是會(huì)被剔除出集群的并且在本機(jī)上會(huì)顯示error,這個(gè)時(shí)候就需要去看本機(jī)的mysql報(bào)錯(cuò)文件mysql.err了.

需要注意的是,現(xiàn)在是多主模式,MEMBER_ROLE里顯示的都是PRIMARY,如果是單主模式,就會(huì)只顯示一個(gè)PRIMARY,其他是SECONDARY了.

使用

在多主模式下,下面這些連接方式都是能直接讀寫(xiě)的

mysql?-usroot?-p123123?-h20.0.2.5?-P3308
mysql?-usroot?-p123123?-h20.0.2.6?-P3308
mysql?-usroot?-p123123?-h20.0.2.7?-P3308

怎么操作我就不說(shuō)了,和以前的mysql一樣create,insert,delete一樣,你就看到其他服務(wù)器也會(huì)有數(shù)據(jù)了.

如果是單主的話,那么就只有PRIMARY狀態(tài)的主庫(kù)可以寫(xiě)數(shù)據(jù),SECONDARY狀態(tài)的只能讀不能寫(xiě),例如下面這樣

mysql>?select?*?from?ttt;
+----+--------+
|?id?|?name???|
+----+--------+
|??1?|?ggg????|
|??2?|?ffff???|
|??3?|?hhhhh??|
|??4?|?tyyyyy?|
|??5?|?aaaaaa?|
+----+--------+
5?rows?in?set?(0.00?sec)
mysql>?delete?from?ttt?where?id?=?5;
ERROR?1290?(HY000):?The?MySQL?server?is?running?with?the?--super-read-only?option?so?it?cannot?execute?this?statement

這些操作相關(guān)就不詳細(xì)展開(kāi)了,搭好了就可以慢慢試.

管理維護(hù)

為了驗(yàn)證我上面說(shuō)過(guò)的東西,先看看當(dāng)前的GTID和從庫(kù)狀態(tài)

#查一下GTID,就是之前設(shè)的那個(gè)group的uuid
mysql>?show?master?status;
+------------------+----------+--------------+------------------+---------------------------------------------------+
|?File?????????????|?Position?|?Binlog_Do_DB?|?Binlog_Ignore_DB?|?Executed_Gtid_Set?????????????????????????????????|
+------------------+----------+--------------+------------------+---------------------------------------------------+
|?mysql-bin.000003?|?????4801?|??????????????|??????????????????|?cc5e2627-2285-451f-86e6-0be21581539f:1-23:1000003?|
+------------------+----------+--------------+------------------+---------------------------------------------------+
1?row?in?set?(0.00?sec)
#再看從庫(kù)狀態(tài),沒(méi)有數(shù)據(jù),因?yàn)楦静皇侵鲝慕Y(jié)構(gòu)
mysql>?show?slave?status;
Empty?set?(0.00?sec)

上面看到了一條命令,是查當(dāng)前節(jié)點(diǎn)信息的,下面慢慢列舉一些常用的命令

#查看group內(nèi)所有成員的節(jié)點(diǎn)信息
mysql>?SELECT?*?FROM?performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
|?CHANNEL_NAME??????????????|?MEMBER_ID????????????????????????????|?MEMBER_HOST?|?MEMBER_PORT?|?MEMBER_STATE?|?MEMBER_ROLE?|?MEMBER_VERSION?|
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
|?group_replication_applier?|?a29a1b91-4908-11e8-848b-08002778eea7?|?ubuntu??????|????????3308?|?ONLINE???????|?PRIMARY?????|?8.0.11?????????|
|?group_replication_applier?|?af892b6e-49ca-11e8-9c9e-080027b04376?|?ubuntu??????|????????3308?|?ONLINE???????|?SECONDARY???|?8.0.11?????????|
|?group_replication_applier?|?d058176a-51cf-11e8-8c95-080027e7b723?|?ubuntu??????|????????3308?|?ONLINE???????|?SECONDARY???|?8.0.11?????????|
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3?rows?in?set?(0.00?sec)

#查看GROUP中的同步情況,當(dāng)前復(fù)制狀態(tài)
mysql>?select?*?from?performance_schema.replication_group_member_stats\G
***************************?1.?row?***************************
??????????????????????????????CHANNEL_NAME:?group_replication_applier
???????????????????????????????????VIEW_ID:?15258529121778212:5
?????????????????????????????????MEMBER_ID:?a29a1b91-4908-11e8-848b-08002778eea7
???????????????COUNT_TRANSACTIONS_IN_QUEUE:?0
????????????????COUNT_TRANSACTIONS_CHECKED:?9
??????????????????COUNT_CONFLICTS_DETECTED:?0
????????COUNT_TRANSACTIONS_ROWS_VALIDATING:?0
????????TRANSACTIONS_COMMITTED_ALL_MEMBERS:?cc5e2627-2285-451f-86e6-0be21581539f:1-23:1000003
????????????LAST_CONFLICT_FREE_TRANSACTION:?cc5e2627-2285-451f-86e6-0be21581539f:23
COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE:?0
?????????COUNT_TRANSACTIONS_REMOTE_APPLIED:?3
?????????COUNT_TRANSACTIONS_LOCAL_PROPOSED:?9
?????????COUNT_TRANSACTIONS_LOCAL_ROLLBACK:?0
***************************?2.?row?***************************
??????????????????????????????CHANNEL_NAME:?group_replication_applier
???????????????????????????????????VIEW_ID:?15258529121778212:5
?????????????????????????????????MEMBER_ID:?af892b6e-49ca-11e8-9c9e-080027b04376
???????????????COUNT_TRANSACTIONS_IN_QUEUE:?0
????????????????COUNT_TRANSACTIONS_CHECKED:?9
??????????????????COUNT_CONFLICTS_DETECTED:?0
????????COUNT_TRANSACTIONS_ROWS_VALIDATING:?0
????????TRANSACTIONS_COMMITTED_ALL_MEMBERS:?cc5e2627-2285-451f-86e6-0be21581539f:1-23:1000003
????????????LAST_CONFLICT_FREE_TRANSACTION:?cc5e2627-2285-451f-86e6-0be21581539f:23
COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE:?0
?????????COUNT_TRANSACTIONS_REMOTE_APPLIED:?10
?????????COUNT_TRANSACTIONS_LOCAL_PROPOSED:?0
?????????COUNT_TRANSACTIONS_LOCAL_ROLLBACK:?0
***************************?3.?row?***************************
??????????????????????????????CHANNEL_NAME:?group_replication_applier
???????????????????????????????????VIEW_ID:?15258529121778212:5
?????????????????????????????????MEMBER_ID:?d058176a-51cf-11e8-8c95-080027e7b723
???????????????COUNT_TRANSACTIONS_IN_QUEUE:?0
????????????????COUNT_TRANSACTIONS_CHECKED:?9
??????????????????COUNT_CONFLICTS_DETECTED:?0
????????COUNT_TRANSACTIONS_ROWS_VALIDATING:?0
????????TRANSACTIONS_COMMITTED_ALL_MEMBERS:?cc5e2627-2285-451f-86e6-0be21581539f:1-23:1000003
????????????LAST_CONFLICT_FREE_TRANSACTION:?cc5e2627-2285-451f-86e6-0be21581539f:23
COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE:?0
?????????COUNT_TRANSACTIONS_REMOTE_APPLIED:?9
?????????COUNT_TRANSACTIONS_LOCAL_PROPOSED:?0
?????????COUNT_TRANSACTIONS_LOCAL_ROLLBACK:?0
3?rows?in?set?(0.00?sec)

#當(dāng)前server中各個(gè)通道的使用情況，
mysql>?select?*?from?performance_schema.replication_connection_status\G
***************************?1.?row?***************************
??????????????????????????????????????CHANNEL_NAME:?group_replication_applier
????????????????????????????????????????GROUP_NAME:?cc5e2627-2285-451f-86e6-0be21581539f
???????????????????????????????????????SOURCE_UUID:?cc5e2627-2285-451f-86e6-0be21581539f
?????????????????????????????????????????THREAD_ID:?NULL
?????????????????????????????????????SERVICE_STATE:?ON
?????????????????????????COUNT_RECEIVED_HEARTBEATS:?0
??????????????????????????LAST_HEARTBEAT_TIMESTAMP:?0000-00-00?00:00:00.000000
??????????????????????????RECEIVED_TRANSACTION_SET:?cc5e2627-2285-451f-86e6-0be21581539f:1-23:1000003
?????????????????????????????????LAST_ERROR_NUMBER:?0
????????????????????????????????LAST_ERROR_MESSAGE:?
??????????????????????????????LAST_ERROR_TIMESTAMP:?0000-00-00?00:00:00.000000
???????????????????????????LAST_QUEUED_TRANSACTION:?cc5e2627-2285-451f-86e6-0be21581539f:23
?LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP:?2018-05-09?16:38:08.035692
LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP:?0000-00-00?00:00:00.000000
?????LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP:?2018-05-09?16:38:08.031639
???????LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP:?2018-05-09?16:38:08.031753
??????????????????????????????QUEUEING_TRANSACTION:?
????QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP:?0000-00-00?00:00:00.000000
???QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP:?0000-00-00?00:00:00.000000
????????QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP:?0000-00-00?00:00:00.000000
***************************?2.?row?***************************
??????????????????????????????????????CHANNEL_NAME:?group_replication_recovery
????????????????????????????????????????GROUP_NAME:?
???????????????????????????????????????SOURCE_UUID:?
?????????????????????????????????????????THREAD_ID:?NULL
?????????????????????????????????????SERVICE_STATE:?OFF
?????????????????????????COUNT_RECEIVED_HEARTBEATS:?0
??????????????????????????LAST_HEARTBEAT_TIMESTAMP:?0000-00-00?00:00:00.000000
??????????????????????????RECEIVED_TRANSACTION_SET:?
?????????????????????????????????LAST_ERROR_NUMBER:?0
????????????????????????????????LAST_ERROR_MESSAGE:?
??????????????????????????????LAST_ERROR_TIMESTAMP:?0000-00-00?00:00:00.000000
???????????????????????????LAST_QUEUED_TRANSACTION:?
?LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP:?0000-00-00?00:00:00.000000
LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP:?0000-00-00?00:00:00.000000
?????LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP:?0000-00-00?00:00:00.000000
???????LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP:?0000-00-00?00:00:00.000000
??????????????????????????????QUEUEING_TRANSACTION:?
????QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP:?0000-00-00?00:00:00.000000
???QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP:?0000-00-00?00:00:00.000000
????????QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP:?0000-00-00?00:00:00.000000
2?rows?in?set?(0.00?sec)

#當(dāng)前server中各個(gè)通道是否啟用,on是啟用
mysql>?select?*?from?performance_schema.replication_applier_status;
+----------------------------+---------------+-----------------+----------------------------+
|?CHANNEL_NAME???????????????|?SERVICE_STATE?|?REMAINING_DELAY?|?COUNT_TRANSACTIONS_RETRIES?|
+----------------------------+---------------+-----------------+----------------------------+
|?group_replication_applier??|?ON????????????|????????????NULL?|??????????????????????????0?|
|?group_replication_recovery?|?OFF???????????|????????????NULL?|??????????????????????????0?|
+----------------------------+---------------+-----------------+----------------------------+
2?rows?in?set?(0.00?sec)

#單主模式下，查看那個(gè)是主庫(kù),只顯示uuid值
mysql>?select?*?from?performance_schema.global_status?where?VARIABLE_NAME='group_replication_primary_member';
+----------------------------------+--------------------------------------+
|?VARIABLE_NAME????????????????????|?VARIABLE_VALUE???????????????????????|
+----------------------------------+--------------------------------------+
|?group_replication_primary_member?|?a29a1b91-4908-11e8-848b-08002778eea7?|
+----------------------------------+--------------------------------------+
1?row?in?set?(0.00?sec)

例如下面這個(gè)例子

mysql>?show?global?variables?like?'server_uuid';
+---------------+--------------------------------------+
|?Variable_name?|?Value????????????????????????????????|
+---------------+--------------------------------------+
|?server_uuid???|?af892b6e-49ca-11e8-9c9e-080027b04376?|
+---------------+--------------------------------------+
1?row?in?set?(0.00?sec)

mysql>?show?global?variables?like?'super%';
+-----------------+-------+
|?Variable_name???|?Value?|
+-----------------+-------+
|?super_read_only?|?ON????|
+-----------------+-------+
1?row?in?set?(0.00?sec)

好明顯,這臺(tái)不是主庫(kù),super_read_only都開(kāi)啟了.