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.

35 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
    Replies
    1. Opensim Tutorials: 04. Migrating To Mysql >>>>> Download Now

      >>>>> Download Full

      Opensim Tutorials: 04. Migrating To Mysql >>>>> Download LINK

      >>>>> Download Now

      Opensim Tutorials: 04. Migrating To Mysql >>>>> Download Full

      >>>>> Download LINK SF

      Delete
  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
  30. The most effective method to Solve MySQL Database Backup Problem through DB Recovery Support
    Well! Taking reinforcement of any database isn't a spoon encouraging assignment. On the off chance that you have some specialized abilities or information with respect to this then you can without much of a stretch take the reinforcement of your MySQL or some other database. Be that as it may, without having specialized abilities it wind up inconceivable that is the reason for this situation you need to get associated with most experienced and committed specialized specialists who settle this reinforcement issue. To determine your reinforcement issue you can contact to DB Recovery Services or Exchange Database Recovery. Here we give Online Database Management Support to our top notch clients to overcome of their concern.

    #DatabaseRecovery #DBARemoteExperts
    #dbrecoverysupport #databasesupport
    #dbrecoveryservices #databaseconfigurationsupport
    #databasemanagementrecoverysupport


    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    ReplyDelete
  31. Not able to Solve MySQL Connection issue? Contact to MySQL Technical Support
    The MySQL association issue happens when utilizing an old client and secret key. At whatever point you are attempting to login it will be obstructed with message which demonstrates that "customer does not bolster verification convention asked for by server" ensure, on the off chance that you need to settle this association issue at that point promptly contact to MySQL Remote Support and MySQL Remote Service.
    Here we give a few stages with the goal that you can physically take care of this issue. To begin with you need to get to the database through charge line and run this illustration: SET PASSWORD FOR 'root '@'localhost' = OLD_PASSWORD ('PASSWORD')
    On the off chance that as yet confronting a similar issue, at that point runs this line: UPDATE mysql.user SET Password = OLD_PASSWORD ('PASSWORD') WHERE User = 'Client '; flush benefits. For speedy arrangement you can contact to our expert specialists and get best help.
    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    ReplyDelete
  32. Top website development company in Brampton
    Top website design company in Toronto
    Developing from a group of specialists to an undeniable top SEO office in Canada, we have confidence in persistently redesigning ourselves with the goal that we can offer the most recent types of assistance to our customers.

    ReplyDelete
  33. Opensim Tutorials: 04. Migrating To Mysql >>>>> Download Now

    >>>>> Download Full

    Opensim Tutorials: 04. Migrating To Mysql >>>>> Download LINK

    >>>>> Download Now

    Opensim Tutorials: 04. Migrating To Mysql >>>>> Download Full

    >>>>> Download LINK 8A

    ReplyDelete