How to use ejabberd with mysql

Posted on 23rd Sep 2018

<-Back to Blogs

1.Ejabberd installation:

We are going to install ejabberd by downloading latest source.
Oo you can download latest ejabberd by simply clonning its git repo:

$ git clone https://github.com/processone/ejabberd.git ejabberd

$ cd ejabberd
Now you need to configure ejabberd to include MySQL.

$ cd ejabberd

$ ./configure --enable-mysql

2.installing MySQL:

You need to install latest Mysql version 5.6 or above to use with ejabberd.

$sudo apt-get install mysql-server

Now start Mysql as follows:

$ mysql start
 

You can connect to the database with your root  user:

$ mysql -uroot -p[password]

3.Create Database and User:

Create a dedicated 'ejabberd' database:

CREATE DATABASE ejabberd;

grant all privileges on ejabberd.* to ejabberd@'localhost' identified by 'ejabberd';

Now new user 'ejabberd' have permission to access database.cYou should be able to connect to MySQL database 'ejabberd' with user 'ejabberd' 

$ mysql -u ejabberd -p -D ejabberd

Enter password of the user.

Now you will see the welcome message along with information about MySQL and your system, like the one below:
 

Welcome to the MySQL monitor.

Your MySQL connection id is 40

Server version: 5.6.33-0ubuntu0.14.04.1 (Ubuntu)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

4.Download and Adapt the MySQL Schema:

You can find the latest version of the MySQL schema in ejabberd Github repository.

You can download it by the command below:

$ wget https://raw.githubusercontent.com/processone/ejabberd/master/sql/mysql.sql

You can load the downloaded schema of ejabberd in your new 'ejabberd' database with the following command:

$ mysql -D ejabberd -u ejabberd -p < mysql.sql

Now you can check the schema by seeing tables of the database ejabberd.

mysql> SHOW TABLES;
it will show you the existing tables in MySQL database.

Now Your MySQL database is ready to connect with ejabberd.

5.Add connection configurations of MySQL to ejabberd configuration file:

In ejabberd.yml configuration file, define your MySQL database parameters as follows:
these parameters are already there in config file,you just need to uncomment and modify them accordingly.

sql_type: mysql

sql_server: "localhost"

sql_database: "ejabberd" 

sql_username: "ejabberd"

sql_password: "password"

## If you want to specify the port:

sql_port: 3306

If you want to store user password in ejabberd, you need to configure ejabberd to use MySQL instead of internal database Mnesia for authentication.

for this You need to change auth_method of ejabberd configuration to replace internal authentication with sql:

auth_method: sql
now by restarting ejabberd, it should connect to your MySQl database for authentication.

6.Switching modules to use MySQL in place of Mnesia:

By far only authentication has been moved to MySQL.For all the modules, ejabberd still use its internal database Mnesia as default.
For each modules which support SQL , you can pass option db_type: sql to use your configured MySQL database.
for example, if you want to store contact list in MySQL, you can do it by changing db_type in ejabberd.yml :

modules:
....
  mod_roster:
    db_type: sql
....
However, if you want to use MySQL as default database for all modules which support MySQL as db_type, you can simply change/add global option as: 

default_db: sql
Note: please note that Mnesia will still be started and used to manage clustering.