Automated MySQL Backup Tool Based on PHP and Perl

MySQL is one of the most popular databases in the world. This database can be installed on Microsoft Windows or Linux platforms. Why is this database so popular? Because it has powerful features and is free. As a database administrator, Backup – database backup is really important to protect the system. It will minimize the risk if something happens to the database.

Since MySQL is a popular database, there are many software that we can use to backup Mysql automatically. From console software to web-based software. But in this article, I will guide you to use MySQLDumper to backup/restore MySQL automatically.

What is MySQLDumper?

MySQLDumper is an open source web-based tool for backing up MySQL databases. It is built from PHP and Perl and can easily dump and restore MySQL data. It is especially suitable for shared hosts, where we do not have access to the Linux shell.

Features of MySQLDumper

There are many features of MySQLDumper, but here are a few that might interest you.

• Easy to install; just make sure you have a working web server and point your browser to the MySQLDumper installation file.
• All parameters are displayed before starting backup; so you are sure what you are doing.
• Database-Overview; Allows viewing of running processes.
• SQL-Browser: Access your MySQL-Tables, delete tables, edit or insert data.
• Two ways of backing up, using PHP or Perl.
• Complete log files.
• Automatically delete files of your old backups.
• Create protected folder.

Install MySQLDumper in Linux

Installing MySQLDumper is very easy. First we need to download MySQLDumper here.

At the time of this writing, the latest version is 1.24. So download the latest version in your working web server directory (ie /var/www or /var/www/html). After downloading, you can extract MySQLDumper1.24.4.zip with the following command.

unzip MySQLDumper1.24.4.zip

Then you will find ‘msd1.24.4’ folder. This directory contains all the MySQLDumper files. Next step, you just need to point your browser to the MySQLDumper installation file ‘msd1.24.4/install.php’.

1. Select the installation language.

2. Fill in some login information like MySQL server name, user and password.

3. You can test the connection to the database by clicking the . button Connect to MySQL. If it succeeds, then we should see a message saying “Database connection was established“.

4. When you get the notification, click the ‘Save‘ and continue with the installation. You will be taken to the main screen.

How to use MySQLDumper

The main function of MySQLDumper is to backup your MySQL database. With this application, backing up (and restoring) MySQL databases is very easy.

Backup Process – Backup MySQL with PHP

The function menu is located on the left navigation panel. First, we need to select the database that we want to back up. You can see the options on the left menu.

In the above screenshot, I choose to backup the database named ‘employees‘.

Then select the ‘Backup’ menu on the left. Then select ‘Backup PHP’. We will have a screen like this.

Then click ‘Start New Backup‘. The progress of the backup will be shown to you.

After the backup is complete, we will see a message.

Backup procedure using Perl

Another backup method supported by MySQLDumper is ‘Backup Perl’. In this way, we will use Perl as a fallback tool.

Note: your web server must support ‘perl/CGI’ scripting before using this method. Otherwise you will get an error like this when you click the Test Perl button.

Similar to the PHP backup method, we need to select the database we want to backup. Then select the Backup menu from the left navigation panel. Then click the Backup Perl button.

MySQLDumper will show you some active parameters at the bottom. Then we can click the ‘Run the Perl Cron‘. Using this way we will not see any progress bar appear. The duration of this backup will depend on the database. If there are no errors, then we will see a message like this.

Process of Restore – Restore

Restoring a backup is also very easy using MySQLDumper. You can click on the menu ‘Restore‘ from the navigation panel on the left. All backups are available at the bottom of the restore page.

You can choose 1 backup from there. Above is the selected backup ready to restore. If you want to do a full restore, click the ‘Restore’ above. If you only want to restore some tables, click “Choose tables“.

Once done, click ‘Restore‘. Just wait a moment for the restore progress to complete.

Create Directory Protection

By default, MySQLDumper’s homepage can be accessed by anyone who knows its URL. Using Directory Protection, we can create a layer of password protection. This Directory Protection uses the ‘.htaccess’ function on the Apache web server.

To create it, just click the button Create directory protection on the main screen.

You will then be asked to provide some authentication information.

When you’re done with that, click the button Create directory protection. Then you need to confirm again.

If there are no errors, a success message will be displayed.

Next time you visit the site, MySQLDumper will ask you for the password first.

Manage backed up data

This menu is used to maintain all available backups and restores.

Here are some activities that can be done in this page.

• Delete the backup; use the Delete buttons at the top.
• Download backups; Click on the backup name.
• Select backup; Click on the Database name in All Backups.
• Upload a large backup to restore.
• Convert the database to MySQLDumper (MSD) format.

Note: When we convert database without using any compression technique, we will see MySQLDumper create database with name ‘part_1.sql’. The size is smaller than the original.

SQL-Browser

If you want to run a specific SQL command, you can do it in this SQL-Browser page. But it’s best to know what you’re doing.

Configuration

All of the above functions can be configured from the Configuration menu. Here are some sections that we can configure.

General

Interface

Autodelete

Email

FTP

Cronscript

Log Management

MySQLDumper also provides basic logs for you. So you can know when a backup-restore operation occurs. To access the log page, simply click on the ‘Log’ menu from the left navigation panel.

There are three types of logs: PHP-Log, Perl-Log and Perl-Complete Log.

Conclude

MySQLDumper may not be the best backup tool for MySQL. But with the ease of use of this app, everyone can start using it. However, I found that MySQLDumper is not equipped with offline documents. But it’s still a great tool for backing up MySQL databases.