Skip to content

Instantly share code, notes, and snippets.

@dotmanila
Created January 18, 2013 13:48
Show Gist options
  • Save dotmanila/4564647 to your computer and use it in GitHub Desktop.
Save dotmanila/4564647 to your computer and use it in GitHub Desktop.
An automation script to use ibdconnect to dump orphan *.ibd files for tables from a specific list (db/tbl format).
#!/bin/bash
#BSE=/data/percona/Percona-Server-5.1.66-rel14.2-501.Linux.x86_64
BSE=/data/percona/Percona-Server-5.0.92-b23.85.Linux.x86_64
MYSQLD=$BSE/libexec/mysqld
DTA=/data/percona/data
SRC=/data/mysql.RECOVERED_FROM_2013_01_13_BACKUP
TBLS=/data/percona/tables.lst
TKIT=/data/percona/percona-data-recovery-tool-for-innodb
IBDC=$TKIT/ibdconnect
INNOCHKSUM=$TKIT/innochecksum_changer
MYSQL="mysql -S /data/percona/data/percona-sql.sock"
MYADMIN="mysqladmin -S /data/percona/data/percona-sql.sock"
MYDUMP="mysqldump -S /data/percona/data/percona-sql.sock"
FRSHDTA=/data/percona/data-cp-5.0
SRV="$MYSQLD --basedir=$BSE --pid-file=$DTA/percona-sql.pid --socket=$DTA/percona-sql.sock --datadir=$DTA --skip-grant-tables --log-error=$DTA/percona-sql.log --user=mysql --innodb-buffer-pool-size=1G --innodb-log-file-size=512M --port=3309 --innodb-data-home-dir=$DTA --innodb-log-files-in-group=2 --innodb-file-per-table"
for t in $(cat $TBLS); do
db=$(dirname $t)
tbl=$(basename $t)
echo "Trying $db.$tbl"
echo "Reloading fresh data ..."
rm -rf $DTA
cp -r $FRSHDTA $DTA
if [ ! -d $DTA/mysql ]; then
echo "Failed to copy fresh data, aborting!"
exit 1
fi
chown -R mysql.mysql $DTA
# Start MySQL
echo "Starting MySQL"
$MYSQLD --basedir=$BSE \
--pid-file=$DTA/percona-sql.pid \
--socket=$DTA/percona-sql.sock \
--datadir=$DTA --skip-grant-tables \
--log-error=$DTA/percona-sql.log \
--user=mysql --innodb-buffer-pool-size=1G \
--innodb-log-file-size=512M --port=3309 \
--innodb-data-home-dir=$DTA \
--innodb-log-files-in-group=2 \
--innodb-file-per-table &
PID=$!
echo "Giving mysqld 15 seconds to start..."
sleep 15
RET=1
while [ "x$RET" != "x0" ]; do
$MYADMIN ping
RET=$?
echo "Giving mysqld 5 seconds more to start..."
sleep 5
done
$MYSQL -e "CREATE DATABASE $db"
$MYSQL $db < /data/percona/schemas/$db.$tbl.sql
$MYADMIN shutdown
rm -rf data/$db/$tbl.ibd
cp $SRC/$db/$tbl.ibd $DTA/$db/$tbl.ibd
chown -R mysql.mysql $DTA/$db/$tbl.ibd
$IBDC -o $DTA/ibdata1 -f $DTA/$db/$tbl.ibd -d $db -t $tbl
RET=$?
echo "ibdconnect first pass returned $RET"
if [ "x$RET" != "x0" ]; then
echo "ibdconnect failed for $db.$tbl"
continue
fi
$INNOCHKSUM -f $DTA/ibdata1
RET=$?
if [ "x$RET" != "x0" ]; then
echo "innochecksum first pass failed for $db.$tbl"
continue
fi
$INNOCHKSUM -f $DTA/ibdata1
RET=$?
if [ "x$RET" != "x0" ]; then
echo "innochecksum second pass failed for $db.$tbl"
continue
fi
$INNOCHKSUM $DTA/ibdata1
RET=$?
if [ "x$RET" != "x0" ]; then
echo "innochecksum third pass failed for $db.$tbl"
continue
fi
chown -R mysql.mysql $DTA
echo "Starting MySQL again"
$MYSQLD --basedir=$BSE \
--pid-file=$DTA/percona-sql.pid \
--socket=$DTA/percona-sql.sock \
--datadir=$DTA --skip-grant-tables \
--log-error=$DTA/percona-sql.log \
--user=mysql --innodb-buffer-pool-size=1G \
--innodb-log-file-size=512M --port=3309 \
--innodb-data-home-dir=$DTA \
--innodb-log-files-in-group=2 \
--innodb-file-per-table --innodb-force-recovery=5 &
PID=$!
echo "Giving mysqld 15 seconds to start..."
sleep 15
RET=1
while [ "x$RET" != "x0" ]; do
$MYADMIN ping
RET=$?
echo "Giving mysqld 5 seconds more to start..."
sleep 5
done
ROWS=$($MYSQL -BNe "SELECT COUNT(*) FROM $db.$tbl")
RET=$?
echo "Rows check returned $RET"
echo "Number of rows found $ROWS"
if [ "x$RET" == "x0" ]; then
echo "Dumping rows for $db.$tbl"
$MYDUMP --databases $db --tables $tbl > /data/percona/dumps/$db.$tbl.sql
RET=$?
if [ "x$RET" != "x0" ]; then
echo "Dump failed!"
fi
fi
$MYADMIN shutdown
done
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment