Arrow

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

First published on July 5, 2006

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 1Cron 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).

Arrow

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


  1. Omid says:

    Does it work with big databases?


  2. 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.


  3. 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 !


  4. 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);


  5. 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 ?


  6. 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 :P

    $message->AddAttachment($backupzip);


  7. 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…


  8. 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.


  9. 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.


  10. 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.


  11. 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.


  12. 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


  13. Cuong says:

    @Peter
    @newmorning

    Thank you very much for responding. Maybe I will try to install pear.

    Have a nice day.


  14. J says:

    mysqlhotcopy executes faster :>


  15. William says:

    Juste, clair, net, précis, fonctionnel et utile.
    Que dire de plus?
    Ah oui!! Mille merci
    William


  16. 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.


  17. 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.


  18. 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


  19. 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.


  20. 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.


  21. 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");


  22. 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?


  23. 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.


  24. 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);

    ?>


  25. CK says:

    Nice tool, thanks a lot.


  26. 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.


  27. 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.


  28. 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.


  29. freddo says:

    thx…


  30. 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


  31. 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");


  32. 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.


  33. 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


  34. 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


  35. 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.


  36. 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.


  37. Henri says:

    Works like the train toilet! Thanks, I use it with cron job and now it backups my and my customers db hourly.


  38. 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.


  39. 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.


  40. 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


  41. Jesean says:

    This works perfectly but cant we make this more elegant :-) …. ?
    can’t we store the database on the server it self and protect the compressed file with a password and then email the URL to the File + Password to the user :-)

    further more , we could add a login system and create a list view thingy to view the backups if needed :-)
    Thanks :-)


  42. Johnny says:

    if you have big sql file just follow this comment
    http://www.theblog.ca/mysql-email-backup#comment-9819
    then edit php.ini
    max post, max upload to number larger then your sql file. and memory limit as high as possible (mine 2000MB)

    if you have problem with cront job , you can read this comment:
    http://www.theblog.ca/mysql-email-backup#comment-14676


  43. Mike S says:

    This script works great, and very easy to use. Works great as is. Thanks. One thing though, I have a database with 7 tables in it, and I only want to back up 1 table with a cron job. How can I modify the script not to send me all 7 tables, but 1 table and in a csv file? I think I have the csv part licked; just having trouble isolating the single table.

    I see the comment above for ignore table command, but I want to ignore multiple tables, and just export the one table in the db. I can’t seem to get it to work that way. Any ideas or help is super appreciated!


  44. Bahadur says:

    Hi Sir,
    i want to email mysql database to a rediffmail id.
    My questions are
    1. Can i use your script for it?
    2. Where should i place your script? should i create a button in a page or anytyhing else?

    Reply from Peter: Yes, you should be able to use any e-mail provider. Typically the script is run via a cronjob or manually on the command line.


  45. LT says:

    For those of you getting a blank email i.e. no text in the body or no attachment. Make sure your db password is not longer that 12 characters AND does not contain any variables such as a dollar sign.

    Substitute an easy password and see if this corrects the problem. If it does, experiment with harder passwords until you get what you need.

    I have done this and it works. Now I get an email with no body and an attachment that is the database. The attachment is labeled "noname" and has no file extension.

    If anyone knows how to correct this do tell.


  46. Paul says:

    Nice post. php / sql dump commands helpful. thanks for sharing!


  47. RainS says:

    Nothing happened. Missing Pear.php


  48. Kelson says:

    Used this and after some finagling I got it to work – IE – I get an approval message that nothing failed, but my email comes through with no attachment. When I take off the mail send function, the mysqldump function still doesn’t work. Using IIS, not apache, could this be the issue?


  49. raghava says:

    Please give me solution for this errors.

    1) Deprecated: Assigning the return value of new by reference is deprecated in C:\wamp\www\payrolv1\example\backup mail\Mail.php on line 134

    2) Warning: require_once(PEAR.php): failed to open stream: No such file or directory in C:\wamp\www\payrolv1\example\backup mail\Mail.php on line 21.

    3) Fatal error: require_once(): Failed opening required ‘PEAR.php’ (include_path=’.;C:\php\pear’) in C:\wamp\www\payrolv1\example\backup mail\Mail.php on line 21


  50. Sunny says:

    I Installed the script and everything seems to be working fine except the attached file. Its attaches a blank file with 0Bytes. Has anyone else encountered the same problem? What kind of permission do you all allow for the DB USER?


  51. Paul Sijpkes says:

    Hi and thanks for this great script.

    I just found a small detail that might save some headaches for other users. If you’ve got a super-secure password with weird characters in it then you’ll need to put it in quotes before calling the system() command. Here’s my updated code:

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


  52. lokilust says:

    Can you add a part that says email has been sent ?


  53. Brad says:

    What is the format for to have the sql sent to two email addresses?
    $sendto = "Webmaster <xxxxx@xxxxl.com>";
    somthing like: $sendto = "Webmaster <xxxxx@xxxxxl.com>,<xxxxx@xxxxxxxx.com>";

    Reply from Peter: You should be able to separate the recipients by commas or use an array. For more information, check out the Pear Mail documentation


  54. rodrigo says:

    THE DUMP FILE STORES IN ANY DIRECTORY OF WINDOWS??? HOW CAN I FIND IT??

Speak your mind

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Click to hear an audio file of the anti-spam word