Using ejabberd with MySQL

ejabberd is bundled with native Erlang driver to use MySQL as a backend for persistent storage.

Using MySQL as backend is thus extremely straightforward.

ejabberd installation

ejabberd packages and binary installers contains all the module needed to connect to your MySQL server. You have no extra module to install anymore.

If you are building ejabberd from source, you need to make sure that you configure ejabberd to include MySQL module. It can be done by passing option --enable-mysql to configure script. For example:

cd ejabberd-source
./configure --enable-mysql

MySQL installation

You need a MySQL server that you can point your ejabberd configuration to. The database does not have to be on the same server than ejabberd.

Requirements

ejabberd make use of FULLTEXT indexes with InnoDB. Thus, you need MySQL 5.6 or greater to use with ejabberd.

Note: If you do not store message archive in database however, you can try using older 5.5 version. You may need to adapt MySQL database schema to cope with those older MySQL versions.

MySQL on Linux

This documentation will not get into the details of making MySQL running on Linux for production. It is dependent on Linux distribution and system administrators preferences and habits.

It is also well documented, so it should not be an issue.

Amazon RDS compliance

ejabberd is fully compliant with MySQL on Amazon RDS.

You just need to make sure to use MySQL version 5.6 or greater when you create your database.

Example MySQL installation on OSX with homebrew

For testing / development, it is common to start experimenting with MySQL with Homebrew installation.

Here is how to get started to help with setup up environment.

With homebrew properly installed, you can use the following command to install MySQL:

brew install mysql

You can then follow instruction to finish the installation, for example by running mysql_secure_installation.

You can manually start server with:

mysql.server start

To connect to your local MySQL server using mysql command-line, assuming you kept the default set up, use:

mysql -uroot

To stop it, use:

mysql.server stop

Install with Windows Bash

On Windows you can install MySQL easily like on Linux using Ubuntu Bash:

sudo apt-get install mysql-server-5.6

After configuration, you can start MySQL with:

sudo /etc/init.d/mysql start

You can connect on the database with your created admin password:

mysql -uroot -ppassword

MySQL database creation

Create ejabberd user and database

MySQL admins should use that schema and grant right to a dedicated 'ejabberd' user (replace password with your desired password):

echo "GRANT ALL ON ejabberd.* TO 'ejabberd'@'localhost' IDENTIFIED BY 'password';" | mysql -h localhost -u root

You can then create a dedicated 'ejabberd' database (use password created earlier):

echo "CREATE DATABASE ejabberd;" | mysql -h localhost -u ejabberd -p

You should now be able to connect to 'ejabberd' database with user 'ejabberd' (use password defined on GRANT command):

mysql -h localhost -u ejabberd -p -D ejabberd

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.11 Homebrew

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Download and adapt MySQL schema

The schema files can be found in ejabberd priv directory. MySQL default schema is defined in a file called mysql.sql.

After build and install from source, the SQL schemas are copied here:

  PREFIX/lib/ejabberd-VERSION/priv/sql

After installing ejabberd from binary installer, you can find the SQL schema in similar directory:

  INSTALLDIR/lib/ejabberd-VERSION/priv/sql

Starting from ejabberd 16.03, database schemas are installed in the more usual directory:

INSTALLDIR_OR_PREFIX/share/doc/ejabberd

Finally, you can always find the latest version of the MySQL schema in ejabberd Github repository: mysql.sql

You can download it with command:

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

From there, you can read it and check that it suites your production constrains. You can learn more about the database schema, you can read ejabberd SQL database schema documentation.

Load database schema into your new database

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

mysql -h localhost -D ejabberd -u ejabberd -p < mysql.sql

To make sure all looks fine, you can show the list of SQL tables:

echo "SHOW TABLES;" | mysql -h localhost -D ejabberd -u ejabberd -p --table

mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------------------+
| Tables_in_ejabberd      |
+-------------------------+
| archive                 |
| archive_prefs           |
| caps_features           |
| last                    |
| motd                    |
| muc_registered          |
| muc_room                |
| privacy_default_list    |
| privacy_list            |
| privacy_list_data       |
| private_storage         |
| pubsub_item             |
| pubsub_node             |
| pubsub_node_option      |
| pubsub_node_owner       |
| pubsub_state            |
| pubsub_subscription_opt |
| roster_version          |
| rostergroups            |
| rosterusers             |
| sm                      |
| spool                   |
| sr_group                |
| sr_user                 |
| users                   |
| vcard                   |
| vcard_search            |
| vcard_xupdate           |
+-------------------------+

Your database is now ready to connect with ejabberd.

ejabberd configuration

ejabberd default backend is Mnesia internal database. However, ejabberd is extremely flexible and you can decide to use MySQL instead on a module-by-module basis.

Adding MySQL connection configuration to ejabberd config file

In ejabberd.yml, define your database parameters:

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

Those parameters are mandatory if you want to use MySQL with ejabberd.

Configure desired authentication backend

If you decide to store user password in ejabberd, you need to tell ejabberd to use MySQL instead of internal database for authentication.

You thus need to change ejabberd configuration auth_method to replace internal authentication with sql:

auth_method: sql

If you restart ejabberd, it should connect to your database for authentication. In case it does not work as expected, check your config file syntax and log files (ejabberd.log, error.log, crash.log)

For example, you can create a user in database with ejabberdctl:

/sbin/ejabberdctl register "testuser" "localhost" "passw0rd"

User testuser@localhost successfully registered

You should now be able to connect XMPP users based on MySQL user base.

Switch modules to use MySQL instead of Mnesia

At this stage, only the authentication / user base has been moved to MySQL. For data managed by modules, ejabberd still use internal database as default.

For each modules that support SQL backend, you can pass option db_type: sql to use your configured MySQL database. Switch can be done on a module by module basis. For example, if you want to store contact list in MySQL, you can do:

modules:
  ...
  mod_roster:
    db_type: sql
  ...

However, if you want to use MySQL for all modules that support MySQL as db_type, you can simply use global option default_db: sql:

default_db: sql

Note: even if you move all the persistent data you can to MySQL, Mnesia will still be started and used to manage clustering.

Migrating data from internal database to MySQL

To migrate your data, once you have setup your sql service, you can move most of the data to your database.

You need to take precautions before you launch the migration:

Before you launch migration from internal database, make sure you have made a proper backup.
Always try the migration first on an instance created from your data backup, to make sure the migration script will work fine on your dataset.
Then, when doing final migration, make sure your instance is not accepting connections by blocking incoming connections, for example with firewall rules (block port 5222, 5269 and 5280 as default).

When you are ready, you can:

Connect to a running ejabberd:

   ./ejabberdctl debug

Alternatively, use ejabberdctl live to launch ejabberd with an Erlang shell attached.
Launch the migration command ejd2sql:export/2 from Erlang shell. First parameter is the XMPP domain name you want to migrate (i.e localhost). Second parameter sql tells ejabberd to export to configured MySQL database. For example:

   ejd2sql:export(<<"localhost">>, sql).

You should be set now.

Getting further

To get further you can read the ejabberd Configuration section about Databases.