MySQL Repair

This page is for level 3+ techs only.

The strategy for MySQL crashes is simple:

Last Ditch Efforts

The steps outlined in this wiki are drastic. Check the following items to see if you can avoid the rest of this.

mysql -e 'show engines;'
less /var/lib/mysql/`hostname`.err
mysqlcheck -Asc

If InnoDB exhibits corruption, always create a new datadir. Even after recreating a single table or database, the corrupted pages remain in the tablespace, and will lead to further corruption.

Make sure you have a full MySQL datadir backup before any of the following.

Prep Work

Backup the datadir

First things first, if you have not already, create a backup of the datadir. This can be done with the following - adjust as needed, and make sure your last rsync occurs while MySQL is shutdown.

rsync -aHlP /var/lib/mysql/ /backup/mysql.datadir.$(date -u +%F.%T%z)/

Start MySQL in Recovery

In /etc/my.cnf, first 1, 4, then 6.

innodb_force_recovery=4

Then make sure you start/restart MySQL.

Stop MySQL Monitoring

On cPanel:

/usr/local/cpanel/libexec/tailwatchd --disable=Cpanel::TailWatch::ChkServd

Prep

In one terminal, run (and let it run):

mysql -Bse 'show variables like "log_error";'|awk '{print $2}'|xargs tail -f

In another terminal, start a screen:

screen -S "mysql_recovery"

Verify /backup has plenty of space - if it does not, modify $dumpdest.

Within that screen:

dumpdest=/backup/mysql.recovery.$(date -u +%F.%T%z)
datadir=$(mysql -Bse 'SHOW GLOBAL VARIABLES LIKE "datadir";')
mkdir -p $dumpdest/dbdumps
mysql -Bse 'show databases;'|grep -v -e 'information_schema' -e 'performance_schema' -e 'mysql' > $dumpdest/dbs.in
cat /dev/null > $dumpdest/export.log

Dump Permissions

mysqldump --events mysql > $dumpdest/mysql.sql

Dump Databases

Run multiple times. Does not repeat good DBs and delays on bad.

echo 'starting db dumps' | tee -a $dumpdest/export.log
[[ -f $dumpdest/dbs.bad ]] && mv $dumpdest/dbs.bad $dumpdest/dbs.in
cat $dumpdest/dbs.in|while read db; do echo 'dumping' $db | tee -a $dumpdest/export.log; mysqldump --single-transaction --triggers --routines --events $db  2>>$dumpdest/export.log >$dumpdest/dbdumps/$db.sql; if [[ $? -ne 0 ]]; then echo $db >> $dumpdest/dbs.bad ; sleep 5 ; rm -f $dumpdest/dbdumps/$db.sql; fi; done
rm -f $dumpdest/dbs.in

Prep Tables

When you want to move onto tables, run the following to prep.

cat $dumpdest/dbs.bad|while read db; do mkdir -p $dumpdest/bad_dumps $dumpdest/tbldumps/$db; echo "SHOW TABLES FROM $db;"|mysql -Bs > $dumpdest/$db.tbl.in; done

Dump Tables

Run multiple times. Does not repeat good tables and delays on bad.

echo 'starting table dumps' | tee -a $dumpdest/export.log
find $dumpdest -mindepth 1 -maxdepth 1 -type f -name "*.tbl.bad"|awk '{orig=$0; gsub(".tbl.bad$", ".tbl.in", $0); print "mv", orig, $0}'|bash
find $dumpdest -mindepth 1 -maxdepth 1 -type f -name "*.tbl.in"|awk -F'/' '{gsub(".tbl.in", "", $NF); print $NF;}'|while read db; do cat $dumpdest/$db.tbl.in|while read tbl; do echo 'dumping' $db $tbl | tee -a $dumpdest/export.log; mysqldump --single-transaction --triggers --routines --events $db $tbl 2>>$dumpdest/export.log >$dumpdest/tbldumps/$db/$db.$tbl.sql; if [[ $? -ne 0 ]] ; then echo $tbl >> $dumpdest/$db.tbl.bad ; sleep 5 ; rm -f $dumpdest/tbldumps/$db/$db.$tbl.sql; fi; done; rm -f $dumpdest/$db.tbl.in; done

Last ditch attempt to grab the structure of any bad tables.

Will not retrieve data - like truncating tables.

echo 'starting table structure panic' | tee -a $dumpdest/export.log
find $dumpdest -mindepth 1 -maxdepth 1 -type f -name "*.tbl.bad"|awk '{orig=$0; gsub(".tbl.bad$", ".tbl.in", $0); print "mv", orig, $0}'|bash
find $dumpdest -mindepth 1 -maxdepth 1 -type f -name "*.tbl.in"|awk -F'/' '{gsub(".tbl.in", "", $NF); print $NF;}'|while read db; do cat $dumpdest/$db.tbl.in|while read tbl; do echo 'dumping' $db $tbl | tee -a $dumpdest/export.log; mysqldump --single-transaction --triggers --routines --events --no-data $db $tbl 2>>$dumpdest/export.log >$dumpdest/tbldumps/$db/$db.$tbl.create ; if [[ $? -ne 0 ]] ; then echo $tbl >> $dumpdest/$db.tbl.bad ; sleep 5 ; rm -f $dumpdest/tbldumps/$db/$db.$tbl.create; fi; done; rm -f $dumpdest/$db.tbl.in; done

Config Settings

Verify the following settings right now.

Create Datadir

The instructions for this are actually in another page.

Reimport Dumps

Reimport databases.

find $dumpdest/dbdumps -maxdepth 1 -type f -name "*.sql"|awk -F'[/.]' '{print $(NF-1)}'|while read db; do echo Importing $db; echo "CREATE DATABASE IF NOT EXISTS \`${db}\`;"|mysql; mysql $db < $dumpdest/dbdumps/$db.sql; done

Reimport tables.

find $dumpdest/tbldumps -maxdepth 1 -mindepth 1 -type d|xargs -L1 basename|awk '{print "CREATE DATABASE IF NOT EXISTS", $0,";"}'|mysql
find $dumpdest/tbldumps -maxdepth 2 -mindepth 2 -type f -name "*.sql"|xargs -L1 basename|tr '.' ' '|while read db tbl junk; do mysql $db < $dumpdest/tbldumps/$db/$db.$tbl.sql; done

Recreate missing tables.

find $dumpdest/tbldumps -maxdepth 2 -mindepth 2 -type f -name "*.create"|xargs -L1 basename|tr '.' ' '|while read db tbl junk; do mysql $db < $dumpdest/tbldumps/$db/$db.$tbl.sql; done

Reimport your original grants:

mysql mysql < $dumpdest/mysql.sql

If there are any gaps, correct that.

Clean up

If a cPanel server, run the following:

/usr/local/cpanel/libexec/tailwatchd --enable=Cpanel::TailWatch::ChkServd
\ls /var/cpanel/users/|xargs -L1 -I{} /usr/local/cpanel/bin/restoregrants --cpuser={} --db=mysql --all
mysql -e 'flush privileges;'

On all servers, notify the customer the location of:

The customer may review these items as they please. These locations should be removed to free up space.

Set the ticket to pending, turn off autoclose, and note that the locations have not been cleaned up yet.