Saving blob field to disk from bash

Question

I have a mysql database with a blob field containing zip and I need to save it as a file on disk, from bash. I am doing the following, but the end result is not readable as zip ... Am I doing something wrong or the file is not actually zip (is the record in the database actually created by the seismological station, so I do not control it)?

echo "USE database; SELECT blobcolumn FROM table LIMIT 1" | mysql -u root > file.zip

then I open the .zip file with a file editor and delete the first row containing the column heading. Then "unzip" does not recognize it as a zip file.

Answer 1

For gzipped blob you can use:

echo "use db; select blob from table where id=blah" | mysql -N --raw -uuser -ppass > mysql.gz

I have not tried this with a zip file.

Answer 2

- DUMPFILE, mysql .

mysql -uroot -e "SELECT blobcolumn INTO DUMPFILE '/tmp/file.zip' FROM table LIMIT 1" database

Answer 3

, , , .

I found that mysql appends a newline at the end, which needs to be removed before the correct binary value remains.

echo "USE database; SELECT blobcolumn FROM table LIMIT 1" | mysql -N --raw -u root | head -c -1 > file.zip

Answer 4

you will need to skip the column e.g.

sql="USE database; SELECT blobcolumn FROM table LIMIT 1"
mysql -u root -N <<< $sql > file.zip