innodb-recovery.sh

#!/bin/bash
#[email protected]
#run as root in BACKUP_DB_PATH

MYSQL_USER=root
MYSQL_PASSWORD=joseph
MYSQL_HOST=127.0.0.1
MYSQL_DATA_PATH=/var/lib/mysql
TARGET_PORT=9999
THIS_USER=root
DB_NAME=keystone
BACKUP_DB_PATH=.

echo "Preparing mysql for recovery..."
service mysql stop
rm -fr $MYSQL_DATA_PATH
dpkg-reconfigure mysql-server-5.6

sleep 10s

mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "CREATE DATABASE $DB_NAME;"

for f in *.ibd
do
	#f=credential.ibd
	#f=$1
	f=${f%.ibd}
	echo "Processing $f..";

	echo ">>>generate create statements"
	mysqlfrm --server=$MYSQL_USER:$MYSQL_PASSWORD@$MYSQL_HOST --port $TARGET_PORT --user $THIS_USER $BACKUP_DB_PATH/$f.frm | grep -v ^# > $f.sql; 

	#cat $f.sql

	echo ">>>feed the create to mysql"
	mysql -u$MYSQL_USER -p$MYSQL_PASSWORD $DB_NAME < $f.sql
done


echo ">>>stop mysql" 
service mysql stop	

for f in *.ibd
do
	f=${f%.ibd}
	echo "Processing $f..";
	echo ">>>remove generated data"
	rm $MYSQL_DATA_PATH/$DB_NAME/$f.ibd

	echo ">>>copy backup table"
	cp $BACKUP_DB_PATH/$f.ibd $MYSQL_DATA_PATH/$DB_NAME/$f.ibd

	echo ">>>set permission and ownership"
	chmod 660 $MYSQL_DATA_PATH/$DB_NAME/$f.ibd
	chown mysql.mysql  $MYSQL_DATA_PATH/$DB_NAME/$f.ibd
done

echo ">>>restart mysql"
service mysql start
sleep 30s


for f in *.ibd
do
	f=${f%.ibd}
	echo "Processing $f..";
	echo ">>>discard table space"
	mysql -u$MYSQL_USER -p$MYSQL_PASSWORD --connect_timeout 120 -e "ALTER TABLE $DB_NAME.$f DISCARD TABLESPACE;"
	
	sleep 10s

	echo ">>>import table space"
	mysql -u$MYSQL_USER -p$MYSQL_PASSWORD --connect_timeout 120 -e "ALTER TABLE $DB_NAME.$f IMPORT TABLESPACE;"
	
	sleep 10s

	echo ">>>check for data"
	mysql -u$MYSQL_USER -p$MYSQL_PASSWORD --connect_timeout 120 -e "SELECT COUNT(*) FROM $DB_NAME.$f;"
		
done

#cleanup
#rm *.sql