# 1. 安裝MySQL YUM源
wget https://dev.mysql.com/get/mysql80-community-release-el7-7.noarch.rpm
rpm -ivh mysql80-community-release-el7-7.noarch.rpm
# 2. 安裝MySQL服務(wù)器（忽略GPG檢查，避免版本兼容問(wèn)題）
yum install -y mysql-community-server --nogpgcheck
# 3. 啟動(dòng)MySQL并設(shè)置開(kāi)機(jī)自啟
systemctl start mysqld
systemctl enable mysqld
# 4. 獲取初始密碼
initial_pass=$(grep 'temporary password' /var/log/mysqld.log | awk '{print $NF}')
echo "初始密碼：$initial_pass"

# 1. 登錄MySQL并修改root密碼（需符合復(fù)雜度：大小寫(xiě)+數(shù)字+特殊字符）
mysql -u root -p"$initial_pass" -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'MySQL@123456'; FLUSH PRIVILEGES;"
# 2. 遠(yuǎn)程訪問(wèn)授權(quán)（生產(chǎn)環(huán)境建議限制IP，此處為測(cè)試放開(kāi)）
mysql -u root -p"MySQL@123456" -e "CREATE USER 'root'@'%' IDENTIFIED BY 'MySQL@123456'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES;"

[mysqld]
# 基礎(chǔ)配置
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
lower_case_table_names=1
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
# MGR核心配置
server-id=30  # 每個(gè)節(jié)點(diǎn)唯一，建議用IP最后一段
gtid_mode=ON  # 開(kāi)啟GTID，MGR必需
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
binlog_format=ROW
transaction_write_set_extraction=XXHASH64  # MGR用于檢測(cè)沖突的哈希算法
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"  # 固定UUID，所有節(jié)點(diǎn)一致
loose-group_replication_start_on_boot=OFF  # 不隨MySQL啟動(dòng)自動(dòng)啟動(dòng)MGR
loose-group_replication_local_address="192.168.1.30:33061"  # 當(dāng)前節(jié)點(diǎn)MGR通信地址
loose-group_replication_group_seeds="192.168.1.30:33061,192.168.1.31:33061,192.168.1.32:33061"  # 所有節(jié)點(diǎn)通信地址
loose-group_replication_bootstrap_group=OFF  # 僅初始化集群時(shí)設(shè)為ON
loose-group_replication_single_primary_mode=ON  # 單主模式（核心：自動(dòng)故障切換依賴(lài)此模式）
loose-group_replication_enforce_update_everywhere_checks=OFF  # 單主模式設(shè)為OFF
loose-group_replication_auto_increment_increment=7  # 避免主鍵沖突

server-id=31
loose-group_replication_local_address="192.168.1.31:33061"

server-id=32
loose-group_replication_local_address="192.168.1.32:33061"

# 重啟MySQL
systemctl restart mysqld
# 驗(yàn)證GTID是否開(kāi)啟（輸出ON即正常）
mysql -u root -p"MySQL@123456" -e "SHOW VARIABLES LIKE 'gtid_mode';"

mysql -u root -p"MySQL@123456" << EOF
# 創(chuàng)建MGR通信用戶
CREATE USER 'mgr_repl'@'%' IDENTIFIED BY 'MGR@123456';
# 授予MGR所需權(quán)限
GRANT REPLICATION SLAVE ON *.* TO 'mgr_repl'@'%';
GRANT GROUP_REPLICATION_ADMIN ON *.* TO 'mgr_repl'@'%';
FLUSH PRIVILEGES;
# 配置MGR插件使用該用戶
CHANGE MASTER TO MASTER_USER='mgr_repl', MASTER_PASSWORD='MGR@123456' FOR CHANNEL 'group_replication_recovery';
EOF

mysql -u root -p"MySQL@123456" << EOF
# 加載MGR插件（8.0+默認(rèn)內(nèi)置，無(wú)需手動(dòng)安裝）
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
# 啟動(dòng)集群引導(dǎo)（僅第一次執(zhí)行）
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
# 驗(yàn)證集群狀態(tài)（輸出ON即正常）
SELECT * FROM performance_schema.replication_group_members;
EOF

mysql -u root -p"MySQL@123456" << EOF
# 加載MGR插件
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
# 加入集群
START GROUP_REPLICATION;
# 驗(yàn)證節(jié)點(diǎn)狀態(tài)
SELECT * FROM performance_schema.replication_group_members;
EOF

mysql -u root -p"MySQL@123456" -e "SELECT MEMBER_HOST, MEMBER_ROLE, MEMBER_STATE FROM performance_schema.replication_group_members;"

# 節(jié)點(diǎn)1執(zhí)行：創(chuàng)建測(cè)試庫(kù)和表
mysql -u root -p"MySQL@123456" << EOF
CREATE DATABASE IF NOT EXISTS mgr_test;
USE mgr_test;
CREATE TABLE user (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50));
INSERT INTO user (name) VALUES ('MGR測(cè)試用戶1'), ('MGR測(cè)試用戶2');
EOF
# 節(jié)點(diǎn)2/3執(zhí)行：查詢(xún)數(shù)據(jù)（應(yīng)同步成功）
mysql -u root -p"MySQL@123456" -e "SELECT * FROM mgr_test.user;"

# 節(jié)點(diǎn)1執(zhí)行：停止MySQL服務(wù)，模擬故障
systemctl stop mysqld

mysql -u root -p"MySQL@123456" -e "SELECT MEMBER_HOST, MEMBER_ROLE, MEMBER_STATE FROM performance_schema.replication_group_members;"

# 節(jié)點(diǎn)2執(zhí)行：插入數(shù)據(jù)
mysql -u root -p"MySQL@123456" << EOF
USE mgr_test;
INSERT INTO user (name) VALUES ('故障切換后寫(xiě)入');
EOF
# 節(jié)點(diǎn)3執(zhí)行：查詢(xún)數(shù)據(jù)（應(yīng)同步新數(shù)據(jù)）
mysql -u root -p"MySQL@123456" -e "SELECT * FROM mgr_test.user;"

# 節(jié)點(diǎn)1執(zhí)行：重啟MySQL
systemctl start mysqld
# 重新加入集群
mysql -u root -p"MySQL@123456" << EOF
START GROUP_REPLICATION;
# 驗(yàn)證狀態(tài)（此時(shí)節(jié)點(diǎn)1為SECONDARY）
SELECT MEMBER_HOST, MEMBER_ROLE FROM performance_schema.replication_group_members;
EOF

loose-group_replication_start_on_boot=ON

# 1. 集群成員狀態(tài)
SELECT MEMBER_HOST, MEMBER_ROLE, MEMBER_STATE FROM performance_schema.replication_group_members;
# 2. MGR運(yùn)行狀態(tài)
SHOW STATUS LIKE 'group_replication%';
# 3. 事務(wù)沖突（生產(chǎn)環(huán)境需監(jiān)控）
SELECT * FROM performance_schema.replication_group_conflicts;