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.

29 comments:

  1. Quick question on the necessity of this step...

    Ok, I am re-re-reinstalling OpenSim... I have been generating a more generous pile of assets to include some of the 'free' Linden stock downloadable from their website, plus some other trinkets I have found here and there. (this is quite a process, basically a complete overhaul of the stock OS assets/inventory folders).

    Now, before I boot up OS using the generic SQLite again, and considering I will be installing the web interface and all the other goodies, should I just use the WAMP version of MySQL or should I simply start at step 1 (installing opensim) and make the process even longer?

    Right now, I am doing the research and grabbing the needed files for my test grid setup I am working on with a partner, so this question is sort of 'preventive research' :))

    Great job on these pages, btw... I started to make a similar set of Windoze toots back in v0.4 and gave up after about the 3rd website change :lol:

    ReplyDelete
  2. I could not get this section to work. I have a feeling that the OpenSim.ini has been updated since this was written.

    ReplyDelete
  3. Same issue with MySQL on Apr 18th, 2009 with 0.6.3.8730 version.

    ReplyDelete
  4. I couldn't even install version 5 (or any version) - wouldn't let me passed the password prompt - and yes I was using the correct password.

    ReplyDelete
  5. lhernandez@udc.es27 April 2009 at 00:01

    Same here. This is the log:

    _________________
    2009-04-26 23:57:42,612 INFO - OpenSim.ConfigurationLoader [CONFIG] Reading configuration settings
    2009-04-26 23:57:42,612 INFO - OpenSim.ConfigurationLoader [CONFIG] Reading configuration file C:\OS\OpenSim_TorreHercules\OpenSim.ini
    2009-04-26 23:57:42,627 INFO - OpenSim.Framework.Servers.BaseOpenSimServer [STARTUP]: Beginning startup processing
    2009-04-26 23:57:42,643 INFO - OpenSim.Framework.Servers.BaseOpenSimServer [STARTUP]: Version: OpenSimulator Server 0.6.3.8730

    2009-04-26 23:57:42,643 INFO - OpenSim.OpenSim ====================================================================
    2009-04-26 23:57:42,643 INFO - OpenSim.OpenSim ========================= STARTING OPENSIM =========================
    2009-04-26 23:57:42,643 INFO - OpenSim.OpenSim ====================================================================
    2009-04-26 23:57:42,643 INFO - OpenSim.OpenSim [OPENSIM MAIN]: Running in sandbox mode
    2009-04-26 23:57:42,659 INFO - OpenSim.Region.Framework.StorageManager [DATASTORE]: Attempting to load OpenSim.Data.MySQL.dll
    2009-04-26 23:57:42,659 INFO - OpenSim.Data.MySQL.MySQLEstateStore [REGION DB]: MySql - connecting: Data Source=localhost;Database=opensim;User ID=root;Password=***;
    2009-04-26 23:57:42,737 ERROR - OpenSim.Application [APPLICATION]:
    APPLICATION EXCEPTION DETECTED: System.UnhandledExceptionEventArgs

    Exception: MySql.Data.MySqlClient.MySqlException: Unable to connect to any of the specified MySQL hosts. ---> System.Exception: Se produjo una excepción de tipo 'System.Exception'.
    en MySql.Data.MySqlClient.NativeDriver.Open()
    --- Fin del seguimiento de la pila de la excepción interna ---
    en MySql.Data.MySqlClient.NativeDriver.Open()
    en MySql.Data.MySqlClient.MySqlPool.GetPooledConnection()
    en MySql.Data.MySqlClient.MySqlPool.TryToGetDriver()
    en MySql.Data.MySqlClient.MySqlPool.GetConnection()
    en MySql.Data.MySqlClient.MySqlConnection.Open()
    en OpenSim.Data.MySQL.MySQLEstateStore.Initialise(String connectionString)
    en OpenSim.Region.Framework.StorageManager..ctor(String dllName, String connectionstring, String estateconnectionstring)
    en OpenSim.OpenSimBase.CreateStorageManager()
    en OpenSim.Region.ClientStack.RegionApplicationBase.StartupSpecific()
    en OpenSim.OpenSimBase.StartupSpecific()
    en OpenSim.OpenSim.StartupSpecific()
    en OpenSim.Framework.Servers.BaseOpenSimServer.Startup()
    en OpenSim.Application.Main(String[] args)
    InnerException: System.Exception: Se produjo una excepción de tipo 'System.Exception'.
    en MySql.Data.MySqlClient.NativeDriver.Open()

    Application is terminating: True

    ReplyDelete
  6. lhernandez@udc.es27 April 2009 at 00:41

    Solved!
    1.- Uncomment ths inventory plugin line
    inventory_plugin = "OupenSim.Data.MySQL.dll"
    2.- Uncomment the user database plugin
    userDatabase_plugin = "OpenSim.Data.MySQL.dll"

    3.- (weird, but needed)Substitute "localhost" with the IP address of your machine.

    Now the sim is running...

    ReplyDelete
  7. I had problems installing MySql ver 5.x. (Couldn't start the service). The problem solved using MySql ver 4.1

    In opensim.ini we must uncomment any entry refers to MySql (except 'MySql example' lines of course)

    I didn't change localhost.

    Thnx a lot for these tutorials

    ReplyDelete
  8. Hi sorry can any one me send the ready ?

    OpenSim: bei OpenSim.Data.MySQL.MySQLInventoryData.Initialise(String connect)
    OpenSim: bei OpenSim.Framework.InventoryDataInitialiser.Initialise(IPlugin plugin)
    OpenSim: bei OpenSim.Framework.PluginLoader`1.Load()
    OpenSim: bei OpenSim.Framework.Communications.InventoryServiceBase.AddPlugin(String provider, String connect)
    OpenSim: bei OpenSim.OpenSimBase.Startup()
    OpenSim: bei OpenSim.OpenSim.Startup()
    OpenSim: bei OpenSim.Application.Main(String[] args)
    OpenSim:
    OpenSim: Application is terminating: True
    OpenSim:
    OpenSim:
    OpenSim: Unbehandelte Ausnahme: System.Exception: Error initialising MySql Database: System.Exception: Connection error while using connection string [Data Source=127.0.0.1] ---> MySql.Data.MySqlClient.MySqlException: Access denied for user ''@'localhost' (using password: NO)
    OpenSim: bei MySql.Data.MySqlClient.MySqlStream.OpenPacket()
    OpenSim:
    OpenSim: bei MySql.Data.MySqlClient.NativeDriver.Authenticate411()
    OpenSim: bei MySql.Data.MySqlClient.NativeDriver.Authenticate()
    OpenSim: bei MySql.Data.MySqlClient.NativeDriver.Open()
    OpenSim: bei MySql.Data.MySqlClient.MySqlPool.GetPooledConnection()
    OpenSim: bei MySql.Data.MySqlClient.MySqlPool.GetConnection()
    OpenSim: bei MySql.Data.MySqlClient.MySqlConnection.Open()
    OpenSim: bei OpenSim.Data.MySQL.MySQLManager.Initialise(String connect)
    OpenSim: --- Ende der internen Ausnahmestapelberwachung ---

    mail to: barnibear@hotmail.de

    Big thx

    ReplyDelete
  9. Hi,
    I have just migrate to MySQL my standalone simple server OpenSim (one region, no object, two users). Not that I'm new in OpenSim.
    I use version 0.6.6 for the server and Hippo OpenSim viewer.
    All is OK, but just a strange thing.
    The first user that I create during the first install is KO.
    When I try to login with, the following message arrive :
    "Login failed.
    The avatar inventory service is not responding. Please notify your login region operator."

    Note that this user is realy in the new database in MySQL.

    If I Create a new different user all is OK.

    It's not possible to delete (directly in database) and manually re-create the first user because this user is automaticaly re-create at each server restart.

    In the OpenSim.ini I modify all where the database is notify, not only where the tuto said to do this : perhaps it's my error ?
    Thanks for your help.

    ReplyDelete
  10. I'm not sure how long this has been going on as I haven't added a user to my Virtual world in months. But I'm getting the avatar inventory service error on any new account and any old one that never actually got in world.
    I've been with mysql from the start and use the wamp setup. Any suggetions?

    ReplyDelete
  11. I'm actually moving this to the web interface tutorial as I have discovered that manually created members do NOT get the "Avitar Inventory Service" error

    ReplyDelete
  12. This comment has been removed by the author.

    ReplyDelete
  13. i have problem - i couldn't get this working - its needs updated to relfect the changes in opensim INI because mine kept crashing saying somthing has do with Mysql
    because this tutorial is out of date its needs changed to reflect the changes

    btw e-mail me to if you want to help me out
    rossifan2007@hotmail.co.uk

    ReplyDelete
  14. Hello. Same problem with avatar. Is not created. I have no solution for now. How to make? TY!

    ReplyDelete
  15. having the same problem as lhernandez@udc.es. I tried changing 'Localhost' to the computer's IP, and had already uncommented the other two MySQL files, but im still getting the error.

    ReplyDelete
  16. I ran into the same problem. But fixed it :)
    Try not to use default (standard) installation, but choose custom. There select role server (or dedicated), and choose for a high traffic server. I think the 20 connections on default is terminating the connection. OS needs more.
    Worked for me, good luck.

    ReplyDelete
  17. lmao i got a headace when looking at the opemsim.ini

    ReplyDelete
  18. I just did this without too many problems. The directions specify changing fields that do not exist in the OSgrid pre-packaged zip file.

    Here are the corrections:
    Step 1: Choose the .MSI installer, not the .ZIP extraction. (The .zip extraction gets you the source. The .msi extension is for Windows installation.) It's a good idea to run the installation file through your anti-virus software BEFORE you install it.

    Step 3: I used separate databases for my local grid and for OSgrid. This is probably not necessary, but it's what I did.

    Step 4: If you have a local grid and choose to use MySQL for that, make sure you save your user information in .iar files.

    Step 5: The only file to be modified is "config-include\GridCommon.ini". Comment out the SQLite Include-Storage line and comment in the MySql lines as in the description. There is no standalone section, no asset_plugin, nor any inventory_source line to be found in any of the configuration files.

    ReplyDelete
  19. Which is the best username to search for when looking for good guitar tutorials on youtube?

    ReplyDelete
  20. How to incorporate MySQL or other open source database instead of Postgres SQL or EnterpriseDB?

    ReplyDelete
  21. cant you just do this for us and upload the file for us to download?

    ReplyDelete
  22. hmm. now i have problems with it.i did as 7 Dec. 2010 Anonymous said, and restored a original Opensim.ini file to bin, and edited standalonecommon.ini in config-include (i looked at gridcommon.ini but it didnt have anything relevant) and commented out sqlite, and commented in mysql. now i get this error-
    APPLICATION EXCEPTION DETECTED: System.UnhandledExceptionEventArgs

    Exception: System.Exception: Unable to proceed. Please make sure your ini files in config-include are updated according to .example's
    at OpenSim.Region.CoreModules.ServiceConnectorsOut.Inventory.LocalInventoryServicesConnector.Initialise(IConfigSource source)
    at OpenSim.ApplicationPlugins.RegionModulesController.RegionModulesControllerPlugin.Initialise(OpenSimBase openSim)
    at OpenSim.ApplicationPluginInitialiser.Initialise(IPlugin plugin)
    at OpenSim.Framework.PluginLoader`1.Load()
    at OpenSim.Framework.PluginLoader`1.Load(String extpoint)
    at OpenSim.OpenSimBase.LoadPlugins()
    at OpenSim.OpenSimBase.StartupSpecific()
    at OpenSim.OpenSim.StartupSpecific()
    at OpenSim.Framework.Servers.BaseOpenSimServer.Startup()
    at OpenSim.Application.Main(String[] args)

    Application is terminating: True


    can someone help me?

    my email is arklelinuke@gmail.com

    thanks in advance

    ReplyDelete
  23. ok is there any tutorials on making REALLY COOL SIM HOUSES only on sims2 with nooo expantions??

    ReplyDelete
  24. This must change from release to release, I had to change StandaloneCommon.ini to use mysql

    I am also running it on OSC Lion and had to use Mono version 2.6.4 it wouldn't work with a later release.

    Next is to run in grid mode and plug in to somewhere like OSGrid

    ReplyDelete
  25. How can i run a php or mysql script automatically every month, quarter or year?

    ReplyDelete
  26. Is there a FREE c tutorial that assumes no prior knowledge of coding/terminology?

    ReplyDelete
  27. Where can i find a tutorial on how to styllise a picture in corel picture using transparency tool for example?

    ReplyDelete
  28. Hi guys whwn using opensim version 0.7.6 and trying to change the data base to MYSQL I get an error. please see the lines below.
    2014-03-27 12:44:45,635 INFO - OpenSim.Framework.Servers.ServerBase [SERVER BASE]: Starting in C:\opensim-0.7.6\bin
    2014-03-27 12:44:45,635 INFO - OpenSim.Framework.Servers.ServerBase [SERVER BASE]: OpenSimulator version: OpenSim 0.7.6 Release
    2014-03-27 12:44:45,635 INFO - OpenSim.Framework.Servers.ServerBase [SERVER BASE]: Operating system version: Microsoft Windows NT 6.1.7601 Service Pack 1, .NET platform Win32NT, 64-bit
    2014-03-27 12:44:45,650 INFO - OpenSim.Framework.Servers.ServerBase [SERVER BASE]: Logging started to file C:\opensim-0.7.6\bin\OpenSim.log
    2014-03-27 12:44:45,931 ERROR - OpenSim.Services.Base.ServiceBase [SERVICE BASE]: Failed to load plugin OpenSim.Region.Framework.Interfaces.ISimulationDataStore from OpenSim.Data.MySQL.dll with args Data Source=localhost;Database=opensim;User ID=opensim;Password=***;Old Guids=true;
    System.Reflection.TargetInvocationException: Het doel van een aanroep heeft een uitzondering veroorzaakt. ---> MySql.Data.MySqlClient.MySqlException: Access denied for user 'opensim'@'localhost' (using password: YES)
    bij MySql.Data.MySqlClient.MySqlStream.ReadPacket()
    bij MySql.Data.MySqlClient.NativeDriver.AuthenticateNew()
    bij MySql.Data.MySqlClient.NativeDriver.Open()
    bij MySql.Data.MySqlClient.Driver.Open()
    bij MySql.Data.MySqlClient.Driver.Create(MySqlConnectionStringBuilder settings)
    bij MySql.Data.MySqlClient.MySqlPool.GetPooledConnection()
    bij MySql.Data.MySqlClient.MySqlPool.TryToGetDriver()
    bij MySql.Data.MySqlClient.MySqlPool.GetConnection()
    bij MySql.Data.MySqlClient.MySqlConnection.Open()
    bij OpenSim.Data.MySQL.MySQLSimulationData.Initialise(String connectionString)
    --- Einde van intern uitzonderingsstackpad ---
    Can Anyone please help me get this to work?
    Any help please send to: jankromhoutvandermeer@gmail.com

    ReplyDelete
  29. Nice comment!!!!!!!!
    Thanks for sharing this with us..
    6 Months Internship Programs MySQL

    ReplyDelete