CentOS Stream服务器MySQL配置实战指南从零开始构建稳定数据库环境

引言

CentOS Stream作为RHEL的上游开发平台，提供了稳定且更新的企业级操作系统环境，而MySQL作为世界上最流行的开源关系型数据库管理系统，在各类应用中扮演着关键角色。本指南将详细介绍如何在CentOS Stream服务器上从零开始安装、配置和优化MySQL，构建一个稳定、安全、高性能的数据库环境。

环境准备

系统要求

在开始安装MySQL之前，确保您的CentOS Stream服务器满足以下基本要求：

CPU：至少2核心
内存：至少4GB RAM（推荐8GB以上）
磁盘空间：至少20GB可用空间（根据数据量需求增加）
网络：稳定的网络连接

更新系统

首先，确保您的系统是最新的：

sudo dnf update -y sudo dnf upgrade -y

配置SELinux和防火墙

CentOS Stream默认启用SELinux，这是一个安全增强功能。我们需要确保它正确配置：

# 检查SELinux状态 sestatus # 如果需要，可以临时设置为宽松模式（不推荐生产环境） sudo setenforce 0 # 永久修改SELinux配置（谨慎操作） sudo sed -i 's/SELINUX=enforcing/SELINUX=permissive/g' /etc/selinux/config

配置防火墙以允许MySQL通信：

# 安装firewalld（如果未安装） sudo dnf install firewalld -y sudo systemctl start firewalld sudo systemctl enable firewalld # 开放MySQL默认端口3306 sudo firewall-cmd --permanent --add-port=3306/tcp sudo firewall-cmd --reload

创建必要的用户和目录

为了安全起见，我们创建一个专门用于管理MySQL的用户：

# 创建mysql用户（如果安装MySQL时未自动创建） sudo useradd -r -s /bin/false mysql # 创建数据目录（如果需要自定义位置） sudo mkdir -p /data/mysql sudo chown -R mysql:mysql /data/mysql sudo chmod -R 750 /data/mysql

MySQL安装

使用官方MySQL仓库安装

MySQL官方提供了适用于CentOS Stream的仓库，我们可以通过以下步骤添加并安装：

# 下载并安装MySQL官方仓库 sudo dnf install -y https://dev.mysql.com/get/mysql80-community-release-el9-1.noarch.rpm # 验证仓库是否已添加 sudo dnf repolist enabled | grep "mysql.*-community.*" # 安装MySQL服务器 sudo dnf install -y mysql-community-server

使用AppStream仓库安装

CentOS Stream的AppStream仓库也提供了MySQL，但版本可能不是最新的：

# 查看可用的MySQL模块 sudo dnf module list mysql # 安装默认版本的MySQL sudo dnf module enable mysql:8.0 -y sudo dnf install -y mysql-server

启动MySQL服务

安装完成后，启动MySQL服务并设置开机自启：

# 启动MySQL服务 sudo systemctl start mysqld # 设置MySQL开机自启 sudo systemctl enable mysqld # 检查MySQL服务状态 sudo systemctl status mysqld

初始配置

获取临时root密码

MySQL 8.0在首次安装时会为root用户生成一个临时密码，我们可以通过以下命令获取：

# 获取临时root密码 sudo grep 'temporary password' /var/log/mysqld.log

安全配置脚本

MySQL提供了安全配置脚本，帮助我们进行基本的安全设置：

# 运行安全配置脚本 sudo mysql_secure_installation

该脚本会引导您完成以下步骤：

输入获取的临时root密码
设置新的root密码
是否删除匿名用户（建议删除）
是否禁止root远程登录（根据需求选择）
是否删除测试数据库（建议删除）
是否重新加载权限表（选择是）

登录MySQL

使用新设置的密码登录MySQL：

# 使用root用户登录 mysql -u root -p

创建用户和数据库

登录后，我们可以创建新的数据库和用户：

-- 创建新数据库 CREATE DATABASE myapp_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 创建新用户并设置密码 CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'StrongPassword123!'; -- 授予用户对数据库的权限 GRANT ALL PRIVILEGES ON myapp_db.* TO 'myapp_user'@'localhost'; -- 刷新权限 FLUSH PRIVILEGES; -- 退出MySQL EXIT;

基本参数配置

编辑MySQL配置文件以优化基本设置：

# 备份原始配置文件 sudo cp /etc/my.cnf /etc/my.cnf.bak # 编辑配置文件 sudo vi /etc/my.cnf

在配置文件中添加或修改以下参数：

[mysqld] # 基本设置 character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci default-storage-engine = InnoDB # 日志设置 log-error = /var/log/mysqld.log pid-file = /var/run/mysqld/mysqld.pid # 网络设置 bind-address = 0.0.0.0 port = 3306 max_connections = 200 # 缓存设置 table_open_cache = 2000 table_definition_cache = 2000

保存文件后，重启MySQL服务使配置生效：

sudo systemctl restart mysqld

性能优化

内存配置

MySQL的内存配置是性能优化的关键，主要涉及以下参数：

[mysqld] # InnoDB缓冲池大小（建议为系统内存的50%-70%） innodb_buffer_pool_size = 4G # InnoDB日志文件大小（建议为缓冲池的25%） innodb_log_file_size = 1G # InnoDB日志缓冲区大小 innodb_log_buffer_size = 64M # 查询缓存（MySQL 8.0已移除查询缓存） # query_cache_type = 0 # query_cache_size = 0 # 排序缓冲区大小 sort_buffer_size = 4M # 连接缓冲区大小 join_buffer_size = 4M # 表缓存 table_open_cache = 4000 # 线程缓存 thread_cache_size = 100

连接优化

优化MySQL的连接处理能力：

[mysqld] # 最大连接数 max_connections = 500 # 最大允许的数据包大小 max_allowed_packet = 64M # 连接超时时间（秒） wait_timeout = 600 interactive_timeout = 600 # 最大连接错误数 max_connect_errors = 100000 # TCP backlog back_log = 512

InnoDB存储引擎优化

InnoDB是MySQL的默认存储引擎，对其进行优化可以显著提高性能：

[mysqld] # InnoDB设置 innodb_file_per_table = 1 innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_io_capacity = 2000 innodb_io_capacity_max = 4000 innodb_lru_scan_depth = 2048 innodb_thread_concurrency = 0 innodb_read_io_threads = 8 innodb_write_io_threads = 8 innodb_purge_threads = 4 innodb_page_cleaners = 4 innodb_open_files = 4000 innodb_buffer_pool_instances = 4

日志配置

合理配置日志可以平衡性能和数据安全性：

[mysqld] # 错误日志 log_error = /var/log/mysql/error.log # 慢查询日志 slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 2 log_queries_not_using_indexes = 1 # 二进制日志（用于复制和恢复） log_bin = /var/log/mysql/mysql-bin.log binlog_format = ROW expire_logs_days = 7 max_binlog_size = 1G # 通用查询日志（调试用，生产环境建议关闭） general_log = 0 general_log_file = /var/log/mysql/general.log

创建日志目录并设置权限：

sudo mkdir -p /var/log/mysql sudo chown -R mysql:mysql /var/log/mysql sudo chmod -R 750 /var/log/mysql

安全加固

用户安全

限制MySQL用户的权限和访问范围：

-- 查看现有用户 SELECT host, user FROM mysql.user; -- 删除匿名用户 DROP USER IF EXISTS ''@'localhost'; DROP USER IF EXISTS ''@'%'; -- 删除测试数据库 DROP DATABASE IF EXISTS test; -- 限制root用户只能本地登录（如需远程访问，请使用VPN或SSH隧道） UPDATE mysql.user SET host = 'localhost' WHERE user = 'root'; FLUSH PRIVILEGES; -- 为特定应用创建有限权限的用户 CREATE USER 'app_user'@'%' IDENTIFIED BY 'SecurePassword123!'; GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_user'@'%'; FLUSH PRIVILEGES;

网络安全

加强网络层面的安全措施：

# 限制MySQL监听特定IP（如果不需要远程访问） # 编辑my.cnf，设置bind-address sudo vi /etc/my.cnf [mysqld] bind-address = 127.0.0.1 # 只允许本地连接 # 或 bind-address = 192.168.1.100 # 绑定到特定内网IP # 重启MySQL服务 sudo systemctl restart mysqld # 使用防火墙限制访问（如果需要远程访问） sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.1.0/24" port protocol="tcp" port="3306" accept' sudo firewall-cmd --reload

SSL/TLS配置

为MySQL配置SSL/TLS加密连接：

# 检查MySQL是否支持SSL mysql -u root -p -e "SHOW VARIABLES LIKE '%ssl%';" # 生成SSL证书（如果不存在） sudo mkdir -p /etc/mysql/ssl cd /etc/mysql/ssl # 创建CA证书 sudo openssl genrsa 2048 > ca-key.pem sudo openssl req -new -x509 -nodes -days 3650 -key ca-key.pem -out ca.pem # 创建服务器证书 sudo openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem -out server-req.pem sudo openssl rsa -in server-key.pem -out server-key.pem sudo openssl x509 -req -in server-req.pem -days 3650 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem # 创建客户端证书 sudo openssl req -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem -out client-req.pem sudo openssl rsa -in client-key.pem -out client-key.pem sudo openssl x509 -req -in client-req.pem -days 3650 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem # 设置权限 sudo chown -R mysql:mysql /etc/mysql/ssl sudo chmod -R 600 /etc/mysql/ssl/*.pem

在MySQL配置文件中添加SSL设置：

[mysqld] # SSL配置 ssl-ca = /etc/mysql/ssl/ca.pem ssl-cert = /etc/mysql/ssl/server-cert.pem ssl-key = /etc/mysql/ssl/server-key.pem

重启MySQL服务后，可以强制特定用户使用SSL连接：

-- 创建需要SSL连接的用户 CREATE USER 'secure_user'@'%' IDENTIFIED BY 'VerySecurePassword123!' REQUIRE SSL; GRANT SELECT, INSERT, UPDATE, DELETE ON secure_db.* TO 'secure_user'@'%'; FLUSH PRIVILEGES; -- 验证SSL连接 mysql -u secure_user -p --ssl-ca=/etc/mysql/ssl/ca.pem --ssl-cert=/etc/mysql/ssl/client-cert.pem --ssl-key=/etc/mysql/ssl/client-key.pem -e "STATUS;"

审计插件

安装和配置MySQL企业审计插件（如果使用MySQL企业版）或MariaDB审计插件：

# 安装审计插件（需要MySQL企业版或第三方插件） # 这里以MariaDB的server_audit插件为例 # 下载插件 sudo wget https://github.com/mariadb-corporation/mariadb-audit-plugin/archive/refs/heads/master.zip sudo unzip master.zip sudo cp mariadb-audit-plugin-master/lib/server_audit.so /usr/lib64/mysql/plugin/ # 登录MySQL并安装插件 mysql -u root -p INSTALL PLUGIN server_audit SONAME 'server_audit.so'; -- 配置审计参数 SET GLOBAL server_audit_events = 'CONNECT,QUERY,TABLE'; SET GLOBAL server_audit_logging = 'ON'; SET GLOBAL server_audit_file_path = '/var/log/mysql/audit.log'; SET GLOBAL server_audit_file_rotate_size = 100000000; SET GLOBAL server_audit_file_rotations = 10; -- 使配置持久化 SET PERSIST server_audit_events = 'CONNECT,QUERY,TABLE'; SET PERSIST server_audit_logging = 'ON'; SET PERSIST server_audit_file_path = '/var/log/mysql/audit.log'; SET PERSIST server_audit_file_rotate_size = 100000000; SET PERSIST server_audit_file_rotations = 10;

备份与恢复

备份策略

制定合理的备份策略是数据库管理的重要部分：

完全备份：定期（如每周）对整个数据库进行完全备份
增量备份：每天备份自上次备份以来的更改
二进制日志备份：实时备份二进制日志以实现时间点恢复
异地备份：将备份文件存储在不同的物理位置

使用mysqldump进行逻辑备份

mysqldump是MySQL提供的逻辑备份工具：

# 备份单个数据库 mysqldump -u root -p --single-transaction --routines --triggers myapp_db > /backup/myapp_db_$(date +%Y%m%d).sql # 备份所有数据库 mysqldump -u root -p --single-transaction --routines --triggers --all-databases > /backup/all_databases_$(date +%Y%m%d).sql # 备份特定表 mysqldump -u root -p myapp_db users products > /backup/myapp_db_tables_$(date +%Y%m%d).sql # 压缩备份文件 mysqldump -u root -p --single-transaction myapp_db | gzip > /backup/myapp_db_$(date +%Y%m%d).sql.gz

使用mysqlpump进行并行备份（MySQL 5.7+）

mysqlpump是MySQL 5.7引入的并行备份工具，可以显著提高备份速度：

# 使用mysqlpump进行并行备份 mysqlpump -u root -p --single-transaction --default-parallelism=4 myapp_db > /backup/myapp_db_$(date +%Y%m%d).sql # 并行备份多个数据库 mysqlpump -u root -p --single-transaction --default-parallelism=8 --include-databases=db1,db2 > /backup/multiple_dbs_$(date +%Y%m%d).sql

使用mydumper进行高速备份

mydumper是一个第三方的高性能MySQL备份工具：

# 安装mydumper sudo dnf install -y mydumper # 使用mydumper进行备份 mydumper -u root -p 'password' -B myapp_db -o /backup/myapp_db_$(date +%Y%m%d)/ # 并行备份 mydumper -u root -p 'password' -B myapp_db -o /backup/myapp_db_$(date +%Y%m%d)/ -t 8

物理备份（Percona XtraBackup）

Percona XtraBackup是一个开源的物理备份工具，支持热备份：

# 安装Percona仓库 sudo dnf install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm # 安装XtraBackup sudo dnf install -y percona-xtrabackup-80 # 创建完整备份 xtrabackup --user=root --password='password' --backup --target-dir=/backup/full_$(date +%Y%m%d)/ # 准备备份 xtrabackup --prepare --target-dir=/backup/full_$(date +%Y%m%d)/ # 创建增量备份 xtrabackup --user=root --password='password' --backup --target-dir=/backup/inc1_$(date +%Y%m%d)/ --incremental-basedir=/backup/full_$(date +%Y%m%d)/

恢复数据

根据备份类型不同，恢复方法也有所不同：

从mysqldump备份恢复

# 恢复整个数据库 mysql -u root -p < /backup/all_databases_20230101.sql # 恢复单个数据库 mysql -u root -p -e "CREATE DATABASE myapp_db_new;" mysql -u root -p myapp_db_new < /backup/myapp_db_20230101.sql # 从压缩备份恢复 gunzip < /backup/myapp_db_20230101.sql.gz | mysql -u root -p myapp_db

从XtraBackup物理备份恢复

# 停止MySQL服务 sudo systemctl stop mysqld # 备份原始数据目录 sudo mv /var/lib/mysql /var/lib/mysql_backup # 恢复数据 xtrabackup --copy-back --target-dir=/backup/full_20230101/ # 修复权限 sudo chown -R mysql:mysql /var/lib/mysql # 启动MySQL服务 sudo systemctl start mysqld

自动化备份脚本

创建一个自动化备份脚本，并设置定时任务：

# 创建备份脚本 sudo vi /usr/local/bin/mysql_backup.sh

脚本内容：

#!/bin/bash # 配置变量 BACKUP_DIR="/backup/mysql" MYSQL_USER="root" MYSQL_PASSWORD="your_password" DATE=$(date +%Y%m%d_%H%M%S) RETENTION_DAYS=30 # 创建备份目录 mkdir -p $BACKUP_DIR # 完全备份 mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD --single-transaction --routines --triggers --all-databases | gzip > $BACKUP_DIR/all_databases_$DATE.sql.gz # 删除旧备份 find $BACKUP_DIR -name "*.sql.gz" -type f -mtime +$RETENTION_DAYS -delete # 记录日志 echo "Backup completed at $(date)" >> $BACKUP_DIR/backup.log

设置脚本可执行权限：

sudo chmod +x /usr/local/bin/mysql_backup.sh

添加到crontab以实现定时备份：

# 编辑crontab sudo crontab -e # 添加以下行，每天凌晨2点执行备份 0 2 * * * /usr/local/bin/mysql_backup.sh

监控与维护

日志管理

MySQL提供了多种日志，合理管理这些日志对数据库维护至关重要：

# 创建日志轮转配置 sudo vi /etc/logrotate.d/mysql

添加以下内容：

/var/log/mysql/*.log { daily rotate 7 missingok notifempty compress delaycompress sharedscripts postrotate systemctl reload mysqld endscript }

性能监控

使用MySQL自带的工具和命令监控数据库性能：

-- 查看全局状态 SHOW GLOBAL STATUS; -- 查看系统变量 SHOW GLOBAL VARIABLES; -- 查看进程列表 SHOW FULL PROCESSLIST; -- 查看InnoDB状态 SHOW ENGINE INNODB STATUS; -- 查看查询统计 SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;

使用Performance Schema

Performance Schema是MySQL提供的性能监控工具：

-- 启用Performance Schema（默认已启用） UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'; -- 查看最消耗资源的SQL语句 SELECT DIGEST_TEXT, COUNT_STAR, SUM_ROWS_EXAMINED, SUM_ROWS_SENT, SUM_CREATED_TMP_TABLES, SUM_SORT_ROWS FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_ROWS_EXAMINED DESC LIMIT 10;

使用sys schema

MySQL 5.7+提供了sys schema，简化了性能监控：

-- 查看最消耗资源的SQL SELECT * FROM sys.statements_with_runtimes_in_95th_percentile; -- 查看InnoDB缓冲池状态 SELECT * FROM sys.innodb_buffer_stats_by_schema; -- 查看未使用索引的查询 SELECT * FROM sys.statements_with_full_table_scans;

使用Prometheus和Grafana监控

部署Prometheus和Grafana进行可视化监控：

# 安装Prometheus sudo dnf install -y prometheus sudo systemctl start prometheus sudo systemctl enable prometheus # 安装mysqld_exporter sudo dnf install -y mysqld_exporter # 创建mysqld_exporter配置文件 sudo vi /etc/mysqld_exporter/my.cnf

添加以下内容：

[client] user=exporter password=exporter_password

在MySQL中创建监控用户：

CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'exporter_password'; GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost'; FLUSH PRIVILEGES;

启动mysqld_exporter：

sudo systemctl start mysqld_exporter sudo systemctl enable mysqld_exporter

安装Grafana并配置MySQL仪表板：

# 安装Grafana sudo dnf install -y grafana sudo systemctl start grafana sudo systemctl enable grafana

访问Grafana Web界面（默认端口3000），添加Prometheus数据源，并导入MySQL仪表板（ID：7362）。

定期维护任务

创建定期维护脚本，优化数据库性能：

# 创建维护脚本 sudo vi /usr/local/bin/mysql_maintenance.sh

脚本内容：

#!/bin/bash # 配置变量 MYSQL_USER="root" MYSQL_PASSWORD="your_password" LOG_FILE="/var/log/mysql/maintenance.log" # 创建日志目录 mkdir -p /var/log/mysql # 记录开始时间 echo "Maintenance started at $(date)" >> $LOG_FILE # 优化表 mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SELECT CONCAT('OPTIMIZE TABLE ', table_schema, '.', table_name, ';') FROM information_schema.tables WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys') INTO OUTFILE '/tmp/optimize_tables.sql';" mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -e "source /tmp/optimize_tables.sql" rm -f /tmp/optimize_tables.sql # 检查和修复表 mysqlcheck -u $MYSQL_USER -p$MYSQL_PASSWORD --auto-repair --check --optimize --all-databases >> $LOG_FILE 2>&1 # 清理二进制日志（如果需要） mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -e "PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);" >> $LOG_FILE 2>&1 # 记录结束时间 echo "Maintenance completed at $(date)" >> $LOG_FILE echo "--------------------------------" >> $LOG_FILE

设置脚本可执行权限并添加到crontab：

sudo chmod +x /usr/local/bin/mysql_maintenance.sh sudo crontab -e

添加以下行，每周日凌晨3点执行维护：

0 3 * * 0 /usr/local/bin/mysql_maintenance.sh

高可用配置

主从复制

MySQL主从复制是常见的高可用解决方案，下面是配置步骤：

配置主服务器

编辑主服务器的MySQL配置文件：

sudo vi /etc/my.cnf

添加以下配置：

[mysqld] # 服务器ID（必须唯一） server-id = 1 # 启用二进制日志 log_bin = /var/log/mysql/mysql-bin.log # 设置要复制的数据库（可选） binlog_do_db = myapp_db # 设置忽略的数据库（可选） binlog_ignore_db = mysql # 设置二进制日志格式 binlog_format = ROW

重启MySQL服务：

sudo systemctl restart mysqld

在主服务器上创建复制用户：

CREATE USER 'repl_user'@'%' IDENTIFIED BY 'ReplPassword123!'; GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%'; FLUSH PRIVILEGES;

获取主服务器的二进制日志坐标：

FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; UNLOCK TABLES;

记录下File和Position的值，稍后配置从服务器时需要使用。

配置从服务器

编辑从服务器的MySQL配置文件：

sudo vi /etc/my.cnf

添加以下配置：

[mysqld] # 服务器ID（必须唯一，且不能与主服务器相同） server-id = 2 # 中继日志配置 relay_log = /var/log/mysql/mysql-relay-bin.log # 设置只读（可选） read_only = 1

重启MySQL服务：

sudo systemctl restart mysqld

在从服务器上配置复制：

CHANGE MASTER TO MASTER_HOST='master_server_ip', MASTER_USER='repl_user', MASTER_PASSWORD='ReplPassword123!', MASTER_LOG_FILE='mysql-bin.000001', -- 使用主服务器上记录的File值 MASTER_LOG_POS=154; -- 使用主服务器上记录的Position值 -- 启动复制 START SLAVE; -- 检查复制状态 SHOW SLAVE STATUSG

确保Slave_IO_Running和Slave_SQL_Running的值都是Yes。

主主复制

主主复制允许两个服务器互相复制，提供双向数据同步：

配置第一台服务器

编辑第一台服务器的MySQL配置文件：

sudo vi /etc/my.cnf

添加以下配置：

[mysqld] # 服务器ID server-id = 1 # 二进制日志 log_bin = /var/log/mysql/mysql-bin.log # 设置自增ID避免冲突 auto_increment_increment = 2 auto_increment_offset = 1 # 复制配置 log_slave_updates = 1

重启MySQL服务：

sudo systemctl restart mysqld

创建复制用户：

CREATE USER 'repl_user'@'%' IDENTIFIED BY 'ReplPassword123!'; GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%'; FLUSH PRIVILEGES;

获取二进制日志坐标：

FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; UNLOCK TABLES;

配置第二台服务器

编辑第二台服务器的MySQL配置文件：

sudo vi /etc/my.cnf

添加以下配置：

[mysqld] # 服务器ID server-id = 2 # 二进制日志 log_bin = /var/log/mysql/mysql-bin.log # 设置自增ID避免冲突 auto_increment_increment = 2 auto_increment_offset = 2 # 复制配置 log_slave_updates = 1

重启MySQL服务：

sudo systemctl restart mysqld

创建复制用户：

CREATE USER 'repl_user'@'%' IDENTIFIED BY 'ReplPassword123!'; GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%'; FLUSH PRIVILEGES;

获取二进制日志坐标：

FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; UNLOCK TABLES;

互相配置复制

在第一台服务器上配置复制：

CHANGE MASTER TO MASTER_HOST='second_server_ip', MASTER_USER='repl_user', MASTER_PASSWORD='ReplPassword123!', MASTER_LOG_FILE='mysql-bin.000001', -- 使用第二台服务器上记录的File值 MASTER_LOG_POS=154; -- 使用第二台服务器上记录的Position值 START SLAVE;

在第二台服务器上配置复制：

CHANGE MASTER TO MASTER_HOST='first_server_ip', MASTER_USER='repl_user', MASTER_PASSWORD='ReplPassword123!', MASTER_LOG_FILE='mysql-bin.000001', -- 使用第一台服务器上记录的File值 MASTER_LOG_POS=154; -- 使用第一台服务器上记录的Position值 START SLAVE;

在两台服务器上检查复制状态：

SHOW SLAVE STATUSG

MySQL Group Replication

MySQL Group Replication是MySQL 5.7+提供的高可用解决方案，提供多主复制和自动故障转移：

配置所有服务器

在每个服务器上编辑MySQL配置文件：

sudo vi /etc/my.cnf

添加以下配置：

[mysqld] # 服务器ID（每个服务器必须唯一） server-id = 1 # 在其他服务器上使用不同的值 # 二进制日志 log_bin = /var/log/mysql/mysql-bin.log log_slave_updates = ON binlog_format = ROW # Group Replication设置 transaction_write_set_extraction = XXHASH64 loose-group_replication_group_name = "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee" loose-group_replication_start_on_boot = off loose-group_replication_local_address = "server1_ip:33061" # 每个服务器使用自己的IP loose-group_replication_group_seeds = "server1_ip:33061,server2_ip:33061,server3_ip:33061" loose-group_replication_bootstrap_group = off

重启MySQL服务：

sudo systemctl restart mysqld

配置复制用户

在每个服务器上执行：

SET SQL_LOG_BIN=0; CREATE USER 'repl_user'@'%' IDENTIFIED BY 'ReplPassword123!'; GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%'; FLUSH PRIVILEGES; SET SQL_LOG_BIN=1;

启动Group Replication

在第一个服务器上执行：

CHANGE MASTER TO MASTER_USER='repl_user', MASTER_PASSWORD='ReplPassword123!' FOR CHANNEL 'group_replication_recovery'; -- 安装Group Replication插件 INSTALL PLUGIN group_replication SONAME 'group_replication.so'; -- 启动Group Replication（只在第一个服务器上） SET GLOBAL group_replication_bootstrap_group = ON; START GROUP_REPLICATION; SET GLOBAL group_replication_bootstrap_group = OFF;

在其他服务器上执行：

CHANGE MASTER TO MASTER_USER='repl_user', MASTER_PASSWORD='ReplPassword123!' FOR CHANNEL 'group_replication_recovery'; -- 安装Group Replication插件 INSTALL PLUGIN group_replication SONAME 'group_replication.so'; -- 启动Group Replication START GROUP_REPLICATION;

检查Group Replication状态：

SELECT * FROM performance_schema.replication_group_members;

MySQL Router和MySQL InnoDB Cluster

MySQL InnoDB Cluster是一个完整的高可用解决方案，包括MySQL Shell、MySQL Router和Group Replication：

安装MySQL Shell和MySQL Router

# 安装MySQL Shell sudo dnf install -y mysql-shell # 安装MySQL Router sudo dnf install -y mysql-router

使用MySQL Shell配置InnoDB Cluster

启动MySQL Shell：

mysqlsh

在MySQL Shell中执行：

// 连接到第一个服务器 connect root@server1_ip:3306 // 配置实例 dba.configureInstance('root@server1_ip:3306', {clusterAdmin: "'cluster_admin'@'%'"}); // 创建集群 var cluster = dba.createCluster('myCluster'); // 添加其他实例 cluster.addInstance('root@server2_ip:3306'); cluster.addInstance('root@server3_ip:3306'); // 检查集群状态 cluster.status();

配置MySQL Router

# 创建MySQL Router配置 sudo mysqlrouter --bootstrap cluster_admin@server1_ip:3306 --user=mysqlrouter # 启动MySQL Router sudo systemctl start mysqlrouter sudo systemctl enable mysqlrouter

应用程序现在可以通过MySQL Router（默认端口6446）连接到InnoDB Cluster。

故障排除

常见启动问题

MySQL服务无法启动

检查MySQL服务状态：

sudo systemctl status mysqld

查看错误日志：

sudo tail -f /var/log/mysqld.log

常见解决方案：

sudo chown -R mysql:mysql /var/lib/mysql sudo chmod -R 750 /var/lib/mysql

检查配置文件语法：

sudo mysqld --help --verbose

检查磁盘空间：

df -h

检查端口占用：

sudo netstat -tulnp | grep 3306

连接问题

无法连接到MySQL服务器

检查MySQL服务状态：

sudo systemctl status mysqld

检查网络连接：

telnet mysql_server_ip 3306

检查用户权限：

SELECT host, user FROM mysql.user;

检查绑定地址：

SHOW VARIABLES LIKE 'bind_address';

性能问题

查询执行缓慢

启用慢查询日志：

SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 2; SET GLOBAL log_queries_not_using_indexes = 'ON';

分析慢查询日志：

sudo mysqldumpslow /var/log/mysql/slow.log

使用EXPLAIN分析查询：

EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

检查索引使用情况：

SELECT * FROM sys.schema_unused_indexes; SELECT * FROM sys.schema_redundant_indexes;

复制问题

主从复制中断

检查从服务器状态：

SHOW SLAVE STATUSG

常见错误及解决方案：

连接错误：

STOP SLAVE; CHANGE MASTER TO MASTER_HOST='new_master_ip', MASTER_USER='repl_user', MASTER_PASSWORD='ReplPassword123!'; START SLAVE;

主服务器二进制日志被清除：

STOP SLAVE; -- 获取新的主服务器日志坐标 SHOW MASTER STATUS; -- 在从服务器上重新配置 CHANGE MASTER TO MASTER_LOG_FILE='new_log_file', MASTER_LOG_POS=new_position; START SLAVE;

SQL执行错误：

-- 跳过有问题的SQL语句（谨慎使用） STOP SLAVE; SET GLOBAL sql_slave_skip_counter = 1; START SLAVE;

数据损坏

表损坏

检查表状态：

CHECK TABLE my_table;

修复表：

REPAIR TABLE my_table;

使用myisamchk修复MyISAM表（MySQL服务必须停止）：

sudo myisamchk -r /var/lib/mysql/my_db/my_table.MYI

数据库崩溃恢复

如果数据库崩溃，尝试以下步骤：

检查错误日志：

sudo tail -f /var/log/mysqld.log

尝试启动MySQL并检查InnoDB恢复：

sudo systemctl start mysqld

如果无法启动，尝试强制InnoDB恢复：

编辑MySQL配置文件：

sudo vi /etc/my.cnf

添加以下配置：

[mysqld] innodb_force_recovery = 1 # 从1到6逐步增加

尝试启动MySQL：

sudo systemctl start mysqld

导出数据并重新初始化数据库：

# 导出所有数据库 mysqldump -u root -p --all-databases > /backup/all_databases.sql # 停止MySQL sudo systemctl stop mysqld # 备份数据目录 sudo mv /var/lib/mysql /var/lib/mysql_backup # 初始化新数据目录 sudo mysqld --initialize # 启动MySQL sudo systemctl start mysqld # 获取临时root密码 sudo grep 'temporary password' /var/log/mysqld.log # 恢复数据 mysql -u root -p < /backup/all_databases.sql

总结与最佳实践

最佳实践总结

安全性
- 使用强密码和定期更换
- 限制用户权限，遵循最小权限原则
- 启用SSL/TLS加密连接
- 定期更新MySQL版本
- 使用防火墙限制访问
性能优化
- 合理配置内存参数，特别是InnoDB缓冲池
- 使用适当的索引优化查询
- 定期监控和优化慢查询
- 合理设置连接参数
- 使用适当的存储引擎
备份与恢复
- 制定并执行定期备份策略
- 测试备份恢复流程
- 保留多个备份副本，包括异地备份
- 使用二进制日志实现时间点恢复
高可用性
- 根据业务需求选择合适的高可用方案
- 定期测试故障转移流程
- 监控复制状态和延迟
- 实施负载均衡
监控与维护
- 建立全面的监控系统
- 定期检查日志和性能指标
- 执行定期维护任务
- 建立告警机制