二十一、mysqldump 数据库备份

1、mysqldump 命令

用于将数据库或表的结构和数据导出为 SQL 脚本文件（纯文本）。它适用于备份、迁移、复制数据库等场景。

• 输出是标准 SQL 语句（如 CREATE TABLE, INSERT INTO），可直接用 mysql 命令恢复。

mysqldump [选项] [数据库名 [表名...]] > 备份文件.sql

常用选项：

// 1. 连接选项
-h 主机名           # 数据库服务器地址（默认localhost）
-P 端口号           # 端口（默认3306）
-u 用户名           # 用户名
-p                 # 提示输入密码
--password=密码     # 直接指定密码

// 2. 输出内容控制
--all-databases      # 备份所有数据库
--databases db1 db2  # 备份多个指定数据库
--tables tb1 tb2     # 只备份指定表
--no-data            # 只备份结构，不备份数据
--no-create-info     # 只备份数据，不备份结构
--where="条件"        # 按条件备份数据

// 3. 格式控制
--compact              # 简化输出（去掉注释）
--complete-insert      # 使用完整的INSERT语句
--extended-insert      # 使用多值INSERT语句（默认）
--skip-extended-insert # 每行一个INSERT
--hex-blob             # 用16进制格式导出二进制数据

// 4. 事件、存储过程等
--events            # 备份事件
--routines          # 备份存储过程和函数
--triggers          # 备份触发器（默认包含）
--skip-triggers     # 不备份触发器

// 5. 锁和事务
--single-transaction  # 使用事务保证一致性（InnoDB）
--lock-tables         # 锁表（MyISAM）
--skip-lock-tables    # 不锁表
--add-locks           # 在INSERT前后加LOCK/UNLOCK

 

如

// 1. 备份单个数据库（最常用）
mysqldump -u root -p mydb > mydb_backup.sql         // 系统会提示输入密码。包含建表语句 + 所有数据。

// 2. 备份多个数据库
mysqldump -u root -p --databases db1 db2 > multi_db_backup.sql      // 必须加 --databases，否则第二个参数会被当作表名！

//  3. 备份所有数据库（全量备份）
mysqldump -u root -p --all-databases > full_backup.sql
// 或简写：
mysqldump -u root -p -A > full_backup.sql

// 4. 只备份表结构（不含数据）
mysqldump -u root -p --no-data mydb > schema_only.sql  // 适用于生成建表脚本。

// 5. 只备份数据（不含建表语句）
mysqldump -u root -p --no-create-info mydb > data_only.sql

// 6. 备份指定表
mysqldump -u root -p mydb table1 table2 > tables_backup.sql

// 7. 压缩备份（节省空间）
mysqldump -u root -p mydb | gzip > mydb_backup.sql.gz
# 解压查看
gunzip < backup.sql.gz | less

// 8. 带条件备份数据
mysqldump -u root -p --where="order_date >= '2023-01-01'" 数据库名 orders > orders_2023.sql       // 备份2023年的订单

// 9. 备份到远程服务器
mysqldump -u root -p 数据库名 | ssh user@远程主机 "cat > /path/to/backup.sql"    // 备份并直接传输到远程
//或者使用管道
mysqldump -u root -p 数据库名 > backup.sql
scp backup.sql user@远程主机:/path/

// 10. 定时备份（crontab）
0 3 * * * /usr/bin/mysqldump -u root -p密码 数据库名 > /backup/db_$(date +\%Y\%m\%d).sql         // 每天凌晨3点备份
0 3 * * * /usr/bin/mysqldump -u root -p密码 数据库名 > /backup/db_$(date +\%Y\%m\%d).sql && find /backup/ -name "*.sql" -mtime +7 -delete    // 保留最近7天

恢复数据库

// 1. 恢复整个数据库
mysql -u root -p 数据库名 < backup.sql

// 2. 恢复所有数据库
mysql -u root -p < all_databases.sql

// 3. 恢复特定表
# 先进入数据库
mysql -u root -p 数据库名
# 然后导入表数据
mysql> source users.sql

// 4. 边备份边恢复（迁移数据）
# 从源服务器备份，直接恢复到目标服务器
mysqldump -h 源主机 -u 用户 -p 数据库名 | mysql -h 目标主机 -u 用户 -p 数据库名