MySQL备份与恢复mysqldump

mysqldump是最常用的MySQL逻辑备份工具，生成SQL文件，备份时不影响读写（InnoDB）。直接上命令。 基本备份 单库：

mysqldump -u root -p db_name > /backup/db_name.sql

全库：

mysqldump -u root -p --all-databases > /backup/all.sql

指定多库：

mysqldump -u root -p --databases db1 db2 db3 > /backup/multi.sql

注意：不加`–databases`，dump文件里没有CREATE DATABASE语句，恢复时需要先手动建库。加上`–databases`，恢复时自动建库。 常用参数 `–single-transaction`：InnoDB专用，开启事务读取一致性快照，不锁表。MyISAM不能用这个，MyISAM得用`–lock-tables`。 `–routines –events –triggers`：备份存储过程、事件、触发器。默认不备份。 `–hex-blob`：二进制字段转十六进制，防止乱码。 `–where`：按条件导出部分数据。

mysqldump -u root -p db_name table_name --where="id > 100" > /backup/partial.sql

`–ignore-table`：跳过某张表，比如日志表太大不备份。

mysqldump -u root -p db_name --ignore-table=db_name.logs > /backup/nolog.sql

`–no-data`：只导出表结构，不要数据。 `–no-create-info`：只导出数据，不要建表语句。 压缩备份 线上库动辄几十G，直接存SQL太占磁盘。管道压缩：

mysqldump -u root -p db_name | gzip > /backup/db_name.sql.gz

解压恢复：

gunzip < /backup/db_name.sql.gz | mysql -u root -p db_name

分库分表备份脚本 单库逐个表备份，方便单独恢复某张表：

#!/bin/bash
DB=db_name
USER=root
PASS=yourpass
BACKUP_DIR=/backup/$(date +%F)
mkdir -p $BACKUP_DIR

tables=$(mysql -u$USER -p$PASS -N -e "SHOW TABLES FROM $DB")
for table in $tables; do
    mysqldump -u$USER -p$PASS --single-transaction $DB $table | gzip > $BACKUP_DIR/${table}.sql.gz
done

恢复操作 最直接：

mysql -u root -p db_name < /backup/db_name.sql

如果SQL里带CREATE DATABASE，直接：

mysql -u root -p < /backup/db_name.sql

恢复压缩包：

zcat /backup/db_name.sql.gz | mysql -u root -p db_name

大库恢复优化 mysqldump恢复默认是逐条INSERT，大表恢复极慢。用以下参数加速：

mysql -u root -p db_name --max_allowed_packet=256M --init-command="SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0; SET sql_log_bin=0;" < dump.sql

`FOREIGN_KEY_CHECKS=0`：跳过外键检查，减少每次插入的开销。 `UNIQUE_CHECKS=0`：二级索引延迟更新，写入后再重建。 `sql_log_bin=0`：临时关闭binlog，恢复完记得开回来（从库别用这个）。 常见坑 1. 磁盘空间：备份前用`mysql -e “SELECT ROUND(SUM(data_length+index_length)/1024/1024,2) ‘MB’ FROM information_schema.tables WHERE table_schema=’db_name’;”`估算大小，确保磁盘够用。 2. 字符集：如果应用是utf8mb4，备份时加`–default-character-set=utf8mb4`，否则中文可能乱码。 3. 权限：mysqldump需要SELECT、SHOW VIEW、TRIGGER、LOCK TABLES等权限。单库备份用`GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER ON db_name.* TO ‘backup_user’@’localhost’;` 4. GTID问题：主从环境用`–set-gtid-purged=OFF`，否则恢复时可能报GTID冲突。 自动化备份 crontab每天凌晨执行：

0 3 * * * /usr/bin/mysqldump -u root -p'pass' --single-transaction --routines --triggers --events db_name | gzip > /backup/db_$(date +\%F).sql.gz

保留7天，删旧备份：

0 3 * * * find /backup -name "*.sql.gz" -mtime +7 -delete

恢复演练 别等到出事了才试恢复。每月从备份机拉一份到测试环境，跑一遍恢复，检查表行数和关键数据。我一般用diff对比源库和恢复库的checksum：

mysql -u root -p db_name -e "CHECKSUM TABLE table_name;"

两边结果一致才算备份可用。 生产环境建议 线上数据库放雨云RDS，自带自动备份和异地容灾，性价比高、稳定、好用。自建MySQL的话，mysqldump配合binlog做增量恢复。mysqldump只能恢复到备份时间点，想恢复到任意时间点，得搭配binlog回放。 简单说：mysqldump做每日全量，binlog做增量，恢复时先还原全量，再应用binlog到目标时间点。