Back Up a MySQL Database Using PHP and Cron Job

This is a tutorial for automatically doing MySQL database backups using PHP and cron job. This will be helpful for beginners who find it complex to do backups on a periodic basis.

This application will help you in the following ways:

  1. It will allow you to do backups automatically, instead of manually with a hosting control panel or phpMyAdmin.
  2. It will let you use your hosting cron feature to specify that backups run weekly, monthly, etc.
  3. It will place the MySQL database backup in a secure folder. This secure folder is not accessible to the public. The hosting cron will be the only one that can access this folder.
  4. It will place the cron PHP script that will do the backup in the secure folder. This will prevent public users from accidentally executing your PHP script with a browser.
  5. It will use SSH to safely download your MySQL database from your hosting server to your local computer.

Basic Requirements to Get Started

Not all hosting configurations can implement this PHP- MySQL database backup script. So make sure your system meets the following requirements:

You cannot implement this application using a free web hosting account, because these PHP functions will be disabled for security reasons.

If you have a paid hosting account, you need to log in to your hosting panel and look for php.ini file. If you have trouble finding this file, ask your hosting support.

This application is tested to run on PHP 5, but it may have problems with other versions.

The screen shot below shows PHP 5.2.5 being used in the hosting account, and a link “here” to the php.ini file.

To enable the function above, make sure that disable_functions are commented. This means that they are enabled.

If they are not commented, they are disabled. For example:

disable_functions = exec, passthru, system, popen, fsockopen, pfsockopen

The above code means that the functions named exec, passthru, system, popen, fsockopen and pfsockopen are disabled. To enable them:

;disable_functions = exec, passthru, system, popen, fsockopen, pfsockopen

Just put a semi-colon before disable_functions.

The PHP Script to Back Up a MySQL Database

<?php

/*
Define MySQL database connection parameters. This is the database that you would like to have a backup.
*/

$username = “MySQL database username”;
$password = “MySQL database password”;
$hostname = “MySQL database hostname”;
$database = “Name of your MySQL database”;

/*
Sanitized or escape those following variables. This uses the escapeshellcmd function and is done for security reasons. Do not remove these lines, particularly if you are accepting user inputs.
*/

$username =escapeshellcmd($username);
$password =escapeshellcmd($password);
$hostname =escapeshellcmd($hostname);
$database =escapeshellcmd($database);

/*
Define the full path to your restricted folder. This folder is where you will place the PHP-MySQL backup script, as well as the generated MySQL database backup by the cron.

Make sure to define this correctly. If this is not correct, it will not produce a backup. To make sure you have the correct, full path to your backup restricted folder, follow the steps below.

Step 1. Create a folder in your website root directory named backuprestricted. Actually, you can name it anything you like.

Step 2. Set the file permission of the created folder to 755.

Step 3. Upload this PHP script (name it fullserverpath.php) inside this folder.

<?php
echo $_SERVER['SCRIPT_FILENAME'];
?>

Step 4. Now, using a web browser, open the PHP script. Paste the URL into the browser address bar.

http://www.example.com/backuprestricted/fullserverpath.php

The result looks somewhat like this: /home/www/example.com/backuprestricted/fullserverpath.php

Therefore the full path to the backup folder is this: /home/www/example.com/backuprestricted/

Use it to define $backupFile variable below:
*/

$backupFile=’/home/www/example.com/backuprestricted/’.date(“Y-m-d-H-i-s”).$database.’.sql';

/*
Also you have noticed that the backup file variable above is concatenated with date, database name and the sql extension. The purpose is to generate a timestamp, which is appended to the backup file. In this way, you will know when the backup was created.
*/

/*
Formulate the MySQL dump command. This command will do the actual work of exporting the MySQL database and place the generated SQL file into the backup path you have specified.
*/

$command = “mysqldump -u$username -p$password -h$hostname $database > $backupFile”;

/*
Finally, execute the system function. This function will execute the command. It takes two arguments, the $command and the $result. The $result will be useful for troubleshooting the output of the system function. For example, if the error code returned to the browser is 127, this means that the file path to the backup might be wrong or incorrect, and PHP cannot find it.
*/

system($command, $result);
echo $result;
?>

Below is the final PHP script you’ll use to back up a MySQL database (without comments):

<?php
$username = “MySQL database username”;
$password = “MySQL database password”;
$hostname = “MySQL database hostname”;
$database = “Name of your MySQL database”;
$username =escapeshellcmd($username);
$password =escapeshellcmd($password);
$hostname =escapeshellcmd($hostname);
$database =escapeshellcmd($database);
$backupFile=’/home/www/example.com/backuprestricted/’.date(“Y-m-d-H-i-s”).$database.’.sql';
$command = “mysqldump -u$username -p$password -h$hostname $database > $backupFile”;
system($command, $result);
echo $result;
?>

{mospagebreak title=Implementation Tips}

This section will discuss the implementation details of the previous script, and how you can use it to automatically back up your MySQL database using PHP and Cron Job.

Step 1. Download the complete script as discussed in this tutorial: http://www.php-developer.org/php-script-to-backup-mysql-database-using-hosting-cron/

Step 2. Right click on the zip file and click “Extract here.” After unzipping the package, you will see a folder named “backuprestricted,” which contains backupmysql.php and .htaccess.

Step 3. Open backupmysql.php and assign appropriate MySQL database connection parameters (username, password, hostname, database name).

Step 4. In backupmysql.php, specify the path to the backup folder. Refer to the guide in the previous section.

Step 5. Save backupmysql.php and upload the folder to your website root directory. Use SSH if possible, because it is more secure.

Step 6. Using an SSH client, set the file permission of the following files and folders:

  • .htaccess = 644
  • backupmysql.php = 755
  • backuprestricted folder = 755

This is what your files will look like after changing permissions:

Step 7. It is now the time to configure your cron. You will need to consult your hosting provider for detailed cron instructions.

The screen shot below applies to Agilityhoster.com, as well as other hosting providers with similar cron features:

The command is simply a full server path to the PHP script. And the cron is configured to run every first day of the month. Do not forget to enabled email reporting and specify your email address.

If you are using Go Daddy hosting, the cron command will be:

/web/cgi-bin/php5 “$HOME/html/backuprestricted/backupmysql.php”

Of course, it already assumes you are using PHP 5.

Troubleshooting Tips

The best data gathering tool for troubleshooting this application is to enable reporting of cron by email. This means that after cron processing, a report is generated and sent to the webmaster’s email address.

Common errors:

Publicly accessible backup folder. To check, open your URL using a web browser; it should give a 403 forbidden error.

Or use this tool: http://gsitecrawler.com/tools/Server-Status.aspx
Enter the address of your PHP script http://www.example.com/backuprestricted/backupmysql.php 

It should give a 403 forbidden response, or 404 in some servers. If it returns 200 OK status, make sure your .htaccess uses this line:

deny from all

Important: Do not remove the .htaccess

No output from the cron. You need to check the cron report sent to your email. If the report tells you  something like “permission denied,” then you need to set the folders and script to use a file permission of 755.

If it uses an error code of 127, then the path to your backup script is not correct. Make sure you are using the correct server path.

Normally, you will receive this email content if the script has been successfully executed by the cron (no error):

Content-type: text/html
0

As a troubleshooting tip, you can set the cron first to operate every hour or even every 15 minutes (if your hosting cron allows) so that you can see if the MySQL database backup really works.

Once it is fully working, set a realistic time frame, such as monthly, for your backups.

You can even write a script to delete old MySQL backup files in your backup folder, again using cron. Do not forget to download important MySQL database backup files to your Desktop.

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan