04. Migrating to MySQL

The OpenSim binaries to date have all been based around the SQLite database system, for managing assets, inventory, users etc. Unfortunately, this does not lend itself well to persistance (the ability to close down your viewer and your server, then start them back up again and find your appearance is back to the dreaded 'Ruth'). The solution to this is to use the MySQL database system, and this article will show you how to do that.

Step 1: Install MySQL
Download MySQL from here. I chose the Windows ZIP/Setup.EXE (x86) version (of course if you're running a different system you would pick the appropriate binary for your system). Unzip the package, and launch the Installer using the Typical setting.

Step 2: Configure MySQL
When it has finished installing it will take you through the Setup Wizard to Configure MySQL, choose Standard Configuration. On the next page of the Wizard ensure that 'Install as a Window Service', 'Launch the MySQL Server Automatically', and 'Include Bin Directory' are all checked. The next page is for your security settings. The modify Security Settings should be checked. Leave the 'Current Root Password' box empty, and put your own password into the 'New Root Password' box, and confirm it in the next box. Remember the root password that you choose!!! Write it down somewhere. If you lose this you will not be able to use or reinstall MySQL. Click on the Next, and then the Execute buttons.

MySQL is now installed. We next need to set up a database for MySQL and OpenSim to use.

Step 3: Create the Database
Go to Start, Programs, and you should see the new folder for MySQL, and the MySQL Server 5.0, launch the MySQL Command Line Client. Type in your password, and then at the mysql> prompt, type:

create database opensim;
quit;
(and don't forget the ending semicolons!)

The database has now been created, ready for use by Opensim. We now need to modify the OpenSim.ini file to tell it to use the new database.

Step 4: Save your Existing Objects
If you don't have existing objects in your regions you can skip this step.

Before changing over to MySQL you may want to save all the objects you have in your regions, and restore them after the migration. To do this you must save the objects from each region separately. Suppose you have two regions, called Alpha and Beta. The procedure is to use the following commands in the opensim server console:

change-region Alpha
save-oar c:\Alpha.oar

change-region Beta
save-oar c:\Beta.oar

and so on, if you have more regions.

Note: Do NOT try to do a save-oar while the region is Root, always change the region to the one you want to work on by using the change-region command first.

Step 5: Configure OpenSim
Go to the Opensim Bin folder, and in there you will find the Opensim.ini configuration file. Double-click it and it should open automatically in Notepad. You need to change the following lines (note: placing a semicolon(;) at the start of any line disables it by commenting it out, removing the semicolon enables the line):

Under the ; ## STORAGE section look for the line that says (; --- To use sqlite as region storage:) Make sure that the plugin and connection lines are commented out, thus: ;

storage_plugin = "OpenSim.Data.SQLite.dll" ; storage_connection_string="URI=file:OpenSim.db,version=3";

Then look for the line that says (; --- To use MySQL storage, supply your own connectionstring (this is only an example):) And uncomment out the plugin and connection lines, thus:

storage_plugin="OpenSim.Data.MySQL.dll"
storage_connection_string="Data Source=localhost;Database=opensim;User ID=root;Password=XXXX;"; (replace the XXXX above with the password you use for MySQL)

Next, go down to the [StandAlone] section, and in the Asset Database provider, make sure that only MySQL is uncommented, thus:

;asset_plugin = "OpenSim.Data.SQLite.dll"
asset_plugin = "OpenSim.Data.MySQL.dll"

User Database provider should all have ONLY the following uncommented, respectively: asset_source = "Data Source=localhost;Database=opensim;UserID=root;Password=XXXX;" inventory_source = "Data Source=localhost;Database=opensim;User ID=root;Password=XXXX;" user_source = "Data Source=localhost;Database=opensim;User ID=root;Password=XXXX;" again, replacing the XXXX with your MySQL password. Do make sure that the SQLite source lines are commented out, thus:

; asset_source = "SQLiteDialect;SqliteClientDriver;URI=file:Asset.db,version=3"
; inventory_source = "SQLiteDialect;SqliteClientDriver;URI=file:Inventory.db,version=3" ; user_source = "SQLiteDialect;SqliteClientDriver;URI=file:User.db,version=3" Save the Opensim.ini file.

You should now be fit to go. Start the opensim server, and all the necessary databases will be created for you by MySQL.

Step 6: Restore your Objects
This step only applies if you previously saved your objects in Step 4.

To restore your objects that were saved in Step 4 above, you use the following commands, and again, assuming that you had two regions called Alpha and Beta.

change-region Alpha
load-oar c:\Alpha.oar

change-region Beta
load-oar c:\Beta.oar

You should now have all your objects back as they were before the migration.