# MySQL 数据库配置指南

## 概述

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

## 1. MySQL 安装

### 1.1 Docker 安装（推荐）

```bash
# 拉取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 安装

```bash
# 下载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 安装

```bash
# 更新包索引
sudo apt update

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

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

## 2. 数据库配置

### 2.1 基础配置

#### 修改 root 密码

```sql
-- 登录MySQL
mysql -u root -p

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

-- 刷新权限
FLUSH PRIVILEGES;
```

#### 创建业务数据库和用户

```sql
-- 创建数据库
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)

#### 基础配置

```ini
[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 开发环境配置

```yaml
# 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 生产环境配置

```yaml
# 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 参数优化

#### 内存相关参数

```ini
# 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 相关参数

```ini
# 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 索引优化

#### 索引设计原则

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

#### 常用索引示例

```sql
-- 租户索引（必须）
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 优化建议

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

#### 分页查询优化

```sql
-- 传统分页（性能差）
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 性能监控

#### 关键监控指标

```sql
-- 连接数监控
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';
```

#### 慢查询分析

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

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

### 5.2 备份策略

#### 物理备份（推荐）

```bash
# 使用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
```

#### 逻辑备份

```bash
# 使用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 用户权限管理

#### 最小权限原则

```sql
-- 应用用户只授予必要权限
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 网络安全

#### 防火墙配置

```bash
# 只允许特定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 配置

```ini
# 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 常见问题

#### 连接问题

```bash
# 检查MySQL服务状态
systemctl status mysql

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

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

#### 性能问题

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

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

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

### 7.2 日志分析

#### 错误日志分析

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

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

#### 慢查询日志分析

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

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

## 8. 高可用配置

### 8.1 主从复制

#### 主库配置

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

#### 从库配置

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

### 8.2 集群方案

#### MySQL Group Replication

```sql
-- 安装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 升级准备

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

### 9.2 升级步骤

```bash
# 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
```

## 注意事项

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