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