yum update -y

yum install -y wget curl telnet net-tools perl python3

systemctl stop firewalld
systemctl disable firewalld

setenforce 0
sed -i 's/SELINUX=enforcing/SELINUX=permissive/g' /etc/selinux/config

# 节点1
echo "mysql-node1" > /etc/hostname
hostnamectl set-hostname mysql-node1

# 节点2
echo "mysql-node2" > /etc/hostname
hostnamectl set-hostname mysql-node2

# 节点3
echo "mysql-node3" > /etc/hostname
hostnamectl set-hostname mysql-node3

# 在所有节点上都添加hosts条目
cat >> /etc/hosts << EOF
192.168.1.100 mysql-node1
192.168.1.101 mysql-node2
192.168.1.102 mysql-node3
192.168.1.103 mysql-router  # Router节点
EOF

cd /tmp
wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

rpm -ivh mysql80-community-release-el7-3.noarch.rpm

yum install -y mysql-community-server

systemctl start mysqld
systemctl enable mysqld

grep 'temporary password' /var/log/mysqld.log

mysql_secure_installation

提示：按照提示设置新密码，移除匿名用户，禁用远程root登录，移除test数据库，重新加载权限表。

4. MySQL Shell 安装

4.1 下载MySQL Shell

cd /tmp
wget https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell-8.0.28-1.el7.x86_64.rpm

4.2 安装MySQL Shell

rpm -ivh mysql-shell-8.0.28-1.el7.x86_64.rpm

5. 实例配置

5.1 编辑MySQL配置文件

vi /etc/my.cnf

5.2 配置所有节点

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# InnoDB Cluster配置
server-id=1  # 每个节点使用不同的server-id
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

# InnoDB配置
innodb_buffer_pool_size=8G
innodb_log_file_size=1G
innodb_flush_log_at_trx_commit=1
sync_binlog=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

5.3 重启所有节点的MySQL服务

systemctl restart mysqld

5.4 创建集群管理员用户

mysql -u root -p

SET SQL_LOG_BIN=0;
CREATE USER 'clusteradmin'@'%' IDENTIFIED BY 'ClusterAdmin123!@#';
GRANT ALL PRIVILEGES ON *.* TO 'clusteradmin'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

6. InnoDB Cluster 设置

6.1 启动MySQL Shell

mysqlsh

6.2 连接到第一个节点

mysql-js>
var cluster = dba.createCluster('myCluster');

6.3 添加第一个实例

mysql-js>
cluster.addInstance('clusteradmin@mysql-node1:3306');

6.4 添加其他实例

mysql-js>
cluster.addInstance('clusteradmin@mysql-node2:3306');
cluster.addInstance('clusteradmin@mysql-node3:3306');

6.5 检查集群状态

mysql-js>
cluster.status();

7. MySQL Router 安装配置

7.1 下载MySQL Router

cd /tmp
wget https://dev.mysql.com/get/Downloads/MySQL-Router/mysql-router-8.0.28-1.el7.x86_64.rpm

7.2 安装MySQL Router

rpm -ivh mysql-router-8.0.28-1.el7.x86_64.rpm

7.3 配置MySQL Router

mysqlrouter --bootstrap clusteradmin@mysql-node1:3306 --user=mysqlrouter

7.4 启动MySQL Router

systemctl start mysqlrouter
systemctl enable mysqlrouter

8. 集群验证

8.1 检查集群状态

mysqlsh
mysql-js>
var cluster = dba.getCluster('myCluster');
cluster.status();

8.2 测试数据同步

# 连接到Router
mysql -h mysql-router -P 6446 -u root -p

CREATE DATABASE test_innodb_cluster;
USE test_innodb_cluster;
CREATE TABLE test_table (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50));
INSERT INTO test_table (name) VALUES ('Test 1'), ('Test 2'), ('Test 3');

# 在各个节点上验证数据
mysql -h mysql-node1 -u root -p
USE test_innodb_cluster;
SELECT * FROM test_table;

mysql -h mysql-node2 -u root -p
USE test_innodb_cluster;
SELECT * FROM test_table;

mysql -h mysql-node3 -u root -p
USE test_innodb_cluster;
SELECT * FROM test_table;

8.3 测试故障转移

# 模拟主节点故障
systemctl stop mysqld@mysql-node1

# 检查集群状态
mysqlsh
mysql-js>
var cluster = dba.getCluster('myCluster');
cluster.status();

# 通过Router写入数据
mysql -h mysql-router -P 6446 -u root -p
USE test_innodb_cluster;
INSERT INTO test_table (name) VALUES ('Test 4');

# 恢复节点1
systemctl start mysqld@mysql-node1

# 检查集群状态
cluster.status();

# 验证数据同步
mysql -h mysql-node1 -u root -p
USE test_innodb_cluster;
SELECT * FROM test_table;

9. 性能优化

9.1 操作系统参数优化

vi /etc/sysctl.conf

# 网络参数
net.core.somaxconn = 65535
net.ipv4.tcp_max_syn_backlog = 65535
net.ipv4.tcp_fin_timeout = 30
net.ipv4.tcp_tw_reuse = 1

# 文件系统参数
fs.file-max = 65535

# 内存参数
vm.swappiness = 10
vm.overcommit_memory = 1

sysctl -p

9.2 MySQL参数优化

vi /etc/my.cnf

[mysqld]
# 基础参数
max_connections = 2000
max_connect_errors = 10000

# 缓存参数
key_buffer_size = 256M
innodb_buffer_pool_size = 8G
innodb_log_buffer_size = 32M

# 查询参数
query_cache_size = 64M
query_cache_type = 1

# 复制参数
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

# InnoDB Cluster参数
transaction_write_set_extraction=XXHASH64

systemctl restart mysqld

9.3 MySQL Router优化

vi /etc/mysqlrouter/mysqlrouter.conf

[DEFAULT]
logging_folder = /var/log/mysqlrouter
plugin_folder = /usr/lib64/mysqlrouter
runtime_folder = /var/run/mysqlrouter
config_folder = /etc/mysqlrouter

[logger]
level = INFO

[routing:primary]
bind_address = 0.0.0.0
bind_port = 6446
destination_cluster = myCluster
mode = read-write

[routing:secondary]
bind_address = 0.0.0.0
bind_port = 6447
destination_cluster = myCluster
mode = read-only

[metadata_cache:myCluster]
cluster_type = single-primary
router_id = 1
server_addresses = mysql-node1:3306,mysql-node2:3306,mysql-node3:3306
user = mysql_router1_b6f2a4e6b3b64b51a9f7e3d2c1b0a9f8
password = *A1B2C3D4E5F6G7H8I9J0
metadata_cluster = myCluster

systemctl restart mysqlrouter

10. 故障排查

10.1 常见问题及解决方法

10.1.1 集群创建失败

# 检查错误日志
cat /var/log/mysqld.log | grep "ERROR"

# 检查实例状态
mysqlsh
mysql-js>
var instance = dba.getLocalInstance('clusteradmin@localhost:3306');
instance.status();

# 重置实例
mysql-js>
dba.resetInstance('clusteradmin@localhost:3306');

10.1.2 节点无法加入集群

# 检查网络连接
telnet mysql-node1 3306

# 检查防火墙设置
iptables -L

# 检查实例配置
SHOW VARIABLES LIKE 'gtid_mode';
SHOW VARIABLES LIKE 'enforce_gtid_consistency';

# 重置实例并重新加入
mysqlsh
mysql-js>
dba.resetInstance('clusteradmin@mysql-node2:3306');
var cluster = dba.getCluster('myCluster');
cluster.addInstance('clusteradmin@mysql-node2:3306');

10.1.3 MySQL Router连接失败

# 检查Router日志
cat /var/log/mysqlrouter/mysqlrouter.log

# 检查Router状态
systemctl status mysqlrouter

# 重新引导Router
mysqlrouter --bootstrap clusteradmin@mysql-node1:3306 --user=mysqlrouter

10.1.4 集群状态异常

# 检查集群状态
mysqlsh
mysql-js>
var cluster = dba.getCluster('myCluster');
cluster.status();

# 修复集群
cluster.rejoinInstance('clusteradmin@mysql-node2:3306');

# 强制故障转移
cluster.forcePrimaryInstance('clusteradmin@mysql-node2:3306');