数据库基础05-数据库运维实践
数据库运维实践：权限、备份与排错
目录
进入运维实践
前三篇学会了装库、配库、写 SQL。这一篇是运维真正每天要做的三件事：把账号权限管好（安全）、把数据备份好（保命）、遇到问题能排掉（救火）。对数据库运维来说，这三块的分量不亚于 SQL 本身。
一、用户与权限管理
1.1 为什么要单独管数据库用户
生产环境绝不用 root 连接业务数据库。要像 Linux 里给不同人/不同程序分配不同账号一样，为每个应用、每个运维角色创建最小权限的数据库账号：能读的只给读，能操作某库的只给那个库。这样即使某个账号口令泄露，破坏范围也有限。
1.2 创建用户：CREATE USER
在 MariaDB 10.4+ / MySQL 8.0 中，创建用户与授权是两步（先 CREATE USER 建账号，再 GRANT 给权限）。
-- 创建本地用户 app，密码为 xxxxxxxx
CREATE USER 'app'@'localhost' IDENTIFIED BY 'xxxxxxxx';
-- 创建允许从任意主机连接的用户（% 表示任意主机）
CREATE USER 'app'@'%' IDENTIFIED BY 'xxxxxxxx';
-- 查看有哪些用户
SELECT User, Host FROM mysql.user;
'用户名'@'主机' 里的**主机部分（host）**是连接来源限制，是数据库安全的重要一环：localhost 只允许本机连，% 允许任意主机，也可以写具体 IP（如 '192.168.1.%' 只允许该网段）。
MySQL 8.0 与 MariaDB 的一个差异：MySQL 8.0 里 GRANT 不再隐式创建用户，必须先 CREATE USER；而 MariaDB 的 GRANT 仍可隐式创建用户。为兼容两者、也为了语义清晰，统一先 CREATE USER 再 GRANT。
1.3 授权：GRANT 与查看
权限分库级/表级与操作级。常见操作权限：SELECT（查）、INSERT（增）、UPDATE（改）、DELETE（删）、CREATE（建表/建库）、DROP（删表/删库）、ALTER（改表）、ALL PRIVILEGES（该范围内的所有权限）。
-- 给 app 用户授予 shop 库所有表的增删改查权限
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app'@'localhost';
-- 只读权限（报表账号常用）
GRANT SELECT ON shop.* TO 'reader'@'%';
-- 授予某库的所有权限
GRANT ALL PRIVILEGES ON shop.* TO 'admin'@'localhost';
-- 授予所有库的某权限（谨慎）
GRANT SELECT ON *.* TO 'monitor'@'localhost';
-- 查看某用户的权限
SHOW GRANTS FOR 'app'@'localhost';
-- 查看当前登录用户自己的权限
SHOW GRANTS;
语法说明：ON 库名.表名，其中 shop.* 表示 shop 库的所有表，*.* 表示所有库所有表。
1.4 回收与删除：REVOKE、DROP USER
-- 回收某权限
REVOKE DELETE ON shop.* FROM 'app'@'localhost';
-- 删除用户（连同其权限）
DROP USER 'app'@'localhost';
-- 改密码（推荐用 ALTER USER）
ALTER USER 'app'@'localhost' IDENTIFIED BY 'newpass';
1.5 权限修改后要不要 FLUSH
在 MySQL/MariaDB 中，用 SQL 语句修改权限后会自动生效，一般无需手动 FLUSH PRIVILEGES。FLUSH PRIVILEGES 主要在直接改了授权表（如手动编辑 mysql.user）或从备份恢复授权表时使用。正常用 GRANT/REVOKE 管理，不必每次都 FLUSH。
1.6 与 Linux 权限的呼应（异同要分清）
数据库的用户与权限模型和 Linux 的 POSIX 权限模型理念相通、机制不同：
| 维度 | Linux 权限 | 数据库（MySQL/MariaDB）权限 |
|---|---|---|
| 用户 | 系统用户（/etc/passwd） |
数据库账号（mysql.user 表） |
| 权限对象 | 文件/目录（r/w/x 三位） | 库/表/列（SELECT/INSERT/...） |
| 权限授予 | chmod、chown、属主/属组 |
GRANT / REVOKE |
| 用户归属 | 属组（group） | 无「组」概念，但可用 host 区分来源 |
| 管理入口 | root 或 sudo | root（mysql 超级用户） |
| 主机限制 | 无（靠网络层） | 'user'@'host' 限定来源 |
一个形象的类比：Linux 的 root 对应数据库的 root（超级用户），都是「最高权限、慎用」；Linux 给普通用户/组分配文件权限，数据库给应用账号分配库表权限——思路一致，都是最小权限 + 职责分离。
1.7 最小权限实践建议
- 每个应用/服务单独建账号，不共用 root；
- 只给该应用实际用到的库的实际用到的操作（通常
SELECT/INSERT/UPDATE/DELETE即可，别给DROP）； host尽量收紧：应用和数据库同机就用localhost，跨机才放开到内网网段，避免%满天飞；- 密码用强口令，敏感环境可配密码策略；
- 定期
SHOW GRANTS巡检，回收不再需要的账号（DROP USER）。
二、数据备份与恢复
对运维来说，备份是数据库工作中最重要、也最不能出事的一环。丢代码可以重写，丢业务数据往往无法挽回。本文讲最常用的逻辑备份工具 mysqldump。
2.1 备份的思路与类型
数据库备份按方式大致分两类：
| 类型 | 方式 | 特点 | 常用工具 |
|---|---|---|---|
| 逻辑备份 | 导出 SQL 语句/文本 | 可读、可部分恢复、体积大、恢复慢 | mysqldump |
| 物理备份 | 直接复制数据文件 | 恢复快、依赖存储引擎/版本 | mariadb-backup/xtrabackup、冷备复制 datadir |
新手入门先掌握 mysqldump（逻辑备份），理解原理后再接触物理备份。
2.2 用 mysqldump 做备份
mysqldump 把数据库导出成一个 SQL 文本文件，里面是建库建表 + 插入数据的语句，将来用这个文件就能重建数据库。
# 备份单个数据库（含建库语句）
$ mysqldump -u root -p --databases shop > /backup/shop.sql
# 备份所有数据库
$ mysqldump -u root -p --all-databases > /backup/all.sql
# InnoDB 推荐加 --single-transaction：备份时不锁表，保证一致性
$ mysqldump -u root -p --single-transaction --databases shop > /backup/shop.sql
# 附带存储过程/函数与触发器
$ mysqldump -u root -p --routines --triggers --databases shop > /backup/shop.sql
参数说明：
--databases shop：导出 shop 库，且 SQL 里含CREATE DATABASE，恢复时自动建库；--all-databases：导出所有库（含系统库，常用于整机迁移/全量备份）；--single-transaction：InnoDB 下在单个事务内导出，读到的是一致快照，不锁表，在线备份时推荐加；--routines/--triggers：连同存储过程、函数、触发器一起导出（默认不包含存储过程）。
注意：mysqldump 是逻辑备份，适合中小规模数据；数据量达到 GB 级以上时，恢复速度慢，需要考虑物理备份（mariadb-backup/xtrabackup）。
2.3 恢复备份
恢复就是把备份的 SQL 文件喂回数据库执行。用客户端 mysql 重定向：
# 恢复单个库（备份时用了 --databases 会自动建库）
$ mysql -u root -p shop < /backup/shop.sql
# 恢复所有库
$ mysql -u root -p < /backup/all.sql
恢复是追加执行 SQL：如果目标库里已有同名表，mysqldump 默认导出的语句含 DROP TABLE IF EXISTS，会先删掉再重建，所以一般能覆盖。但恢复前仍建议确认目标库状态，避免误覆盖。
2.4 备份策略要点
只备份不验证等于没备份。几个必须做对的点：
- 定期 + 自动化：用
cron定时跑备份脚本（对应「定时任务」文档），例如每天凌晨全量备份； - 异地/多份：不要把备份只放在本机（机器挂了备份也没了），至少备份到另一台机器或对象存储；
- 定期恢复演练：定期把备份恢复到一个测试实例，确认备份可用来恢复——这才是备份有效的唯一证明；
- 保留策略：按业务要求保留多天/多份，避免单份备份损坏或误删后无从恢复；
- 注意权限：备份文件里含明文数据，注意文件权限，别让普通用户可读。
一个简单的每日备份脚本示例：
#!/bin/bash
BACKUP_DIR=/backup/db
mkdir -p "$BACKUP_DIR"
mysqldump -u root -p'PASSWORD' --single-transaction --databases shop \
> "$BACKUP_DIR/shop_$(date +%F_%H%M).sql"
find "$BACKUP_DIR" -name '*.sql' -mtime +7 -delete # 保留 7 天，删更早的
配合 crontab -e 定时执行即可（见「定时任务」文档）。
三、常见问题与排错
运维排数据库问题，核心是看服务状态、日志、连接与权限。下面列最常见的几类。
3.1 客户端连不上（ERROR 2002 Can't connect）
ERROR 2002 (HY000): Can't connect to local MySQL server 通常意味着服务没起来或socket 连不上。排查顺序：
# 1. 服务是否在运行
$ systemctl status mariadb
# 2. 端口是否监听
$ ss -tlnp | grep 3306
# 3. 看错误日志
$ sudo tail -n 50 /var/log/mysql/error.log # Debian 系
$ sudo tail -n 50 /var/log/mariadb/mariadb.log # RHEL 系（视配置）
# 4. 确认 socket 文件存在且权限可读
$ ls -l /run/mysqld/mysqld.sock # Debian 系
$ ls -l /var/lib/mysql/mysql.sock # RHEL 系可能在此
若服务没起，用 systemctl start mariadb 启动并 systemctl enable --now mariadb 设开机自启，再看日志确认启动报错。
3.2 认证失败（ERROR 1045 Access denied）
ERROR 1045 (28000): Access denied for user 'xxx'@'host' 常见原因与对策：
- 密码错误：确认密码，或用
ALTER USER ... IDENTIFIED BY ...重设； - 该用户不允许从这个 host 连接：报错里的
'xxx'@'host'是实际命中不到的账号，检查是否创建了'xxx'@'localhost'而你在用%来源连，或反过来——按第一篇 1.2 节的'user'@'host'规则补建账号； - 用户根本没有权限：用
SHOW GRANTS FOR 'xxx'@'host';查看，缺权限就GRANT。
3.3 忘记 root 密码怎么恢复
忘记 root 密码时，可以通过跳过授权表方式进入数据库重置密码。步骤（以 MariaDB 为例）：
# 1. 停止服务
$ sudo systemctl stop mariadb
# 2. 以跳过授权验证的方式后台启动
$ sudo mysqld_safe --skip-grant-tables --skip-networking &
# 3. 无密码连接
$ sudo mysql -u root
-- 4. 进入后刷新权限并重置密码
MariaDB> FLUSH PRIVILEGES;
MariaDB> ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';
MariaDB> FLUSH PRIVILEGES;
MariaDB> EXIT;
# 5. 结束临时进程，正常启动服务
$ sudo killall mysqld_safe
$ sudo systemctl start mariadb
--skip-grant-tables 会跳过权限校验，非常危险，务必配合 --skip-networking（只允许本机 socket 连接）使用，且只用于应急恢复、改完立刻恢复正常模式。
3.4 服务起不来：看日志
服务起不来时先看日志定位原因。常见原因：
- 数据目录权限不对：datadir（如
/var/lib/mysql）属主必须是mysql用户，chown -R mysql:mysql /var/lib/mysql； - 端口被占用：另一个 MySQL/MariaDB 实例或应用占了 3306，
ss -tlnp | grep 3306确认； - 配置文件语法错误：改过
my.cnf后启动失败，检查配置项写法； - 磁盘满：
df -h看 datadir 所在分区是否满； - SELinux/AppArmor 拦截：报权限被拒又查不出文件权限问题时，按日志、权限位、SELinux 上下文逐一排查。
启动失败后 tail 错误日志通常能直接看到原因。
3.5 中文乱码
乱码几乎都是字符集不一致导致的（客户端、连接、库/表、数据 4 处要一致）。建库时用 utf8mb4，连接时指定：
$ mysql -u root -p --default-character-set=utf8mb4
或在连接后执行 SET NAMES utf8mb4;。已存在的乱码数据修复比较麻烦，最好从源头建库就用 utf8mb4。
3.6 慢查询排查
业务变慢时，先确认是否数据库慢查询导致：
-- 开启慢查询日志（运行时开启）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- 超过 1 秒的查询记入日志
-- 对可疑 SQL 用 EXPLAIN 看是否走了索引
EXPLAIN SELECT * FROM user WHERE email = 'a@ex.com';
缺索引就加索引，往往是治本之法。
四、总结
- 用户权限：
CREATE USER+GRANT分开（MySQL 8.0 中 GRANT 不再隐式建用户）；'user'@'host'限定来源；REVOKE/DROP USER回收与删除；权限修改自动生效、不必每次FLUSH；与 Linux 权限理念相通但机制不同，坚持最小权限； - 备份恢复：
mysqldump逻辑备份（InnoDB 加--single-transaction），mysql < 备份.sql恢复；要定期、异地、演练； - 排错：先看服务状态与日志，再查连接、权限、字符集；忘 root 密码用
--skip-grant-tables应急恢复；EXPLAIN排查慢查询。
装好库、写会 SQL、管好权限、做好备份、能排错——这套能力就是中级运维在数据库方面的完整闭环。
参考资料
- MySQL 官方手册（CREATE USER）：https://dev.mysql.com/doc/refman/8.0/en/create-user.html
- MySQL 官方手册（GRANT 语句）：https://dev.mysql.com/doc/refman/8.0/en/grant.html
- MySQL 官方手册（账号管理语句）：https://dev.mysql.com/doc/refman/8.0/en/account-management-statements.html
- MySQL 官方手册（mysqldump）：https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html
- MariaDB 官方知识库（mysqldump）：https://mariadb.com/kb/en/mysqldump/
- MariaDB 官方知识库（备份与恢复总览）：https://mariadb.com/kb/en/backup-and-restore-overview/
- MariaDB 官方（unix_socket 认证插件）：https://mariadb.com/docs/server/reference/plugins/authentication-plugins/authentication-plugin-unix-socket
- 菜鸟教程·MySQL 教程：https://www.runoob.com/mysql/mysql-tutorial.html