InnoDB Crash Recovery Guide

STOP : If there are any problems or you are uncomfortable doing this procedure, do not proceed and ASK A COWORKER  :D :D :D 

• Make sure you have free disk space in /home and /var/lib/mysql or where ever your mysql databases are stored. This guide assumes they are in /var/lib/mysql.

FOR CPANEL[edit]

Step 1: Disable tailwatchd (chkservd on 11.23) to prevent mysql from being restarted. Stop any other processes that may access mysql including munin, backups, and the webserver if possible.

 killall -TERM tailwatchd

Step 2: Add

 innodb_force_recovery = 3

to /etc/my.cnf

Step 3: Restart mysql

 /etc/init.d/mysqld restart

Step 4: Verify mysql is up

Step 5: Put this script in ~/recover.pl and run it

#!/usr/bin/perl
 
BEGIN { unshift @INC, '/usr/local/cpanel'; }
 
use Cpanel::MysqlUtils         ();
 
mkdir('/home/innodb_dumps',0700);
 
chdir('/home/innodb_dumps') || die "Could not create /home/innodb_dumps";
 
my $pwd = `pwd`;
chomp($pwd);
my $hasinnodb = 0;
my $dbdir     = Cpanel::MysqlUtils::getmysqldir();
my $dbcheckok = -d $dbdir . '/mysql' ? 1 : 0;
if ($dbcheckok) {
    opendir( my $sql_dh, $dbdir );
    while ( my $db = readdir($sql_dh) ) {
        next if ( $db =~ m/^\.+$/ );
        next if $db eq 'mysql';    # mysql db should never have InnoDB on
        next if $db eq 'horde';    # Horde session table is the only one that uses InnoDB, so this is not a problem
        next if ( !-d $dbdir . '/' . $db );
        my $ms = sqlcmd("show table status from `$db`;");
        if ( !$ms ) {
            $dbcheckok = 0;
            last;
        }
        elsif ( $ms =~ m/\s+InnoDB\s+/m ) {
                print "Saving to $pwd/$db.sql...";
                system "mysqldump -c $db > $db.sql";
                print "Done\n";
        }
    }
    closedir($sql_dh);
}
 
 
 
 
sub sqlcmd {
    my ($cmd) = @_;
    my $result;
 
    my $mysql = Cpanel::MysqlUtils::find_mysql();
    my $pid = IPC::Open3::open3( \*WTRFH, \*RDRFH, ">&STDERR", $mysql, '-N' );
    print WTRFH "show status like 'uptime'; $cmd\n";    #make sure we already get something back so we know mysql is up
    close(WTRFH);
    while (<RDRFH>) {
        $result .= $_;
    }
    close(RDRFH);
    waitpid( $pid, 0 );
    return $result;
}

If the script fails, remove your backup attempt in /home/innodb_dumps, increase the innodb_recovery_level in /etc/my.cnf, restart MySQL, and rerun the script. Repeat until you complete a backup without errors

Step 6: Stop MySQL

Step 7: Do this:

 mkdir /var/lib/mysql/INNODB_BACKUPS
 mv /var/lib/mysql/ib* /var/lib/mysql/INNODB_BACKUPS

cd /home/innodb_dumps
for i in $(ls -1  | cut -d'.' -f1) ; do mv /var/lib/mysql/$i  /var/lib/mysql/INNODB_BACKUPS/ ;  mkdir /var/lib/mysql/$i ; chown mysql. /var/lib/mysql/$i ; done

Step 8: For each database that was dumped to /home/innodb_dumps move (do not copy, completely move, or the database may not be properly recreated from the backups you made) the /var/lib/mysql/DBNAME folder to the /var/lib/mysql/INNODB_BACKUPS directory

Step 9: Create folders for each database that was moved in /var/lib/mysql and chown them to mysql:mysql

Step 10: Remove the following from my.cnf and start mysql:

 innodb_force_recovery = 3

Step 11: Start mysql and restore all databases in /home/innodb_dumps to their respective database

cd /home/innodb_dumps
for i in * ; do x=$(echo $i | cut -d'.' -f1) ;  mysql -o $x < $i ; done

Step 12: Check the mysql server logs to ensure there were no errors

Step 13: Restart mysql and party.

FOR ALL OTHER LINUXES 

Step 1: Add

 innodb_force_recovery = 3

to /etc/my.cnf

Step 2: Restart mysql

 /etc/init.d/mysqld restart

Step 3: Verify mysql is up

Step 4: Dump all innodb databases to a directory i.e /root or /root/recovery

Step 5: Stop MySQL

Step 6: Do this:

 mkdir /var/lib/mysql/INNODB_BACKUPS
 mv /var/lib/mysql/ib* /var/lib/mysql/INNODB_BACKUPS

Step 7: For each database that was dumped move the /var/lib/mysql/DBNAME folder to the /var/lib/mysql/INNODB_BACKUPS directory

Step 8: Create folders for each database that was moved in /var/lib/mysql and chown them to mysql:mysql

Step 9: Remove the following from my.cnf and start mysql:

 innodb_force_recovery = 3

Step 10: Start mysql and restore all databases in /home/innodb_dumps to their respective database

Step 11: Check the mysql server logs to ensure there were no errors

Step 12: Restart mysql and party.