Backup

= 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 &gt; /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;
 * 1) MySQL Backup Script by Tedd Hansen, tedd@konge.net, feb 2010
 * 2) THIS SCRIPT REQUIRES PERL INSTALLED. FOR WINDOWS USE ACTIVESTATE PERL.
 * 3) (1)
 * 4) This script requires a backup-user to be created on the database.
 * 5) Execute this: GRANT ALL on *.* to 'backup-user'@'localhost' identified by 'ZeBackup0perat00r';
 * 1) (1)
 * 2) This script requires a backup-user to be created on the database.
 * 3) Execute this: GRANT ALL on *.* to 'backup-user'@'localhost' identified by 'ZeBackup0perat00r';


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

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

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

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

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";
 * 1) Full path to binary files we need

my $KEEP_HISTORY_FOR_DAYS = 30;
 * 1) How long before we delete it?

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

my %DBDONE = {};
 * 1) START OF SCRIPT ##
 * 1) START OF SCRIPT ##

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

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);
 * 1) Check that we got all we need

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

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

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

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)) { if ($bfile =~ m/(\d{8})\s+(\d{6})/) { my $bfiledate = $1.$2; if ($bfiledate &lt; $CLEANTIME) { print "Deleting old backup file: $bfile\n"; unlink "$DIR_BACKUP\\$bfile"; } } } closedir(BDIR); }
 * 1) Does filename match?
 * 1) Is it old?

sub BackupDatabase { my $dbname = shift;

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

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/;
 * 1) Get current date

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";
 * 1) Get output filename

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

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";
 * 1) Make backup...
 * 1) Make backup...

} 