MySQL
适用环境：RHEL/OEL7 Linux
1. 硬件及系统要求
1.1 硬件要求
- CPU: 至少8核
- 内存: 至少16GB
- 磁盘: 至少500GB SSD
- 网络: 千兆网卡，低延迟网络环境
1.2 系统要求
- 操作系统: RHEL/OEL 7.x
- 内核版本: 3.10.0-1160.el7.x86_64或更高
- 文件系统: ext4或xfs
- 关闭SELinux或设置为permissive模式
- 关闭防火墙或开放必要端口
2. 环境准备
2.1 系统更新
yum update -y2.2 安装必要依赖
yum install -y wget curl telnet net-tools perl2.3 关闭防火墙和SELinux
systemctl stop firewalld
systemctl disable firewalld
setenforce 0
sed -i 's/SELINUX=enforcing/SELINUX=permissive/g' /etc/selinux/config2.4 配置主机名和hosts文件
# 节点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
EOF3. MySQL 8.0 安装
3.1 下载MySQL 8.0 RPM包
cd /tmp
wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm3.2 安装MySQL仓库
rpm -ivh mysql80-community-release-el7-3.noarch.rpm3.3 安装MySQL 8.0
yum install -y mysql-community-server3.4 启动MySQL服务
systemctl start mysqld
systemctl enable mysqld3.5 获取初始密码
grep 'temporary password' /var/log/mysqld.log3.6 安全配置MySQL
mysql_secure_installation
提示：按照提示设置新密码，移除匿名用户，禁用远程root登录，移除test数据库，重新加载权限表。
4. 组复制配置
4.1 编辑MySQL配置文件
vi /etc/my.cnf4.2 节点1配置
[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
# 组复制配置
server-id=1
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
# 组复制特定配置
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="ce9b909f-2271-11e6-b8f4-00212844f856"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="192.168.1.100:33061"
loose-group_replication_group_seeds="192.168.1.100:33061,192.168.1.101:33061,192.168.1.102:33061"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="192.168.1.0/24"
loose-group_replication_enforce_update_everywhere_checks=ON
loose-group_replication_single_primary_mode=OFF
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid4.3 节点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
# 组复制配置
server-id=2
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
# 组复制特定配置
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="ce9b909f-2271-11e6-b8f4-00212844f856"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="192.168.1.101:33061"
loose-group_replication_group_seeds="192.168.1.100:33061,192.168.1.101:33061,192.168.1.102:33061"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="192.168.1.0/24"
loose-group_replication_enforce_update_everywhere_checks=ON
loose-group_replication_single_primary_mode=OFF
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid4.4 节点3配置
[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
# 组复制配置
server-id=3
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
# 组复制特定配置
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="ce9b909f-2271-11e6-b8f4-00212844f856"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="192.168.1.102:33061"
loose-group_replication_group_seeds="192.168.1.100:33061,192.168.1.101:33061,192.168.1.102:33061"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="192.168.1.0/24"
loose-group_replication_enforce_update_everywhere_checks=ON
loose-group_replication_single_primary_mode=OFF
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid4.5 重启所有节点的MySQL服务
systemctl restart mysqld5. 组复制设置
5.1 在所有节点上安装组复制插件
mysql -u root -p
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
SHOW PLUGINS LIKE 'group_replication';
5.2 在所有节点上创建复制用户
mysql -u root -p
SET SQL_LOG_BIN=0;
CREATE USER 'repl'@'%' IDENTIFIED BY 'Repl123!@#';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='Repl123!@#' FOR CHANNEL 'group_replication_recovery';
5.3 引导组复制（仅在第一个节点执行）
mysql -u root -p
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
# 检查组状态
SELECT * FROM performance_schema.replication_group_members;
5.4 将其他节点加入组
mysql -u root -p
START GROUP_REPLICATION;
# 检查组状态
SELECT * FROM performance_schema.replication_group_members;
6. 复制验证
6.1 检查组状态
mysql -u root -p
SELECT * FROM performance_schema.replication_group_members;
SELECT * FROM performance_schema.replication_group_member_stats;
6.2 测试数据同步
# 在节点1上创建测试数据库
mysql -u root -p
CREATE DATABASE test_mgr;
USE test_mgr;
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');
# 在节点2和节点3上验证数据
mysql -u root -p
SHOW DATABASES;
USE test_mgr;
SELECT * FROM test_table;
6.3 测试故障转移
# 模拟节点1故障
systemctl stop mysqld
# 在节点2上检查组状态
mysql -u root -p
SELECT * FROM performance_schema.replication_group_members;
# 在节点2上写入数据
USE test_mgr;
INSERT INTO test_table (name) VALUES ('Test 4');
# 恢复节点1
systemctl start mysqld
# 在节点1上启动组复制
mysql -u root -p
START GROUP_REPLICATION;
# 检查数据是否同步
USE test_mgr;
SELECT * FROM test_table;
7. 性能优化
7.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 -p7.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
# 组复制参数
loose-group_replication_flow_control_mode = "DISABLED"
loose-group_replication_consistency = "BEFORE_ON_PRIMARY_FAILOVER"
systemctl restart mysqld8. 故障排查
8.1 常见问题及解决方法
8.1.1 组复制启动失败
# 检查错误日志
cat /var/log/mysqld.log | grep "group_replication"
# 检查GTID模式
mysql -u root -p
SHOW VARIABLES LIKE 'gtid_mode';
SHOW VARIABLES LIKE 'enforce_gtid_consistency';
# 重置组复制
STOP GROUP_REPLICATION;
RESET MASTER;
START GROUP_REPLICATION;
8.1.2 节点无法加入组
# 检查网络连接
telnet mysql-node1 33061
# 检查防火墙设置
iptables -L
# 检查组复制配置
SHOW VARIABLES LIKE 'group_replication%';
# 重置复制并重新加入
STOP GROUP_REPLICATION;
RESET SLAVE ALL FOR CHANNEL 'group_replication_recovery';
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='Repl123!@#' FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;
8.1.3 数据一致性问题
# 检查组状态
SELECT * FROM performance_schema.replication_group_members;
SELECT * FROM performance_schema.replication_group_member_stats;
# 检查GTID执行情况
SHOW MASTER STATUS;
SHOW SLAVE STATUS FOR CHANNEL 'group_replication_recovery';
# 重新同步数据
STOP GROUP_REPLICATION;
RESET MASTER;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='Repl123!@#', MASTER_AUTO_POSITION=1 FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;
警告：本指南仅供参考，实际部署时请根据具体环境进行调整。在生产环境中，建议进行充分的测试和监控。
提示：定期监控组复制状态，确保所有节点正常运行，数据一致性良好。
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
