Install and configure MariaDB with ejabberd

By default, ejabberd uses the Mnesia internal database. It is great for home and small office environments, but in larger companies, as the amount of chat logs and users grows, we need more scalability. Today, I will show you how to install MariaDB, a MySQL-compatible database, migrate your data and configure ejabberd to use MariaDB instead of Mnesia.

» Don’t want to migrate data yourself?
ProcessOne experts will make your communication scalable. Contact us »

MariaDB with ejabberd

Installing MariaDB

We assume the usual Debian configuration as in my previous tutorials. I have updated my ejabberd to version 21.01 (the update process is the same as the initial ejabberd installation, so check my first tutorial).

To install MariaDB simply use:

apt-get install mariadb-server

Then run the installation wizard and follow the instructions:

mysql_secure_installation

Preparing MariaDB for ejabberd

To get MariaDB ready for ejabberd, we need to create a new database, its user, and then populate the database with the ejabberd SQL schema.

First, let’s create the database using your MariaDB root user:

echo "CREATE DATABASE ejabberd;" | mysql -h localhost -u root -p

Next, let’s create a dedicated ejabberd user authenticated with a password, and assign it to this database. The Enter password prompt is again asking about the root MariaDB user:

echo "GRANT ALL ON ejabberd.* TO 'ejabberd'@'localhost' IDENTIFIED BY 'password';" | mysql -h localhost -u root -p

Finally, let’s download the latest ejabberd SQL schema and load it into our database. This time, we are switching to using the ejabberd MariaDB user, and the Enter password prompt is asking for the password we just specified in the GRANT command above:

wget https://raw.githubusercontent.com/processone/ejabberd/master/sql/mysql.sql
mysql -h localhost -D ejabberd -u ejabberd -p < mysql.sql

To verify that everything is correct, run a command to display all the database tables, again using the ejabberd MariaDB user, and the output should look something like that:

echo "SHOW TABLES;" | mysql -h localhost -D ejabberd -u ejabberd -p --table
Enter password: 
+-------------------------+
| Tables_in_ejabberd      |
+-------------------------+
| archive                 |
| archive_prefs           |
| bosh                    |
| caps_features           |
| last                    |
| mix_channel             |
| mix_pam                 |
| mix_participant         |
| mix_subscription        |
| motd                    |
| mqtt_pub                |
...

Configuring ejabberd for MariaDB

Now that our MariaDB tables are ready, we need to configure ejabberd to use this MySQL-compatible database. Edit your ejabberd.yml config and add the following settings, where password refers to the ejabberd MariaDB user:

sql_type: mysql
sql_server: "localhost"
sql_database: "ejabberd"
sql_username: "ejabberd"
sql_password: "password"

Migrating Mnesia data to MariaDB database

At this point, if you restart your ejabberd, it won’t be using MariaDB just yet. Let’s first migrate Mnesia data into our new SQL database using ejabberdctl – we first export the data into a mnesia.sql file, and then we import it into the MariaDB database:

cd /opt/ejabberd-21.01/bin/
./ejabberdctl export2sql marekfoss.org /tmp/mnesia.sql
mysql -h localhost -D ejabberd -u ejabberd -p < /tmp/mnesia.sql
rm /tmp/mnesia.sql

It’s a good practice to remove the /tmp/mnesia.sql after we are done with it. Now, add default_db: sql and auth_method: sql to your ejabberd.yml configuration file:

default_db: sql
auth_method: sql

sql_type: mysql
sql_server: "localhost"
sql_database: "ejabberd"
sql_username: "ejabberd"
sql_password: "password"

Then, restart your ejabberd instance – it will now use the MariaDB database! Please note that the Mnesia database will still be started up and used for non-persistent data and clustering.

In this ejabberd tutorial series:

Photo by Amy Asher on Unsplash


Let us know what you think 💬


16 thoughts on “Install and configure MariaDB with ejabberd

  1. Very well done!
    This article let me switch from Mnesia DB to MariaDB without any hassles.
    This former article (https://docs.ejabberd.im/tutorials/mysql/) gives different information and I was
    not able to succeed with the instructions given there.

    Chapeau, Martin!

    One short question: after migrating the data is there any way to delete the (duplicate) data from Mnesia DB, something like restoring to a virgin (clean) state?

    • To delete content from the Mnesia tables, there is a command called delete_mnesia. but it doesn’t work for me right now https://github.com/processone/ejabberd/issues/3564

      Alternative: the page in ejabberd WebAdmin -> Nodes -> your node -> Database shows the Mnesia tables and their size, and lets you perform actions with them, like deleting content and even delete some mnesia tables, as the modules you configured no longer need them.

      Of course, it’s highly recommended first of all to backup the mnesia database, just in case you make some mistake, or there is any other problem in ejabberd.

      • Thank you!
        Well, deleting tables is for sure not necessary (dangerous?), but I deleted the content of the “archive_msg” table which made the database size shrink by 98 %! I am pretty satisfied ;)

    • I double checked and indeed it is needed. On a fresh install the default value of “auth_method” is “mnesia”, and it is no explicitly defined in the config. To now fully use auth through the sql, you need to define “auth_method: sql” as you stated. I will update the tutorial, thanks.

  2. Question:

    ./ejabberdctl export2sql xmppcal.de /tmp/mnesia.sql
    Failed RPC connection to the node ejabberd@localhost: timeout

    What ist that?
    THX

    • Looks like your node is down. You should have it running in the background prior running ejabberdctl.

  3. Hi
    i tried your settings. Users are saved in database but persistent rooms are not saved in database. my settings

    mod_muc:
    db_type: sql
    access:
    – allow
    access_admin:
    – allow: admin
    access_create: muc_create
    access_persistent: muc_create
    access_mam:
    – allow
    default_room_options:
    allow_subscription: true # enable MucSub
    mam: true
    members_only: true
    persistent: true
    ….
    I can see rooms in web panel.

  4. Hello

    “ejabberdctl export2sql” works well but not include “users” account and “muc archive” (only users mam). From sql file, I find : archive_msg, archive_prefs, caps_features, last_activity, muc_room, offline_msg, privacy, private_storage, pubsub_item, pubsub_node, pubsub_state, push_session, roster, vcard, vcard_search.

    With “ejabberdctl dump”, I think to have everything but cant execute this file in psql for migrate from mnesia to postgres.

    Did I miss something ? or any trick to have everything with export2sql or to convert dump from xml to sql ?

    ejabberd version 21.07

    Thanks

    • export2sql will only export the mnesia tables when you have configured them to use SQL. So, first set auth_method sql, and mod_muc to use sql, and then run export2sql.

      • Ty for fast answer !

        I confirm got :
        -default_db: sql
        -auth_method: sql
        -mod_muc:
        db_type: sql
        -and for mod_announce, mod_caps, mod_last, mod_mam, mod_roster.

        My step on fresh server with empty postgres database pop with new pg structure (and new_sql_schema: true) :
        – ejabberdctl install_fallback ejabberd.backup
        – restart ejabberd services and wait restore finish to mnesia database, then
        – ejabberdctl export2sql domain.tld /tmp/domain.sql
        still not include “users” account and “muc archive” in sql file.

        • Obviously something is going wrong, either configuration, usage, or a bug in ejabberd. And this is an article about MariaDB, not a bug tracker or a forum. Please open an issue in the ejabberd bug tracker, and let’s investigate this problem there.

  5. I had a problem on Debian to import the emojis / UTF8mb4 characters.
    I had to change the import command to:
    mysql -h localhost -D ejabberd -u ejabberd -p –default-character-set utf8mb4 < /tmp/mnesia.sql

    before that I had to change the character set on the DB:
    ALTER DATABASE [database_name] CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

  6. In 2023 using 23.04 this no longer works – everything setup exactly as listed here and checked… results are:
    [warning] @ejabberd_sql:handle_reconnect/2:479 mysql connection failed:
    [Warning] Aborted connection 628 to db: ‘unconnected’ user: ‘unauthenticated’ host: ‘::1’ (This connection closed normally without authentication)

    This occurs as soon as I add auth_method: sql and the db_type entries as indicated, there is no info anywhere that I can find that indicates why this happens.

Leave a Reply to Zoschel Cancel Reply


This site uses Akismet to reduce spam. Learn how your comment data is processed.