I was a happy user of MySQLDumper until a while ago, when my shared hosting upgraded to the cloud architecture and latest PHP version; with that, some modules for PERL went missing and I was left without a viable backup solution (MySQLDumper PHP approach uses a javascript reloading routine which is superslow and is not compatible with wget nor remote cronjobs like SetCronJob.com).
So I found a couple of tutorials on the net (this, and this), mixed them together, especially improving David Walsh’s routine, blended them with some healthy bzip2 compression, and shipped to my webspace.
I stripped the part where you can select which tables to backup; I figured that any backup deserving this name should be “all-inclusive”; also I think that anyone with the special need to backup only selective tables should also be smart enough to change the code to suit his/her needs.
UPDATE 9/9/11: I just added a snippet of code to blacklist some tables from the backup, since I needed to trim the size or it gave a fatal error about the available memory being full; just fill the array with rows as you see fit
This is the result:
<?php $creationstart=strtok(microtime()," ")+strtok(" "); $dbhost="your.sql.host"; $dbname="yourdbname"; $dbuser="yourmysqlusername"; $dbpass="yourmysqlpassword"; $mailto="mail.me@mymailbox.com"; $subject="Backup DB"; $from_name="Your trustworthy website"; $from_mail="noreply@yourwebsite.com"; mysql_connect($dbhost, $dbuser, $dbpass); mysql_select_db($dbname); $tablesblocklist=array( "tablename1"=>1, "tablename2"=>1, "tablename3"=>1, ); $tables = array(); $result = mysql_query("SHOW TABLES"); while($row = mysql_fetch_row($result)) $tables[] = $row[0]; foreach($tables as $table) { if (!isset($tablesblocklist[$table])) { $result = mysql_query("SELECT * FROM $table"); $return.= "DROP TABLE IF EXISTS $table;"; $row2 = mysql_fetch_row(mysql_query("SHOW CREATE TABLE $table")); $return.= "\n\n".$row2[1].";\n\n"; while($row = mysql_fetch_row($result)) { $return.= "INSERT INTO $table VALUES("; $fields=array(); foreach ($row as $field) $fields[]="'".mysql_real_escape_string($field)."'"; $return.= implode(",",$fields).");\n"; } $return.="\n\n\n"; } } $filename='db-backup-'.date("Y-m-d H.m.i").'.sql.bz2'; $content=chunk_split(base64_encode(bzcompress($return,9))); $uid=md5(uniqid(time())); $header= "From: ".$from_name." <".$from_mail.">\r\n". "Reply-To: ".$replyto."\r\n". "MIME-Version: 1.0\r\n". "Content-Type: multipart/mixed; boundary=\"".$uid."\"\r\n\r\n". "This is a multi-part message in MIME format.\r\n". "--".$uid."\r\n". "Content-type:text/plain; charset=iso-8859-1\r\n". "Content-Transfer-Encoding: 7bit\r\n\r\n". $message."\r\n\r\n". "--".$uid."\r\n". "Content-Type: application/octet-stream; name=\"".$filename."\"\r\n". "Content-Transfer-Encoding: base64\r\n". "Content-Disposition: attachment; filename=\"".$filename."\"\r\n\r\n". $content."\r\n\r\n". "--".$uid."--"; mail($mailto,$subject,"",$header); $creationend=strtok(microtime()," ")+strtok(" "); $creationtime=number_format($creationend-$creationstart,4); echo "Database backup compressed to bz2 and sent by email in $creationtime seconds"; ?>
(WordPress killed the indenting and I’ve no intention of fixing it manually)
The whole routine runs in roughly 7seconds on my not-so-bright hoster server, including bzip2’ing and mailing, while the original text-only output for David’s code took on the same server roughly 17seconds; probably it’s due to the removal of several redundant loops, and using the builtin mysql_real_escape_string() and implode() functions instead of David’s workarounds.
My sincere thanks go to the authors of the two guides, without whom I wouldn’t be writing this today 😉