ejabberd: we migrate from mnesia to mysql

As I use the xmpp of the ejabberd server, I observe an extreme lack of documentation. Every sneeze slightly deviating from the standard needs has to be invented by oneself, or collected piece by piece from conferences, mailing lists, comments in svn and the source itself.

With the release of ejabberd 2.1.11, in which developers added ODBC support in new modules , I was faced with the problem of transferring data from the regular mnesia database to a more suitable mysql for my tasks. I once solved it for other modules, but only remotely remember how. Now I will try to recall and document the process for the future of myself and all who might be interested.


')
So, you have ejabberd 2.1.11, which uses its standard mnesia database as a repository, and you don't want to decide anything you want to want mysql.

First, create a database. We take the structure for it here or in the file /src/odbc/mysql.sql in the source package.

After that, you need to export data from mnesia. We will need the mod_admin_extra module included in ejabberd-modules .

Downloading:
svn co svn.process-one.net/ejabberd-modules

Since mod_admin_extra has not been updated for a long time, it knows nothing about new modules and therefore new tables. I solved the problem clumsily: I opened the source of ejabberd-modules / mod_admin_extra / trunk / src / mod_admin_extra.erl and a piece

 
               {export_last, last},
               {export_offline, offline},
               {export_passwd, passwd},
               {export_private_storage, private_storage},
               {export_roster, roster},
               {export_vcard, vcard},
               {export_vcard_search, vcard_search}], 

replaced by

               {export_last, last},
               {export_offline, offline},
               {export_passwd, passwd},
               {export_private_storage, private_storage},
               {export_roster, roster},
               {export_vcard, vcard},
                 {export_motd, motd},
                 {export_motd_users, motd_users},
                 {export_muc_registered, muc_registered},
                 {export_muc_room, muc_room},
                 {export_sr_group, sr_group},
                 {export_sr_user, sr_user},
               {export_vcard_search, vcard_search}], 

Note: I do not use irc transport, as I prefer warm tube irc clients, and mod_irc is off for me. If you use it, you will probably need to add something like '{export_irc, irc},' to the list. You can find out exactly by making ls /var/spool/jabber/*.DCD and getting the table names in mnesia as output.

We collect (need erlang):

cd ejabberd-modules/mod_admin_extra/trunk
./build.sh

Note : more detailed assembly instructions can be found on the ejabberd-modules home page .

Similarly, we build ejabberd-modules / mysql .

We throw the resulting ejabberd-modules / mod_admin_extra / trunk / ebin / mod_admin_extra.beam and ejabberd-modules / mysql / trunk / ebin / *. Beam to ebin in the ejabberd installation (I have this /usr/lib/erlang/lib/ejabberd-2.1 .11 / ebin / ).

We cut off the ejabberd server from the network for reliability and restart (but do not stop!).

Create a writable folder for the user who is running ejabberd (I have this jabber: jabber, but I just did mkdir / tmp / temp; chmod 777 / tmp / temp).

Exporting data from mnesia ( - jabber-):
ejabberdctl export2odbc /tmp/test/
txt sql-, mysql. ( phpMyAdmin). , - .
- mnesia, . , - 14 .

, .

, !

ejabberd mysql. ( ) .

,
{auth_method, odbc}

{odbc_server, {mysql, "host", "db", "user", "pass"}}.
,
{host_config, "domain2", [{odbc_server, {mysql, "host2", "db2", "user2", "pass2"}}]}.

, XXX->XXX_odbc, :
mod_announce_odbc
mod_blocking_odbc
mod_last_odbc
mod_muc_odbc
mod_offline_odbc
mod_privacy_odbc
mod_private_odbc
mod_pubsub_odbc
mod_roster_odbc
mod_shared_roster
mod_vcard_odbc
mod_vcard_xupdate_odbc



, , 2009 , , - . . , , . - jabber-):
ejabberdctl export2odbc /tmp/test/
txt sql-, mysql. ( phpMyAdmin). , - .
- mnesia, . , - 14 .

, .

, !

ejabberd mysql. ( ) .

,
{auth_method, odbc}

{odbc_server, {mysql, "host", "db", "user", "pass"}}.
,
{host_config, "domain2", [{odbc_server, {mysql, "host2", "db2", "user2", "pass2"}}]}.

, XXX->XXX_odbc, :
mod_announce_odbc
mod_blocking_odbc
mod_last_odbc
mod_muc_odbc
mod_offline_odbc
mod_privacy_odbc
mod_private_odbc
mod_pubsub_odbc
mod_roster_odbc
mod_shared_roster
mod_vcard_odbc
mod_vcard_xupdate_odbc



, , 2009 , , - . . , , .

---

- jabber-):
ejabberdctl export2odbc /tmp/test/
txt sql-, mysql. ( phpMyAdmin). , - .
- mnesia, . , - 14 .

, .

, !

ejabberd mysql. ( ) .

,
{auth_method, odbc}

{odbc_server, {mysql, "host", "db", "user", "pass"}}.
,
{host_config, "domain2", [{odbc_server, {mysql, "host2", "db2", "user2", "pass2"}}]}.

, XXX->XXX_odbc, :
mod_announce_odbc
mod_blocking_odbc
mod_last_odbc
mod_muc_odbc
mod_offline_odbc
mod_privacy_odbc
mod_private_odbc
mod_pubsub_odbc
mod_roster_odbc
mod_shared_roster
mod_vcard_odbc
mod_vcard_xupdate_odbc



, , 2009 , , - . . , , .