I ran into a very interesting issue with a customer recently. The customer was storing JPEGs in MySQL (as BLOBs obviously) and was wanting to setup some additional MySQL slaves. Nothing crazy there. So I did the normal steps of scheduling a mysqldump with the --all-databases and --master-data options. Trouble is, the next day when I start importing the dump, 'mysql' bombed up with "ERROR at line X: Unknown command '\0'". I used 'sed' to pull out the lines around the error and didn't really notice anything out of the ordinary. After a bit of Googling, one suggestion was to do a simultaneous backup and restore (basically a "mysqldump .. | mysql -h ..."). In this case, since I was using an existing slave to do the backups, this was something that was feasible to do. Unfortunately, the result was the same.
It turns out that 'mysqldump' will not always product a dump that is always BLOB friendly. To get around this, the '--hex-blob' option must be used. This option causes BLOBs to be written to the dump in hexadecimal notation. That is to say, each byte in the BLOB is converted to a hexadecimal string, thereby making it safe for importing, although this will likely inflate the raw dump by quite a bit. This is the first time I have actually run into this problem so I went ahead and gave this a shot. So far, it seems to have done the trick - the import completed without issue.
The thing I'm curious about, however, is why is this not default? The man page and online documentation also appear to be quite terse in regards to this setting. If it is unsafe to backup BLOBs using mysqldump without this option, seems like it should always be enabled. I would rather end up with a larger dump file (which could be compressed anyway) than end up with a backup that might not be restorable. In fact, while I am on that subject, I also wonder why '--quote-names' is not enabled default...
UPDATE: Turns out, this may have been related to
this bug which affects MySQL 5.0.81 and 5.1.34. The issue is when importing a series of large BLOBs in one compound statement. Rolling back the version of the 'mysql' client will resolve the issue. Also, '--quote-names' is, in fact, enabled by default.
Tags:
mysqldump, BLOBs