入职这些年我发现自己干得最多的活不是写业务代码也不是搭架构而是跟一堆数据库常用脚本打交道。无论是帮业务部门查一条慢SQL还是半夜被叫起来看主从延迟手里没几个趁手的脚本真就寸步难行。今天这篇不整虚的把我实际工作中反复在用、踩过坑之后修正过的脚本整理出来按场景分类讲清楚每个脚本为什么这么写、参数怎么调、遇到什么问题怎么改都一并交代。适合刚接触数据库运维的开发者也适合想把手头脚本整理得更规范的DBA。1. 信息查询与元数据脚本先把家底盘清楚1.1 三分钟摸清实例整体状况接手一台新的数据库服务器第一件事不是急着优化而是先搞清楚这台实例到底什么配置、什么负载、什么水位。我习惯先跑一套体检三连-- 查看实例版本、运行时长、字符集等基础信息 SELECT VERSION() AS version, NOW() AS current_time, hostname AS host_name, port AS port, character_set_server AS charset, collation_server AS collation; -- 查看关键运行状态值重点看连接数和慢查询 SHOW GLOBAL STATUS LIKE Threads_connected; SHOW GLOBAL STATUS LIKE Threads_running; SHOW GLOBAL STATUS LIKE Slow_queries; SHOW GLOBAL STATUS LIKE Uptime; -- 查看InnoDB buffer pool命中率相关指标 SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read_requests; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_reads;这几个脚本看着简单但信息密度很高。版本和端口就不说了Threads_connected和Threads_running这两个值一定要配合看前者代表当前建立的连接数后者代表真正在执行SQL的线程数。如果连接数飙到几百但Threads_running一直不高说明大部分连接是闲置连接问题多半出在应用侧连接池配置上而不是数据库本身。InnoDB buffer pool命中率有个经验公式1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)这个值常年低于99%说明内存配置或查询模式有问题光靠加内存未必管用可能得从SQL层面找原因。1.2 快速定位大表与膨胀空间刚接手一个库业务方问我哪些表最大、哪些表膨胀得厉害我从来不看information_schema因为那个统计信息在数据量大的时候本身就不准扫描也慢。直接用系统表查快得多-- 查看指定库中各表的行数、数据大小、索引大小MySQL 8.0 SELECT table_name, table_rows, 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 FROM information_schema.tables WHERE table_schema your_db ORDER BY total_mb DESC LIMIT 20;这里有个细节必须提醒table_rows是估算值InnoDB引擎下误差可能很大尤其频繁增删的表。真要精确行数还是得SELECT COUNT(*)但大表别在生产上直接跑低峰期再操作。另外我还会加一个查询看哪些表的数据量和索引量比例失调。曾遇到一张表数据才200MB索引却占了1.5GB查下来是建了一堆冗余的联合索引删掉三个索引后空间直接释放写入性能也提升了。索引不是越多越好这个后面专门讲。1.3 找出从未被使用的索引索引是有维护成本的每次写入都要更新索引结构。很多系统上线久了当初拍脑袋建的索引根本没人用白白拖累写入性能。查索引使用情况MySQL有现成的统计表-- 查看索引使用统计需开启performance_schemaMySQL 5.7 SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR, COUNT_READ, COUNT_WRITE FROM performance_schema.table_io_waits_summary_by_index_usage WHERE OBJECT_SCHEMA your_db AND INDEX_NAME IS NOT NULL ORDER BY COUNT_READ ASC LIMIT 20;COUNT_READ长期为0的索引基本就是死索引。但删之前务必确认两点第一查询计划里是否真的没用到有些统计值低但偶尔高峰用到的索引不能动第二有没有唯一性约束依赖这个索引。我习惯把候选索引先标记为INVISIBLEMySQL 8.0支持观察一周确认没有报错再真正DROP这个操作可以说非常稳妥。2. 备份与恢复脚本平时用不上出事保命2.1 逻辑备份脚本的正确姿势说到备份很多人第一反应就是mysqldump一把梭。但我见过太多因为参数没加对导致备份恢复失败的案例。我最常用的一套逻辑备份脚本长这样#!/bin/bash # 按库备份保留最近7天带时间戳 BACKUP_DIR/data/backup/mysql DATE$(date %Y%m%d_%H%M%S) DB_USERbackup_user DB_PASSyour_password DB_LISTdb1 db2 db3 for DB in $DB_LIST; do mysqldump \ --single-transaction \ --routines \ --triggers \ --events \ --set-gtid-purgedOFF \ -u$DB_USER -p$DB_PASS $DB \ | gzip $BACKUP_DIR/${DB}_${DATE}.sql.gz done # 清理7天前的备份 find $BACKUP_DIR -name *.sql.gz -mtime 7 -delete这几个参数每个都有讲究--single-transaction不加这个InnoDB表备份时会对全表加锁线上业务直接卡住。加上后基于MVCC一致性快照不锁表。--routines --triggers --events不加这三个存储过程、触发器、事件调度器全部丢失。很多人恢复后发现跑批任务不见了就是栽在这。--set-gtid-purgedOFFGTID模式下的老坑。不关掉这个问题备份文件里带着GTID信息恢复到新实例时会因为事务ID冲突直接报错。注意--single-transaction对MyISAM表无效MyISAM依旧会锁表。如果库里还有MyISAM表建议要么迁移到InnoDB要么备份安排在业务低谷期执行。2.2 更稳的物理备份xtrabackupmysqldump在数据量超过几十GB时就不太合适了备份慢、恢复更慢而且逻辑备份恢复时重放SQL耗时很长。这时候我直接用物理备份工具做全量增量备份#!/bin/bash # 全量物理备份脚本 BACKUP_BASE/data/backup/xtra DATE$(date %Y%m%d) FULL_DIR$BACKUP_BASE/full_$DATE xtrabackup \ --backup \ --target-dir$FULL_DIR \ --userbackup_user \ --passwordyour_password \ --parallel4 \ --compress # 备份完成后再做一步prepare保证备份集可用于恢复 xtrabackup --prepare --target-dir$FULL_DIR很多人备份完直接拷走恢复的时候才发现xtrabackup --prepare这步没做数据文件处于不一致状态根本起不来。--prepare的原理类似InnoDB崩溃恢复把redo log回放到数据文件里这步必须执行且prepare后的备份集不能再增量备份。物理备份的恢复动作也比逻辑备份简单直接把备份集解压拷回数据目录改好属主权限启动实例即可。我实测过100GB的数据xtrabackup备份加恢复全程不到半小时mysqldump可能要折腾两个小时以上。2.3 备份不等于高枕无忧恢复演练脚本这是我最想强调的一点不做恢复演练的备份等于没有备份。遇到过不止一次平时备份任务天天跑真到恢复那天发现备份文件损坏、参数不兼容、磁盘空间不够全抓瞎。我现在的习惯是每月至少做一次完整的恢复演练脚本固定如下#!/bin/bash # 恢复到临时实例并校验数据完整性 RESTORE_DIR/data/restore_test PORT3307 DATA_DIR$RESTORE_DIR/data # 1. 解压最新全量备份 LATEST_BACKUP$(ls -t /data/backup/mysql/*.sql.gz | head -1) gunzip -c $LATEST_BACKUP $RESTORE_DIR/restore.sql # 2. 直接初始化一个新实例 mysqld --no-defaults \ --datadir$DATA_DIR \ --port$PORT \ --socket$RESTORE_DIR/mysql.sock \ --initialize-insecure # 3. 导入数据 mysql --socket$RESTORE_DIR/mysql.sock -uroot $RESTORE_DIR/restore.sql # 4. 关键表行数校验 mysql --socket$RESTORE_DIR/mysql.sock -uroot -e SELECT COUNT(*) FROM your_db.key_table;恢复演练不只是验证备份文件可用还能顺便验证备份脚本里的参数变化是否兼容当前版本。比如之前从MySQL 5.7升级到8.0后老的备份恢复脚本就因为sql_mode差异跑挂过演练第一个发现问题。3. 巡检与监控脚本日常保健胜过临时抢救3.1 连接与线程状态实时巡检数据库出问题十有八九先反应在连接和线程上。我写了一个一键巡检脚本每分钟跑一次把异常情况直接打到日志里-- 检查当前连接数是否超过阈值这里是200 SHOW STATUS WHERE variable_name Threads_connected; -- 查看当前所有非Sleep状态的SQL SELECT id, user, host, db, command, time, state, LEFT(info, 120) AS sql_text FROM information_schema.processlist WHERE command ! Sleep ORDER BY time DESC; -- 查看处于锁等待的会话 SELECT * FROM information_schema.innodb_trx WHERE trx_state LOCK WAIT;information_schema.processlist这个表是排查问题的第一现场。看到time特别长的Query别急着KILL先看state字段如果是Sending data可能真的是大查询如果是Waiting for table metadata lock多半是DDL和DML打架了如果是Waiting for handler commit可能是磁盘刷盘太慢。有一次线上业务卡死我用这个脚本查出来一个time2400的ALTER TABLE在等元数据锁后面堵了一串查询。找到根源后把那个一直没提交的长事务KILL掉业务立刻恢复。没有这个巡检思路光看连接数飚高是定位不到根因的。3.2 慢查询与错误日志分析脚本慢查询日志一定要开但开了不分析等于白开。我习惯把慢查询日志和错误日志都统一到一个目录然后用脚本做每日分析#!/bin/bash # 每日慢查询统计输出Top10耗时SQL SLOW_LOG/var/log/mysql/slow-query.log TODAY$(date %Y%m%d) # 统计当天慢查询总数 TOTAL$(grep -c Time: $SLOW_LOG || true) echo $TODAY slow query total: $TOTAL # 提取慢查询中的SQL文本统计出现频率 grep -A2 Query_time $SLOW_LOG | \ grep ^SELECT\|^UPDATE\|^DELETE\|^INSERT | \ sort | uniq -c | sort -rn | head -20这个脚本的思路是先看量再看质。慢查询多不一定是坏事要看是否集中在少数几条SQL上。如果每天都有一批SQL稳定出现在慢查询列表且执行计划没变说明SQL本身有问题需要从索引和改写两个方向处理。有个经验分享慢查询日志里的Rows_examined远比Query_time重要。Rows_examined几十万但只返回几行说明扫描了大量数据索引没走对或者索引选择性差。优化这类SQL收益大且确定。3.3 磁盘与表空间水位监控数据库的磁盘满了是真的会写不进去任何数据的而且一旦满盘恢复起来极其痛苦。所以磁盘监控我宁可多写几个脚本也不愿意半夜被叫起来。#!/bin/bash # 磁盘与关键目录监控超过80%告警 THRESHOLD80 ALERT_FILE/data/monitor/disk_alert.log df -h | grep -E ^/dev/(sd|vd) | while read line; do USAGE$(echo $line | awk {print $5} | sed s/%//) MOUNT_POINT$(echo $line | awk {print $6}) if [ $USAGE -ge $THRESHOLD ]; then echo $(date %Y-%m-%d %H:%M:%S) WARN: $MOUNT_POINT usage $USAGE% $ALERT_FILE fi done # InnoDB表空间大小分布Top10 mysql -u monitor_user -p$PASSWORD -e SELECT table_schema, SUM(data_length index_length) / 1024 / 1024 / 1024 AS total_gb FROM information_schema.tables GROUP BY table_schema ORDER BY total_gb DESC; $ALERT_FILE告警阈值80%是我个人的习惯不一定适合所有人。如果业务有大量临时排序或日志写入建议把阈值调到70%给临时文件留出缓冲。数据目录和日志目录最好分开监控binlog目录涨速快一天几个GB很正常单独看才不会忽略。另外提醒一个容易忽略的点监控脚本本身也要做清理监控日志文件无限增长会把磁盘反过来写满。logrotate配一下或者用find ... -mtime 30 -delete做轮转。4. 性能优化辅助脚本找到瓶颈、对症下药4.1 直击性能瓶颈的SQL定位线上系统变慢最忌讳的就是凭感觉调参数。我的做法是先把压在库上的SQL揪出来分析执行计划再决定怎么改。下面这个脚本是我最常用的SQL分析三板斧-- 1. 开启执行计划分析 EXPLAIN ANALYZE SELECT order_id, user_id, amount FROM orders WHERE user_id 12345 AND status PAID AND create_time 2024-01-01 ORDER BY create_time DESC LIMIT 20;EXPLAIN ANALYZE是MySQL 8.0才有的它不只是给出执行计划还会真实执行SQL并返回每一步的耗时和扫描行数。对于一条慢SQL先用它看瓶颈在哪一步是索引扫描行数太多还是排序耗时太长还是回表次数太多。我遇到过一条SQL加了个索引之后从2秒提升到50毫秒以为是索引生效了但用EXPLAIN ANALYZE一查发现真正的问题是ORDER BY create_time DESC触发了filesort。后来把索引改成(user_id, status, create_time)让索引天然有序彻底消除filesort才真正解决问题。没有EXPLAIN ANALYZE我只能看到加索引变快了看不到完整的优化链路。4.2 大表碎片整理脚本InnoDB表在频繁更新和删除后会产生大量碎片。表现就是表的数据量没怎么涨但物理文件越来越大全表扫描越来越慢。整理碎片的方法很直接-- 查看表碎片率 SELECT table_name, ROUND(data_length / 1024 / 1024, 2) AS data_mb, ROUND(data_free / 1024 / 1024, 2) AS fragmented_mb, ROUND(data_free / data_length * 100, 2) AS fragment_pct FROM information_schema.tables WHERE table_schema your_db AND data_free 100 * 1024 * 1024 -- 只列出碎片超过100MB的表 ORDER BY fragment_pct DESC LIMIT 20;对于碎片率高的表执行ALTER TABLE table_name ENGINEInnoDB即可重建表、回收碎片空间。但这里有几个必须注意的坑这个操作会锁表尽管MySQL 5.6是Online DDL但数据量巨大的表仍可能引发长时间元数据锁务必安排在维护窗口。整理碎片前先看磁盘剩余空间重建表需要约等于原表大小的额外空间。MySQL 8.0之后这个动作可以用ALTER TABLE ... FORCE代替效果更明确。有个取巧的办法如果表上有定期归档任务归档后碎片自然产生建议把碎片整理加入每月的自动任务比一次性整理大表风险低得多。4.3 关键参数调优自查脚本网上到处是MySQL优化必须改这10个参数的文章但盲目调参翻车的案例我见得太多了。参数调优最靠谱的依据是自己的监控数据我写了一个自查脚本输出当前实际运行情况对应的参数建议-- 查看缓存命中率的真实数据 SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read%; SHOW GLOBAL STATUS LIKE Innodb_row_lock_current_waits; SHOW GLOBAL STATUS LIKE Innodb_row_lock_time; -- 查看当前实际生效的参数值 SHOW VARIABLES LIKE innodb_buffer_pool_size; SHOW VARIABLES LIKE innodb_flush_log_at_trx_commit; SHOW VARIABLES LIKE max_connections; SHOW VARIABLES LIKE transaction_isolation;这里我最想展开讲的是innodb_flush_log_at_trx_commit。这个参数只有三个取值0、1、2很多优化文章建议改成0或2来提升性能但代价是事务提交时可能丢失最多1秒的日志这在金融、订单等场景是绝对不可接受的。我的原则是核心业务必须保持1非核心报表库可以考虑20绝不推荐。调参之前先问自己三个问题改这个参数要解决的问题是什么改完如何验证确实解决了回滚方案是什么答不上来任何一个就不要动。5. 常见问题与排查技巧实录5.1 连接数打满排查现象Threads_connected达到max_connections上限新连接直接报Too many connections。排查顺序先看Threads_running如果这个值不高说明大量连接是Sleep状态应用连接池没释放。查information_schema.processlist看Sleep连接来自哪些host定位是哪个应用。看应用连接池配置maxPoolSize是不是设置得过大、idleTimeout是不是没生效。如果是突发流量导致临时调大max_connections能救急但根治要看应用侧连接复用。我以前遇到过一个案例某服务每执行一次查询就新建连接用完不归还连接池形同虚设。排查完发现是应用框架的配置项被误改了maximumPoolSize写成了1000而数据库max_connections才500必然打满。脚本只是定位工具根因分析还得靠对业务侧的理解。5.2 锁等待问题处理现象业务报错里出现Lock wait timeout exceeded或者Deadlock found。处理思路-- 查看当前所有事务 SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id FROM information_schema.innodb_trx; -- 查看锁等待关系 SELECT waiting_trx_id, waiting_thread, waiting_query, blocking_trx_id, blocking_thread, blocking_query FROM sys.innodb_lock_waits;sys.innodb_lock_waits是MySQL 5.7及以上版本提供的视图直接列出谁在等锁、谁持有锁。优先处理持有锁的那个长事务如果业务上允许直接把它KILL掉。但KILL是治标根源通常在两条事务没及时提交应用层忘记commit、或者事务范围过大一个事务里做了太多查询和计算。死锁的话MySQL会自动回滚其中一个事务应用侧要有重试机制。这个事情别依赖数据库帮你想办法应用层做重试才是正解。5.3 主从延迟排查主从延迟是另一种常见的数据库没事但业务很慢的场景。查主从状态用下面的脚本SHOW SLAVE STATUS\G -- 关键字段 -- Seconds_Behind_Master: 从库落后主库的秒数 -- Slave_IO_Running: IO线程是否正常必须是Yes -- Slave_SQL_Running: SQL线程是否正常必须是Yes -- Last_IO_Error / Last_SQL_Error: 有值必查这是报错现场Seconds_Behind_Master为0不代表没延迟它只是估算值主库大事务执行期间这个值可能显示0因为从库根本没追上主库当前的binlog位置。更可靠的判断标准是对比主从的Master_Log_File/Read_Master_Log_Pos与从库执行到的Relay_Master_Log_File/Exec_Master_Log_Pos。主从延迟最常见的原因按优先级排列大事务一条SQL更新几百万行、从库硬件性能弱、从库上跑着分析查询抢资源、DDL在从库被打断。解决方向也很明确把大事务拆小、控制从库上的分析负载、保障主从硬件规格一致。5.4 快速定位性能拐点最后一个脚本也是我调优时候最重要的参考依据通过performance_schema拿到真实的等待事件分布。-- 等待事件Top10MySQL 5.7 SELECT EVENT_NAME, COUNT_STAR, ROUND(SUM_TIMER_WAIT / 1000000000, 2) AS wait_seconds FROM performance_schema.events_waits_summary_global_by_event_name WHERE SUM_TIMER_WAIT 0 ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;看到wait_seconds最大的等待事件基本就锁定了瓶颈所在。io/sql/file类等待大说明磁盘I/O扛不住wait/synch/mutex类等待大说明并发争用严重可能需要拆分热点或升级配置idle类占比高说明数据库很闲问题大概率在应用侧。这个脚本的价值在于把感觉慢变成数据说话。我优化完某个参数或SQL之后都会跑一次这个查询做前后对比用数据确认优化是否真的有效而不是感觉似乎快了一点。6. 数据订正与批量操作脚本小心驶得万年船6.1 大批量数据更新的安全写法日常工作中总有改数据的需求比如订正一条状态、批量覆盖一个字段。这类操作看着简单翻车概率却极高尤其是几百万行的大表直接UPDATE锁表能把整个业务拖死。我现场更的每条大语句都遵循同一个模式-- 分批更新每批1000条循环执行 DELIMITER $$ CREATE PROCEDURE batch_update() BEGIN DECLARE v_count INT DEFAULT 1; WHILE v_count 0 DO UPDATE orders SET status ARCHIVED WHERE status OLD AND id IN ( SELECT id FROM ( SELECT id FROM orders WHERE status OLD LIMIT 1000 ) tmp ); SET v_count ROW_COUNT(); -- 每批之间歇一下给其他事务留机会 DO SLEEP(0.1); END WHILE; END$$ DELIMITER ; CALL batch_update();外层套一层SELECT id FROM (...)子查询是因为MySQL不允许在UPDATE的子查询里直接查同一张表。SLEEP(0.1)是为了让出部分锁资源避免长事务囤积大量行锁。这种方式虽然慢但安全度完全不同实测200万行也就跑十几分钟比起锁死核心表让整个业务瘫痪这点时间完全值得。6.2 误更新后的快速恢复方案批量更新的另一面是万一条件写错了怎么办。我给自己留的后手是定期维护一份关键表的变更前快照-- 大操作前先备份目标表到备份表 CREATE TABLE orders_backup_20240101 AS SELECT * FROM orders; -- 如果操作出错用备份表恢复 -- 先删掉错误数据再按主键从备份表恢复 DELETE FROM orders; INSERT INTO orders SELECT * FROM orders_backup_20240101;这个方案虽然简单粗暴但在关键时刻能救命。注意几点备份表本身也占空间大表要确认磁盘够用恢复是物理复制业务侧如果在这期间有新写入会丢失这部分数据所以误操作发生后第一时间要停止业务写入再执行恢复。其实最理想的方案是开启binlog并用binlog2sql这类工具做行级回滚但这类工具的部署和学习成本较高。如果团队刚起步备份表方案先顶上,后续再逐步完善。6.3 主键冲突与唯一键冲突处理批量导入数据时最容易碰到的就是主键或唯一键冲突。常见场景从A库导入B库、重新跑批任务、数据回刷。我通常用INSERT ... ON DUPLICATE KEY UPDATE来平滑处理INSERT INTO user_profile (user_id, nickname, level, update_time) VALUES (10001, 张三, 5, NOW()) ON DUPLICATE KEY UPDATE nickname VALUES(nickname), level VALUES(level), update_time NOW();这个写法的含义是有冲突就更新没冲突就插入。用起来要格外小心它会覆盖目标记录的全部字段如果只想更新部分字段就只列出那几列。还有一个容易被忽视的点这个语句对唯一键冲突同样生效不一定非主键不可。如果是纯导入场景不想覆盖原有数据那就用INSERT IGNORE遇到冲突直接跳过。两种写法差异很大先想清楚业务意图再选我见过因为用错导致数据被覆盖写坏的教训很深刻。7. 脚本管理的工程化实践脚本写得再好没有一套管理规范时间一长就变成一团乱麻。我自己是从下面几件事里受益最多的分享出来。版本管理。数据库脚本也是代码必须进版本库。有些公司把SQL脚本扔在共享文件夹里版本靠文件名标注v2_final_最终版这种管理方式迟早出事。我的习惯是每个数据库变更脚本按日期和序号命名比如20240115_01_add_index_orders.sql提交到统一的代码仓库和代码一起评审、一起发布。命名规范。脚本命名建议包含三个要素操作对象、操作类型、日期。db_backup_20240115.sh比backup.sh好在哪一年后你翻出来不用打开文件就知道这个脚本是干什么的、什么时候写的、针对哪个库的。敏感信息处理。脚本里直接写数据库密码是大忌。我现在的基本做法是脚本从环境变量读取密码环境变量由部署平台注入代码库里只保留模板。虽然多了一道工序但密码泄露的风险大幅降低。执行前评审。涉及生产库的DDL和不常见DML无论多紧急都要先走评审。哪怕是DBA自己执行的脚本也先在测试环境完整跑一遍确认影响行数在预期范围内再上生产。这个习惯帮我挡掉了至少三次足以酿成大事故的操作。我个人还有一个小习惯每个核心脚本开头都用注释写清楚这脚本什么时候用、怎么用、踩过哪些坑。这个习惯救过我很多次。半年后你面对一个陌生或者陌生的脚本注释里那三行字比任何文档都管用。有一次团队里一位同事半夜调备份恢复正是靠着脚本头部注释里的注意该脚本依赖/data/backup/.env文件存在快速定位了问题少走了不少弯路。做数据库运维这些年最大的体会是所谓常用脚本不是一份到处都能抄的标准答案而是你在一次次故障处理、一次次数据恢复、一次次深夜值班中沉淀下来的自己的答案。今天的这些脚本是我实践中的一部分积累希望能给你的脚本库带来一些灵感。你在实际工作里积累了什么好用的脚本或者踩过什么独特的坑欢迎一起聊聊也许你踩过的那个坑正好是我下一个脚本要解决的问题。 数据库运维常用脚本实战：从备份恢复到性能优化
入职这些年我发现自己干得最多的活不是写业务代码也不是搭架构而是跟一堆数据库常用脚本打交道。无论是帮业务部门查一条慢SQL还是半夜被叫起来看主从延迟手里没几个趁手的脚本真就寸步难行。今天这篇不整虚的把我实际工作中反复在用、踩过坑之后修正过的脚本整理出来按场景分类讲清楚每个脚本为什么这么写、参数怎么调、遇到什么问题怎么改都一并交代。适合刚接触数据库运维的开发者也适合想把手头脚本整理得更规范的DBA。1. 信息查询与元数据脚本先把家底盘清楚1.1 三分钟摸清实例整体状况接手一台新的数据库服务器第一件事不是急着优化而是先搞清楚这台实例到底什么配置、什么负载、什么水位。我习惯先跑一套体检三连-- 查看实例版本、运行时长、字符集等基础信息 SELECT VERSION() AS version, NOW() AS current_time, hostname AS host_name, port AS port, character_set_server AS charset, collation_server AS collation; -- 查看关键运行状态值重点看连接数和慢查询 SHOW GLOBAL STATUS LIKE Threads_connected; SHOW GLOBAL STATUS LIKE Threads_running; SHOW GLOBAL STATUS LIKE Slow_queries; SHOW GLOBAL STATUS LIKE Uptime; -- 查看InnoDB buffer pool命中率相关指标 SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read_requests; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_reads;这几个脚本看着简单但信息密度很高。版本和端口就不说了Threads_connected和Threads_running这两个值一定要配合看前者代表当前建立的连接数后者代表真正在执行SQL的线程数。如果连接数飙到几百但Threads_running一直不高说明大部分连接是闲置连接问题多半出在应用侧连接池配置上而不是数据库本身。InnoDB buffer pool命中率有个经验公式1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)这个值常年低于99%说明内存配置或查询模式有问题光靠加内存未必管用可能得从SQL层面找原因。1.2 快速定位大表与膨胀空间刚接手一个库业务方问我哪些表最大、哪些表膨胀得厉害我从来不看information_schema因为那个统计信息在数据量大的时候本身就不准扫描也慢。直接用系统表查快得多-- 查看指定库中各表的行数、数据大小、索引大小MySQL 8.0 SELECT table_name, table_rows, 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 FROM information_schema.tables WHERE table_schema your_db ORDER BY total_mb DESC LIMIT 20;这里有个细节必须提醒table_rows是估算值InnoDB引擎下误差可能很大尤其频繁增删的表。真要精确行数还是得SELECT COUNT(*)但大表别在生产上直接跑低峰期再操作。另外我还会加一个查询看哪些表的数据量和索引量比例失调。曾遇到一张表数据才200MB索引却占了1.5GB查下来是建了一堆冗余的联合索引删掉三个索引后空间直接释放写入性能也提升了。索引不是越多越好这个后面专门讲。1.3 找出从未被使用的索引索引是有维护成本的每次写入都要更新索引结构。很多系统上线久了当初拍脑袋建的索引根本没人用白白拖累写入性能。查索引使用情况MySQL有现成的统计表-- 查看索引使用统计需开启performance_schemaMySQL 5.7 SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR, COUNT_READ, COUNT_WRITE FROM performance_schema.table_io_waits_summary_by_index_usage WHERE OBJECT_SCHEMA your_db AND INDEX_NAME IS NOT NULL ORDER BY COUNT_READ ASC LIMIT 20;COUNT_READ长期为0的索引基本就是死索引。但删之前务必确认两点第一查询计划里是否真的没用到有些统计值低但偶尔高峰用到的索引不能动第二有没有唯一性约束依赖这个索引。我习惯把候选索引先标记为INVISIBLEMySQL 8.0支持观察一周确认没有报错再真正DROP这个操作可以说非常稳妥。2. 备份与恢复脚本平时用不上出事保命2.1 逻辑备份脚本的正确姿势说到备份很多人第一反应就是mysqldump一把梭。但我见过太多因为参数没加对导致备份恢复失败的案例。我最常用的一套逻辑备份脚本长这样#!/bin/bash # 按库备份保留最近7天带时间戳 BACKUP_DIR/data/backup/mysql DATE$(date %Y%m%d_%H%M%S) DB_USERbackup_user DB_PASSyour_password DB_LISTdb1 db2 db3 for DB in $DB_LIST; do mysqldump \ --single-transaction \ --routines \ --triggers \ --events \ --set-gtid-purgedOFF \ -u$DB_USER -p$DB_PASS $DB \ | gzip $BACKUP_DIR/${DB}_${DATE}.sql.gz done # 清理7天前的备份 find $BACKUP_DIR -name *.sql.gz -mtime 7 -delete这几个参数每个都有讲究--single-transaction不加这个InnoDB表备份时会对全表加锁线上业务直接卡住。加上后基于MVCC一致性快照不锁表。--routines --triggers --events不加这三个存储过程、触发器、事件调度器全部丢失。很多人恢复后发现跑批任务不见了就是栽在这。--set-gtid-purgedOFFGTID模式下的老坑。不关掉这个问题备份文件里带着GTID信息恢复到新实例时会因为事务ID冲突直接报错。注意--single-transaction对MyISAM表无效MyISAM依旧会锁表。如果库里还有MyISAM表建议要么迁移到InnoDB要么备份安排在业务低谷期执行。2.2 更稳的物理备份xtrabackupmysqldump在数据量超过几十GB时就不太合适了备份慢、恢复更慢而且逻辑备份恢复时重放SQL耗时很长。这时候我直接用物理备份工具做全量增量备份#!/bin/bash # 全量物理备份脚本 BACKUP_BASE/data/backup/xtra DATE$(date %Y%m%d) FULL_DIR$BACKUP_BASE/full_$DATE xtrabackup \ --backup \ --target-dir$FULL_DIR \ --userbackup_user \ --passwordyour_password \ --parallel4 \ --compress # 备份完成后再做一步prepare保证备份集可用于恢复 xtrabackup --prepare --target-dir$FULL_DIR很多人备份完直接拷走恢复的时候才发现xtrabackup --prepare这步没做数据文件处于不一致状态根本起不来。--prepare的原理类似InnoDB崩溃恢复把redo log回放到数据文件里这步必须执行且prepare后的备份集不能再增量备份。物理备份的恢复动作也比逻辑备份简单直接把备份集解压拷回数据目录改好属主权限启动实例即可。我实测过100GB的数据xtrabackup备份加恢复全程不到半小时mysqldump可能要折腾两个小时以上。2.3 备份不等于高枕无忧恢复演练脚本这是我最想强调的一点不做恢复演练的备份等于没有备份。遇到过不止一次平时备份任务天天跑真到恢复那天发现备份文件损坏、参数不兼容、磁盘空间不够全抓瞎。我现在的习惯是每月至少做一次完整的恢复演练脚本固定如下#!/bin/bash # 恢复到临时实例并校验数据完整性 RESTORE_DIR/data/restore_test PORT3307 DATA_DIR$RESTORE_DIR/data # 1. 解压最新全量备份 LATEST_BACKUP$(ls -t /data/backup/mysql/*.sql.gz | head -1) gunzip -c $LATEST_BACKUP $RESTORE_DIR/restore.sql # 2. 直接初始化一个新实例 mysqld --no-defaults \ --datadir$DATA_DIR \ --port$PORT \ --socket$RESTORE_DIR/mysql.sock \ --initialize-insecure # 3. 导入数据 mysql --socket$RESTORE_DIR/mysql.sock -uroot $RESTORE_DIR/restore.sql # 4. 关键表行数校验 mysql --socket$RESTORE_DIR/mysql.sock -uroot -e SELECT COUNT(*) FROM your_db.key_table;恢复演练不只是验证备份文件可用还能顺便验证备份脚本里的参数变化是否兼容当前版本。比如之前从MySQL 5.7升级到8.0后老的备份恢复脚本就因为sql_mode差异跑挂过演练第一个发现问题。3. 巡检与监控脚本日常保健胜过临时抢救3.1 连接与线程状态实时巡检数据库出问题十有八九先反应在连接和线程上。我写了一个一键巡检脚本每分钟跑一次把异常情况直接打到日志里-- 检查当前连接数是否超过阈值这里是200 SHOW STATUS WHERE variable_name Threads_connected; -- 查看当前所有非Sleep状态的SQL SELECT id, user, host, db, command, time, state, LEFT(info, 120) AS sql_text FROM information_schema.processlist WHERE command ! Sleep ORDER BY time DESC; -- 查看处于锁等待的会话 SELECT * FROM information_schema.innodb_trx WHERE trx_state LOCK WAIT;information_schema.processlist这个表是排查问题的第一现场。看到time特别长的Query别急着KILL先看state字段如果是Sending data可能真的是大查询如果是Waiting for table metadata lock多半是DDL和DML打架了如果是Waiting for handler commit可能是磁盘刷盘太慢。有一次线上业务卡死我用这个脚本查出来一个time2400的ALTER TABLE在等元数据锁后面堵了一串查询。找到根源后把那个一直没提交的长事务KILL掉业务立刻恢复。没有这个巡检思路光看连接数飚高是定位不到根因的。3.2 慢查询与错误日志分析脚本慢查询日志一定要开但开了不分析等于白开。我习惯把慢查询日志和错误日志都统一到一个目录然后用脚本做每日分析#!/bin/bash # 每日慢查询统计输出Top10耗时SQL SLOW_LOG/var/log/mysql/slow-query.log TODAY$(date %Y%m%d) # 统计当天慢查询总数 TOTAL$(grep -c Time: $SLOW_LOG || true) echo $TODAY slow query total: $TOTAL # 提取慢查询中的SQL文本统计出现频率 grep -A2 Query_time $SLOW_LOG | \ grep ^SELECT\|^UPDATE\|^DELETE\|^INSERT | \ sort | uniq -c | sort -rn | head -20这个脚本的思路是先看量再看质。慢查询多不一定是坏事要看是否集中在少数几条SQL上。如果每天都有一批SQL稳定出现在慢查询列表且执行计划没变说明SQL本身有问题需要从索引和改写两个方向处理。有个经验分享慢查询日志里的Rows_examined远比Query_time重要。Rows_examined几十万但只返回几行说明扫描了大量数据索引没走对或者索引选择性差。优化这类SQL收益大且确定。3.3 磁盘与表空间水位监控数据库的磁盘满了是真的会写不进去任何数据的而且一旦满盘恢复起来极其痛苦。所以磁盘监控我宁可多写几个脚本也不愿意半夜被叫起来。#!/bin/bash # 磁盘与关键目录监控超过80%告警 THRESHOLD80 ALERT_FILE/data/monitor/disk_alert.log df -h | grep -E ^/dev/(sd|vd) | while read line; do USAGE$(echo $line | awk {print $5} | sed s/%//) MOUNT_POINT$(echo $line | awk {print $6}) if [ $USAGE -ge $THRESHOLD ]; then echo $(date %Y-%m-%d %H:%M:%S) WARN: $MOUNT_POINT usage $USAGE% $ALERT_FILE fi done # InnoDB表空间大小分布Top10 mysql -u monitor_user -p$PASSWORD -e SELECT table_schema, SUM(data_length index_length) / 1024 / 1024 / 1024 AS total_gb FROM information_schema.tables GROUP BY table_schema ORDER BY total_gb DESC; $ALERT_FILE告警阈值80%是我个人的习惯不一定适合所有人。如果业务有大量临时排序或日志写入建议把阈值调到70%给临时文件留出缓冲。数据目录和日志目录最好分开监控binlog目录涨速快一天几个GB很正常单独看才不会忽略。另外提醒一个容易忽略的点监控脚本本身也要做清理监控日志文件无限增长会把磁盘反过来写满。logrotate配一下或者用find ... -mtime 30 -delete做轮转。4. 性能优化辅助脚本找到瓶颈、对症下药4.1 直击性能瓶颈的SQL定位线上系统变慢最忌讳的就是凭感觉调参数。我的做法是先把压在库上的SQL揪出来分析执行计划再决定怎么改。下面这个脚本是我最常用的SQL分析三板斧-- 1. 开启执行计划分析 EXPLAIN ANALYZE SELECT order_id, user_id, amount FROM orders WHERE user_id 12345 AND status PAID AND create_time 2024-01-01 ORDER BY create_time DESC LIMIT 20;EXPLAIN ANALYZE是MySQL 8.0才有的它不只是给出执行计划还会真实执行SQL并返回每一步的耗时和扫描行数。对于一条慢SQL先用它看瓶颈在哪一步是索引扫描行数太多还是排序耗时太长还是回表次数太多。我遇到过一条SQL加了个索引之后从2秒提升到50毫秒以为是索引生效了但用EXPLAIN ANALYZE一查发现真正的问题是ORDER BY create_time DESC触发了filesort。后来把索引改成(user_id, status, create_time)让索引天然有序彻底消除filesort才真正解决问题。没有EXPLAIN ANALYZE我只能看到加索引变快了看不到完整的优化链路。4.2 大表碎片整理脚本InnoDB表在频繁更新和删除后会产生大量碎片。表现就是表的数据量没怎么涨但物理文件越来越大全表扫描越来越慢。整理碎片的方法很直接-- 查看表碎片率 SELECT table_name, ROUND(data_length / 1024 / 1024, 2) AS data_mb, ROUND(data_free / 1024 / 1024, 2) AS fragmented_mb, ROUND(data_free / data_length * 100, 2) AS fragment_pct FROM information_schema.tables WHERE table_schema your_db AND data_free 100 * 1024 * 1024 -- 只列出碎片超过100MB的表 ORDER BY fragment_pct DESC LIMIT 20;对于碎片率高的表执行ALTER TABLE table_name ENGINEInnoDB即可重建表、回收碎片空间。但这里有几个必须注意的坑这个操作会锁表尽管MySQL 5.6是Online DDL但数据量巨大的表仍可能引发长时间元数据锁务必安排在维护窗口。整理碎片前先看磁盘剩余空间重建表需要约等于原表大小的额外空间。MySQL 8.0之后这个动作可以用ALTER TABLE ... FORCE代替效果更明确。有个取巧的办法如果表上有定期归档任务归档后碎片自然产生建议把碎片整理加入每月的自动任务比一次性整理大表风险低得多。4.3 关键参数调优自查脚本网上到处是MySQL优化必须改这10个参数的文章但盲目调参翻车的案例我见得太多了。参数调优最靠谱的依据是自己的监控数据我写了一个自查脚本输出当前实际运行情况对应的参数建议-- 查看缓存命中率的真实数据 SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read%; SHOW GLOBAL STATUS LIKE Innodb_row_lock_current_waits; SHOW GLOBAL STATUS LIKE Innodb_row_lock_time; -- 查看当前实际生效的参数值 SHOW VARIABLES LIKE innodb_buffer_pool_size; SHOW VARIABLES LIKE innodb_flush_log_at_trx_commit; SHOW VARIABLES LIKE max_connections; SHOW VARIABLES LIKE transaction_isolation;这里我最想展开讲的是innodb_flush_log_at_trx_commit。这个参数只有三个取值0、1、2很多优化文章建议改成0或2来提升性能但代价是事务提交时可能丢失最多1秒的日志这在金融、订单等场景是绝对不可接受的。我的原则是核心业务必须保持1非核心报表库可以考虑20绝不推荐。调参之前先问自己三个问题改这个参数要解决的问题是什么改完如何验证确实解决了回滚方案是什么答不上来任何一个就不要动。5. 常见问题与排查技巧实录5.1 连接数打满排查现象Threads_connected达到max_connections上限新连接直接报Too many connections。排查顺序先看Threads_running如果这个值不高说明大量连接是Sleep状态应用连接池没释放。查information_schema.processlist看Sleep连接来自哪些host定位是哪个应用。看应用连接池配置maxPoolSize是不是设置得过大、idleTimeout是不是没生效。如果是突发流量导致临时调大max_connections能救急但根治要看应用侧连接复用。我以前遇到过一个案例某服务每执行一次查询就新建连接用完不归还连接池形同虚设。排查完发现是应用框架的配置项被误改了maximumPoolSize写成了1000而数据库max_connections才500必然打满。脚本只是定位工具根因分析还得靠对业务侧的理解。5.2 锁等待问题处理现象业务报错里出现Lock wait timeout exceeded或者Deadlock found。处理思路-- 查看当前所有事务 SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id FROM information_schema.innodb_trx; -- 查看锁等待关系 SELECT waiting_trx_id, waiting_thread, waiting_query, blocking_trx_id, blocking_thread, blocking_query FROM sys.innodb_lock_waits;sys.innodb_lock_waits是MySQL 5.7及以上版本提供的视图直接列出谁在等锁、谁持有锁。优先处理持有锁的那个长事务如果业务上允许直接把它KILL掉。但KILL是治标根源通常在两条事务没及时提交应用层忘记commit、或者事务范围过大一个事务里做了太多查询和计算。死锁的话MySQL会自动回滚其中一个事务应用侧要有重试机制。这个事情别依赖数据库帮你想办法应用层做重试才是正解。5.3 主从延迟排查主从延迟是另一种常见的数据库没事但业务很慢的场景。查主从状态用下面的脚本SHOW SLAVE STATUS\G -- 关键字段 -- Seconds_Behind_Master: 从库落后主库的秒数 -- Slave_IO_Running: IO线程是否正常必须是Yes -- Slave_SQL_Running: SQL线程是否正常必须是Yes -- Last_IO_Error / Last_SQL_Error: 有值必查这是报错现场Seconds_Behind_Master为0不代表没延迟它只是估算值主库大事务执行期间这个值可能显示0因为从库根本没追上主库当前的binlog位置。更可靠的判断标准是对比主从的Master_Log_File/Read_Master_Log_Pos与从库执行到的Relay_Master_Log_File/Exec_Master_Log_Pos。主从延迟最常见的原因按优先级排列大事务一条SQL更新几百万行、从库硬件性能弱、从库上跑着分析查询抢资源、DDL在从库被打断。解决方向也很明确把大事务拆小、控制从库上的分析负载、保障主从硬件规格一致。5.4 快速定位性能拐点最后一个脚本也是我调优时候最重要的参考依据通过performance_schema拿到真实的等待事件分布。-- 等待事件Top10MySQL 5.7 SELECT EVENT_NAME, COUNT_STAR, ROUND(SUM_TIMER_WAIT / 1000000000, 2) AS wait_seconds FROM performance_schema.events_waits_summary_global_by_event_name WHERE SUM_TIMER_WAIT 0 ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;看到wait_seconds最大的等待事件基本就锁定了瓶颈所在。io/sql/file类等待大说明磁盘I/O扛不住wait/synch/mutex类等待大说明并发争用严重可能需要拆分热点或升级配置idle类占比高说明数据库很闲问题大概率在应用侧。这个脚本的价值在于把感觉慢变成数据说话。我优化完某个参数或SQL之后都会跑一次这个查询做前后对比用数据确认优化是否真的有效而不是感觉似乎快了一点。6. 数据订正与批量操作脚本小心驶得万年船6.1 大批量数据更新的安全写法日常工作中总有改数据的需求比如订正一条状态、批量覆盖一个字段。这类操作看着简单翻车概率却极高尤其是几百万行的大表直接UPDATE锁表能把整个业务拖死。我现场更的每条大语句都遵循同一个模式-- 分批更新每批1000条循环执行 DELIMITER $$ CREATE PROCEDURE batch_update() BEGIN DECLARE v_count INT DEFAULT 1; WHILE v_count 0 DO UPDATE orders SET status ARCHIVED WHERE status OLD AND id IN ( SELECT id FROM ( SELECT id FROM orders WHERE status OLD LIMIT 1000 ) tmp ); SET v_count ROW_COUNT(); -- 每批之间歇一下给其他事务留机会 DO SLEEP(0.1); END WHILE; END$$ DELIMITER ; CALL batch_update();外层套一层SELECT id FROM (...)子查询是因为MySQL不允许在UPDATE的子查询里直接查同一张表。SLEEP(0.1)是为了让出部分锁资源避免长事务囤积大量行锁。这种方式虽然慢但安全度完全不同实测200万行也就跑十几分钟比起锁死核心表让整个业务瘫痪这点时间完全值得。6.2 误更新后的快速恢复方案批量更新的另一面是万一条件写错了怎么办。我给自己留的后手是定期维护一份关键表的变更前快照-- 大操作前先备份目标表到备份表 CREATE TABLE orders_backup_20240101 AS SELECT * FROM orders; -- 如果操作出错用备份表恢复 -- 先删掉错误数据再按主键从备份表恢复 DELETE FROM orders; INSERT INTO orders SELECT * FROM orders_backup_20240101;这个方案虽然简单粗暴但在关键时刻能救命。注意几点备份表本身也占空间大表要确认磁盘够用恢复是物理复制业务侧如果在这期间有新写入会丢失这部分数据所以误操作发生后第一时间要停止业务写入再执行恢复。其实最理想的方案是开启binlog并用binlog2sql这类工具做行级回滚但这类工具的部署和学习成本较高。如果团队刚起步备份表方案先顶上,后续再逐步完善。6.3 主键冲突与唯一键冲突处理批量导入数据时最容易碰到的就是主键或唯一键冲突。常见场景从A库导入B库、重新跑批任务、数据回刷。我通常用INSERT ... ON DUPLICATE KEY UPDATE来平滑处理INSERT INTO user_profile (user_id, nickname, level, update_time) VALUES (10001, 张三, 5, NOW()) ON DUPLICATE KEY UPDATE nickname VALUES(nickname), level VALUES(level), update_time NOW();这个写法的含义是有冲突就更新没冲突就插入。用起来要格外小心它会覆盖目标记录的全部字段如果只想更新部分字段就只列出那几列。还有一个容易被忽视的点这个语句对唯一键冲突同样生效不一定非主键不可。如果是纯导入场景不想覆盖原有数据那就用INSERT IGNORE遇到冲突直接跳过。两种写法差异很大先想清楚业务意图再选我见过因为用错导致数据被覆盖写坏的教训很深刻。7. 脚本管理的工程化实践脚本写得再好没有一套管理规范时间一长就变成一团乱麻。我自己是从下面几件事里受益最多的分享出来。版本管理。数据库脚本也是代码必须进版本库。有些公司把SQL脚本扔在共享文件夹里版本靠文件名标注v2_final_最终版这种管理方式迟早出事。我的习惯是每个数据库变更脚本按日期和序号命名比如20240115_01_add_index_orders.sql提交到统一的代码仓库和代码一起评审、一起发布。命名规范。脚本命名建议包含三个要素操作对象、操作类型、日期。db_backup_20240115.sh比backup.sh好在哪一年后你翻出来不用打开文件就知道这个脚本是干什么的、什么时候写的、针对哪个库的。敏感信息处理。脚本里直接写数据库密码是大忌。我现在的基本做法是脚本从环境变量读取密码环境变量由部署平台注入代码库里只保留模板。虽然多了一道工序但密码泄露的风险大幅降低。执行前评审。涉及生产库的DDL和不常见DML无论多紧急都要先走评审。哪怕是DBA自己执行的脚本也先在测试环境完整跑一遍确认影响行数在预期范围内再上生产。这个习惯帮我挡掉了至少三次足以酿成大事故的操作。我个人还有一个小习惯每个核心脚本开头都用注释写清楚这脚本什么时候用、怎么用、踩过哪些坑。这个习惯救过我很多次。半年后你面对一个陌生或者陌生的脚本注释里那三行字比任何文档都管用。有一次团队里一位同事半夜调备份恢复正是靠着脚本头部注释里的注意该脚本依赖/data/backup/.env文件存在快速定位了问题少走了不少弯路。做数据库运维这些年最大的体会是所谓常用脚本不是一份到处都能抄的标准答案而是你在一次次故障处理、一次次数据恢复、一次次深夜值班中沉淀下来的自己的答案。今天的这些脚本是我实践中的一部分积累希望能给你的脚本库带来一些灵感。你在实际工作里积累了什么好用的脚本或者踩过什么独特的坑欢迎一起聊聊也许你踩过的那个坑正好是我下一个脚本要解决的问题。