You are welcome to look around. You will have to register before you can post a message, create a blog, chat live with our members, or add a site to our directory.
I did not write this tutorial. I found it very useful and am re-posting it for your convenience. Original: Database Backup via Cron
This tutorial allows you to backup your MySQL database, and then send it to you either via email or via FTP. You will need to set up a cron for the files below, and specify a time/date (IE Mondays at 4pm) and the file will run a backup at the time/day you specify.
SQL Backup - Email To You
Code:
<?
$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
?>
SQL Backup - FTP'ed To You
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 = ""; // 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
?>
You need to fill in all the variables and then save the script as .php and upload it to your site via FTP. You need to give it 755 permissions.
Then in cron add this code for the cron job:
Code:
php -q /home/username/public_html/folder/name.php
You need to edit this so it points to where you uploaded the script.
This script and variations of it have been around for several years. I found that you will get a blank SQL file if the permissions are not set right for the database user.
Try this. You need the following permissions: SELECT, RELOAD, FILE, SHOW DATABASES, SUPER, LOCK TABLES, SHOW VIEW
It is a good idea to set up a separate DB user for backups. These are not standard settings.
One of the basic security rules for DB user permissions is that you never grant anything more than the minimal requirements to any DB user.
There may be other reasons for a blank file, but this is what fixed my backups when I first encountered the problem.
Another problem you might run into is with e-mailing large databases. Some ISPs place limits on e-mail file sizes. The --all-databases option may not work if the databases are large. I've also seen problem trying to e-mail WordPress backups if you have the wassup plugin installed and you do not set time limits on the data it retains. By default, wassup saves all the history and the tables can become very large very quickly.
Database Backup (to Email and FTP) via Cron
I'm the kind of guy who thinks that a con job is a cron job with the R silent 
Linear Mode
