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]


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.


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?

Cognegic said...

Step by step instructions to Recover Backup File to MySQL Database with DB Recovery Support
Confronting reinforcement issue with respect to MySQL or not ready to recoup whole reinforcement record to MySQL database? Taking any sort of reinforcement is exceptionally basic undertaking and which isn't effortlessly recouped by the clients. It requires heaps of specialized aptitudes and capacities. Any way you can without much of a stretch recuperate your whole database with Cognegic's DB Recovery Support or Exchange Database Recovery. We at Cognegic additionally give Online Database Management Support to our top notch clients to determine their issues.
For More Info:
Contact Number: 1-800-450-8670
Email Address-
Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801