Backup-MySQL

Revision as of 02:52, 22 November 2010

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:

Windows

<THIS SECTION NEEDS TO BE TESTED!!!>

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

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.

Mac

<unknown, please add Mac version>

Automatic Daily Backups

Windows

<Example needed>

Linux

This example is very Ububtu centered. Please add modifications for other distros, if/when needed.

In this example, we will assume that the username is admin, and the password is secret.

• Open a text editor, and paste the following.

#!/bin/bash
  #Change to the folder where the files should be saved, for ease of use.
cd /home/Johnny/OpenSimBackups/

  #Make a backup of the opensim database
mysqldump -uadmin -psecret -r OpensimBackup.sql opensim

  #Tricky one! Compresses the backup file, and appends time and date.
  #This saves disk space, and lets you go back to an earlier date if needed.
tar -czf OpensimBackup_$(date +%Y-%m-%d-%H.%M.%S).sql.tar.gz OpensimBackup.sql  

  #Lastly, we remove the uncompressed backup file. We no longer need that big file!
rm OpensimBackup.sql

• Save the file, and give it a good name. For example, OpenSimBackup.sh. I would suggest saving it directly to your homedirectory (~/OpenSimBackup.sh).
• Next, we need to create a cron job. This can be done with one of the graphical tools that are available.
  • In GNOME, use gnome-schedule to set a scheduled job.
  • In KDE, use the Task Scheduler from your System Settings panel.

(Info needed about setting a cronjob from the terminal)

Mac

<Example needed>