mysqldump备份

前言：

本质与特点

• 本质：导出SQL语句文件。
• 优点：兼容所有存储引擎，可备份为SQL语句。
• 缺点：备份速度较慢，导入可能存在格式兼容问题，无法直接增量备份。
• 备份级别：支持表级、库级和全库级备份。

常用参数

--flush-logs, -F                  # 开始备份前刷新日志  
--flush-privileges               # 备份包含mysql数据库时刷新授权表  
--lock-all-tables, -x            # MyISAM一致性（锁所有库表，影响服务可用性）  
--lock-tables, -l                # 锁指定备份的库表  
--single-transaction             # InnoDB引擎专用，保证一致性（不影响服务）  
--master-data=2                  # 写入二进制日志位置和文件名到备份文件并注释  
--master-data=1                  # 写入日志位置和文件名到备份文件（不注释）  

–master-data参数说明

1. 恢复时默认执行，需开启二进制日志且具备RELOAD权限。
2. 自动启用–lock-all-tables，关闭–lock-tables。

表级别备份

语法

mysqldump [参数] 数据库名 表名 -u用户名 -p密码 > 备份文件路径  

示例

单表备份与恢复

# 备份表
[root@WEB ~]# mysqldump php message -uroot -p > /tmp/message.sql  

# 删除表
mysql> show tables;
+---------------+
| Tables_in_php |
+---------------+
| message       |
| test          |
+---------------+
mysql> drop table message;
Query OK, 0 rows affected (0.03 sec)

# 恢复表
mysql> source /tmp/message.sql;

多表备份与恢复

# 备份多表（关闭GTID记录）
[root@WEB ~]# mysqldump php message test -uroot -p --set-gtid-purged=OFF > /tmp/all.sql  

# 删除表
mysql> drop table message;
Query OK, 0 rows affected (0.02 sec)

# 命令行恢复
[root@WEB ~]# mysql php < /tmp/all.sql -p

库级别备份

语法

mysqldump --databases [参数] 库名1 库名2 -u用户名 -p密码 > 备份文件路径  

示例

备份与恢复数据库

# 备份多个库
[root@WEB ~]# mysqldump --databases php zabbix --set-gtid-purged=OFF > /tmp/data.sql -uroot -p  

# 删除数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| php                |
| sys                |
| testldx            |
| z_blog             |
| zabbix             |
+--------------------+
mysql> drop database zabbix;
Query OK, 166 rows affected (2.93 sec)

# 方式1：MySQL内恢复
mysql> source /tmp/data.sql;

# 方式2：命令行恢复
[root@WEB ~]# mysql < /tmp/data.sql -p

全库级别备份

语法

mysqldump --all-databases [参数] > 备份文件路径  

示例

# 全库备份（关闭GTID记录，InnoDB一致性）
[root@WEB ~]# mysqldump --all-databases --single-transaction --set-gtid-purged=OFF > /root/all.sql -uroot -p  

总结

1. 备份特性：mysqldump备份SQL语句，无需停止数据库服务。
2. 恢复前提：数据库需处于运行状态才能导入备份文件。
3. 备份限制：仅支持全量备份（全库/库/表），无法直接增量备份。
4. 适用场景：中小型数据库场景。