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 < 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
Comments
0 responses to “Using ejabberd with MySQL native driver”
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
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
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
Dear GP,
I have not experienced that issue. Sorry.
With Best Wishes,
unai
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.