了解如何在现代版本的MySQL中最好地使用mysqldump。
本文翻译自 #Mysqldump with Modern MySQL
译者：耐心的农夫2020
时间：2020-02-16
mysqldump这个工具有很多选项，我算了一下，大概有111个？
我们大部分人喜欢保持简单，下面是我通常导出单个数据库的方式：
mysqldump some_database > some_database.sql
# Or with user auth
mysqldump -u some_user -p some_database > some_database.sql
# Or with gzip compression
mysqldump some_database | gzip > some_database.sql.gz
# Or with the "pv" tool, which let's us know how much data is
# flowing between our pipes - useful for knowing if the msyqldump
# has stalled
mysqldump some_database | pv | gzip > some_database.sql.gz
# 102kB 0:01:23 [1.38MB/s] [ <=>
但是，稍微深入地研究一下这个命令以理解正在发生的情况是值得的。如果你对生产环境的数据库使用mysqldump命令，它在运行过程中可能会对用户带来实际问题。
默认值
让我们先来看一下mysqldump的默认值。mysqldump默认使用--opt选项，除非我们显示指定其不使用这个选项。--opt选项是下面这些标志位的别名：
- --add-drop-table - 在每一条CREATE TABLE语句前添加DROP TABLE语句，这样可以让你幂等地多次重复使用产生的.sql文件。
- --add-locks - 这个标志位在你导入dump文件的时候起作用，而不是在运行mysqldump命令的时候起作用。它会在每一个转储表（原味是table dump，个人理解应该包含建表和插入数据）前和表后添加LOCK TABLES和UNLOCK TABLES语句。当dump文件被加载的时候，这样做会让插入速度变得更快。这也意味着，当你导入数据的时候，每张表在创建的过程中都会加锁，以组织读取和写入。
- --create-options - 包含CREATE TABLE语句所有MySQL特定的表选项。在测试这个选项的时候(可以使用-create-options=false关闭这个选项)，我发现主要/最明显的差异是当把这个选项设置为false时，主键上的AUTO_INCREMENT会缺失。
-
--disable-keys - 这个选项只对MyISAM表的非unique索引起作用。这个选项可以让加载MyISAM表的dump文件更快，因为索引是在所有行都被插入之后才会创建。
--extended-insert - 这个选项可以让插入语句使用多行语法（即一个Insert语句包含多个values列表）。对于有些列（通常是blobs）很大的表，不要使用这个选项，这会造成很多查询超过client和server之间max_allowed_packet的配置项。但是通常来说，最好是总是使用这个选项。每个插入使用单个查询会大大降低导入速度。 -
--lock-tables - 和
--add-locks不同，这个选项是在运行mysqldump的时候起作用。这个选项会在mysqldump运行期间对所有的表上锁，最好不要在实时运行的环境中使用这个选项。这个选项的主要目的是在dumping MyISAM表的时候保护数据的完整性。由于InnoDB是目前大多数表的默认存储引擎，这个选项通常应该使用--skip-lock-tables关闭以阻止锁表的行为，并且应该使用--single-transaction选项确保在一个事务中运行mysqldump，我会在下面更详细的阐述这个选项。 - --quick - 这个选项可以让mysqldump在读取大表的时候不会把整张表都放到内存，从而不占用过多的内存。
- --set-charset - 在输出中添加SET NAMES default_character_set。这个操作不会做任何字符集的转换（mysqldump没有任何选项支持这样的操作）。相反，这个选项只是表名你想要添加字符集信息，所以当重新导入dump文件的时候，字符集信息会被设置。
除了--lock-tables这个选项之外，其他默认值都很好。--lock-tables这个选项会让mysqldump运行期间让数据库不可用，但也不一定非得这样做。
我们可以更聪明地使用mysqldump。
mysqldump和锁表
在使用mysqldump时需要在数据库性能和数据完整性之间做出权衡。你的策略在很大程度上取决于你数据库表所使用的存储引擎。
由于每张表都有一个独立的存储引擎，这将变得很有趣 :D
默认情况下，mysqldump会锁住所有它要dump的表，这确保了数据在dump期间保持一致性状态。
数据一致性
“一致性状态”指的是数据处于预期的状态。更具体来说，所有的关系都应该是匹配的。假设mysqldump从20张表中导出了前5张表，如果mysqldump在导出第1张表之后并且在导出第20张表之前，第1张表和第20张表通过主键/外键的关联插入了新的行，那么我们就处于不一致的状态了。表20有数据和表1的一行数据关联，但是表1的这一行数据并没有导出到dump文件。
由于MyISAM表不支持事务，所以这种类型的表需要加锁机制。但是，InnoDB（从MySQL 5.5.5起是默认存储引擎）支持事务。尽管mysqldump采用了对所有表加锁的保守设置作为默认配置，但是我们不需要这个默认值 - 避免对所有表完全加锁。
mysqldump与事务
作为一个经验法则，除非出于特定原因使用MyISAM，否则应在所有表上使用InnoDB存储引擎。如果您多年来（可以回溯到MyISAM是默认存储引擎的时候）一直在将数据库移植到各种MySQL服务器，一定要检查一下确保你的表使用了InnoDB引擎。
假设你导出的是InnoDB存储引擎的表，你的mysqldump可以这样写：
mysqldump --single-transaction --skip-lock-tables some_database > some_database.sql
--single-transaction标志会在mysqldump运行前开启一个事务。这个标志位不会让mysqldump锁定整个数据库，而是在事务处理时读取数据库的当前状态，从而实现一致性的数据转储。
single-transaction选项使用默认的事务隔离模式：REPEATABLE READ。
注意如果你导出的表是混合类型的（既有MyISAM类型又有InnoDB类型），使用single-transaction选项可能会让你的MyISAM表（或者内存表）处于不一致性状态，因为这个选项不会对 MyISAM 类型的表的读写操作加锁。
在上面这种情况下，我建议两种类型的表分开dump。
但是如果分开dump还是会造成不一致的状态（如果MyISAM表和InnoDB表之间有主键和外键关联），那么使用--lock-tables选项就成了确保数据库处于一致性状态的唯一选择了。在这种情况下，当你dump实时数据库的时候就一定要非常小心。
或许使用副本数据库而不是主数据库dump数据或者调研一下Xtrabackup（这个工具会拷贝mysql的数据目录，不会造成宕机）这样其他的选择也是可行的。
数据复制
如果使用复制的话，那副本服务器上已经有一个备份。这很棒！但是，异地备份仍然是一件好事。在这样的设置中，我尝试在副本服务器而不是主服务器上运行mysqldump。
就mysqldump而言，这几乎没有什么影响：
-
在副本服务器上运行mysqldump意味着副本服务器上接收到的数据稍微落后于主服务器。
- 对于定期备份而言，这很不错。如果你需要某一时间点的数据，那么你需要等到数据到达副本服务器才行。
因为在理论上，这已经是一个内在的假设：即无论如何副本服务器都会落后。为mysqldump添加一点“张力”没什么大不了的。（译者注：这句话没太理解）。因此我认为在副本服务器上运行mysqldump是最好的。
当使用数据复制时或者当启用binlog时，无论是通过主服务器dump数据还是通过副本服务器dump数据，都有相应的标志支持。
dump主服务器上的数据
--master-data 标志可以向转储文件中添加特定的输出，让转储文件可以用来配置主服务器的副本服务器。副本服务器需要主服务器上的数据，以便知道从哪里才开始同步。
--master-data选项会自动关闭--lock-tables选项，因为转储文件中包含的binlog位置会告诉副本服务器从哪里开始同步，这样做的好处是即使转储文件处于不一致的状态，你也不会丢失任何查询。（同样，如果您有MyISAM表，那只是一个考虑因素）。
如果你也使用--single-transaction选项，dump开始时尽在很多时间内会获取一个读取锁。
当从主服务器dump数据时可以使用这个选项。
dump副本服务器上的数据
--dump-slave选项与--master-data很相似，除了以下区别：
- 这个选项适用于从副本服务器dump数据
- 它会包含与副本服务器一样的主服务器信息， 而
-master-data会把自己设置为主服务器。
当从副本服务器dump数据时使用这个选项。
摘录自文档：如果dump文件要加载的服务器使用了 gtid_mode=ON和MASTER_AUTOPOSITION=1，那么就不应该使用这个选项。
从MySQL 5.6起，GTID是一种数据拷贝的新方式，这种方式更好，因此理论上可以不考虑--dump-slave了。
Dump多个数据库
我通常会dump特定的数据库，这使我在需要时可以轻松地恢复特定的数据库。
但是，你也可以同时dump多个数据库：
mysqldump --single-transaction --skip-lock-tables --databases db1 db2 db3 \
> db1_db2_and_db3.sql
你也可以从一个数据库中dump特定的表：
mysqldump --single-transaction --skip-lock-tables some_database table_one table_two table_three \
> some_database_only_three_tables.sql
你也可以dump整个数据库。注意，这个也包含内部的mysql数据库。
mysqldump --single-transaction --skip-lock-tables --flush-privileges --all-databases > entire_database_server.sql
上面的命令使用了--all-databases 选项和--flush-privileges选项。
由于我们要获取内部的mysql数据库，这个数据库中包含了mysql的用户和权限，--flush-privileges选项会在dump结尾添加FLUSH PRIVILEGES查询，这是需要的因为dump文件在导入的时候会改变用户和权限。
