By using this site you agree to the use of cookies by Brugbart and our partners.

Learn more

Backup all MySQL Databases with PHP

How to use mysqldump to backup all your databases and tables.

Edited: 2013-04-15 22:27

There are multiple ways to backup all databases on a MySQL server with PHP, one way is to do it from PHP directly, writing the insert statements dynamically with PHP to a file. The problem with this approach, is that its very slow, heavy, and might run into different limits on the server. So in this tutorial, we will be focusing on calling mysqldump from PHP, one time for each database on the server – this is assuming that your db-user have the required permissions to list the databases. Another main advantage of using mysqldump, is that we can use gzip directly, which would othewhise require us to load the file, which can be close to impossible with large database backups.

One thing you should note in this Tutorial, is that we are using the Object oriented style of mysqli, so it may be a little confusing if you havn't worked much with OOP before. Brugbart has a nice Tutorial to help you get started with OOP: PHP OOP Tutorial

We are going to use the exec function in php tp camm mysqldump, this can be done as demonstrated:

exec("/opt/lampp/bin/mysqldump --opt -h $dbhost -u $dbuser -p$dbpass DatabaseName| gzip > $backupFile

The --opt part is explained in the MySQL documentation. It is important because it enables the --quick option – among other things – which dumps the table row by row, rather than reading the entire table into memory before dumping it.

Use of --opt is the same as specifying --add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert, --lock-tables, --quick, and --set-charset. All of the options that --opt stands for also are on by default because --opt is on by default.

To get all databases from the server, we can use the below:

$databases = $db->query("show databases");

While looping over the returned databases, we also make sure that the information_schema and performance_schema databases are skipped, since we will not be using them for anything.

if (($dbrow['Database'] !== 'information_schema') && ($dbrow['Database'] !== 'performance_schema')) {
  // ...do backup here
}

The full backup script is included below. To show that the script has finished – you can simply echo "done" at the end'.

$db = new mysqli('localhost', 'my_user', 'my_password', 'my_db');

$dbhost = 'my.wild.hosting.site.tld';
$dbuser = 'username';
$dbpass = 'password';

$databases = $db->query("show databases");
while($dbrow = $databases->fetch_array(MYSQLI_ASSOC)) {
  if (($dbrow['Database'] !== 'information_schema') && ($dbrow['Database'] !== 'performance_schema')) {
    $db->select_db($dbrow['Database']);

    $date = date("Y-m-d_Hi");
    $backupFile = $_SERVER["DOCUMENT_ROOT"] . '/backups/'.$dbrow['Database'].$date.'.sql';

    exec("/opt/lampp/bin/mysqldump --opt -h $dbhost -u $dbuser -p$dbpass ".$dbrow['Database']."| gzip > $backupFile");
  }
}
echo 'done';

More Tutorials

  1. Using gzip directly in mysqldump