Doing the MySQL backup
In this short post I would like to share with you a problem I encountered and the solution found while working with the MySQL tool mysqldump. The story is quite simple: doing some maintenance work on one production application server it was standard procedure to make a full MySQL backup before we did any work. So, we did using the following command:
mysqldump -u root - p --all-databases > DUMP-FILE-NAME.dmp
Dump file was created, it had about 3.5 GB and everything seemed fine. We started doing the operations planed and we hit an error which resulted in the database becoming compromised. We said, no problem, we just restore the backup and that’s all. So, we used the following command:
mysql -u root -p < DUMP-FILE-NAME.dmp
This started processing for a while and them booom, we got errors, lots of them:
ERROR at line 12282: Unknown command '\0'. Invalid command "\0" Invalid command "\\" PAGER set to stdout ERROR: Unknown command '\0'. ERROR 1102 (42000): Incorrect database name 'q0~0?sq
Looking into the file (with some difficulty due to it’s size) we realized that our BLOB columns were exported “as-is”, so with binary characters inside the insert queries. No wonder why the queries were not accepted on import. Doing some research it turns out that you can export BLOBs and later import them as long as the content of the BLOBs do not mess up the queries – so sometimes it works, sometimes it doesn’t. After more investigation it turns out we should have used something like:
mysqldump -u root -p --all-databases --hex-blob > DUMP-FILE-NAME.dmp
–hex-blob option encodes BLOBs, so the queries don’t get messed up anymore. Oh, how we wished we knew that before making the export and compromising the database. What is beyond me is why mysqldump does not do the encoding by default when exporting since chances are that the import will not work. Or at least why doesn’t it give a warning. Anyway, now we knew the problem, but the solution was not so obvious.
Solution that worked
After trying several things like:
- splitting the dump file per database (we had aprox 100 databases in the dump out of which 30 were live)
- thinking about extracting the values of the BLOB columns and replacing it with the encoded version
- trying all sorts of googling
we were pretty miserable (it was already 11 pm), so we contacted our hosting provider to make the restore of the entire virtual machine – meaning we basically lost aprox 24h of work for our clients…
However, we had downloaded the 3.5 GB dump file and just for the heck of it we tried an import on our MySQL installation. To our amazement it worked. Why did it work? Turns out that on the server we had MySQL 5.0 while on our internal servers we had MySQL 5.5.
So, that means that the newer version of MySQL can import dumps with BLOBs even without the –hex-blob option. Interesting, isn’t it?
The obvious solution was therefore:
- import the dump in MySQL 5.5
- export it again but WITH the –hex-blob option; this resulted in a 6.3 GB file
- import it on MySQL 5.0
We did just that, everything worked, we were all happy and around 1 am we could all go home “happy”.
Hope this experience is useful to others. Please leave me some comments if you have any questions/remarks.
