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.

#!/bin/sh

workdir=`pwd`

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"
exit
fi

echo -n "Creating my.cnf for temporary (sandbox) instance ... "
cat > $workdir/my.cnf << EOF
[mysqld]
skip-networking
datadir=$workdir/mysql
socket=$workdir/mysql/mysql.sock
innodb_data_home_dir=$workdir/mysql
innodb_data_file_path=ibdata1:10M:autoextend
innodb_log_group_home_dir=$workdir/mysql
innodb_log_files_in_group=2
skip-grant-tables
[mysql.server]
user=mysql
basedir=$workdir
[mysqld_safe]
err-log=$workdir/mysql/mysqld.log
pid-file=$workdir/mysql/mysqld.pid
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."
exit
fi

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"
done

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 *