Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Does mysqldump handle binary data reliably?

I have some tables in MySQL 5.6 that contain large binary data in some fields. I want to know if I can trust dumps created by mysqldump and be sure that those binary fields will not be corrupted easily when transferring the dump files trough systems like FTP, SCP and such. Also, should I force such systems to treat the dump files as binary transfers instead of ascii?

Thanks in advance for any comments!

like image 415
Francisco Zarabozo Avatar asked May 15 '13 07:05

Francisco Zarabozo


People also ask

Is Mysqldump consistent?

mysqldump is used to take logical backup of the database or multiple database. --single-transaction uses a consistent read and guarantees that data seen by mysqldump does not change.

How long will Mysqldump take?

It took a total of 1 minute 27 seconds to take a dump of the entire database (same data as used for mysqldump) and also it shows its progress which will be really helpful to know how much of the backup has completed.

Does Mysqldump lock DB?

By default, the mysqldump utility, which allows to back a MySQL database, will perform a lock on all tables until the backup is complete. In many cases, the amount of data in the database and the uptime requirements will not allow this lock in real life.

Does Mysqldump drop database?

In this case, mysqldump writes a DROP DATABASE statement preceding each CREATE DATABASE statement.


1 Answers

No, it is not always reliable when you have binary blobs. In that case you MUST use the "--hex-blob" flag to get correct results.

Caveat from comment below:

If you combine the --hex-blob with the -T flag (file per table) then the hex-blob flag will be ignored, silently

I have a case where these calls fail (importing on a different server but both running 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 

It produces a file that silently fails to import. Adding "--skip-extended-insert" gives me a file that's much easier to debug, and I find that this line is generated but can't be read (but no error is reported either exporting or importing):

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

Note that the terminating quote on the binary data is missing in the original.

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

The column is binary data:

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`),   ... 

So no, not only can you not necessarily trust mysqldump, you can't even rely on it to report an error when one occurs.


An ugly workaround I used was to mysqldump excluding the two afflicted tables by adding options like this to the dump:

--ignore-table=myalarm.panels  

Then this BASH script hack. Basically run a SELECT that produces INSERT values where the NULL columns are handled and the binary column gets turned into an UNHEX() call like so:

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

Paste it into your editor of choice to play with it if you need to.

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 

That gives me a file called "all.sql" that needs the final comma in the INSERT turned into a semicolon, then it can be run as above. I needed the "large import buffer" tweaks set in both the interactive mysql shell and the command line to process that file because it's large.

mysql ... --max_allowed_packet=1GB 

When I reported the bug I was eventually pointed at the "--hex-blob" flag, which does the same as my workaround but in a trivial from my side way. Add that option, blobs get dumped as hex, the end.

like image 145
Code Abominator Avatar answered Sep 17 '22 13:09

Code Abominator