How to e-mail yourself an automatic backup of your MySQL database table with PHP 
This script will send an e-mail to you with an .sql file attached, thus enabling you to back up specific tables easily. If you have a database-driven site, your mysql info is what is most valuable to you! You could even set up an e-mail account just to receive these backups…
First of all, this script works best if you place it in a non-web accessible folder and run a daily cron job on it. Cron is a server “tool” that can run scripts regularly or at specified times (thus you don’t need to call them in your browser). I used to be scared of cron but it’s really easy. Here are two tutorials if you are interested Cron Tutorial 1 … Cron Tutorial 2. If you have cPanel on your server it’s even easier (see here).
The code is below, and requires the Pear Mime and Pear Mail packages. If you want to download everything at once, I have provided a zip file at the end of this post.
Known limitations of this script: if you have a big database table over 2mb, you will probably run into php timeouts and php mail attachment limits. Check this comment for information about compressing the backup file before sending or storing it.
<?
// Create the mysql backup file
// edit this section
$dbhost = "yourhost"; // usually localhost
$dbuser = "yourusername";
$dbpass = "yourpassword";
$dbname = "yourdb";
$sendto = "Webmaster <webmaster@yourdomain.com>";
$sendfrom = "Automated Backup <backup@yourdomain.com>";
$sendsubject = "Daily Mysql Backup";
$bodyofemail = "Here is the daily backup.";
// don't need to edit below this section
$backupfile = $dbname . date("Y-m-d") . '.sql';
system("mysqldump -h $dbhost -u $dbuser -p$dbpass $dbname > $backupfile");
// Mail the file
include('Mail.php');
include('Mail/mime.php');
$message = new Mail_mime();
$text = "$bodyofemail";
$message->setTXTBody($text);
$message->AddAttachment($backupfile);
$body = $message->get();
$extraheaders = array("From"=>"$sendfrom", "Subject"=>"$sendsubject");
$headers = $message->headers($extraheaders);
$mail = Mail::factory("mail");
$mail->send("$sendto", $headers, $body);
// Delete the file from your server
unlink($backupfile);
?>
Download Peter’s automatic mysql backup tool (in the zip file, backup.php is the file you need to execute).
keung.biz. Hire my web consulting services at 
March 25th, 2008 at 1:13 am
Omid says:
Does it work with big databases?
March 25th, 2008 at 8:30 am
Peter says:
Hi Omid, I think it depends on your server setup. So far this blog's database is at 3.5mb and the automatic backup seems to be working. However, I have not tested it with bigger databases.
September 5th, 2008 at 3:17 am
newmorning says:
I've had some hard work to add Pear and Mime : you may add them to your archive for those who can't access to server configuration.
What's more, I was scared that data wasn't droped : actually the tables come into one single line per table ! (coma separated values).
I tryed to import dump just as it was on a local server : no problem at all, this is great, many thanks !
September 5th, 2008 at 10:52 am
Peter says:
Hi newmorning, I'm glad it helped you! I should note that I have since added one more step to my own backup process — I compress the file before storing and e-mailing it. This cuts the size of the DB backup by almost 75%. I replaced the two lines below "don't need to edit below this section" with this:
$backupfile = $dbname . date("Y-m-d") . '.sql';
$backupzip = $backupfile . '.tar.gz';
system("mysqldump -h $dbhost -u $dbuser -p$dbpass $dbname > $backupfile");
system("tar -czvf $backupzip $backupfile");
You would also need to tweak the line that adds the attachment:
$message->AddAttachment($backupzip);
Then, at the bottom you have to also delete the compressed file as well:
unlink($backupzip);
September 5th, 2008 at 11:49 am
newmorning says:
Well, I've just tried this improvement, and I'm afraid my configuration does not permit it : the backup comes just as before with the whole SQL, which is quite big !
Any idea why this should not work ?
September 5th, 2008 at 12:01 pm
Peter says:
Since the "mysqldump" command works, then it's probably not an issue with PHP being allowed to run shell commands. It's either an issue of "tar" not being available (or that the full path to the tar utility needs to be entered). That, or, I'd forgotten that you need to also modify the part where it adds the attachment to use the compressed file
$message->AddAttachment($backupzip);
September 5th, 2008 at 12:12 pm
newmorning says:
YES ! That was it, brilliant
Anything similar to mail the whole folder and subfolders in a compress archive ? It could be helpful since some CMS, forums and wikis use" flat" FTP text files instead of MySQL database…
September 5th, 2008 at 12:25 pm
Peter says:
Erm, that is certainly possible — you'll just have to look up the syntax and usage instructions for the "tar" command. You could also look into SVN if you want to have version control for larger amounts of files. Or, Dropbox if you're adventurous and have full access to your server.
September 5th, 2008 at 1:00 pm
newmorning says:
trouble is precisely that I don't have full access : this is why your code is precious, trouhg webcron.org to execute it every day.
I wish I'd find the equivalent for files since I don't know enough of php to do it myself.
September 26th, 2008 at 5:16 am
Cuong says:
Dear Peter,
Thank you in advance for that great script.
I have got one trouble, when I run backup.php. The out put error like this:
——————–
Warning: require_once(pear.php) [function.require-once]: failed to open stream: No such file or directory in /home2/cuong/public_html/absql/Mail.php on line 21
Fatal error: require_once() [function.require]: Failed opening required 'pear.php' (include_path='.:/usr/lib/php:/usr/local/lib/php') in /home2/cuong/public_html/absql/Mail.php on line 21
———-
I opened Mail.php and I saw:
———-
require_once 'PEAR.php';
———-
Does it mean that my host doesnot support "PEAR"
Sorry I am newbie.
September 26th, 2008 at 2:06 pm
Peter says:
Hi Cuong, I'm not completely sure how to solve that. It has to do with your system not having PEAR installed or it is not where it should be. I'll have to defer to others (maybe "newmorning" knows what to do) for more precise instructions.
September 27th, 2008 at 1:43 am
newmorning says:
Install Pear in your backup folder OR trie this : http://www.commentcamarche.net/forum/affich-8578881-php-un-mail-qui-veut-pas-venir
September 29th, 2008 at 2:32 am
Cuong says:
@Peter
@newmorning
Thank you very much for responding. Maybe I will try to install pear.
Have a nice day.
January 3rd, 2009 at 9:26 am
J says:
mysqlhotcopy executes faster :>
February 25th, 2009 at 8:58 am
William says:
Juste, clair, net, précis, fonctionnel et utile.
Que dire de plus?
Ah oui!! Mille merci
William
March 15th, 2009 at 5:33 pm
Matt says:
I've got everything working, as far as I can tell, but all the attachments sent to the email are only 1kb/empty. Any suggestions as to what I might be doing wrong?
Thanks!
Reply from Peter: Unfortunately, I don’t see an obvious solution. I would break it down by first making sure the backup SQL file is being generated, then test file compression if you are using that, then test e-mails on their own, then test e-mails with small attachments, then put it all together.
March 16th, 2009 at 4:41 am
Basti says:
Hi,
thanks for that script…
I only get Emails with an attached file called "noname" without extension.
This file is not the mysql-database, it seems to contain it, because its size is similar to my database.
This is the beginning of my file
boundary="=_b93bc378af2ddbeabe770c611c3fb955"
Message-Id: <20090316122421.8138E1B48131@dd14802.xxxxx.de>
Date: Mon, 16 Mar 2009 13:24:21 +0100 (CET)
–=_b93bc378af2ddbeabe770c611c3fb955
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="ISO-8859-1"
Here is the daily backup.
–=_b93bc378af2ddbeabe770c611c3fb955
Content-Transfer-Encoding: base64
Content-Type: application/octet-stream;
name="xxxxeeb12dd009-03-16.sql";
Content-Disposition: attachment;
filename="xxxxxx.sql";
LS0gTXlTUUwgZHVtcCAxMC4xMQotLQotLSBIb3N0OiBsb2NhbGhvc3QgICAgRGF0YWJhc2U6IGQw
Thanks for help.
Reply from Peter: Could be some sort of mail encoding issue, but I am no expert here.
March 16th, 2009 at 7:44 am
DanoNH says:
Hi folks, Good comments and such…
After reading through this page, I wanted to share a great WordPress plugin that I use: WP-DBManager.
This plugin lets you:
While it doesn’t have the granular abilities of phpMyAdmin, it is a clean, easy to use interface.
Just wanted to share with my fellow WordPress administrators out there.
-Dan
May 22nd, 2009 at 1:50 pm
Joel Cornuz says:
Hi guys,
I don't know if this could be of interest, but I modified the script so it takes an array of database(s) instead of just one. So you can backup several databases in one go (1 tar.gz file with several .sql ones). See code here.
November 12th, 2009 at 12:10 am
kaiz says:
well, we can change the email attachment size from 2mb to the size we want in the php.ini file. i think it is possible to do it.
Thanks.
December 26th, 2009 at 6:06 am
Simon says:
I had a problem with your compression method so I did a bit of scratching around and came up with the following method which is only a minor alteration to your original script:
// don't need to edit below this section
$backupfile = $dbname . date("Y-m-d") . '.sql.gz';
system("mysqldump -h $dbhost -u $dbuser -p$dbpass $dbname | gzip > $backupfile");
December 26th, 2009 at 9:09 am
Simon says:
I have struck a problem on another server. The first server worked beautifully. The second server returns an email with the attachment displayed as scrambled text in the body of the email.
Anybody had a similar problem? Is it a server mail config issue?
January 14th, 2010 at 5:48 am
Brian says:
It sends me a blank notepad attachment.
Any suggestions??
Reply from Peter: You might not have permission to run the command or you might have input the wrong database information. If you don’t have access to the command line and want to find out what specific error is being returned, see this comment.