Connect ejabberd with mysql instead of mnesia

By default Ejabberd uses mnesia database and if you are not familiar with this then ejabberd provides many other database options for you. You can connect with anyone of them in which you are comfortable. The advantage of connecting ejabberd with your familiar database is that you can read data in database and can create your own web services for reporting and other purposes.

I am going to explain here how can we use ejabberd with mysql.

If you are installing ejabberd from source code then you have to pass mysql option when you configure ejabberd. Follow below steps to configure ejabberd source with mysql option.

Step 1.

cd ejabberd-source

./configure –enable-mysql

Step 2.

Now you need mysql server where you can point your ejabberd configuration. ejabberd make use of FULLTEXT indexes with InnoDB. Thus, you need MySQL 5.6 or greater to use with ejabberd.

Step 3.

You can find latest schema of ejabberd database from here.

https://github.com/processone/ejabberd/blob/master/sql/mysql.sql

Now load this database schema into your database.

Step 4.

Now in ejabberd configuration file /etc/ejabberd/ejabberd.yml we have to update mysql configuration.

Change auth method internal to odbc.

auth_method: odbc

And  change below configuration as well.

odbc_type: mysql

odbc_server: “localhost”

odbc_database: “ejabberd”

odbc_username: “ejabberd”

odbc_password: “password”

# If you want to specify the port:

odbc_port: 3306

Now you have configured your ejabberd with mysql. Now create a new admin user and start using ejabberd.

Change modules to use MySQL instead of Mnesia:

Whatever steps we followed so far only ejabberd authentication is moved from mnesia to mysql, other modules are still using internal database. To globally move default database to mysql add below configuration in your ejabberd.yml.

default_db: odbc