Backing up your website is extremely important to the server security of your dedicated server, but you can’t forget about your server databases either! Your MySQL databases are the backbone of your website, especially if you are using it for database server hosting. In either situation, you cannot rely on manual backups of your dedicated server databases if you access them every day and if they are integral to your website’s success and uptime.
So below, we have included some helpful scripts that can be setup via cron jobs (scheduled tasks) on your server. These will automatically backup your databases for you, so you’ll never have to worry again! There are two options for these backups – you can either have them emailed to you or FTPed to you, both of which will assist with your dedicated web hosting experience.
MySQL backup via cron – Emailed to You
<?
$datestamp = date(“Y-m-d”); // Current date to append to filename of backup file in format of YYYY-MM-DD
/* CONFIGURE THE FOLLOWING SEVEN VARIABLES TO MATCH YOUR SETUP */
$dbuser = “”; // Database username
$dbpwd = “”; // Database password
$dbname = “”; // 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
$to = “you@remotesite.com”; // Email address to send dump file to
$from = “you@yourhost.com”; // Email address message will show as coming from.
$subject = “MySQL backup file”; // Subject of email
$command = “mysqldump -u $dbuser –password=$dbpwd $dbname | gzip > $filename”;
$result = passthru($command);
$attachmentname = array_pop(explode(“/”, $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);
unlink($filename); //delete the backup file from the server
?>
MySQL backup via cron – FTPed to You
<?
$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 = “”; // Database username
$dbpwd = “”; // Database password
$dbname = “”; // 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 = “”; // 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 = “anonymous”; // FTP account username
$ftp_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
?>
**MAKE ABSOLUTELY SURE that either script you use from above includes a .php extension. That file should have 755 permissions, and you MUST change the first variables to those specific to your website’s setup. Also, you MUST have a cron job setup in Cpanel and the path to the script needs to look like this: php /home/username/path-to-the-php-script. Just make sure you replace path-to-the-script with your script’s physical path. 
More Dedicated Web Hosting Help!