Skip to content
A script that gets a list of all your mysql databases and backs them up using php
PHP
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Failed to load latest commit information.
README.markdown
backup.php

README.markdown

If you haven't worked too much with the Linux bash (or command line), the following tasks may seem over-complicated to you:

  • Run a PHP script every day at a specified time (a cron job)
  • Have that PHP script get a list of all databases for a given user on a server
  • Back up all of those databases

So in the interest of helping you out, I gift thee a guide...

First let's get that PHP script. You can find it on GitHub in the file backup.php or by clicking the following link: https://github.com/janhartigan/phpMySQLAutoBackup. Create a directory somewhere on your server (outside of your web root) called scripts and place backup.php in that directory.

Now that the script is on your server, let's go over it and see what it does.

<?php

// Creates a mysqldump and emails the resulting dump file
$dbhost = "localhost";

// Edit the following values
$dbuser = "mysql_user"; //the mysql user
$dbpass = "mysql_pass"; //the mysql password
$path = "/home/admin/backups/mysql/"; //the directory path to where you want to store your backups

Here you must define the following items:

  • $dbhost: This is normally localhost, unless your mysql driver is hosted on a foreign server
  • $dbuser: The mysql user that will connect to the database
  • $dbpass: The password for $dbuser
  • $path: The directory path where you want to store your backups. On my server, I store them in /home/admin/backups/mysql, but you can store them anywhere you like

Next we must get a list of databases using these database settings:

//get the list of databases
$link = mysql_connect($dbhost, $dbuser, $dbpass);
$db_list = mysql_list_dbs($link);

Here we establish a MySQL connection using PHP's mysql_connect function. Keep an eye on that $link variable as we will need to close it at the end of the script. Using the resource $link, we then call PHP's mysql_list_dbs function which returns a resource that we can use to iterate over our list of databases.

//iterate over the list of databases
while ($row = mysql_fetch_object($db_list)) {
    $dbname = $row->Database;
    $dir = $path.$dbname;

    //if the db directory doesn't exist yet, create it
    if (!is_dir($dir))
        mkdir($dir);

    //create the file name for the backup (if you want to run the update more frequently than once a day, add more specificity to the date
    $backupfile = $dir.'/'.$dbname.'_'.date("Y-m-d").'.sql';

    //make the system call to mysqldump
    system("mysqldump -h $dbhost -u $dbuser -p$dbpass $dbname > $backupfile");
}

Now that we've got the list of databases, we can perform a while loop in order to iterate over the results. We store the database name in our $dbname variable and the full directory path (our backups directory plus a directory to contain our daily backups for this database) in the $dir variable. If the $dir directory doesn't exist, we create it. Then we create another variable called $backupfile which is simply a string representing the full directory path to the database's backup directory ($dir) and the database name plus a date string. If I had a database named "bird_watchers" and I ran this script on October 3, 2011, the $backupfile variable would look like this:

/home/admin/backups/mysql/bird_watchers/bird_watchers_2011-10-03.sql

Now that all the variables are set up, we're ready to run the system command that will actually back up the mysql database that our while loop is currently iterating over. In order to run this command, you need to ensure that you have mysqldump running on your server (most Apache servers have it). The syntax for this command, given the inputs we used above, is:

mysqldump -h localhost -u mysql_user -pmysql_pass bird_waters > /home/admin/backups/mysql/bird_watchers/bird_watchers_2011-10-03.sql

You should be able to figure out what's going on in there, but here's more info in case you need it.

Finally, we close the MySQL connection:

//close the mysql connection
mysql_close($link);
Something went wrong with that request. Please try again.