> ## Content Index
> Fetch the complete content index at: https://www.process-one.net/llms.txt
> Use this file to discover other available public pages before exploring further.

# Install and configure MariaDB with ejabberd
- URL: https://www.process-one.net/blog/install-and-configure-mariadb-with-ejabberd/
- Published: 2021-02-12T15:25:00.000Z
- Updated: 2024-09-16T14:26:18.000Z
- Author: ProcessOne

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 »](https://www.process-one.net/en/company/contact)

## 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](https://www.process-one.net/blog/how-to-move-the-office-to-real-time-im-on-ejabberd/)).

To install MariaDB simply use:

```bash
apt-get install mariadb-server

```

Then run the installation wizard and follow the instructions:

```bash
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:

```bash
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:

```bash
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:

```bash
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:

```bash
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:

```yaml
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:

```bash
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:

```yaml
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:

- [How to move the office to ejabberd XMPP server](https://www.process-one.net/blog/how-to-move-the-office-to-real-time-im-on-ejabberd/)
- [How to set up ejabberd video & voice calling (STUN/TURN)](https://www.process-one.net/blog/how-to-set-up-ejabberd-video-voice-calling/)
- [How to configure ejabberd to get 100% in XMPP compliance test](https://www.process-one.net/blog/how-to-configure-ejabberd-to-get-100-in-xmpp-compliance-test/)
- [Check ejabberd XMPP server useful configuration steps](https://www.process-one.net/blog/ejabberd-xmpp-server-useful-configuration-steps/)
- [Starting with MQTT protocol and ejabberd MQTT broker](https://www.process-one.net/blog/starting-with-mqtt-protocol-and-ejabberd-mqtt-broker/)
- [Getting started with WebSocket API in ejabberd](https://www.process-one.net/blog/getting-started-with-websocket-api-in-ejabberd/)
- [Install and configure MariaDB with ejabberd](https://www.process-one.net/blog/install-and-configure-mariadb-with-ejabberd/)
- [Publish-Subscribe pattern and PubSub in ejabberd](https://www.process-one.net/blog/publish-subscribe-pattern-and-pubsub-in-ejabberd/)

*Photo by* [*Amy Asher*](https://unsplash.com/photos/giZJHm2m9yY?ref=process-one.net) *on Unsplash*