mysql - 使用 mysqldump 转储和加载 MySQL InnoDB 数据库的最快方法是什么？

Question

我想使用 mysqldump 和 MySQL 5.1 创建一个包含大约 40 个 InnoDB 表和大约 1.5GB 数据的数据库副本。

什么是可以最快转储和加载数据的最佳参数(即:--single-transaction)？

同样，当将数据加载到第二个数据库时，是否更快:

1) 将结果直接通过管道传输到第二个 MySQL 服务器实例并使用 --compress 选项

或

2) 从文本文件加载它(即:mysql < my_sql_dump.sql)

Answer 1

快速转储静止的数据库:

将“-T”选项与 mysqldump 一起使用会导致指定目录中出现大量 .sql 和 .txt 文件。与使用 INSERT 语句的单个 .sql 文件相比，转储大型表的速度要快约 50%(挂钟时间减少 1/3)。

此外，如果您可以并行加载多个表并使多个内核饱和，那么在恢复时会有很大的好处。在 8 核机器上，除了“-T”提供的效率改进之外，恢复转储的挂钟时间差异可能高达 8 倍。因为“-T”导致每个表都存储在一个单独的文件中，所以并行加载它们比拆分一个巨大的 .sql 文件更容易。

将上述策略发挥到逻辑上的极致，可以创建一个脚本来广泛并行地转储数据库。好吧，这正是 Maakit mk-parallel-dump(参见 http://www.maatkit.org/doc/mk-parallel-dump.html)和 mk-parallel-restore 工具的用途；多次调用底层 mysqldump 程序的 perl 脚本。但是，当我尝试使用这些时，我无法在没有重复 key 错误的情况下完成恢复，而普通转储不会发生这种情况，因此请记住，您的里程数可能会有所不同。

从实时数据库中转储数据(没有服务中断):

--single-transaction 开关对于转储实时数据库而无需停止它或转储从属数据库而无需停止从动非常有用。

遗憾的是，-T 与 --single-transaction 不兼容，所以你只能得到一个。

通常，获取转储比恢复它要快得多。仍然有一个工具可以接收传入的整体转储文件并将其分成多个部分以并行加载。据我所知，这样的工具还不存在。

---

通过网络传输转储通常是成功的

要在一台主机上监听传入的转储:

nc -l 7878 > mysql-dump.sql

然后在您的数据库主机上运行

mysqldump $OPTS | nc myhost.mydomain.com 7878

这减少了主服务器上磁盘轴的争用，避免将转储写入磁盘，从而稍微加快转储速度(假设网络速度足以跟上，对于同一数据中心的两台主机来说，这是一个相当安全的假设)。另外，如果你正在构建一个新的从站，这就省去了完成后必须传输转储文件的步骤。

注意事项 - 显然，您需要有足够的网络带宽才不会拖慢速度，如果 TCP session 中断，您必须从头开始，但对于大多数转储来说，这不是主要问题。

---

最后，我想澄清一点常见的困惑。

尽管您在 mysqldump 示例和教程中经常看到这些标志，但它们是多余的，因为它们默认打开:

• --opt
• --add-drop-table
• --添加锁
• --create-options
• --禁用键
• --扩展插入
• --锁表
• --快速
• --设置字符集。

来自 http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html :

Use of --opt is the same as specifying --add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert, --lock-tables, --quick, and --set-charset. All of the options that --opt stands for also are on by default because --opt is on by default.

在这些行为中，“--quick”是最重要的行为之一(在传输第一行之前跳过在 mysqld 中缓存整个结果集)，并且可以与“mysql”一起使用(它不会打开 --quick默认情况下)以显着加快返回大型结果集的查询(例如转储大表的所有行)。