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

Everyday MySQL console commands and MySQL hints

PHPMyAdmin is a great tool, but due its web based nature it can sometimes fall a little short, especially when dealing with large amounts of data. Don't get me wrong, it's still very useful for browsing large data sets, but if you are looking to do imports, exports, adding indexes etc., then you really need to use the console or some other application.

To be honest, most of the commands here, you will find on many other websites, I just wanted to compile this list as a reference: All the following commands were ran using the console in BASH, but should work on Windows/Mac consoles.

Logging in with a supplied username and using password option:

mysql --user=your_username -p

Logging in with a supplied username and a supplied password:

mysql --user=your_username --password=your_password

Logging in to a different host with a supplied username and password:

mysql --host  host_address --user=your_username --password=your_password

List databases

SHOW DATABASES;

Select a table

USE table_name;

Add an index

When you have a million plus records, this can save so much time in your lookups. I had a table that 1.5 million records, using a single WHERE clause on a non-index field was taking around 1.9 seconds, adding an index reduced the time to 0.0005 seconds:

ALTER TABLE table_name add index (column_name);

Export a database to a file:

mysqldump --user=your_username --password=your_password database_name > name_and_path_of_sql_file_to_create.sql

Will use a modification of the above to show how you can use PHP to backup an entire database in a future post - you can read that here: backup up your MySQL server with PHP

Import a database on to a different host with a supplied username and password:

mysql --host host_address --user=your_username --password=your_password db_name < your_sql_file.sql

Hope this is of use to you.

James