Using ejabberd with MySQL - correction

oficcial ejabberd HAS LIE. THis is not the first time that official ejabberd are LIE in they official documentations:

ejabberd is bundled with native Erlang driver to use MySQL as a backend for persistent storage.

Using MySQL as backend is thus extremely straightforward. ejabberd installation

ejabberd packages and binary installers contains all the module needed to connect to your MySQL server. You have no extra module to install anymore.

If you are building ejabberd from source, you need to make sure that you configure ejabberd to include MySQL module. It can be done by passing option --enable-mysql to configure script. For example:

cd ejabberd-source ./configure --enable-mysql Select Code

MySQL installation

You need a MySQL server that you can point your ejabberd configuration to. The database does not have to be on the same server than ejabberd. Requirements

ejabberd make use of FULLTEXT indexes with InnoDB. Thus, you need MySQL 5.6 or greater to use with ejabberd.

Note: If you do not store message archive in database however, you can try using older 5.5 version. You may need to adapt MySQL database schema to cope with those older MySQL versions. MySQL on Linux

This documentation will not get into the details of making MySQL running on Linux for production. It is dependant on Linux distribution and system administrators preferences and habits.

It is also well documented, so it should not be an issue. Amazon RDS compliance

ejabberd is fully compliant with MySQL on Amazon RDS.

You just need to make sure to use MySQL version 5.6 or greater when you create your database. Example MySQL installation on OSX with homebrew

For testing / development, it is common to start experimenting with MySQL with Homebrew installation.

Here is how to get started to help with setup up environment.

With homebrew properly installed, you can use the following command to install MySQL:

brew install mysql Select Code

You can then follow instruction to finish the installation, for example by running mysql_secure_installation.

You can manually start server with:

mysql.server start Select Code

To connect to your local MySQL server using mysql command-line, assuming you kept the default set up, use:

mysql -uroot Select Code

To stop it, use:

mysql.server stop Select Code

Install with Windows Bash

On Windows you can install MySQL easily like on Linux using Ubuntu Bash:

sudo apt-get install mysql-server-5.6 Select Code

After configuration, you can start MySQL with:

sudo /etc/init.d/mysql start Select Code

You can connect on the database with your created admin password:

mysql -uroot -ppassword Select Code

MySQL database creation Create ejabberd user and database

MySQL admins should use that schema and grant right to a dedicated 'ejabberd' user (replace password with your desired password):

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

You can then create a dedicated 'ejabberd' database (use password created earlier):

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

You should now be able to connect to 'ejabberd' database with user 'ejabberd' (use password defined on GRANT command):

mysql -h localhost -u ejabberd -p -D ejabberd

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.7.11 Homebrew

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> Select Code

Download and adapt MySQL schema

The schema files can be found in ejabberd priv directory. MySQL default schema is defined in a file called mysql.sql.

After build and install from source, the SQL schemas are copied here:

PREFIX/lib/ejabberd-VERSION/priv/sql Select Code

After installing ejabberd from binary installer, you can find the SQL schema in similar directory:

INSTALLDIR/lib/ejabberd-VERSION/priv/sql Select Code

Starting from ejabberd 16.03, database schemas are installed in the more usual directory:

INSTALLDIR_OR_PREFIX/share/doc/ejabberd Select Code

Finally, you can always find the lastest version of the MySQL schema in ejabberd Github repository: mysql.sql

You can download it with command:

wget https://raw.githubusercontent.com/processone/ejabberd/master/sql/mysql.sql Select Code

From there, you can read it and check that it suites your production constrains. You can learn more about the database schema, you can read ejabberd SQL database schema documentation.

Now if you know how to import mysql directly from terminal do it now if you dont know how to import from directly mysql from terminal, then use phpmyadmin: https://www.phpmyadmin.net/downloads/

When Your database is now ready to connect with ejabberd.

Continue with the next step.

ejabberd configuration

ejabberd default backend is Mnesia internal database. However, ejabberd is extremely flexible and you can decide to use MySQL instead on a module-by-module basis. Adding MySQL connection configuration to ejabberd config file

In ejabberd.yml, define your database parameters:

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

If you want to specify the port " THIS IS NOT NECESARRY ":

sql_port: 3306

Those parameters are mandatory if you want to use MySQL with ejabberd.

Configure desired authentication backend

If you decide to store user password in ejabberd, you need to tell ejabberd to use MySQL instead of internal database for authentication.

You thus need to change ejabberd configuration auth_method to replace internal authentication with sql:

auth_method: sql Select Code

If you restart ejabberd, it should connect to your database for authentication. In case it does not work as expected, check your config file syntax and log files (ejabberd.log, error.log, crash.log)

For example, you can create a user in database with ejabberdctl:

/sbin/ejabberdctl register "testuser" "localhost" "passw0rd"

User testuser@localhost successfully registered

You should now be able to connect XMPP users based on MySQL user base. Switch modules to use MySQL instead of Mnesia

At this stage, only the authentication / user base has been moved to MySQL. For data managed by modules, ejabberd still use internal database as default.

For each modules that support SQL backend, you can pass option db_type: sql to use your configured MySQL database. Switch can be done on a module by module basis. For example, if you want to store contact list in MySQL, you can do:

modules: ... mod_roster: db_type: sql ... Select Code

However, if you want to use MySQL for all modules that support MySQL as db_type, you can simply use global option default_db: sql:

default_db: sql Select Code

Note: even if you move all the persistent data you can to MySQL, Mnesia will still be started and used to manage clustering.

Now in official ejabberd THEY LIE how to be imported from mnesia to mysql: https://docs.ejabberd.im/admin/databases/mysql/#switch-modules-to-use-mysql-instead-of-mnesia

This has to be done ejabberd work with mysql:

you have to run ejabberdctl live

and in ejabberdctl live write this: ejabberdctl export2mysql localhost /tmp/ejabberd.sql

And just in case export yourdomain.com: ejabberdctl export2mysql yourdomain.com /tmp/ejabberd.sql

When you had exported yourdomain.com, if you know mysql import from terminal do it now, if you don't know how to import from terminal mysql, then use phpmyadmin " on upper there is download phpmyadmin link in this message "

Now when you had finish exporting with ejabberdctl export2mysql yourdomain.com /tmp/ejabberd.sql " on day 15, month January, year 2019, importing 5.1 MB. maybe it take more than 5 min. include phpmyadmin will comment that it was timeout, don't worry just upload the same ejabberd.sql that you exported with ejabberdctl export2mysql yourdomain.com /tmp/ejabberd.sql and i will resume import "

run ejabberdctl live and see if there is errors. If everything is good, then you can use it.