1. 为什么值得把 MySQL 操作交给 Shell 脚本干运维这行天天和 MySQL 打交道你是不是也经常碰到这种场景凌晨两点被电话叫醒说某个库的连接数满了你得赶紧上去查processlist然后手动 kill 掉几个阻塞会话或者每周都要定时导出线上数据用mysqldump跑一遍再把备份文件同步到远程机器甚至有时候要跨几十台实例批量执行一条ALTER TABLE只能一台一台登上去敲。这些都是 Shell 脚本的用武之地。对于 MySQL 运维来说Shell 脚本的价值不在于写得有多炫酷而在于三个字可复用。你把一次手动排障的过程固化成一个脚本下次再遇到同样问题一行命令就能搞定你把每天都要做的备份动作写成脚本配上定时任务就不用再担心“今天忘了备份”这种低级事故。说白了脚本是把你的运维经验沉淀下来的载体。这篇分享基于我一个真实项目的实践——一个管理大约 30 台 MySQL 实例的模拟环境。从一开始靠人肉登服务器执行命令到后来整理出一整套 Shell 脚本工具集排障时间从平均二十分钟缩短到三分钟以内日常巡检也基本自动化了。我踩过的坑、试出来的经验都会写出来直接抄作业就行。但这篇文章不是只给运维 DBA 看的。只要你的工作里需要跟 MySQL 打交道——后端开发环境要建库建表、测试环境要刷数据、数据分析要临时跑查询——学会用 Shell 脚本调用 MySQL 命令都能省下大把时间。脚本的门槛不高你只需要懂一点 bash 语法加上几个关键参数就能做出非常顺手的工具。2. 工具选型与基础姿势搞懂 mysql 命令行客户端的正确用法2.1 一键登录别在密码上浪费时间很多人在 Shell 脚本里操作 MySQL第一步就卡在登录上。直接在命令行里用mysql -uroot -p密码确实简单但有两个问题一是密码明文暴露在进程列表里安全上过不去二是在脚本里这样写密码重复出现后期改密码要改一堆地方。我常用的做法是配置文件集中管理。# config.sh DB_HOST127.0.0.1 DB_PORT3306 DB_USERops_user DB_PASSWORD你的密码 DB_NAMEinformation_schema然后在主脚本里引入这个配置source ./config.sh MYSQL_CMDmysql -h${DB_HOST} -P${DB_PORT} -u${DB_USER} -p${DB_PASSWORD} --skip-column-names --batch注意我特意加了--batch和--skip-column-names这两个参数。--batch让输出以制表符分隔且不做边框美化结果更容易用 awk 分割处理--skip-column-names去掉表头避免在循环处理时把字段名当成数据。提示生产环境建议用.my.cnf或专门的运维账号 权限最小化但在这篇文章的示例里用配置文件管理已经比直接硬编码强一个层级。2.2 执行单条 SQL 的正确姿势mysql -e是最常用的方式直接在命令行后面跟 SQL 字符串mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASSWORD} -e SELECT NOW();脚本里如果要执行多条 SQL可以用分号分隔mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASSWORD} -e USE test_db; SHOW TABLES; SELECT COUNT(*) FROM users;但这里有个容易踩的坑如果 SQL 里包含特殊字符比如$、反引号、双引号在 bash 里就要特别注意转义。我的经验是能用单引号包 SQL 就用单引号SQL 内部字符串再用双引号这样能减少一层转义麻烦。mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASSWORD} -e SELECT * FROM users WHERE name 张三;2.3 从文件读 SQL写复杂逻辑的必备技能当 SQL 逻辑变复杂比如要写一个存储过程或者多表关联的查询直接在-e后面拼接字符串就非常痛苦各种转义、换行问题接踵而至。这时候把 SQL 写到文件里再用输入重定向喂给 mysql 命令是更优雅的解法。mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASSWORD} ./cleanup_expired_data.sql甚至可以结合管道动态生成 SQLecho SELECT id FROM users WHERE status0; | mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASSWORD}这条思路很关键后面讲批量处理的时候会用到。它的本质是把生成 SQL和执行 SQL两个阶段解耦你可以在脚本里用循环一行一行生成 SQL也可以读文件批量执行灵活性完全不一样。3. 核心操作脚本实战从查询、导出到批量执行3.1 查询结果如何处理Shell 脚本的灵魂三问脚本操作 MySQL最核心的问题只有一个查询结果怎么拿、怎么拆、怎么用。我总结为灵魂三问结果是一行还是一个集合结果里有没有空值和特殊字符结果要喂给后续的哪个命令对应到 bash 代码常见的有三种处理姿势。姿势一直接赋值给变量适合查一个值的场景比如取当前时间、取某个表的行数。DB_ROWS$(mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASSWORD} --batch --skip-column-names -e SELECT COUNT(*) FROM test_db.users;) echo 当前 user 表数据量: ${DB_ROWS}注意输出里如果有多行变量只会拿到第一行。所以这种姿势只适合单值查询。姿势二while 循环逐行处理查询结果有多行时这是我最常用的姿势。mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASSWORD} --batch --skip-column-names -e SELECT id, name FROM test_db.users WHERE status1; | while IFS$\t read -r user_id user_name; do echo 用户 ${user_id} 的名字是 ${user_name} done这里有两个关键点IFS$\t把字段分隔符临时改为制表符对应前面--batch的输出格式-r参数防止 read 把反斜杠当转义符吃掉。实测下来只要字段值里不包含制表符这个拆分方式非常稳。姿势三先导出成文件再用 awk/sed 二次处理当查询结果作为中间产物后面还要复杂加工时建议先落盘。mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASSWORD} --batch --skip-column-names -e SELECT id, name FROM test_db.users; /tmp/users.tsv awk -F\t {print $2} /tmp/users.tsv | sort | uniq -c | sort -rn这种先导出、再分析的方式还有一个好处导出文件和排查步骤拆分中途断了不用重新查数据库。3.2 批量执行 SQL小心你的引号和分隔符批量操作是最能体现 Shell 脚本价值的一块但也是最容易翻车的一块。我举个实际场景要给所有订单表加上一个索引表名分别是orders_2023_01、orders_2023_02、orders_2023_03……一直到orders_2023_12。用一条命令拼出全部 SQL 再执行TABLES$(mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASSWORD} --batch --skip-column-names -e SHOW TABLES LIKE orders_2023_%;) INDEX_SQL for tbl in ${TABLES}; do INDEX_SQL${INDEX_SQL} ALTER TABLE ${tbl} ADD INDEX idx_created_at (created_at); done mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASSWORD} -e ${INDEX_SQL}这个方案表面上可用但有一个隐患如果TABLES为空INDEX_SQL为空字符串mysql 会直接报错说没有 SQL 传入。所以在脚本里做一层判空非常有必要。if [ -z ${INDEX_SQL} ]; then echo 未匹配到任何表跳过索引变更 exit 0 fi另一个常见问题是ALTER TABLE这种 DDL 语句在 MySQL 5.6 以后的版本会自动提交不能像 DML 那样包在事务里回滚。所以真要执行批量 DDL请务必先打印 SQL 做个 dry run。# dry run 模式 if [ ${DRY_RUN} 1 ]; then echo 将要执行的 SQL echo ${INDEX_SQL} exit 0 fi3.3 导入 SQL 文件source 命令和重定向的区别有时候需要导入一个较大的 SQL 备份文件比如从测试环境导出的带数据的 dump 文件。常见做法有两种# 方法一mysql 客户端直接重定向 mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASSWORD} --default-character-setutf8mb4 test_db /backup/test_db.sql # 方法二source 命令 mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASSWORD} -e source /backup/test_db.sql;这两种方式都能用但有几个细节容易被忽略。第一务必指定--default-character-setutf8mb4否则中文数据很容易乱码。第二重定向方式在导入过程中如果出错mysql 默认会继续往下执行不会自动中断如果希望遇到错误立即停止需要在 SQL 文件开头加一句SET GLOBAL max_allowed_packet1073741824; SET FOREIGN_KEY_CHECKS0; -- 遇到错误立即停止 \p -- 或者用 mysql --force 的相反逻辑不用 --force 即可其实 mysql 客户端默认遇到错误不会停止除非你用--force的反向思路——不传--force但依然有更稳妥的办法。我实际验证过最简单可靠的是在执行命令末尾加21然后把输出捕获到日志文件脚本里再 grep 有没有 ERROR 关键字。mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASSWORD} --default-character-setutf8mb4 test_db /backup/test_db.sql /tmp/import.log 21 if grep -q ERROR /tmp/import.log; then echo 导入过程中出现错误请检查日志 exit 1 fi4. 备份、巡检与自动化运维场景中的三把实战利器4.1 自动化备份脚本的设计思路备份是运维的底线Shell 脚本做备份最合适不过。设计思路一般是四步导出、压缩、保留轮转、异地同步。先给一个基础版本#!/bin/bash # backup_mysql.sh source ./config.sh BACKUP_DIR/data/backup/mysql DATE$(date %Y%m%d_%H%M%S) DB_LISTtest_db order_db user_db RETENTION_DAYS7 mkdir -p ${BACKUP_DIR} for db in ${DB_LIST}; do echo [$(date %Y-%m-%d %H:%M:%S)] 开始备份 ${db} mysqldump -h${DB_HOST} -u${DB_USER} -p${DB_PASSWORD} --single-transaction --routines --triggers --events ${db} | gzip ${BACKUP_DIR}/${db}_${DATE}.sql.gz if [ $? -eq 0 ]; then echo [$(date %Y-%m-%d %H:%M:%S)] ${db} 备份成功 else echo [$(date %Y-%m-%d %H:%M:%S)] ${db} 备份失败 | tee -a /var/log/mysql_backup.log fi done # 保留最近 7 天 find ${BACKUP_DIR} -name *.sql.gz -mtime ${RETENTION_DAYS} -delete echo 备份完成这里面的--single-transaction必须给 InnoDB 表用它利用事务一致性快照做备份不锁表线上低峰期跑没问题。--routines、--triggers、--events三个参数保证存储过程、触发器、事件都被导出否则恢复的时候会丢东西。这个过程我反复被坑过——有次恢复完数据才发现少了触发器业务直接报错。经验mysqldump 导出的 SQL 文件可以在导入前先做一次语法快速检查办法是grep -i CREATE.*TRIGGER ${BACKUP_DIR}/xxx.sql.gz压缩文件要先用 zcat 再 grep。这个检查 30 秒内完成但能救一次事故。4.2 一台机器上的定时巡检脚本备份做了之后巡检也不能落下。我设计了一个轻量巡检脚本放在 crontab 里每五分钟跑一次检查三个核心指标实例是否存活、慢查询数量有没有突增、主从延迟如果有主从的话。#!/bin/bash # health_check.sh source ./config.sh ALERT_EMAILopsexample.com # 1. 实例存活检查 if ! mysqladmin -h${DB_HOST} -u${DB_USER} -p${DB_PASSWORD} ping /dev/null 21; then echo [CRITICAL] MySQL 实例无法 ping 通 | mail -s MySQL Down Alert ${ALERT_EMAIL} exit 1 fi # 2. 慢查询数量基于 information_schema SLOW_COUNT$(mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASSWORD} --batch --skip-column-names -e SELECT COUNT(*) FROM information_schema.processlist WHERE command ! Sleep AND time 10; 2/dev/null) if [ ${SLOW_COUNT} -gt 20 ]; then echo [WARN] 慢查询数量异常${SLOW_COUNT} | mail -s MySQL Slow Query Alert ${ALERT_EMAIL} fi # 3. 连接数使用率 MAX_CONN$(mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASSWORD} --batch --skip-column-names -e SHOW VARIABLES LIKE max_connections; | awk {print $2}) CUR_CONN$(mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASSWORD} --batch --skip-column-names -e SHOW STATUS LIKE Threads_connected; | awk {print $2}) if [ ${CUR_CONN} -gt $((MAX_CONN * 80 / 100)) ]; then echo [WARN] 连接数达到 max_connections 的 80% fi巡检脚本的要点是宁可漏报不能误报太频繁。如果每分钟发一封告警邮件最后运维会直接把它拉黑真正出问题时反而没人看。所以我通常把慢查询阈值设得比较高比如超过 20 或 30 才算异常而不是一有慢查询就报警。crontab 配置是这样的*/5 * * * * /opt/scripts/health_check.sh /var/log/mysql_health.log 21注意脚本内部千万不能写交互式的逻辑全程非交互输出否则 crontab 环境下会卡住。4.3 批量实例的统一运维脚本如果你的环境里有多台 MySQL 实例单机脚本就有点浪费人力了。这时候我给每台实例一个独立的配置文件用一个分发脚本去批量执行。#!/bin/bash # run_all_instances.sh # 用法./run_all_instances.sh /opt/scripts/task.sh TASK_SCRIPT$1 INSTANCE_CONFIG_DIR/opt/ops/instances for conf in ${INSTANCE_CONFIG_DIR}/*.conf; do instance_name$(basename ${conf} .conf) echo 开始处理实例${instance_name} # 读取该实例的配置并注入环境变量 source ${conf} DB_HOST${INSTANCE_HOST} DB_PORT${INSTANCE_PORT} DB_USER${INSTANCE_USER} DB_PASSWORD${INSTANCE_PASSWORD} # 在新进程中执行任务脚本 bash ${TASK_SCRIPT} if [ $? -eq 0 ]; then echo 实例 ${instance_name} 执行成功 else echo 实例 ${instance_name} 执行失败检查日志 fi done配套的实例配置文件长这样# instance_192_168_1_10.conf INSTANCE_HOST192.168.1.10 INSTANCE_PORT3306 INSTANCE_USERops_user INSTANCE_PASSWORDpassword这个方案能扩展到几十台实例只要新加一台机器就复制一份 conf 文件。我有一次做全量索引检查和 schema 比对就是用这套框架跑完的效率提升非常明显。5. 常见问题与排查技巧这些坑我替你踩过了5.1 参数压测表不同场景的命令模板先上一张我在项目中整理的速查表把不同场景下推荐用的命令模板固定下来。这省去了很多临时查参数的麻烦。场景推荐命令关键参数说明单条查询mysql -e SELECT ...--batch--skip-column-names批量变更拼 SQL 字符串 判空 dry run禁止直接用-e传多行未转义 SQL备份mysqldump --single-transaction加上--routines --triggers --events导入重定向 日志捕获 grep ERROR必须指定--default-character-setutf8mb4数据导出mysql --batch -e ... file.tsv用 IFS$\t拆分实例状态巡检mysqladmin pingSHOW STATUS非交互式5.2 引号地狱bash 与 SQL 的转义冲突Shell 脚本里最容易踩的坑就是引号。特别是 SQL 里有单引号字符串、bash 里有单引号包裹、变量还要嵌入的时候三层套娃非常折磨人。我举一个真实例子要更新一张表把公司名称里的旧后缀替换掉。OLD_SUFFIX旧 NEW_SUFFIX新 mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASSWORD} -e UPDATE company SET name REPLACE(name, ${OLD_SUFFIX}, ${NEW_SUFFIX}) WHERE name LIKE %${OLD_SUFFIX}%;看起来没问题但万一OLD_SUFFIX变量里本身包含单引号整个 SQL 就废了。更稳的办法是把 SQL 中所有用户可控的变量都经过 sed 转义或者直接用参数化思路把值放在拼接前的变量里单独做转义。但 MySQL 命令行客户端本身不支持绑定参数所以实在复杂的情况下我会改用--init-command或者在代码里用 Python 写更复杂的变更。Shell 脚本适合 80% 的简单场景剩下那 20% 别有执念换工具不丢人。5.3 定时任务里的环境变量陷阱很多人写好的脚本手动执行一切正常但放进 crontab 就各种 command not found。原因很简单crontab 的环境变量非常少PATH通常只有/usr/bin:/bin而你的 mysql 命令可能装在/usr/local/mysql/bin下面。解决方案包括脚本开头显式设置 PATH#!/bin/bash export PATH/usr/local/mysql/bin:/usr/local/bin:/usr/bin:/bin:$PATH在 crontab 里直接写全路径*/5 * * * * /usr/local/mysql/bin/mysqladmin -h127.0.0.1 -uops_user -ppassword ping我自己的习惯是第一种统一在脚本开头加一行省得后期换路径还要全局搜。还有一个容易被忽略的坑crontab 默认的 shell 是/bin/sh不是/bin/bash。如果你的脚本用了 bash 特有的语法比如[[ ]]、$RANDOM、数组在 cron 里执行就会报错。稳妥做法是脚本第一行写#!/bin/bash并且给脚本可执行权限crontab 里直接写脚本路径而不是bash /path/script.sh的变体。5.4 排查慢查询的一个完整实战案例最后分享一个我印象比较深的案例。某天上午巡检脚本报警说information_schema.processlist里有超过 20 个执行时间大于 10 秒的会话。手动上去查mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASSWORD} -e SELECT id, user, host, db, command, time, state, LEFT(info, 80) FROM information_schema.processlist WHERE command ! Sleep ORDER BY time DESC LIMIT 20;发现大量会话卡在Waiting for table metadata lock状态。这个状态的意思是有会话持有表的元数据锁其他会话在排队等锁。常见触发原因是某个长事务或者没提交的 DDL。我用另一条 SQL 查是否存在长时间未结束的事务mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASSWORD} -e SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query FROM information_schema.innodb_trx ORDER BY trx_started ASC LIMIT 10;定位到一个跑了四个小时的事务对应的线程 id 是12345。再回到 processlist 里看这个线程发现是一条慢到离谱的 SELECT是业务侧一个没加索引的全表扫描。处理方式分两步先 kill 掉这个阻塞源再给相关查询补索引。kill 操作用脚本执行非常简单# kill_all_blocking_sessions.sh mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASSWORD} --batch --skip-column-names -e SELECT CONCAT(KILL , id, ;) FROM information_schema.processlist WHERE commandQuery AND time 300 AND info IS NOT NULL; | while read kill_stmt; do echo 执行: ${kill_stmt} mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASSWORD} -e ${kill_stmt} done这个脚本的逻辑是先把所有执行超过 300 秒的查询拼成KILL id;语句然后一行一行执行。注意它生成的KILL语句本身就是 SQL再用 mysql 去执行属于用 SQL 生成 SQL的经典思路。这次事故之后我反思总结出三条经验写在最后供你参考第一杀会话前务必确认影响。这个脚本一次杀所有超过 300 秒的查询在生产环境可能过于激进建议先输出会话列表人工确认再决定是否执行第二段 kill 逻辑。第二timeout变量的单位是秒阈值设置要看业务特征。数据分析团队跑批任务经常超过 300 秒如果一刀切杀掉可能把正常的批任务误杀。我在生产环境的做法是另外维护一个白名单排除已知的长任务。第三信息收集优先于干预。任何自动化脚本在对 MySQL 做变更前都要先做一遍只读探测并输出日志。这个原则适用于所有运维脚本。如果你打算在自己的环境里落地这套脚本我还有个建议先搭一套最小可用的版本管理好一台实例的备份、巡检和批量变更再考虑扩展到多实例。因为脚本这东西复杂度是随实例数量非线性增长的——多一台实例就多一类网络超时、认证失败、版本差异的可能这些只有跑起来才会遇到静态推演很难穷尽。慢慢来把第一台跑稳了后面就是复制粘贴的事。