MySQL8.0的MGR多主搭建與測試
環境：
虛擬機器VMware，
OS：CentOS Linux release 7.3.1611 (Core)
按照下述的單機Mysql8.0的安裝方式進行安裝完之後，將該機複製兩臺，分別修改IP為192.168.1.78和192.168.1.78，以及兩臺新機器配置檔案/etc/my.cnf的serverid，還有要去資料路徑下刪掉auto.cnf不然配置不成。
vim /etc/hosts
192.168.1.78 mgr-mul1
192.168.1.79 mgr-mul2
192.168.1.80 mgr-mul3
要用hostname修改每臺機器的主機名確保不相同，不然配置不成功。
一、單機安裝(192.168.1.78)
官網下載8.0.11版本的tar.gz包解壓，複製更名到/usr/local/mysql
建立資料路徑及日誌路徑並把他們許可權改給mysql使用者：
/data/mysql3306/mysql3306
/data/mysql3306/logs
配置檔案如下：注意修改server-id及組相關ip
vim /etc/my.cnf
-----------------------------------------------------------------------
[client]
port = 3306
socket = /tmp/mysql3306.sock
default_character_set = utf8mb4
[mysql]
default_character_set = utf8mb4
[mysqld]
lower_case_table_names = 1
port = 3306
basedir = /usr/local/mysql8
datadir = /data/mysql3306/mysql3306
socket = /tmp/mysql3306.sock
pid_file = /data/mysql3306/mysql3306.pid
tmpdir = /tmp/
default_authentication_plugin = mysql_native_password
skip_name_resolve = 1
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci
max_connections = 2000
max_connect_errors = 10000
interactive_timeout = 600
wait_timeout = 600
table_open_cache = 2048
thread_cache_size = 51
max_allowed_packet = 16M
tmp_table_size = 256M
max_heap_table_size = 96M
sort_buffer_size = 4M
read_buffer_size = 4M #??IO
join_buffer_size = 4M
read_rnd_buffer_size = 64M #??簍虜
bulk_insert_buffer_size = 64M #
log_error = /data/mysql3306/logs/mysql-error.log
log_timestamps = system
slow_query_log = 1
slow_query_log_file = /data/mysql3306/logs/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes =10
log_slow_admin_statements = 1
log_slow_slave_statements = 1
min_examined_row_limit = 100
log_queries_not_using_indexes = 1
log_output = FILE
relay_log = /data/mysql3306/mysql3306/mysql-relay
#binlog
server_id = 1783306
log_bin = /data/mysql3306/mysql3306/mysql-bin
binlog_expire_logs_seconds =604800
binlog_format = row
max_binlog_size = 1024M
max_binlog_cache_size = 8G
binlog_cache_size = 4M
sync_binlog = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = ON
log_slave_updates = 1
#innodb
default_storage_engine = InnoDB
innodb_page_size = 16384
innodb_data_home_dir = /data/mysql3306/mysql3306/
innodb_data_file_path = ibdata1:512M:autoextend
innodb_log_group_home_dir = /data/mysql3306/mysql3306
innodb_buffer_pool_instances = 1
innodb_buffer_pool_size = 1024M
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 8
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 32M #default 16M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 30
innodb_strict_mode = 1
innodb_print_all_deadlocks = 1
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_dump_pct = 40
innodb_open_files = 65536
innodb_file_per_table = 1
innodb_lock_wait_timeout = 30
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_io_capacity = 200
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_purge_threads = 4
innodb_max_dirty_pages_pct = 75
transaction_isolation = READ-COMMITTED
innodb_page_cleaners = 16
explicit_defaults_for_timestamp = 1
#undo
innodb_undo_directory = /data/mysql3306/mysql3306/
innodb_undo_tablespaces = 3
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 1000M
innodb_purge_rseg_truncate_frequency = 128
#rep
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
binlog_checksum=NONE
slave_preserve_commit_order=1
#group_replication_flow_control_mode=DISABLED
log_slave_updates=1
#GR
enforce_gtid_consistency=1
gtid_mode = on
transaction_write_set_extraction =XXHASH64 # off
loose-group_replication_group_name ="a876d35e-9110-11e6-a365-882b2b5s09d6" #可在庫裡使用SELECT UUID()生成，一個組的所有機器都相同。
loose-group_replication_local_address ="192.168.1.78:24900" #本機IP
loose-group_replication_group_seeds ="192.168.1.78:24900,192.168.1.79:24900,192.168.1.80:24900"
loose-group_replication_bootstrap_group =off # off
loose-group_replication_start_on_boot =off #非常重要
[mysqldump]
max_allowed_packet = 16M
[mysqldump]
max_allowed_packet = 16M
-----------------------------------------------------------------------
初始化
/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure --basedir=/usr/local/mysql --datadir=/data/mysql3306/mysql3306/ --explicit_defaults_for_timestamp --user=mysql
22個檔案。
啟動
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --ledir=/usr/local/mysql/bin --datadir=/data/mysql3306/mysql3306 --user=mysql &
echo "/usr/local/mysql8/bin/mysqld_safe --defaults-file=/etc/my.cnf --ledir=/usr/local/mysql8/bin --datadir=/data/mysql3306/mysql3306 --user=mysql &">>/etc/rc.local
登入：
mysql
alter user root@localhost identified by '123456';
create user root@'192.168.1.%' identified by '123456';
grant all on *.* to root@'192.168.1.%';
CREATE USER rep@'%' IDENTIFIED BY 'rep';
GRANT REPLICATION SLAVE ON *.* TO rep@'%';
flush privileges;
儲存快照(before MGR-Multi)，然後複製兩臺，做以下幾處修改：
(1)修改虛擬機器IP分別為192.168.1.79和192.168.1.80，並修改各自的主機名hostname與hosts檔案對應;
(2)然後修改各自的my.cnf裡的server_id和loose-group_replication_local_address改為本機IP;
(3)進入/data/mysql3306/mysql3306裡刪除auto.cnf檔案。
然後各自儲存快照(before MGR-Multi);
二、多主搭建
1、所有節點上安裝元件：
install plugin group_replication soname 'group_replication.so';
show plugins;
2、在192.168.1.78上：(只需要在這個上執行，跟單主模式相同)
CHANGE MASTER TO MASTER_USER='rep',MASTER_PASSWORD='rep' FOR CHANNEL 'group_replication_recovery';
set persist group_replication_single_primary_mode=off; --單主的都是ON
臨時開啟bootstrap：
SET GLOBAL group_replication_bootstrap_group=ON;
start group_replication;
SET GLOBAL group_replication_bootstrap_group=OFF;
select * from performance_schema.replication_group_members;
3、等上述操作完成之後，在192.168.1.79和192.168.1.80依次執行：
CHANGE MASTER TO MASTER_USER='rep',MASTER_PASSWORD='rep' FOR CHANNEL 'group_replication_recovery';
set persist group_replication_single_primary_mode=off;
START GROUP_REPLICATION;
4、驗證：
任意庫執行：select * from performance_schema.replication_group_members;
select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 5651f374-97c8-11e8-9966-000c295ce53e | mgr-mul1 | 3306 | ONLINE | PRIMARY | 8.0.11 |
| group_replication_applier | 749eb449-97e1-11e8-ab87-000c29ee3a4b | mgr-mul3 | 3306 | ONLINE | PRIMARY | 8.0.11 |
| group_replication_applier | 7753edfa-97e1-11e8-a10c-000c296952a6 | mgr-mul2 | 3306 | ONLINE | PRIMARY | 8.0.11 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
可以看到三臺都線上、各自主機名正確，並且都是primary則表示MGR多主配置成功。
select * from performance_schema.replication_connection_status\G，檢視是否有報錯;
例如：select * from performance_schema.replication_connection_status\G;
*************************** 1. 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. row ***************************
CHANNEL_NAME: group_replication_applier
GROUP_NAME: 8ef01324-97ce-11e8-b4cc-000c295ce53e
SOURCE_UUID: 8ef01324-97ce-11e8-b4cc-000c295ce53e
THREAD_ID: NULL
SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 0
LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00.000000
RECEIVED_TRANSACTION_SET: 8ef01324-97ce-11e8-b4cc-000c295ce53e:1-7:1000006:2000006
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION: 8ef01324-97ce-11e8-b4cc-000c295ce53e:2000006
LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2018-08-08 09:30:19.410114
LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 2018-08-08 09:30:19.412069
LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 2018-08-08 09:30:19.412081
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
三臺機器都是group_replication_applier的狀態為ON，group_replication_recovery的狀態為OFF。
5、注意：#############################################################################
如果把三臺全都關閉了再重啟多主MGR的時候，要再使用bootstrap;
#############################################################################
三、測試
任意庫建庫建表，其他庫均可同步。
四、問題
感覺MGR多主似乎不太穩定，經常會出現某個庫掉線的情況又不會自動拉起來;用迴圈指令碼測試一旦出現較高的併發，就會掉節點，不論是否用代理測試都掉節點，好像不太實用，也可能是因為我配置的什麼地方引數有問題，希望有高人給指點一下。
拉起來節點只需要依次執行stop group_replication和start group_replication即可。
轉自：https://blog.csdn.net/zhangwenjiang001/article/details/81503576
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9522838/viewspace-2641750/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MGR(MySQL Group Replication)部署搭建測試MySql
- mysql8.0以後的版本，進行多主一從的叢集環境搭建MySql
- 單機MGR搭建
- MySQL MGR 叢集搭建MySql
- mysql8.0.31 mgr搭建MySql
- MGR測試過程中出現的問題彙總
- 使用MySQL8.0 clone技術線上搭建主從複製MySql
- MySQL8.0主從複製MySql
- raid概述與CentOS7.4中raid5的搭建與測試AICentOS
- dbdeployer 快速安裝MySQL8.0各測試環境MySql
- MySQL MGR 叢集搭建（單主模式）MySql模式
- MySQL8.0輕鬆搞定GTID主主複製MySql
- Redis5.0 主從模式和高可用 搭建和測試報告Redis模式測試報告
- 關於測試平臺的搭建 (我們要不要搭建測試平臺)
- python進階（15）多執行緒與多程式效率測試Python執行緒
- 小白測試系列:介面測試與效能測試的區別
- MySQL MGR單主模式詳細搭建過程MySql模式
- mysql MGR搭建和模擬切換演練MySql
- 測試筆試多選題筆試
- MySQL8.0 高可用叢集化 · mysql-shell · mysql-router · docker · 單主多從MySqlDocker
- android測試環境搭建Android
- 『與善仁』Appium基礎 — 14、Appium測試環境搭建APP
- 『與善仁』Appium基礎 — 1、Android系統的測試環境搭建APPAndroid
- 軟體測試培訓：如何搭建測試環境
- web測試與手機app測試的異同WebAPP
- MySQL主從複製架構轉換MGR架構MySql架構
- APP 多語言測試APP
- MySQL8.0輕鬆搞定GTID主從複製MySql
- Web 滲透測試入門：環境搭建、流程與實踐Web
- 新手搭建 kubernetes 測試環境
- kaldi環境搭建 | yesno 測試
- selenium-測試框架搭建(十三)框架
- redis哨兵模式搭建和測試Redis模式
- 介面自動化測試框架搭建的思路框架
- 黑盒測試、白盒測試與灰盒測試方法
- 探索性測試的分類與測試用例
- Apifox介面測試教程（一）介面測試的原理與工具API
- 移動應用的測試策略與測試架構架構