I run a number of web servers with PHP and MySQL and have a PHP command line script that runs on a daily basis to back up MySQL databases using the mysqldump command. It would be possible to do this using a simple bash script as well, and I know I used to use a bash script in the past but for some reason switched it to PHP at some stage.
The script runs as follows, with an explanation below about what each thing does:
#!/usr/bin/php -q
<?php
$datadir = '/var/lib/mysql';
$backupdir = '/var/mysql-backup/' . date('l');
$mysqldump = '/usr/bin/mysqldump';
$password = 'root-password-here';
$dir = opendir($datadir);
while($database = readdir($dir)) {
if($database != '.' && $database != '..' && is_dir("$datadir/$database")) {
if(!is_dir("$backupdir/$database")) {
mkdir("$backupdir/$database");
chown("$backupdir/$database", "mysql");
chgrp("$backupdir/$database", "mysql");
}
system("$mysqldump --user=root --password=$password --quick --add-drop-table -all --add-locks --force -d $database > $backupdir/$database/$database.sql");
system("$mysqldump --user=root --password=$password --quick --force -t -T$backupdir/$database $database");
system("bzip2 -f $backupdir/$database/*.txt");
}
}
closedir($dir);
?>
$datadir is the directory that MySQL actually stores its database files. It is required by this script to be able to work out what it actually needs to back up by looking at the directories. It could instead connect to the database and use a database query to get the databases, but this works just as well.
$backupdir is where the database backups should be stored. I create backups for each day of the week, hence the date(‘l’) added to the end of the directory. This returns the current day of the week name (eg Sunday, Monday, Tuesday etc).
$mysqldump is the location of the mysqldump command.
$password is the MySQL root user’s password.
The next section of the script loops through all the files in $datadir. The opendir() and readdir() functions will also show the . and .. directories, so we don’t want to attempt to back up databases with those names, so the loop excludes those.
If the directory that we want to dump data into for the specific database doesn’t exist, then it is created. The script then also changes the ownership and group to be the user that MySQL runs as; this is to avoid any errors that might happen when attempting to dump the data because MySQL won’t be able to write to the directory if the MySQL user cannot write to it.
Finally, mysqldump is called twice. The first time is to dump the database structure and the second time to dump the data itself. The data dump is into tab delimited files with one file for each tablename. These tab files are then compressed using bzip2.
In the next couple of days, I’ll look at the mysqldump command in more detail and then how to restore data from a backup in this format.