MySQL运维实战——从优化、监控到故障恢复

February 18, 2017 Database MySQL, Innodb, Database, Devops 4400 字 9 分钟阅读

前言

在业务系统的运维过程中，MySQL数据库作为核心组件，其性能稳定性和数据安全性至关重要。本文基于多年实际运维经验，从性能优化、监控体系、Binlog管理、高可用部署到故障恢复，全方位分享MySQL数据库的运维实战经验。这些经验来自多个生产环境的实践案例，涵盖了从日常调优到紧急故障处理的完整技术栈，希望能为各位数据库管理员和运维工程师提供有价值的参考。

InnoDB 性能优化实战

基础状态分析

在进行InnoDB优化之前，首先需要全面了解当前数据库的运行状态：

sql
1
2
3
4
5
-- 查看InnoDB存储引擎状态
show engine innodb status \G;

-- 查看InnoDB相关配置参数
show variables like 'innodb%';

这些命令能帮助我们识别性能瓶颈，为后续优化提供数据支撑。

I/O线程优化

合理配置读写线程数量可以有效利用CPU资源：

ini
1
2
3
# 读写线程配置，参考值总和 = CPU核数 - 2
innodb_read_io_threads = 8
innodb_write_io_threads = 12

日志参数配置

重要提示：以下配置的调整需要清理ib_logfile文件，否则会导致MySQL无法启动

ini
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
# 事务日志缓存区，建议1M~8M
innodb_log_buffer_size = 16M

# 事务日志文件大小，建议1G~2G
innodb_log_file_size = 1G

# 事务日志文件数量
innodb_log_files_in_group = 2

# 事务日志文件目录
innodb_log_group_home_dir = /var/lib/mysql/

# 事务提交时写入磁盘的策略
innodb_flush_log_at_trx_commit = 1

参数说明：

innodb_log_buffer_size：事务日志缓存区，形如"漏斗"状，会持续向磁盘记录缓存的日志记录。启用大的缓存可以将完整运行的事务日志暂时存放，减少磁盘IO。
innodb_log_file_size：控制事务日志文件大小。大的日志文件意味着checkpoint减少，节省磁盘IO，但会增加数据库crash时的恢复时间。
innodb_flush_log_at_trx_commit：控制事务提交时日志的写入策略，1表示每次提交都写入磁盘，2表示每秒写入一次，0表示由操作系统控制写入时机。

磁盘IO优化

提高磁盘IO利用率的关键参数：

sql
1
2
-- 根据磁盘类型调整，SAS 15000转的磁盘：200个/块，SATA盘：100个/块
set global innodb_io_capacity=300;

⚠️ 危险警告：此参数比较危险，设置不当会直接影响磁盘IO能力导致性能下降。

缓存优化

ini
1
2
3
4
5
# 缓冲池大小，建议物理内存的50%-70%
innodb_buffer_pool_size = 1024M

# 缓冲池实例数（建议1-8个）
innodb_buffer_pool_instances = 3

异步IO支持

在Linux系统下建议开启MySQL的异步IO支持：

bash
1
2
# 安装libaio库
yum -y install libaio

ini
1
2
# 开启异步IO
innodb_use_native_aio = ON

MySQL 监控体系搭建

权限管理原则

在搭建监控体系前，需要建立完善的权限管理原则：

最小权限原则：只授予能满足需要的最小权限，防止用户干坏事
登录主机限制：创建用户时限制登录主机，通常为指定IP或内网IP段
清理默认用户：初始化数据库后删除没有密码的默认用户
密码复杂度：为每个用户设置满足密码复杂度的密码
定期清理：定期清理不需要的用户，回收权限或删除用户

监控账号授权

针对本地监控和远程监控分别授权：

sql
1
2
3
4
5
-- 远程监控账号
GRANT USAGE,PROCESS,REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'monitor'@'192.168.1.%' IDENTIFIED BY 'YOUR_PASSWORD';

-- 本地监控账号
GRANT USAGE,SUPER,PROCESS,REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'monitor'@'localhost' IDENTIFIED BY 'YOUR_PASSWORD';

监控原理与常用命令

MySQL监控主要通过以下命令获取系统状态：

sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
-- 全局状态查看
show global status;

-- 全局变量设置查看
show global variables;

-- 主库状态查看
show master status;

-- 从库状态查看
show slave status;

-- 二进制日志文件查看
show binary logs;

-- InnoDB存储引擎状态
show engine innodb status \G;

-- MyISAM存储引擎状态
show engine myisam status \G;

还可以通过information_schema数据库获取InnoDB存储引擎的详细信息。

权限表结构

MySQL权限控制基于精细的权限级别：

权限	权限级别	权限说明
CREATE	数据库、表或索引	创建数据库、表或索引权限
DROP	数据库或表	删除数据库或表权限
GRANT OPTION	数据库、表或保存的程序	赋予权限选项
REFERENCES	数据库或表	外键引用权限
ALTER	表	更改表结构权限
DELETE	表	删除数据权限
INDEX	表	索引操作权限
INSERT	表	插入数据权限
SELECT	表	查询数据权限
UPDATE	表	更新数据权限
CREATE VIEW	视图	创建视图权限
SHOW VIEW	视图	查看视图权限
ALTER ROUTINE	存储过程	更改存储过程权限
CREATE ROUTINE	存储过程	创建存储过程权限
EXECUTE	存储过程	执行存储过程权限
FILE	服务器主机上的文件访问	文件访问权限
CREATE TEMPORARY TABLES	服务器管理	创建临时表权限
LOCK TABLES	服务器管理	锁表权限
CREATE USER	服务器管理	创建用户权限
PROCESS	服务器管理	查看进程权限
RELOAD	服务器管理	执行刷新命令权限
REPLICATION CLIENT	服务器管理	复制客户端权限
REPLICATION SLAVE	服务器管理	复制从库权限
SHOW DATABASES	服务器管理	查看数据库权限
SHUTDOWN	服务器管理	关闭数据库权限
SUPER	服务器管理	执行kill线程权限

Binlog 操作指南

binlog 基础认识

MySQL的二进制日志是MySQL最重要的日志之一，它记录了所有的DDL和DML语句（除了数据查询语句），以事件形式记录，还包含语句所执行的消耗时间，MySQL的二进制日志是事务安全型的。

开启二进制日志大概会有1%的性能损耗，其主要用途有两个：

MySQL Replication：在Master端开启binlog，Master把它的二进制日志传递给slaves来实现数据同步
数据恢复：通过使用mysqlbinlog工具来恢复数据

二进制日志包括两类文件：

二进制日志索引文件（后缀为.index）：记录所有的二进制日志文件
二进制日志文件（后缀为.00000*）：记录数据库所有的DDL和DML语句事件

开启binlog日志

编辑MySQL配置文件：

bash
1
2
# 编辑配置文件
vi /etc/my.cnf

在[mysqld]区块设置：

ini
1
2
3
[mysqld]
# 开启二进制日志
log-bin=mysql-bin

重启MySQL服务使配置生效：

bash
1
2
3
# 重启MySQL
pkill mysqld
/usr/local/mysql/bin/mysqld_safe --user=mysql &

查看binlog状态

登录MySQL服务器，通过查看变量配置确认二进制日志是否已开启：

bash
1
2
# 登录MySQL
/usr/local/mysql/bin/mysql -uroot -p

sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
-- 查看二进制日志状态
mysql> show variables like 'log_%';
+----------------------------------------+---------------------------------------+
| Variable_name                          | Value                                 |
+----------------------------------------+---------------------------------------+
| log_bin                                | ON                                    |
| log_bin_basename                       | /usr/local/mysql/data/mysql-bin       |
| log_bin_index                          | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators        | OFF                                   |
| log_bin_use_v1_row_events              | OFF                                   |
| log_error                              | /usr/local/mysql/data/martin.err      |
| log_output                             | FILE                                  |
| log_queries_not_using_indexes          | OFF                                   |
| log_slave_updates                      | OFF                                   |
| log_slow_admin_statements              | OFF                                   |
| log_slow_slave_statements              | OFF                                   |
| log_throttle_queries_not_using_indexes | 0                                     |
| log_warnings                           | 1                                     |
+----------------------------------------+---------------------------------------+

常用binlog操作命令

1. 查看所有binlog日志列表

sql
1
mysql> show master logs;

2. 查看当前master状态

sql
1
mysql> show master status;

3. 刷新日志

刷新log日志，自此刻开始产生一个新编号的binlog日志文件：

sql
1
mysql> flush logs;

注意：当使用mysqldump备份数据时加-F选项也会刷新log日志

4. 重置所有binlog日志

sql
1
mysql> reset master;

MySQL 双主高可用部署

部署架构

双主部署架构提供高可用性，两个MySQL实例互为主从，确保单点故障时服务不中断。

双主配置要点

服务器1配置（192.168.1.10）：

ini
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
[mysqld]
server-id = 1
log-bin = mysql-bin
log_bin_index = mysql-bin.index
log_slave_updates = on
relay-log = mysql-relay-bin
auto_increment_increment = 2
auto_increment_offset = 1
slave_skip_errors = ddl_exist_errors

# 忽略同步的表
replicate-wild-ignore-table=information_schema.%
replicate-wild-ignore-table=performance_schema.%
replicate-wild-ignore-table=cmxt_mail_log.%
replicate-wild-ignore-table=cmxt_mx_log.%

# InnoDB优化配置
innodb_buffer_pool_size = 2G
innodb_log_buffer_size = 15M
innodb_io_capacity = 3000

服务器2配置（192.168.1.11）：

ini
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
[mysqld]
server-id = 2
log-bin = mysql-bin
log_bin_index = mysql-bin.index
log_slave_updates = on
relay-log = mysql-relay-bin
auto_increment_increment = 2
auto_increment_offset = 2
slave_skip_errors = ddl_exist_errors

# 忽略同步的表
replicate-wild-ignore-table=information_schema.%
replicate-wild-ignore-table=performance_schema.%
replicate-wild-ignore-table=cmxt_mail_log.%
replicate-wild-ignore-table=cmxt_mx_log.%

# InnoDB优化配置
innodb_buffer_pool_size = 2G
innodb_log_buffer_size = 15M
innodb_io_capacity = 3000

主从关系建立

1. 创建复制用户

sql
1
2
3
4
5
-- 在服务器1上
mysql> grant replication slave on *.* to 'replication'@'192.168.1.11' identified by 'YOUR_PASSWORD';

-- 在服务器2上
mysql> grant replication slave on *.* to 'replication'@'192.168.1.10' identified by 'YOUR_PASSWORD';

2. 配置主从关系

服务器1配置（指向服务器2）：

sql
1
2
3
4
5
6
7
mysql> change master to 
    -> master_host='192.168.1.11',
    -> master_user='replication',
    -> master_password='YOUR_PASSWORD',
    -> master_port=3308,
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=107;

服务器2配置（指向服务器1）：

sql
1
2
3
4
5
6
7
mysql> change master to 
    -> master_host='192.168.1.10',
    -> master_user='replication',
    -> master_password='YOUR_PASSWORD',
    -> master_port=3308,
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=107;

3. 启动复制

sql
1
mysql> start slave;

数据同步

1. 初始数据同步

从主服务器导出数据：

bash
1
2
3
4
5
# 导出业务数据库
mysqldump --opt --master-data=2 -uapp -pYOUR_PASSWORD -h192.168.1.10 -P3308 cmxt > cmxt_backup.sql

# 导出日志数据库
mysqldump --opt --master-data=2 -uapp -pYOUR_PASSWORD -h192.168.1.10 -P3308 cmxt_log > cmxt_log_backup.sql

导入到备服务器：

bash
1
2
3
4
5
# 导入业务数据库
mysql -uapp -pYOUR_PASSWORD -h192.168.1.11 -P3308 cmxt < cmxt_backup.sql

# 导入日志数据库
mysql -uapp -pYOUR_PASSWORD -h192.168.1.11 -P3308 cmxt_log < cmxt_log_backup.sql

2. 应用授权配置

sql
1
2
3
4
5
6
-- 为业务系统访问授权
GRANT ALL PRIVILEGES ON cmxt_mail.* TO 'app'@'192.168.1.%' IDENTIFIED BY 'YOUR_PASSWORD' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON cmxt_mail_log.* TO 'app'@'192.168.1.%' IDENTIFIED BY 'YOUR_PASSWORD' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON cmxt_mx.* TO 'app'@'192.168.1.%' IDENTIFIED BY 'YOUR_PASSWORD' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON cmxt_mx_log.* TO 'app'@'192.168.1.%' IDENTIFIED BY 'YOUR_PASSWORD' WITH GRANT OPTION;

状态检查

1. 查看复制状态

sql
1
mysql> show slave status\G;

关键指标：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 应该为0或较小数值

2. 监控复制延迟

sql
1
2
3
4
5
-- 查看复制位置
mysql> show master status;

-- 查看从库状态
mysql> show slave status\G;

数据文件损坏的紧急恢复

故障诊断

当发现数据库文件损坏时，需要快速判断损坏程度和影响范围：

sql
1
2
3
4
5
-- 检查表状态
mysql> check table 表名;

-- 检查引擎状态
mysql> show engine innodb status;

紧急修复步骤

1. 临时修复方案

如果存储异常导致分区变成只读状态，可以在my.cnf中添加以下配置：

ini
1
2
[mysqld]
innodb_force_recovery=2

⚠️ 重要提醒：

此参数值尽量不要超过3，否则会导致数据永久丢失
处理前必须做好完整备份
innodb_force_recovery可以设置为1-6，大的数字包含前面所有数字的影响

2. recovery参数详解

ini
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
# 1: 忽略检查到的corrupt页
innodb_force_recovery=1

# 2: 阻止主线程的运行，如主线程需要执行full purge操作，会导致crash
innodb_force_recovery=2

# 3: 不执行事务回滚操作
innodb_force_recovery=3

# 4: 不执行插入缓冲的合并操作
innodb_force_recovery=4

# 5: 不查看重做日志，InnoDB存储引擎会将未提交的事务视为已提交
innodb_force_recovery=5

# 6: 不执行前滚的操作
innodb_force_recovery=6

3. 完整恢复流程

步骤1：停止写入操作

bash
1
2
# 停止所有业务程序，防止进一步损坏
systemctl stop 业务系统

步骤2：导出数据

bash
1
2
3
4
# 导出业务数据库
for db in cmxt cmxt_log; do
    mysqldump -uapp -pYOUR_PASSWORD -h127.0.0.1 -P3308 --opt --hex-blob ${db} > ${BACKUP_DIR}/${db}_$(date +%Y%m%d).sql
done

步骤3：备份现有数据

bash
1
2
# 备份整个MySQL目录
mv /data/mysql /data/mysql_bak_$(date +%Y%m%d)

步骤4：重建数据库

bash
1
2
# 重新初始化数据库
mysql_install_db --defaults-file=/opt/app/conf/my.cnf --datadir=/data/mysql --initialize

步骤5：跳过权限表启动

ini
1
2
[mysqld]
skip-grant-tables

步骤6：设置密码和权限

bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
# 启动数据库
systemctl start mysql

# 设置root密码
mysqladmin -u root password 'YOUR_PASSWORD'

# 授权业务系统用户
mysql -u root -p
mysql> GRANT ALL PRIVILEGES ON cmxt.* TO 'app'@'localhost' IDENTIFIED BY 'YOUR_PASSWORD';
mysql> FLUSH PRIVILEGES;

步骤7：导入数据

bash
1
2
3
4
# 恢复业务数据库
for db in cmxt cmxt_log; do
    mysql -uapp -pYOUR_PASSWORD ${db} < ${BACKUP_DIR}/${db}_$(date +%Y%m%d).sql
done

步骤8：重启业务系统

bash
1
2
# 启动业务系统
systemctl start 业务系统

4. 设置定期备份

bash
1
2
# 添加crontab任务
0 2 * * * /opt/app/scripts/backup_mysql.sh

bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
#!/bin/bash
# backup_mysql.sh

BACKUP_DIR="/data/mysql_backup"
DATE=$(date +%Y%m%d)

# 创建备份目录
mkdir -p ${BACKUP_DIR}

# 备份数据库
mysql -uapp -pYOUR_PASSWORD -e "SHOW DATABASES;" | grep -v "information_schema" | grep -v "performance_schema" | xargs -I {} mysqldump -uapp -pYOUR_PASSWORD --opt --hex-blob {} > ${BACKUP_DIR}/mysql_backup_${DATE}.sql

# 压缩备份
gzip ${BACKUP_DIR}/mysql_backup_${DATE}.sql

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

总结

本文从多个维度详细介绍了MySQL数据库的运维实战经验：

性能优化要点

I/O优化：合理配置读写线程数量，提高磁盘IO利用率
缓存优化：合理设置缓冲池大小和实例数
日志优化：调整事务日志参数，平衡性能与数据安全
异步IO：开启Linux异步IO支持，提升IO性能

监控体系建设

权限管理：遵循最小权限原则，建立完善的权限体系
监控账号：区分本地和远程监控，合理配置权限
状态检查：通过多种命令实时监控系统状态
权限表：了解MySQL权限级别和权限类型

高可用部署

双主架构：实现高可用性，确保服务连续性
配置同步：合理的双主配置参数
数据同步：完整的初始数据同步流程
状态监控：实时监控复制状态和延迟

故障恢复

应急处理：数据文件损坏时的快速响应
恢复策略：从临时修复到完整恢复的完整流程
备份机制：建立完善的定期备份机制
预防措施：通过合理的参数配置预防故障

MySQL运维是一个系统性工程，需要从架构设计、性能优化、监控维护到故障恢复全流程考虑。本文提供的实战经验基于多个生产环境，希望能帮助各位工程师更好地管理和维护MySQL数据库，确保业务系统的稳定运行。记住，好的运维不仅仅是解决问题，更重要的是预防问题的发生。