Raymii.org

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