MySQL 05 - 用户权限与备份恢复

数据库存着最有价值的数据，权限管理与备份是运维的生命线。本章覆盖用户管理、GRANT 授权体系、MySQL 8 角色、mysqldump 逻辑备份、binlog 时间点恢复、定时备份脚本，最后一句话介绍主从复制。

一、用户管理

1.1 创建与修改用户

-- 创建用户：'用户名'@'允许登录的主机'
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'Str0ng!Pass';
CREATE USER 'dev'@'192.168.1.%' IDENTIFIED BY 'Dev#2026Pass';   -- 允许该网段远程
CREATE USER 'admin'@'%' IDENTIFIED BY 'Adm1n!Pass';              -- 任意主机（慎用）
 
-- 修改密码
ALTER USER 'appuser'@'localhost' IDENTIFIED BY 'N3w!Pass2026';
 
-- 修改当前登录者自己的密码
ALTER USER USER() IDENTIFIED BY 'My#NewPass';
 
-- 锁定/解锁账号
ALTER USER 'dev'@'192.168.1.%' ACCOUNT LOCK;
ALTER USER 'dev'@'192.168.1.%' ACCOUNT UNLOCK;
 
-- 删除用户
DROP USER 'dev'@'192.168.1.%';
 
-- 查看所有用户
SELECT user, host FROM mysql.user;

1.2 密码策略

SHOW VARIABLES LIKE 'validate_password%';   -- 查看密码策略组件参数
 
-- MySQL 8 安装策略组件后可调整强度
SET GLOBAL validate_password.policy = MEDIUM;   -- LOW/MEDIUM/STRONG
SET GLOBAL validate_password.length = 12;       -- 最小长度

MEDIUM 级别要求：至少 8 位，包含数字、大小写字母和特殊字符。

二、授权体系

2.1 GRANT 与 REVOKE

MySQL 权限模型一句话：谁能（user@host）在哪个范围（库.表）做什么（权限）。

-- 授予 appuser 对 shop 库所有表的增删改查
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'appuser'@'localhost';
 
-- 授予全库全表所有权限（仅管理员）
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%';
 
-- 授予单表权限
GRANT SELECT ON shop.students TO 'report'@'localhost';
 
-- 收回权限
REVOKE INSERT, DELETE ON shop.* FROM 'appuser'@'localhost';
 
-- 刷新权限（直接改 mysql 库的表时才需要；GRANT 语句自动生效）
FLUSH PRIVILEGES;

2.2 查看权限

SHOW GRANTS FOR 'appuser'@'localhost';   -- 查指定用户
SHOW GRANTS FOR CURRENT_USER();          -- 查自己

2.3 常用权限清单

权限	作用	适用对象
`SELECT` / `INSERT` / `UPDATE` / `DELETE`	四大基本 DML	表
`CREATE` / `DROP` / `ALTER`	建删改表结构	库/表
`INDEX`	创建删除索引	表
`REFERENCES`	创建外键	表
`EXECUTE`	执行存储过程	过程
`FILE`	读写服务器文件（LOAD DATA），高危	全局
`PROCESS`	查看其他会话线程	全局
`REPLICATION SLAVE`	主从复制从库所需	全局
`SUPER`	高危管理权限，8.0 已拆分	全局
`GRANT OPTION`	把自己的权限转授他人，慎给	随授权

最小权限原则：业务账号只给增删改查，报表账号只给 SELECT，root 只留给 DBA 应急。

三、角色（MySQL 8+）

角色是一组权限的打包，方便批量管理：

-- 创建角色并赋权
CREATE ROLE 'app_read', 'app_write';
GRANT SELECT ON shop.* TO 'app_read';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app_write';
 
-- 把角色授予用户（需激活后才生效）
GRANT 'app_write' TO 'appuser'@'localhost';
 
-- 查看与激活角色
SHOW GRANTS FOR 'appuser'@'localhost' USING 'app_write';
SET DEFAULT ROLE ALL TO 'appuser'@'localhost';   -- 登录时自动激活
 
-- 回收角色
REVOKE 'app_write' FROM 'appuser'@'localhost';

四、mysqldump 备份全套

4.1 备份命令矩阵

# 备份单个库（含建表语句 + 数据）
mysqldump -u root -p shop > shop_full.sql
 
# 仅备份表结构（-d 即 --no-data）
mysqldump -u root -p -d shop > shop_schema.sql
 
# 仅备份数据不备份结构
mysqldump -u root -p --no-create-info shop > shop_data.sql
 
# 备份单张表
mysqldump -u root -p shop students > students.sql
 
# 备份多个库
mysqldump -u root -p --databases shop blog > multi.sql
 
# 全库备份（含系统库；加 --single-transaction 保证 InnoDB 一致性且不锁表）
mysqldump -u root -p --all-databases --single-transaction > all_$(date +%F).sql
 
# 压缩备份（大库必用）
mysqldump -u root -p --single-transaction shop | gzip > shop_$(date +%F).sql.gz

常用参数：

参数	作用
`-d` / `--no-data`	只导出结构
`--single-transaction`	InnoDB 一致性快照备份，不锁表
`--routines` / `--triggers`	包含存储过程/触发器
`--source-data=2`	记录备份时的 binlog 位点（MySQL 8.0.26+ 新名称；旧名 `--master-data` 已弃用，MariaDB 仍用旧名）

4.2 恢复

# 方法一：shell 重定向
mysql -u root -p shop < shop_full.sql
 
# 方法二：登录客户端内用 source
mysql -u root -p

USE shop;
SOURCE /backup/shop_full.sql;

恢复压缩包：

gunzip < shop_2026-08-21.sql.gz | mysql -u root -p shop

注意：mysqldump 的输出不含 CREATE DATABASE 时（没加 —databases），要先手动建好目标库。

五、物理备份提一嘴

除了逻辑导出，还可以直接拷贝数据目录。数据目录位置按安装方式不同：

安装方式	默认数据目录
Linux 包管理（apt/pacman/dnf）	`/var/lib/mysql`
macOS Homebrew	`/opt/homebrew/var/mysql`（Intel 机型 `/usr/local/var/mysql`）
Windows 安装包	`C:\ProgramData\MySQL\MySQL Server 8.4\Data`
Docker	容器内 `/var/lib/mysql`（对应宿主机的挂载卷）

必须先停服务或使用 Percona XtraBackup 这类支持热备的工具，冷拷贝才能保证一致性
恢复 = 停服务 → 替换 data 目录 → 改属主为 mysql 用户 → 启动
速度快、适合超大库；跨版本/跨平台兼容性差，一般由专业工具（XtraBackup）完成

六、binlog 与基于时间点恢复

mysqldump 只能恢复到”备份那一刻”，配合 binlog 可以恢复到任意时间点（PITR）。

-- 确认 binlog 开启（MySQL 8 默认开启）
SHOW VARIABLES LIKE 'log_bin';
 
-- 查看所有 binlog 文件
SHOW BINARY LOGS;
 
-- 查看 binlog 内容
SHOW BINLOG EVENTS IN 'binlog.000005';

# 用 mysqlbinlog 工具解析日志
mysqlbinlog --base64-output=decode-rows -v /var/lib/mysql/binlog.000005
 
# 时间点恢复：先恢复昨晚的全量备份，再重放今早 10 点前的 binlog
mysql -u root -p shop < lastnight_backup.sql
mysqlbinlog --start-datetime="2026-08-20 22:00:00" \
            --stop-datetime="2026-08-21 10:00:00" \
            /var/lib/mysql/binlog.000005 | mysql -u root -p

误删表的经典自救流程：立刻停止写入 → 恢复最近全量备份到临时实例 → 用 mysqlbinlog 从备份位点重放到误操作之前一秒 → 导出被删表数据回灌生产。

七、定时备份 cron 脚本

#!/bin/bash
# /opt/scripts/mysql_backup.sh — 每日凌晨全量备份并保留 7 天
set -e
 
# 凭据不要硬编码在脚本里！放在 ~/.my.cnf（权限 600）中：
#   [mysqldump]
#   user=backup_user
#   password=B4ckup!Pass
BACKUP_DIR=/backup/mysql
DATE=$(date +%F)
KEEP_DAYS=7
 
mkdir -p "$BACKUP_DIR"
 
mysqldump --defaults-extra-file="$HOME/.my.cnf" \
    --all-databases --single-transaction --routines --triggers \
    | gzip > "$BACKUP_DIR/all_$DATE.sql.gz"
 
# 删除过期备份
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$KEEP_DAYS -delete
 
# 可选：同步到远端
# rsync -az "$BACKUP_DIR/" backup@nas:/volume1/db_backup/

加入 crontab：

crontab -e
# 每天 02:30 执行，日志追加到文件
30 2 * * * /opt/scripts/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

备份账号只需最小权限：GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';

铁律：没有验证过恢复的备份等于没有备份。

八、主从复制一句话级介绍

主库把写操作记入 binlog，从库的 IO 线程拉取 binlog 写入本地 relay log，SQL 线程重放 relay log，从而让从库与主库保持准实时一致。用途：读写分离（查询走从库）、灾备切换、数据分析隔离。核心配置三件套：主库 server_id + 为从库创建 REPLICATION SLAVE 账号 + 从库 CHANGE REPLICATION SOURCE TO ... 后 START REPLICA。细节展开见任何一本 MySQL 运维书或数据库目录中推荐的进阶资料。

九、本章小结

主题	要点
用户	`CREATE USER 'u'@'host'`，host 决定来源，密码策略用 validate_password 管
授权	GRANT 权限 ON 库.表 TO 用户；最小权限原则
角色	MySQL 8 的权限打包机制，GRANT 角色给用户
逻辑备份	mysqldump 单库/单表/-d 结构/—all-databases 全库
恢复	`mysql < dump.sql` 或客户端 source
时间点恢复	全量备份 + mysqlbinlog 重放到指定时刻

本章与 06 存储过程与触发器、07 主从复制与高可用、08 与后端语言集成、09 高级主题与性能诊断共同构成 MySQL 进阶线。继续横向扩展：PostgreSQL 教程或 SQLite 教程

返回数据库目录

RootStack

探索

05-用户权限与备份恢复