Home arrow MySQL arrow Back Up a MySQL Database Using PHP and Cron Job

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.

TABLE OF CONTENTS:
  1. Back Up a MySQL Database Using PHP and Cron Job
  2. Implementation Tips
By: Codex-M
Rating: starstarstarstarstar / 4
November 30, 2010

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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;
?>



 
 
>>> More MySQL Articles          >>> More By Codex-M
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: