Using ejabberd with MySQL
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
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
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.
Load database schema into your new database
You can load the schema in your new 'ejabberd' database with the following command:
mysql -h localhost -D ejabberd -u ejabberd -p < mysql.sql
Select Code
To make sure all looks fine, you can show the list of SQL tables:
echo "SHOW TABLES;" | mysql -h localhost -D ejabberd -u ejabberd -p --table
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------------------+
| Tables_in_ejabberd |
+-------------------------+
| archive |
| archive_prefs |
| caps_features |
| last |
| motd |
| muc_registered |
| muc_room |
| privacy_default_list |
| privacy_list |
| privacy_list_data |
| private_storage |
| pubsub_item |
| pubsub_node |
| pubsub_node_option |
| pubsub_node_owner |
| pubsub_state |
| pubsub_subscription_opt |
| roster_version |
| rostergroups |
| rosterusers |
| sm |
| spool |
| sr_group |
| sr_user |
| users |
| vcard |
| vcard_search |
| vcard_xupdate |
+-------------------------+
Select Code
Your database is now ready to connect with ejabberd.
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:
sql_port: 3306
Select Code
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
Select Code
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.
Migrating data from internal database to MySQL
To migrate your data, once you have setup your sql service, you can move most of the data to your database.
You need to take precautions before you launch the migration:
Before you launch migration from internal database, make sure you have made a proper backup.
Always try the migration first on an instance created from your data backup, to make sure the migration script will work fine on your dataset.
Then, when doing final migration, make sure your instance is not accepting connections by blocking incoming connections, for example with firewall rules (block port 5222, 5269 and 5280 as default).
When you are ready, you can:
Connect to a running ejabberd:
./ejabberdctl debug
Select Code
Alternatively, use ejabberdctl live to launch ejabberd with an Erlang shell attached.
Launch the migration command ejd2sql:export/2 from Erlang shell. First parameter is the XMPP domain name you want to migrate (i.e localhost). Second parameter sql tells ejabberd to export to configured MySQL database. For example:
ejd2sql:export(<<"localhost">>, sql).
Select Code
You should be set now.
Getting further
To get further you can read ejabberd Installation and Operation Guide: Database and LDAP configuration