Optimize and Backup your MySQL databases

At the moment I’m juggling 3 MySQL databases. It could very easily be more.

I usually use phpMyAdmin for backing them up but restrictions on port forwarding has meant I can’t do this whilst at work ⁱ. So, I wrote myself a script to do this. And optimize the tables as well.

An extract of the script is below… mine allows me to choose the database and compression type and then populates the appropriate database fields (username, password, etc) but for generic usage purposes, the script below should be fine for other people to use and build upon.

if ($compression=="None") {$comp_cmd=""; $comp_ext="sql";}
if ($compression=="Gzip") {$comp_cmd=" | gzip"; $comp_ext="gzip";}
if ($compression=="Zip") {$comp_cmd=" | zip"; $comp_ext="zip";}

mysql_connect($host,$user,$password);
mysql_select_db($database);
$query="SHOW TABLE STATUS FROM ".$database;
$result=mysql_query($query);
$num=mysql_numrows($result);
$compressed=0;
$i=0;
while ($i < $num) {
   $table=mysql_result($result,$i,"Name");
   $gain=mysql_result($result,$i,"Data_free");
   if ($gain!=0) {
       if ($compressed==0) {echo "<br/>Compressing tables...<br/><br/>n";}
       echo "Table ".$table." - ".$gain." bytes gained<br>n";
       $query="OPTIMIZE TABLE ".$table;
       $optimise=mysql_query($query);
       $compressed=$compressed+$gain;
   }
   $i++;
}
if ($compressed!=0) {
   echo "<br/>".$compressed." bytes gained in total<br/>";
} else {
   echo "<br/>No tables were optimised.<br/>";
}
$backupFile = "backup/".$name."_".date("Ymd").'.'.$comp_ext;
$command = "mysqldump --opt --extended-insert --complete-insert --hex-blob
--host=".$host." --user=".$user." --password='".$password."' ".$database.
$comp_cmd." > $backupFile";
exec($command, $ret_arr, $ret_code);
if ($ret_code==0) {
   echo "<br/>Database backed up: <a href="".$backupFile."">download</a>.n";
} else {
   echo "<br/>The database could not be backed up - the return code was ".
$ret_code.".n";
};

Before running the above, you need to populate the following fields…

$database – your MySQL database
$user – your MySQL username
$password – your MySQL password
$host – your MySQL host name
$name – used to name your backup
$compression – this is the type of compression you wish to apply and should be either None, Zip or Gzip.

When run any tables that require optimizing will be, well, optimized and the details output. Finally a backup is made of the database and a link displayed so that you can download it.

Downloads are placed in a folder named /download, so make sure you add write permissions for this folder. The filename will be xxx_yymmdd, where xxx is the name you specified in $name and yymmdd is the date. Obviously the extension will be based on the type of compression you requested.

Whilst trying to write this code I came across various scripts and resources which did something similar but often didn’t work (grrr) for one reason or another. In particular, often the backup files are create empty if you have the MySQLdump parameters wrong. One thing you might spot that I’ve worked around – and here’s my tip for the day – is that I’ve placed single quotes around the password. This was because one of my passwords had an ampersand in it and, well, MySQLdump doesn’t like it.

Enjoy, play and let me know how you get on!

1. and I have a tendency to forget if I leave it until the evening at home[↩]