使用mysql5.7.26搭建MGR單主及多主模式（參考鏈接https://www.jianshu.com/p/ca1af156f656）一、搭建單主1.1前言：MySQL Group Replication（MGR）是MySQL官方在5.7.17版本引進(jìn)的一個(gè)數(shù)據(jù)庫高可用與高擴(kuò)展的解決方案，以插件形式提供，實(shí)現(xiàn)了分布式下數(shù)據(jù)的最終一致性，總結(jié)MGR特點(diǎn)如下：高一致性：基于分布式paxos協(xié)議實(shí)現(xiàn)組復(fù)制，保證數(shù)據(jù)一致性；高容錯(cuò)性：自動(dòng)檢測(cè)機(jī)制，只要不是大多數(shù)節(jié)點(diǎn)都宕機(jī)就可以繼續(xù)工作，內(nèi)置防腦裂保護(hù)機(jī)制；高擴(kuò)展性：節(jié)點(diǎn)的增加與移除會(huì)自動(dòng)更新組成員信息，新節(jié)點(diǎn)加入后，自動(dòng)從其他節(jié)點(diǎn)同步增量數(shù)據(jù)，直到與其他節(jié)點(diǎn)數(shù)據(jù)一致；高靈活性：提供單主模式和多主模式，單主模式在主庫宕機(jī)后能夠自動(dòng)選主，所有寫入都在主節(jié)點(diǎn)進(jìn)行，多主模式支持多節(jié)點(diǎn)寫入。1.2網(wǎng)絡(luò)拓?fù)?92.168.56.141 centos7.node2 ##從192.168.56.149 docker.node01 ##主192.168.56.150 centos7.node1 ##從1.3安裝mysql1.3.1 從官網(wǎng)下載mysql安裝包1.3.2 安裝過程(每臺(tái)服務(wù)器都做一遍)配置集群中全部節(jié)點(diǎn)的免密登錄(以其中一個(gè)節(jié)點(diǎn)為例，其它的節(jié)點(diǎn)類似操作即可)：[root@docker .ssh]# ssh-keygen -t rsa [root@docker .ssh]# ssh-copy-id -i ./id_rsa.pub root@192.168.56.150[root@docker .ssh]# ssh-copy-id -i ./id_rsa.pub root@192.168.56.141測(cè)試(所有節(jié)點(diǎn)操作)[root@docker .ssh]# ssh root@192.168.56.150[root@docker .ssh]# ssh root@192.168.56.141安裝mysql5.7(所有節(jié)點(diǎn)操作)[root@docker local]# tar -zxvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz -C /usr/local/[root@docker local]# cd /usr/local/[root@docker local]# ln -s mysql-5.7.26-linux-glibc2.12-x86_64/ mysql[root@docker local]# groupadd mysql[root@docker local]# useradd -r -g mysql mysql ##創(chuàng)建系統(tǒng)用戶，并設(shè)置組[root@docker local]# chown -R mysql . ## 設(shè)置軟件產(chǎn)品目錄的屬主[root@docker local]# chgrp -R mysql .[root@docker local]# vim /etc/profile.d/mysql.sh ##添加環(huán)境變量，添加如下內(nèi)容export MYSQL_HOME=/usr/local/mysqlexport PATH=$MYSQL_HOME/bin:$PATH[root@docker local]# source /etc/profile[root@docker mysql]# cp support-files/mysql.server /etc/init.d/mysqld ##添加啟動(dòng)uni files/mysql[root@centos7 mysql_data]# systemctl stop firewalld.service ##關(guān)閉防火墻[root@centos7 mysql_data]# systemctl disable firewalld.service [root@centos7 mysql_data]# vim /etc/hosts添加如下內(nèi)容：192.168.56.141 centos7.node2192.168.56.149 docker.node01192.168.56.150 centos7.node1[root@centos7 mysql_data]# vim /etc/my.cnf ###添加內(nèi)容,下面的配置每臺(tái)機(jī)器注意修改server-id、report_host、bind_address、group_replication_local_address都不一樣，其它都一樣[client]user=rootpassword=wiki123[mysqld]########basic settings########server-id = 11port = 3306user = mysqlbind_address = 192.168.56.149report_host=192.168.56.149report_port=3306autocommit = 0character_set_server=utf8mb4skip_name_resolve = 1max_connections = 800max_connect_errors = 1000datadir = /data/mysql_datatransaction_isolation = READ-COMMITTEDexplicit_defaults_for_timestamp = 1join_buffer_size = 134217728tmp_table_size = 67108864tmpdir = /tmpmax_allowed_packet = 16777216sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"interactive_timeout = 1800wait_timeout = 1800read_buffer_size = 16777216read_rnd_buffer_size = 33554432sort_buffer_size = 33554432########log settings########log_error = error.logslow_query_log = 1slow_query_log_file = slow.loglog_queries_not_using_indexes = 1log_slow_admin_statements = 1log_slow_slave_statements = 1log_throttle_queries_not_using_indexes = 10expire_logs_days = 90long_query_time = 2min_examined_row_limit = 100########replication settings########master_info_repository = TABLErelay_log_info_repository = TABLElog_bin = bin.logsync_binlog = 1gtid_mode = onenforce_gtid_consistency = 1log_slave_updatesbinlog_format = rowbinlog_checksum=NONErelay_log = relay.logrelay_log_recovery = 1binlog_gtid_simple_recovery = 1slave_skip_errors = ddl_exist_errors########innodb settings########innodb_page_size = 8192innodb_buffer_pool_size = 2Ginnodb_buffer_pool_instances = 8innodb_buffer_pool_load_at_startup = 1innodb_buffer_pool_dump_at_shutdown = 1innodb_lru_scan_depth = 2000innodb_lock_wait_timeout = 5innodb_io_capacity = 4000innodb_io_capacity_max = 8000innodb_flush_method = O_DIRECTinnodb_file_format = Barracudainnodb_file_format_max = Barracudainnodb_log_group_home_dir = /redolog/innodb_undo_directory = /undolog/innodb_undo_logs = 128innodb_undo_tablespaces = 3innodb_flush_neighbors = 1innodb_log_file_size = 1Ginnodb_log_buffer_size = 524288 ##512Minnodb_purge_threads = 4innodb_large_prefix = 1innodb_thread_concurrency = 64innodb_print_all_deadlocks = 1innodb_strict_mode = 1innodb_sort_buffer_size = 67108864########semi sync replication settings########plugin_dir=/usr/local/mysql/lib/pluginplugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so;group_replication=group_replication.so"###group_replication.so是mgr插件loose_rpl_semi_sync_master_enabled = 1loose_rpl_semi_sync_slave_enabled = 1loose_rpl_semi_sync_master_timeout = 5000####MGR SETTING#######loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"loose-group_replication_start_on_boot=OFFloose-group_replication_local_address= "docker.node01:33061"loose-group_replication_group_seeds= "docker.node01:33061,centos7.node1:33061,centos7.node2:33061"loose-group_replication_bootstrap_group=OFF[mysqld-5.7]innodb_buffer_pool_dump_pct = 40innodb_page_cleaners = 4innodb_undo_log_truncate = 1innodb_max_undo_log_size = 524288 ##512Minnodb_purge_rseg_truncate_frequency = 128binlog_gtid_simple_recovery=1log_timestamps=systemtransaction_write_set_extraction=XXHASH64show_compatibility_56=on創(chuàng)建目錄(所有節(jié)點(diǎn)操作)：創(chuàng)建my.cnf配置文件中的全部目錄，并授權(quán)，下面以datadir = /data/mysql_data為例[root@docker local]# mkdir -pv /data/mysql_data[root@docker local]# chown -R mysql:mysql /data[root@docker local]# chmod -R 770 /data#######################################到這里，安裝的準(zhǔn)備工作已經(jīng)完成，下面開始初始化數(shù)據(jù)庫初始化數(shù)據(jù)庫(所有節(jié)點(diǎn)操作)[root@docker local]# mysqld --initialize --user=mysql[root@docker local]# mysqld_safe --user=mysql & ##啟動(dòng)數(shù)據(jù)庫#######################################到這里，所有節(jié)點(diǎn)的數(shù)據(jù)都已經(jīng)出事化完畢1.3.4 設(shè)置復(fù)制賬號(hào)（所有節(jié)點(diǎn)操作）mysql> SET SQL_LOG_BIN=0;mysql> CREATE USER repl@'%' IDENTIFIED BY 'repl';mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';mysql> FLUSH PRIVILEGES;mysql> SET SQL_LOG_BIN=1;mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';1.3.5 啟動(dòng)MGR單主模式# 啟動(dòng)MGR，在主庫(192.168.56.149)上執(zhí)行mysql> SET GLOBAL group_replication_bootstrap_group=ON;mysql> START GROUP_REPLICATION;mysql> SET GLOBAL group_replication_bootstrap_group=OFF;# 查看MGR組信息mysql> SELECT * FROM performance_schema.replication_group_members;+---------------------------+--------------------------------------+----------------+-------------+--------------+| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |+---------------------------+--------------------------------------+----------------+-------------+--------------+| group_replication_applier | 16855150-f223-11e9-9b22-000c294aef44 | 192.168.56.149 | 3306 | ONLINE |+---------------------------+--------------------------------------+----------------+-------------+--------------+# 其他節(jié)點(diǎn)加入MGR，在從庫（192.168.56.150，192.168.56.141）上執(zhí)行mysql> START GROUP_REPLICATION; ##啟動(dòng)時(shí)報(bào)錯(cuò)ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.出現(xiàn)這個(gè)現(xiàn)象的原因：MySQL是新裝的沒問題，但是每次新裝MySQL都要修改密碼，如果在修改密碼的時(shí)候就已經(jīng)把binlog_format=on配在了/etc/my.cnf中，那么修改密碼的記錄是存在在binlog日志中的。所以就會(huì)提示前文中的日志錯(cuò)誤。解決方案(每一個(gè)從節(jié)點(diǎn)執(zhí)行)：mysql> RESET MASTERmysql> START GROUP_REPLICATION; ##從新啟動(dòng)，問題解決(錯(cuò)誤解決參考鏈接：https://blog.csdn.net/snowhite91/article/details/83791997)# 查看MGR組信息(在主庫(192.168.56.149)上執(zhí)行)mysql> SELECT * FROM performance_schema.replication_group_members;+---------------------------+--------------------------------------+----------------+-------------+--------------+| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |+---------------------------+--------------------------------------+----------------+-------------+--------------+| group_replication_applier | 16855150-f223-11e9-9b22-000c294aef44 | 192.168.56.149 | 3306 | ONLINE || group_replication_applier | 2d1b48ec-dbd1-11e9-9f76-000c2995d20d | 192.168.56.141 | 3306 | ONLINE || group_replication_applier | 6bd815d0-f22b-11e9-b02f-000c29b9031b | 192.168.56.150 | 3306 | ONLINE |+---------------------------+--------------------------------------+----------------+-------------+--------------+mysql> show variables like '%read_only%';##192,168.56.149自動(dòng)為讀寫+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| read_only | OFF || super_read_only | OFF |其它庫，默認(rèn)啟動(dòng)就為只讀mysql> show variables like '%read_only%';+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| read_only | ON || super_read_only | ON |可以看到，3個(gè)節(jié)點(diǎn)狀態(tài)為online，并且主節(jié)點(diǎn)為192.168.56.149，只有主節(jié)點(diǎn)可以寫入，其他節(jié)點(diǎn)只讀，MGR單主模式搭建成功1.3.6 測(cè)試插入數(shù)據(jù)mysql> create table t1(a int);mysql> insert into t1 values(1),(2),(3);ERROR 3098 (HY000): The table does not comply with the requirements by an external plugin.錯(cuò)誤的原因是在MGR下，表必須要有主鍵mysql> create table t1(a int primary key);mysql> insert into t1 values(1),(2),(3);Query OK, 3 rows affected (0.00 sec)二、將單主模式切換為多主模式2.1 要求MGR切換模式需要重新啟動(dòng)組復(fù)制，因些需要在所有節(jié)點(diǎn)上先關(guān)閉組復(fù)制，設(shè)置 group_replication_single_primary_mode=OFF 等參數(shù)，再啟動(dòng)組復(fù)制。2.2 切換步驟# 停止組復(fù)制(所有節(jié)點(diǎn)執(zhí)行)：mysql> stop group_replication;mysql> set global group_replication_single_primary_mode=OFF;mysql> set global group_replication_enforce_update_everywhere_checks=ON;# 隨便選擇某個(gè)節(jié)點(diǎn)執(zhí)行mysql> SET GLOBAL group_replication_bootstrap_group=ON; mysql> START GROUP_REPLICATION; mysql> SET GLOBAL group_replication_bootstrap_group=OFF;# 其他節(jié)點(diǎn)執(zhí)行mysql> START GROUP_REPLICATION; # 查看組信息，所有節(jié)點(diǎn)的 MEMBER_ROLE 都為 PRIMARYmysql> SELECT * FROM performance_schema.replication_group_members;+---------------------------+--------------------------------------+----------------+-------------+--------------+| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |+---------------------------+--------------------------------------+----------------+-------------+--------------+| group_replication_applier | 16855150-f223-11e9-9b22-000c294aef44 | 192.168.56.149 | 3306 | ONLINE || group_replication_applier | 2d1b48ec-dbd1-11e9-9f76-000c2995d20d | 192.168.56.141 | 3306 | ONLINE || group_replication_applier | 6bd815d0-f22b-11e9-b02f-000c29b9031b | 192.168.56.150 | 3306 | ONLINE |+---------------------------+--------------------------------------+----------------+-------------+--------------+可以看到所有節(jié)點(diǎn)狀態(tài)都是online，都可以讀寫，角色都是PRIMARY，MGR多主模式搭建成功。三、從多主切換為單主# 所有節(jié)點(diǎn)執(zhí)行mysql> stop group_replication;mysql> set global group_replication_enforce_update_everywhere_checks=OFF;mysql> set global group_replication_single_primary_mode=ON;# 主節(jié)點(diǎn)（192.168.56.149）執(zhí)行SET GLOBAL group_replication_bootstrap_group=ON; START GROUP_REPLICATION; SET GLOBAL group_replication_bootstrap_group=OFF;# 從節(jié)點(diǎn)（192.168.56.150、192.168.56.149）執(zhí)行START GROUP_REPLICATION; # 查看MGR組信息mysql> SELECT * FROM performance_schema.replication_group_members;

?

總結(jié)

以上是生活随笔為你收集整理的mysql5.7.26搭建MGR单主及多主模式的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。