Using ejabberd with MySQL native driver

Get mysql driver (if ejabberd < 2.0.0)

If you are using an ejabberd version previous to 2.0.0 (about end of 2007) then you need to put the MySQL .beam files somewhere in your Erlang path (possibly with your ejabberd .beam files):

cd /opt/
wget https://support.process-one.net/doc/download/attachments/415/mysql_beam.tar.gz
tar xvfz mysql_beam.tar.gz
cp -v *.beam /var/lib/ejabberd/ebin/

Mysql initialization

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

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

Empty database creation:

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

Schema creation:

cd /tmp
wget http://svn.process-one.net/ejabberd/trunk/src/odbc/mysql.sql
mysql ejabberd &lt; mysql.sql -p
Enter password:

Check that the database structure has been correctly created:

echo "show tables;" | mysql -D ejabberd -uroot -p
Enter password:
Tables_in_ejabberd
last
privacy_default_list
privacy_list
privacy_list_data
private_storage
rostergroups
rosterusers
spool
users
vcard
vcard_search

Ejabberd configuration

If you installed ejabberd from sources, you would probably need to proceed compiling again but using:

./configure --enable-odbc

Comment out 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 information that you might want to store in MySQL. Change the module used in ejabberd.cfg to change the persistence 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_vcard to mod_vcard_odbc to store user description in MySQL.

References


Posted

in

,

by

Comments

5 responses to “Using ejabberd with MySQL native driver”

  1. GP Avatar
    GP

    Hi All,

    I’m facing an intermittent authentication failure issue (error: 401) while doing authentication with remote mysql DB,

    %%
    %% Authentication using ODBC
    %% Remember to setup a database in the next section.
    %%
    {auth_method, odbc}.

    %%
    %% MySQL server:
    %%
    %%{odbc_server, {mysql, “server”, “database”, “username”, “password”}}.
    %%
    {odbc_server, {mysql,”myremoteserverip”, 3306, “ejabberd”,”ejabberd”,”mypassword”}}.

    earlier when I use localhost this problem does not occurred, this problem started when I point to remote mysql db host. We tested with couple of machine re-installing the latest version of ejabberd (2.x) server, but the problem remains. We have multiple ejabberd host and would want to share common mysql db for authentication. Soon would like to use MUC clustering…so wondering that remote mysql db authentication is an issue

  2. unai Avatar

    Dear GP,

    Can you connect to the mysql server from the ejabberd server? You could test by executing the command (on the ejabberd server):

    telnet mysql_ip 3306

    If you are not able to connect, it seems to be a MySQL permissions issue… Have you created the username on the MySQL server and given permissions to the ejabberd server IP?

    unai

  3. GP Avatar
    GP

    Hi Unai,
    As I mentioned the problem is intermittent, some time the authentication goes fine and fails randomly. Would like to know if any one faced the same issue or the issue is with in the our network
    Thanks,
    GP

  4. unai Avatar

    Dear GP,

    I have not experienced that issue. Sorry.

    With Best Wishes,
    unai

  5. Ray Avatar
    Ray

    GP,
    It might be a networking/firewall issue. As “unai”, mentioned, run the telnet command and if that works try logging in from the ejabberd server shell:
    $ mysql -u user123 -h mysql.host.name -p???
    if that works too and you’re able to get into your ejabber db and read table contents then your problem is with ejabberd. To troubleshoot turn on mysql query logging in mysql’s my.cnf file and see what queries ejabberd is sending to the db server.

Leave a Reply

Your email address will not be published. Required fields are marked *