MySQL 数据库配置指南

概述

本文档详细说明了 RuoYi-Vue-Plus 项目中 MySQL 数据库的安装、配置和优化方案。

1. MySQL 安装

1.1 Docker 安装（推荐）

# 拉取MySQL 8.0镜像
docker pull mysql:8.0

# 创建MySQL容器
docker run -d \
  --name mysql-ruoyi \
  -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=your_password \
  -e MYSQL_DATABASE=ruoyi \
  -e MYSQL_USER=ruoyi \
  -e MYSQL_PASSWORD=ruoyi_password \
  -v mysql_data:/var/lib/mysql \
  mysql:8.0 \
  --character-set-server=utf8mb4 \
  --collation-server=utf8mb4_unicode_ci

1.2 传统安装

CentOS/RHEL 安装

# 下载MySQL官方Yum Repository
wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

# 安装MySQL Yum Repository
sudo rpm -Uvh mysql80-community-release-el7-3.noarch.rpm

# 安装MySQL
sudo yum install mysql-community-server

# 启动MySQL服务
sudo systemctl start mysqld
sudo systemctl enable mysqld

Ubuntu/Debian 安装

# 更新包索引
sudo apt update

# 安装MySQL服务器
sudo apt install mysql-server

# 启动MySQL服务
sudo systemctl start mysql
sudo systemctl enable mysql

2. 数据库配置

2.1 基础配置

修改 root 密码

-- 登录MySQL
mysql -u root -p

-- 修改root密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

-- 刷新权限
FLUSH PRIVILEGES;

创建业务数据库和用户

-- 创建数据库
CREATE DATABASE ruoyi DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 创建用户
CREATE USER 'ruoyi'@'%' IDENTIFIED BY 'ruoyi_password';

-- 授权
GRANT ALL PRIVILEGES ON ruoyi.* TO 'ruoyi'@'%';

-- 刷新权限
FLUSH PRIVILEGES;

2.2 配置文件 (my.cnf)

基础配置

[mysqld]
# 基本设置
port = 3306
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
pid-file = /usr/local/mysql/data/mysql.pid
user = mysql
bind-address = 0.0.0.0

# 字符集设置
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'

# SQL模式设置
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

# 时区设置
default-time-zone = '+8:00'

# 表名大小写敏感
lower_case_table_names = 1

# 日志设置
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

# 连接设置
max_connections = 1000
max_connect_errors = 100000
max_allowed_packet = 64M
interactive_timeout = 600
wait_timeout = 600

# 缓冲区设置
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT

# 其他优化
innodb_file_per_table = 1
innodb_open_files = 500
innodb_io_capacity = 1000
innodb_read_io_threads = 8
innodb_write_io_threads = 8

[mysql]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4

3. 应用配置

3.1 开发环境配置

# application-dev.yml
spring:
    datasource:
        dynamic:
            primary: master
            strict: true
            datasource:
                master:
                    type: com.zaxxer.hikari.HikariDataSource
                    driver-class-name: com.mysql.cj.jdbc.Driver
                    url: jdbc:mysql://localhost:3306/ruoyi?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&autoReconnect=true&rewriteBatchedStatements=true&allowPublicKeyRetrieval=true&nullCatalogMeansCurrent=true
                    username: ruoyi
                    password: ruoyi_password
            hikari:
                maximum-pool-size: 20
                minimum-idle: 10
                connection-timeout: 30000
                validation-timeout: 5000
                idle-timeout: 600000
                max-lifetime: 1800000
                keepalive-time: 30000

3.2 生产环境配置

# application-prod.yml
spring:
    datasource:
        dynamic:
            primary: master
            strict: true
            datasource:
                master:
                    type: com.zaxxer.hikari.HikariDataSource
                    driver-class-name: com.mysql.cj.jdbc.Driver
                    url: jdbc:mysql://mysql-server:3306/ruoyi?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&autoReconnect=true&rewriteBatchedStatements=true&allowPublicKeyRetrieval=true&nullCatalogMeansCurrent=true
                    username: ${DB_USERNAME:ruoyi}
                    password: ${DB_PASSWORD:encrypted_password}
            hikari:
                maximum-pool-size: 50
                minimum-idle: 20
                connection-timeout: 30000
                validation-timeout: 5000
                idle-timeout: 600000
                max-lifetime: 1800000
                keepalive-time: 30000
                connection-test-query: SELECT 1

4. 性能优化

4.1 参数优化

内存相关参数

# InnoDB缓冲池大小（建议设置为可用内存的70-80%）
innodb_buffer_pool_size = 4G

# 缓冲池实例数（建议设置为CPU核心数）
innodb_buffer_pool_instances = 8

# 查询缓存（MySQL 8.0已移除）
# query_cache_size = 0
# query_cache_type = 0

# 排序缓冲区
sort_buffer_size = 2M

# 连接缓冲区
read_buffer_size = 2M
read_rnd_buffer_size = 8M

# 临时表缓冲区
tmp_table_size = 128M
max_heap_table_size = 128M

I/O 相关参数

# InnoDB I/O容量
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# InnoDB读写线程数
innodb_read_io_threads = 16
innodb_write_io_threads = 16

# InnoDB刷盘策略
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1

# 并发插入
concurrent_insert = 2

4.2 索引优化

索引设计原则

主键索引: 使用雪花 ID，保证唯一性和有序性
外键索引: 所有外键字段必须建立索引
复合索引: 根据查询条件建立复合索引
覆盖索引: 优先使用覆盖索引减少回表

常用索引示例

-- 租户索引（必须）
CREATE INDEX idx_tenant_id ON sys_user(tenant_id);

-- 复合索引
CREATE INDEX idx_user_status_dept ON sys_user(status, dept_id, create_time);

-- 唯一索引
CREATE UNIQUE INDEX uk_user_name_tenant ON sys_user(user_name, tenant_id);

4.3 查询优化

SQL 优化建议

避免 SELECT *: 只查询需要的字段
使用 LIMIT: 大数据量查询使用 LIMIT 分页
避免子查询: 使用 JOIN 替代子查询
使用 EXPLAIN: 分析查询执行计划

分页查询优化

-- 传统分页（性能差）
SELECT * FROM sys_user ORDER BY id LIMIT 10000, 10;

-- 优化分页（使用覆盖索引）
SELECT u.* FROM sys_user u
INNER JOIN (
    SELECT id FROM sys_user ORDER BY id LIMIT 10000, 10
) tmp ON u.id = tmp.id;

-- 游标分页（推荐）
SELECT * FROM sys_user WHERE id > 10000 ORDER BY id LIMIT 10;

5. 监控和维护

5.1 性能监控

关键监控指标

-- 连接数监控
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

-- 查询性能监控
SHOW STATUS LIKE 'Slow_queries';
SHOW STATUS LIKE 'Questions';

-- InnoDB监控
SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';

-- 锁监控
SHOW STATUS LIKE 'Table_locks_waited';
SHOW STATUS LIKE 'Innodb_deadlocks';

慢查询分析

# 分析慢查询日志
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 实时监控慢查询
mysql -e "SHOW FULL PROCESSLIST" | grep -v "Sleep"

5.2 备份策略

物理备份（推荐）

# 使用xtrabackup进行热备份
xtrabackup --backup --target-dir=/backup/mysql/$(date +%Y%m%d_%H%M%S)

# 备份脚本示例
#!/bin/bash
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_PATH="$BACKUP_DIR/$DATE"

# 创建备份目录
mkdir -p $BACKUP_PATH

# 执行备份
xtrabackup --backup --target-dir=$BACKUP_PATH

# 压缩备份
tar -czf $BACKUP_PATH.tar.gz -C $BACKUP_DIR $DATE

# 删除原备份目录
rm -rf $BACKUP_PATH

# 删除7天前的备份
find $BACKUP_DIR -name "*.tar.gz" -mtime +7 -delete

逻辑备份

# 使用mysqldump备份
mysqldump -u root -p --single-transaction --routines --triggers \
  --events --databases ruoyi > ruoyi_$(date +%Y%m%d_%H%M%S).sql

# 恢复数据
mysql -u root -p ruoyi < ruoyi_20250101_120000.sql

6. 安全配置

6.1 用户权限管理

最小权限原则

-- 应用用户只授予必要权限
CREATE USER 'app_user'@'%' IDENTIFIED BY 'strong_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON ruoyi.* TO 'app_user'@'%';

-- 只读用户
CREATE USER 'readonly_user'@'%' IDENTIFIED BY 'readonly_password';
GRANT SELECT ON ruoyi.* TO 'readonly_user'@'%';

-- 备份用户
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'backup_password';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';

6.2 网络安全

防火墙配置

# 只允许特定IP访问MySQL
iptables -A INPUT -p tcp --dport 3306 -s 192.168.1.0/24 -j ACCEPT
iptables -A INPUT -p tcp --dport 3306 -j DROP

SSL 配置

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

7. 故障排查

7.1 常见问题

连接问题

# 检查MySQL服务状态
systemctl status mysql

# 检查端口监听
netstat -tlnp | grep 3306

# 检查连接数
mysql -e "SHOW STATUS LIKE 'Threads_connected'"

性能问题

-- 查看当前运行的查询
SHOW FULL PROCESSLIST;

-- 查看锁等待
SHOW ENGINE INNODB STATUS;

-- 查看表锁
SHOW OPEN TABLES WHERE In_use > 0;

7.2 日志分析

错误日志分析

# 查看错误日志
tail -f /var/log/mysql/error.log

# 检查常见错误
grep -i "error\|warning" /var/log/mysql/error.log

慢查询日志分析

# 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

# 分析慢查询
pt-query-digest /var/log/mysql/slow.log

8. 高可用配置

8.1 主从复制

主库配置

# my.cnf 主库配置
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
expire_logs_days = 7

从库配置

# my.cnf 从库配置
[mysqld]
server-id = 2
relay-log = mysql-relay-bin
read-only = 1

8.2 集群方案

MySQL Group Replication

-- 安装Group Replication插件
INSTALL PLUGIN group_replication SONAME 'group_replication.so';

-- 配置Group Replication
SET GLOBAL group_replication_group_name="550fa9ee-a1f8-4b6d-9bfe-c03c12cd1c72";
SET GLOBAL group_replication_start_on_boot=off;
SET GLOBAL group_replication_local_address="node1:33061";
SET GLOBAL group_replication_group_seeds="node1:33061,node2:33061,node3:33061";

9. 版本升级

9.1 升级准备

备份数据: 升级前完整备份
测试环境验证: 在测试环境先进行升级测试
应用兼容性: 检查应用与新版本的兼容性
回滚计划: 准备回滚方案

9.2 升级步骤

# 1. 停止应用服务
systemctl stop app-service

# 2. 备份数据
mysqldump --all-databases > backup_before_upgrade.sql

# 3. 升级MySQL
yum update mysql-community-server

# 4. 启动MySQL
systemctl start mysqld

# 5. 运行升级脚本
mysql_upgrade -u root -p

# 6. 启动应用服务
systemctl start app-service

注意事项

密码安全: 生产环境使用强密码，定期更换
权限控制: 遵循最小权限原则，不要使用 root 用户连接应用
定期备份: 建立完善的备份策略，定期测试恢复
监控告警: 设置关键指标监控和告警
性能调优: 根据实际业务负载调整参数
安全更新: 及时安装安全补丁和更新

mysql-config.md 12 KB Lịch sử Raw

MySQL 数据库配置指南

概述

1. MySQL 安装

1.1 Docker 安装（推荐）

1.2 传统安装

CentOS/RHEL 安装

Ubuntu/Debian 安装

2. 数据库配置

2.1 基础配置

修改 root 密码

创建业务数据库和用户

2.2 配置文件 (my.cnf)

基础配置

3. 应用配置

3.1 开发环境配置

3.2 生产环境配置

4. 性能优化

4.1 参数优化

内存相关参数

I/O 相关参数

4.2 索引优化

索引设计原则

常用索引示例

4.3 查询优化

SQL 优化建议

分页查询优化

5. 监控和维护

5.1 性能监控

关键监控指标

慢查询分析

5.2 备份策略

物理备份（推荐）

逻辑备份

6. 安全配置

6.1 用户权限管理

最小权限原则

6.2 网络安全

防火墙配置

SSL 配置

7. 故障排查

7.1 常见问题

连接问题

性能问题

7.2 日志分析

错误日志分析

慢查询日志分析

8. 高可用配置

8.1 主从复制

主库配置

从库配置

8.2 集群方案

MySQL Group Replication

9. 版本升级

9.1 升级准备

9.2 升级步骤

注意事项

mysql-config.md 12 KB

Lịch sử Raw