Moving a MySQL Database

While I am waiting for my server (which hopefully be available on either Monday or Tuesday), which has a Windows 2008 Web Server OS, I thought I would have a go at moving my opensim and MySQL database from my home PC to my laptop, to see if it was straightforward. I am glad I have tried to have a go, because I got bogged down for quite a while, but fortunately with the help of DigiDaz from the #opensim irc channel, I eventually got there. Here are the steps:

Step 1: Moving OpenSim
Getting opensim onto my laptop was as easy as copying my bin folder from my home PC onto a memory stick, and copying it onto my laptop.

Step 2: Installing MySQL
I then installed MySQL onto my laptop. When configuring I used the same user name (root) and password as on my PC, I then created an empty opensim database. (see Tutorial 5, in the Main Tutorials, for full instructions on installing MySQL and creating the opensim database).

Step 3: Backing up the MySQL Database
On my PC I made a backup copy of my opensim database. I did this by opening a command console window (go to Start, Run, and type 'cmd' (without the quotes) in the Open: box.). In the command console I typed the following:

mysqldump -u root -p opensim > opensim.sql

This command starts the mysqldump backup utility. The full syntax for this command is:

mysqldump -u [username] -p [databasename] > [backupfile.sql]

where:

username = the user name you use in MySQL, usually 'root' the -p switch will prompt you for the password you use for MySQL

databasename = the name of the database you wish to backup, in our case this is 'opensim'

backupfile.sql = the name of the backup file, can be anything, I chose 'opensim.sql'

Like this.

I had to do a search to see where this file had been saved, I found it in the 'C:\Documents and Settings\Colin' folder.

Step 4: Transfer the Database backup File
I then copied opensim.sql file onto my memory stick, and pasted it in the C:\ root folder on my laptop.

I then opened a command console on the laptop, and navigated to the root folder by typing:

cd c:\

(it is important to be in the same folder as the test.sql file)

Step 5: Restore the Database
I then restored the database by typing at the command console:

mysql -uroot -p opensim < opensim.sql

It was successful and it returned me to the c:\ prompt.

After starting my Opensim, and logging in with my Hippo viewer, I was pleased to see my region exactly as it was on my PC. A very happy bunny indeed, eventually :)



The big benefit of transferring the database, rather than using the console command, save-oar, is that everything is transferred, not just the objects, so all the terrains, terrain textures, users, estate settings etc are all migrated.

Again, many thanks to DigiDaz.

1 comment:

Dan Baxter said...

when I try to restore the opensim.sql file I get the following error:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databa' at line 1

any ideas?