Simple PHP MySQL backup script for CPanel. Create a php file and insert the following code:


<?
$datestamp = date("Y-m-d");// Current date to append to filename of backup file in format of YYYY-MM-DD

/* CONFIGURE THE FOLLOWING THREE VARIABLES TO MATCH YOUR SETUP */
$dbuser = "username";// Database username
$dbpwd = "password";// Database password
$dbname = "database";// Database name. Use --all-databases if you have more than one
$filename= "backup-$datestamp.sql.gz";// The name (and optionally path) of the dump file

$command = "mysqldump -u $dbuser --password=$dbpwd $dbname | gzip > $filename";
$result = passthru($command);

/* CONFIGURE THE FOLLOWING FOUR VARIABLES TO MATCH YOUR FTP SETUP */
$ftp_server = "hostname";// Shouldn't have any trailing slashes and shouldn't be prefixed with ftp://
$ftp_port = "21";// FTP port - blank defaults to port 21
$ftp_username = "username";// FTP account username
$ftp_password = "password";// FTP account password - blank for anonymous

// set up basic connection
$ftp_conn = ftp_connect($ftp_server);

// Turn PASV mode on or off
ftp_pasv($ftp_conn, false);

// login with username and password
$login_result = ftp_login($ftp_conn, $ftp_username, $ftp_password);

// check connection
if ((!$ftp_conn) || (!$login_result))
{
echo "FTP connection has failed.";
echo "Attempted to connect to $ftp_server for user $ftp_username";
exit;
}
else
{
echo "Connected to $ftp_server, for user $ftp_username";
}

// upload the file
$upload = ftp_put($ftp_conn, $filename, $filename, FTP_BINARY);

// check upload status
if (!$upload)
{
echo "FTP upload has failed.";
}
else
{
echo "Uploaded $filename to $ftp_server.";
}

// close the FTP stream
ftp_close($ftp_conn);

unlink($filename);   //delete the backup file from the server
?>

Now add a cron job to trigger the backup script when required:


1 1 * * * php -q /home/user/path_to_phpscript/sqlbackup.php

The above code will trigger the backup to run at 1:01 a every day of the year.

Use a cron calculator to sepcify the time and days you would like your cron to run: http://www.csgnetwork.com/crongen.html

Written by Matt Cooper
Hi, I'm Matt Cooper. I started this blog to pretty much act as a brain dump area for things I learn from day to day. You can contact me at: matt@linuxtutorial.co.uk.