Automatic MySQL daily backup with cron job

iBorderliner.net provides these few simple steps to encourage our clients to create an automatic MySQL database daily backup. This solution works without user intervention and works outside the Cpanel.

How does it work?
This backup will email the gzip file with the time you set.

Follow these steps below:

Create a directory

1) In your home directory create a new directory

/home/username/backup_mysql

where backup_mysql is the new directory name.

NOTE: this is outside your public_html directory.

2) Chmod the directory created in step 1 to 766.

Grant Db permissions

3) Go cPanel > MySQLDatabase.

4) Create a new user - password.

5) Click Add User.

6) Indicate LOCK and SELECT permissions for the user created in step 4.

7) Click Add User to Db.

Configure the source

8) Below are the variables you MUST adjust in backup_mysql.php.

$dbuser = 'username_backupaccountname';
$dbpass = 'yourpassword';
$dbname = 'username_dbnametobackup';
$savepath = "/home/username/backup_mysql";
$to = "ausername[at]yourdomain.com";
$from = "anotherusername[at]yourdomain.com";

These are variables that you adjust to suit your needs.

$subject = "Full backup of $dbname completed - $senddate";
$message = "See attached file for mysqldump of $dbname";

These last 3 variables are all up to you. "yes" or "no"

$use_gzip = "yes";
$remove_sql_file = "yes";
$remove_gzip_file = "yes";

Upload the source

9) Save changes to backup_mysql.php

10) Upload the backup_mysql.php file to the directory created in step 2.

11) Chmod the file to 644.

Schedule a cron job

12) Go cPanel > Chron Jobs > Advanced ( Unix Style )

13) Enter an email address for the chron output ( in case there are errors )

14) Decide when / how often you want the chron job to run in the example below it runs 1x a day at midnight

0 0 * * *

15) Enter the command - in our example - it invokes the backup_mysql.php - and sends output to a log file in the same directory

php /home/username/backup_mysql/backup_mysql.php > /home/username/backup_mysql/backup_mysql.log

16) Click Commit changes

CONGRATULATIONS! You made it this far.
Below is the php code to run this backup (Copy the code and save it as backup_mysql.php)

Code:

<?php

$dbhost = 'localhost';
$dbuser = 'username_backupaccountname';
$dbpass = 'yourpassword';
$dbname = 'username_dbnametobackup';
$savepath = "/home/username/backup_mysql";

$send_email = "yes";
$to  = "ausername[at]yourdomain.com";
$from = "anotherusername[at]yourdomain.com";
$senddate = date("j F Y");
$subject = "Full backup of $dbname completed - $senddate";
$message = "See attached zip file for mysqldump of $dbname";

$use_gzip = "yes";
$remove_sql_file = "yes";
$remove_gzip_file = "yes";

// Do not Modify below this line!

$date = date("mdy-hia");
$filename = "$savepath/$dbname-$date.sql";
passthru("mysqldump --opt -h$dbhost -u$dbuser -p$dbpass $dbname >$filename");

if($use_gzip=="yes"){
    $zipline = "tar -czf ".$dbname."-".$date."_sql.tar.gz $dbname-$date.sql";
    shell_exec($zipline);
}
if($remove_sql_file=="yes"){
    exec("rm -r -f $filename");
}

if($use_gzip=="yes"){
    $filename2 = "$savepath/".$dbname."-".$date."_sql.tar.gz";
} else {
    $filename2 = "$savepath/$dbname-$date.sql";
}


if($send_email == "yes" ){

    $fileatt_type = filetype($filename2);
    $fileatt_name = "".$dbname."-".$date."_sql.tar.gz";

    $headers = "From: $from";

    // Read the file to be attached ('rb' = read binary)
    $file = fopen($filename2,'rb');
    $data = fread($file,filesize($filename2));
    fclose($file);

    // Generate a boundary string
    $semi_rand = md5(time());
    $mime_boundary = "==Multipart_Boundary_x{$semi_rand}x";

    // Add the headers for a file attachment
    $headers .= "\nMIME-Version: 1.0\n" ."Content-Type: multipart/mixed;\n" ." boundary=\"{$mime_boundary}\"";

    // Add a multipart boundary above the plain message
    $message = "This is a multi-part message in MIME format.\n\n" ."--{$mime_boundary}\n" ."Content-Type: text/plain; charset=\"iso-8859-1\"\n" ."Content-Transfer-Encoding: 7bit\n\n" .
    $message . "\n\n";

    // Base64 encode the file data
    $data = chunk_split(base64_encode($data));

    // Add file attachment to the message
    $message .= "--{$mime_boundary}\n" ."Content-Type: {$fileatt_type};\n" ." name=\"{$fileatt_name}\"\n" ."Content-Disposition: attachment;\n" ." filename=\"{$fileatt_name}\"\n" ."Content-Transfer-Encoding: base64\n\n" .
        $data . "\n\n" ."--{$mime_boundary}--\n";

    // Send the message
    $ok = mail($to, $subject, $message, $headers);
    if ($ok) {
        echo "<h4><center>Database backup created and sent! File name $filename2</center></h4>";
    } else {
        echo "<h4><center>Mail could not be sent. Sorry!</center></h4>";
    }
}


if($remove_gzip_file=="yes"){
    exec("rm -r -f $filename2");
}

?>

DONE! You should receive the first email containing your mysql database in gzip file on midnight.

Questions?

Question: Do I have to write the chron output to a log file?
Answer: No, if you like you can send output to > /dev/null

Question: What if I want the backups to live on the server after the chron job runs?
Answer: Just set $remove_sql_file = "no" and/or $remove_gzip_file = "no"

Question: My db is too big for email!
Answer: Just set $send_email = "no"; and leave the backups in the directory by setting $remove_sql_file / $remove_gzip_file to "no". Or get that FTP bit working.