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.

<?php
// 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).

This entry was originally posted on Wednesday, July 5th, 2006 at 10:32 pm and is filed under PHP, CSS, JavaScript tutorials. You can follow any responses to this entry through the RSS 2.0 feed.

40 Responses to “How to e-mail yourself an automatic backup of your MySQL database table with PHP”

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:
- Automatically or manually email yourself the DB, either gzipped or sql format
- Manage backups
- Optimize and Repair the DB
- Empty/Drop tables
- Run a SQL query
- Configure automated backup/email and optimization
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.
June 7th, 2010 at 7:12 pm

Zach says:

Here’s a version of the backup.php that just creates a compressed backup file in whatever folder it’s in. It is completely independent and doesn’t use any of the other files.

<?php
// Inspired by tutorials: http://www.phpfreaks.com/tutorials/130/6.php
// http://www.vbulletin.com/forum/archive/index.php/t-113143.html
// http://hudzilla.org

// Create the mysql backup file
// edit this section
ini_set("memory_limit","40M");

$dbhost = ""; // usually localhost
$dbuser = "";
$dbpass = "";
$dbname = "";
// don’t need to edit below this section

$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");

// Delete the unzipped file from your server
unlink($backupfile);

?>
June 18th, 2010 at 4:25 pm

CK says:

Nice tool, thanks a lot.
July 1st, 2010 at 12:07 am

Jegan says:

hi,
i got an warning message when i try to run it on my windows server. Also i got the mail only with out any attachments…

The warning message is
"PHP Warning: system() [function.system]: Unable to fork [mysqldump -h XXXXXXXXX -u XXXX -p XXXX fancy > fancy2010-07-01.sql] in G:\Domains\XXX\wwwroot\db\maildb.php on line 15 PHP Warning: unlink(fancy2010-07-01.sql) [function.unlink]: No such file or directory in G:\Domains\XXX\wwwroot\db\maildb.php on line 33"

Please help me in this its urgent….

Reply from Peter: Your host might not allow the “system” PHP function. See my answer below to “volt” for some alternatives.
July 1st, 2010 at 8:38 am

volt says:

My host disables access to the system function. Is there another way to make this work?

Reply from Peter: You could try a plugin such as this one or write some PHP and MySQL to loop through every table, instead of using the mysqldump command. Or you could look into a service such as VaultPress.
July 6th, 2010 at 12:10 am

agile says:

where are the mail.php and mail/mime.php files exits?

Reply from Peter: They’re from the PEAR Mail (http://pear.php.net/package/Mail/) and Mime (http://pear.php.net/package/Mail_Mime/download) packages.
July 21st, 2010 at 2:21 pm

freddo says:

thx…
October 28th, 2010 at 1:22 pm

David Ryder says:

works perfectly. well done and thanks! If this helps anybody the actual cron command I used (using cPanel on hostgator shared) is:

php /home/[my_account_name]/petermysqlbackup/backup.php
January 24th, 2011 at 11:22 am

dan-ll says:

try this to sort out the output file, just add

–skip-extended-insert

this will take a new line of each record in each table, hence not all in one unreadable and trucated line.

eg.

system("mysqldump –skip-extended-insert -h $dbhost -u $dbuser -p$dbpass $dbname > $backupfile");

if that don’t work
try

–skip-opt

eg

system("mysqldump –skip-opt -h $dbhost -u $dbuser -p$dbpass $dbname > $backupfile");
February 19th, 2011 at 7:30 pm

Greg says:

Is there some way to make this script export a .xls file instead of .sql?

Reply from Peter: You’d have to write your own code to transform the database information into an .xls structure, but there is likely public code somewhere.
March 4th, 2011 at 12:33 am

krlos says:

i write in my comand of cron jobs :
/ home / [my_account_name] / petermysqlbackup / backup.php

but the message I get is a file. sql is blank (0.0 kb)

Sorry for my english. I no speak english
March 4th, 2011 at 12:38 am

krlos says:

help me please!!!!

my .sql is blank for this error

Warning: mysqldump: Option ‘–set-variable’ is deprecated. Use –variable-name=value instead.
mysqldump: Got error: 1045: Access denied for user ‘odontolo_dental’@'localhost’ (using password: YES) when trying to connect
X-Powered-By: PHP/5.2.14
Content-type: text/html
May 16th, 2011 at 1:01 pm

sourav kumar kar says:

i want to automatically backup in 1 hours interval.. means in 1 hours interval backup & sent to main after then 1 Hours backup n send to mail.. plz help me….thanks to all the poster …

Reply from Peter: A cron job should do that. Check the links in the post for more information on cron jobs.
May 17th, 2011 at 4:07 pm

Stephen says:

Anyone who is getting a blank sql file as an attachment, you either have the username, password, or host incorrect. For example, I was using "localhost" for host which I discovered to be incorrect. I am using GoDadddy and they requires a very specific host name for my connection. I fixed the problem and the script started working.
July 13th, 2011 at 4:17 am

Henri says:

Works like the train toilet! Thanks, I use it with cron job and now it backups my and my customers db hourly.
July 27th, 2011 at 1:02 am

Brian says:

Just wanted to say I found you through a Google search via Noupe.com. Your script works like a charm and gave me enough of a head start to customize and update for my own purposes. I’m glad you called out the Pear Mime and Pear Mail dependencies. Fortunately for me, Hostgator has those installed by default.

I’ve got the script setup to backup a number of WordPress databases. The backups are e-mailed to a Backup account, and if everything is successful, I get an e-mail in my inbox telling me that the backup was performed successfully.

Thanks for sharing. I really appreciate your contribution, and it’s still valid 5 years hence.
October 18th, 2011 at 2:40 pm

Alejandro Arauz says:

I had some errors runing the scritpt due to insufficient privileges on the server but I found this tool MySQLBackupFTP (http://mysqlbackupftp.com). The free version allows you to send the compressed bakups to a remote FTP server and can also send notifications to your email.
The good thing about the tool is that unlike the script you can change the timeout settings for big databases.
November 16th, 2011 at 4:59 am

Thomas says:

Is there a way to exclude a specific table from the export. For example i have a table called ‘file’ which is about 95% of the db but doesn’t contain any data I would need backed up. Can we exclude this?

Reply from Peter: Yes, the mysqldump command supports: –ignore-table=db_name.tbl_name