MySQL database use

This guide describes how to configure Tigase server to use MySQL database as user repository.

If you used XML based user repository before you can copy all user data to MySQL database using repository management tool. All steps are described below.

MySQL database preparation

To load db schema to your MySQL instance first create database:

mysqladmin -p create tigase

And then you can load database schema:

mysql -u dbuser -p tigase < mysql-schema.sql

Server configuration.

Now you have to change configuration to load jdbc module instead of XML based repository. Using configuration management script, first change class name handling repository.
To see current settings run command:

 $ ./scripts/config.sh -c tigase-config.xml -print -key session_1/user-repo-class

As a result you should see something like:

session_1/user-repo-class = tigase.db.xml.XMLRepository

You can see that current setting points to XML repository implementation. To use jdbc module for connection to MySQL database you have to set tigase.db.jdbc.JDBCRepository class (enter text below in one line):

 $ ./scripts/config.sh -c tigase-config.xml -print
      -key session_1/user-repo-class -value tigase.db.jdbc.JDBCRepository -set

As a result you will see new value set for the parameter:

session_1/user-repo-class = tigase.db.jdbc.JDBCRepository

You have also to set the same value as authorization repository unless you want to use different authorization data source:

 $ ./scripts/config.sh -c tigase-config.xml -print
      -key session_1/auth-repo-class -value tigase.db.jdbc.JDBCRepository -set

And again as a result we can see:

session_1/auth-repo-class = tigase.db.jdbc.JDBCRepository

Next step is to set database connection string. Assuming you have database: tigase on localhost with database user: dbuser and password dbpass your connection string will look like this:

jdbc:mysql://localhost/tigase?user=dbuser&password=dbpass

To set this in your configuration file you have to call again configuration management script 2 times. First for user data repository and second for authorization data repository:

 $ ./scripts/config.sh -c tigase-config.xml -print -key session_1/user-repo-url 
       -value "jdbc:mysql://localhost/tigase?user=dbuser&password=dbpass" -set
 $ ./scripts/config.sh -c tigase-config.xml -print -key session_1/auth-repo-url 
       -value "jdbc:mysql://localhost/tigase?user=dbuser&password=dbpass" -set

Note quotes around connection string. They are needed to make sure shell won't interpret special characters.

Now your configuration is ready to load jdbc module and connect to your database.

One more thing you need to do is to tell JVM which jdbc driver to use to connect to database. Depending on your MySQL and jdbc installation it might be: com.mysql.jdbc.Driver. To set is as database driver you have to set is a jdbc.drivers property value. Usually you do this by adding -D parameter to Java call:

 $ java -Djdbc.drivers=com.mysql.jdbc.Driver tigase.server.XMPPServer

If you use tigase.sh script to run server you have to add -Djdbc.drivers=com.mysql.jdbc.Driver to startup script property file to JAVA_OPTIONS values.

User data import

If you previously used XML based user repository you can import all data into MySQL database using repository management tool. This is quite long command so let me list all required parameters first with brief explanation:

1. -cp copy content of the source repository to destination repository.
2. -sc tigase.db.xml.XMLRepository source repository class.
3. -su user-repository.xml source repository connection string - assuming your user repository is in user-repository.xml file.
4. -dc tigase.db.jdbc.JDBCRepository destination repository class.
5. -du "jdbc:mysql://localhost/tigase?user=dbuser&password=dbpass" destination repository connection string.

And now whole command. Enter all in one line:

 $ ./scripts/repo.sh -cp -sc tigase.db.xml.XMLRepository -su user-repository.xml
      -dc tigase.db.jdbc.JDBCRepository
      -du "jdbc:mysql://localhost/tigase?user=dbuser&password=dbpass"

For more information how to use command line administration tools refer to command line tools guide.