mysqldump处理二进制数据可靠吗?

Question

我在MySQL 5.6中有一些表，在某些字段中包含大量二进制数据。我想知道我是否可以信任mysqldump创建的转储，并确保这些二进制字段在通过FTP、SCP等系统传输转储文件时不会轻易损坏。另外，我是否应该强制这些系统将转储文件视为二进制传输而不是ascii传输?

提前感谢您的任何评论!

Answer 1

不，当您有二进制blobs时，它并不总是可靠的。在这种情况下，你必须使用"——hex-blob"获得正确结果的标志。

下面注释的警告:

如果将——hex-blob与-T标志(每个表一个文件)结合使用，则hex-blob标志将被忽略，静默

我有一个案例，这些调用失败(导入在不同的服务器上，但都运行Centos6/MariaDB 10):

mysqldump --single-transaction --routines --databases myalarm -uroot -p"PASSWORD" | gzip > /FILENAME.sql.gz
gunzip < FILENAME.sql.gz | mysql -p"PASSWORD" -uroot --comments

生成一个文件，该文件静默导入失败。添加"——skip-extended-insert"给了我一个更容易调试的文件，我发现生成了这一行，但无法读取(但导出或导入都没有报告错误):

INSERT INTO `panels` VALUES (1003,1,257126,141,6562,1,88891,'??\?ŖeV???,NULL);

请注意，二进制数据的终止引号在原始文件中丢失了。

select hex(packet_key) from panels where id=1003;
--> DE77CF5C075CE002C596176556AAF9ED

列是二进制数据:

CREATE TABLE `panels` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `enabled` tinyint(1) NOT NULL DEFAULT '1',
  `serial_number` int(10) unsigned NOT NULL,
  `panel_types_id` int(11) NOT NULL,
  `all_panels_id` int(11) NOT NULL,
  `installers_id` int(11) DEFAULT NULL,
  `users_id` int(11) DEFAULT NULL,
  `packet_key` binary(16) NOT NULL,
  `user_deleted` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  ...

所以，不，你不仅不能信任mysqldump，你甚至不能依赖它在错误发生时报告错误。

---

我使用的一个丑陋的解决方案是通过在转储中添加如下选项来mysqldump排除两个受影响的表:

--ignore-table=myalarm.panels 

然后这个BASH脚本hack。基本上运行一个SELECT，生成INSERT值，其中NULL列被处理，二进制列被转换为UNHEX()调用，如下所示:

(123,45678,UNHEX("AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA"),"2014-03-17 00:00:00",NULL),

粘贴到你选择的编辑器中，如果你需要使用它。

echo "SET UNIQUE_CHECKS=0;SET FOREIGN_KEY_CHECKS=0;DELETE FROM panels;INSERT INTO panels VALUES " > all.sql
mysql -uroot -p"PASSWORD" databasename -e "SELECT CONCAT('(',id,',', enabled,',', serial_number,',', panel_types_id,',', all_panels_id,',', IFNULL(CONVERT(installers_id,CHAR(20)),'NULL'),',', IFNULL(CONVERT(users_id,CHAR(20)),'NULL'), ',UNHEX("',HEX(packet_key),'"),', IF(ISNULL(user_deleted),'NULL',CONCAT('"', user_deleted,'"')),'),') FROM panels" >> all.sql
echo "SET UNIQUE_CHECKS=1;SET FOREIGN_KEY_CHECKS=1;" > all.sql

这给了我一个名为"all.sql"需要将INSERT中的最后一个逗号转换为分号，然后可以像上面那样运行。我需要一个"大的导入缓冲区"调整在交互式mysql shell和命令行中设置以处理该文件，因为它太大。

mysql ... --max_allowed_packet=1GB

当我报告这个bug时，我最终被指向了"——hex-blob"Flag，它与我的解决方案相同，但从我的角度来看是微不足道的。添加该选项，blobs被转储为十六进制，结束

Answer 2

mysqldump生成的转储文件是可信的。

为了避免编码，二进制传输等问题，使用--hex-blob选项，因此它以十六进制数转换每个字节(例如，'abc'变成0x616263)。它将使转储更大，但它将是最兼容和最安全的方式来获得信息(因为它将是纯文本，没有奇怪的误解，由于在文本文件上生成的二进制数据的特殊符号)。

您可以确保转储文件的完整性(并加快传输速度)，将其打包到rar或zip文件中。这样你就可以很容易地检测到它没有在传输过程中损坏。

当您尝试在服务器上加载它时，检查您已在my.cnf服务器配置文件上分配了

[mysqld]
max_allowed_packet=600M

或更多

顺便说一句，现在我刚刚做了一个迁移，并转储了大量的二进制数据与mysqldump，它工作得很好。

Answer 3

是的，您可以信任mysqldump生成的转储。

是的，您应该使用二进制传输，以避免在传输过程中进行任何编码转换。MySQL dump将控制命令添加到转储中，以便服务器在重新导入时以特定的编码解释文件。您不希望更改此编码