MGR集群搭建及配置過(guò)程
MGR全稱(chēng)MySQL Group Replication(Mysql組復(fù)制),是MySQL官方于2016年12月推出的一個(gè)全新的高可用與高擴(kuò)展的解決方案。MGR提供了高可用、高擴(kuò)展、高可靠的MySQL集群服務(wù)。在MGR出現(xiàn)之前,用戶(hù)常見(jiàn)的MySQL高可用方式,無(wú)論怎么變化架構(gòu),本質(zhì)就是Master-Slave架構(gòu)。MySQL 5.7版本開(kāi)始支持無(wú)損半同步復(fù)制(lossless semi-sync replication),從而進(jìn)一步提示數(shù)據(jù)復(fù)制的強(qiáng)一致性。
MGR是MySQL數(shù)據(jù)庫(kù)未來(lái)發(fā)展的一個(gè)重要方向。
注意:根據(jù)本人測(cè)試group_replication.so插件是mysql-community-server安裝包中攜帶,如果是rpm安裝或yum安裝存放地址為/usr/lib64/mysql/plugin/目錄下,看下圖。另外在安裝5.7.16版本時(shí)是沒(méi)有這個(gè)插件,而在安裝5.7.20版本有這個(gè)插件,推測(cè)這是一個(gè)5.7.16到5.7.20之間新加的插件,個(gè)人建議安裝5.7.20以上的版本。另外大家請(qǐng)?jiān)诎惭b好mysql后查看一下是否存在這個(gè)插件。
如果提示group_replication.so不存在,或提示有問(wèn)題并且查看時(shí)發(fā)現(xiàn)group_replication.so不存在,請(qǐng)重點(diǎn)看一下mysql的版本。(我搜了一大圈,沒(méi)有一個(gè)人說(shuō)這個(gè)問(wèn)題。表示懷疑自己,如果我錯(cuò)了,請(qǐng)留言。)
(1).MGR的特性
高一致性?;谠鷱?fù)制及paxos協(xié)議的組復(fù)制技術(shù),并以插件的方式提供,提供一致數(shù)據(jù)安全保證;
高容錯(cuò)性。只要不是大多數(shù)節(jié)點(diǎn)壞掉就可以繼續(xù)工作,有自動(dòng)檢測(cè)機(jī)制,當(dāng)不同節(jié)點(diǎn)產(chǎn)生資源爭(zhēng)用沖突時(shí),不會(huì)出現(xiàn)錯(cuò)誤,按照先到者優(yōu)先原則進(jìn)行處理,并且內(nèi)置了自動(dòng)化腦裂防護(hù)機(jī)制;
高擴(kuò)展性。節(jié)點(diǎn)的新增和移除都是自動(dòng)的,新節(jié)點(diǎn)加入后,會(huì)自動(dòng)從其他節(jié)點(diǎn)上同步狀態(tài),直到新節(jié)點(diǎn)和其他節(jié)點(diǎn)保持一致,如果某節(jié)點(diǎn)被移除了,其他節(jié)點(diǎn)自動(dòng)更新組信息,自動(dòng)維護(hù)新的組信息;
高靈活性。有單主模式和多主模式,單主模式下,會(huì)自動(dòng)選主,所有更新操作都在主上進(jìn)行;多主模式下,所有server都可以同時(shí)處理更新操作。
(2).搭建MGR的基礎(chǔ)結(jié)構(gòu)要求和使用限制(重點(diǎn))
基礎(chǔ)結(jié)構(gòu)要求:1.引擎必須為innodb,因?yàn)樾枋聞?wù)支持在commit時(shí)對(duì)各節(jié)點(diǎn)進(jìn)行沖突檢查;2.每個(gè)表必須有主鍵,在進(jìn)行事務(wù)沖突檢測(cè)時(shí)需要利用主鍵值對(duì)比;3.必須開(kāi)啟binlog且為row格式;4.開(kāi)啟GTID,且主從狀態(tài)信息存于表中(--master-info-repository=TABLE 、--relay-log-info-repository=TABLE),--log-slave-updates打開(kāi);5.一致性檢測(cè)設(shè)置--transaction-write-set-extraction=XXHASH64。
使用限制:1.RP和普通復(fù)制binlog校驗(yàn)不能共存,需設(shè)置--binlog-checksum=none;2.不支持gap lock(間隙鎖),隔離級(jí)別需設(shè)置為read_committed;3.不支持對(duì)表進(jìn)行鎖操作(lock /unlock table),不會(huì)發(fā)送到其他節(jié)點(diǎn)執(zhí)行 ,影響需要對(duì)表進(jìn)行加鎖操作的情況,列入mysqldump全表備份恢復(fù)操作;4.不支持serializable(序列化)隔離級(jí)別;5.DDL語(yǔ)句不支持原子性,不能檢測(cè)沖突,執(zhí)行后需自行校驗(yàn)是否一;6.多主模式下不支持外鍵,單主模式下支持外鍵;最多9個(gè)節(jié)點(diǎn),超過(guò)9臺(tái)無(wú)法加入集群
(3).實(shí)驗(yàn)環(huán)境
youxi1 192.168.1.6 CentOS7.6 Mysql5.7.26 端口號(hào)3306 server-id=1
youxi2 192.168.1.7 CentOS7.6 Mysql5.7.26 端口號(hào)3306 server-id=2
youxi3 192.168.1.8 CentOS7.6 Mysql5.7.26 端口號(hào)3306 server-id=3
另外,三臺(tái)服務(wù)器上都進(jìn)行IP的映射(如果此處不映射,請(qǐng)修改/etc/my.cnf配置文件中對(duì)應(yīng)的域名為IP地址)
[root@youxi1 ~]# vim /etc/hosts 192.168.1.6 youxi1.cn youxi1 //長(zhǎng)域名和短域名只要映射一個(gè)即可 192.168.1.7 youxi2.cn youxi2 192.168.1.8 youxi3.cn youxi3
(1).單主模式
我是在空數(shù)據(jù)庫(kù)下操作,如果主數(shù)據(jù)集已存在數(shù)據(jù),需要將主數(shù)據(jù)庫(kù)的數(shù)據(jù)導(dǎo)出再導(dǎo)入到從數(shù)據(jù)庫(kù)。另外還需保證引擎為innodb,每個(gè)表必須存在主鍵。
1)以youxi1作為主服務(wù)器,對(duì)youxi1進(jìn)行配置
創(chuàng)建一個(gè)復(fù)制用的用戶(hù)
mysql> grant replication slave on *.* to 'repl'@'192.168.1.%' identified by '12345678'; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
修改配置文件,加入到[mysqld]模塊下。然后重啟mysqld
[root@youxi1 ~]# vim /etc/my.cnf server-id=1 //Mysql服務(wù)ID gtid-mode=on //全局事務(wù) enforce-gtid-consistency=on //強(qiáng)制GTID的一致性 master-info-repository=TABLE //將master.info元數(shù)據(jù)保存在系統(tǒng)表中 relay-log-info-repository=TABLE //將relay.info元數(shù)據(jù)保存在系統(tǒng)表中 binlog-checksum=none //禁用二進(jìn)制日志事件校驗(yàn) log-slave-updates=on //級(jí)聯(lián)復(fù)制 log-bin=binlog //開(kāi)啟二進(jìn)制日志記錄 binlog-format=ROW //以行的格式記錄 transaction-write-set-extraction=XXHASH64 //使用哈希算法將其編碼為散列 loose-group_replication_group_name='ce9be252-2b71-11e6-b8f4-00212844f856' //加入的組名,可以修改,只要格式對(duì) loose-group_replication_start_on_boot=off //不自動(dòng)啟用組復(fù)制集群 loose-group_replication_local_address='youxi1:33061' //以本機(jī)端口33061接受來(lái)自組中成員的傳入連接 loose-group_replication_group_seeds='youxi1:33061,youxi2:33062,youxi3:33063' //組中成員訪(fǎng)問(wèn)表 loose-group_replication_bootstrap_group=off //不啟用引導(dǎo)組 [root@youxi1 ~]# systemctl restart mysqld
注意:如果防火墻是打開(kāi)的,記得添加mysql的端口號(hào)。
[root@youxi1 ~]# firewall-cmd --permanent --zone=public --add-port={3306,33061}/tcp success [root@youxi1 ~]# firewall-cmd --reload success [root@youxi1 ~]# firewall-cmd --zone=public --list-ports 3306/tcp 33061/tcp
修改master信息,構(gòu)建組復(fù)制(group replication)集群信息
mysql> change master to master_user='repl',master_password='12345678' for channel 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.04 sec)
安裝組復(fù)制(group replication)插件,并查看組件信息
mysql> install PLUGIN group_replication SONAME 'group_replication.so'; Query OK, 0 rows affected (0.21 sec) 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 | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | 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 | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | | validate_password | DISABLED | VALIDATE PASSWORD | validate_password.so | GPL | | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL | +----------------------------+----------+--------------------+----------------------+---------+ 46 rows in set (0.00 sec)
作為主服務(wù)器需要由這臺(tái)服務(wù)器開(kāi)啟引導(dǎo),開(kāi)啟組復(fù)制(group replication)集群
mysql> set global group_replication_bootstrap_group=on; //開(kāi)啟組復(fù)制引導(dǎo) Query OK, 0 rows affected (0.00 sec) mysql> start group_replication; //開(kāi)啟組復(fù)制 Query OK, 0 rows affected (2.24 sec) mysql> set global group_replication_bootstrap_group=off; //關(guān)閉組復(fù)制引導(dǎo) Query OK, 0 rows affected (0.00 sec)
查看到添加到組復(fù)制集群的服務(wù)器信息
mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | c9e3662b-9020-11e9-94aa-000c29721e89 | youxi1 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 1 row in set (0.00 sec)
2)對(duì)youxi2進(jìn)行配置
創(chuàng)建一個(gè)復(fù)制用的用戶(hù)
mysql> grant replication slave on *.* to 'repl'@'192.168.1.%' identified by '12345678'; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)
修改配置文件,加入到[mysqld]模塊下。然后重啟mysqld
[root@youxi2 ~]# vim /etc/my.cnf server-id=2 //修改 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 transaction-write-set-extraction=XXHASH64 loose-group_replication_group_name='ce9be252-2b71-11e6-b8f4-00212844f856' loose-group_replication_start_on_boot=off loose-group_replication_local_address='youxi2:33062' //修改 loose-group_replication_group_seeds='youxi1:33061,youxi2:33062,youxi3:33063' loose-group_replication_bootstrap_group=off [root@youxi2 ~]# systemctl restart mysqld
注意:如果防火墻是打開(kāi)的,記得添加mysql的端口號(hào)。
[root@youxi2 ~]# firewall-cmd --permanent --zone=public --add-port={3306,33062}/tcp success [root@youxi2 ~]# firewall-cmd --reload success [root@youxi2 ~]# firewall-cmd --zone=public --list-ports 3306/tcp 33062/tcp
修改master信息,構(gòu)建組復(fù)制(group replication)集群信息
mysql> change master to master_user='repl',master_password='12345678' for channel 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.05 sec)
安裝組復(fù)制(group replication)插件
mysql> install PLUGIN group_replication SONAME 'group_replication.so'; Query OK, 0 rows affected (0.35 sec)
把youxi2加到之前的組復(fù)制(group replication)
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> start group_replication; Query OK, 0 rows affected, 1 warning (5.92 sec)
查看到添加到組復(fù)制集群的服務(wù)器信息
mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 8fbf8b41-84fe-11e9-897e-000c29f27e52 | youxi2 | 3306 | ONLINE | | group_replication_applier | c9e3662b-9020-11e9-94aa-000c29721e89 | youxi1 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 2 rows in set (0.00 sec)
3)youxi3的配置與youxi2的配置幾乎一樣,只需在/etc/my.cnf修改server-id和loose-group_replication_local_address即可。
查看一下組復(fù)制集群信息
mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 74df3399-91b8-11e9-be5f-000c299fdf40 | youxi3 | 3306 | ONLINE | | group_replication_applier | 8fbf8b41-84fe-11e9-897e-000c29f27e52 | youxi2 | 3306 | ONLINE | | group_replication_applier | c9e3662b-9020-11e9-94aa-000c29721e89 | youxi1 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 3 rows in set (0.01 sec)
注意:如果防火墻是打開(kāi)的,記得添加mysql的端口號(hào)。
[root@youxi3 ~]# firewall-cmd --permanent --zone=public --add-port=3306/tcp success [root@youxi3 ~]# firewall-cmd --permanent --zone=public --add-port=33063/tcp success [root@youxi3 ~]# firewall-cmd --reload success [root@youxi3 ~]# firewall-cmd --zone=public --list-ports 3306/tcp 33063/tcp
4)測(cè)試
在youxi1上創(chuàng)建數(shù)據(jù)
mysql> create database test_db; Query OK, 1 row affected (0.00 sec) mysql> use test_db; Database changed mysql> create table user_tb(id int key,name varchar(20)); //id是主鍵,引擎默認(rèn)是innodb Query OK, 0 rows affected (0.02 sec) mysql> insert into user_tb values(1,'zhangsan'); Query OK, 1 row affected (0.08 sec)
在youxi2上查看
mysql> select * from test_db.user_tb; +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | +----+----------+ 1 row in set (0.00 sec)
在youxi3上查看
mysql> select * from test_db.user_tb; +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | +----+----------+ 1 row in set (0.00 sec)
5)那么怎么區(qū)分主從服務(wù)器
MGR區(qū)分主從服務(wù)器使用show variables like '%read_only%';查看read_only相關(guān)參數(shù)。如果是主服務(wù)器(youxi1),會(huì)顯示如下:
mysql> show variables like '%read_only%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_read_only | OFF | | read_only | OFF | | super_read_only | OFF | | transaction_read_only | OFF | | tx_read_only | OFF | +-----------------------+-------+ 5 rows in set (0.00 sec)
如果是從服務(wù)器(youxi2)會(huì)顯示如下:
mysql> show variables like '%read_only%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_read_only | OFF | | read_only | ON | | super_read_only | ON | | transaction_read_only | OFF | | tx_read_only | OFF | +-----------------------+-------+ 5 rows in set (0.01 sec)
6)查看當(dāng)前服務(wù)器的組復(fù)制集群參數(shù)設(shè)置列表
mysql> show variables like 'group_replication%'; +----------------------------------------------------+----------------------------------------+ | Variable_name | Value | +----------------------------------------------------+----------------------------------------+ | group_replication_allow_local_disjoint_gtids_join | OFF | | group_replication_allow_local_lower_version_join | OFF | | group_replication_auto_increment_increment | 7 | | group_replication_bootstrap_group | OFF | | group_replication_components_stop_timeout | 31536000 | | group_replication_compression_threshold | 1000000 | | group_replication_enforce_update_everywhere_checks | OFF | | group_replication_exit_state_action | READ_ONLY | | group_replication_flow_control_applier_threshold | 25000 | | group_replication_flow_control_certifier_threshold | 25000 | | group_replication_flow_control_mode | QUOTA | | group_replication_force_members | | | group_replication_group_name | ce9be252-2b71-11e6-b8f4-00212844f856 | | group_replication_group_seeds | youxi1:33061,youxi2:33062,youxi3:33063 | | group_replication_gtid_assignment_block_size | 1000000 | | group_replication_ip_whitelist | AUTOMATIC | | group_replication_local_address | youxi1:33061 | | group_replication_member_weight | 50 | | group_replication_poll_spin_loops | 0 | | group_replication_recovery_complete_at | TRANSACTIONS_APPLIED | | 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 | ON | | group_replication_ssl_mode | DISABLED | | group_replication_start_on_boot | OFF | | group_replication_transaction_size_limit | 0 | | group_replication_unreachable_majority_timeout | 0 | +----------------------------------------------------+----------------------------------------+ 36 rows in set (0.00 sec)
7)如果主服務(wù)器出問(wèn)題,MGR會(huì)自動(dòng)切換主服務(wù)器
將youxi1的mysqld人為關(guān)閉
[root@youxi1 ~]# systemctl stop mysqld
之后前往youxi2和youxi3查看誰(shuí)是主服務(wù)器
mysql> show variables like '%read_only%'; //這是youxi2的 +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_read_only | OFF | | read_only | ON | | super_read_only | ON | | transaction_read_only | OFF | | tx_read_only | OFF | +-----------------------+-------+ 5 rows in set (0.01 sec) mysql> show variables like '%read_only%'; //這是youxi3的 +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_read_only | OFF | | read_only | OFF | | super_read_only | OFF | | transaction_read_only | OFF | | tx_read_only | OFF | +-----------------------+-------+ 5 rows in set (0.02 sec)
可以看到y(tǒng)ouxi3變成了主服務(wù)器,那么嘗試在youxi3中插入數(shù)據(jù)
mysql> insert into test_db.user_tb values(2,'lisi'); Query OK, 1 row affected (0.14 sec)
再到y(tǒng)ouxi2中查看數(shù)據(jù)
mysql> select * from test_db.user_tb; +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 2 | lisi | +----+----------+ 2 rows in set (0.00 sec)
(2).多主模式
1)在創(chuàng)建時(shí)就啟用多主模式
在創(chuàng)建時(shí)就啟用多主模式只需在修改配置文件/etc/my.cnf時(shí)多加兩行參數(shù)。其余保持不變
loose-group_replication_single_primary_mode=off //關(guān)閉單master模式 loose-group_replication_enforce_update_everywhere_checks=ON //多主一致性檢查
2)由單主模式改為多主模式
由單主改為多主時(shí),一樣需要所有服務(wù)器配置信息增加兩行參數(shù),為了下次開(kāi)啟就是多主模式
loose-group_replication_single_primary_mode=off //關(guān)閉單master模式 loose-group_replication_enforce_update_everywhere_checks=ON //開(kāi)啟多主一致性檢查
然后全部服務(wù)器停止組復(fù)制(GROUP_REPLICATION)集群,并設(shè)置參數(shù)
mysql> stop GROUP_REPLICATION; Query OK, 0 rows affected (9.51 sec) mysql> set global group_replication_single_primary_mode=off; //關(guān)閉單主模式 Query OK, 0 rows affected (0.00 sec) mysql> set global group_replication_enforce_update_everywhere_checks=ON; //開(kāi)啟多主一致性檢查 Query OK, 0 rows affected (0.00 sec)
選擇其中一臺(tái)引導(dǎo)組復(fù)制(GROUP_REPLICATION)集群
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 (2.13 sec) mysql> SET GLOBAL group_replication_bootstrap_group=OFF; Query OK, 0 rows affected (0.01 sec)
剩下的開(kāi)啟組復(fù)制(GROUP_REPLICATION)即可
mysql> start group_replication; Query OK, 0 rows affected, 1 warning (6.05 sec)
最后查看非引導(dǎo)組復(fù)制的服務(wù)器
mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 74df3399-91b8-11e9-be5f-000c299fdf40 | youxi3 | 3306 | ONLINE | | group_replication_applier | 8fbf8b41-84fe-11e9-897e-000c29f27e52 | youxi2 | 3306 | ONLINE | | group_replication_applier | c9e3662b-9020-11e9-94aa-000c29721e89 | youxi1 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 3 rows in set (0.00 sec) mysql> show variables like '%read_only%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_read_only | OFF | | read_only | OFF | | super_read_only | OFF | | transaction_read_only | OFF | | tx_read_only | OFF | +-----------------------+-------+ 5 rows in set (0.01 sec)
(3).如果宕機(jī)了
停掉youxi1的mysqld模擬宕機(jī)
[root@youxi1 ~]# systemctl stop mysqld
這時(shí)候查看youxi2和youxi3誰(shuí)是新的master
[root@youxi2 ~]# mysql -uroot -p123456 mysql> show variables like '%read_only%'; //這是youxi2的 +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_read_only | OFF | | read_only | ON | | super_read_only | ON | | transaction_read_only | OFF | | tx_read_only | OFF | +-----------------------+-------+ 5 rows in set (0.00 sec) [root@youxi3 ~]# mysql -uroot -p123456 mysql> show variables like '%read_only%'; //這是youxi3的,可以看出youxi3成為了新的master +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_read_only | OFF | | read_only | OFF | | super_read_only | OFF | | transaction_read_only | OFF | | tx_read_only | OFF | +-----------------------+-------+ 5 rows in set (0.00 sec)
在youxi3上寫(xiě)入數(shù)據(jù),這是因?yàn)樯a(chǎn)環(huán)境數(shù)據(jù)庫(kù)是一直在使用的狀態(tài),不可能等你修復(fù)后再用
[root@youxi3 ~]# mysql -uroot -p123456 mysql> insert into test_db.user_tb values(2,'lisi'); Query OK, 1 row affected (0.03 sec) mysql> select * from test_db.user_tb; +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 2 | lisi | +----+----------+ 2 rows in set (0.00 sec)
將修好的youxi1添加回復(fù)制組
[root@youxi1 ~]# systemctl start mysqld [root@youxi1 ~]# mysql -uroot -p123456 mysql> select * from performance_schema.replication_group_members; +---------------------------+-----------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+-----------+-------------+-------------+--------------+ | group_replication_applier | | | NULL | OFFLINE | +---------------------------+-----------+-------------+-------------+--------------+ 1 row in set (0.00 sec) mysql> change master to master_user='repl',master_password='12345678' for channel 'group_replication_recovery'; //這一步是為了以防萬(wàn)一, Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> set global group_replication_allow_local_disjoint_gtids_join=ON; //將這臺(tái)服務(wù)器重新添加進(jìn)復(fù)制組 Query OK, 0 rows affected (0.00 sec) mysql> start group_replication; Query OK, 0 rows affected (3.36 sec) mysql> select * from performance_schema.replication_group_members; //查看是否加入復(fù)制組 +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 07c6f159-964f-11e9-b2c7-000c2934a723 | youxi3 | 3306 | ONLINE | | group_replication_applier | 8247f048-962f-11e9-a210-000c2975fa5d | youxi2 | 3306 | ONLINE | | group_replication_applier | 8b703193-962a-11e9-b582-000c29e6d627 | youxi1 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 3 rows in set (0.00 sec) mysql> select * from test_db.user_tb; //查看是否同步數(shù)據(jù),沒(méi)有同步請(qǐng)耐心等待,時(shí)間可能會(huì)長(zhǎng)點(diǎn) +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 2 | lisi | +----+----------+ 2 rows in set (0.00 sec)
到此這篇關(guān)于MGR集群搭建的文章就介紹到這了,更多相關(guān)MGR集群搭建內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
3種高效的Tags標(biāo)簽系統(tǒng)數(shù)據(jù)庫(kù)設(shè)計(jì)方案分享
這篇文章主要介紹了3種高效的Tags標(biāo)簽系統(tǒng)數(shù)據(jù)庫(kù)設(shè)計(jì)方案分享,現(xiàn)在主流的博客、CMS系統(tǒng)都有一個(gè)標(biāo)簽系統(tǒng),本文就探討它的數(shù)據(jù)庫(kù)設(shè)計(jì)方式,需要的朋友可以參考下2014-07-07mysql出現(xiàn)提示錯(cuò)誤10061的解決方法
這篇文章主要為大家詳細(xì)介紹了mysql出現(xiàn)提示錯(cuò)誤10061的解決方法,具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2017-10-10mysql如何修改表結(jié)構(gòu)(alter table),多列/多字段
這篇文章主要介紹了mysql如何修改表結(jié)構(gòu)(alter table),多列/多字段問(wèn)題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2022-12-12mysql存儲(chǔ)過(guò)程多層游標(biāo)循環(huán)嵌套的寫(xiě)法分享
這篇文章主要介紹了mysql存儲(chǔ)過(guò)程多層游標(biāo)循環(huán)嵌套的寫(xiě)法,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-07-07mysql如何將sql查詢(xún)的結(jié)果以百分比展示出來(lái)
這篇文章主要給大家介紹了關(guān)于mysql如何將sql查詢(xún)的結(jié)果以百分比展示出來(lái)的相關(guān)資料,用到了MySQL字符串處理中的兩個(gè)函數(shù)concat()和left()實(shí)現(xiàn)查詢(xún)結(jié)果以百分比顯示,需要的朋友可以參考下2023-08-08