Recover data blob binary data

[Peter Peter]

Hello to all,

So I'll explain my problem :

I backed up a database using the following script :

[root@nothing backup]# cat backup_db.sh
#!/bin/sh

TIMESTAMP=`date +%Y%m%d%H%M`

echo "### Dumping database - mysql ###"
mysqldump -u root -p mysql --single-transaction | gzip > mysql_database-$TIMESTAMP.sql.gz
echo "### Dumping database - f360db ###"
mysqldump -u root -p db --single-transaction | gzip > db_database-$TIMESTAMP.sql.gz

And then tried to restore it after a migration failure of the database due to lack of disk space :

[root@nothing backup]# gunzip < db_database-201106291110.sql.gz | mysql -u root -p db
Enter password:
ERROR at line 1853: Unknown command '\''.

I used the following command to get the line 1853 :

awk '{ if (NR==1853) print $0 }' db_database-201106291110.sql >> getit.txt

Here is the begining of this line :

INSERT INTO `datablob` VALUES (1978,'PK\0\0\0F‹~=\0\0\0\0\0\0\0\0\0\0\0\0\n\0\0\0audit.fvdlì½[

So my main problem is that I didn't used the --hex-blob option to hex all the binary data of blobs and ... well I deleted the main database so I can't do the backup again in the right way. I only discovered afterwards that I would need this kind of option.

I tried to hex all the binary data back but it's not really binary as the \0 is on 2 bytes and not as it should be on one byte.

Is there a possibility to transform back the data to real binary data and then to hex it ?

I hope I was precise enough, should you require more information I will provide it.

Thanks for the help if there is any !

Regards,

Peter



Edited 1 time(s). Last edit at 07/25/2011 02:53AM by Peter Peter.