MySQL日常运维中，数据备份是保障业务稳定的核心操作，mysqldump作为MySQL官方自带逻辑备份工具，无需额外安装，兼容性覆盖MySQL5.6/5.7/8.0全系列版本，支持单数据库、多指定数据库、服务器全部数据库三种主流备份场景，同时附带备份压缩、定时脚本、数据还原、常见报错处理实操命令，全文命令可直接复制在Linux服务器执行，适配线上生产环境。

一、前置基础说明

1. 工具调用前提

1. Linux系统默认随MySQL客户端一同安装，可执行which mysqldump校验工具是否存在；
2. Windows环境需进入MySQL安装目录bin文件夹执行命令；
3. 执行备份需拥有对应权限：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-transaction	InnoDB引擎热备，不锁表，线上业务必加参数
–master-data=2	记录二进制日志位点，用于主从同步、数据增量恢复
–routines	同步备份存储过程、函数
–triggers	同步备份触发器
–events	同步备份定时事件

生产环境InnoDB引擎备份固定搭配参数：--single-transaction --routines --triggers --events --master-data=2

二、单数据库备份（最常用场景）

场景1：基础单库备份（无压缩）

语法：

mysqldump -u用户名 -p --single-transaction --routines --triggers --events --databases 数据库名 > 备份文件.sql

实操示例：备份名为shop的电商库

mysqldump -uroot -p --single-transaction --routines --triggers --events --databases shop > shop_20260712.sql

执行后输入数据库密码，当前目录生成shop_20260712.sql完整备份文件，文件自带建库语句，还原时直接导入即可。

场景2：单库备份并压缩（节省磁盘空间）

线上服务器数据量大，备份文件建议直接通过gzip压缩，分两种写法：

1. 备份同时压缩

mysqldump -uroot -p --single-transaction --routines --triggers --events --databases shop | gzip > shop_20260712.sql.gz

2. 先备份后压缩

mysqldump -uroot -p --single-transaction --routines --triggers --events --databases shop > shop_20260712.sql
gzip shop_20260712.sql

场景3：仅备份单库指定数据表

无需--databases参数，库名后空格拼接表名

# 只备份shop库user、order两张表
mysqldump -uroot -p --single-transaction shop user order > shop_user_order.sql

场景4：远程服务器单库备份

数据库不在本地，增加-h指定IP、-P指定端口

mysqldump -uroot -p -h192.168.1.100 -P3306 --single-transaction --databases shop > shop_remote.sql

三、多数据库同时备份

适用于业务多库部署、需要一次性导出多个业务库，分两种写法。

写法1：使用–databases 空格分隔多个库名（推荐）

自动生成各库创建语句，还原无需手动创建数据库

# 同时备份shop、user、log三个数据库
mysqldump -uroot -p --single-transaction --routines --triggers --events --databases shop user log > multi_db_20260712.sql

压缩版本：

mysqldump -uroot -p --single-transaction --routines --triggers --events --databases shop user log | gzip > multi_db_20260712.sql.gz

写法2：不使用–databases（不推荐）

仅导出表数据，无建库语句，还原前必须手动创建对应数据库

mysqldump -uroot -p --single-transaction shop user log > multi_db_simple.sql

四、全实例所有数据库备份

备份当前MySQL实例全部数据库，包含mysql系统库、information_schema、performance_schema，迁移服务器、整机数据搬迁使用。

1. 基础全库备份命令

mysqldump -uroot -p --single-transaction --routines --triggers --events --all-databases > all_db_20260712.sql

2. 压缩全库备份（生产标准写法）

mysqldump -uroot -p --single-transaction --routines --triggers --events --all-databases | gzip > all_db_20260712.sql.gz

注意事项

1. 全库备份包含mysql系统权限库，还原后账号密码会同步覆盖，跨服务器迁移慎用；
2. 若仅需要业务库，不要使用该命令，改用多库备份单独指定业务数据库。

五、备份文件数据还原操作

1. 普通sql文件还原

# 方式1 mysql命令导入
mysql -uroot -p < shop_20260712.sql

# 方式2 登录MySQL内部source导入
mysql -uroot -p
source /data/backup/shop_20260712.sql;

2. gzip压缩包无需解压直接还原

gzip -d -c shop_20260712.sql.gz | mysql -uroot -p

六、生产环境定时自动备份脚本

创建定时备份文件mysql_backup.sh，搭配crontab实现每日自动备份、自动清理7天前过期备份，适配搜索引擎收录运维脚本需求。

1. 脚本完整内容

#!/bin/bash
# 数据库配置
DB_USER="root"
DB_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 --databases shop | 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" -mtime +7 -delete

2. 脚本授权与定时任务

# 添加执行权限
chmod +x mysql_backup.sh
# 每日凌晨2点执行备份
crontab -e
# 添加定时规则
0 2 * * * /bin/bash /data/mysql_backup.sh

安全提示：脚本明文填写密码仅内网服务器使用，公网机器建议去掉脚本密码，采用交互式输入。

七、常见线上问题与优化方案

1. MyISAM引擎备份锁表问题
   MyISAM不支持事务，–single-transaction参数失效，备份会全局锁表，低峰期执行备份或切换InnoDB引擎。
2. 备份文件过大磁盘不足
   搭配gzip压缩，定时清理过期备份，或挂载独立磁盘存放备份文件。
3. MySQL8.0执行mysqldump报认证插件错误
   登录MySQL执行修改账号认证规则：ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '密码';
4. 备份缺失存储过程、触发器
   命令必须携带--routines --triggers --events三个参数，缺少会丢失函数、定时任务。
5. 还原时报不存在数据库
   备份命令添加--databases参数导出，无该参数需手动创建数据库再导入。