Once databases are up and running and connected to whatever application uses them all is well, but what happens when disaster strikes? If you haven’t backed up those databases, a nightmare situation will ensue. To that end, you must have a solid backup plan in place. One tool that makes backing up MySQL databases a snap is MySQLDumper.
MySQLDumper is a web-based, PHP- and Perl-based tool. Databases can be dumped into a backup file and then easily restored. MySQLDumper is perfectly suited for shared hosting workspaces where shell access isn’t an option. MySQLDumper is released under the GNU Open Source License and is free to use. Let’s walk through the process of installing and using this handy tool.
The installation requirements are Apache, MySQL, and PHP. A basic Linux Apache MySQL PHP (LAMP) server should easily meet all of these requirements. Once you meet the requirements, follow the steps for installation, which will be a breeze if you’ve installed tools like phpMyAdmin.
Step 1: Download and extract the MySQLDumper file
Download the latest release and move the file into your server’s document root . I am installing on a Ubuntu 12.04 machine, so the file will be placed in /var/www. With the file in its proper place, extract it with the following steps:
- Open a terminal window.
- Issue the command sudo unzip MySQLDumperXXX.zip (XXX is the release number).
- Rename the newly created file with the command sudo mv msdXXX msd (XXX is the release number).
Step 2: Create permissions and directories
There are certain sub-folders that have to be created and permissions re-assigned. Here are the commands to complete this step from within the /var/www/msd directory:
- sudo mkdir work
- sudo chmod ugo+w work
- sudo mkdir work/config
- sudo chmod ugo+w work/config
- sudo mkdir work/log
- sudo chmod ugo+w work/log
- sudo mkdir work/backup
- sudo chmod ugo+w work/backup/
- sudo chmod ugo+w config.php
Step 3: Create a database
Yes, a tool that is used to back up databases requires a database. To make this step easy, I prefer to take advantage of the MySQL Workbench tool. Create a database called “msd” using the UTF default collation.
Step 4: Install MySQLDumper
It’s time to fire up your browser and point it to http://ADDRESS_OF_SERVER/msd and start the installation. The first screen will require you to select the language for the installation. Then, click the Install button. The next screen you should see is the database information screen (Figure A).
At the bottom of the screen, you can see MySQLDumper detected the databases on my machine. (Click the image to enlarge.)
Fill out the necessary information and click the Connect To MySQL button. If the connection is successful, you will be taken to one of two screens. If you have already taken care of the directories and permissions issues, you’ll immediately be taken to the MySQLDumper main window (Figure B). If you haven’t taken care of those issues, you will land on a page instructing you on how to complete the installation by creating the necessary directories and permissions.
There is still one installation step left. (Click the image to enlarge.)
From the main window, you will notice a warning labled Urgently Recommended. You need to protect the MySQLDumper directory with a password. Do this by clicking the Create Directory Protection button. This will open a new screen (Figure C) that allows you to set up the password protection.
You have different encryption options to choose from here. (Click the image to enlarge.)
Here’s how to back up a database using MySQLDumper:
- Select the database to be backed up from the Database drop-down.
- Click Backup from the left navigation.
- Enter a comment (if necessary) in the comment field (Figure D).
- Click Start New Backup.
If you check the box for Select Tables, you will be prompted to select which tables you’d like to back up. (Click the image to enlarge.)
When the backup completes, you will be presented with a link that allows you to download the database (Figure E).
You can also opt to edit the database or get an overview of the database. (Click the image to enlarge.)
Click the Continue button to move to the database overview screen. This screen (Figure F) displays all of the current backups available on the MySQLDumper system.
From this screen you can upload a database to the system. (Click the image to enlarge.)
Restoring is just as simple:
- Select the database you want to restore from the Database drop-down in the left navigation.
- Click Restore from the left navigation.
- Select the backup you wish to restore (Figure G).
- Click the Restore button.
You can select specific tables to restore if you don’t need to restore the entire database. (Click the image to enlarge.)
By regularly employing MySQLDumper, you will have a solid set of backups to ensure your databases are always functional.