作为系列的最后一篇，本文将聚焦MySQL的性能调优和运维实战，涵盖参数优化、监控告警、备份恢复、故障排查等核心内容，协助你全面掌握MySQL的运维能力。
性能调优概述
调优层次
┌─────────────────────────────────────────────────────────────┐
│ 应用层 │
│ SQL优化、连接池配置、缓存策略、读写分离 │
├─────────────────────────────────────────────────────────────┤
│ MySQL层 │
│ 参数调优、索引优化、表结构优化、查询缓存 │
├─────────────────────────────────────────────────────────────┤
│ 系统层 │
│ 内存、CPU、磁盘I/O、网络、文件系统 │
├─────────────────────────────────────────────────────────────┤
│ 硬件层 │
│ SSD、更大内存、更多CPU核心、RAID │
└─────────────────────────────────────────────────────────────┘调优原则
- 先测量后优化：使用监控数据定位瓶颈
- 一次改一个：便于评估每项优化的效果
- 优先解决最大瓶颈：遵循二八法则
- 权衡取舍：性能、成本、复杂度的平衡
系统层优化
操作系统参数
文件描述符限制
# 查看当前限制
ulimit -n
# 临时修改
ulimit -n 65535
# 永久修改 /etc/security/limits.conf
mysql soft nofile 65535
mysql hard nofile 65535
mysql soft nproc 65535
mysql hard nproc 65535内核参数优化
# /etc/sysctl.conf
# 网络优化
net.core.somaxconn = 65535
net.core.netdev_max_backlog = 65535
net.ipv4.tcp_max_syn_backlog = 65535
net.ipv4.tcp_fin_timeout = 10
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_keepalive_time = 120
# 内存优化
vm.swappiness = 1 # 尽量不使用swap
vm.dirty_ratio = 10 # 脏页占内存比例
vm.dirty_background_ratio = 5 # 后台刷脏页阈值
# 应用配置
sysctl -p磁盘I/O调度
# 查看当前调度算法
cat /sys/block/sda/queue/scheduler
# SSD推荐使用noop或deadline
echo noop > /sys/block/sda/queue/scheduler
# 永久配置（GRUB）
# /etc/default/grub
GRUB_CMDLINE_LINUX="elevator=noop"文件系统优化
# 使用XFS或ext4
# 挂载参数优化
mount -o noatime,nodiratime,nobarrier /dev/sda1 /data
# /etc/fstab
/dev/sda1 /data xfs defaults,noatime,nodiratime 0 0硬件选择提议
|
组件 |
提议配置 |
说明 |
|
CPU |
多核高频 |
MySQL单线程查询受限于单核性能 |
|
内存 |
尽量大 |
Buffer Pool越大越好 |
|
磁盘 |
NVMe SSD |
IOPS和延迟是关键 |
|
网络 |
万兆网卡 |
主从复制和客户端连接 |
MySQL参数调优
内存相关参数
Buffer Pool配置
[mysqld]
# InnoDB缓冲池大小（推荐物理内存的70-80%）
innodb_buffer_pool_size = 12G
# 缓冲池实例数（大内存时分多个实例减少锁竞争）
innodb_buffer_pool_instances = 8
# 预热：重启时加载热数据
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_pct = 40查看Buffer Pool状态
-- 查看Buffer Pool使用情况
SHOW STATUS LIKE 'Innodb_buffer_pool%';
-- 关键指标
-- Innodb_buffer_pool_read_requests: 读请求次数
-- Innodb_buffer_pool_reads: 从磁盘读取次数
-- 命中率 = 1 - (reads / read_requests)
-- 计算命中率
SELECT
(1 - (
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') /
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
)) * 100 AS buffer_pool_hit_rate;其他内存参数
[mysqld]
# 排序缓冲区（每个连接）
sort_buffer_size = 4M
# 连接缓冲区（每个连接）
join_buffer_size = 4M
# 读取缓冲区
read_buffer_size = 2M
read_rnd_buffer_size = 8M
# 临时表大小
tmp_table_size = 64M
max_heap_table_size = 64M
# 表缓存
table_open_cache = 4000
table_definition_cache = 2000
# 线程缓存
thread_cache_size = 100连接相关参数
[mysqld]
# 最大连接数
max_connections = 500
# 最大用户连接数
max_user_connections = 400
# 连接超时
wait_timeout = 600
interactive_timeout = 600
# 连接错误限制
max_connect_errors = 100000
# 跳过DNS解析
skip_name_resolve = ON监控连接状态
-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';
-- 查看最大使用连接数
SHOW STATUS LIKE 'Max_used_connections';
-- 查看连接详情
SHOW PROCESSLIST;
-- 查看连接来源统计
SELECT
USER,
HOST,
COUNT(*) AS connections
FROM information_schema.PROCESSLIST
GROUP BY USER, HOST
ORDER BY connections DESC;InnoDB相关参数
[mysqld]
# 日志文件大小
innodb_log_file_size = 1G
# 日志缓冲区
innodb_log_buffer_size = 64M
# 刷盘策略（1=每次提交刷盘，最安全）
innodb_flush_log_at_trx_commit = 1
# 刷盘方法
innodb_flush_method = O_DIRECT
# I/O线程
innodb_read_io_threads = 8
innodb_write_io_threads = 8
# I/O容量
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
# 并发线程
innodb_thread_concurrency = 0
# 锁等待超时
innodb_lock_wait_timeout = 50
# 死锁检测
innodb_deadlock_detect = ON
# 自适应哈希索引
innodb_adaptive_hash_index = ON
# 双写缓冲（SSD可关闭）
innodb_doublewrite = ON日志相关参数
[mysqld]
# 慢查询日志
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON
log_throttle_queries_not_using_indexes = 10
# 错误日志
log_error = /var/log/mysql/error.log
log_error_verbosity = 2
# 通用查询日志（生产环境关闭）
general_log = OFF
# Binlog配置
log_bin = mysql-bin
binlog_format = ROW
binlog_row_image = FULL
max_binlog_size = 1G
expire_logs_days = 7
binlog_cache_size = 4M
sync_binlog = 1完整配置示例
[mysqld]
# 基础配置
server_id = 1
port = 3306
socket = /var/lib/mysql/mysql.sock
datadir = /var/lib/mysql
pid-file = /var/run/mysqld/mysqld.pid
user = mysql
# 字符集
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci
# 连接配置
max_connections = 500
max_connect_errors = 100000
wait_timeout = 600
interactive_timeout = 600
skip_name_resolve = ON
# InnoDB配置
default_storage_engine = InnoDB
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_lock_wait_timeout = 50
# 内存配置
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
tmp_table_size = 64M
max_heap_table_size = 64M
table_open_cache = 4000
table_definition_cache = 2000
thread_cache_size = 100
# 日志配置
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_error = /var/log/mysql/error.log
log_bin = mysql-bin
binlog_format = ROW
max_binlog_size = 1G
expire_logs_days = 7
sync_binlog = 1
# GTID复制
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON监控与告警
Performance Schema
启用Performance Schema
[mysqld]
performance_schema = ON
performance_schema_instrument = '%=ON'常用监控查询
-- 1. 查看最耗时的SQL
SELECT
DIGEST_TEXT,
COUNT_STAR AS exec_count,
SUM_TIMER_WAIT/1000000000000 AS total_time_sec,
AVG_TIMER_WAIT/1000000000 AS avg_time_ms,
SUM_ROWS_EXAMINED AS rows_examined,
SUM_ROWS_SENT AS rows_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
-- 2. 查看表I/O统计
SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
COUNT_READ,
COUNT_WRITE,
SUM_TIMER_READ/1000000000 AS read_time_ms,
SUM_TIMER_WRITE/1000000000 AS write_time_ms
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema')
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
-- 3. 查看索引使用情况
SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME,
COUNT_FETCH,
COUNT_INSERT,
COUNT_UPDATE,
COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema')
AND INDEX_NAME IS NOT NULL
ORDER BY COUNT_FETCH DESC
LIMIT 20;
-- 4. 查看锁等待
SELECT
r.trx_id AS waiting_trx_id,
r.trx_mysql_thread_id AS waiting_thread,
r.trx_query AS waiting_query,
b.trx_id AS blocking_trx_id,
b.trx_mysql_thread_id AS blocking_thread,
b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;
-- 5. 查看内存使用
SELECT
EVENT_NAME,
CURRENT_NUMBER_OF_BYTES_USED/1024/1024 AS current_mb,
HIGH_NUMBER_OF_BYTES_USED/1024/1024 AS high_mb
FROM performance_schema.memory_summary_global_by_event_name
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC
LIMIT 10;sys Schema
MySQL 5.7+内置的sys schema提供了更友善的监控视图。
-- 1. 查看最耗时的语句
SELECT * FROM sys.statement_analysis LIMIT 10;
-- 2. 查看未使用的索引
SELECT * FROM sys.schema_unused_indexes;
-- 3. 查看冗余索引
SELECT * FROM sys.schema_redundant_indexes;
-- 4. 查看表统计
SELECT * FROM sys.schema_table_statistics LIMIT 10;
-- 5. 查看I/O统计
SELECT * FROM sys.io_global_by_file_by_bytes LIMIT 10;
-- 6. 查看等待事件
SELECT * FROM sys.wait_classes_global_by_avg_latency;
-- 7. 查看用户统计
SELECT * FROM sys.user_summary;
-- 8. 查看主机统计
SELECT * FROM sys.host_summary;
-- 9. 查看内存使用
SELECT * FROM sys.memory_global_total;
-- 10. 查看当前运行的语句
SELECT * FROM sys.session WHERE command != 'Sleep';监控指标清单
关键指标
|
指标 |
获取方式 |
告警阈值 |
|
QPS |
SHOW STATUS LIKE 'Queries' |
根据基线 |
|
TPS |
SHOW STATUS LIKE 'Com_commit' |
根据基线 |
|
连接数 |
SHOW STATUS LIKE 'Threads_connected' |
> 80% max_connections |
|
慢查询数 |
SHOW STATUS LIKE 'Slow_queries' |
持续增长 |
|
Buffer Pool命中率 |
计算公式 |
< 99% |
|
复制延迟 |
SHOW REPLICA STATUS |
> 60秒 |
|
锁等待 |
SHOW STATUS LIKE 'Innodb_row_lock_waits' |
持续增长 |
|
死锁 |
SHOW STATUS LIKE 'Innodb_deadlocks' |
任意增长 |
监控脚本示例
#!/bin/bash
# mysql_monitor.sh
MYSQL_USER="monitor"
MYSQL_PASS="monitor123"
MYSQL_HOST="localhost"
# 获取关键指标
mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST -N -e "
SELECT
'connections' AS metric, VARIABLE_VALUE AS value
FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Threads_connected'
UNION ALL
SELECT
'qps', VARIABLE_VALUE
FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Queries'
UNION ALL
SELECT
'slow_queries', VARIABLE_VALUE
FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Slow_queries'
UNION ALL
SELECT
'innodb_rows_read', VARIABLE_VALUE
FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_rows_read'
UNION ALL
SELECT
'innodb_rows_inserted', VARIABLE_VALUE
FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_rows_inserted';
"Prometheus + Grafana监控
安装mysqld_exporter
# 下载
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.0/mysqld_exporter-0.15.0.linux-amd64.tar.gz
# 解压
tar -xzf mysqld_exporter-0.15.0.linux-amd64.tar.gz
# 创建MySQL监控用户
mysql -e "
CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'exporter123';
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';
"
# 配置文件
cat > /etc/mysqld_exporter.cnf << EOF
[client]
user=exporter
password=exporter123
EOF
# 启动
./mysqld_exporter --config.my-cnf=/etc/mysqld_exporter.cnfPrometheus配置
# prometheus.yml
scrape_configs:
- job_name: 'mysql'
static_configs:
- targets: ['localhost:9104']常用Grafana Dashboard
- MySQL Overview: Dashboard ID 7362
- MySQL InnoDB Metrics: Dashboard ID 7991
备份与恢复
备份策略
|
备份类型 |
工具 |
优点 |
缺点 |
|
逻辑备份 |
mysqldump |
跨版本兼容 |
速度慢 |
|
物理备份 |
xtrabackup |
速度快 |
版本敏感 |
|
快照备份 |
LVM/存储 |
最快 |
依赖存储 |
mysqldump备份
基本用法
# 备份单个数据库
mysqldump -u root -p mydb > mydb_backup.sql
# 备份多个数据库
mysqldump -u root -p --databases db1 db2 > dbs_backup.sql
# 备份所有数据库
mysqldump -u root -p --all-databases > all_backup.sql
# 只备份表结构
mysqldump -u root -p --no-data mydb > mydb_schema.sql
# 只备份数据
mysqldump -u root -p --no-create-info mydb > mydb_data.sql生产环境备份
#!/bin/bash
# backup.sh
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backup/mysql"
MYSQL_USER="backup"
MYSQL_PASS="backup123"
# 创建备份目录
mkdir -p $BACKUP_DIR
# 全量备份（推荐参数）
mysqldump
-u$MYSQL_USER
-p$MYSQL_PASS
--all-databases
--single-transaction
--routines
--triggers
--events
--master-data=2
--flush-logs
--hex-blob
--set-gtid-purged=ON
| gzip > $BACKUP_DIR/full_backup_$DATE.sql.gz
# 保留最近7天备份
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete
echo "Backup completed: $BACKUP_DIR/full_backup_$DATE.sql.gz"恢复数据
# 恢复全库
gunzip < full_backup.sql.gz | mysql -u root -p
# 恢复单个数据库
mysql -u root -p mydb < mydb_backup.sql
# 恢复时忽略错误
mysql -u root -p --force < backup.sqlXtrabackup备份
安装Xtrabackup
# Ubuntu/Debian
apt-get install percona-xtrabackup-80
# CentOS/RHEL
yum install percona-xtrabackup-80全量备份
# 执行全量备份
xtrabackup --backup
--user=root
--password=root123
--target-dir=/backup/full
# 准备备份（应用redo log）
xtrabackup --prepare --target-dir=/backup/full增量备份
# 基于全量备份的增量备份
xtrabackup --backup
--user=root
--password=root123
--target-dir=/backup/inc1
--incremental-basedir=/backup/full
# 第二次增量备份
xtrabackup --backup
--user=root
--password=root123
--target-dir=/backup/inc2
--incremental-basedir=/backup/inc1恢复增量备份
# 1. 准备全量备份（只应用已提交事务）
xtrabackup --prepare --apply-log-only --target-dir=/backup/full
# 2. 合并第一个增量
xtrabackup --prepare --apply-log-only
--target-dir=/backup/full
--incremental-dir=/backup/inc1
# 3. 合并第二个增量（最后一个不用--apply-log-only）
xtrabackup --prepare
--target-dir=/backup/full
--incremental-dir=/backup/inc2
# 4. 恢复数据
systemctl stop mysqld
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backup/full
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld自动化备份脚本
#!/bin/bash
# xtrabackup_full_inc.sh
BACKUP_BASE="/backup/mysql"
FULL_BACKUP_DAY="Sunday" # 每周日全量备份
TODAY=$(date +%A)
DATE=$(date +%Y%m%d)
MYSQL_USER="backup"
MYSQL_PASS="backup123"
# 判断是否需要全量备份
if [ "$TODAY" = "$FULL_BACKUP_DAY" ] || [ ! -d "$BACKUP_BASE/full" ]; then
# 全量备份
rm -rf $BACKUP_BASE/full $BACKUP_BASE/inc*
xtrabackup --backup
--user=$MYSQL_USER
--password=$MYSQL_PASS
--target-dir=$BACKUP_BASE/full
xtrabackup --prepare --apply-log-only --target-dir=$BACKUP_BASE/full
echo "Full backup completed"
else
# 增量备份
# 找到最新的备份目录
LATEST=$(ls -td $BACKUP_BASE/inc* 2>/dev/null | head -1)
if [ -z "$LATEST" ]; then
LATEST="$BACKUP_BASE/full"
fi
INC_DIR="$BACKUP_BASE/inc_$DATE"
xtrabackup --backup
--user=$MYSQL_USER
--password=$MYSQL_PASS
--target-dir=$INC_DIR
--incremental-basedir=$LATEST
echo "Incremental backup completed: $INC_DIR"
fi基于Binlog的时间点恢复
# 1. 恢复全量备份
mysql -u root -p < full_backup.sql
# 2. 查看binlog位置（从备份文件中获取）
head -30 full_backup.sql | grep "CHANGE MASTER"
# -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=785;
# 3. 应用binlog到指定时间点
mysqlbinlog
--start-position=785
--stop-datetime="2024-01-15 10:30:00"
mysql-bin.000010 mysql-bin.000011
| mysql -u root -p
# 或者跳过某个错误的事务
mysqlbinlog
--start-position=785
--stop-position=1234
mysql-bin.000010
| mysql -u root -p
mysqlbinlog
--start-position=1500
mysql-bin.000010
| mysql -u root -p故障排查
常见问题排查
问题一：连接数过多
-- 查看当前连接
SHOW PROCESSLIST;
-- 查看连接来源
SELECT
USER, HOST, COUNT(*)
FROM information_schema.PROCESSLIST
GROUP BY USER, HOST
ORDER BY COUNT(*) DESC;
-- 杀死空闲连接
SELECT
CONCAT('KILL ', ID, ';')
FROM information_schema.PROCESSLIST
WHERE COMMAND = 'Sleep' AND TIME > 300;解决方案：
- 增加max_connections
- 优化应用连接池配置
- 设置合理的wait_timeout
问题二：CPU使用率高
-- 查看正在执行的SQL
SELECT * FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep'
ORDER BY TIME DESC;
-- 查看耗时最长的SQL
SELECT * FROM sys.statement_analysis
ORDER BY avg_latency DESC
LIMIT 10;解决方案：
- 优化慢SQL
- 添加合适索引
- 减少复杂查询
问题三：磁盘I/O高
-- 查看I/O状态
SHOW ENGINE INNODB STATUSG
-- 查看文件I/O
SELECT * FROM sys.io_global_by_file_by_bytes
ORDER BY total DESC
LIMIT 10;
-- 查看等待I/O的线程
SELECT * FROM sys.io_global_by_wait_by_latency;解决方案：
- 增加innodb_buffer_pool_size
- 使用SSD
- 优化innodb_io_capacity
问题四：锁等待
-- 查看当前锁等待
SELECT * FROM sys.innodb_lock_waitsG
-- 查看锁信息
SELECT * FROM performance_schema.data_locks;
-- 查看锁等待
SELECT * FROM performance_schema.data_lock_waits;
-- 查看事务
SELECT * FROM information_schema.INNODB_TRX;解决方案：
- 优化事务，减少锁持有时间
- 使用合适的索引减少锁范围
- 调整事务隔离级别
问题五：死锁
-- 查看最近死锁信息
SHOW ENGINE INNODB STATUSG
-- 查找 LATEST DETECTED DEADLOCK 部分
-- 查看死锁次数
SHOW STATUS LIKE 'Innodb_deadlocks';解决方案：
- 保持一致的加锁顺序
- 减小事务粒度
- 使用合适的索引
问题六：复制延迟
-- 查看复制状态
SHOW REPLICA STATUSG
-- 关注字段
-- Seconds_Behind_Source: 延迟秒数
-- Relay_Log_Space: 中继日志大小解决方案：
- 增加并行复制线程
- 优化从库硬件
- 减少大事务
故障排查工具
pt-query-digest
分析慢查询日志：
# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log > slow_report.txt
# 分析最近1小时的慢查询
pt-query-digest
--since '1h'
/var/log/mysql/slow.log
# 输出到数据库
pt-query-digest
--review h=localhost,D=slow_query,t=query_review
/var/log/mysql/slow.logpt-table-checksum
检查主从数据一致性：
pt-table-checksum
--host=master
--user=root
--password=xxx
--databases=mydb
--replicate=percona.checksumspt-table-sync
修复主从数据不一致：
pt-table-sync
--execute
--sync-to-master
h=slave,D=mydb,t=userspt-online-schema-change
在线DDL：
pt-online-schema-change
--alter "ADD COLUMN new_col INT"
D=mydb,t=users
--execute安全加固
账户安全
-- 1. 删除匿名用户
DELETE FROM mysql.user WHERE User = '';
-- 2. 删除测试数据库
DROP DATABASE IF EXISTS test;
-- 3. 限制root远程登录
DELETE FROM mysql.user WHERE User = 'root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
-- 4. 设置强密码策略
SET GLOBAL validate_password.policy = STRONG;
SET GLOBAL validate_password.length = 12;
-- 5. 创建应用专用账户
CREATE USER 'app'@'192.168.1.%' IDENTIFIED BY 'StrongP@ssw0rd!';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app'@'192.168.1.%';
-- 6. 刷新权限
FLUSH PRIVILEGES;网络安全
[mysqld]
# 绑定特定IP
bind-address = 192.168.1.100
# 禁用本地文件加载
local_infile = OFF
# 禁用符号链接
symbolic-links = 0
# 启用SSL
require_secure_transport = ON
ssl_ca = /etc/mysql/ssl/ca.pem
ssl_cert = /etc/mysql/ssl/server-cert.pem
ssl_key = /etc/mysql/ssl/server-key.pem审计日志
[mysqld]
# 安装审计插件
plugin-load-add = audit_log.so
# 审计配置
audit_log_file = /var/log/mysql/audit.log
audit_log_format = JSON
audit_log_policy = ALL运维自动化
日常巡检脚本
#!/bin/bash
# daily_check.sh
MYSQL_USER="monitor"
MYSQL_PASS="monitor123"
echo "========== MySQL Daily Check =========="
echo "Date: $(date)"
echo ""
# 1. 服务状态
echo "=== Service Status ==="
systemctl status mysqld | head -5
# 2. 连接数
echo ""
echo "=== Connections ==="
mysql -u$MYSQL_USER -p$MYSQL_PASS -N -e "
SELECT
'Current Connections:', VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Threads_connected'
UNION ALL
SELECT
'Max Used Connections:', VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Max_used_connections';
"
# 3. 慢查询
echo ""
echo "=== Slow Queries (Last 24h) ==="
mysql -u$MYSQL_USER -p$MYSQL_PASS -N -e "
SELECT COUNT(*) AS slow_query_count
FROM mysql.slow_log
WHERE start_time > DATE_SUB(NOW(), INTERVAL 24 HOUR);
"
# 4. 磁盘使用
echo ""
echo "=== Disk Usage ==="
mysql -u$MYSQL_USER -p$MYSQL_PASS -N -e "
SELECT
table_schema AS 'Database',
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC
LIMIT 10;
"
# 5. 复制状态（如果是从库）
echo ""
echo "=== Replication Status ==="
mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW REPLICA STATUSG" 2>/dev/null | grep -E "Replica_IO_Running|Replica_SQL_Running|Seconds_Behind"
# 6. InnoDB状态
echo ""
echo "=== InnoDB Buffer Pool ==="
mysql -u$MYSQL_USER -p$MYSQL_PASS -N -e "
SELECT
ROUND(@@innodb_buffer_pool_size/1024/1024/1024, 2) AS 'Buffer Pool Size (GB)',
ROUND((1 - (
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') /
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
)) * 100, 2) AS 'Hit Rate (%)';
"
echo ""
echo "========== Check Complete =========="告警脚本
#!/bin/bash
# mysql_alert.sh
MYSQL_USER="monitor"
MYSQL_PASS="monitor123"
ALERT_EMAIL="dba@company.com"
# 阈值配置
MAX_CONNECTIONS_THRESHOLD=80
REPLICATION_LAG_THRESHOLD=60
BUFFER_POOL_HIT_THRESHOLD=99
# 获取指标
CONNECTIONS=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -N -e "SHOW STATUS LIKE 'Threads_connected'" | awk '{print $2}')
MAX_CONNECTIONS=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -N -e "SHOW VARIABLES LIKE 'max_connections'" | awk '{print $2}')
CONNECTION_RATIO=$((CONNECTIONS * 100 / MAX_CONNECTIONS))
# 检查连接数
if [ $CONNECTION_RATIO -gt $MAX_CONNECTIONS_THRESHOLD ]; then
echo "ALERT: Connection usage at ${CONNECTION_RATIO}%" | mail -s "MySQL Alert: High Connections" $ALERT_EMAIL
fi
# 检查复制延迟
REPLICATION_LAG=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -N -e "SHOW REPLICA STATUSG" 2>/dev/null | grep "Seconds_Behind_Source" | awk '{print $2}')
if [ -n "$REPLICATION_LAG" ] && [ "$REPLICATION_LAG" != "NULL" ] && [ $REPLICATION_LAG -gt $REPLICATION_LAG_THRESHOLD ]; then
echo "ALERT: Replication lag is ${REPLICATION_LAG} seconds" | mail -s "MySQL Alert: Replication Lag" $ALERT_EMAIL
fi总结
本文作为MySQL系列的收官之作，全面介绍了性能调优和运维实战：
- 系统层优化：内核参数、文件系统、磁盘I/O
- MySQL参数调优：Buffer Pool、连接、InnoDB、日志
- 监控告警：Performance Schema、sys Schema、Prometheus
- 备份恢复：mysqldump、Xtrabackup、时间点恢复
- 故障排查：连接、CPU、I/O、锁、死锁、复制延迟
- 安全加固：账户、网络、审计
- 运维自动化：巡检脚本、告警脚本
系列回顾
至此，”深入学习MySQL：从新手到高手”系列8篇文章全部完成：
|
篇数 |
主题 |
核心内容 |
|
第1篇 |
架构与存储引擎 |
MySQL整体架构、InnoDB内部结构 |
|
第2篇 |
索引原理与优化 |
B+树、索引类型、索引设计 |
|
第3篇 |
SQL优化与执行计划 |
EXPLAIN、慢查询、SQL优化技巧 |
|
第4篇 |
事务与锁机制 |
ACID、隔离级别、MVCC、锁类型 |
|
第5篇 |
日志系统与数据恢复 |
redo log、undo log、binlog |
|
第6篇 |
主从复制与读写分离 |
复制原理、GTID、读写分离方案 |
|
第7篇 |
高可用与分库分表 |
MHA、MGR、ShardingSphere |
|
第8篇 |
性能调优与运维 |
参数优化、监控、备份、故障排查 |
希望这个系列能协助你系统性地掌握MySQL，在实际工作中游刃有余！
© 版权声明
文章版权归作者所有，未经允许请勿转载。
相关文章
暂无评论...
