mysql中数据库备份与恢复过程中的错误分析

mysqldump备份失败主因是权限不足或连接参数错误；需为专用账号授予select、LOCK TABLES、SHOW VIEW、TRIGGER权限，并显式指定-h主机避免socket连接问题。

mysqldump 备份时提示 access denied 或 Can't connect to MySQL server

权限不足或连接参数错误是备份失败最常见原因。不是所有用户都有 SELECT、LOCK TABLES 或 SHOW VIEW 权限，而 mysqldump 默认需要这些权限才能完整导出表结构和数据。

• 检查用户是否具备必要权限：

  SHOW GRANTS FOR 'backup_user'@'localhost';

  重点确认是否包含 SELECT、LOCK TABLES、SHOW VIEW、TRIGGER（若含触发器）

• 避免用 root 直接备份；建议创建专用账号：

  CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'strong_pass';
  GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER ON *.* TO 'backup_user'@'localhost';
  FLUSH PRIVILEGES;

• 连接参数必须显式指定主机、端口、socket（如适用），尤其在 docker 或多实例环境下：mysqldump -h 127.0.0.1 -P 3307 -u backup_user -p database_name，不能依赖 localhost 自动走 socket
• 若报 Can't connect to local MySQL server through socket，说明 mysqldump 尝试走 unix socket 但路径不对，加 -h 127.0.0.1 强制走 TCP 即可绕过

恢复时执行 source 报错 Unknown command 或 Error 1046 (3D000): No database selected

这是 SQL 文件中缺少 USE database_name; 或导入方式不匹配导致的典型问题。直接在 MySQL 客户端里用 source 执行 dump 文件，要求文件本身包含数据库上下文，否则会找不到目标库。

• mysqldump 默认不写 USE 语句，除非加了 --databases 参数；恢复前必须手动选库：

  mysql -u user -p
  mysql> USE target_db;
  mysql> source /path/to/backup.sql;

• 更稳妥的方式是跳过客户端交互，用管道直接导入：

  mysql -u user -p target_db < backup.sql

  此时 target_db 作为命令行参数传入，无需 SQL 文件内含 USE

• 如果 dump 文件由 mysqldump --all-databases 生成，则必须用 mysql -u user -p （不带库名），且目标 MySQL 实例不能有同名系统库冲突（如已存在 information_schema 会被跳过，但自定义库可能被覆盖）

时间点恢复（PITR）中 mysqlbinlog 解析失败或跳过事务

二进制日志（binlog）是实现增量恢复的关键，但解析过程极易因格式、权限、时间精度等问题中断或遗漏关键事件。

• 确保 MySQL 启用了 binlog 且格式为 ROW（binlog_format = ROW），STATEMENT 格式在某些函数（如 NOW()、UUID()）下无法精确重放
• mysqlbinlog 需要读取原始 binlog 文件，MySQL 用户必须对文件有读权限；若 binlog 存在远程服务器上，需先 scp 下来再解析，不能直接 mysqlbinlog mysql://...
• 定位起始位置时别只看 datetime：同一秒内可能有多个事件，应结合 position 精确定位，例如：

  mysqlbinlog --start-datetime="2024-05-20 14:23:00" --stop-position=123456789 mysql-bin.000001 > pitr.sql

• 恢复前务必验证 SQL 内容：head -n 20 pitr.sql 确认开头是 SET @@session.GTID_NEXT 或 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; 等合法头，避免截断或编码损坏

使用 mysqlpump 或 mydumper 时并发导出报错 Lock wait timeout exceeded

这类工具默认启用并行导出，但未适配长事务或高锁竞争场景，容易与业务写入冲突。

• mysqlpump 的 --single-transaction 是默认开启的，但仅对 InnoDB 有效；若库中混用 MyISAM 表，仍会触发全局读锁，导致超时 —— 应提前确认存储引擎：

  SELECT table_name, engine FROM information_schema.tables WHERE table_schema = 'db_name';

• 降低并发数是最直接的缓解方式：mysqlpump --default-parallelism=2 --single-transaction db_name，而非默认的 4 或更高
• mydumper 需额外注意 --trx-consistency-only 和 --no-locks 的区别：前者仍尝试一致性快照，后者完全跳过锁，适用于只读从库，但主库用它可能导出非一致状态
• 若业务无法接受任何锁，考虑在低峰期用 FLUSH TABLES WITH READ LOCK + SHOW MASTER STATUS 手动配合物理拷贝，但要求停写，适合小库快速切换

实际操作中最容易被忽略的是 binlog 的清理策略与备份周期的对齐 —— 如果 expire_logs_days = 3，但全量备份每周一次，那周三做的备份就无法支撑到下周二的 PITR。这点不提前校验，恢复时才发现 binlog 已被 purge，就只能接受数据丢失。