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:

[code lang=bash]
apt-get install lua-dbi-mysql
[/code]

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

[code lang=bash]
apt-get install mysql-client mysql-server
[/code]

Create MySQL user for Prosody:

[code lang=sql]
echo "CREATE USER 'prosody'@'localhost' IDENTIFIED BY 'rahasia';" | mysql -uroot -p
[/code]

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_*:

[code lang=sql]
echo "GRANT ALL PRIVILEGES ON \`prosody\_%\` . * TO 'prosody'@'localhost';" | mysql -uroot -p
[/code]

Please note the backticks and single-quotes.

Just to make sure everything as expected:

[code lang=sql]
echo "FLUSH PRIVILEGES;" | mysql -uroot -p
echo "SHOW GRANTS FOR 'prosody'@'localhost'" | mysql -uroot -p
[/code]

Create MySQL database prosody_db1:

[code lang=bash]
mysqladmin -uprosody -p create prosody_db1
[/code]

Edit prosody.cfg.lua:

[code lang=bash]
vi /etc/prosody/prosody.cfg.lua
[/code]

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

[code lang=css]
storage = "sql"
sql = { driver = "MySQL", database = "prosody_db1", username = "prosody", password = "rahasia", host = "localhost" }
[/code]

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:

[code lang=bash]
/etc/init.d/prosody restart
[/code]

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

[code lang=bash]
prosodyctl adduser anton@textng.com
[/code]

Check on MySQL:

[code lang=bash]
echo "SELECT * FROM prosody" | mysql -uprosody -p prosody_db1
[/code]

That is all.