Following the Prosody installation tutorial I wrote last time you’ll notice that we didn’t touch any database configuration for storage, therefore Prosody will use the default storage, the file system.

Here is how to use MySQL as storage backend for Prosody.

Install lua-dbi-mysql:

apt-get install lua-dbi-mysql

In this tutorial the MySQL database is on the same server, you need to install MySQL server in the same server as Prosody:

apt-get install mysql-client mysql-server

Create MySQL user for Prosody:

echo "CREATE USER 'prosody'@'localhost' IDENTIFIED BY 'rahasia';" | mysql -uroot -p

Please note that on above example the created MySQL user is prosody and the password is rahasia.

Grant access for prosody user to MySQL database prosody_*:

echo "GRANT ALL PRIVILEGES ON \`prosody\_%\` . * TO 'prosody'@'localhost';" | mysql -uroot -p

Please note the backticks and single-quotes.

Just to make sure everything as expected:

echo "FLUSH PRIVILEGES;" | mysql -uroot -p
echo "SHOW GRANTS FOR 'prosody'@'localhost'" | mysql -uroot -p

Create MySQL database prosody_db1:

mysqladmin -uprosody -p create prosody_db1

Edit prosody.cfg.lua:

vi /etc/prosody/prosody.cfg.lua

On prosody.cfg.lua, enable sql storage. Add these lines just before the Virtual hosts marks:

storage = "sql"
sql = { driver = "MySQL", database = "prosody_db1", username = "prosody", password = "rahasia", host = "localhost" }

Please note that above configuration must be added on global part of prosody.cfg.lua, that is the part before the Virtual hosts part.

Reload Prosody:

/etc/init.d/prosody restart

Add an XMPP/Jabber ID just to fill the database:

prosodyctl adduser anton@textng.com

Check on MySQL:

echo "SELECT * FROM prosody" | mysql -uprosody -p prosody_db1

That is all.