mysqldump 使用总结

目录

一、介绍

二、需要的权限

三、工具使用限制

四、常用方法举例

4.1 全库备份

4.2 指定schema备份

4.3  指定表备份

4.4 导出文本数据（-T）

4.5 根据条件备份

五、数据导入

六、小技巧

6.1 场景1

6.2 场景2

七、常用参数 

---

一、介绍

mysqldump是mysql自带的逻辑备份工具。

• 免费备份工具
• 支持远程备份
• 支持生成CSV格式或XML格式的文件
• 可以使用文本工具直接处理对应的备份数据，以更灵活便利的进行恢复工作
• 与存储引擎无关，可以在多种存储引擎下进行备份恢复，对innodb引擎支持热备，对MyISAM引擎支持温备（施加表锁）
• 当数据为浮点类型时，会出现精度丢失
• 备份的过程是串行化的，不支持并行备份

二、需要的权限

grant select,show view,trigger,lock tables on database.table_name to user_name;

三、工具使用限制

1. mysqldump转储时默认不会备份INFORMATION_SCHEMA, performance_schema, sys，如有需求转储，需要再命令行上显式的指定他们（没有特殊需求，一般也不要转储这些库）。
2. mysqldump不会转储InnoDB CREATE TABLESPACE语句。
3. mysqldump不会备份 NDB cluster ndbinfo信息数据库。
4. 在启用了GTID的数据库中使用mysqldump备份时需要注意，如果备份文件中包含了GTID信息，则无法恢复到没有启用GTID的数据库中。
5. Windows通过PowerShell使用如下命令进行转储时，转储文件默认使用UTF-16编码，而MySQL不允许将UTF-16作为连接字符集，所以通过如下命令备份的转储文件将无法正确加载到数据库中
   mysqldump [options] > dump.sql
6. mysqldump是单线程，当数据量大时备份时间长，甚至有可能在备份过程中非事务表长期锁表对业务造成影响（SQL形式的备份数据恢复时间也较长）。
7. 慎用 --compact 参数，此参数会去掉文件头与文件尾的一些参数设置（比如时区，字符集...），导致隐患。

四、常用方法举例

4.1 全库备份

mysqldump -u -p -h127.0.0.1 -P3306 --default-character-set=utf8mb4 --single-transaction --master-data=2 --flush-logs --hex-blob --flush-privileges --triggers --routines --events --all-databases > /backup/all_db_with_data.sql

4.2 指定schema备份

## 指定1个或多个schema进行备份，多个schema之间用空格分开
## 备份表结构和数据
mysqldump -u -p -h127.0.0.1 -P3306 --default-character-set=utf8mb4 --single-transaction --master-data=2 --flush-logs --set-gtid-purged=off --hex-blob --databases employees departments > /backup/dbs_all.sql
 
## 只备份表结构
mysqldump -u -p -h127.0.0.1 -P4444 --default-character-set=utf8mb4 --single-transaction --master-data=2 --flush-logs --set-gtid-purged=off --hex-blob --no-data --databases employees departments > /backup/dbs_schema.sql
  
## 只备份数据
mysqldump -u -p -h127.0.0.1 -P4444 --default-character-set=utf8mb4 --single-transaction --master-data=2 --flush-logs --set-gtid-purged=off --hex-blob --no-create-info --databases employees departments > /backup/dbs_only_data.sql

4.3  指定表备份

#########################指定备份的表#########################
## 只备份employees数据库中的salaries表和users表
## 未指定--databases选项，则--tables参数为 schema tab tab ……，使用空格分隔
mysqldump -u -p -h127.0.0.1 -P3306 --default-character-set=utf8mb4 --single-transaction --master-data=2 --flush-logs --set-gtid-purged=off --hex-blob --tables employees salaries users > /backup/employees_salaries.sql

## 也可以将schema单独指定，二者等价
mysqldump -u -p -h127.0.0.1 -P3306 --default-character-set=utf8mb4 --single-transaction --master-data=2 --flush-logs --set-gtid-purged=off --hex-blob --databases employees --tables salaries users > /backup/tbs.sql
  
#########################排除指定数据库的指定表#########################
## 排除指定数据库的指定表
## --ignore-table必须为<schema>.<table name>，需要排除多个表，则写多个 --ignore-table
## 可以利用--databases 指定要备份的多个数据库，也可以直接指定-A（全备）
mysqldump -u -p -h127.0.0.1 -P3306 --default-character-set=utf8mb4 --single-transaction --master-data=2 --flush-logs --set-gtid-purged=off --hex-blob --ignore-table=employees.departments --ignore-table=employees.employees --databases employees > /backup/exclude_tbs.sql

4.4 导出文本数据（-T）

语法格式：

mysqldump -u root -pPassword -T 目标目录 dbname [tables] [option];

其中：

• -T, --tab=name：导出.sql（建表语句）和.txt（数据）文件。
• 目标目录：是指导出的文本文件的路径。
• dbname参数表示数据库的名称。
• tables参数表示要导出的表的名称。如果不指定，则导出数据库dbname中所有的表，导出多张表，则表之间使用空格分开。

option为可选参数选项，这些选项需要结合-T选项使用，常见的参数如下：

• --fields-terminated-by：设置分隔符，可以为单个或多个字符。默认值为制表符\t。
• --fields-enclosed-by：设置字符来括住字段的值。
• --fields-optionally-enclosed-by：设置字符括住CHAR、VARCHAR和TEXT等字符型字段，只能为单个字符。
• --fields-escaped-by：设置转义字符，只能为单个字符。默认值为“\”。
• --lines-terminated-by：设置换行符，可为单个或多个字符。默认值为\n。

其实是调用 SELECT ...INTO OUTFILE语句，所以必须在MySQL Server本地执行，并且执行备份的MySQL账户还必须具有FILE权限，即要设置secure_file_priv参数：

• secure_file_prive=null   -- 限制mysqld 不允许导入导出
• secure_file_priv=/tmp/   -- 限制mysqld的导入导出只能发生在/tmp/目录下
• secure_file_priv=' '         -- 不对mysqld 的导入 导出做限制

## 导出schema employees下的users和salaries表
## 导出完成后会在/data/mysql_3306/backup目录下生成users.sql、users.txt、salaries.sql、salaries.txt四个文件

mysqldump -u -p -h127.0.0.1 -P3306 --default-character-set=utf8mb4 --single-transaction -T /data/mysql_3306/backup --databases employees --tables users salaries --fields-terminated-by=, --fields-optionally-enclosed-by=\" --lines-terminated-by=\\r\\n

4.5 根据条件备份

###按照where条件，备份指定库下的指定表的数据
mysqldump -u -p -h127.0.0.1 -P3306 --default-character-set=utf8mb4 --single-transaction --where 'id > 10 and id < 20' --databases testdb --tables sbtest1 > /backup/sbtest1.sql
  
###按照where条件，备份指定库下的所有表的数据
mysqldump -u -p -h127.0.0.1 -P3306 --default-character-set=utf8mb4 --single-transaction --where 'id > 10 and id < 20' --databases testdb > /backup/sbtest1.sql

如果同时备份多张表，则如果有表不包含where条件指定的列，mysqldump会报错。

五、数据导入

如果导出的是mysqldump等工具导出的可执行sql文件，可直接执行脚本进行导入

mysql -u -p < /backup/bak_script.sql

mysql> source /backup/bak_script.sql

如果数据量较大推荐第一种方式，减少了反馈输出，速度相对快一些。

六、小技巧

6.1 场景1

为防止SQL导入中产生过大的binlog，使主从延迟增加，在导入时把日志格式改为statement，减小binlog

涉及参数：

transaction_isolation

binlog_format

注意问题：

如果表是InnoDB表，并且事务隔离级别是READ COMMITTED或READ UNCOMMITTED，那么只能使用基于行的日志记录。可以将日志格式改为statement，但在运行时这样做会导致错误，因为InnoDB表不能再执行insert语句。

mysql> insert into test values(3,'bing');
ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

6.2 场景2

备份或导入SQL包过大，超过max_allowed_packet，备份或导入失败

涉及参数：

max_allowed_packet

注意问题：

备份或导入时确定数据库表中的字符类型：如果有BLOB列或长字符串等字符类型建议合理增加这个值。

#  mysqldump -u -P3306 -p --default-character-set=utf8mb4 --master-data=2 --flush-logs --single-transaction --hex-blob --databases db_test > db_test_all.sql
mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `operlog` at row: 264227

导入时也相似的原理。

可以在mysql、mysqldump等client命令后临时修改此参数：

mysqldump [xxxx] --max_allowed_packet=256M > dump.sql
mysql [xxxx] --max_allowed_packet=256M < dump.sql

七、常用参数 

--flush-logs：开始备份时，切换binlog日志

--master-data：默认1，输出包含CHANGE MASTER TO语句，标记dump源的二进制日志坐标（文件名和位置），设置为2时，输出注释的CHANGE MASTER TO语句

--single-transaction：使用可重复读(REPEATABLE READ)事务隔离级别来保证整个dump过程中数据一致性（开始备份前begin开启事务，保证数据一致性），该选项仅对InnoDB表有用，且不能与ALTER TABLE/CREATE TABLE/DROP TABLE/RENAME TABLE/TRUNCATE TABLE等DDL操作并行。

--hex-blob：以16进制导出blob字段数据，防止乱码。

--set-gtid-purged：默认AUTO，即数据库开启GTID时在备份文件中加入SET @@GLOBAL.GTID_PURGED语句，未开启时啥也不做。如果设置为ON，但数据库没有开启GTID会报错，非全备时，会提醒要设置为OFF。