溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
簡介
MySQL Group Replication（簡稱MGR）字面意思是mysql組復制的意思,但其實他是一個高可用的集群架構,暫時只支持mysql5.7和mysql8.0版本.
是MySQL官方于2016年12月推出的一個全新的高可用與高擴展的解決方案,提供了高可用、高擴展、高可靠的MySQL集群服務.
也是mysql官方基于組復制概念并充分參考MariaDB Galera Cluster和Percona XtraDB Cluster結合而來的新的高可用集群架構.
MySQL Group Replication是建立在基于Paxos的XCom之上的，正因為有了XCom基礎設施，保證數據庫狀態機在節點間的事務一致性，才能在理論和實踐中保證數據庫系統在不同節點間的事務一致性。
由一般主從復制概念擴展,多個節點共同組成一個數據庫集群，事務的提交必須經過半數以上節點同意方可提交,在集群中每個節點上都維護一個數據庫狀態機，保證節點間事務的一致性。
優點:
????高一致性，基于原生復制及paxos協議的組復制技術.
????高容錯性，有自動檢測機制，當出現宕機后,會自動剔除問題節點,其他節點可以正常使用(類似zk集群),當不同節點產生資源爭用沖突時,會按照先到先得處理,并且內置了自動化腦裂防護機制.
????高擴展性，可隨時在線新增和移除節點,會自動同步所有節點上狀態，直到新節點和其他節點保持一致，自動維護新的組信息.
????高靈活性，直接插件形式安裝(5.7.17后自帶.so插件),有單主模式和多主模式，單主模式下，只有主庫可以讀寫,其他從庫會加上super_read_only狀態,只能讀取不可寫入,出現故障會自動選主.
缺點:
????還是太新,不太穩定,暫時性能還略差于PXC,對網絡穩定性要求很高,至少是同機房做.
安裝
1.服務環境設定規劃
ip地址 | mysql版本 | 數據庫端口號 | Server-ID | MGR端口號 | 操作系統 |
| 10.0.2.5 | mysql 8.0.11 | 3308 | 258011 | 33081 | Ubuntu 17.04 |
| 10.0.2.6 | mysql 8.0.11 | 3308 | 268011 | 33081 | Ubuntu 17.04 |
| 10.0.2.7 | mysql 8.0.11 | 3308 | 278011 | 33081 | Ubuntu 17.04 |
多主模式下最好有三臺以上的節點,單主模式則視實際情況而定,不過同個Group最多節點數為9.服務器配置盡量保持一致,因為和PXC一樣,也會有"木桶短板效應".
需要特別注意,mysql數據庫的服務端口號和MGR的服務端口不是一回事,需要區分開來.
而server-id要區分開來是必須的,單純做主從復制也要滿足這一點了.
2.安裝部署
怎么安裝mysql8.0就不多說了,本系列第一篇已經說過了,所以默認就當裝好了.
直接就說怎么安裝MGR了,上面也說了,MGR在mysql5.7.17版本之后就都是自帶插件了,只是沒有安裝上而已,和半同步插件一個套路,所以默認是沒有選項.
所有集群內的服務器都必須安裝MGR插件才能正常使用該功能.
我們可以看到,一開始是沒有裝的
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相關參數也是沒有加載的,只有一個其他相關的參數
mysql>?show?variables?like?'group%'; +----------------------+-------+ |?Variable_name????????|?Value?| +----------------------+-------+ |?group_concat_max_len?|?1024??| +----------------------+-------+ 1?row?in?set
然后,先看看當前插件的目錄
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*
最后,從新進入mysql服務,進行安裝
mysql>install?PLUGIN?group_replication?SONAME?'group_replication.so';
這個時候,就有了
mysql>?show?plugins; +----------------------------+----------+--------------------+----------------------+---------+ |?Name???????????????????????|?Status???|?Type???????????????|?Library??????????????|?License?| +----------------------------+----------+--------------------+----------------------+---------+ ????. ????. ????. |?group_replication??????????|?ACTIVE???|?GROUP?REPLICATION??|?group_replication.so?|?GPL?????| +----------------------------+----------+--------------------+----------------------+---------+
再去看MGR相關的參數,就有很多了
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)
上面有些配置是我預先配置好的,后面會詳細解析.
3.配置MGR環境
熟悉mysql的人都知道,mysql支持set global的全局在線配置方式,所以并不局限于配置文件,這里直接解析參數和給出命令.
假設我們先寫到配置文件my.cnf:
首先,MGR是一定要用GTID的,所以,GTID就必須要開,新版本的mysql可以在線切換,但是建議直接重啟生效吧,方便快捷,這個各位要注意一下,
#開啟GTID,必須開啟 gtid_mode=on #強制GTID的一致性 enforce-gtid-consistency=on
然后,列舉一些公共參數的修改
#binlog格式,MGR要求必須是ROW,不過就算不是MGR,也最好用row binlog_format=row #server-id必須是唯一的 server-id?=?258011 #MGR使用樂觀鎖,所以官網建議隔離級別是RC,減少鎖粒度 transaction_isolation?=?READ-COMMITTED #因為集群會在故障恢復時互相檢查binlog的數據, #所以需要記錄下集群內其他服務器發過來已經執行過的binlog,按GTID來區分是否執行過. log-slave-updates=1 #binlog校驗規則,5.6之后的高版本是CRC32,低版本都是NONE,但是MGR要求使用NONE binlog_checksum=NONE #基于安全的考慮,MGR集群要求復制模式要改成slave記錄記錄到表中,不然就報錯 master_info_repository=TABLE #同上配套 relay_log_info_repository=TABLE
最后就是MGR自身的獨有配置參數了.
#記錄事務的算法,官網建議設置該參數使用?XXHASH64?算法 transaction_write_set_extraction?=?XXHASH64 #相當于此GROUP的名字,是UUID值,不能和集群內其他GTID值的UUID混用,可用uuidgen來生成一個新的, #主要是用來區分整個內網里邊的各個不同的GROUP,而且也是這個group內的GTID值的UUID loose-group_replication_group_name?=?'cc5e2627-2285-451f-86e6-0be21581539f' #IP地址白名單,默認只添加127.0.0.1,不會允許來自外部主機的連接,按需安全設置 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' #是否隨服務器啟動而自動啟動組復制,不建議直接啟動,怕故障恢復時有擾亂數據準確性的特殊情況 loose-group_replication_start_on_boot?=?OFF #本地MGR的IP地址和端口，host:port,是MGR的端口,不是數據庫的端口 loose-group_replication_local_address?=?'10.0.2.5:33081' #需要接受本MGR實例控制的服務器IP地址和端口,是MGR的端口,不是數據庫的端口 loose-group_replication_group_seeds?=?'10.0.2.5:33081,10.0.2.6:33081,10.0.2.7:33081' #開啟引導模式,添加組成員，用于第一次搭建MGR或重建MGR的時候使用,只需要在集群內的其中一臺開啟, loose-group_replication_bootstrap_group?=?OFF #是否啟動單主模式，如果啟動，則本實例是主庫，提供讀寫，其他實例僅提供讀,如果為off就是多主模式了 loose-group_replication_single_primary_mode?=?off #多主模式下,強制檢查每一個實例是否允許該操作,如果不是多主,可以關閉 loose-group_replication_enforce_update_everywhere_checks?=?on
重點來解析幾個參數:
group_replication_group_name:????這個必須是獨立的UUID值,不能和集群里面其他的數據庫的GTID的UUID值一樣,在linux系統下可以用uuidgen來生成一個新的UUID
group_replication_ip_whitelist:????關于IP白名單來說,本來是安全設置,如果全內網涵蓋是不太適合的,我這樣設置只是為了方便,這個參數可以set global動態修改,還是比較方便的
group_replication_start_on_boot:????不建議隨系統啟動的原因有兩個,第一個就是怕故障恢復時的極端情況下影響數據準確性,第二個就是怕一些添加或移除節點的操作被這個參數影響到
group_replication_local_address:????特別注意的是這個端口并不是數據庫服務端口,是MGR的服務端口,而且要保證這個端口沒有被使用,是MGR互相通信使用的端口.
group_replication_group_seeds:????接受本group控制的IP地址和端口號,這個端口也是MGR的服務端口,可以用set global動態修改,用以添加和移動節點.
group_replication_bootstrap_group:????需要特別注意,引導的服務器只需要一臺,所以集群內其他服務器都不需要開啟這個參數,默認off就好了,有需要再set global來開啟就足夠了.
group_replication_single_primary_mode:????取決于想用的是多主模式還是單主模式,如果是單主模式,就類似于半同步復制,但是比半同步要求更高,因為需要集群內過半數的服務器寫入成功后,主庫才會返回寫入成功,數據一致性也更高,通常金融服務也更推薦這種使用方法.如果是多主模式,看上去性能更高,但是事務沖突的幾率也更高,雖然MGR內部有先到先得原則,但是這些還是不能忽略,對于高并發環境,更加可能是致命的,所以一般多主模式也是建議分開來使用,一個地址鏈接一個庫,從邏輯操作上區分開來,避免沖突的可能.
group_replication_enforce_update_everywhere_checks:????如果是單主模式,因為不存在多主同時操作的可能,這個強制檢查是可以關閉,因為已經不存在這樣的操作,多主是必須要開的,不開的話數據就可能出現錯亂了.
如果用set global方式動態開啟的話就如下了:
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內的數據庫服務器的配置,都必須保持一致,不然是會報錯的,或者是造成一些奇葩事情.當然了,server-id和本機的IP地址端口要注意區分.
配置好了,就可以準備啟動了,但是啟動有順序要求,需要特別注意.
4.啟動MGR集群
就如上面說的,啟動MGR是要注意順序的,因為需要有其中一臺數據庫做引導,其他數據庫才可以順利加入進來.
如果是單主模式,那么主庫就一定要先啟動并做引導,不然就不是主了.
當出現異常時,應該要去查看mysql報錯文件mysql.err,一般都有相應的error日志提示.
好了,轉回正題,現在假設用10.0.2.6這臺服務器做引導,先登進本地mysql服務端:
#啟動引導,注意,只有這套開啟引導,其他兩臺都請忽略這一步 mysql>?SET?GLOBAL?group_replication_bootstrap_group=ON; #創建一個用戶來做同步的用戶,并授權,所有集群內的服務器都需要做 mysql>?create?user?'sroot'@'%'?identified?by?'123123'; mysql>?grant?REPLICATION?SLAVE?on?*.*?to?'sroot'@'%'?with?grant?option; #清空所有舊的GTID信息,避免沖突 mysql>?reset?master; #創建同步規則認證信息,就是剛才授權的那個用戶,和一般的主從規則寫法不太一樣 mysql>?CHANGE?MASTER?TO?MASTER_USER='sroot',?MASTER_PASSWORD='123123'?FOR?CHANNEL?'group_replication_recovery'; #啟動MGR mysql>?start?group_replication; #查看是否啟動成功,看到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) #這個時候,就可以先關閉引導了 mysql>?SET?GLOBAL?group_replication_bootstrap_group=OFF;
然后,就到另外兩臺服務器10.0.2.5和10.0.2.7了,也是要登進本地mysql服務端:
#不需要啟動引導了,下面大致是類似的 #用戶授權還是要做的 mysql>?create?user?'sroot'@'%'?identified?by?'123123'; mysql>?grant?REPLICATION?SLAVE?on?*.*?to?'sroot'@'%'?with?grant?option; #清空所有舊的GTID信息,避免沖突 mysql>?reset?master; #創建同步規則認證信息,就是剛才授權的那個用戶,和一般的主從規則寫法不太一樣 mysql>?CHANGE?MASTER?TO?MASTER_USER='sroot',?MASTER_PASSWORD='123123'?FOR?CHANNEL?'group_replication_recovery'; #啟動MGR mysql>?start?group_replication; #查看是否啟動成功,看到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上就應該是下面這樣了
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就是成功連接上了,不過如果出現故障,是會被剔除出集群的并且在本機上會顯示error,這個時候就需要去看本機的mysql報錯文件mysql.err了.
需要注意的是,現在是多主模式,MEMBER_ROLE里顯示的都是PRIMARY,如果是單主模式,就會只顯示一個PRIMARY,其他是SECONDARY了.
使用
在多主模式下,下面這些連接方式都是能直接讀寫的
mysql?-usroot?-p123123?-h20.0.2.5?-P3308 mysql?-usroot?-p123123?-h20.0.2.6?-P3308 mysql?-usroot?-p123123?-h20.0.2.7?-P3308
怎么操作我就不說了,和以前的mysql一樣create,insert,delete一樣,你就看到其他服務器也會有數據了.
如果是單主的話,那么就只有PRIMARY狀態的主庫可以寫數據,SECONDARY狀態的只能讀不能寫,例如下面這樣
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
這些操作相關就不詳細展開了,搭好了就可以慢慢試.
管理維護
為了驗證我上面說過的東西,先看看當前的GTID和從庫狀態
#查一下GTID,就是之前設的那個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) #再看從庫狀態,沒有數據,因為根本不是主從結構 mysql>?show?slave?status; Empty?set?(0.00?sec)
上面看到了一條命令,是查當前節點信息的,下面慢慢列舉一些常用的命令
#查看group內所有成員的節點信息 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中的同步情況,當前復制狀態 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) #當前server中各個通道的使用情況， 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) #當前server中各個通道是否啟用,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) #單主模式下，查看那個是主庫,只顯示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)
例如下面這個例子
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)
好明顯,這臺不是主庫,super_read_only都開啟了.
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
