阿源的知识库

切换日光/暗黑模式

用户、权限、备份与恢复 ​

学习目标 ​

  • 区分账号认证、TLS、对象授权与角色激活，建立最小权限边界。
  • 为 InnoDB 选择一致的逻辑备份，理解物理备份与 PITR 的前提。
  • 按恢复顺序验证对象、数据和业务，并记录恢复时间。

核心知识点 ​

账号是 'user'@'host'，相同用户名在不同 host 下是不同账号。认证证明身份，TLS 保护传输，GRANT 决定能操作哪些对象；三者不能互相替代。host 是客户端来源匹配规则，不是数据库名。不要为方便使用 % 或给应用账号 ALL ON *.*；NAT、代理、DNS 与 skip_name_resolve 会影响实际匹配，应在真实连接中检查 CURRENT_USER()。

逻辑备份保存 SQL/对象定义，适合选择库表、迁移和审查；物理备份保存数据库文件，适合大规模恢复，但依赖工具、文件格式、版本、加密密钥与一致性协议。二进制日志记录备份之后的变更，PITR（point-in-time recovery）先恢复完整备份，再重放连续日志到事故前边界。复制不是备份，误删也会被复制。

本篇基线为 MySQL 8.0 / InnoDB，示例使用独立 learning_lab。权限操作由获授权的管理员执行，备份账号与应用账号分离；所有示例名称均为实验专用。恢复目标是另一个隔离实例，不能把改名数据库当作万无一失的沙箱：SQL 文件可能包含 USE、限定库名、DEFINER 或跨库对象。密码、连接串和备份内容均应受权限控制，不能进入 Git、日志或工单。

常用用法 ​

CREATE USER：建立认证身份 ​

用于为指定来源创建独立账号，避免应用共享管理员身份。

sql
-- 风险：CREATE USER改变账号目录，仅在隔离实例由管理员执行；随机密码禁止入日志。
-- 初始：lab_reader@127.0.0.1不存在；服务端已配置TLS。
CREATE USER 'lab_reader'@'127.0.0.1'
  IDENTIFIED BY RANDOM PASSWORD REQUIRE SSL;
-- 关键变化：创建lab_reader账号并要求SSL，尚未授予业务表权限。
-- 结果：8.0.18+返回生成密码，交给秘密管理器后清除终端记录。

MySQL 8.0 默认认证插件是 caching_sha2_password，应选支持它的当前驱动；不要用退回旧插件代替升级客户端。REQUIRE SSL 要求加密，但客户端仍要验证证书链与主机名（CLI 的 --ssl-mode=VERIFY_IDENTITY）。CREATE USER、TLS 连接。

ALTER USER：轮换凭据与限制登录 ​

用于在账号生命周期中轮换密码或锁定不再使用的身份。

sql
-- 风险：ALTER USER影响登录；先准备秘密更新与回退，只锁定实验账号。
-- 初始：lab_reader@127.0.0.1已存在且ACCOUNT UNLOCK。
ALTER USER 'lab_reader'@'127.0.0.1' ACCOUNT LOCK;
-- 关键变化：lab_reader后续认证被拒绝，已有连接不保证断开。
ALTER USER 'lab_reader'@'127.0.0.1' ACCOUNT UNLOCK;
-- 结果：lab_reader解除锁定后可重新认证，业务权限没有改变。

轮换可用 IDENTIFIED BY RANDOM PASSWORD（8.0.18+）；双密码过渡 RETAIN CURRENT PASSWORD / DISCARD OLD PASSWORD 要按实际插件和版本验证，避免长期保留旧密钥。ALTER USER。

GRANT：落实最小权限 ​

用于只给查询账号开放所需库表与操作。

sql
-- 风险：GRANT扩大权限，由管理员审核对象粒度，不授予应用GRANT OPTION。
-- 初始：账号存在，learning_lab.users为本章基础表。
GRANT SELECT ON learning_lab.users TO 'lab_reader'@'127.0.0.1';
-- 关键变化：允许读users，不能写orders或修改表结构。
SHOW GRANTS FOR 'lab_reader'@'127.0.0.1';
-- 输出：授权集合含SELECT ON learning_lab.users，不含全局ALL。

MySQL 8.0 用 CREATE USER 创建账号，再 GRANT；不要沿用 GRANT 顺带创建用户的旧教程。备份、迁移与运维账号单独授权，不把高权限嵌入应用。GRANT。

REVOKE：收回对象权限 ​

用于撤销不再需要的访问，复核权限是否仍经角色继承。

sql
-- 风险：REVOKE影响访问，只收回实验账号权限并用新连接验证。
-- 初始：lab_reader直接拥有users的SELECT。
REVOKE SELECT ON learning_lab.users FROM 'lab_reader'@'127.0.0.1';
-- 关键变化：移除lab_reader直接SELECT授权，不移除角色授予的权限。
SHOW GRANTS FOR 'lab_reader'@'127.0.0.1';
-- 结果：直接授权清单不再含users SELECT。

权限刷新规则随权限层级而异，管理验收采用新连接做允许/拒绝测试；正常账号管理语句无需额外 FLUSH PRIVILEGES。REVOKE。

SHOW GRANTS：核对身份与权限 ​

用于从当前真实连接验证命中了哪个账号，以及直接或角色授权。

sql
-- 初始：使用lab_reader从127.0.0.1新建连接。
SELECT USER(),CURRENT_USER(),CURRENT_ROLE();
SHOW GRANTS;
-- 关键变化：USER()显示提交身份，CURRENT_USER()返回实际匹配的授权身份。
-- 输出：CURRENT_USER()应为lab_reader@127.0.0.1；角色以实际激活集为准。

SHOW GRANTS 不是完整行为审计。需要记录谁在何时执行何类操作时，应评估审计插件/托管平台日志、保留策略和脱敏；不要把 general log 长期开启当作低成本审计。SHOW GRANTS、账号匹配。

CREATE ROLE：复用权限集合 ​

用于把一组只读权限赋给角色，再授予多个账号。

sql
-- 风险：CREATE ROLE后授权影响所有成员，只操作实验角色。
-- 初始：lab_read_role不存在，lab_reader已存在。
CREATE ROLE 'lab_read_role';
GRANT SELECT ON learning_lab.users TO 'lab_read_role';
GRANT 'lab_read_role' TO 'lab_reader'@'127.0.0.1';
-- 关键变化：lab_reader获得lab_read_role成员资格，尚不保证默认激活。
SHOW GRANTS FOR 'lab_reader'@'127.0.0.1' USING 'lab_read_role';
-- 输出：可看到该角色提供的users SELECT。

角色与用户的账号表示规则相通，但角色用于组织授权；角色成员资格与会话激活是两步。Using Roles。

SET DEFAULT ROLE：指定登录后的激活角色 ​

用于让已经授予的角色在后续登录时自动激活。

sql
-- 风险：SET DEFAULT ROLE改变新连接有效权限，先确认角色无额外写权限。
-- 初始：lab_reader已获授lab_read_role。
SET DEFAULT ROLE 'lab_read_role' TO 'lab_reader'@'127.0.0.1';
-- 关键变化：设置登录默认角色；用lab_reader新连接执行下列语句。
SELECT CURRENT_ROLE();
-- 输出：激活角色包含lab_read_role（按服务器格式显示host）。

已有会话可用 SET ROLE DEFAULT 激活默认集合；仅 GRANT 角色而不激活，查询仍可能被拒绝。

DROP USER：注销实验身份 ​

用于清理失效账号，在删除前核对对象 DEFINER 与依赖。

sql
-- 风险：DROP USER丢失授权，先核对DEFINER依赖与小版本限制；只清理实验身份。
-- 初始：lab_reader@127.0.0.1实验结束且不被任何DEFINER引用。
DROP USER 'lab_reader'@'127.0.0.1';
-- 关键变化：移除账号及授权，users数据保持不变。
SELECT COUNT(*) FROM learning_lab.users;
-- 结果：基础表仍可由管理员查询，原账号无法建立新连接。

DROP USER 不等于终止全部已有会话，也不是删除该用户“拥有”的全部表。

mysqldump：生成一致的逻辑备份 ​

用于把独立 InnoDB 实验库的结构、行数据及存储对象导出成可恢复 SQL。

shell
# 初始：learning_lab只含InnoDB表，备份目录可写；密码交互输入。
# 风险：mysqldump仅在隔离实例演练，确认备份一致性与磁盘空间。
mysqldump --host=127.0.0.1 --user=lab_backup --password --single-transaction --quick --routines --events --triggers --no-tablespaces --set-gtid-purged=OFF --result-file=learning_lab.sql learning_lab
# 关键变化：事务一致性快照流式读取，SQL文件保存对象和数据。
# 结果：检查退出码为0、文件非空，并保存工具/服务端版本与文件校验和。

--single-transaction 对事务表提供一致数据快照，不保证 MyISAM 等非事务表；备份期间 ALTER/CREATE/DROP/RENAME/TRUNCATE 等 DDL 可使快照失败或不一致。它关闭 --lock-tables，但不能说导出完全没有锁；长快照还会增加 undo 保留。--quick 逐行读取，避免把整张表放入客户端内存。--no-tablespaces 省略表空间语句，不能备份相应布局；--set-gtid-purged=OFF 适合本次独立库演练，不是复制初始化/GTID迁移的通用选项。

触发器默认导出，routines/events 要显式选取；普通单库导出不包含账号与角色，也不包含实例配置、binlog、插件或加密密钥。不要跨版本直接导入 mysql 系统表恢复权限，应单独审查账号/角色 DDL、秘密及 DEFINER。SELECT、SHOW VIEW、TRIGGER、存储过程/事件及日志坐标的权限随对象和版本变化；8.0文档的--routines要求全局SELECT，范围明显大于业务单表只读权限，生产须评估工具和对象所需的授权。按 mysqldump 的必要权限逐项授予，不给备份账号应用写权限。

MySQL Shell dump/load utilities 适合并行导出与导入；mysqlpump 从 8.0.34 起弃用，不作为新工作流。托管数据库可能限制文件、权限与日志访问，应采用提供商的快照/PITR工具并实测导出可迁移性。

mysql：在隔离实例恢复逻辑备份 ​

用于先恢复完整基线，再核对数据与对象，而不是直接覆盖生产库。

shell
# 风险：mysql恢复SQL可修改数据与DEFINER，仅在隔离实例3307执行已审查备份。
# 初始：端口3307是空的隔离恢复实例，已有空库learning_lab；SQL文件已审查。
mysql --host=127.0.0.1 --port=3307 --user=lab_restore --password --database=learning_lab --execute="SOURCE learning_lab.sql"
# 关键变化：按SQL文件顺序恢复表结构与数据及选中的存储对象。
# 结果：退出码应为0；任何错误须调查，不能使用--force忽略后宣称成功。

恢复顺序是准备兼容版本/字符集/时区与配置 → 恢复所需账号角色/DEFINER身份（暂不开放应用）→ 恢复结构、数据及存储对象 → 必要时重放binlog → 验证 → 授予上线权限并切换。禁止恢复期间调度事件或向真实外部系统发通知；如果备份含事件，检查全局 event_scheduler 与事件状态。大库物理恢复需按工具恢复完整实例，再处理凭据和业务流量。SQL 文件执行。

SHOW BINARY LOGS：检查 PITR 日志链 ​

用于确认日志保留情况，并把完整备份与之后的日志边界关联。

sql
-- 初始：管理员在启用binlog的实验实例检查日志；读取需要相应监控权限。
SHOW VARIABLES LIKE 'log_bin';
SHOW BINARY LOGS;
-- 关键变化：读取log_bin和现存binlog文件清单，不修改日志。
-- 输出：log_bin应为ON；文件名/大小取决于当前实例，不能从名称猜测恢复点。

PITR 需要备份对应的精确日志坐标/GTID、完整连续日志及校验；8.0.26+ 的 --source-data=2（旧名 --master-data）可在 mysqldump 中记录坐标，和 --single-transaction 组合仍可能短暂获取全局读锁且需要额外权限，不要直接加到生产导出。通过 mysqlbinlog 审查事件后，在隔离实例按位置/GTID重放至误操作前；时间筛选适合定位，精确边界还要核对事务，不能截断半个事务。日志不齐时不能承诺恢复到任意时间。PITR。

SELECT physical backup prerequisites：核对物理备份条件 ​

用于在选物理工具前记录版本、数据目录与加密条件。

sql
-- 初始：有权限的管理员只读检查MySQL 8.0实验实例。
SELECT VERSION(),@@datadir,@@innodb_page_size;
-- 关键变化：取得VERSION、datadir与innodb_page_size，保存工具兼容性信息。
-- 结果：将实际版本、目录与页大小写入恢复清单，不凭这些值直接复制在线文件。

物理备份必须使用支持该版本的工具（例如适用授权的 MySQL Enterprise Backup）或经过验证的停机/一致存储快照协议；在线直接复制 datadir 不是可靠备份。热备工具通常要 prepare/apply-log，再在停止目标服务时恢复文件，保证权限、redo/undo、keyring、插件与配置兼容。托管快照依赖供应商恢复流程；文件校验通过不代表实例能够启动。备份方法。

SELECT restore drill：验证恢复质量 ​

用于把“有备份文件”变成“能在约定时间恢复并运行业务”的证据。

sql
-- 初始：在3307恢复实例完成导入，本章基础orders有101/25、102/15、103/40。
SELECT COUNT(*) AS rows_restored,SUM(amount) AS amount_sum
FROM learning_lab.orders WHERE id IN(101,102,103);
SELECT id,user_id,amount FROM learning_lab.orders
WHERE id IN(101,102,103) ORDER BY id;
-- 关键变化：比较orders的COUNT(*)、SUM(amount)及有序业务字段与备份前清单。
-- 输出：未更改基础数据时为3/80，以及三条对应订单；否则按备份前清单核对。

恢复演练必须覆盖文件校验和、对象/索引/约束/视图/例程/事件、行数和业务聚合，再跑应用读写、权限拒绝及关键业务检查。对大表按稳定键分段做确定序列化的数据摘要；只比较行数无法发现值被改。记录备份耗时、恢复耗时、RPO（可丢失时间）、RTO（恢复时间）、日志边界和错误；定期在隔离环境实演，不能等事故才第一次恢复。

易混点 ​

  • 用户认证成功不等于有表权限；角色授予成功也不等于会话已经激活。
  • TLS加密与服务端身份验证是不同检查，生产客户端应验证证书和主机名。
  • 逻辑快照不是实例全量备份，账号、配置、日志和密钥需要独立计划。
  • 物理复制、复制副本和托管快照都要满足一致性及可恢复性条件。
  • 导出退出码正常只是起点，恢复成功还需要结构、数据和应用验证。

课后小问 ​

  1. 为什么只授予角色后仍可能读不了表？ 答案：成员资格和激活角色是两步。 解析：检查 CURRENT_ROLE()，配置默认角色并用新连接验证；同时核对命中的user@host。

  2. 为什么 single-transaction 期间仍要求禁止 DDL？ 答案：一致性快照保护事务表的数据读取，不能把并发结构变更冻结成统一定义。 解析：DDL可能改变或删除正在导出的对象，导致错误或不一致；备份窗口需控制结构变更。

  3. 为什么恢复后要运行应用检查？ 答案：文件和行数不能证明权限、DEFINER、时区或业务约束正确。 解析：在隔离实例验证读写和关键聚合，才能发现“导入没报错但应用不可用”。

本节小结 ​

  • 使用独立账号、受控来源和最小授权管理身份，分别验证认证、TLS和权限。
  • 逻辑、物理与binlog各有职责，备份方案必须保存对应的恢复前提。
  • 恢复先隔离目标并检查SQL，按对象与日志顺序恢复后验证业务。
  • 定期恢复演练并记录RPO/RTO，才有可复核的恢复能力证据。

快速回顾 ​

  • CREATE/ALTER/DROP USER负责身份，GRANT/REVOKE负责对象操作权限。
  • 角色要授予并激活，SHOW GRANTS与真实连接拒绝测试共同验收。
  • mysqldump快照需要事务表和DDL边界，不能代替账号与实例配置备份。
  • 完整备份加连续binlog支持PITR，精确边界与实际演练必须核对。