Innodb Restore

You might have been through issues while trying restoring  Innodb databases in your servers. You cannot simply overwrite the database folder if the database have innodb tables as you just to with Myisam, being the fact that the innodb are very sensitive to crashes and doing so would break the innodb engine.
I have been fighting such issues earlier where I had to keep mysqldump databases for innodb databases, other than the full server file level backups with R1Soft.

Below are the steps to restore mysql database with innodb tables. What it actually doing is , set up a temporary mysql "sandbox" instance using the innodb files and the filebased backups of  the database and create a mysql dump of the database.

1. restore these files to a temporary directory /dbrestore

/var/lib/mysql/ibdata1
/var/lib/mysql/ib_logfile0
/var/lib/mysql/ib_logfile1
(restore to "alternate location" /dbrestore)

2. Restore the database directory for the database that you want to restore,
to the same alternate directory: (say database name database_name)

/var/lib/mysql/database_name
(restore to "alternate location" /dbrestore)

In case of cpanel, restore to alternate location /dbrestore will place the
files at /dbrestore/var/lib/mysql/database_name. In case of ensim, it will
place at /dbrestore/home/virtual/sitexxx/fst/var/lib/mysql/database_name .
So, after restoring to the alternate folder, you will need to copy the
database folder to /dbrestore/var/lib/mysql so that at the end the files
will be at /dbrestore/var/lib/mysql/

/dbrestore/var/lib/mysql/ibdata1
/dbrestore/var/lib/mysql/ib_logfile0
/dbrestore/var/lib/mysql/ib_logfile1
/dbrestore/var/lib/mysql/database_name

3. Log into the target host with SSH, and cd to our temporary directory
(cd /dbrestore/)


4. Download and run the restore script (Pasted below)

# sh ./restoreDB.sh
(make sure you're in the /dbrestore directory while executing the script !!!)

5. On success, above step will create a mysql dump, database_name.sql in
the /dbrestore folder, which can be imported over the database needed the
restore or to an alternate one.


root@server [~]# cat /dbrestore/restoreDB.sh
#!/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
set-variable = 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 20

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"
echo "back into your production instance. Please contact support if you have any questions."