Backups

From OpenSimulator

(Difference between revisions)
Jump to: navigation, search
m
m (→‎PostgreSQL: typo)
Line 258: Line 258:
  
  
−
=PostgeSQL=
+
=PostgreSQL=
 
I recently had to do a backup and restore of my PostgreSQL database.  Here, for the benefit of the other PostgreSQL user are my notes.  I did my backup and restore because I was updating the database software.
 
I recently had to do a backup and restore of my PostgreSQL database.  Here, for the benefit of the other PostgreSQL user are my notes.  I did my backup and restore because I was updating the database software.
 
* Shutdown OpenSim
 
* Shutdown OpenSim
Line 282: Line 282:
 
** Read the PostgreSQL documentation for more information.
 
** Read the PostgreSQL documentation for more information.
 
In my case, it seemed to work fine.  After I started up OpenSim, I logged in and found everything as it should be.  I would really recommend trying this first on a test Sim that you didn't care about.  Also study the documentation before trying this.  Your setup may be different than mine.
 
In my case, it seemed to work fine.  After I started up OpenSim, I logged in and found everything as it should be.  I would really recommend trying this first on a test Sim that you didn't care about.  Also study the documentation before trying this.  Your setup may be different than mine.
−
 
−
 
  
 
=MS SQL=
 
=MS SQL=

Revision as of 02:32, 4 May 2011

Languages Languages: 

En.png English

Making backups of your configuration files and data is essential.


Contents

Backup Using Tools

If you don't run your own grid, then this is a way to back up your data locally.


These examples below enable you to make a full-backup using OpenSimulator command tools.

Backing Up WIN

  • Console Scripts:
  • Backup & Restore console scripts
  • Batch File:
  • Full Backup Archiving for use with Scheduler or Manually initiated

Backing Up LINUX

  • Console Scripts:
  • Backup & Restore console scripts


Local Files

The following files need to be saved:

  • Configuration files
    • OpenSim.ini
    • All files in config-include/
    • All region .xml or .ini files in Regions/
    • If you are running in grid mode, the various .xml configuration files. I using ROBUST Services a range of other .ini files for the various services must now also be backed up.

Feel free to add more if I've missed anything.


SQLite

Since SQLite saves its databases as local files, it's fairly simple to back it up.

  • Stop OpenSim
  • Copy all of the *.db files out of the /bin directory (assuming that you haven't changed this in the OpenSim.ini file) to wherever you want them saved.


MySQL

Making A Backup of your MYSQL Database

You can, and should backup the MySQL database to a set of SQL commands, which can use used to restore the database, if needed, with the following command:

Run a terminal console (in Windows make sure you run it as administrator)

mysqldump -u<username> -p<password> -r <location> <databasename>

Example, assuming that your username is admin, and your password is secret (Linux/Mac example): 

mysqldump -uadmin -psecret -r /home/Johnny/OpenSimBackups/OpensimBackup.sql opensim

For Windows (testing needed!), this would be: 

mysqldump -uadmin -psecret -r %HOMEPATH%\My Documents\OpenSimBackups\OpensimBackup.sql opensim

Note that there is NO space between -u and the username, and between -p and the password

You will not be prompted for your password, this makes this command suitable to be used for unattended backups.

Timestamped Backups

Making a single backup is good. Making daily backups is even better! However, using the command above, it will keep reusing that one file, making it impossible to go back to an earlier date, if needed.

So what we want, is to give the backup a unique, and meaningful name. Here is how:

Linux

mv /home/Johnny/OpenSimBackups/OpensimBackup.sql /home/Johnny/OpenSimBackups/OpensimBackup_$(date +%Y-%m-%d-%H.%M.%S).sql

Instead of issuing this command after the backup has been made, you can append it to the backup command by adding a pipe symbol (|) to the first command. This will let you use the entire line in a cron job.

Windows

<THIS SECTION NEEDS TO BE TESTED!!!> 

move %HOMEPATH%\My Documents\OpenSimBackups\OpensimBackup.sql %HOMEPATH%\My Documents\OpenSimBackups\OpensimBackup%DATE%_%TIME%.sql


Automatic Daily Backups

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

Instead of using crontab, you can use some graphical tools.

  • In GNOME, use gnome-schedule to set a scheduled job.
  • In KDE, use the Task Scheduler from your System Settings panel.


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";

}


PostgreSQL

I recently had to do a backup and restore of my PostgreSQL database. Here, for the benefit of the other PostgreSQL user are my notes. I did my backup and restore because I was updating the database software.

  • Shutdown OpenSim
  • Use the pg_dump command to backup the database.
    • In my case, I did this as the postgres user. It should work as the OpenSim (or whatever user owns the OpenSim database) user.
    • pg_dump -b -Fc -C -f opensim.db.backup opensim
    • The -b option requests that large objects are included in the dump. This would be things like textures and sounds.
    • The -Fc option creates the backup file as a gzipped tar file
    • The -C option includes commands to create the database when restoring
    • The -f option specifies the name of the output file. In this case opensim.db.backup
    • There are a number of other options that can be used. For example, if you're doing the backup on a different machine, the -h option allows you to specify the machine that the database is running on.
  • Copy the opensim.db.backup file to a CD-ROM or wherever you want it saved.
  • Do what you need to do. In my case, it was upgrading the PostgreSQL software
  • While PostgreSQL was shutdown, I saved a tar of its /data directory to make it easier to recover if the upgrade went bad.
  • I also took copies of the configuration files in the /data directory so that I didn't have to recreate them.
  • Delete the /data directory
  • Initialize the new database
    • The command is initdb -D <path to /data directory>
  • Start the new PostgreSQL software (it will complain about data formats if you try this before you delete the old /data and run initdb)
  • Create the opensim user
  • Restore the data using pg_restore
    • pg_restore -C -d template1 opensim.db.backup
    • Read the PostgreSQL documentation for more information.

In my case, it seemed to work fine. After I started up OpenSim, I logged in and found everything as it should be. I would really recommend trying this first on a test Sim that you didn't care about. Also study the documentation before trying this. Your setup may be different than mine.

MS SQL

<unknown>

Retrieved from "http://opensimulator.org/index.php?title=Backups&oldid=21950"
Personal tools
General
About This Wiki