Prepare the MySQL database for the Tigase server

This guide describes how to prepare the MySQL database for connecting the Tigase server to it.

Basic setup

The MySQL database can be prepared in many ways. Most of Linux distributions contain tools which allow you to go through all steps from the shell command line. To make sure, however it works on all platforms the same way I show first how to do it under MySQL command line client.

Configuring from MySQL command line tool

Run the MySQL command line client in either Linux or MS Windows environment and enter following instructions:

1. Create the database for the Tigase server:
   

   mysql> create database tigasedb;
   

2. Add the tigase_user user and grant him access to the tigasedb database. Depending on how you plan to connect to the database (locally or over the network) use one of following commands or all if you are not sure:
   Grant access to tigase_user connecting from any network address.

   mysql> GRANT ALL ON tigasedb.* TO tigase_user@'%'
                   IDENTIFIED BY 'tigase_passwd';
   

   Grant access to tigase_user connecting from localhost.

   mysql> GRANT ALL ON tigasedb.* TO tigase_user@'localhost'
                   IDENTIFIED BY 'tigase_passwd';
   

   Grant access to tigase_user connecting from local machine only.

   mysql> GRANT ALL ON tigasedb.* TO tigase_user
                   IDENTIFIED BY 'tigase_passwd';
   

   For the Tigase server version 4.x additional permissions must be granted for the database user:

   mysql> GRANT SELECT, INSERT, UPDATE ON mysql.proc TO 'tigase_user'@'localhost';
   mysql> GRANT SELECT, INSERT, UPDATE ON mysql.proc TO 'tigase_user'@'%';
   mysql> GRANT SELECT, INSERT, UPDATE ON mysql.proc TO 'tigase_user';
   
   

   And now you can update user permission changes in the database:

   mysql> FLUSH PRIVILEGES;
   

3. Load database schema to initialize the Tigase server database space. First, switch to the database you have just created:
   

   mysql> use tigasedb;
   

   Assuming you run the mysql client in Linux from the Tigase installation directory. If you run the Tigase server all versions below 4.0:

   mysql> source database/mysql-schema.sql;
   

   For the Tigase server version 4.x you have to use proper schema version:

   mysql> source database/mysql-schema-4.sql;
   

   On Windows you have probably to enter the full path:

   mysql> source c:/Program Files/Tigase/database/mysql-schema.sql;
   

   The initialization schema file should be also available locally in database/ directory of your Tigase installation.

Configuring from the Linux shell command line

Follow steps below to prepare the MySQL database:

1. Create the database space for the Tigase server:
   

   mysqladmin -p create tigasedb
   

2. Add the tigase_user user and grant him access to the tigasedb database. Depending on how you plan to connect to the database (locally or over the network) use one of following commands or all if you are not sure:
   Grant access to tigase_user connecting from any network address.

   echo "GRANT ALL ON tigasedb.* TO tigase_user@'%' \
                   IDENTIFIED BY 'tigase_passwd'; \
                   FLUSH PRIVILEGES;" | mysql -u root -pdbpass mysql
   

   Grant access to tigase_user connecting from localhost.

   echo "GRANT ALL ON tigasedb.* TO tigase_user@'localhost' \
                   IDENTIFIED BY 'tigase_passwd'; \
                   FLUSH PRIVILEGES;" | mysql -u root -pdbpass mysql
   

   Grant access to tigase_user connecting from local machine only.

   echo "GRANT ALL ON tigasedb.* TO tigase_user \
                   IDENTIFIED BY 'tigase_passwd'; \
                   FLUSH PRIVILEGES;" | mysql -u root -pdbpass mysql
   

3. Load database schema to initialize the Tigase server (version below 4.0) database space:
   

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

   For the Tigase server version 4.0 and later:

   mysql -u dbuser -p tigasedb < mysql-schema-4.sql
   

   The initialization schema file should be also available locally in database/ directory of your Tigase installation.