Using ejabberd with MySQL native driver

ejabberd can be use with MySQL in native mode instead of the ODBC generic mode. This document describes the necessary steps to get started.

Erlang MySQL native driver

You first need to download the native Erlang MySQL driver.

Copy the MySQL .beam files somewhere in your Erlang path (for example, with your ejabberd .beam files):

  • mysql.beam
  • mysql_auth.beam
  • mysql_conn.beam
  • mysql_recv.beam

MySQL database creation

Before starting ejabberd, you need to create an ejabberd database in MySQL. You probably might already have a MySQL but in case you do not have one instance already running, you can go through the following steps:

Download MySQL archive and uncompress it:

> tar zxvf  mysql-max-4.1.16-pc-linux-gnu-i686-glibc23.tar.gz

Go to the MySQL directory:

> cd mysql-max-4.1.16-pc-linux-gnu-i686-glibc23

Configure the database:

> scripts/mysql_install_db

Start MySQL:

> bin/mysqld_safe &

Create a new user 'ejabberd':

> mysql -h localhost -p -u root -S /tmp/mysql.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.16-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> GRANT ALL ON ejabberd.* TO 'ejabberd'@'localhost'IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

Create a new database 'ejabberd':

> mysql -h localhost -p -u ejabberd -S /tmp/mysql.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.1.16-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE DATABASE ejabberd;
Query OK, 1 row affected (0.00 sec)

Download MySQL ejabberd schema:

> wget https://git.process-one.net/ejabberd/mainline/blobs/raw/2.1.x/src/odbc/m...

Import ejabberd database schema into the ejabberd database:

> mysql -D ejabberd -h localhost -p -u ejabberd -S /tmp/mysql.sock < mysql.sql

Check that the database structure has been correctly created:

> echo "show tables;" | mysql -D ejabberd -h localhost -p -u ejabberd -S /tmp/mysql.sock
Tables_in_ejabberd
last
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
rostergroups
rosterusers
roster_version
spool
users
vcard
vcard_search

ejabberd configuration (1.x and 2.x)

Get the latest ejabberd version:

git clone git://git.process-one.net/ejabberd/mainline.git ejabberd
cd ejabberd
git checkout -b 2.1.x origin/2.1.x

Go to ejabberd source directory:

> cd ejabberd/src

Compile ejabberd:

> ./configure --enable-odbc && make

Use the example config file as a basis:

> cp ejabberd.cfg.example ejabberd.cfg

Comment the following line in ejabberd.cfg:

{auth_method, internal}.

Add the following lines in ejabberd.cfg:

{auth_method, odbc}.
{odbc_server, {mysql, "localhost", "ejabberd", "ejabberd", "password"}}.

Note: The MySQL configuration description is of the following form:

{mysql, Server, DB, Username, Password}

When you have done that user accounts are stored in MySQL. You can define extra informations that you might want to store in MySQL. Change the module used in ejabberd.cfg to change the persistance from the Mnesia database to MySQL:

  • Change mod_last to mod_last_odbc to store the last seen date in MySQL.
  • Change mod_offline to mod_offline_odbc to store offline messages in MySQL.
  • Change mod_roster to mod_roster_odbc to store contact lists in MySQL.
  • Change mod_private to mod_private_odbc to store user private XML storage in MySQL.
  • Change mod_privacy to mod_privacy_odbc to store user privacy lists in MySQL.
  • Change mod_pubsub to mod_pubsub_odbc to store PubSub nodes, items and subscriptions in MySQL.
  • Change mod_vcard to mod_vcard_odbc to store user description in MySQL.
Syndicate content