Database Settings

From OpenSimulator

(Difference between revisions)
Jump to: navigation, search
m (Reverted edits by RyanBoyle (Talk) to last version by Fritigern)
m (Robot: Cosmetic changes)
Line 55: Line 55:
 
''Data Source=[1];Database=[2];User ID=[3];Password=[4];Old Guids=true;''
 
''Data Source=[1];Database=[2];User ID=[3];Password=[4];Old Guids=true;''
  
*[1] The hostname or ip address of database server
+
* [1] The hostname or ip address of database server
*[2] Database name
+
* [2] Database name
*[3] Username for the database above
+
* [3] Username for the database above
*[4] Password for the user above
+
* [4] Password for the user above
  
 
Example: If you have created the database "opensim" in localhost, and then created  
 
Example: If you have created the database "opensim" in localhost, and then created  
Line 70: Line 70:
 
''URI=file:[1],version=3,UseUTF16Encoding=True;''
 
''URI=file:[1],version=3,UseUTF16Encoding=True;''
  
*[1] The path to the database file (relative to the bin/ directory - If there is no file on this path, it will be created automatically)
+
* [1] The path to the database file (relative to the bin/ directory - If there is no file on this path, it will be created automatically)
  
 
Example: If you want to create the database for Inventory Service in the directory "bin/db", it will be:
 
Example: If you want to create the database for Inventory Service in the directory "bin/db", it will be:
Line 81: Line 81:
 
''Server=[1];Database=[2];User Id=[3]; password=[4];''
 
''Server=[1];Database=[2];User Id=[3]; password=[4];''
  
*[1] "servername\instance name" - You can see this string in SQL Server Managerment Studio login dialog(Server:).
+
* [1] "servername\instance name" - You can see this string in SQL Server Managerment Studio login dialog(Server:).
*[2] Database name
+
* [2] Database name
*[3] Username for the database above
+
* [3] Username for the database above
*[4] Password for the user above
+
* [4] Password for the user above
  
 
Example: If you have installed Microsoft SQL Server Express Edition with default settings on your local machine
 
Example: If you have installed Microsoft SQL Server Express Edition with default settings on your local machine
Line 95: Line 95:
  
 
Currently there are several services in OpenSim.
 
Currently there are several services in OpenSim.
*[InventoryService]
+
* [InventoryService]
*[AssetService]
+
* [AssetService]
*[AvatarService]
+
* [AvatarService]
*[AuthenticationService]
+
* [AuthenticationService]
*[UserAccountService]
+
* [UserAccountService]
*[GridUserService]
+
* [GridUserService]
*[FriendsService]
+
* [FriendsService]
*[EstateDataStore]
+
* [EstateDataStore]
*[SimulationDataStore]
+
* [SimulationDataStore]
*[PresenceService]
+
* [PresenceService]
*[AuthorizationService]
+
* [AuthorizationService]
*[GridService]
+
* [GridService]
  
 
You can use different storage for some service by overriding the same propaties in the section for those service. For example, if you are using OpenSim standalone and want to use MySQL 'opensim' in general but MySQL 'opensim2' for Inventory Service and MSSQL for Authentication Service, you'll need to write in StandaloneCommon.ini like that:
 
You can use different storage for some service by overriding the same propaties in the section for those service. For example, if you are using OpenSim standalone and want to use MySQL 'opensim' in general but MySQL 'opensim2' for Inventory Service and MSSQL for Authentication Service, you'll need to write in StandaloneCommon.ini like that:
Line 127: Line 127:
  
 
In this case, Inventory Service will:
 
In this case, Inventory Service will:
*read [DatabaseService]StorageProvider
+
* read [DatabaseService]StorageProvider
*read [DatabaseService]ConnectionString -> read [InventoryService]ConnectionString and override
+
* read [DatabaseService]ConnectionString -> read [InventoryService]ConnectionString and override
  
 
Ahthentication Service will:
 
Ahthentication Service will:
*read [DatabaseService]StorageProvider -> read [AuthenticationService]StorageProvider and override
+
* read [DatabaseService]StorageProvider -> read [AuthenticationService]StorageProvider and override
*raad [DatabaseService]ConnectionString -> read [AuthenticationService]ConnectionString and override
+
* raad [DatabaseService]ConnectionString -> read [AuthenticationService]ConnectionString and override
  
 
Note: SQLite and MSSQL providers don't support some services.
 
Note: SQLite and MSSQL providers don't support some services.

Revision as of 19:56, 3 March 2012


If you want to quick know the walkthrough for database setup, see #Quick Setup.

Structure

On OpenSim 0.7.1 or later, you need to set StorageProvider and ConnectionString for each data services (e.g. region database, asset service, inventory service) in the config files in config-include/. Usually you'll need to edit StandaloneCommon.ini for standalone or GridCommon.ini for grid mode.

In those files, you'll find [DatabaseService] section, which parameters control the parameters of data services in general. Only you have to care about are these two lines below:

StorageProvider = "OpenSim.Data.MySQL.dll"
ConnectionString = "Data Source=localhost;Database=opensim;User ID=opensim;Password=***;Old Guids=true;"

In this case, it will connect to the database which can access by the connection string "Data Source=localhost;Database=opensim;User ID=opensim;Password=***;Old Guids=true;" using the provider module named "OpenSim.Data.MySQL.dll".

Or, you can include another ini files. You'll find a line like that:

Include-Storage = "config-include/storage/SQLiteStandalone.ini";

It means it will go on to read config-include/storage/SQLiteStandalone.ini after reading this ini file. If you open SQLiteStandalone.ini file, you'll find StrageProvider and ConnectionString lines in each sections.

NOTE: There had been another way to connect to MSSQL database, which was using "mssql_connection.ini" in bin/ directory. On OpenSim 0.7.1 or later, it will NOT be read even if you set it properly. Use ConnectionString in *Common.ini instead. For more specific details, see the section #MSSQL.

Database Parameters

Parameter Possible Values Description
StorageProvider "OpenSim.Data.SQLite.dll" (SQLite)
"OpenSim.Data.MySQL.dll" (MySQL)
"OpenSim.Data.MSSQL.dll" (Microsoft SQL Server)
"OpenSim.Data.Null.dll" (disable persistence)
The name of the storage provider modules, which can be found in bin/ folder. The three above are which OpenSim officialy (partially) supports but if you have another providers, you can freely put its name in it. If you choose "OpenSim.Data.Null.dll", OpenSimulator will keep all data in its memory instead of using DBMS.
ConnectionString See #ConnectionString Syntax A string that OpenSim specifies information about a data source. Usually it may include the hostname or ip addresses of database server, database service names, username, schema name and password. The syntax varies between databases.
EstateConnectionString See #ConnectionString Syntax The same as ConnectionString, but it will be used only for Estate Service. (optional)

ConnectionString Syntax

MySQL

Data Source=[1];Database=[2];User ID=[3];Password=[4];Old Guids=true;

  • [1] The hostname or ip address of database server
  • [2] Database name
  • [3] Username for the database above
  • [4] Password for the user above

Example: If you have created the database "opensim" in localhost, and then created the user "opensimuser" and its password is "opensimpassword", it will be:

ConnectionString = "Data Source=localhost;Database=opensim;User ID=opensimuser;Password=opensimpassword;Old Guids=true;"

SQLite

URI=file:[1],version=3,UseUTF16Encoding=True;

  • [1] The path to the database file (relative to the bin/ directory - If there is no file on this path, it will be created automatically)

Example: If you want to create the database for Inventory Service in the directory "bin/db", it will be:

ConnectionString = "URI=file:db/inventory.db,version=3,UseUTF16Encoding=True"

MSSQL

Server=[1];Database=[2];User Id=[3]; password=[4];

  • [1] "servername\instance name" - You can see this string in SQL Server Managerment Studio login dialog(Server:).
  • [2] Database name
  • [3] Username for the database above
  • [4] Password for the user above

Example: If you have installed Microsoft SQL Server Express Edition with default settings on your local machine , created the database "opensim" in localhost, and then created the user "opensimuser" and its password is "opensimpassword", it will be:

ConnectionString = "Server=localhost\SQLEXPRESS;Database=opensim;User Id=opensimuser; password=opensimpassword;"

Using Multiple Storages

Currently there are several services in OpenSim.

  • [InventoryService]
  • [AssetService]
  • [AvatarService]
  • [AuthenticationService]
  • [UserAccountService]
  • [GridUserService]
  • [FriendsService]
  • [EstateDataStore]
  • [SimulationDataStore]
  • [PresenceService]
  • [AuthorizationService]
  • [GridService]

You can use different storage for some service by overriding the same propaties in the section for those service. For example, if you are using OpenSim standalone and want to use MySQL 'opensim' in general but MySQL 'opensim2' for Inventory Service and MSSQL for Authentication Service, you'll need to write in StandaloneCommon.ini like that:

[DatabaseService]
StorageProvider = "OpenSim.Data.MySQL.dll"
ConnectionString = "Data Source=localhost;Database=opensim;User ID=opensim;Password=***;Old Guids=true;"
...
[InventoryService]
ConnectionString = "Data Source=localhost;Database=opensim2;User ID=opensim;Password=***;Old Guids=true;"
...
[AuthenticationService]
...(another parameters for authentication service)...
StorageProvider = "OpenSim.Data.MSSQL.dll"
ConnectionString = "Data Source=(local);Database=opensim;User=opensim;password=********;"

If OpenSim can't find StorageProvider or ConnectionString in each service's section, it will use those in [DatabaseService] section. If OpenSim find each or them, it will use the parameter in each service's section. Thus, then the settings in these sections override those in [DatabaseService] section.

In this case, Inventory Service will:

  • read [DatabaseService]StorageProvider
  • read [DatabaseService]ConnectionString -> read [InventoryService]ConnectionString and override

Ahthentication Service will:

  • read [DatabaseService]StorageProvider -> read [AuthenticationService]StorageProvider and override
  • raad [DatabaseService]ConnectionString -> read [AuthenticationService]ConnectionString and override

Note: SQLite and MSSQL providers don't support some services.

Note: For Estate Service, there is another parameter EstateConnectionString in [DatabaseService]. It is the middle priority between [DatabaseService] and [EstateDataStore] ConnectionStrings. OpenSim reads ConnectionString in [DatabaseService] section first, next EstateConnectionString, finally ConnectionString in [EstateDataService] section and overrides.


Quick Setup

SQLite Walkthrough

By default, OpenSim will automatically create its SQLite database files(*.db) in bin/ directory. You don't need to do anything generally. If you want to create the database files in another directory, you can change by editing ConnectionString in bin/config-include/storage/SQLiteStandalone.ini included by config-include/*Commons.ini. See #SQLite section for the detailed syntax.

MySQL Walkthrough

Download

Download and install "MySQL Community Server" from MySQL Downloads Page. On Linux, you can even fetch "mysql-server" or "mysql-community-server" with your native package manager.

Installing MySQL Server

On Windows: Download MSI and run it. Select "Typical". After installation, it will launch "MySQL Server Instance Configuration Wizard". Select "Standard Configuration". In the next screen, you might be happy later if you check "Include Bin Directory in Windows PATH". In the next screen, provide mysql root password. It will complete settings for mysql and startup its service automatically. See MySQL Documentation for more details.

On Mac OS X: Download DMG package and install it normally. After install the server package, you can even install MySQL Startup Item if you want MySQL to start automatically during system startup. After that, startup your MySQL by following commands. See MySQL Documentation for more details.

 $ cd /usr/local/mysql
 $ sudo ./bin/mysqld_safe
 (enter your password, if necessary)
 (press ctrl-Z)
 $ bg
 (press ctrl-D or enter "exit" to exit the shell)

On Linux: RPM install the downloaded package or install "mysql-server" or "mysql-community-server" with your package manager. For more instructions for installation, see MySQL Documentation. After that, startup your MySQL with following command.

$ /etc/init.d/mysqld start

If you want to startup MySQL server automatically, see 2.10.1.2. Starting and Stopping MySQL Automatically in MySQL 5.5 Reference Manual.

Creating MySQL Database

Now you can configure your MySQL with the command line tools.

$ mysql -u root --password
(enter your password, or if you haven't set password for MySQL server, type "mysql -u root" instead)

You can even use MySQL Command Line Client on Start menu on Windows. After login, create user and database for OpenSim.

mysql> create database opensim;
mysql> use opensim;
mysql> create user 'opensimuser'@'localhost' identified by 'opensimpassword';
mysql> grant all on opensim.* to 'opensimuser'@'localhost';
mysql> quit

Configuring OpenSim

Open StandaloneCommon.ini or GridCommon.ini with your editor. First, comment-out the line for SQLite.

; SQLite
;Include-Storage = "config-include/storage/SQLiteStandalone.ini";

After that, uncomment StorageProvider and ConnectionString property lines in [DatabaseService] section and change the line like that:

; MySql
StorageProvider = "OpenSim.Data.MySQL.dll"
ConnectionString = "Data Source=localhost;Database=opensim;User ID=opensimuser;Password=opensimpassword;Old Guids=true;"

Save and start OpenSimulator. It will access to your new MySQL database instead of the default SQLite databases.

MSSQL Walkthrough

Download

To download Microsoft SQL Server 2008 Express edition: [1] Make sure you download Microsoft SQL Server 2008 Express with Tools ([2]) since you will likely want a nice tool to work with your database. If you already downloaded Microsoft SQL Server before, skip to "Checking Instance Configurations" step.

Installing Microsoft SQL Server

Double-click the downloaded executable and select click Installation and "New SQL Server stand-alone installation or add features to an existing installation". It will start installation process, you only need to click "OK", "Next" or "Install" until you come across the "Feature Selection". In this screen, you need to check "Database Engine Service" and "Management Tools - Basic", then click "Next". In next screen "Instance Configuration", check what is filled in "Named instance:" property - you will later use this later. For example, for default Express Edition, you'll see "SQLEXPRESS". Click "Next". Then go on default until you reach "Server Configuration". In this screen, fill in Account Name and Password field with you windows login name and password. In next "Database Engine Configuration" screen, you will need to enable Mixed Mode. After that provide password, click "Add Current User", and then continue clicking "Next" or "OK" until it completes the installation process. After that, you'll find the entry "SQL Server Managerment Studio" in "Microsoft SQL Server 2008" on the Windows Start menu. Open it and click "connect" to connect to your instance.

Checking Instance Configurations

Right-click on your database server in Management Studio and select Properties, then go to the Security tab and select SQL Server and Windows Authentication mode, click OK. If you have already installed SQL Server before and you find "Windows Authentication mode" enabled instead, change it.

Creating User for Database

Expand the Security node, then right-click the Logins node. Select "New Login..." to open "Login - New" dialog. In this screen, fill "Login name" with the string whatever you like, select "SQL Server authentication", give it a strong password, uncheck "User must change password at next login", and click "OK". In this tutorial, we'll assume you have typed "opensimuser" for Login name, and "opensimpassword" for password.

Creating Database

Right-click on the Databases node and and select "New Database..." to open "New Database" dialog. In "Database name" field, you provide any names for the new database, which we'll assume is "opensim". In "Owner" field, provide the name of newly created database user("opensimuser" in this tutorial). Accept all defaults and click "OK".

Configuring OpenSim

Open StandaloneCommon.ini or GridCommon.ini with your editor. First, comment-out the line for SQLite.

; SQLite
;Include-Storage = "config-include/storage/SQLiteStandalone.ini";

After that, add these line in [DatabaseService] section. As of OpenSim 0.7.1, there is no sample configurations for MSSQL(Microsoft SQL Server), so you need to add them by yourself.

; MSSQL
StorageProvider = "OpenSim.Data.MSSQL.dll"
ConnectionString = "Server=localhost\SQLEXPRESS;Database=opensim;User Id=opensimuser; password=opensimpassword;"

Save and start OpenSimulator. It will access to your new SQL Server database instead of the default SQLite databases.

Personal tools
General
About This Wiki