How to Schedule MySQL Backups
From JumbaWiki
This guide will show you how to schedule MySQL backups and have them sent to you via e-mail. This can be used to send a copy of your database daily.
Backup script
- Create a password protected directory called backup or whatever you think is appropriate.
- Create a file called cron.php with the following script, changing the parts relevant to your account:
- Upload the modified script to the backups folder.
<?
$datestamp = date("Y-m-d"); // Current date to append to filename of backup file in format of YYYY-MM-DD
// CONFIGURE THE FOLLOWING LINES TO MATCH YOUR SETUP
//=================================================================
$dbname = "usr_dbname"; // Database name. Use --all-databases if you have more than one
$dbuser = "usr_dbusername"; // Database username
$dbpwd = "mydbpassword"; // Database password
$filename = "backup-$datestamp.sql.gz"; // The name (and optionally path) of the dump file
$to = "email@somewhere.com"; // Email address to send dump file to
$from = "email@somewhere.com"; // Email address message will show as coming from
$subject = "MYUSERNAME database backup - $datestamp"; // Subject of email
// one or both of the following must be true:
$sendEmail = true;
$keepFile = false;
//=================================================================
// Do not change anything below here
$command = "mysqldump -u $dbuser --password=\"$dbpwd\" $dbname | gzip > $filename";
$result = passthru($command);
if ($sendEmail)
{
$attachmentname = basename($filename); // If a path was included, strip it out for the attachment name
$message = "Compressed database backup file $attachmentname attached.";
$mime_boundary = "<<<:" . md5(time());
$data = chunk_split(base64_encode(implode("", file($filename))));
$headers = "From: $from\r\n";
$headers .= "MIME-Version: 1.0\r\n";
$headers .= "Content-type: multipart/mixed;\r\n";
$headers .= " boundary=\"$mime_boundary\"\r\n";
$content = "This is a multi-part message in MIME format.\r\n\r\n";
$content.= "--$mime_boundary\r\n";
$content.= "Content-Type: text/plain; charset=\"iso-8859-1\"\r\n";
$content.= "Content-Transfer-Encoding: 7bit\r\n\r\n";
$content.= $message."\r\n";
$content.= "--$mime_boundary\r\n";
$content.= "Content-Disposition: attachment;\r\n";
$content.= "Content-Type: Application/Octet-Stream; name=\"$attachmentname\"\r\n";
$content.= "Content-Transfer-Encoding: base64\r\n\r\n";
$content.= $data."\r\n";
$content.= "--$mime_boundary\r\n";
mail($to, $subject, $content, $headers);
}
if (!$keepFile) unlink($filename); //delete the backup file from the server
?>
Cron job
Access the cron jobs option in cPanel. The cron job command is written as:
php -q /home/username/public_html/backups/cron.php
where username is your Jumba Hosting username and the backups directory is directory just created where the backup files are located.
If you don't want to receive the email result, set $sendEmail=false; in the code, or add >/dev/null 2>&1 to the command:
php -q /home/username/public_html/backups/cron.php >/dev/null 2>&1
