MySQL 转储_MySQL

作为 HTTP Archive 项目的一部分，我为每次爬网创建 MySQL 转储（每月 1 日和 15 日）。您可以从下载页面访问转储列表。有几个人使用这些转储，最著名的是 Ilya Grigorik，他将数据导入 Google BigQuery。

去年，我对许多功能请求犹豫不决，因为它们需要架构更改。我不确定更改架构会如何影响更改之前的转储文件的使用。这篇博文总结了我的发现。

格式

当我启动 HTTP Archive 时，所有转储都使用如下命令以 MySQL 格式导出：

mysqldump --opt --skip-add-drop-table -u USERNAME -p -h SERVER DBNAME TABLENAME | gzip > TABLENAME.gz

这些 MySQL 格式的转储文件是这样导入的：

gunzip -c TABLENAME.gz | mysql -u USERNAME -p -h SERVER DBNAME

使用 MySQL 以外的数据库的人要求我也以 CSV 格式导出。此导出命令的输出是两个文件：TABLENAME.txt 和 TABLENAME.sql。 .txt 文件为 CSV 格式，可以使用单独的命令进行 gzip 压缩。

mysqldump --opt --complete-insert --skip-add-drop-table -u USERNAME -p -h SERVER -T DIR DBNAME TABLENAMEgzip -c DIR/TABLENAME.txt > DIR/TABLENAME.csv.gz

此 CSV 转储的导入方式如下：

gunzip DIR/TABLENAME.csv.gzmysqlimport --local --fields-optionally-enclosed-by="/"" --fields-terminated-by=, --user=USERNAME -p DBNAME DIR/TABLENAME.csv

最大的 HTTP 存档转储文件是 ~解压后 25G，gzip 后约 3G。这凸显了使用 CSV 格式转储的一个缺点：无法在内存中进行 gzip 和 ungzip。这是因为 mysqlimport 命令使用文件名来确定要使用哪个表 - 如果您通过管道输入行，那么它不会知道表名。如果磁盘空间有限，解压缩 25G 文件可能是一个挑战。

另一方面，CSV 导入比使用 MySQL 格式文件快约 30%。导入 3000 万行时，这可以节省一个多小时。 HTTP Archive 目前提供 MySQL 和 CVS 格式的转储，以便人们可以在更少的磁盘空间或更快的导入之间进行选择。

前向兼容性

我主要关心的是先前生成的转储的灵活性根据以后的架构更改创建文件 - 即添加和删除列。

MySQL 格式的转储文件可以很好地处理添加的列。转储中的 INSERT 命令与特定的列名称相关联，因此新列将被忽略。 CSV 格式的转储不太灵活。行中的值按顺序填充到表的列中。如果最后添加一个新列，一切都会正常。但是，如果在现有列的中间添加一列，则行值都会向左移动一列。

这两种格式都不适用于删除的列。 MySQL 格式的文件将失败并出现“未知列”错误。 CSV 格式的文件可以工作，但所有列都会移动，这次是向右移动。

要点

现在，如果我遵循这些准则，我可以放心地进行架构更改，而无需使现有转储文件失效:

• 不要删除列– 如果不再需要某个列，我会将其保留在原处并将列定义修改为较小的尺寸。
• 在末尾添加列 – 我更喜欢按语义组织我的列，但从此时开始的所有新列都将添加在末尾。

我'将继续以 MySQL 和 CSV 格式创建转储。这些准则确保所有过去和未来的转储文件都将根据最新架构工作。