Dumping BLOB's from the MySQL database

A customer who is handling digital certificates had a problem with one of those. So we had to investigate.

Because the certificate is in binary form it is stored in a BLOB and we had to extract it from the database to do some verifications.

What first came to my mind was to extract the certificate with the SELECT INTO OUTFILE command. But the verification tool complained and told us that the certificate has a wrong format.

Luckily I found in the documentation the following statement: If you use INTO DUMPFILE instead of INTO OUTFILE, MySQL writes only one row into the file, without any column or line termination and without performing any escape processing. This is useful if you want to store a BLOB value in a file.

We tried it out with:

mysql> SELECT certificate
  INTO DUMPFILE '/tmp/certificate.bin'
  FROM identity
 WHERE id = 42;

and it perfectly worked. The certificate verification tool did not complain anymore and we could continue investigating, why this certificate had a problem at all...

With this command, which I did not know before, it is very simple to dump a single BLOB into a file and process it further from there.

Just keep in mind that MySQL has a general protocol limitation for BLOB's of 1 Gbyte and max_allowed_packet size has to be adjusted accordingly for the client AND the server when you use big BLOB's. More information about MySQL limitations you can find here.

Further the type of BLOB has to be chosen according to the expected size of BLOB's. See Data Type Storage Requirements.

Literature

1. SELECT Syntax