mySQL innodb binary to .sql dump files

Today I found out that you can grab a .sql file from an innodb or other format binary reliably – as opposed to copying the whole lot back and hoping for the best.

This may not sound like the kind of thing that would excite – however if you have ever faced the ever diminishing options of a database that has died, corrupted, or platform that has delivered this stinking mess to your door – and all you have is a binary backup or access to the binary files – then this – this is mana from heaven.

Before being aware of this it would very much have been an “oh you have those do you, well good luck”.

For this to function – you WILL NEED TO BE ROOT – just so you know.

YMMV – however false hope and options are sometimes all it needs to get you in the right direction – and this may just be close enough to work for you.

The plan is to:

  • Create a directory such as /tmp/recover-databases or similar;
  • Give this a relative path for the mysql guff so for example /tmp/recover-databases/var/lib/mysql like a little chroot gaol for all this goodness to take place in – a sandbox (cue memories of clearing the kitty litter tray) as it where;
  • Go locate your existing broken dreams – and grab the whole /var/lib/mysql/mysql grap the mighty big ib_* files, grab the folders of any of the DB’s that you need;
  • Copy these into your /tmp/recover-databases/var/lib/mysql ;
  • Copy the script below into the root of /tmp/recover-databases/ – chmod +x it so we can run it – and run it.

While it may error – the proof is in the logging / errors it creates – along with the creation of .sql files that you can then rebuild your DB’s from.

Praise be.



if [ -z “$workdir” ]||[ “$workdir” = “/” ]||[ “$workdir” = “/var/lib” ]||[ “$workdir” = “/var/lib/mysql” ]; then
echo “$workdir is not a valid working directory…”
echo “Please run this script from the ‘alternate location’ where the files were restored”

echo -n “Creating my.cnf for temporary (sandbox) instance … ”
cat > $workdir/my.cnf << EOF

echo “Done”

echo -n “Creating temporary datadir … ”
mkdir ${workdir}/mysql
echo “Done”

echo “Installing mysql structure in ${workdir}/mysql/ …”
mysql_install_db –datadir=${workdir}/mysql > /dev/null 2>&1

echo “Creating empty logfile…”
touch ${workdir}/mysql/mysqld.log

echo “Copying restored data into temporary datadir…”
mv ${workdir}/var/lib/mysql/* ${workdir}/mysql/

echo “Setting ownerships and permissions…”
chown -R mysql.mysql $workdir
chmod -R 755 $workdir
chmod 644 ${workdir}/my.cnf

echo “Starting mysql sandbox instance…”
mysqld_safe –defaults-file=${workdir}/my.cnf &
sleep 40

echo “Checking for InnoDB…”
have_innodb=$(mysql -S ${workdir}/mysql/mysql.sock -NB -e “show variables like ‘have_innodb'”|awk ‘{print $2}’)
if [ “$have_innodb” != “YES” ]; then
echo “Did not detect InnoDB functionality… This script is exiting, and manual checking will be necessary.”

echo “Looking for databases…”

for database in $(mysql -S ${workdir}/mysql/mysql.sock -BN -e “show databases”|grep -Ev ‘mysql|test|information_schema’); do
echo “Found: $database”
echo -n “Dumping $database to: $database.sql … ”
mysqldump -S${workdir}/mysql/mysql.sock $database > ${workdir}/${database}.sql
echo “Done”
echo “You can import this into your production instance with something like:”
echo “# Example:”
echo “# mysql -uroot -p $database < ${workdir}/${database}.sql”

echo -n “Stopping temporary mysql (sandbox) instance … ”
kill $(ps -fC mysqld|grep “$workdir”|awk ‘{print $2}’)
echo “Done”

echo; echo “This script is finished. Any mysqldump files should be found”
echo “in $workdir. You should be able to use these files to import databases”

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: