Migrating your Sim to MySQL

Ok, you followed the steps in the previous post to setup a sim, and all is up and running. If you don't plan to running more than 1 region, or have no urge to stuff 45000 prims on it, the SQL lite version OSG comes with will work & perform just fine. Apart from having better performance, running MySQL has a some other slight  advantages. Whenever you need to upgrade your OSG server software, you will have to make an OAR backup of your sim, and import that in the new installation.

With MySQL you have all your stuff stored in a database sever which runs seperate from OSG. This means that you only need to point the new installation to the database, and it will just load all, without you needing to import or export anything. Below the page break you will find a step by step instruction on how to install mySQL, and what to change in the gridcommon.ini file to point OSG towards the correct database.

As its still wise to make a backup OAR file every once and a while, you can read up on how to do that in the Wiki. Its well documented. Hence we will skip this part in the installation below. You DO want to read that wikipage. Many people that just follow shortcut guides and have no interest or clue of what their actually doing, will  run stuck in the long run. Don't let the technical terminology scare you. And mind there are no perfect guides. If you get the bigger picture, the small stuff will fall into place by itself. 


Preparations:
1. download and install the latest version of MySQL
2. Setting up the Opensim database
3. Making a "backup" of your current region.  (upgrade style).
4. Configuring gridcommon.ini

1. Assuming you do not have a MySQL server yet, download the appropriate software for your operating system HERE
2. Just follow the installation wizard and setup with default settings. Please note the points marked below.

• During the installation / database setup you will need to put in a username and password. By default MySQL installs the admin user as Root. Make sure to note down usernames and passwords !
• The setup will ask if it should install workbench and CLI (console) access. You want both. 
• If you choose for Full install, and "Server" as role, you should be fine.  
• You want MySQL to run as a service that starts with windows. 

3. Now MySQL is installed. We need to create a database in it for opensim. No worries, this sounds harder than it is. There is 2 ways you can create one. Via MySQL manager software or workbench or via console.
We will go for the console. Its found under your program files / MySQL folder.
Open the command line interface by doubleclicking the shortcut to it.
type the follwing commands (the ones in yellow), without brackets :

• " mysql -u root -p "   note : If you used a different username to install MySQL use that instead of root.
•  You will now be prompted for a password. Fill in the password you used at installation of MySQL and hit enter.
•  Your now logged in type the nex tline: 
• " Create database opensim; "    ( make sure to include the ; at the end !)
• CREATE USER ‘opensim’@'localhost’ IDENTIFIED BY ‘NewPasswordForUserOpensim’;
• GRANT ALL PRIVILEGES ON opensim.* TO opensim@localhost IDENTIFIED BY ‘NewPasswordForUserOpensim’;
• FLUSH PRIVILEGES;
• "exit" will close console. That was all. 

If this went too fast, click for an instruction video (less than 1,5 minute). Sorry, can't make it harder than that.

Well we you now have a working MySQL database. OSgrid software will create all tables and put the required stuff where it needs to be, so we now only need to tell it to talk to MySQL instead of SQL lite.

Instead of making a backup, we will download a fresh copy from OSgrid website, and install it. Simply put it in a subfolder. c:\opensim\dec2011 or so.Do NOT overwrite your previous install.
Once installed, you need to copy 3 files over from your old install, to the new one :

• Gridcommon.ini ( found in:  bin/config-include folder )
• Opensim.ini ( found in bin folder)
• Regions.ini ( found in bin/regions folder)

Copy and overwrite the files in the new installation.  Now open the gridcommon.ini file with wordpad or notepad++ ( not default notepad, as it is lacking readable layout ).

What to change in gridcommon.ini (see example snippet below):

• You have to uncomment the line in yellow ( put a ;; in front of it),
• Edit the line in red. Remove the ;; in front the red line, and fill in the database username and password you noted for MySQL at the corresponding places.  

[DatabaseService]
 
    ; ;### Choose the DB
    ;;SQLite
    Include-Storage = "config-include/storage/SQLiteStandalone.ini";

    ;; MySql
    ;; Uncomment these lines if you want to use mysql storage
    ;; Change the connection string to your db details
    StorageProvider = "OpenSim.Data.MySQL.dll"
    ConnectionString = "Data Source=localhost;Database=opensim;User ID=root;Password=yourpassword;Old Guids=true;"
    ;; Uncomment this line if you are using MySQL and want to use a different database for estates
    ;; EstateConnectionString = "Data Source=localhost;Database=opensim;User ID=opensim;Password=***;Old Guids=true;"
     ;; -> file has more stuff, no need to change it

Save the file and exit. At startup, your OSgrid software will now connect to MySQL. Your done. That wasn't all that hard was it ?

When started, your server will write the appropriate tabes for MySQL, and boot an empty sim. If you made an OAR file of your previous region, you can load it into your region now. Once loaded It will remain in MySQL forever. This means at every new install, you simply leave the old version where it is, unpack the fresh download in a new folder, and you only need to copy the 3 files like mentioned above. Start it, and your good to go. If anything should fail, you just boot the previous installation. And thus your regions are kept up to date easily.

Since your old SQL lite install is still there, you can still start it. Mind that as it has the same regions file and UUIDS etc, you cannot run the 2 simultaniously. You can however, still start it and pull an OAR if required. This as the full SQL lite database is still contained within the BIN folder ( .db file ) of the old installation.
For the rest, every region you make, any prim you rez, they will be all safely tucked away in your MySQL database.If you want to move your stuff between grids, or want an additional backup next to the MySQL database itself, you can still make OAR's of your regions, and IAR's of your inventory.

If you have sufficient space on your machine, you can keep your old installs in subfolders. Unless you manage to muck the MySQL database, you can that way just boot a previous version if required. Very old stuff you no longer require can just be deleted.

One other things, just for your reference : Other usefull "console commands" for OSG are found HERE Scroll through them some day. (I can remember it's very enlightning to see you can put terrain to 50 mtr with a simple command, especially after 2 days of manual terraforming).