Backing up data stored in a MySQL database is an important issue for anyone running a blog, wiki, or any Web-based application that relies on the popular database engine. Many MySQL management solutions allow you to export database data as an SQL query, but if you are looking for a dedicated MySQL backup tool, phpMyBackupPro (pMBP) is your best bet.
Installing pMBP is as easy as unpacking the downloaded tarball and uploading the resulting directory to your server. For better security, the pMBP documentation recommends moving the global_conf.php file to another directory on your server. If you choose to do this, you have to update the path to global_conf.php in the definitions.php file. Open the file in a text editor and locate the line:
Replace the default value with the actual relative path to the global_conf.php file; for example:
Make the global_conf.php file and the export directory writable by setting their permissions to 777, and pMBP is ready to go. Point your browser to http://yourserver/phpmybackuppro/config.php and fill out the required fields in the SQL data section. Once you've provided your MySQL user name and password, you have to use them the next time you access pMBP.
When performing backup, pMBP saves the database data as an SQL file in the export directory. For off-site backup, pMBP can also upload backups to a remote FTP server. To enable this feature, tick the Save backups per FTP check box and fill out the necessary fields. In a similar manner, you can enable the ability to email backup files to a specified email address. Tick the Use email check box and enter the desired email address in the Email address field.
To see whether your configuration works properly, switch to the Backup section, make sure that the Export tables, Export data, and Add 'drop table' check boxes are ticked, and press the Backup button. You should see the message that backup has been performed successfully. Switch to the Import section, and you'll see a new entry in the list of backup files. You can use the commands available here to view the backup file, download it to your hard disk, and delete it. More importantly, you can use the Import command to restore the database.
pMBP also supports scheduled backups, which is a boon for users and administrators who don't want to be bothered with backing up databases manually. Setting up a scheduled backup consists of two steps: you have to generate a backup script using pMBP, and then set up a trigger that kicks off the created backup script. The first part is straightforward. Switch to the Schedule backups section and select the desired time interval from the Backup period drop-down list. Specify then the location where the generated backup file will be saved, and press the Show script button. Give the generated script a name and press the Save data button to save the script in the specified location.
Next, pMBP's manual offers several ways to set up a trigger. You can, for example, embed the generated backup script into another php file, or you can use the following code to call the backup script from another PHP file (e.g. index.php):
Just remember to replace the ../phpmybackuppro/export/scheduled_backup.php part with the actual path to the saved backup script.
pMBP can also handle backup of multiple databases running on different MySQL servers. To enable this feature, copy the global_conf_sql.php file into the same directory where you have the global_conf.php file. Open the definitions.php file and locate the line:
Replace the default value with the relative path to the global_conf_sql.php file; for example:
Make the global_conf_sql.php writable, open it in a text editor, and enter the required MySQL information.
pMBP is by far the easiest and most elegant backup solution for MySQL. If you are running a MySQL-based application on your server, pMBP is a must-have tool that can help you to protect your precious data with minimum effort.