In this tutorial we will export our regions from a SQLite database, and import them in a MySQL database, for better overall performance. Now there is a whole lot to tell about databases. Our focus will be on MySQL. If you use a different database, you will need different parameters. All of those are documented on the opensimulator wiki page. There are various ways to migrate data between databases but for this course, since we are only dealing with region/simulator data, we will stick to the tools as facilitated in OpenSimulator. This is because physically migrating data, converting it, and load it to another database can be technically challenging and is only needed when you run a grid or standalone.
* Goals for this class : * learning to prepare setup MySQL & configure an empty database with appropriate permissions * configuring your gridcommon,ini to use MySQL instead of SQL lite * Configuring database parameters
For class, we going to assume you followed the steps in the previous posts to setup a simulator, and all is up and running. If you don't plan to running more than 1 region, or have no need to stuff 30000 prims on it, the SQL lite version OSgrid build comes with will work & perform just fine. But apart from having better performance, running MySQL has a some other slight advantages over SQL lite. One of them being software updates. Whenever you need to upgrade your OSgrid server software, you will have to make an archive OAR backup of your sim, and import that in the new installation, or manually copy over your database files. Which can take time and be quite a hassle or fail altogether leaving you with a wrecked database.
With MySQL you have all your stuff stored in a database sever which runs separate from your OSgrid instance. This means that when you reinstall a region that is on MySQL, you only need to point the new installation to the database and it will just load all data from it, without you needing to import or export anything. Lets take you through a step by step instruction on how to install mySQL, and what to change in the GridCommon.ini file to point your simulator 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, and also listed in next weeks maintenance course. 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.
- -1. download and install the latest version of MySQL
- -2. Setting up the Opensim database
- -3. Creating a user and granting it permissions for the database
- -4. Making a archive of your current region(s).
- -5. Configuring gridcommon.ini
1. Assuming you do not have a MySQL server yet, download the appropriate software for your operating system HERE The installer will automatically install both 32 and 64 bits binary's so don't worry about that.
2. Just follow the installation wizard and setup with default settings. Please note the points marked below.
- - accept license
- - If you choose for Full install, and "Development Server" as role, you should be fine.
- - You do not need the things that require a manual install. When prompted, skip these. Execute.
- - If sample data (selected by default) gives an error downloading. Don't loose sleep over it, we don't need it.
- - At configure screen, leave defaults unless you want to use different local ports (in case you already run a database / plan to run multiple).
- - By default MySQL installs the admin user as Root. You may change this. Make sure to note down usernames and passwords !
- - During the installation / MySQL setup you will be asked to put in a user, with username and password. We don't do that here, we will do that later.
- - You want MySQL to run as a service that starts with windows. (else you manually need to start the database server every time you boot your region).
Click here for a Videotutorial of what you can expect when dong above. (Is a random vid, i'm not going to make one, as there are hundreds of still relevant tutorials on it for any version of windows).
3. Now MySQL is installed. We need to create a database in it for OSgrid. 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. It's found under your program files / MySQL folder. So, If on windows, you goto start / program files / MySQL and open the CLI by clicking the shortcut to it, and logging in with your Root password from setup.
If on Linux type the following commands, 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.
On either OS, you're now logged in, lets create the database you will use:
Create database opensim; ( make sure to include the ; at the end !)
It will report back "database created, 1 row affected", or something similar. Now lets create the user:
Create User ‘opensim’@'localhost’ IDENTIFIED BY ‘NewPasswordForUserOpensim’;
You will need to grant privileges for the database by your user:
Grant All Privileges On opensim.* TO opensim@localhost IDENTIFIED BY ‘NewPasswordForUserOpensim’;
save all to disk with flush:
thats it :) now lets quit the terminal
That was all. If this went too fast, click here for an instruction video, or here or Here for more examples. Well we you now have a working MySQL database. The only thing we still need to do is open the file My.ini, and check some settings. The file is found in (C:\ProgramData\MySQL\MySQL Server 5.x) Open it with Notepad++ and there check if the below settings are present / set as per this example :
[mysqld] max_allowed_packet = 64M
( This value is likely on 1 or 4 MB, but 32 is absolute minimum for OSG, if t is not present, you should add it yourself !)
If you have made changes to this file, you will need to save them, and restart your machine for them to take effect !
At the installation, OSgrid software will create all tables and put the required stuff where it needs to be in the database, as it does with SQL lite, so we now only need to tell it to talk to MySQL instead of SQL lite. We do this in the gridcommon.ini file, located in /bin/config-include/ folder
Before we start doing this, lets install ourselves a fresh clean instance first. So we will download a fresh copy from OSgrid website, and install it. Simply put it in a subfolder. X:\Osgrid\april16 or so. Do NOT delete or overwrite your previous install ! Once installed, you need to COPY (do NOT move) 4 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)
- osslEnable.ini ( found in: bin/config-include folder )
Copy and overwrite the files in the new installation.
4. Now open the GridCommon.ini file with notepad++ [DatabaseService]
; ;### Choose the DB ;;SQLite
comment out the SQlite line below as shown:
;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
Un-comment the two lines below and add your database, user and password you configured above:
StorageProvider = "OpenSim.Data.MySQL.dll" >> enable this line by removing ;; in front of it ) ConnectionString = "Data Source=localhost;Database=opensim;User ID=root;Password=NewPasswordForUserOpensim;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. You're done. That wasn't all that hard was it. If it crashes at startup, check the log. Typically you either forgot to start the SQL server, (you can start the service from "services.mmc") or the database / username credentials are incorrect.
When started, your server will write the appropriate tabes for MySQL, and boot you an empty region. 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 4 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. It is wise to check for changes in the files by comparing them with the new install.
Now 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 instances simultaneously. 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. If you made your OAR's, you can delete the folder if no longer needed. For you, 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. If you stay out of the MySQL, your regions and content will be stored there forever. You can dump the database, load it elsewhere, and connect your simulator against it / have an additional backup. etc.
This concludes this weeks tutorial. Next week we will take a look at Maintenance, logging, server commands, and other things useful to know when maintaining a simulator.