MySQL 企业级实战

HSXISAWD2026/3/20大约 12 分钟

MySQL 企业级实战

一、生产环境最佳实践

1.1 数据库设计规范

命名规范

-- 表命名
-- 1. 使用小写字母和下划线
-- 2. 使用有意义的名称
-- 3. 添加业务前缀

-- 好的命名
user_info
order_detail
product_category

-- 不好的命名
UserInfo       -- 大小写混用
orderdetail    -- 难以阅读
t1, temp       -- 无意义


-- 字段命名
-- 1. 主键统一使用 id
-- 2. 外键使用 表名_id
-- 3. 时间字段使用 _at 后缀

id BIGINT UNSIGNED PRIMARY KEY
user_id BIGINT UNSIGNED
created_at DATETIME
updated_at DATETIME


-- 索引命名
-- 1. 主键: pk_表名
-- 2. 唯一索引: uk_表名_字段
-- 3. 普通索引: idx_表名_字段

PRIMARY KEY pk_user
UNIQUE KEY uk_user_email (email)
INDEX idx_user_status (status)

字段设计规范

-- 1. 选择合适的数据类型
-- 整数：根据范围选择 TINYINT/SMALLINT/INT/BIGINT
-- 小数：金额必须用 DECIMAL
-- 字符串：合理设置 VARCHAR 长度
-- 时间：优先使用 DATETIME

-- 2. 避免使用 NULL
-- 使用 NOT NULL DEFAULT 替代
status TINYINT NOT NULL DEFAULT 0
name VARCHAR(50) NOT NULL DEFAULT ''

-- 3. 主键设计
-- 使用自增 BIGINT UNSIGNED
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY

-- 4. 必备字段
CREATE TABLE example (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    -- 业务字段
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_deleted TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

索引设计规范

-- 1. 单表索引不超过 5-6 个
-- 2. 复合索引字段不超过 5 个
-- 3. 避免冗余索引
-- 4. 覆盖高频查询

-- 索引设计步骤：
-- 1. 收集 SQL 查询模式
-- 2. 分析 WHERE、ORDER BY、GROUP BY
-- 3. 设计复合索引
-- 4. 定期审查索引使用情况

-- 索引创建示例
CREATE TABLE orders (
    id BIGINT UNSIGNED PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    status TINYINT NOT NULL DEFAULT 0,
    amount DECIMAL(12,2) NOT NULL,
    created_at DATETIME NOT NULL,

    -- 根据查询模式设计索引
    INDEX idx_user_status_time (user_id, status, created_at),
    INDEX idx_status_time (status, created_at)
);

1.2 SQL 开发规范

-- 1. 禁止 SELECT *
-- 不好
SELECT * FROM users WHERE id = 1;
-- 好
SELECT id, username, email FROM users WHERE id = 1;


-- 2. 使用 LIMIT 限制结果
SELECT * FROM orders WHERE user_id = 1 ORDER BY created_at DESC LIMIT 100;


-- 3. 避免在循环中执行 SQL
-- 不好：在应用循环中逐条插入
-- 好：批量插入
INSERT INTO users (name) VALUES ('a'), ('b'), ('c');


-- 4. 使用预编译语句，防止 SQL 注入
-- 应用层使用参数化查询


-- 5. 事务控制
-- 保持事务简短
-- 避免长事务
START TRANSACTION;
-- 只做必要的数据库操作
COMMIT;


-- 6. 合理使用连接
-- 控制 JOIN 表数量（不超过 5 个）
-- 确保 JOIN 字段有索引


-- 7. 避免使用存储过程和触发器
-- 难以维护和调试
-- 影响性能

1.3 表结构变更规范

-- 1. 使用 Online DDL
ALTER TABLE users ADD COLUMN phone VARCHAR(20), ALGORITHM=INPLACE, LOCK=NONE;

-- 2. 大表变更使用 pt-online-schema-change
pt-online-schema-change \
    --alter "ADD COLUMN phone VARCHAR(20)" \
    D=mydb,t=users \
    --execute

-- 3. 添加索引（MySQL 5.6+）
ALTER TABLE users ADD INDEX idx_phone(phone), ALGORITHM=INPLACE, LOCK=NONE;

-- 4. 删除列要谨慎
-- 先确认列未被使用
-- 备份数据后再删除

-- 5. 修改字段类型
-- 小心隐式类型转换
-- 可能导致索引失效

二、备份与恢复

2.1 备份策略

备份类型：

物理备份
- 直接复制数据文件
- 速度快，恢复快
- 工具：xtrabackup
逻辑备份
- 导出 SQL 语句
- 可读性好，灵活
- 工具：mysqldump
备份策略

2.2 mysqldump 备份

# 全量备份
mysqldump -u root -p \
    --single-transaction \
    --master-data=2 \
    --routines \
    --triggers \
    --events \
    --all-databases > full_backup.sql

# 单库备份
mysqldump -u root -p \
    --single-transaction \
    --master-data=2 \
    mydb > mydb_backup.sql

# 单表备份
mysqldump -u root -p \
    --single-transaction \
    mydb users > users_backup.sql

# 只导出表结构
mysqldump -u root -p --no-data mydb > schema.sql

# 只导出数据
mysqldump -u root -p --no-create-info mydb > data.sql

# 压缩备份
mysqldump -u root -p mydb | gzip > mydb_$(date +%Y%m%d).sql.gz

2.3 xtrabackup 备份

# 安装 xtrabackup
yum install percona-xtrabackup-80

# 全量备份
xtrabackup --backup \
    --user=root \
    --password=xxx \
    --target-dir=/backup/full

# 准备备份（应用日志）
xtrabackup --prepare --target-dir=/backup/full

# 增量备份
xtrabackup --backup \
    --user=root \
    --password=xxx \
    --target-dir=/backup/inc1 \
    --incremental-basedir=/backup/full

# 恢复备份
# 1. 停止 MySQL
systemctl stop mysqld

# 2. 清空数据目录
rm -rf /var/lib/mysql/*

# 3. 恢复数据
xtrabackup --copy-back --target-dir=/backup/full

# 4. 修改权限
chown -R mysql:mysql /var/lib/mysql

# 5. 启动 MySQL
systemctl start mysqld

2.4 binlog 恢复

# 查看 binlog 文件
mysqlbinlog --no-defaults mysql-bin.000001

# 按时间恢复
mysqlbinlog --no-defaults \
    --start-datetime="2024-01-01 00:00:00" \
    --stop-datetime="2024-01-01 12:00:00" \
    mysql-bin.000001 | mysql -u root -p

# 按位置恢复
mysqlbinlog --no-defaults \
    --start-position=154 \
    --stop-position=1000 \
    mysql-bin.000001 | mysql -u root -p

# 跳过某个事务（误操作恢复）
mysqlbinlog --no-defaults \
    --start-position=154 \
    --stop-position=500 \
    mysql-bin.000001 > part1.sql

mysqlbinlog --no-defaults \
    --start-position=600 \
    mysql-bin.000001 > part2.sql

mysql -u root -p < part1.sql
mysql -u root -p < part2.sql

2.5 备份验证

# 定期恢复测试
# 1. 在测试环境恢复备份
# 2. 检查数据完整性
# 3. 记录恢复时间

# 数据校验
pt-table-checksum \
    --host=master \
    --user=root \
    --password=xxx \
    --databases=mydb

# 备份脚本示例
#!/bin/bash
BACKUP_DIR=/backup/mysql
DATE=$(date +%Y%m%d)
MYSQL_USER=root
MYSQL_PASS=xxx

# 创建备份
mysqldump -u$MYSQL_USER -p$MYSQL_PASS \
    --single-transaction \
    --all-databases | gzip > $BACKUP_DIR/full_$DATE.sql.gz

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

# 上传到远程存储
aws s3 cp $BACKUP_DIR/full_$DATE.sql.gz s3://mysql-backup/

三、故障排查

3.1 常见故障排查

MySQL 无法启动

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

# 常见原因：
# - 配置文件语法错误
# - 数据目录权限问题
# - 端口被占用
# - 磁盘空间不足
# - InnoDB 恢复失败

# 2. 检查配置文件
mysqld --validate-config

# 3. 检查权限
ls -la /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql

# 4. 检查端口
netstat -tlnp | grep 3306

# 5. 检查磁盘空间
df -h

# 6. InnoDB 恢复（崩溃后）
# 尝试设置恢复模式
innodb_force_recovery = 1  # 1-6 逐级尝试

连接问题

-- 1. Too many connections
SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';

-- 解决：增加连接数或优化连接池
SET GLOBAL max_connections = 500;

-- 2. 连接超时
SHOW VARIABLES LIKE '%timeout%';

-- 解决：调整超时参数
SET GLOBAL wait_timeout = 600;
SET GLOBAL interactive_timeout = 600;

-- 3. 认证失败
-- 检查用户权限
SELECT user, host FROM mysql.user;
SHOW GRANTS FOR 'user'@'host';

-- 重置密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

性能问题

-- 1. 查看当前正在执行的 SQL
SHOW PROCESSLIST;
SELECT * FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep' AND TIME > 10;

-- 2. 杀死长时间运行的查询
KILL query_id;

-- 3. 查看锁等待
SELECT * FROM sys.innodb_lock_waits\G

-- 4. 查看慢查询
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

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

3.2 数据恢复

误删数据恢复

# 方法1：从备份恢复
# 1. 找到最近的全量备份
# 2. 使用 binlog 恢复到误操作之前

# 方法2：使用 binlog2sql 闪回
# 安装
pip install binlog2sql

# 解析 binlog
binlog2sql -h127.0.0.1 -P3306 -uroot -p'xxx' \
    --start-file='mysql-bin.000001' \
    --start-datetime='2024-01-01 00:00:00' \
    --stop-datetime='2024-01-01 12:00:00' \
    -d mydb -t users

# 生成回滚 SQL
binlog2sql -h127.0.0.1 -P3306 -uroot -p'xxx' \
    --start-file='mysql-bin.000001' \
    --start-pos=154 \
    --stop-pos=1000 \
    -B > rollback.sql

# 方法3：使用延迟从库
# 从延迟从库恢复数据

误删表恢复

# 如果表被 DROP 且有备份
# 1. 从备份中恢复表结构和数据
mysql -u root -p mydb < table_backup.sql

# 如果只有 binlog
# 1. 解析 binlog 找到 CREATE TABLE 和 INSERT 语句
mysqlbinlog --no-defaults mysql-bin.000001 | grep -A 100 "CREATE TABLE users"

3.3 常用诊断命令

-- 系统状态
SHOW GLOBAL STATUS;
SHOW GLOBAL VARIABLES;

-- InnoDB 状态
SHOW ENGINE INNODB STATUS\G

-- 查看进程
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- 查看锁
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

-- 查看事务
SELECT * FROM information_schema.INNODB_TRX;

-- 查看表状态
SHOW TABLE STATUS LIKE 'table_name';

-- 查看索引状态
SHOW INDEX FROM table_name;

四、安全加固

4.1 用户权限管理

-- 1. 删除匿名用户
DELETE FROM mysql.user WHERE User = '';

-- 2. 禁止 root 远程登录
DELETE FROM mysql.user WHERE User = 'root' AND Host != 'localhost';

-- 3. 创建最小权限用户
-- 应用用户
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongPassword123!';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'192.168.1.%';

-- 只读用户
CREATE USER 'readonly'@'%' IDENTIFIED BY 'ReadOnlyPass123!';
GRANT SELECT ON mydb.* TO 'readonly'@'%';

-- 备份用户
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'BackupPass123!';
GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup'@'localhost';

-- 4. 定期审查权限
SELECT user, host, authentication_string FROM mysql.user;
SHOW GRANTS FOR 'user'@'host';

-- 5. 密码策略
SHOW VARIABLES LIKE 'validate_password%';
SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.policy = STRONG;

4.2 网络安全

# my.cnf 配置

[mysqld]
# 绑定特定 IP
bind-address = 192.168.1.100

# 禁用本地文件加载
local_infile = 0

# 禁用符号链接
symbolic-links = 0

# 跳过名称解析（提高性能，增加安全）
skip-name-resolve

# 禁用 LOAD DATA LOCAL
local_infile = 0

# 防火墙配置
# 只允许应用服务器访问
iptables -A INPUT -p tcp -s 192.168.1.0/24 --dport 3306 -j ACCEPT
iptables -A INPUT -p tcp --dport 3306 -j DROP

4.3 数据加密

-- 1. 传输加密（SSL/TLS）
-- 生成证书
mysql_ssl_rsa_setup --datadir=/var/lib/mysql

-- 配置 SSL
[mysqld]
ssl-ca = /var/lib/mysql/ca.pem
ssl-cert = /var/lib/mysql/server-cert.pem
ssl-key = /var/lib/mysql/server-key.pem
require_secure_transport = ON

-- 强制用户使用 SSL
ALTER USER 'app_user'@'%' REQUIRE SSL;


-- 2. 静态数据加密（MySQL 8.0）
-- 加密表空间
ALTER TABLE users ENCRYPTION='Y';

-- 配置主密钥
[mysqld]
early-plugin-load = keyring_file.so
keyring_file_data = /var/lib/mysql/keyring

4.4 审计日志

-- 安装审计插件（企业版或 Percona）
INSTALL PLUGIN audit_log SONAME 'audit_log.so';

-- 或使用通用查询日志（开发环境）
SET GLOBAL general_log = ON;
SET GLOBAL general_log_file = '/var/log/mysql/general.log';

-- 查看登录记录
SELECT * FROM mysql.general_log WHERE command_type = 'Connect';

五、运维规范

5.1 日常运维检查

#!/bin/bash
# 日常检查脚本

# 1. 检查 MySQL 状态
systemctl status mysqld

# 2. 检查磁盘空间
df -h | grep -E '(mysql|data)'

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

# 4. 检查慢查询
mysql -e "SHOW STATUS LIKE 'Slow_queries';"

# 5. 检查复制状态（从库）
mysql -e "SHOW SLAVE STATUS\G" | grep -E '(Slave_IO_Running|Slave_SQL_Running|Seconds_Behind)'

# 6. 检查锁等待
mysql -e "SELECT COUNT(*) FROM information_schema.INNODB_TRX WHERE trx_state='LOCK WAIT';"

# 7. 检查错误日志
tail -50 /var/log/mysql/error.log | grep -i error

5.2 变更管理流程

5.3 监控告警

# 关键监控指标

MySQL 可用性:
  - 进程存活
  - 端口可达
  - 连接成功

性能指标:
  - QPS
  - TPS
  - 连接数使用率
  - Buffer Pool 命中率
  - 慢查询数量

资源指标:
  - CPU 使用率
  - 内存使用率
  - 磁盘空间
  - 磁盘 I/O

复制指标:
  - 复制状态
  - 复制延迟
  - GTID 延迟

告警阈值:
  - 连接数 > 80%: 警告
  - 连接数 > 95%: 严重
  - 复制延迟 > 60s: 警告
  - 复制延迟 > 300s: 严重
  - 磁盘空间 < 20%: 警告
  - 磁盘空间 < 10%: 严重

5.4 容量规划

-- 表大小统计
SELECT
    table_schema AS 'Database',
    table_name AS 'Table',
    ROUND(data_length / 1024 / 1024, 2) AS 'Data (MB)',
    ROUND(index_length / 1024 / 1024, 2) AS 'Index (MB)',
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS 'Total (MB)',
    table_rows AS 'Rows'
FROM information_schema.tables
WHERE table_schema = 'mydb'
ORDER BY (data_length + index_length) DESC;

-- 数据增长趋势
-- 记录每日表大小，分析增长趋势

-- 容量预估
-- 1. 当前数据量
-- 2. 每日增长量
-- 3. 预估未来数据量
-- 4. 考虑备份空间

六、应急预案

6.1 主库故障

# 预案流程

1. 确认故障
   - 检查 MySQL 进程
   - 检查错误日志
   - 检查系统资源

2. 尝试恢复
   - 重启 MySQL
   - 检查数据完整性

3. 故障转移（如果无法恢复）
   - 选择数据最新的从库
   - 提升为新主库
   - 切换应用连接
   - 通知相关人员

4. 事后处理
   - 分析故障原因
   - 修复原主库
   - 重新加入集群
   - 编写故障报告

6.2 数据误删

# 预案流程

1. 立即停止相关应用写入

2. 评估影响范围
   - 影响的表/数据
   - 影响的业务

3. 选择恢复方案
   - 方案A：从备份恢复
   - 方案B：binlog 闪回
   - 方案C：延迟从库恢复

4. 执行恢复
   - 测试环境验证
   - 生产环境执行

5. 验证数据
   - 检查数据完整性
   - 业务验证

6. 事后处理
   - 分析原因
   - 完善防护措施
   - 培训相关人员

6.3 性能突降

# 预案流程

1. 快速定位
   - 查看当前执行的 SQL
   - 查看系统资源使用
   - 查看锁等待情况

2. 紧急处理
   - 杀死问题 SQL
   - 限制并发连接
   - 临时扩容（如果可能）

3. 根本解决
   - 优化问题 SQL
   - 添加缺失索引
   - 调整配置参数

4. 预防措施
   - 完善 SQL 审核
   - 加强监控告警
   - 定期性能巡检

七、总结

生产环境核心要点

规范先行：建立完善的开发和运维规范
备份为王：定期备份，定期验证
监控到位：全面监控，及时告警
安全加固：最小权限，加密传输
预案完备：制定预案，定期演练

关键检查清单

□ 备份策略已制定并执行
□ 备份恢复已定期验证
□ 主从复制正常运行
□ 监控告警已配置
□ 用户权限最小化
□ 安全加固已完成
□ 应急预案已制定
□ 文档齐全并更新

运维工具推荐

类别	工具	用途
备份	xtrabackup, mysqldump	数据备份
监控	Prometheus + Grafana	指标监控
复制	orchestrator	复制管理
分析	pt-query-digest	慢查询分析
对比	pt-table-checksum	数据一致性
变更	pt-online-schema-change	在线 DDL