mysqldump 使用规范

数据库很重要，没有备份，数据丢失只能跑路。所以还是做好备份吧！

一、工具介绍

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

它的备份原理是，通过协议连接到mysql数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的insert语句，当我们需要还原这些数据时，只要执行这些insert语句，即可将对应的数据还原。

二、工具特点

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

三、备份权限

备份对象	权限
table	SELECT
view	SHOW VIEW
trigger	TRIGGER
event	EVENT
储存过程、函数	SELECT mysql.proc
转储用户	SELECT "mysql"系统库

备份未使用–single-transaction选项时，还需要LOCK TABLES权限

四、工具使用限制

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 参数，此参数会去掉文件头与文件尾的一些参数设置（比如时区，字符集…），导致隐患。

8. 参数 --lock-all-tables，–flush-privileges分别会在备份时进行 flush tables 和 flush privileges 操作，会产生GTID，备份从库时请注意。

9. 在对数据库进行完全备份前，需要收集数据库相关信息（存储引擎、字符集等），确保备份内容完整，以下为收集语句：

   -- 查看表相关信息
   select table_schema,table_name,table_collation,engine,table_rows
   from information_schema.tables
   where table_schema NOT IN ('information_schema' , 'sys', 'mysql', 'performance_schema');
     
   -- 查看是否存在存储过程、触发器、调度事件等
   select count(*) from information_schema.events;
   select count(*) from information_schema.routines;
   select count(*) from information_schema.triggers;
      
   -- 查看字符集信息
   show variables like 'character%';
   

10. mysqldump的一些选项跟mysqlpump有些变化，在使用中需注意：

mysqldump	mysqlpump
–routines、–events的别名分别为-R、-E	–routines、–events不存在别名
存在master-data选项	不存在master-data选项，在进行构建主从需要通过master_auto_position来控制，不能够直观的通过指定binlog以及position来构建主从
-d的别名是–no-data	-d的别名是–skip-dump-rows
转储文件默认带DROP TABLE语句	转储文件默认不带DROP TABLE、DROP USER（在使用–users备份用户时）语句，导入时可能会因为用户存在或者表存在而报错
备份不指定数据库或者-A会提示报错	备份不指定数据库或者-A，默认备份所有的数据。ps：除了INFORMATION_SCHEMA, performance_schema, ndbinfo, or sys

五、已知BUG

MySQL5.7.7至今（2020/11/02）的所有5.7的小版本，在使用mysqldump在执行–all-databases都会清空mysql.proc导致sys无法正常使用。

mysql> SELECT * FROM sys.processlist;
ERROR 1356 (HY000): View 'sys.processlist' references invalid table(s) or column(s) or function(s) or definer/invoker o