Saturday, November 19, 2011

Using PHP to backup an entire MySQL server

This post can be used as a free way to use PHP to backup your MySQL server. The code below comes from a script I developed so that we could back up the server with the execution of a single PHP page. Due to the way the script works, it passes off most of the heavy lifting to the system using exec() function so that it doesn't have to run through the webserver. It does this by using the mysqldump client program that is installed on the MySQL server - The PHP script is just used as a trigger. This is especially helpful if a non-technical person needs to run a database backup, yet doesn't know how to use console commands.

A few notes:
  • Each database is backed up in to it's own file and zipped up using gzip - make sure you have it installed. If not, you can always remove the zip command. 
  • The database script also adds the date to the file name
  • If you do not want to backup the entire database, just alter the database_list array with the names of the databases you want to backup, i.e.
$database_list = array('database_1', 'database_2');
Usage:

To use the script, all you have to do is modify the connection variables at the top and make sure your dump folder has the correct permissions.
  1. I List databases using a little bit of PHP and add them to an array
  2. Loop through the databases
The script:
set_time_limit(0);
$username = 'username';
$password = 'password';
$host = 'localhost';
/**
* Put your path here - Can be any valid path, but remember that you must have write permission on the folder
*/
$dump_path = './dumps/';
$dbc = mysqli_connect($host,$username,$password);
$result = mysqli_query($dbc,'SHOW DATABASES'); 
$database_list = array();
while($row = mysqli_fetch_array($result)) {
   $database_list[] = $row['Database'];
}
foreach ($database_list as $database) {
    $dump_name = $database.'_'.date('d-m-y');
    $dump_string = 'mysqldump --host '.$host.' --user='.$username.' --password='.$password.' '.$database.' > '.$dump_path.$dump_name.'.sql';
    exec($dump_string);
    $zip_cmd = 'gzip '.$dump_path.$dump_name.'.sql';
    exec ($zip_cmd );
}
mysqli_close($dbc);
Download
If you would like to download the script, you may get it here

Expansion
Another one of the benefits of using the exec() function is that it can easily adapted to perform scheduled, reliable backups using a CRON job. This is one of the reasons I used the date() function to append the backup name, so that you could have more than copy of the database in the same directory. One further modification to the script you could perform is to sub categorise your database backups so that they are easier to manage.

James

Labels: , ,

1 Comments:

Blogger Eric said...

Hi James, I was just looking at any recent posts on Google for PHP MySQL backup solutions (to see how the one we just released ranks) and came across your post.

We released our code at https://github.com/LinkUp/PHP-MySQL-Backup, I'd love to know what you think of it. I uses a lot of the same fundamentals as your script, so that's why I'd like to know your opinion on it.

@ecaron

November 24, 2011 at 3:06 AM  

Post a Comment

Subscribe to Post Comments [Atom]

<< Home