Automated MySQL database backup restoration

 

Introduction:

Mainly we use mysqldump to take backups of our MySQL database servers. Although mysqldump is very reliable but we need to make sure that backup should be restorable when it really matters. So there should be some backup verification process. As per this process we need to download latest database mysqldump backups from our MySQL database servers and restored it to some test server regularly after some days of interval. This activity is used to verify the integrity and reliability of the backups that are getting generated. Previously this activity was manual and it was taking human working hours to monitor and account/report the restoration activity. My task was to automate this activity and generate report of this activity for later analysis.

 

 

Platform:

We use MySQL 5.5 for our database engine on Ubuntu 12.04 64bit edition.

 

 

Technologies/Tools Used:

MySQL 5.5, PHP 5.3 scp, bzip2, shell scripting on Ubuntu 12.04

 

 

 

The Task:

It consists of two components:

1) A Shell script and a PHP script to do the restoration and statistics generation

2) A small reporting PHP-MySQL web application to generate reports from the gathered statistics.

Restoration and statistics generation

A normal OS user (stgsync) is created in all the database servers and in the backup testing server. The shell script will use this user to connect to a database server to download the latest backup using Linux’s builtin scp tool.

After the backup file is downloaded, it is decompressed.

Before restoring the decompressed MySQL backup, MySQL database server in the Backup Testing Server will be cleaned up (removing all the existing data and log files).

MySQL server is cleaned up and restarted after that the decompressed mysqldump backup file is restored.

If restoration is successful, a PHP script will be called to generate statistics from the restored databases, these statistics are added to a centralized MySQL report database.

Restoration log will be mailed to the concerned people.

Backup Restoration Report Generation

When we open the home page of the report application, we can see when the last restoration took place for a particular MySQL database server.

In the above screenshot we can see last restoration information for the four configured database servers:

1) Restoration of the backup for the server IA6-MKCLSUPPORT-AS-01-P took place on 2013-08-12 and the name of the backup file is all_db_2013-08-12_04-00.bz2

2) Restoration of the backup for the server NMS-MKCLOS-DB-01-P took place on 2013-08-06 and the name of the backup file is all_db_2013-08-06_02-00.bz2

3) Restoration of the backup for the server NMS-PORTALS-AS-01-P took place on 2013-08-08 and the name of the backup file is all_db_2013-08-08_02-00.bz2

4) Restoration of the backup for the server SEW-SETS-DB-01-S took place on 2013-08-12 and the name of the backup file is all_db_2013-08-12_02-00.bz2

We can see the details of the latest backup restoration for a server, click on View Details

We can see the statistics of the restoration like how many table are restored, how many views are restored, how many procedures are restored etc.

Now to get more information about different restored items, say I need to check which tables are restored for a particular database, click on the number of tables restored.

I can get the table names and number of rows restored. Here the tables with 0 rows restored are shown in dark color.

To find which views are restored, click on the number of views for a database

Same way we can view the functions, procedures and events restored for a particular table.

Also we can get the detailed information by clicking View Details link

Now say we want to compare backup restorations reports for a particular database server, so that we are sure that backups are happening properly (by checking number of objects restored, number of tables and rows restored).

For that we have to find out when the previous backup restorations took place for a particular period

Select the database server name and select the backup restoration period (selecting a Start date and End date) and click on Go button.

For example we will find out when backup restoration took place for the server IA6-MKCLSUPPORT-AS-01-P from 1^st of August 2013 and 13^th of August 2013. After searching we could see that backup restoration took place 5 times.

Say we want to compare backup restoration that took place on 13-08-2013 and 12-08-2013. Click on View Details links for both backup restoration records. Here we can see how many different objects are restored for each database.

Now say we want to compare table restoration for the database redmine_ajitj, click on the number of tables restored for both restoration reports.

So we can see the number of rows restored for each table and now we can compare J. In this example we can see the latest backup restored lesser number of rows than the previous backup. There may be some issue or may be the application owner had cleaned up some data, so it needs some attention.

Also if required I can generate Table-Row report (restoration report of tables and number of rows for a particular server, database or table). For this type of report click on Table-Row Report link.

Here we can generate report as per our requirement. I will show you three possibilities.

1) We want Table and number of rows restoration report for a particular database server.
e.g. We will generate report for the server NMS-MKCLOS-DB-01-P
Select the Database server name, in Select Database Name list, leave it as --All Databases-- and in Select Table Name list keep --All-Tables-- . Select the report period by selecting Start Date and End Date.

Click on Go button to generate the report

2) We want to generate report for a particular database within a database server
e.g. We will generate report for the survey database in server NMS-MKCLOS-DB-01-P.
Select the Database server name NMS-MKCLOS-DB-01-P, in Select Database Name list select survey and in Select Table Name list select --All-Tables--. Select the report period by selecting Start Date and End Date.

Click on Go button to generate the report

3) In last report type, we can get restoration report for a particular table within a database.
e.g. We will generate report for the table survey_answer of the database survey in server NMS-MKCLOS-DB-01-P
Select the Database server name NMS-MKCLOS-DB-01-P, in Select Database Name list select survey and in Select Table Name list select survey_answer . Select the report period by selecting Start Date and End Date.

Click on Go button to generate the report

 

Conclusion:

This automation will save lots of human resource hour which was previously being wasted in database backup restoration, verification and reporting tasks. Also this will help us to find errors/issues in database backups and to find out inconsistent backups.