Skip to main content Logo (IEC resistor symbol)logo

Quis custodiet ipsos custodes?
Home | About | All pages | RSS Feed | Gopher

MySQL restore after a crash and disk issues

Published: 10-10-2016 | Author: Remy van Elst | Text only version of this article

Table of Contents

Recently I had to restore a MySQL server. The hardware had issues with thestorage and required some FSCK's, disk replacements and a lot of RAID and LVMlove to get working again. Which was the easy part. MySQL was a bit harder tofix. This post describes the proces I used to get MySQL working again with arecent backup. In this case it was a replicated setup so the client had noactual downtime.

If you like this article, consider sponsoring me by trying out a Digital OceanVPS. With this link you'll get $100 credit for 60 days). (referral link)

The proces requires a backup, preferably a recent one. What I did was removeeverything and start fresh, restoring the backups.

MySQL was not starting and the hostname.err log file had stack trace errorslike in this post. The server uses InnoDB, so no MyISAM recovery. Althoughmy experience with that is not very good as well. But still, error messageslike:

Attempting backtrace. You can use the following information to find outwhere mysqld died. If you see no messages after this, something wentterribly wrong...

Are extra funny.

First move the current MySQL folder away somewhere safe:

mv /var/lib/mysql{,.bak-$(date +%s)}

Recreate the folder and make sure the correct permissions are set up:

mkdir -p /var/lib/mysqlchown mysql:mysql /var/lib/mysql

Now restore your backup so that you have the dumps available somewhere. In mycase that's in /var/restore.10219/ and the files are compressed with gzip.They all have an .sql.gz extension and their filename is the name of thedatabase. The below steps do require that format.

Start up MySQL and make sure we can login without a password:

mysqld_safe --skip-grant-tables &

Restore the MySQL system database first. This database contains data MySQLrequires to function. For example, the grants (users and permisions), storedprocedures, logging, time zone information, replication information and othermiscellaneous system tables.

gunzip /var/restore.10219/mysql.sql.gzcd /var/restore.10219/# Note that I always chuckle when typing this command. mysql mysql < mysql.sql

Since the grants are now reset, you need to (re)set a root password. Or, anyother administrative user (da_admin for DirectAdmin):

mysql -e 'use mysql; update user set password=PASSWORD("password") where User="root";'

Stop the MySQL server:

killall mysqld # SIGTERM, not SIGKILL

Start it up via the system init script:

service mysqld restart # (or systemctl if that floats your boat)

Check if the service starts up normally now. It should, if not then your backupprobably is corrupt as well. You might need to recreate all users by hand.

Move the mysql database backup out of the restore folder:

mv /var/restore.10219/mysql.sql /root/mysql.sql.bak.$(date +%s)

Unpack all the databases in the restore folder:

gunzip /var/restore.10219/*.gz

The below script takes all .sql files from the restore folder and does thefollowing:

It doesn't take into account stuff like existing datbases, they are justoverwritten. You can copy and paste it in the shell, but saving it to a .shfile and running that is better:

for backupfile in /var/restore.10219/*.sql; do   FULLFILE="$(basename $backupfile)";  DBNAME="${FULLFILE%.*}";   echo "Started restoring ${DBNAME} from ${backupfile}";   mysql -e "create database ${DBNAME}";   mysql "${DBNAME}" < "${backupfile}";   sleep 5;   echo "Finished restoring ${DBNAME} from ${backupfile}";done

Depending on the size of the database it could take a while to restore all thebackups.

Afterwards, you might also want to check the replication if you had any setup.The master/slave settings should be restored via the mysql database, but thelog position might be off or there might be duplicates or other errors.

Just to be sure, check, repair and optimize all databases. If you had any errorswith the above script, you will get those here as well.

mysqlcheck -uroot -ppassword --auto-repair  --optimize --all-databases

In my case there were no databases that were corrupt in the backup and couldn'tbe restored in this case. I did have cases were I had one or more databases failbecause of corrupt backups. So make sure you do not only set up and monitor yourbackup process, also set a recurring event every month or so to do a testrestore, just to be sure.

Tags: backup, blog, database, duplicity, mariadb, mysql, ubuntu