Building an Ejabberd Server with MySql

Yesterday I was upgrading my OpenFire server and thought it might be fun to learn something new and switch to a different server software. After doing some research, I decided upon ejabberd since that one seems to be a popular solution (not to mention that specs of course).

I keep my jabber data in a MySql database and I don't really want to migrate away from that. That being said, I had a really difficult time finding any complete documentation on how to configure an ejabberd server to work with a MySql database. Here's how I did it.

Firstly, you of course need to grab said bin here. Once you have extracted and installed, you'll need to edit your config file (conf/ejabberd.cfg). You'll see a section in the middle (or so) that looks like %%%  ============== %%%   AUTHENTICATION

%% %% auth_method: Method used to authenticate the users. %% The default method is the internal. %% If you want to use a different method, %% comment this line and enable the correct ones. %% {auth_method, internal}.

%% %% Authentication using external script %% Make sure the script is executable by ejabberd. %% %%{auth_method, external}. %%{extauth_program, "/path/to/authentication/script"}.

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

Comment out the internal auth method line %%{auth_method, internal}. Now, skip down to the line and uncomment the odbc auth method. {auth_method, odbc}.

Lastly in the config file, we need to configure our database connection string. Head on down to the following location, uncomment the first odbc_server line and fill in your database connection information. %% %% MySQL server: %% {odbc_server, {mysql, "MySqlServer", "MySqlDatabase", "MySqlUsername", "MySqlPassword"}}.

It's at this point that you might be thinking to yourself, "...but I don't have a database or tables configured". This is the part where I initially got stuck. All of the documentation I found pointed towards a sql file that could be found in the source code. Other sources indicated that ejabberd needs to be compiled with mysql support for this all to work. Thankfully, this is not the case (as per my experience at least). I can't say this about the deb or the rpm installs, but the gzipped binary at least has this.

If you go into the install location and navigate on down to &lt;ejabberd-home&gt;/lib/ejabberd-2.1.8/priv/odbc/mysql.sql and run the mysql file in there on the database you have created, you will find yourself with a completely empty database structure (but a structure none the less).

Finally, we have to go back and make a few more simple changes to our conf file. The config file references several modules that store their data to the internal database, unless otherwise specified. We are going to otherwise specify here.

Crack open that config file again located at conf/ejabberd.cfg Navigate down to the section that looks like the following (I won't put the whole thing in here since it's a big section)

%%%  ======= %%%   MODULES

%% %% Modules enabled in all ejabberd virtual hosts. %%

Here you'll find a lot of lines starting with mod_. These are all the modules your ejabberd instance will load on startup. There are several in here that we need to add _odbc to the end of to make them talk to our MySql database instead of the internal database. Find the following listed modules and add _odbc to them (I've already done that in my list) {mod_last_odbc,    []}, {mod_offline_odbc, []}, {mod_privacy_odbc, []}, {mod_private_odbc, []}, {mod_pubsub_odbc,  [ % requires mod_caps ... {mod_roster_odbc,  []}, {mod_vcard_odbc,   []},

And finally, we're done. On a side note, you might want to uncomment the module mod_65[] to enable file transfers. You never know when you'll need to transfer a big file.