Raymii.org
Quis custodiet ipsos custodes?Home | About | All pages | Cluster Status | RSS Feed
MySQL restore after a crash and disk issues
Published: 10-10-2016 | Author: Remy van Elst | Text only version of this article
❗ This post is over seven years old. It may no longer be up to date. Opinions may have changed.
Recently I had to restore a MySQL server. The hardware had issues with the storage and required some FSCK's, disk replacements and a lot of RAID and LVM love to get working again. Which was the easy part. MySQL was a bit harder to fix. This post describes the proces I used to get MySQL working again with a recent backup. In this case it was a replicated setup so the client had no actual downtime.
Recently I removed all Google Ads from this site due to their invasive tracking, as well as Google Analytics. Please, if you found this content useful, consider a small donation using any of the options below:
I'm developing an open source monitoring app called Leaf Node Monitoring, for windows, linux & android. Go check it out!
Consider sponsoring me on Github. It means the world to me if you show your appreciation and you'll help pay the server costs.
You can also sponsor me by getting a Digital Ocean VPS. With this referral link you'll get $200 credit for 60 days. Spend $25 after your credit expires and I'll get $25!
The proces requires a backup, preferably a recent one. What I did was remove everything and start fresh, restoring the backups.
MySQL was not starting and the hostname.err
log file had stack trace errors
like in this post. The server uses InnoDB, so no MyISAM recovery. Although
my experience with that is not very good as well. But still, error messages
like:
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly 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/mysql
chown mysql:mysql /var/lib/mysql
Now restore your backup so that you have the dumps available somewhere. In my
case 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 the
database. 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 MySQL requires to function. For example, the grants (users and permisions), stored procedures, logging, time zone information, replication information and other miscellaneous system tables.
gunzip /var/restore.10219/mysql.sql.gz
cd /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, any
other 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 backup probably 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 the
following:
- Creates a database with the name of the file (without the
.sql
extension) - Restores the dump to that database
It doesn't take into account stuff like existing datbases, they are just
overwritten. You can copy and paste it in the shell, but saving it to a .sh
file 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 the backups.
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 the
log 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 errors with 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't be restored in this case. I did have cases were I had one or more databases fail because of corrupt backups. So make sure you do not only set up and monitor your backup process, also set a recurring event every month or so to do a test restore, just to be sure.
Tags: backup , blog , database , duplicity , mariadb , mysql , ubuntu