Another MySQL Backup Script

Published December 24, 2006
Filed under

I've been meaning to backup my sites' databases for quite some time now. Today I was finally overcome with guilt and the fear of something going horrible wrong, so I finally decided to put a backup script into place. My hosting provider provides backups, and fortunately I've never needed to request a restore. You may ask "Then why are you worried about backups?". The answer is simple—there are some things you should count on in this world–things will go wrong from time to time. Yes, my hosting provider is a reliable and stable company providing a reliable and stable hosting environment, but things can change, and one should always be prepared.

I've been reading up on MySQL backup options for the past few months and there are plenty of options. The automated options fall into the following categories:

You need to know your needs before choosing the option that will work best for you. Here are some of the things you'll need to ask yourself:

  • How often are my databases changing?
  • Do I have access to MySQL's data directory?
  • Which storage engine do my databases use, MyISAM, InnoDB?
  • Do I want to automate the backup process or handle backups manually?

For my needs, nightly backup will suffice, none of my sites' databases change that often. I'm in a shared hosting environment and I do not have access to my MySQL data files. My databases are primarily InnoDB but I do still have a few MyISAM databases. And I definitely want to automate my backup process.
After exploring the MySQL Adminstrator's backup feature, I opted to write my own backup script. Don't get me wrong, the MySQL Administrator's backup feature works great, but it was a bit tedious setting up backup projects for several servers. If I need to create individual database backups I'll probably use the MySQL Adminstrator's backup option.
This PHP script, as most other scripts, uses the mysqldump command line utility. Running mysqldump looks something like this:

mysqldump --all-databases --opt -h dbhost.com -u dbuser -pdbpass | gzip > dbhost.com.sql.gz

The --all-databases option dumps all databases on the server. The --opt stands for optimize and adds drop and recreate table statements that we need when restoring databases. Add -h to set host, -u to specify the user, and -p to set the password–there should not be a space between -p and the password. Review all of mysqldump's options to see which options make the most sense for you. Finally, pipe the output to gzip to compress the output.
I'm running the script in a *nix environment, but with a few modifications I imagine this script will run on a Windows box. Regardless of the system, PHP and MySQL must be installed. To use this script, you'll need to

  • Create a database user on the remote database server for the script. For security's sake, only give this user LOCK TABLES and SELECT privileges on the databases to be backed up. 

    GRANT LOCK TABLES, SELECT ON mydatabase.* TO 'backup'@'backuphost' IDENTIFIED BY 'password';
  • Give the backup database user's account access from the IP or host address of the machine it will run.
  • Create a folder in your local home directory where the script will run and save dump files.
  • Download and save the backup script (mysql_backup.php.txt) to this folder. Remove the .txt extension.
  • At the top of the script
    • Add your database connection parameters
    • Add MySQL hosts
    • Edit the path to mysqldump, which mysqldump from a command line comes in handy
  • Create a crontab entry, try crontab -e. My crontab to perform nightly backups looks like this 

    0 01 * * * "/usr/bin/php ~/MySQL_Backups/mysql_backup.php"

That's it. Feel free to use and edit this script to suit your purposes.

Share