How to Backup SQL Website Databases with cPanel and cron via FTP or Email

How to Backup SQL Website Databases with cPanel and cron via FTP or Email https://ift.tt/eA8V8J

Are you using cPanel with MySQL and need to do automatic backups one your website databases? You can set up a cron to backup your MySQL database several ways.

Please note that all scripts for cron should end with a .php extension, and the file should have 755 permissions. You would also need to change the first few variables to those of your site setup.

When setting up the cron job it should look like this:

php -q /home/userpath/name.php

Where /home/userpath/name.php is that path of the backup script you created.

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: $fromrn”;
$headers .= “MIME-Version: 1.0rn”;
$headers .= “Content-type: multipart/mixed;rn”;
$headers .= ” boundary=””.$mime_boundary.””rn”;

$content = “This is a multi-part message in MIME format.rnrn”;
$content.= “–“.$mime_boundary.”rn”;
$content.= “Content-Type: text/plain; charset=”iso-8859-1″rn”;
$content.= “Content-Transfer-Encoding: 7bitrnrn”;
$content.= $message.”rn”;
$content.= “–“.$mime_boundary.”rn”;
$content.= “Content-Disposition: attachment;rn”;
$content.= “Content-Type: Application/Octet-Stream; name=”$attachmentname”rn”;
$content.= “Content-Transfer-Encoding: base64rnrn”;
$content.= $data.”rn”;
$content.= “–” . $mime_boundary . “rn”;

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