👋 欢迎来到吴老二技术博客,专注运维 / 数据库 / 云原生
首页/数据库/正文

备份、权限、故障定位——运维的三条底线

吴
吴老二
2026-11-19数据库3 次浏览0 条评论

导语

前三篇讲的是“会用”:建库建表、增删改查、关联统计。这一篇讲“敢运维”。运维和开发的区别不在于会不会写 SQL,而在于出了问题能不能兜住:备份能不能恢复、权限给得对不对、慢查询和锁等待能不能定位到根因。

本篇是系列最后一篇。test_ops 库经过前三周已经有了 users(5 个用户)和 orders(5 条订单)两张表。我们用它完成一次完整闭环:备份 → 故意删表 → 恢复 → 授权 → 定位锁等待。

一、备份:一致性从 --single-transaction 来

mysqldump -u root -p --single-transaction --databases test_ops > /tmp/test_ops_backup.sql

--single-transaction 是这里最关键的参数。它在备份开始时开启一个可重复读事务,利用 InnoDB 的 MVCC 快照保证备份期间数据一致,并且不锁表,业务写入不受影响。

不加这个参数,mysqldump 默认会 LOCK TABLES,备份期间业务写入被阻塞。对一个线上库来说,这可能是几分钟的写入停顿。

但 --single-transaction 只对 InnoDB 有效。如果库里有 MyISAM 表,一致性无法保证——这正是第一篇强调显式写 ENGINE=InnoDB 的原因之一。设计期的一个习惯,在备份期兑现价值。

检查备份文件非空:

ls -lh /tmp/test_ops_backup.sql

二、恢复:没验证过的备份等于没备份

USE test_ops;
DROP TABLE orders;
SHOW TABLES;

orders 没了。从备份恢复:

mysql -u root -p test_ops < /tmp/test_ops_backup.sql

回 mysql> 验证:

SHOW TABLES;
SELECT COUNT(*) FROM orders;

5 条订单完整回来。备份的完成标准不是 mysqldump 跑成功,是能导回去。 很多团队的备份策略失效,不是因为没备份,是因为从没验证过恢复。

三、权限:GRANT 背后是逐级匹配模型

CREATE USER 'dev_read'@'%' IDENTIFIED BY 'ReadOnly@123';
GRANT SELECT ON test_ops.* TO 'dev_read'@'%';
FLUSH PRIVILEGES;

背后机制:MySQL 权限存在 mysql 库的 user、db、tables_priv、columns_priv 等表中,权限检查按用户 → 库 → 表 → 列逐级匹配,取并集。

两个运维判断:

'dev_read'@'%' 里的 % 是主机通配,意味着任意 IP 都能连。 生产环境应限制为具体网段(如 'dev_read'@'10.0.1.%'),否则等于把账号暴露给整个网络。

FLUSH PRIVILEGES 在 MySQL 8.0 用 GRANT 语句授权时通常不必要,权限表会自动重载。它真正需要的场景是手动改 mysql 库表之后。知道“什么时候不必要”,比每次无脑敲更能体现理解。

查看授权确认:

SHOW GRANTS FOR 'dev_read'@'%';

输出里只有 GRANT SELECT ON test_ops.*,没有 ALL。最小必要原则。

四、故障定位:从 PROCESSLIST 到锁等待视图

SHOW PROCESSLIST;

看 State 列能区分几类问题:

  • Sending data:在扫描或排序,通常是查询本身慢。
  • Waiting for table metadata lock:DDL 被长事务阻塞,典型场景是有人 ALTER TABLE 卡住了。
  • Locked:行锁等待。

但 SHOW PROCESSLIST 只能看到“在等”,看不到“等谁的锁”。再深一层:

SELECT * FROM performance_schema.data_lock_waits;

这条能直接看出阻塞方和被阻塞方的线程 ID、锁对象。这是从“看现象”到“定位根因”的分界线。

慢查询日志的开关也顺手看一眼:

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

生产环境通常开启并调低阈值,但修改属于 DBA 范畴,运维小白先会看、会判断是否开启即可。

五、一个容易被忽略的变更风险

第三篇留的坑在这里补上。回顾一下:

ALTER TABLE users ADD COLUMN phone VARCHAR(20);

ALTER TABLE 在大表上可能锁表。SHOW PROCESSLIST 里出现 Waiting for table metadata lock,常见原因就是一条 ALTER 排在长事务后面等锁,而后续所有访问这张表的查询又排在 ALTER 后面,形成连锁阻塞。

生产环境做结构变更,要么选低峰期,要么用 pt-online-schema-change / gh-ost 这类在线工具。这是“会用 ALTER”和“敢在生产用 ALTER”之间的差距。

本篇小结

备份一致性靠 --single-transaction 加 InnoDB,权限生效靠逐级匹配模型加主机限制,故障定位从 PROCESSLIST 深入到 data_lock_waits,结构变更要评估锁表风险。这四条是运维的底线。

评论 0

评论需 登录 后发表(注册并验证邮箱)。
还没有评论,来抢沙发~