|
|
| Line 1: |
Line 1: |
| − | = Backup of OpenSim MySQL database =
| + | #Redirect[[Backups]] |
| − | | + | {{delete|The contents in this page is merged into [[Backups#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)
| + | |
| − | <pre>mysqldump -uuser -ppass dbname | gzip > /tmp/mydb_`date +%Y%m%d_%H%M%S`.sql.gz</pre>
| + | |
| − | 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:
| + | |
| − | <pre>zcat /tmp/mydb_YYYYMMDD_HHmmSS.sql.gz | more</pre>
| + | |
| − | To restore a given db:
| + | |
| − | <pre>cat /tmp/mydb_YYYYMMDD_HHmmSS.sql.gz | gunzip | mysql -uuser -ppass dbname </pre>
| + | |
| − | == 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/ http://www.activestate.com/activeperl/]<br>7-Zip Command line: [http://www.7-zip.org/download.html http://www.7-zip.org/download.html]
| + | |
| − | <pre>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";
| + | |
| − | | + | |
| − | }
| + | |
| − | </pre>
| + | |
| − | <font style="background-color: #f9f9f9" face="Courier New"></font>
| + | |
| − | | + | |
| − | | + | |
| − | [[Category:Database]]
| + | |
