MySQL 备份还原导入导出脚本详解

MySQL 的数据迁移是日常运维和开发中的常见任务尤其在服务器搬迁、系统升级或数据备份时尤为重要。本文将基于test数据库为例详细讲解导出与导入的完整脚本并补充最佳实践和常见坑位。 一、数据导出导出是数据迁移的第一步。我们建议使用mysqldump工具结合压缩功能这样不仅可以节省磁盘空间还能加快传输速度。️ 基础导出脚本# 使用 mysqldump 导出 test 数据库包含存储过程routines并使用 gzip 压缩 /usr/local/mysql/bin/mysqldump -uroot -p test --routines | gzip test_250102.sql.gz 参数详解参数作用说明-uroot指定用户名替换为你的数据库用户名-p指定密码执行时会提示输入密码确保密码安全test指定数据库本例中为test数据库--routines导出存储过程默认不导出需加上gzip数据压缩 test_250102.sql.gz重定向输出将导出的内容保存为test_250102.sql.gz 二、数据导入导入时有两种方式推荐使用方法1直接解压导入因为它最省事且效率高。 方法1直接 gunzip 导入推荐这种方法不需要先手动解压文件直接将压缩包通过管道传输到 MySQL 中。gunzip /root/test_250102.sql.gz | /usr/local/mysql/bin/mysql -uroot -p test️ 方法2先解压后导入更稳妥如果你担心网络波动导致管道中断可以先解压成.sql文件再单独执行。# 1. 解压 gunzip test_250102.sql.gz # 2. 导入注意这里的 sql 文件名需要与实际解压的文件名一致 mysql -uroot -p test test_250102.sql⚠️ 注意事项•数据库必须已创建导入前请确保目标服务器上已创建好test数据库且排序规则Collation与源数据库一致。•字符集问题如果出现中文乱码尝试在导入命令中添加字符集参数--default-character-setutf8mb4。•大数据量优化导入大库时可考虑先关闭外键检查SET FOREIGN_KEY_CHECKS0; -- 执行导入 SET FOREIGN_KEY_CHECKS1;✅ 三、最佳实践清单为了确保数据迁移的安全性和完整性请参考以下清单•压缩备份始终使用gzip或bzip2压缩导出的文件节省空间。•加密传输不要通过明文方式如 FTP传输.sql文件建议使用scp或sftp。•密码安全不要将密码明文写入脚本最好使用--defaults-extra-file参数读取配置文件。•定时备份使用cron定时任务自动执行上述导出脚本确保数据定期备份。️ 四、自动化脚本示例以下是一个完整的自动化备份脚本示例支持备份指定数据库并自动清理一周前的旧文件#!/bin/bash # # MySQL 备份脚本 v1.0 # 作者数据库小白 # 日期2026-01-23 # # 1. 基础变量 MYSQL_BIN/usr/local/mysql/bin MYSQL_USERroot MYSQL_PASSyour_password # 建议使用安全的方式读取 DB_NAMEtest BACKUP_DIR/root/backup DATE$(date %Y%m%d) # 2. 确保备份目录存在 mkdir -p $BACKUP_DIR # 3. 导出数据库 echo [$DATE] 正在导出数据库 $DB_NAME ... $MYSQL_BIN/mysqldump -u$MYSQL_USER -p$MYSQL_PASS $DB_NAME --routines | gzip $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz # 4. 检查导出是否成功 if [ $? -eq 0 ]; then echo [$DATE] 备份成功${DB_NAME}_${DATE}.sql.gz else echo [$DATE] 备份失败 exit 1 fi # 5. 清理 7 天前的旧备份 find $BACKUP_DIR -type f -name *.sql.gz -mtime 7 -exec rm -f {} \; echo [$DATE] 清理完成 五、常见错误与排查错误现象可能原因解决方案Access denied for user用户名/密码错误或权限不足确认用户名和密码是否正确或在 MySQL 中为该用户授权SELECT权限Got packet bigger than max_allowed_packet数据包过大在my.cnf中增大max_allowed_packet参数或使用--quick选项导入后数据乱码字符集不匹配确认导出和导入时使用相同的字符集如utf8mb4外键约束错误导入顺序问题导入时临时关闭外键检查SET FOREIGN_KEY_CHECKS0;总结通过上述脚本你可以轻松实现 MySQL 数据库的备份与恢复。建议大家定期执行备份脚本并在关键节点如升级、迁移手动执行一次完整备份确保数据安全。