Revision as of 08:29, 19 November 2010

Backup of OpenSim MySQL database

Linux

Add something like this to crontab for a weekly/daily/hourly run. (“crontab -e” to edit, “man crontab” to understand how to edit)

mysqldump -uuser -ppass dbname | gzip > /tmp/mydb_`date +%Y%m%d_%H%M%S`.sql.gz

Crontab should e-mail you and errors in backup if it fails. (If your *nix is set up correctly)

To verify the backup gunzip it and look inside the .sql-file, it should contain a DB-dump. For example by executing:

zcat /tmp/mydb_YYYYMMDD_HHmmSS.sql.gz | more

To restore a given db:

cat /tmp/mydb_YYYYMMDD_HHmmSS.sql.gz | gunzip | mysql -uuser -ppass dbname 

Windows

The same as above can be done in Windows, but rotating the date can't be done in batch scripting afaik. I do however have a script for Windows I wrote for my OpenSim.

This is a script for taking a full MySQL backup on Windows (all databases). It required ActiveState Perl and 7zip command line to work. It works equally well for Linux, but a few adjustments to paths in header needs to be made.

ActiveState Perl: http://www.activestate.com/activeperl/
7-Zip Command line: http://www.7-zip.org/download.html

use strict;
use locale;
#
# MySQL Backup Script by Tedd Hansen, tedd@konge.net, feb 2010
#
# THIS SCRIPT REQUIRES PERL INSTALLED. FOR WINDOWS USE ACTIVESTATE PERL.
#
# (1)
# This script requires a backup-user to be created on the database.
# Execute this: GRANT ALL on *.* to 'backup-user'@'localhost' identified by 'ZeBackup0perat00r'; 

# (change user and password at start of script))
#
# (2)
# To schedule script in Windows you must create a user with log on locally rights like Administrator has.
# DO NOT USE ADMINISTRATOR USER. If you do then backup will stop working next time we change password.
# Set startup path to the location of script, and schedule script to execute every 5 hours for example.
#


# Where to put backup
my $DIR_ROOT = "E:\\Backup";
my $DIR_BACKUP = "$DIR_ROOT\\MySQL_Backup";

# What to name backup
# Note: !DB! will be replaced with database name, !DATE! with current date yyyyMMdd and !TIME! with current time HHmmss
my $FILETEMPLATE = "MySQL !DB! !DATE! !TIME!.sqldump.7z";

my $MYSQL_USER="backup-user";
my $MYSQL_PASSWORD="ZeBackup0perat00r";
my $MYSQL_HOST="localhost";

# Full path to binary files we need
my $BIN_7Z = "$DIR_ROOT\\7za.exe";
my $BIN_MYSQLDUMP = "E:\\MySQL\\bin\\mysqldump.exe";
my $BIN_MYSQL = "E:\\MySQL\\bin\\mysql.exe";
my $CMD_LIST_DB = "echo show databases; | $BIN_MYSQL -u$MYSQL_USER -p$MYSQL_PASSWORD -h $MYSQL_HOST";

# How long before we delete it?
my $KEEP_HISTORY_FOR_DAYS = 30;

# We are piping MySQLDump output directly into 7z for compression. 7z writes to filesystem.
#
# Note: !DB! and !OUTFILE! will be replaced with database name and filename for output file
# -t7z = 7zip format
# -mx5 = Medium compression: 0 = none , 9 = ultra .. Note: Increasing compression will use more CPU, and we want to reserve CPU for customers... :)
# -ms = Create solid archive
# -mmt = Multithreading
# -mo=PPMd = PPMd algorithm (good for text, and we have only text)
my $BACKUP_ARGS = "-u $MYSQL_USER -p$MYSQL_PASSWORD -h $MYSQL_HOST !DB! | $BIN_7Z a -y -t7z -mx7 -ms -mmt -si !OUTFILE!";

#####################
## START OF SCRIPT ##
### ### 
my %DBDONE = {};

# Attempt to create missing dirs
mkdir "$DIR_ROOT" unless (-d $DIR_ROOT);
mkdir "$DIR_BACKUP" unless (-d $DIR_BACKUP);

# Check that we got all we need
die "ERROR: ROOT DIR $DIR_ROOT DOES NOT EXIS!\n" unless (-d $DIR_ROOT);
die "ERROR: BACKUP DIR $DIR_BACKUP DOES NOT EXIS!\n" unless (-d $DIR_BACKUP);
die "ERROR: 7Z DOES NOT EXIST AT $BIN_7Z!\n" unless (-f $BIN_7Z);
die "ERROR: MYSQLDUMP DOES NOT EXIST AT $BIN_MYSQLDUMP!\n" unless (-f $BIN_MYSQLDUMP);
die "ERROR: MYSQLDUMP DOES NOT EXIST AT $BIN_MYSQL!\n" unless (-f $BIN_MYSQL);

# Delete old backups
print "Removing backups older than $KEEP_HISTORY_FOR_DAYS days.\n";
CleanupOldies();

# Make new backup
print "Backing up all MySQL databases on $MYSQL_HOST...\n";
open(DBLIST, "$CMD_LIST_DB |");
while (my $dbname = <DBLIST>) {
chomp $dbname;
BackupDatabase($dbname);
}
close(DBLIST);

sub CleanupOldies
{
# Get what time we should delete files at
$KEEP_HISTORY_FOR_DAYS = 30 if ($KEEP_HISTORY_FOR_DAYS < 1);

my ($dsec,$dmin,$dhour,$dmday,$dmon,$dyear) = localtime(time - ($KEEP_HISTORY_FOR_DAYS * 24 * 60 * 60));
my $DDATE = "0000".($dyear+1900).":00".($dmon+1).":00$dmday";
my $DTIME = "00$dhour:00$dmin:00$dsec";
$DDATE =~ s/^0*([^:]{4}):0*([^:]{2}):0*([^:]{2}).*/$1$2$3/;
$DTIME =~ s/^0*([^:]{2}):0*([^:]{2}):0*([^:]{2}).*/$1$2$3/;
my $CLEANTIME = $DDATE.$DTIME;

opendir(BDIR, "$DIR_BACKUP");
while (my $bfile = readdir(BDIR)) 
{
# Does filename match?
if ($bfile =~ m/(\d{8})\s+(\d{6})/) {
my $bfiledate = $1.$2;
# Is it old?
if ($bfiledate < $CLEANTIME) {
print "Deleting old backup file: $bfile\n";
unlink "$DIR_BACKUP\\$bfile";
}
}
}
closedir(BDIR);
}

sub BackupDatabase
{
my $dbname = shift;

# Only process each DB once
return if ($DBDONE{lc($dbname)});
$DBDONE{lc($dbname)} = 1;

# Get current date
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime(time);
my $DATE = "0000".($year+1900).":00".($mon+1).":00$mday";
my $TIME = "00$hour:00$min:00$sec";
$DATE =~ s/^0*([^:]{4}):0*([^:]{2}):0*([^:]{2}).*/$1$2$3/;
$TIME =~ s/^0*([^:]{2}):0*([^:]{2}):0*([^:]{2}).*/$1$2$3/;

# Get output filename
my ($backup_filename) = $FILETEMPLATE;
$backup_filename =~ s/!DB!/$dbname/i;
$backup_filename =~ s/!DATE!/$DATE/i;
$backup_filename =~ s/!TIME!/$TIME/i;
my ($backupfile) = "$DIR_BACKUP\\$backup_filename";

# Get output command
my ($cmd_mysqldump) = "$BIN_MYSQLDUMP $BACKUP_ARGS";
$cmd_mysqldump =~ s/!DB!/$dbname/i;
$cmd_mysqldump =~ s/!OUTFILE!/\"$backupfile\"/i;

#
# Make backup...
#
print "* Backing up database '$dbname'\nTo file: $backupfile\n";
print `$cmd_mysqldump`;
my ($dsec,$dmin,$dhour,$dmday)=localtime(time);
my $doneTime = "00".($dmday - $mday).":00".($dhour - $hour).":00".($dmin - $min).":00".($dsec - $sec);
$doneTime =~ s/^0*([^:]{2}):0*([^:]{2}):0*([^:]{2}):0*([^:]{2}).*/$1d $2:$3:$4/;
print "Backup of $dbname done in $doneTime.\n\n";

}