dijexi.com

phpMyBackupPro is a tool created using PHP programming language that can be used to perform backup and restore on MySQL database server. In this article we are going to learn how to install and configure this tool, perform manual backup and then perform restore the previously backed up database. We will also learn how to setup an automatic periodic backup using the same tool.

Installation

Installation of this tool is simple. Just download the newest version from http://www.phpmybackuppro.net/download.php.

After downloading, extract the zip file to a directory on your PHP and Apache web server accessible from browser, ie the Document Root, for example C:\XAMPP\HTDOCS. Then you will get a new folder under it, for example C:\XAMPP\HTDOCS\phpMyBackupPro.

That’s it! Now go to your web browser and point the URL of phpMyBackupPro, for example http://localhost/phpMyBackupPro.

Configuration

After successfully copying the files, and pointing your browser to the URL , you will get a page similar to the following:

As you can see, there are several tabs that we need to explore:

1. Start, the current page, showing the system information
2. Configuration, the place to do some system configuration
3. Import, to import recently backed up databases
4. Backup, to do backing up of databases
5. Scheduled backup, to create a script to do an automatic backup regularly
6. Database info, showing detailed database information
7. SQL query, to do arbitrary SQL command on the server

To configuring the system, click on the Configuration tab. You will see a page like the following:

On that page, you need to adjust the configuration according to your system. That will be:

1. Site name: the name of your site or application to backup
2. Language: english
3. SQL Data (mandatory settings)
   1. MySQL hostname: the host name or IP address of your MySQL server
   2. MySQL username: the user name to connect to your MySQL server
   3. MySQL password: the password for the user name to connect to your MySQL server
   4. only this database: enter the name of the database if you need to backup only that database, otherwise leave it blank so that phpMyBackupPro can backup all of databases on the server
4. FTP Settings (optional), set it up only if you want to save (upload) the backup file to an FTP server
   1. Save backup per FTP server ? check on it if you want to save (upload) the backup file to an FTP server
   2. FTP server, fill with the hostname or IP Address of the FTP server
   3. FTP username: the username to connect to the FTP server
   4. FTP password: the password of the username to connect to the FTP server
   5. FTP path: the location of the backup file on the FTP server
   6. User passive FTP: check it if you are using passive FTP server
   7. FTP Port: the port number of the FTP server, or leave it to 21 for the default number
   8. delete files on FTP server:
5. Backup per Email, set it up if you want to send the backup file to an email address
   1. use email: check it if you want to send the backup file to an email address
   2. email address: enter the email address to receive the backup file

Click on the Save button when you are done.

That’s all for the basic configuration. Your backup system can now be operated to do the backup and restore process from the same URL address. You may check also the Extended Configuration and the System Variables section.

Performing Manual Backup

After successfully configure your system, now you can do the backup process. Click on the Backup tab, then a page like the following will be shown:

You need to do the following steps to backup database(s):

1. Select the database(s) to backup, click on the databases list; to select more than one database, use CTRL-click (press CTRL button on your keyboard and then click your mouse). You may click “select all” to select all of your database listed.
2. double check the options to
   1. “export tables”, whether you want to backup the table definition or not
   2. “export data”, whether you want to backup the table data or not
   3. “add drop table”, whether you want to add the drop table statement or not, before the create table statement
   4. compression, select one of none, gzip, or zip
3. Select directories to backup to FTP server, select the directory on your FTP server (if you set it up before on the configuration step), or you can add manual FTP directories on the box right to the directory list. You may also check on the “Packed in One ZIP file”.
4. Click Backup button when you are done.

That’s all. You backup is now complete.

Performing Restore

To do the restore of a previously backed up database, click on the Import tab. A page similar to the following will show:

On that page you will see a list several files grouped by the database name corresponding to the backup process. On that list of files, there are some information like the file name, date time the backup is created, and the file size.

To restore a database, simply click “import” link on the file list that correspond to the database to be restored and the date the backup was performed.

There are other options that you can do:

The backup information, clicking the “info” link, will show the following information:

The SQL statement view, clicking the “view” link, will show the following information:

To download the backup file, click on the “download” link.

To delete the backup file, click on the “delete” link.

Performing Scheduled Backup

To do the backup periodically, go to the Scheduled Backup tab, you will see a page similar to the following:

You need to do the following steps to setup a periodic backup database(s):

1. Select the backup period: for example: each time the script is called, every 1 hour, every 6 hours, etc.
2. Select directory where the PHP script file generated will be placed. Leave it to ./ to place the file on the same directory to phpMyBackupPro directory. (You may need to click on “Update directory list”  to refresh the directory listing, in case you create a new directory)
3. Select the database(s) to backup, click on the databases list; to select more than one database, use CTRL-click (press CTRL button on your keyboard and then click your mouse). You may click “select all” to select all of your database listed.
4. double check the options to
   1. “export tables”, whether you want to backup the table definition or not
   2. “export data”, whether you want to backup the table data or not
   3. “add drop table”, whether you want to add the drop table statement or not, before the create table statement
   4. compression, select one of none, gzip, or zip
5. Select directories to backup to FTP server, select the directory on your FTP server (if you set it up before on the configuration step), or you can add manual FTP directories on the box right to the directory list. You may also check on the “Packed in One ZIP file”.
6. Click Show Script button when you are done.

The following page will appear:

The page shows a PHP script to be run to do the backup. Enter the file name for the script, for example script.php, then click Save data. The file will be created on the directory specified at the previuos step.

After it is generated, you can run the script by the following ways:

1. run it using cron tabs (on Linux), or using Task Scheduler (on Windows, see below)
2. run it using command line

That’s all. You backup script is now complete. To restore the backup, do the same way as restoring backup created by manual backup.

Setting up Windows Task Scheduler

Add New Scheduled Task

If you need to run the backup script periodically using the Windows Task Scheduler, do the following steps:

Click on Start – Settings – Control Panel – Scheduled Tasks

Double click Add Scheduled Task

Click Next.

Click Browse… and locate the PHP.EXE executable file, for example C:\XAMPP\PHP\PHP.EXE.

Select the execution period of Daily, Weekly, Monthly, etc depending on your backup need. Click Next.

Select the start time and Date of the task. Also select the daily operation for the task, eg Every Day, Weekdays only, or Every x days. Click Next.

Next, Enter the username and password that will run the program as. Click Next.

Refine the Scheduled Task

On the dialog, the task periodic schedule is shown, you can refine it by checking “Open advanced properties for this task when I click Finish”. Then click Finish.

You need to change the Run field from

c:\xampp\php\php.exe

to

C:\xampp\php\php.exe c:\xampp\htdocs\phpMyBackupPro\script.php db1,db2

where db1, db2, etc is the database name to backup.

You may need to put a double quote on the command line parameter if your script resides on a folder that contains spaces, for example:

C:\xampp\php\php.exe  “C:\Documents and Settings\Toshiba\My Documents\backup\script.php” db1,db2

This will run php program with an argument, which is our backup script created before.

If you need to adjust the period, you can click on the Schedule tab.

and then click Advanced button

On the above example, we check on Repeat task, which means we will run the backup program every 6 hours for 24 hours a day, starting from 29 June 2009. Click OK.

Our scheduled task will be listed at the Scheduled Tasks window as follow:

Modify the PHP Script

The final step is to modify the script.php so that the included path is correct. The original script.php file is for example as follow:

<?php
// This code was created by phpMyBackupPro v.2.1 
// http://www.phpMyBackupPro.net
$_POST['db']=array("cibpdb", "mysql", );
$_POST['tables']="on";
$_POST['data']="on";
$_POST['drop']="on";
$_POST['zip']="zip";
$period=(3600*24)/2;
$security_key="e24c0df313d1cbc9a1e7258811ba5342";
// This is the relative path to the phpMyBackupPro v.2.1 directory
@chdir("../backup/");
@include("backup.php");
?>

Next, modify line contains @chdir command to point to the correct absolute folder where the phpMyBackupPro folder (the file named backup.php) resides, for example:

<?php
// This code was created by phpMyBackupPro v.2.1 
// http://www.phpMyBackupPro.net
$_POST['db']=array("cibpdb", "mysql", );
$_POST['tables']="on";
$_POST['data']="on";
$_POST['drop']="on";
$_POST['zip']="zip";
$period=(3600*24)/2;
$security_key="e24c0df313d1cbc9a1e7258811ba5342";
// This is the relative path to the phpMyBackupPro v.2.1 directory
@chdir("C:/xampp/htdocs/phpMyBackupPro/");
@include("backup.php");
?>

Run the Scheduled Task

When you are done, just wait until the schedule for the task to run, or you can right-click on it and click Run to run it now.

Backup file created by this periodic backup task can be restored as usual like on the Restoring step above.