In this blog post, I will be looking at how you install MySql as the database engine for OpenSim.
Reasons for using MySql for OpenSim
It’s good to realize that installing MySql as the database engine for OpenSim is completely optional. You don’t need it to run the full version of OpenSim – using MySql doesn’t give you extra functionality or options when you’re in-world. In fact, if you’re running OpenSim just for you, on your own computer, it’s easier and sufficient to use the default SqlLite database engine.
However, if you’re planning to let other people connect to your OpenSim region(s), MySql is going to give you better performance and stability, because it has been designed to deliver data very quickly, for multiple users at the same time. You’re likely to experience delays and instability when you’re using the default database engine.
Please note that you will need to plan your strategy. You can start out small, as an experiment, or for research, by using the default database engine. Later on you can switch to MySql, but there is no easy way to upgrade or move everything inside your sim to the new database environment. When you move to MySql, you will get a new, empty, OpenSim environment.
You will need to download two install files. The first download is for the database engine itself. This is the software that runs the database; it runs invisibly, and once it is installed on your computer or server, it will be started automatically.
The second download is the MySql workbench. It is a tool that allows you to perform administrative tasks on the database engine, such as creating databases, and making backups. To operate OpenSim, you will only need it once: to create a new, empty database, which the OpenSim installation process can use to prepare your OpenSim environment.
Download the MySql database engine (Community Server edition) from this location: http://www.mysql.com/downloads/mysql/.
If your server or PC is running on Windows, download the MSI Installer. For Mac, there is a DMG Archive available for both Mac OS X 10.5 and 10.6. Linux users can choose a version that is specific for their Linux distribution.
Regardless of your operating sytsem, you will notice 32-bit and 64-bit versions of the software. The 64-bit version will only work for you, if you have a 64-bit operating system. The difference between a 32-bit and 64-bit operating systems is the way they access the memory of your computer and the amount of memory they can use. If you don’t know if your computer’s operating system is 32 or 64-bit, you can safely choose to download and install the 32-bit version of MySql.
Download the MySql workbench tool from this location: http://www.mysql.com/downloads/workbench/. For Windows, get the 32-bit MSI installer. For Mac, select the 32-bit DMG Archive. There are no 64-bit versions are available for this software.
Note on downloading: the mysql.com website will ask you to register, but this is optional. There is a “No thanks, just take me to the downloads” link which allows you to go straight to the downloads, without registering.
Start the first installer file to install the database engine. You do not need any special installation procedure or settings, so you can use all default settings that are presented during the installation procedure. At the end of the installation, the installer needs you to specify a root account and password. You will use this login information later on in the MySql workbench, to connect to the database engine.
Now start the second installer file, to install the MySql workbench. The installation is very straightforward, but once you start the workbench tool, you will need to do some configuration.
Registering a new server instance
First, you need to let the workbench know about the database engine. You do this by registering a new server instance in the workbench. Click on ‘New Server Instance’ in the Server Administration section on the right.
Then complete the following steps:
- Specify the host machine by selecting ‘localhost’ (which means that you’re referring to the database engine that runs on the same computer), and click Next.
- The next step is titled ‘Set the database connection values’. Use the default values, but store the password by clicking on the ‘Store in vault’ button. Fill in the password you entered for the root account before and click Next.
- Enter the password once again and click Next.
- In the next step, entitled ‘Test host machine settings’, this message may appear: File doesn’t exist. This is okay, you can click Next.
- The next step shows a review of the installation steps. Click Next.
- In the last step ‘Create the Instance Profile’, click Finish.
Creating the empty database
On the workbench home screen, in the SQL Development section on the left, there now should be a connection entitled ‘localhost’.
- Double click on the localhost connection to open it. A new SQL Editor tab opens.
- In the Object Browser on the left, you will see a test database. Right-click or Control-click anywhere in the Object Browser and select the menu option Create Schema, or click on the icon I’ve highlighted using a red circle.
- Create a new schema (new database) and give it the name ‘opensim’. Click Apply.
- In the Review the SQL script dialog, click Apply SQL.
- Click Finish, and Close.
- In the Object Browser you will see the new database.
Allowing OpenSim to access your database
Later on you will start OpenSim, so it can start using the MySql database for the first time. However, you will need to give it access to the database first, by creating a login account. In the next section, you will instruct OpenSim to use that login account in order to access MySql.
First, create a new login account:
- In the MySql workbench, click on the home tab to return to the Home screen.
- In the Server Administration section, double click on the @localhost server instance. A new tab opens, allowing you to do database administration.
- Click on the Accounts button. A list of accounts appears. Currently it only contains the root user.
- Click on the Add Account button. Fill in a login name (I suggest you use ‘opensim’) and a password. Remember the login name and password – you will need it later on.
- Click Apply. You will see that the user gets added to the User Accounts list on the left.
Next, give the new account specific rights to access the database (called schema here) you created earlier.
- Click on the Schema Privileges tab. Make sure that the opensim user on the left is still highlighted.
- Click on Add Entry.
- Click the ‘Selected schema’ option, and select the ‘opensim’ schema. Click on OK.
- Back on the Schema Privileges tab, click on the ‘Select ALL’ button, and click Save Changes.
Instructing OpenSim to use the MySql database
The final thing you need to do is tell OpenSim to start using MySql as its database engine. Without this step, OpenSim would use the default file-based database engine, so this is the part where you tie OpenSim and MySql together. In this section, you will be editing two configuration files. These are simple text files, so you can use the text editor of your choice. For Mac users in particular, editing .ini files may seem a bit unusual. However, the following procedure is applicable for any operating system.
- In your computer’s file system, navigate to the file folder where you installed OpenSim.
- Find the file OpenSim.ini in the Bin sub-folder, and open it using your text editor.
- Scroll down, or use the text editor’s Find/Search function to find the following two lines:
storage_plugin = "OpenSim.Data.SQLite.dll" storage_connection_string="URI=file:OpenSim.db,version=3";
- These lines need to be ‘disabled’ because they tell OpenSim to use the default database engine. Disable them by typing a ; (semicolon) in front of them. See the lines outlined in red in the screenshot below.
- A bit further down, find the following lines:
; storage_plugin="OpenSim.Data.MySQL.dll" ; storage_connection_string="Data Source=localhost;Database=opensim; User ID=opensim;Password=*****;"
- These lines tell OpenSim to start using MySql. Enable them by removing the semicolons at the start. See the lines outlined in green in the screenshot below.
- Note the stars/asterisks in the second line. Replace them with the password you created in the previous section.
- Save the file.
- Inside the Bin sub-folder, you will find another sub-folder called config-include.
- Find the file StandAloneCommon.ini in that sub-folder, and open it using your text editor.
- Find this line:
Include-Storage = "config-include/storage/SQLiteStandalone.ini";
- In the same way as before, disable this line by typing a semicolon in front of it. See the line outlined in red below.
- Find the following lines:
; StorageProvider = "OpenSim.Data.MySQL.dll" ; ConnectionString = "Data Source=localhost;Database=opensim; User ID=opensim;Password=***;"
- Enable these lines by removing the semicolons. See the lines outlined in green below.
- In the second line, replace the asterisks with the password you created in the previous section.
You have now taken all steps necessary to run OpenSim with MySql. I recommend verifying that you have completed each step, before you start OpenSim again, especially the configuration changes in the last section.
When you start OpenSim again it will automatically connect to MySql and start adding tables and data to the new database for your regions, avatars and objects.