How to: Backup MySQL database & email results using PHP
Update Aug 29: You can also try an updated version of this script that uses Amazon S3 instead of email.
There are a bunch of scripts on the Internet that overly complicate the issue of automatically backing up your database. Here is a PHP script I wrote to backup a mysql database and email the results. Just set a cron job (/etc/crontab) to run this script with the following command: php /…./dbBackUp.php
Code:
< ?php
/*
Quickly and easily backup your MySQL database and have the tgz emailed to you. You need PEAR installed with the Mail and Mail_Mime packages installed. Read more about PEAR here: http://pear.php.net. This will work in any *nix enviornment. Make sure you have write access to your /tmp directory.
*/
require_once('Mail.php');
require_once('Mail/mime.php');
// mysql & minor details..
$tmpDir = "/tmp/";
$user = "root";
$password = "pass";
$dbName = "db";
$prefix = "db_";
// email settings...
$to = "[email protected]";
$from = "[email protected]";
$subject = "db - backup";
$sqlFile = $tmpDir.$prefix.date('Y_m_d').".sql";
$attachment = $tmpDir.$prefix.date('Y_m_d').".tgz";
$creatBackup = "mysqldump -u ".$user." --password=".$password." ".$dbName." > ".$sqlFile;
$createZip = "tar cvzf $attachment $sqlFile";
exec($creatBackup);
exec($createZip);
$headers = array('From' => $from, 'Subject' => $subject);
$textMessage = $attachment;
$htmlMessage = "";
$mime = new Mail_Mime("\n");
$mime->setTxtBody($textMessage);
$mime->setHtmlBody($htmlMessage);
$mime->addAttachment($attachment, 'text/plain');
$body = $mime->get();
$hdrs = $mime->headers($headers);
$mail = &Mail::factory('mail');
$mail->send($to, $hdrs, $body);
unlink($sqlFile);
unlink($attachment);
?>
To download the script click here.
Have a second? Check out this great Canadian Health & Living Store based in Toronto
February 17th, 2006 at 8:39 am
Nice script. It inspired me to see if I could implement it purely in abash script.
February 17th, 2006 at 10:50 am
The next thing to add is to encrypt it with a public GPG key before emailing it. If you need to recover your backup, download it from gmail, unencrypt it with your private key, and go recover. I use this
shell_exec("C:\GnuPG\gpg.exe -qe -o $workingdirectory$filename.gpg -r $key $workingdirectory$filename");
February 17th, 2006 at 8:49 pm
@ Oscar: That makes perfect sense. I saw your script, looks good. Using PHP would let you backup on demand from a browser, but for better security your way is better.
@ David: Definitely, I should do that.
February 22nd, 2006 at 3:06 pm
what if you wanted to just run it right in the browser and download the resulting tar file?
February 22nd, 2006 at 3:53 pm
@ jack
Thats no problem, instead of writing the tar file to /tmp just write it to a directory of your webserver. Then have the script output a URL to download the tar file.
You’ll have to write/execute a second script after you download the tar file, so that you unlink (delete) the original.
—————————————————————————–
// mysql & minor details..
$tmpDir = “/var/www/abcdefg.com”;
$user = “root”;
$password = “pass”;
$dbName = “db”;
$prefix = “db_”;
$sqlFile = $tmpDir.$prefix.date(‘Y_m_d’).”.sql”;
$attachment = $tmpDir.$prefix.date(‘Y_m_d’).”.tgz”;
$creatBackup = “mysqldump -u “.$user.” –password=”.$password.” “.$dbName.” > “.$sqlFile;
$createZip = “tar cvzf $attachment $sqlFile”;
exec($creatBackup);
exec($createZip);
unlink($sqlFile);
// now just output a link to download the file
echo “< html>…..”;
—————————————————————————
Good luck,
George
March 1st, 2006 at 4:59 am
where is mail.php and mime.php
March 1st, 2006 at 9:32 am
@george: thanks for the feedback. Also, if you want to download the resulting file, I think you could use readfile() to serve the file directly, after setting the http headers for download. The php documentation is great, as there are many browser specific gotchas. Once readfile is done, you should be able to unlink the file.
March 2nd, 2006 at 8:51 pm
@Gurjeet: You need to install or download PEAR. Its included in PHP (not installed though), just check http://pear.php.net on how to install it.
@Oscar: Nice tip and no problem
March 9th, 2006 at 1:21 am
I’ve installed the script and get the following error!
Warning: exec() has been disabled for security reasons in /home/sdwtuvpv/public_html/dbBackUp.php on line 42
Warning: exec() has been disabled for security reasons in /home/sdwtuvpv/public_html/dbBackUp.php on line 43
Warning: unlink(/tmp/db_2006_03_09.sql): No such file or directory in /home/sdwtuvpv/public_html/dbBackUp.php on line 58
Warning: unlink(/tmp/db_2006_03_09.tgz): No such file or directory in /home/sdwtuvpv/public_html/dbBackUp.php on line 59
what should I do?
March 10th, 2006 at 9:09 am
@ ken
Sorry buddy, your out of luck. Your host has disabled exec. You’ll have to find another method to backup (phpMyAdmin?) or get another host.
July 4th, 2006 at 12:26 am
hi there,
Thanks for your lean and mean script! I had some trouble with the tmp directory causing the script to send me an empty file. After I created a tmp directory in the folder the script is in (and chmoded it 777) and changed the tmp directory in the script from /tmp to /physicalserverpath/blabla/tmp (where physicalserverpath is the physical server path to your html document base and blabla the directory where the script is in) it worked fine!
September 22nd, 2006 at 6:44 pm
Hi, i can’t install and use the PEAR PHP Extension on my webhosting.. so i want to know if there is another solution to replace the Mail.php and the Mail/Mime.php function with another kind of php script.
Thanks for your script and support
September 22nd, 2006 at 8:41 pm
@ charliepro
PEAR is just PHP files, all you really need to do is copy the files over to your to your webserver that you need.
Set up PHP & PEAR the way you would normally on your computer — then just copy the files to a folder you in your webserver directory.
Good luck,
George
September 24th, 2006 at 11:27 pm
Hi again, thanks for the feedback.. i have installed the pear extension into my shared webhosting provided by GoDaddy, and GoDaddy have changed the pear.conf file of my installation directory so GoDaddy doesn’t allow me to install and use Pear PHP extensions, so please, what can i do to replace the PEAR extensions with another php script to send the backup to my personal email?
September 25th, 2006 at 1:11 pm
@ charliepro
Your missing the point buddy, in your PHP directory there is the PEAR directory. This PEAR directory has a bunch of PHP scripts — thats all pear really is. Transfer via FTP this PEAR directory (as is) to your webserver’s home directroy. So above in the script, in the ‘require’, you’ll have:
require_once('PEAR/Mail.php');
require_once('PEAR/Mail/mime.php');
........
......
There is no need to re-install PEAR, just FTP the folder to your webserver.
Hope this helps,
George
September 26th, 2006 at 4:28 pm
@ George
Hi and thanks a lot for the feedback!!!! I really appreciate it! Now PEAR it worked ok, but i’m still have a trouble!!
When i run the script it appears to me this message:
Warning: unlink(/physicalserverpath/example/tmp/db_26-09-2006.tgz): No such file or directory in
I had CHMOD 777 the “tmp directory”, i put the same /physicalserverpath/example/tmp/ in the $tmpDir and also i can modify the php.ini file at my GoDaddy webhosting(but really i don’t know what i can modify here to fix my trouble)
So when i run the script it send to me an empty email (Robert Buzink wrote after the same thing, only that he could fix the trouble, modifying the physicalserverpath in the script but for me doesn’t work at all)
Thanks Again!
September 28th, 2006 at 11:39 am
@ charlie
Hopefully you figured out your problem, if not email me papayiya gmail.
I sent you an email.
George
December 2nd, 2006 at 12:02 am
Excellent script! Extremely convnient!
Thanks for sharing. :-)
December 3rd, 2006 at 9:56 am
Hi,
for some of you who dont have exec enabled or want to go that way, this guy has a cool solution,
http://www.googlepayments.co.in/index.php/topic,180.0.htm
its a long script to copy paste, so just am giving the link!
Really wrks cool, ….did slight modifications to the naming convention of the file, but rest wrks like a charm,
recommended
Rahul.
February 2nd, 2007 at 10:09 am
it works good but when i import backup files by phpmyadmin an error occurs.my page encoding is utf8 but i have problem with this item either.please help me resolve this problems.thanks.
this error #1065 – Query was empty
March 8th, 2007 at 1:08 pm
i just tried it , with UTF-8,..wrks good,
let me know which line os creating issues
Bob.
May 23rd, 2007 at 2:22 pm
Great script, although I’m having problems with it sending an attachment with no name. It creates the file on the server ok, then it attaches something to the email (I can tell that it has attached the correct file by the filesize that come up in my mail program) – but it has no name or extension and when opened it is all garbage. I have used the script exactly as it is above, except for the variables that needed changing. Any ideas why this would be? I’ve tried it in a few mail programs, so I don’t think that it’s specific to that.
August 3rd, 2007 at 2:27 pm
terrific idea for a script.
I managed to create and send the backup. However, when testing the received attachment, I get the following error message:
lee@dellLaptop:~$ tar zxfv samsourcedb_2007_08_03.tgz
tar: This does not look like a tar archive
tar: Skipping to next header
tar: Error exit delayed from previous errors
not sure how to progress this….
thanks
November 13th, 2007 at 10:58 pm
Hi George
Great script. Just what I was looking for.
Only change I needed to make was specify the host name for my purposes (mysqldump defaults to localhost).
// Host Name
$dbHost = “myhostname”;
then
$creatBackup = “mysqldump –host=”.$dbHost.” -u “.$user.” –password=”.$password.” “.$dbName.” > “.$sqlFile;
May 15th, 2008 at 7:31 am
i have already write script $creatBackup = “mysqldump –host=”.$dbHost.” -u “.$user.” –password=”.$password.” “.$dbName.” > “.$sqlFile; so file is created in folder but the file was empty no such data in this file.
June 14th, 2008 at 3:38 pm
This is a nice script. I user this script on my Web Based Application. My client is happy to use this email backup databases.
Thanks
July 8th, 2008 at 6:31 am
Hi
Nice script but it won’t work with remote SQL servers. Any ideas?
August 2nd, 2008 at 5:07 pm
if you can’t use mysqldump try this
<?php
require_once “Mail.php”;
require_once(‘Mail/mime.php’);
/*************** Modify database settings ***************/
$Host = “MYSQL-HOST”;
$UserName = ‘MYSQL-USER’;
$Password = ‘MYSQL-PASSWORD’;
$Database = ‘MYSQL-DATABASE’;
$tmpDir = “/tmp/”;
/*************** Modify email settings ***************/
//$from = $_POST["From"] or die(“NO From Address”);
//$to = $_POST["To"] or die(“NO To Address”);
$from = “webmaster Sender “;
$to = “webmaster Recipient “;
$subject = “$Database – backup”;
$ehost = “smtp.EXAMPLE.com”;
$eusername = “[email protected]”;
$epassword = “PASSWORD”;
/*************** Do not modify below this line ***************/
$connection = mysql_connect(“$Host”,”$UserName”,”$Password”);
mysql_select_db($Database, $connection);
$sql = ‘SHOW TABLES FROM ‘.$Database;
$result = mysql_query($sql);
$contents = “– Created By: My Database Mailer\n– Copyright: Aaron St.Clair McMurray\n– http://www.ourfreeinfo.com\n\n– Database: “.$Database.”\n– Created: “.date(‘M j, Y’).” at “.date(‘h:i A’).”\n\n”;
while ($tables = mysql_fetch_array($result)) {
$TableList[] = $tables[0];
}
foreach ($TableList as $table) {
$row = mysql_fetch_assoc(mysql_query(‘SHOW CREATE TABLE ‘.$table));
$contents .= $row["Create Table"].”;\n\n”;
$sql = ‘SELECT * FROM ‘.$table;
$result = mysql_query($sql);
$columns = explode(‘,’,$row["Create Table"]);
$i = 0;
while ($records = mysql_fetch_array($result)) {
$contents .= “INSERT INTO “.$table.” VALUES (“;
for ($i=0;$i< count($records)/2;$i++) {
if ($i $from, ‘To’ => $to, ‘Subject’ => $subject);
$textMessage = $attachment;
$htmlMessage = “”;
$mime = new Mail_Mime(“\n”);
$mime->setTxtBody($textMessage);
$mime->setHtmlBody($htmlMessage);
$mime->addAttachment($attachment, ‘text/plain’);
$body = $mime->get();
$hdrs = $mime->headers($headers);
$mail = Mail::factory(‘smtp’,
array (‘host’ => $ehost,
‘auth’ => true,
‘username’ => $eusername,
‘password’ => $epassword));
$mail->send($to, $hdrs, $body);
unlink($sqlFile);
unlink($attachment);
if (PEAR::isError($mail)) {
echo(“” . $mail->getMessage() . “”);
} else {
echo(“$sqlFile Message successfully sent!”);
}
mysql_close($connection);
?>
August 2nd, 2008 at 5:08 pm
Previous post has SMTP auth
August 21st, 2008 at 4:16 pm
My coworker pointed me to this script. We modified it with an PHPMailer and it works great. It is being used to back up Media Temple databases for us. I created an array of the databases that we want to back up and it loops through and sends an email for each.
Great job.
October 18th, 2008 at 1:05 am
Nice script. Though some web hosts do provide back ups of our database many shared web hosting providers don’t. I am also in the process of moving one of my sites
November 12th, 2008 at 1:26 pm
Hello,
What a great way to do backups! I tried to implement the script and had a problem…I was hoping you could help. I don’t have permission to write to my /tmp file (I’m on a shared server), so I created a folder within my httpdocs called tmp. While the program can write to the folder, it cannot seem to create the files on the fly, and therefore gives the error
unlink(filepath…./tmp/db_2008_11_12.sql) [function.unlink]: No such file or directory
When I create the files FIRST, manually, i.e. db_2008_11_12.sql and db_2008_11_12.tgz in that directory, I do not get the error, but the resulting file that is emailed to me is unreadable (db_2008_11_12.tgz)
thanks for any help you can provide!
December 11th, 2008 at 5:00 pm
Cool idea. Having it in a PHP script will let people use it that doesn’t have shell access to their hosting account, as long as the exec() statement is available. The only thing is, storing it locally is better than emailing it because emailing anything is insecure, unless it’s encrypted.
There is also a shell script solution out there called AutoMySQLBackup, found here: http://members.lycos.co.uk/wipe_out/automysqlbackup/
February 19th, 2009 at 9:15 am
Spent a while looking at options for backing up db’s from shared hosting and this worked brilliantly here. Thanks for sharing.
August 24th, 2009 at 5:06 pm
I had to cheat a little bit to make it work on my server, but this script really works brilliantly there too !!
Thank you all for all of your precious hints! !!
c ya !
September 15th, 2009 at 1:17 pm
I cann’t locate the mail.php and mail/mime.php files
require_once(‘Mail.php’);
require_once(‘Mail/mime.php’);
September 29th, 2009 at 7:38 am
hi
very very thanks for your database backup codings.
its very useful and correctly to work.
thanks.
February 1st, 2012 at 10:35 am
Internet hosting…
[...]How to: Backup MySQL database & email results using PHP | George Papayiannis[...]…