MySql Database Backup Script

Using the script below single/multiple MySql database can be backed up and emailed to designated email id. If required this file can be executed periodically through CRON Job for monthly / daily backup of database(s). In the script I have shown script for backup of two database. You can edit this file as per your need.

<?php

$to = "yourmailid@xyz.com";
// Address to wihch mail is to be sent,
// can comma separate many mail ids.

date_default_timezone_set('Asia/Calcutta');
// you can set your timezone or you can delete this line also

$file_path = "http://yoursite.com/your-folder/"
// place where you will keep this file, don't forget trailing slash

echo "<h1>Backing up databases</h1><ol>";

backup('MukeshDak.Com', 'muk090356', 'ItisSecret12', 'muk090356', 'localhost');
// backing up first database
backup('Dak.Me', 'dakme033', 'mypass23', 'mydbname', 'dak.345.db.hostedresources.com');
// backing up second database

// here you can write backup(); with following argument in correct sequence.
// AnyName --> for easy database identification as xyz.com
// database user --> username for access to database
// database password
// database name
// database server --> Normally this is localhost

// You can add as many line as many database you want to backup

// ========== CONFIGURABLE SECTION ENDS HERE ==================
// ========== YOU NEED NOT EDIT ANYTHING BELOW THIS LINE ========

function backup($sitename, $db_user, $db_pass, $db_name, $db_server)
    {
	$subject = "Backup of ".$sitename." at ".date('Y-m-d-H-i-s'); // subject of mail
	$headers = 	"From: Dak Database Backup Script 1.0<".$to.">\r\n";

        $bound_text = "Bound Text";
	$bound = 	"--".$bound_text."\r\n";
	$bound_last = 	"--".$bound_text."--\r\n";

	$date_stamp         = date('Y-m-d-H-i-s');
	$backup_filename = ($db_name == '' ? 'all_databases' : $db_name) . '_' . $date_stamp . '.sql.gz';

	$cmd = 'mysqldump -u ' . $db_user . ' -h ' . $db_server . ' --password=' . $db_pass . ' ' . ($db_name == '' ? '--all-databases' : $db_name) . ' | gzip > ' . $backup_filename;
	$dump_status = (passthru($cmd) === false) ? 'No' : 'Yes';

	$headers .= 	"MIME-Version: 1.0\r\n"
		."Content-Type: multipart/mixed; boundary=\"$bound_text\"";

	$message .= 	"If you can see this MIME than your client doesn't accept MIME types!\r\n"
		.$bound;

	$message .= 	"Content-Type: text/html; charset=\"iso-8859-1\"\r\n"
		."Content-Transfer-Encoding: 7bit\r\n\r\n"
		."Thank you for using dak database script from "
                ."<A HREF='http://dak.me/mysql-database-backup-script/'>http://dak.me</A>"
                ." by <A HREF='http://mukeshdak.com'>Mukesh Dak</A>\r\n"
		.$bound;

		$file = file_get_contents($file_path.$backup_filename);

		$message .= 	"Content-Type: application/x-zip-compressed; name=$backup_filename\"\r\n"
			."Content-Transfer-Encoding: base64\r\n"
			."Content-disposition: attachment; file=$backup_filename\r\n"
			."\r\n"
			.chunk_split(base64_encode($file))
			.$bound_last;

	if($dump_status="Yes")
		{
			if(mail($to, $subject, $message, $headers))
			{
				echo "<li>Backup of database ".$db_name." of ".$sitename." has been sent to ".$to."</li>";
				unlink($backup_filename);
			} else {
				echo 'MAIL FAILED';
				}
			}
			else{
				echo "<p>There is some problem in taking dump of your database";
			}
	}  // function backup ends here
	echo "</ol><h2>Database backup job completed</h2>";
?>

Tagged as: backup, database, email, MySql, php, script