#!/bin/bash
# ========== MySQL 基礎(chǔ)安裝腳本 (All Nodes) ==========
# 1. 創(chuàng)建用戶和目錄
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
mkdir -p /data1/mysql/{data,logs,tmp}
chown -R mysql:mysql /data1/mysql

# 關(guān)閉SELINUX
sudo sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
sudo reboot


# 2. 安裝依賴（在線優(yōu)先，離線備用）
echo “正在安裝系統(tǒng)依賴...”
dnf install -y libaio numactl-libs openssl libtirpc ncurses-compat-libs 2>/dev/null || {
    echo “在線安裝失敗，嘗試使用離線依賴包...”
    rpm -ivh ./deps/*.rpm --nodeps 2>/dev/null || true
}

# 3. 解壓并安裝 MySQL
echo “正在安裝 MySQL...”
tar -xf /data1/mysql-8.0.44-linux-glibc2.28-x86_64.tar.xz
mv /data1/mysql-8.0.44-linux-glibc2.28-x86_64/* /data1/mysql/

# 4. 創(chuàng)建軟鏈接
ln -sf /data1/mysql/bin/mysql /usr/local/bin/mysql
ln -sf /data1/mysql/bin/mysqld /usr/local/bin/mysqld
ln -sf /data1/mysql/bin/mysqld_safe /usr/local/bin/mysqld_safe

# 5. 創(chuàng)建主配置文件 /etc/my.cnf
# 注意：初始時(shí) plugin-load 行被注釋，將在動(dòng)態(tài)配置后啟用。
cat > /etc/my.cnf << 'EOF'
[mysqld]
basedir=/data1/mysql
datadir=/data1/mysql/data
socket=/data1/mysql/mysql.sock
port=3306

character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
#default_authentication_plugin=mysql_native_password

max_connections=1000
max_connect_errors=100000
table_open_cache=2000
thread_cache_size=100
tmp_table_size=64M
max_heap_table_size=64M

log-error=/data1/mysql/logs/mysql-error.log
slow_query_log=1
slow_query_log_file=/data1/mysql/logs/mysql-slow.log
long_query_time=2
log-bin=/data1/mysql/logs/mysql-bin
binlog_format=ROW
binlog_expire_logs_seconds=604800

innodb_buffer_pool_size=1G
innodb_log_file_size=256M
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=1
innodb_file_per_table=1

server_id=1
report_host=192.168.65.137

gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
log_slave_updates=ON
binlog_row_image=FULL

#初始注釋，步驟3.2中啟用
#plugin-load="group_replication.so"
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

#ssl_ca=/etc/mysql/ca.pem
#ssl_cert=/etc/mysql/server-cert.pem
#ssl_key=/etc/mysql/server-key.pem
#require_secure_transport=ON

[mysql]
default-character-set=utf8mb4
socket=/data1/mysql/mysql.sock

[client]
socket=/data1/mysql/mysql.sock
EOF

# 6. 初始化 MySQL (使用 --initialize-insecure)
/data1/mysql/bin/mysqld --defaults-file=/etc/my.cnf \
  --initialize-insecure --user=mysql \
  --basedir=/data1/mysql --datadir=/data1/mysql/data

# 7. 配置并啟動(dòng) Systemd 服務(wù)
cat > /etc/systemd/system/mysqld.service << 'EOF'
[Unit]
Description=MySQL Server
After=network.target
[Service]
Type=simple
User=mysql
Group=mysql
ExecStart=/usr/local/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE=65535
Restart=on-failure
RestartSec=10
TimeoutSec=300
[Install]
WantedBy=multi-user.target
EOF

systemctl daemon-reload
systemctl start mysqld
systemctl enable mysqld

mysql -uroot << 'EOF'
-- 1. 安裝插件
INSTALL PLUGIN group_replication SONAME 'group_replication.so';

-- 2. 設(shè)置全局變量 (請(qǐng)?zhí)鎿QIP為當(dāng)前節(jié)點(diǎn)實(shí)際IP)
SET GLOBAL group_replication_group_name = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa';
SET GLOBAL group_replication_local_address = '192.168.65.137:33061';
SET GLOBAL group_replication_group_seeds = '192.168.65.137:33061,192.168.65.142:33061,192.168.65.143:33061';
SET GLOBAL group_replication_ip_allowlist = '10.0.1.0/24,127.0.0.1/32,localhost';
SET GLOBAL group_replication_bootstrap_group = OFF;
SET GLOBAL group_replication_single_primary_mode = ON;

-- 3. 使用 PERSIST 使配置持久化（重啟后生效）
SET PERSIST group_replication_group_name = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa';
SET PERSIST group_replication_local_address = '192.168.65.137:33061';
SET PERSIST group_replication_group_seeds = '192.168.65.137:33061,192.168.65.142:33061,192.168.65.143:33061';
SET PERSIST group_replication_ip_allowlist = '10.0.1.0/24,127.0.0.1/32,localhost';
EOF

# 1. 編輯配置文件，取消 plugin-load 行的注釋
sed -i 's/^#plugin-load=“group_replication.so”/plugin-load=“group_replication.so”/' /etc/my.cnf

# 2. 重啟MySQL服務(wù)
systemctl restart mysqld

# 1. 設(shè)置管理用戶和權(quán)限（同您提供的命令）
mysql -uroot << 'EOF'
ALTER USER 'root'@'localhost' IDENTIFIED BY 'mysql#password';
CREATE USER 'repl'@'%' IDENTIFIED BY 'mysql#password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
CREATE USER 'clusteradmin'@'%' IDENTIFIED BY 'mysql#password';
GRANT ALL PRIVILEGES ON *.* TO 'clusteradmin'@'%' WITH GRANT OPTION;
GRANT GROUP_REPLICATION_ADMIN ON *.* TO 'clusteradmin'@'%';
FLUSH PRIVILEGES;
EOF

#允許任何ip登錄
mysql -uroot -p'mysql#password' -h127.0.0.1 -P3306 -e "CREATE USER IF NOT EXISTS 'root'@'%' IDENTIFIED BY 'mysql#password';GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;FLUSH PRIVILEGES;"

# 2. 引導(dǎo)并啟動(dòng)組復(fù)制
mysql -uroot -pmysql#password << 'EOF'
SET GLOBAL group_replication_bootstrap_group=ON;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='mysql#password' FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
EOF

mysql -uroot -pmysql#password << 'EOF'
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='mysql#password' FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;
EOF

# 1. 開(kāi)放MySQL及組復(fù)制通信端口
firewall-cmd --permanent --add-port=3306/tcp
firewall-cmd --permanent --add-port=33061/tcp

# 2. （可選）在主節(jié)點(diǎn)開(kāi)放MySQL Router端口
# 判斷如果是主節(jié)點(diǎn)（例如通過(guò)IP判斷），則開(kāi)放Router端口
if [[ “$(hostname -I)“ =~ 192.168.65.137 ]]; then
    firewall-cmd --permanent --add-port=6446/tcp
    firewall-cmd --permanent --add-port=6447/tcp
fi

# 3. 重新加載配置
firewall-cmd --reload
# 4. 驗(yàn)證結(jié)果
firewall-cmd --list-ports

#!/bin/bash
# ========== MySQL Router 部署腳本 ==========
# 1. 創(chuàng)建系統(tǒng)用戶
groupadd mysqlrouter
useradd -r -g mysqlrouter -s /bin/false mysqlrouter

# 2. 解壓軟件
tar -xf mysql-router-8.0.37-linux-glibc2.17-x86_64.tar.gz -C /tmp
mv /tmp/mysql-router-8.0.37-linux-glibc2.17-x86_64 /data1/mysql-router

# 3. 創(chuàng)建軟鏈接
ln -sf /data1/mysql-router/bin/mysqlrouter /usr/bin/mysqlrouter

# 4. 創(chuàng)建目錄與配置文件
mkdir -p /etc/mysqlrouter
#引導(dǎo)生成配置文件
mysqlrouter --user=mysqlrouter --bootstrap root@192.168.65.137:3306 --directory /etc/mysqlrouter

# 5. 設(shè)置權(quán)限
chown -R mysqlrouter:mysqlrouter /etc/mysqlrouter /data1/mysql-router

# 6. 創(chuàng)建并啟動(dòng)系統(tǒng)服務(wù)
cat > /etc/systemd/system/mysqlrouter.service << 'EOF'
[Unit]
Description=MySQL Router
After=network.target mysqld.service
Wants=network.target mysqld.service
Documentation=man:mysqlrouter

[Service]
Type=forking
ExecStart=/etc/mysqlrouter/start.sh
WorkingDirectory=/etc/mysqlrouter
User=mysqlrouter
Group=mysqlrouter
PIDFile=/etc/mysqlrouter/mysqlrouter.pid
Environment="PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin"
Environment="ROUTER_PID=/etc/mysqlrouter/mysqlrouter.pid"

Restart=on-failure
RestartSec=5
StartLimitInterval=100
StartLimitBurst=10
TimeoutStartSec=300
TimeoutStopSec=30
KillSignal=SIGTERM
KillMode=mixed

[Install]
WantedBy=multi-user.target
EOF

systemctl daemon-reload && systemctl start mysqlrouter && systemctl status mysqlrouter

echo “MySQL Router 部署完成！”

# 1. 解壓安裝包
tar -xf mysql-shell-8.0.37-linux-glibc2.17-x86_64.tar.gz
mv mysql-shell-8.0.37-linux-glibc2.17-x86_64 /data1/mysql-shell

# 2. 創(chuàng)建軟鏈接
ln -sf /data1/mysql-shell/bin/mysqlsh /usr/local/bin/mysqlsh
ln -sf /data1/mysql-shell/bin/mysqlsh /usr/bin/mysqlsh

# 3. 驗(yàn)證安裝
mysqlsh --version

cat > /tmp/mysql_script.js << 'EOF'
try {
    print("連接到主節(jié)點(diǎn)...");
    shell.connect('clusteradmin:mysql%23password@192.168.65.137:3306');
    
    print("接管現(xiàn)有的 Group Replication 為 InnoDB Cluster...");
    var cluster = dba.createCluster('myCluster', {adoptFromGR: true});
    
    print("集群接管完成！");
    print("集群狀態(tài)：");
    cluster.status();
    
} catch(e) {
    print("錯(cuò)誤: " + e.message);
    print("詳細(xì)信息: " + JSON.stringify(e));
}
EOF


mysqlsh --js -f /tmp/mysql_script.js

cat > /tmp/check_node.js << 'EOF'
// 定義集群節(jié)點(diǎn)
var instances = [
    {host: '192.168.65.137', port: 3306, label: '節(jié)點(diǎn)1'},
    {host: '192.168.65.142', port: 3306, label: '節(jié)點(diǎn)2'},
    {host: '192.168.65.143', port: 3306, label: '節(jié)點(diǎn)3'}
];

var username = 'clusteradmin';
var password = 'mysql#password';

// 配置實(shí)例
print("\n配置實(shí)例...");

for (var i = 0; i < instances.length; i++) {
	var instance = instances[i];
	print("\n配置節(jié)點(diǎn) " + instance.label + " (" + instance.host + ":" + instance.port + ")...");
	
	var primarySession = shell.connect({
		host: instance.host,
		port: instance.port,
		user: username,
		password: password
	});
	
	if (!primarySession) {
		throw new Error("無(wú)法連接到節(jié)點(diǎn)");
	}
	print("? 成功連接到節(jié)點(diǎn)");
	
	print("\n2. 檢查節(jié)點(diǎn)配置...");
	var checkResult = dba.checkInstanceConfiguration({
		host: instance.host,
		port: instance.port,
		user: username,
		password: password
	});
	
	print("配置檢查結(jié)果狀態(tài): " + checkResult.status);
	
	if (checkResult.status === 'ok') {
		print("\n? 節(jié)點(diǎn)配置正常");
	} else if (checkResult.status === 'error') {
		print("\n? 節(jié)點(diǎn)配置有錯(cuò)誤，需要修復(fù)");
		print("\n錯(cuò)誤信息: " + checkResult.errors);
		print("\n正在自動(dòng)配置節(jié)點(diǎn)...");
		var configureResult = dba.configureInstance({
			host: instance.host,
			port: instance.port,
			user: username,
			password: password
		}, {restart: true});

		print("\n配置結(jié)果: " + JSON.stringify(configureResult, null, 2));
		
		if (configureResult.status === 'ok') {
			print("\n? 節(jié)點(diǎn)配置成功");
			// 重新連接
			primarySession = shell.connect({
				host: instance.host,
				port: instance.port,
				user: username,
				password: password
			});
		} else {
			throw new Error("節(jié)點(diǎn)配置失敗: " + configureResult.errors);
		}
	}
}
EOF

mysqlsh --js -f /tmp/check_node.js


cat > /tmp/create_cluster.js << 'EOF'
// 定義集群節(jié)點(diǎn)
var instances = [
    {host: '192.168.65.137', port: 3306, label: '節(jié)點(diǎn)1'},
    {host: '192.168.65.142', port: 3306, label: '節(jié)點(diǎn)2'},
    {host: '192.168.65.143', port: 3306, label: '節(jié)點(diǎn)3'}
];

var username = 'clusteradmin';
var password = 'mysql#password';

print("\n創(chuàng)建集群...");

var clusterName = 'myCluster';
print("\n正在創(chuàng)建集群: " + clusterName);

try {    
	print("連接到主節(jié)點(diǎn): " + instances[0].label);
    var primarySession = shell.connect({
        host: instances[0].host,
        port: instances[0].port,
        user: username,
        password: password,
        scheme: 'mysql'
    });
    
    if (!primarySession) {
        throw new Error("無(wú)法連接到主節(jié)點(diǎn)");
    }

    var cluster = dba.createCluster(clusterName);
    
    if (cluster) {
        print("? 集群創(chuàng)建成功");
        print("\n集群名稱: " + clusterName);
        print("\n主節(jié)點(diǎn): " + instances[0].host + ":" + instances[0].port);
        
        // 檢查集群狀態(tài)
        print("\n集群初始狀態(tài):");
        var clusterStatus = cluster.status({extended: 0});
        print(JSON.stringify(clusterStatus, null, 2));
        
    } else {
        throw new Error("集群創(chuàng)建失敗");
    }
    
    print("\n添加其他實(shí)例到集群...");
    
    for (var i = 1; i < instances.length; i++) {
        var instance = instances[i];
        print("\n添加 " + instance.label + " (" + instance.host + ":" + instance.port + ") 到集群...");
        
        try {
            // 嘗試添加實(shí)例
            var addResult = cluster.addInstance({
					host: instance.host,
					port: instance.port,
					user: username,
					password: password
				}, {
					recoveryMethod: 'clone'
				});
            
            print("\n  ? " + instance.label + " 添加成功");
            print("\n  恢復(fù)方法: clone");
        } catch (addErr) {
            print("\n  ? 添加 " + instance.label + " 失敗: " + addErr.message);
        }
    }
    
    print("\n集群最終狀態(tài):");
    
    var finalStatus = cluster.status({extended: 1});
    
    print("\n集群成員:");
    print(JSON.stringify(finalStatus, null, 2));
    
    print("\n設(shè)置集群選項(xiàng)...");

    try {
        cluster.setOption("exitStateAction", "OFFLINE_MODE");
	cluster.setOption("memberWeight", 50);
        cluster.setOption("consistency", "BEFORE_ON_PRIMARY_FAILOVER");
        cluster.setOption("ipAllowlist", "192.168.65.0/24,127.0.0.1");
        cluster.setOption("failoverConsistency", "EVENTUAL");
        
        print("? 集群選項(xiàng)設(shè)置完成");
    } catch (optionErr) {
        print("? 設(shè)置集群選項(xiàng)時(shí)出錯(cuò): " + optionErr.message);
    }
} catch (e) {
    print("\n? 錯(cuò)誤發(fā)生: " + e.message);
    print("錯(cuò)誤堆棧: " + e.stack);
    
    // 嘗試獲取更多錯(cuò)誤信息
    if (e.code) {
        print("錯(cuò)誤代碼: " + e.code);
    }
    
    // 檢查集群是否存在
    try {
        var existingCluster = dba.getCluster(clusterName);
        if (existingCluster) {
            print("\n? 集群已存在，當(dāng)前狀態(tài):");
            print(JSON.stringify(existingCluster.status({extended: 0}), null, 2));
        }
    } catch (clusterErr) {
        // 忽略
    }
}
EOF

mysqlsh --js -f /tmp/create_cluster.js


cat > /tmp/check_cluster.js << 'EOF'
// 定義集群節(jié)點(diǎn)
var instances = [
    {host: '192.168.65.137', port: 3306, label: '節(jié)點(diǎn)1'},
    {host: '192.168.65.142', port: 3306, label: '節(jié)點(diǎn)2'},
    {host: '192.168.65.143', port: 3306, label: '節(jié)點(diǎn)3'}
];

var username = 'clusteradmin';
var password = 'mysql#password';

var clusterName = 'myCluster';

try {    
    print("驗(yàn)證集群...\n");
    
    var clusterValid = dba.getCluster(clusterName);
    if (clusterValid) {
        print("? 集群驗(yàn)證成功\n");
        print("集群對(duì)象有效，可以使用以下命令管理:\n");
        print("  var cluster = dba.getCluster('" + clusterName + "');\n");
        print("  cluster.status();\n");
        print("  cluster.describe();\n");
    }
    
    print("\n=== MySQL InnoDB Cluster 創(chuàng)建完成 ===");
    print("\n管理命令:");
    print("1. 查看集群狀態(tài): cluster.status()\n");
    print("2. 查看集群描述: cluster.describe()\n");
    print("3. 切換主節(jié)點(diǎn): cluster.setPrimaryInstance('host:port')\n");
    print("4. 重新掃描集群: cluster.rescan()\n");
    print("5. 移除實(shí)例: cluster.removeInstance('host:port')\n");
} catch (e) {
    print("\n? 錯(cuò)誤發(fā)生: " + e.message);
    print("錯(cuò)誤堆棧: " + e.stack);
    
    // 嘗試獲取更多錯(cuò)誤信息
    if (e.code) {
        print("錯(cuò)誤代碼: " + e.code);
    }
    
    // 檢查集群是否存在
    try {
        var existingCluster = dba.getCluster(clusterName);
        if (existingCluster) {
            print("\n? 集群已存在，當(dāng)前狀態(tài):");
            print(JSON.stringify(existingCluster.status({extended: 0}), null, 2));
        }
    } catch (clusterErr) {
        // 忽略
    }
}
EOF


mysqlsh --js -f /tmp/check_cluster.js

# 1. 檢查集群狀態(tài)
mysql -uclusteradmin -p'mysql#password' -h192.168.65.137 -e “
SELECT member_host, member_port, member_state, member_role
FROM performance_schema.replication_group_members\G”

# 2. 測(cè)試Router連接
mysql -uclusteradmin -p'mysql#password' -h192.168.65.137 -P6446 -e “SELECT @@hostname;”
mysql -uclusteradmin -p'mysql#password' -h192.168.65.137 -P6447 -e “SELECT @@hostname;”

# 3.1 測(cè)試同步，主節(jié)點(diǎn)執(zhí)行
mysql -uclusteradmin -pmysql#password -h192.168.65.137 << EOF
CREATE DATABASE IF NOT EXISTS cluster_test;
USE cluster_test;
CREATE TABLE IF NOT EXISTS test_sync (
    id INT AUTO_INCREMENT PRIMARY KEY,
    node_name VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO test_sync (node_name) VALUES ('from_node1');
SELECT * FROM test_sync;
EOF

# 3.2 每個(gè)從節(jié)點(diǎn)看是否有數(shù)據(jù)
mysql -uclusteradmin -pmysql#password -h192.168.65.142 -e "SELECT * FROM cluster_test.test_sync;"
mysql -uclusteradmin -pmysql#password -h192.168.65.143 -e "SELECT * FROM cluster_test.test_sync;"

# 查看集群成員狀態(tài)
mysql -uroot -p'mysql#password' -e “SELECT * FROM performance_schema.replication_group_members;”
# 查看復(fù)制延遲
mysql -uroot -p'mysql#password' -e “SELECT * FROM performance_schema.replication_group_member_stats\G”
# 檢查錯(cuò)誤日志
tail -50 /data1/mysql/logs/mysql-error.log

# 模擬主節(jié)點(diǎn)故障
# 1. 停止主節(jié)點(diǎn)的MySQL服務(wù)
systemctl stop mysqld

# 2. 觀察集群自動(dòng)選舉新主節(jié)點(diǎn)
mysql -uroot -pmysql#password -h192.168.65.142 -e "SELECT * FROM performance_schema.replication_group_members;"

# 3. 通過(guò)Router測(cè)試連接
mysql -uroot -pmysql#password -h192.168.65.137 -P6446 -e "SELECT @@hostname;"

# 4. 恢復(fù)原主節(jié)點(diǎn)
systemctl start mysqld

error while loading shared libraries:
1. libaio.so.1: cannot open shared object file
2. libnuma.so.1: cannot open shared object file

# openEuler 23.03 系統(tǒng)
sudo dnf install libaio libaio-devel numactl numactl-devel openssl openssl-devel

# 安裝后運(yùn)行
sudo ldconfig

# 驗(yàn)證依賴庫(kù)
ldd /data1/mysql/bin/mysqld | grep -E "libaio|libnuma"

Process: 30526 ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf 
(code=exited, status=203/EXEC)

# 1. 檢查 mysqld 路徑是否正確
ls -la /data1/mysql/bin/mysqld

# 2. 修正 systemd 服務(wù)文件（已在上面配置中修正）
# 3. 設(shè)置正確權(quán)限
sudo chmod 755 /data1/mysql/bin/mysqld

# 4. 重新加載 systemd
sudo systemctl daemon-reload
sudo systemctl restart mysql

CHANNEL_NAME    MEMBER_ID       MEMBER_HOST     MEMBER_PORT     MEMBER_STATE    MEMBER_ROLE
group_replication_applier       113b5a00-d7fa-11f0-b01c-fa163e5fc9a1    192.168.65.137       3306    ONLINE  PRIMARY
group_replication_applier       11476a61-d7fa-11f0-8f8b-fa163ed8b49b    192.168.65.142      3306    RECOVERING      SECONDARY
group_replication_applier       115ee1ec-d7fa-11f0-9029-fa163e2d65c6    192.168.65.143      3306    RECOVERING      SECONDARY

# 1. 查看恢復(fù)進(jìn)度
mysql -h192.168.65.142 -uroot -pmysql#password -e "
SELECT 
    CHANNEL_NAME,
    SERVICE_STATE,
    REMAINING_DELAY,
    COUNT_TRANSACTIONS_RETRIES
FROM performance_schema.replication_connection_status
WHERE CHANNEL_NAME = 'group_replication_applier';
"

# 2. 查看恢復(fù)錯(cuò)誤（如果有）
mysql -h192.168.65.142 -uroot -pmysql#password -e "
SELECT 
    CHANNEL_NAME,
    LAST_ERROR_NUMBER,
    LAST_ERROR_MESSAGE,
    LAST_ERROR_TIMESTAMP
FROM performance_schema.replication_applier_status_by_worker
WHERE LAST_ERROR_NUMBER != 0;
"

# 3. 查看恢復(fù)線程狀態(tài)
mysql -h192.168.65.142 -uroot -pmysql#password -e "SHOW PROCESSLIST;" | grep -i recover

# 4. 如果長(zhǎng)時(shí)間處于 RECOVERING（超過(guò)30分鐘），嘗試：
mysql -h192.168.65.142 -uroot -pmysql#password << 'EOF'
STOP GROUP_REPLICATION;
START GROUP_REPLICATION;
EOF

[ERROR] [MY-011526] [Repl] Plugin group_replication reported: 
'This member has more executed transactions than those present in the group. 
Local transactions: 11476a61-d7fa-11f0-8f8b-fa163ed8b49b:1-11 > 
Group transactions: 113b5a00-d7fa-11f0-b01c-fa163e5fc9a1:1-11, aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1'
[ERROR] [MY-011522] [Repl] Plugin group_replication reported: 
'The member contains transactions not present in the group. The member will now exit the group.'

mysql -uroot -pmysql#password << 'EOF'
-- 1. 停止組復(fù)制
STOP GROUP_REPLICATION;

-- 2. 臨時(shí)禁用只讀模式
SET GLOBAL read_only = OFF;
SET GLOBAL super_read_only = OFF;

-- 3. 跳過(guò)問(wèn)題事務(wù)
SET GTID_NEXT='113b5a00-d7fa-11f0-b01c-fa163e5fc9a1:7';
BEGIN;
COMMIT;
SET GTID_NEXT='AUTOMATIC';

-- 4. 重新啟動(dòng)組復(fù)制（會(huì)自動(dòng)設(shè)置回只讀模式）
START GROUP_REPLICATION;

-- 5. 檢查狀態(tài)
SELECT * FROM performance_schema.replication_group_members;
EOF

[Warning] [MY-011735] [Repl] Plugin group_replication reported: 
'[GCS] Connection attempt from IP address ::ffff:192.168.65.142 refused. 
Address is not in the IP allowlist.'
[ERROR] [MY-011735] [Repl] Plugin group_replication reported: 
'[GCS] Error on opening a connection to peer node 192.168.65.143:33061 when joining a group. 
My local port is: 33061.'

# 在所有節(jié)點(diǎn)上執(zhí)行（需要持久化配置）
mysql -uroot -pmysql#password << 'EOF'
-- 查看當(dāng)前的IP白名單
SHOW VARIABLES LIKE 'group_replication_ip_allowlist';

-- 持久化添加IP地址到白名單（使用 SET PERSIST）
SET PERSIST group_replication_ip_allowlist = '10.0.1.0/24,127.0.0.1/32,localhost';

-- 或者允許所有IP（僅測(cè)試環(huán)境）
-- SET PERSIST group_replication_ip_allowlist = '0.0.0.0/0';

-- 驗(yàn)證持久化設(shè)置
SELECT * FROM performance_schema.persisted_variables 
WHERE VARIABLE_NAME = 'group_replication_ip_allowlist';

-- 重啟組復(fù)制使設(shè)置生效
STOP GROUP_REPLICATION;
START GROUP_REPLICATION;
EOF

# 驗(yàn)證配置是否已持久化
mysql -uroot -pmysql#password -e "SHOW VARIABLES LIKE 'group_replication_ip_allowlist';"

[ERROR] [MY-011735] [Repl] Plugin group_replication reported: 
'[GCS] There is no local IP address matching the one configured for the local node (192.168.65.137:33061).'

# 1. 查看服務(wù)器IP地址
ip addr show
hostname -I

# 2. 修改Group Replication本地地址配置（使用持久化設(shè)置）
mysql -uroot -pmysql#password << 'EOF'
-- 查看當(dāng)前配置
SHOW VARIABLES LIKE 'group_replication_local_address';

-- 持久化修改為正確的IP地址（使用實(shí)際IP）
SET PERSIST group_replication_local_address = '192.168.65.137:33061';

-- 檢查持久化變量
SELECT * FROM performance_schema.persisted_variables 
WHERE VARIABLE_NAME LIKE 'group_replication%';
EOF

# 3. 重啟MySQL服務(wù)使持久化配置生效
sudo systemctl restart mysqld

# 4. 重啟組復(fù)制
mysql -uroot -pmysql#password << 'EOF'
STOP GROUP_REPLICATION;
START GROUP_REPLICATION;
EOF

# 1. 檢查插件是否正確加載
mysql -uroot -pmysql#password -e "SHOW PLUGINS;" | grep group_replication

# 2. 檢查Group Replication是否已啟動(dòng)
mysql -uroot -pmysql#password -e "SHOW STATUS LIKE 'group_replication%';"

# 3. 如果沒(méi)有啟動(dòng)，配置并啟動(dòng)（使用持久化設(shè)置）
mysql -uroot -pmysql#password << 'EOF'
-- 持久化配置Group Replication參數(shù)
SET PERSIST group_replication_group_name = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa';
SET PERSIST group_replication_local_address = '192.168.65.137:33061';
SET PERSIST group_replication_group_seeds = '192.168.65.137:33061,192.168.65.142:33061,192.168.65.143:33061';
SET PERSIST group_replication_single_primary_mode = ON;
SET PERSIST group_replication_enforce_update_everywhere_checks = OFF;

-- 臨時(shí)設(shè)置引導(dǎo)模式
SET GLOBAL group_replication_bootstrap_group = ON;

-- 啟動(dòng)Group Replication（這會(huì)創(chuàng)建監(jiān)聽(tīng)端口）
START GROUP_REPLICATION;

-- 關(guān)閉引導(dǎo)模式
SET GLOBAL group_replication_bootstrap_group = OFF;
EOF

# 4. 驗(yàn)證端口監(jiān)聽(tīng)
netstat -tlnp | grep 33061
ss -tlnp | grep 33061

# 5. 檢查防火墻（openEuler）
sudo firewall-cmd --list-ports
sudo firewall-cmd --add-port=33061/tcp --permanent
sudo firewall-cmd --reload

The START GROUP_REPLICATION command failed as there was an error when initializing the group communication layer

# 1. 檢查關(guān)鍵配置
mysql -uroot -pmysql#password << 'EOF'
-- 檢查Group Replication配置
SELECT 
    VARIABLE_NAME, 
    VARIABLE_VALUE 
FROM performance_schema.global_variables 
WHERE VARIABLE_NAME LIKE 'group_replication%' 
ORDER BY VARIABLE_NAME;

-- 檢查插件狀態(tài)
SELECT PLUGIN_NAME, PLUGIN_STATUS 
FROM information_schema.PLUGINS 
WHERE PLUGIN_NAME LIKE '%group_replication%';
EOF

# 2. 清理并重新配置（使用持久化設(shè)置）
mysql -uroot -pmysql#password << 'EOF'
-- 停止組復(fù)制
STOP GROUP_REPLICATION;

-- 重置主節(jié)點(diǎn)
RESET MASTER;

-- 持久化重新配置
SET PERSIST group_replication_group_name = UUID();
SET PERSIST group_replication_local_address = '192.168.65.137:33061';
SET PERSIST group_replication_group_seeds = '192.168.65.137:33061,192.168.65.142:33061,192.168.65.143:33061';
SET PERSIST group_replication_ip_allowlist = '10.0.1.0/24,127.0.0.1/32';

-- 臨時(shí)設(shè)置引導(dǎo)模式
SET GLOBAL group_replication_bootstrap_group = ON;

-- 重新啟動(dòng)
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group = OFF;
EOF

# 3. 重啟MySQL服務(wù)
sudo systemctl restart mysqld

[ERROR] [MY-000067] [Server] unknown variable 'group_replication_group_name=aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa'.
[ERROR] [MY-013236] [Server] The designated data directory /data1/mysql/data/ is unusable.

# 1. 正確的初始化命令（不要在初始化時(shí)包含運(yùn)行時(shí)參數(shù)）
sudo -u mysql /data1/mysql/bin/mysqld --initialize \
  --user=mysql \
  --datadir=/data1/mysql/data \
  --basedir=/data1/mysql

# 2. 啟動(dòng)MySQL后，在配置文件中添加Group Replication配置
cat >> /etc/my.cnf << 'EOF'
# Group Replication 配置
plugin-load-add=group_replication.so
group_replication=FORCE_PLUS_PERMANENT
group_replication_start_on_boot=OFF
# 注意：這里只是設(shè)置默認(rèn)值，實(shí)際使用SET PERSIST
# group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
# group_replication_local_address="192.168.65.137:33061"
# group_replication_group_seeds="192.168.65.137:33061,192.168.65.142:33061,192.168.65.143:33061"
EOF

# 3. 啟動(dòng)MySQL
sudo systemctl restart mysql

# 4. 使用SET PERSIST動(dòng)態(tài)配置Group Replication參數(shù)
mysql -uroot -pmysql#password << 'EOF'
-- 安裝插件（如果未自動(dòng)加載）
INSTALL PLUGIN group_replication SONAME 'group_replication.so';

-- 使用SET PERSIST持久化配置
SET PERSIST group_replication_group_name = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa';
SET PERSIST group_replication_local_address = '192.168.65.137:33061';
SET PERSIST group_replication_group_seeds = '192.168.65.137:33061,192.168.65.142:33061,192.168.65.143:33061';
EOF

mysqld: Table 'mysql.plugin' doesn't exist

# 1. 重新初始化MySQL數(shù)據(jù)目錄（注意：會(huì)丟失所有數(shù)據(jù)?。?
sudo systemctl stop mysql
sudo rm -rf /data1/mysql/data/*
sudo -u mysql /data1/mysql/bin/mysqld --initialize \
  --user=mysql \
  --datadir=/data1/mysql/data \
  --basedir=/data1/mysql

# 2. 獲取初始密碼
sudo grep 'temporary password' /data1/mysql/data/error.log

# 3. 啟動(dòng)MySQL并修改密碼
sudo systemctl start mysql
# 用臨時(shí)密碼登錄并修改
mysql -uroot -p臨時(shí)密碼 -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'mysql#password';"

# 4. 持久化安裝和配置Group Replication插件
mysql -uroot -pmysql#password << 'EOF'
-- 安裝插件
INSTALL PLUGIN group_replication SONAME 'group_replication.so';

-- 驗(yàn)證插件
SELECT PLUGIN_NAME, PLUGIN_STATUS 
FROM INFORMATION_SCHEMA.PLUGINS 
WHERE PLUGIN_NAME = 'group_replication';

-- 持久化配置參數(shù)
SET PERSIST group_replication_group_name = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa';
SET PERSIST group_replication_local_address = '192.168.65.137:33061';
SET PERSIST group_replication_group_seeds = '192.168.65.137:33061,192.168.65.142:33061,192.168.65.143:33061';
SET PERSIST group_replication_bootstrap_group = OFF;
EOF

# 5. 驗(yàn)證33061端口
sleep 5
netstat -tlnp | grep 33061

Function 'group_replication' already exists

# 1. 檢查插件是否已安裝
mysql -uroot -pmysql#password -e "SHOW PLUGINS;" | grep group_replication

# 2. 如果已安裝，直接使用已安裝的插件繼續(xù)配置
mysql -uroot -pmysql#password << 'EOF'
-- 跳過(guò)安裝，直接配置
SET PERSIST group_replication_group_name = UUID();
SET PERSIST group_replication_local_address = '192.168.65.137:33061';
SET PERSIST group_replication_group_seeds = '192.168.65.137:33061,192.168.65.142:33061,192.168.65.143:33061';
SET PERSIST group_replication_ip_allowlist = '10.0.1.0/24,127.0.0.1/32';

-- 臨時(shí)設(shè)置引導(dǎo)模式
SET GLOBAL group_replication_bootstrap_group = ON;

-- 啟動(dòng)組復(fù)制
START GROUP_REPLICATION;

-- 關(guān)閉引導(dǎo)模式
SET GLOBAL group_replication_bootstrap_group = OFF;
EOF

# 3. 如果需要重新安裝（不推薦，除非插件損壞）
mysql -uroot -pmysql#password << 'EOF'
-- 卸載插件
UNINSTALL PLUGIN group_replication;

-- 等待幾秒
DO SLEEP(2);

-- 重新安裝
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
EOF

# 關(guān)鍵：使用 metadata_cache（下劃線），不是 metadata-cache
[metadata_cache:mycluster]
router_id=1
# 關(guān)鍵：添加 mysql:// 協(xié)議前綴
bootstrap_server_addresses=mysql://192.168.65.137:3306
user=root
password="mysql#password"
ttl=5

[routing:rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://mycluster/default?role=PRIMARY
routing_strategy=round-robin
protocol=classic

[routing:ro]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://mycluster/default?role=SECONDARY
routing_strategy=round-robin
protocol=classic

Error: Can't open file '/data1/mysql-router/mysqlrouter.key': No such file or directory

# 1. 創(chuàng)建目錄
sudo mkdir -p /data1/mysql-router/mysqlrouter
sudo chown -R mysqlrouter:mysqlrouter /data1/mysql-router

# 2. 創(chuàng)建 master key 文件
sudo -u mysqlrouter openssl rand -hex 32 > /data1/mysql-router/mysqlrouter/mysqlrouter.key
sudo chmod 600 /data1/mysql-router/mysqlrouter/mysqlrouter.key

# 3. 創(chuàng)建 keyring 文件
sudo -u mysqlrouter touch /data1/mysql-router/mysqlrouter/keyring
sudo chmod 600 /data1/mysql-router/mysqlrouter/keyring

# 4. 初始化 keyring（如果工具可用）
if command -v mysqlrouter_keyring > /dev/null; then
    sudo -u mysqlrouter mysqlrouter_keyring init \
        --master-key-file=/data1/mysql-router/mysqlrouter/mysqlrouter.key \
        /data1/mysql-router/mysqlrouter/keyring
fi

# 1. 檢查庫(kù)依賴
ldd /data1/mysql/bin/mysqld
# 2. 檢查 systemd 日志
sudo journalctl -u mysql -n 100 --no-pager
# 3. 檢查 MySQL 錯(cuò)誤日志
sudo tail -f /data1/mysql/data/error.log
# 4. 檢查集群狀態(tài)
mysql -h192.168.65.137 -uroot -pmysql#password -e "SELECT * FROM performance_schema.replication_group_members;"
# 5. 檢查 Router 狀態(tài)
sudo systemctl status mysqlrouter
# 6. 檢查持久化配置
mysql -uroot -pmysql#password -e "SELECT * FROM performance_schema.persisted_variables;"

應(yīng)用程序
    ↓
MySQL Router (負(fù)載均衡/故障轉(zhuǎn)移)
    ├── 讀寫端口: 6446 → 主節(jié)點(diǎn)
    └── 只讀端口: 6447 → 從節(jié)點(diǎn)
        ↓
Group Replication 集群 (3節(jié)點(diǎn))
    ├── 主節(jié)點(diǎn): 192.168.65.137
    ├── 從節(jié)點(diǎn): 192.168.65.142
    └── 從節(jié)點(diǎn): 192.168.65.143

# 啟動(dòng) PRIMARY 節(jié)點(diǎn)
mysql -h192.168.65.137 -P3306 -uclusteradmin -pmysql#password 2>/dev/null << 'SQL'
STOP GROUP_REPLICATION;
RESET MASTER;
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
SELECT MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, MEMBER_ROLE FROM performance_schema.replication_group_members;
SQL

# 去PRIMARY節(jié)點(diǎn)查詢集群信息
mysql -h192.168.65.137 -P3306 -uclusteradmin -pmysql#password 2>/dev/null << 'SQL'
SHOW VARIABLES LIKE 'group_replication_group_name';
SHOW VARIABLES LIKE 'group_replication_local_address';
SHOW VARIABLES LIKE 'group_replication_group_seeds';
SHOW VARIABLES LIKE 'group_replication_bootstrap_group';

# 節(jié)點(diǎn)信息
SELECT MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, MEMBER_ROLE FROM performance_schema.replication_group_members;
SQL

# 修正節(jié)點(diǎn)參數(shù)問(wèn)題
mysql -h192.168.65.137 -P3306 -uclusteradmin -pmysql#password 2>/dev/null << 'SQL'
STOP GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
SET GLOBAL group_replication_group_seeds='192.168.65.137:3306,192.168.65.142:3306,192.168.65.143:3306';
SET GLOBAL group_replication_local_address='192.168.65.137:3306';
START GROUP_REPLICATION;
SQL


# 啟動(dòng) SECONDARY 節(jié)點(diǎn)，需要用到集群信息
mysql -h192.168.65.142 -P3306 -uclusteradmin -pmysql#password 2>/dev/null << 'SQL'
STOP GROUP_REPLICATION;

RESET SLAVE ALL;
RESET MASTER;

SET GLOBAL group_replication_bootstrap_group=OFF;
SET GLOBAL group_replication_group_name='672ba05f-e7f9-11f0-8d2e-000c29e0b548';
SET GLOBAL group_replication_group_seeds='192.168.65.137:3306,192.168.65.142:3306,192.168.65.143:3306';
SET GLOBAL group_replication_local_address='192.168.65.142:3306';

CHANGE MASTER TO MASTER_USER='clusteradmin', MASTER_PASSWORD='mysql#password' FOR CHANNEL 'group_replication_recovery';

START GROUP_REPLICATION;
SELECT MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, MEMBER_ROLE FROM performance_schema.replication_group_members;
SQL


# 啟動(dòng) SECONDARY 節(jié)點(diǎn)，需要用到集群信息
mysql -h192.168.65.143 -P3306 -uclusteradmin -pmysql#password 2>/dev/null << 'SQL'
STOP GROUP_REPLICATION;

RESET SLAVE ALL;
RESET MASTER;

SET GLOBAL group_replication_bootstrap_group=OFF;
SET GLOBAL group_replication_group_name='672ba05f-e7f9-11f0-8d2e-000c29e0b548';
SET GLOBAL group_replication_group_seeds='192.168.65.137:3306,192.168.65.142:3306,192.168.65.143:3306';
SET GLOBAL group_replication_local_address='192.168.65.143:3306';

CHANGE MASTER TO MASTER_USER='clusteradmin', MASTER_PASSWORD='mysql#password' FOR CHANNEL 'group_replication_recovery';

START GROUP_REPLICATION;
SELECT MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, MEMBER_ROLE FROM performance_schema.replication_group_members;
SQL

# 下載 MySQL 8.0 Server
# 下載 MySQL Shell
# 下載 MySQL Router
# 下載依賴
dnf reinstall --downloadonly --downloaddir=./deps \
  libaio numactl-libs openssl libtirpc ncurses-compat-libs \
  libstdc++ libgcc pcre2 libedit numactl


# 1. 創(chuàng)建用戶和目錄
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
mkdir -p /data1/mysql
mkdir -p /data1/mysql/{data,logs,tmp}
chown -R mysql:mysql /data1/mysql

# 2. 檢查并安裝依賴
dnf install -y libaio numactl-libs openssl libtirpc ncurses-compat-libs 2>/dev/null || {
    echo "使用離線依賴包..."
    rpm -ivh ./deps/*.rpm --nodeps 2>/dev/null || true
}

# 3. 解壓并安裝 MySQL
tar -xf /data1/mysql-8.0.44-linux-glibc2.28-x86_64.tar.xz
mv /data1/mysql-8.0.44-linux-glibc2.28-x86_64/* /data1/mysql/

# 4. 創(chuàng)建軟鏈接
ln -sf /data1/mysql/bin/mysql /usr/local/bin/mysql
ln -sf /data1/mysql/bin/mysqld /usr/local/bin/mysqld
ln -sf /data1/mysql/bin/mysqld_safe /usr/local/bin/mysqld_safe

# 5. 創(chuàng)建配置文件
cat > /etc/my.cnf << EOF
[mysqld]
basedir=/data1/mysql
datadir=/data1/mysql/data
socket=/data1/mysql/mysql.sock
port=3306

character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
default_authentication_plugin=mysql_native_password

max_connections=1000
max_connect_errors=100000
table_open_cache=2000
thread_cache_size=100
tmp_table_size=64M
max_heap_table_size=64M

log-error=/data1/mysql/logs/mysql-error.log
slow_query_log=1
slow_query_log_file=/data1/mysql/logs/mysql-slow.log
long_query_time=2
log-bin=/data1/mysql/logs/mysql-bin
binlog_format=ROW
binlog_expire_logs_seconds=604800

innodb_buffer_pool_size=1G
innodb_log_file_size=256M
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=1
innodb_file_per_table=1

server_id=1
report_host=192.168.100.19

gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
binlog_row_image=FULL

# plugin-load="group_replication.so"

transaction_write_set_extraction=XXHASH64
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

# ssl_ca=/etc/mysql/ca.pem
# ssl_cert=/etc/mysql/server-cert.pem
# ssl_key=/etc/mysql/server-key.pem
# require_secure_transport=ON

[mysql]
default-character-set=utf8mb4
socket=/data1/mysql/mysql.sock

[client]
socket=/data1/mysql/mysql.sock
EOF

# 6. 初始化 MySQL
/data1/mysql/bin/mysqld --defaults-file=/etc/my.cnf \
  --initialize-insecure --user=mysql \
  --basedir=/data1/mysql --datadir=/data1/mysql/data

# 7. 創(chuàng)建 systemd 服務(wù)
cat > /etc/systemd/system/mysqld.service << 'EOF'
[Unit]
Description=MySQL Server
After=network.target

[Service]
Type=simple
User=mysql
Group=mysql
ExecStart=/usr/local/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE=65535
Restart=on-failure
RestartSec=10
TimeoutSec=300

[Install]
WantedBy=multi-user.target
EOF

# 8. 啟動(dòng)全部 MySQL
systemctl daemon-reload && systemctl start mysqld && systemctl enable mysqld && systemctl status mysqld

# 啟動(dòng)插件，每個(gè)節(jié)點(diǎn)的ip不一樣
mysql -uroot << EOF
INSTALL PLUGIN group_replication SONAME 'group_replication.so';

SET GLOBAL gtid_mode = 'ON';
SET GLOBAL enforce_gtid_consistency = 'ON';
SET GLOBAL master_info_repository = 'TABLE';
SET GLOBAL relay_log_info_repository = 'TABLE';
SET GLOBAL binlog_checksum = 'NONE';
SET GLOBAL binlog_format = 'ROW';

SET GLOBAL transaction_write_set_extraction = 'XXHASH64';
SET GLOBAL group_replication_group_name = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa';
SET GLOBAL group_replication_start_on_boot = 'OFF';
SET GLOBAL group_replication_local_address = '192.168.100.19:33061';
SET GLOBAL group_replication_ip_allowlist = '192.168.100.19,192.168.100.153,192.168.100.154,192.168.65.137,192.168.65.142,192.168.65.143,127.0.0.1,localhost';
SET GLOBAL group_replication_group_seeds = '192.168.100.19:33061,192.168.100.153:33061,192.168.100.154:33061';
SET GLOBAL group_replication_bootstrap_group = 'OFF';
SET GLOBAL group_replication_single_primary_mode = 'ON';
SET GLOBAL group_replication_enforce_update_everywhere_checks = 'OFF';

SET PERSIST gtid_mode = 'ON';
SET PERSIST enforce_gtid_consistency = 'ON';
SET PERSIST master_info_repository = 'TABLE';
SET PERSIST relay_log_info_repository = 'TABLE';
SET PERSIST binlog_checksum = 'NONE';
SET PERSIST binlog_format = 'ROW';

SET PERSIST transaction_write_set_extraction = 'XXHASH64';
SET PERSIST group_replication_group_name = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa';
SET PERSIST group_replication_start_on_boot = 'OFF';
SET PERSIST group_replication_local_address = '192.168.100.19:33061';
SET PERSIST group_replication_ip_allowlist = '192.168.100.19,192.168.100.153,192.168.100.154,192.168.65.137,192.168.65.142,192.168.65.143,127.0.0.1,localhost';
SET PERSIST group_replication_group_seeds = '192.168.65.137:33061,192.168.65.142:33061,192.168.65.143:33061';
SET PERSIST group_replication_bootstrap_group = 'OFF';
SET PERSIST group_replication_single_primary_mode = 'ON';
SET PERSIST group_replication_enforce_update_everywhere_checks = 'OFF';
EOF

設(shè)置完后，在my.cnf 開(kāi)啟 plugin-load="group_replication.so"，最后重啟服務(wù)

# 確認(rèn)修改
mysql -uroot << EOF
-- 檢查插件狀態(tài)
SHOW PLUGINS;

-- 檢查 Group Replication 變量
SHOW VARIABLES LIKE 'group_replication%';

-- 檢查端口監(jiān)聽(tīng)
SHOW VARIABLES LIKE 'port';
SHOW VARIABLES LIKE 'group_replication_local_address';
"


# 主節(jié)點(diǎn)上執(zhí)行
# 9. 設(shè)置 root 密碼
mysql -uroot << EOF
ALTER USER 'root'@'localhost' IDENTIFIED BY 'mysql#password';

CREATE USER 'root'@'%' IDENTIFIED BY 'mysql#password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

CREATE USER 'repl'@'%' IDENTIFIED BY 'mysql#password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
GRANT BACKUP_ADMIN ON *.* TO 'repl'@'%';

CREATE USER 'clusteradmin'@'%' IDENTIFIED BY 'mysql#password';
GRANT ALL PRIVILEGES ON *.* TO 'clusteradmin'@'%' WITH GRANT OPTION;
GRANT CLONE_ADMIN, SYSTEM_VARIABLES_ADMIN ON *.* TO 'clusteradmin'@'%';
GRANT GROUP_REPLICATION_ADMIN ON *.* TO 'clusteradmin'@'%';

FLUSH PRIVILEGES;
EOF

# 3. 配置Group Replication
mysql -uroot -pmysql#password << EOF
-- 設(shè)置恢復(fù)通道
SET GLOBAL group_replication_bootstrap_group=ON;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='mysql#password'
FOR CHANNEL 'group_replication_recovery';

-- 啟動(dòng)組復(fù)制
START GROUP_REPLICATION;

-- 關(guān)閉引導(dǎo)模式
SET GLOBAL group_replication_bootstrap_group=OFF;
EOF

# 4. 驗(yàn)證主節(jié)點(diǎn)狀態(tài)
mysql -uroot -pmysql#password << EOF
SELECT * FROM performance_schema.replication_group_members;
EOF

# 其他節(jié)點(diǎn)
# 2. 加入集群
mysql -uroot -pmysql#password << EOF
-- 設(shè)置恢復(fù)通道
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='mysql#password'
FOR CHANNEL 'group_replication_recovery';

-- 啟動(dòng)組復(fù)制
START GROUP_REPLICATION;
EOF

# 3. 驗(yàn)證狀態(tài)
mysql -uroot -pmysql#password << EOF
SELECT * FROM performance_schema.replication_group_members;
EOF


Group Replication（GR） 集群創(chuàng)建到這就行