mysqldump完整使用教程：单库、多库、全库数据备份与还原

MySQL日常运维中数据备份是保障业务稳定的核心操作mysqldump作为MySQL官方自带逻辑备份工具无需额外安装兼容性覆盖MySQL5.6/5.7/8.0全系列版本支持单数据库、多指定数据库、服务器全部数据库三种主流备份场景同时附带备份压缩、定时脚本、数据还原、常见报错处理实操命令全文命令可直接复制在Linux服务器执行适配线上生产环境。一、前置基础说明1. 工具调用前提Linux系统默认随MySQL客户端一同安装可执行which mysqldump校验工具是否存在Windows环境需进入MySQL安装目录bin文件夹执行命令执行备份需拥有对应权限SELECT、LOCK TABLES、EVENT、TRIGGER全库备份额外需要RELOAD权限。2. 基础通用参数解释参数作用说明-u指定MySQL登录用户名-p交互式输入数据库密码安全推荐明文密码存在泄露风险-P指定数据库端口非3306端口必须添加例-P3307-h指定数据库主机远程备份填写IP本地127.0.0.1可省略–databases备份数据库时自动生成CREATE DATABASE、USE语句还原无需手动建库–all-databases备份实例下所有数据库包含mysql系统库–single-transactionInnoDB引擎热备不锁表线上业务必加参数–master-data2记录二进制日志位点用于主从同步、数据增量恢复–routines同步备份存储过程、函数–triggers同步备份触发器–events同步备份定时事件生产环境InnoDB引擎备份固定搭配参数--single-transaction --routines --triggers --events --master-data2二、单数据库备份最常用场景场景1基础单库备份无压缩语法mysqldump -u用户名-p--single-transaction--routines--triggers--events--databases数据库名备份文件.sql实操示例备份名为shop的电商库mysqldump-uroot-p--single-transaction--routines--triggers--events--databasesshopshop_20260712.sql执行后输入数据库密码当前目录生成shop_20260712.sql完整备份文件文件自带建库语句还原时直接导入即可。场景2单库备份并压缩节省磁盘空间线上服务器数据量大备份文件建议直接通过gzip压缩分两种写法备份同时压缩mysqldump-uroot-p--single-transaction--routines--triggers--events--databasesshop|gzipshop_20260712.sql.gz先备份后压缩mysqldump-uroot-p--single-transaction--routines--triggers--events--databasesshopshop_20260712.sqlgzipshop_20260712.sql场景3仅备份单库指定数据表无需--databases参数库名后空格拼接表名# 只备份shop库user、order两张表mysqldump-uroot-p--single-transaction shop user ordershop_user_order.sql场景4远程服务器单库备份数据库不在本地增加-h指定IP、-P指定端口mysqldump-uroot-p-h192.168.1.100-P3306--single-transaction--databasesshopshop_remote.sql三、多数据库同时备份适用于业务多库部署、需要一次性导出多个业务库分两种写法。写法1使用–databases 空格分隔多个库名推荐自动生成各库创建语句还原无需手动创建数据库# 同时备份shop、user、log三个数据库mysqldump-uroot-p--single-transaction--routines--triggers--events--databasesshop user logmulti_db_20260712.sql压缩版本mysqldump-uroot-p--single-transaction--routines--triggers--events--databasesshop user log|gzipmulti_db_20260712.sql.gz写法2不使用–databases不推荐仅导出表数据无建库语句还原前必须手动创建对应数据库mysqldump-uroot-p--single-transaction shop user logmulti_db_simple.sql四、全实例所有数据库备份备份当前MySQL实例全部数据库包含mysql系统库、information_schema、performance_schema迁移服务器、整机数据搬迁使用。1. 基础全库备份命令mysqldump-uroot-p--single-transaction--routines--triggers--events--all-databasesall_db_20260712.sql2. 压缩全库备份生产标准写法mysqldump-uroot-p--single-transaction--routines--triggers--events--all-databases|gzipall_db_20260712.sql.gz注意事项全库备份包含mysql系统权限库还原后账号密码会同步覆盖跨服务器迁移慎用若仅需要业务库不要使用该命令改用多库备份单独指定业务数据库。五、备份文件数据还原操作1. 普通sql文件还原# 方式1 mysql命令导入mysql-uroot-pshop_20260712.sql# 方式2 登录MySQL内部source导入mysql-uroot-psource/data/backup/shop_20260712.sql;2. gzip压缩包无需解压直接还原gzip-d-cshop_20260712.sql.gz|mysql-uroot-p六、生产环境定时自动备份脚本创建定时备份文件mysql_backup.sh搭配crontab实现每日自动备份、自动清理7天前过期备份适配搜索引擎收录运维脚本需求。1. 脚本完整内容#!/bin/bash# 数据库配置DB_USERrootDB_PASS数据库密码BACKUP_DIR/data/mysql_backup# 备份日期命名DATE$(date%Y%m%d)# 创建备份目录mkdir-p${BACKUP_DIR}# 1.单库定时备份 shop库mysqldump -u${DB_USER}-p${DB_PASS}--single-transaction--routines--triggers--events--databasesshop|gzip${BACKUP_DIR}/shop_${DATE}.sql.gz# 2.多库定时备份 shop user log# mysqldump -u${DB_USER} -p${DB_PASS} --single-transaction --routines --triggers --events --databases shop user log | gzip ${BACKUP_DIR}/multi_${DATE}.sql.gz# 3.全库定时备份# mysqldump -u${DB_USER} -p${DB_PASS} --single-transaction --routines --triggers --events --all-databases | gzip ${BACKUP_DIR}/all_db_${DATE}.sql.gz# 删除7天之前备份文件find${BACKUP_DIR}-name*.sql.gz-mtime7-delete2. 脚本授权与定时任务# 添加执行权限chmodx mysql_backup.sh# 每日凌晨2点执行备份crontab-e# 添加定时规则02* * * /bin/bash /data/mysql_backup.sh安全提示脚本明文填写密码仅内网服务器使用公网机器建议去掉脚本密码采用交互式输入。七、常见线上问题与优化方案MyISAM引擎备份锁表问题MyISAM不支持事务–single-transaction参数失效备份会全局锁表低峰期执行备份或切换InnoDB引擎。备份文件过大磁盘不足搭配gzip压缩定时清理过期备份或挂载独立磁盘存放备份文件。MySQL8.0执行mysqldump报认证插件错误登录MySQL执行修改账号认证规则ALTER USER rootlocalhost IDENTIFIED WITH mysql_native_password BY 密码;备份缺失存储过程、触发器命令必须携带--routines --triggers --events三个参数缺少会丢失函数、定时任务。还原时报不存在数据库备份命令添加--databases参数导出无该参数需手动创建数据库再导入。