How to use ejabberd with mysql

Although ejabberd has its default database called Mnesia but when you want to access this database,you have to learn this new database.so if you have used sql database earlier then it will be easy for you to use mysql with ejabberd.
         For using Mnesia database,you do not need to do anything it comes by default with ejabberd installation but if you want use mysql instead of Mnesia then you need to enable it at the time of ejabberd installation and after installation you have to configure mysql in the configuration file of ejabberd called "ejabberd.yml".
 so we are going to give a step by step guide to make ejabberd working with mysql.
1.ejabberd installation:
we are going to install ejabberd by downloading latest source.
so 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 Mysql version 5.6 or above to use with ejabberd.

$ sudo apt-get install mysql-server-5.6 

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@'%' indentified by 'password'

now new user 'ejabberd' have permission to access database.

now You 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.  Commands end with ; or \g.

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:/p>

$ 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.