Backup my databases! PHP + Shell + Cron = simple backups

January 25, 2008

Posted in: Bash, MySql, PHP, Programming

Tags: backup, Bash, cron, database, drupal, headache, linux, mysql, php, site5, unix, wikipedia

Alright, I think I have it (I’m just waiting to make sure cron fires off and runs everything)! It’s very simple, and probably pretty dirty - but hey I’m not a whiz with shell scripting, nor cron so I think what I have is pretty sweet.

Because I’m lazy (I’m a programmer!) and I know I wouldn’t remember to run backups in an normal fashion, I wanted to make a system to run them automatically, think set it and forget it (wait wasn’t that an infomercial of some type?). So without further adieu…

The Shell script:

#!/bin/sh
export PATH=/usr/local/bin:/bin:/usr/bin
mysqldump --opt -uUsername -pPassword --all-databases >
~/db_backup_tmp/all-db-backup.sql &&
mv ~/db_backup_tmp/all-db-backup.sql ~/db_backup/all-db-backup.sql

The PHP script:

$file = "~/mysql_backup.sh";
exec($file);

Calm down, I’ll explain it…

So first we have the shell script.

#!/bin/sh

From Wiki

On some systems, such as Solaris, this is the Bourne shell. On Linux systems there is usually no Bourne shell and this is a link to another shell, such as bash. According to the Single UNIX Specification’s requirements for /bin/sh, such a shell will usually mimic the Bourne shell’s behaviour, but be aware that using #!/bin/sh in shell scripts may invoke different Bourne-compatible shells on different systems.

So basically we’re telling the system to use the shell for this. The next part just makes sure the paths are all in order, so it knows what to use to do this and where it is.

Finally the last line.

mysqldump --opt -uUsername -pPassword --all-databases >
 ~/db_backup_tmp/all-db-backup.sql &&
 mv ~/db_backup_tmp/all-db-backup.sql ~/db_backup/all-db-backup.sql

What I have going on here is a basic dump of all the databases into a temporary directory, then once that has completed (&&) move that file into my backup directory overwriting whatever was there previously. This is a very simple way of doing it with a bit of reassurance in that it will only overwrite if it has successfully made the backup first. Another bit on security here is that I have the script outside my html directories so that no one would be able to read it or find it through browsing my directories.

The final step to the shell script to tell the system it’s executable. You can do this by typing at your shell prompt, chmod -x path/filename.

A note on using the ~ tilde, this represents the “home” directory. So if your directory looks like this “/public_html/website_folder/” and you’re not sure what is before public_html, if you put “~/public_html/website_folder” is would represent “home/username/public_html/website_folder”. It’s very useful when you’re working outside the html scope and when you’re not sure the directory structure.

Now on to the PHP script.

$file = "~/mysql_backup.sh";

I just assign the $file variable to my shell script. You’ll see the ~ again, this tells it that the script resides off my home directory, it’s not inside anything else so it basically means “home/username/scriptfile”.

The last line of the PHP script.

exec($file);

This uses the built in exec function. This runs an external application, in this case a shell script. So I tell it, run mysql_backup.sh. You might be asking yourself why am I running it through PHP, well because that’s the only way I could get it to work. I didn’t feel like asking tech support why the shell script wouldn’t fire off on it’s own through cron, but I did know that my drupal php cron script ran so I though I’d replicate that and just make a simple PHP wrapper for this. Which upon initial testing seemed to work just fine.

I just checked and it looks like the hourly run of cron picked it up and ran it, so now I’ll set it to run the same time as my drupal cron job. Which will look like this.

0 20 * * 0 /usr/bin/php ~/public_html/cron_jobs/run_backup.php

Then come Sunday night, I should have 2 emails showing that cron fired off and ran. I’m rather excited about this…

Posted in Bash, MySql, PHP, Programming