Speed Up Confluence by Running MySQL on a Separate Raspberry Pi

If you’re serious about running Confluence on a Raspberry Pi, one way to eke out additional performance is to migrate the back-end database from the Pi running Confluence to another Pi.

In this case, I have a second Pi running the lightweight LAMP stack, and it’s general usage is on the minimal side. MySQL for Confluence isn’t as memory intensive as one might expect, but it’ll take 3-6% of total available memory. Why not offload that plus the processing power over to the other Pi already running the LAMP stack?

Prerequisites

Raspberry Pi 3 Model B running Confluence and MySQL
Separate Pi running MySQL
Basic Linux knowledge

In the below steps, the following servers are referenced with fictional but realistic IP addresses:

Source server, running Confluence & MySQL together – 192.168.1.101
Destination server, running MySQL (e.g. in a LAMP configuration) – 192.168.1.102

The idea is pretty straightforward. Migrate the DB (source & destination), update permissions (destination), update the connection string (source), test (source), and make sure your backups are updated if necessary. This post may be a learning lesson for some as the instructions will be step-by-step but not in intricate detail.

Migrate the DB

Make a backup copy of the existing Confluence DB
1. If phpMyAdmin is installed, you can select the database and then click the Export tab to export the DB data.
2. Or, you can do something like this in the command line
```
mysqldump -u YourUserName -p YourConfluenceDatabaseName > confluence-backup.sql
```
3. Leave the source data alone until you are fully running on the new destination, otherwise the risk of data loss increases.
Restore/Import the backup copy of the DB to the destination MySQL instance.
1. If phpMyAdmin is installed, you can select the Import tab, locate the exported database SQL file from the previous step, and restore it.
2. Or, you can do something like this in the command line
```
mysql -D YourDatabaseName -o YourDatabaseName < confluence-backup.sql
```

Permissions

When first setting up Confluence and MySQL, a database user and password are required to authenticate between the application and the database. In my case, the username is confluence; and not to be confusing, but I happened to name the database confluence as well.

Destination server: grant privileges to the confluence user

# In the terminal shell
mysql -u root -p -hlocalhost

# This is in MySQL. 'confluence' is the user, change it if it's not the same for you. Change insertpasswordhere and leave all of the single quotes.
GRANT ALL PRIVILEGES ON confluence.* TO 'confluence'@'192.168.1.101' IDENTIFIED BY 'insertpasswordhere';

In this step you’re granting privilege to the confluence user on the Confluence server only, and only for accessing the confluence DB.

Source server: Test permission (helpful link to read/learn more).
1. On the source server, remotely connect to the destination server’s MySQL service and show the databases to test basic authentication.
```
# username is confluence, and the destination server address is 192.168.1.102
mysql -u confluence -h 192.168.1.102 -p

# This is in MySQL
show databases;
```
2. It should return results like this, again, this is on the source server running Confluence but now you’re testing the connection to the destination server.

Update the Confluence Connection String

Still on the source server running Confluence, it’s time to point Confluence to the new destination server.

Source server: Open the confluence.cfg.xml file and change the value of the node with attribute name=”hibernate.connection.url”. The directories may vary on different systems.

# Change directory to your confluence home folder
cd /var/confluence-home

# Open confluence.cfg.xml
nano confluence.cfg.xml

# Modify the URL, then save
# Old: <property name="hibernate.connection.url">jdbc:mysql://192.168.1.101/confluence</property>
# New: <property name="hibernate.connection.url">jdbc:mysql://192.168.1.102/confluence</property>

# Ctrl+o to save, ctrl+x to exit.

Test Confluence

Source server: Stop MySQL so Confluence isn’t inadvertently hitting the incorrect MySQL instance
```
service mysql stop
```

Source server: Restart Confluence (the location of stop- and start-confluence.sh may vary)

/home/atlassian-confluence-6.9.1/bin/stop-confluence.sh

/home/atlassian-confluence-6.9.1/bin/start-confluence.sh

Source server: Load Confluence the same way you always do to test it out.

Backups

If you normally back up your MySQL Confluence database, make sure you start backing it up on the new destination instance. Be certain to test and validate any changes to backup processes.

Summary

The process of migrating MySQL to a separate instance is very straightforward, and MySQL certainly doesn’t need to be running on a Pi for this to work. Since I had a Pi with plenty of resources to handle the Confluence workload it worked out quite well. This also makes it easier to run a MySQL front-end like phpMyAdmin on the MySQL server without having to run additional apache/web server processes on the Confluence server that can consume the limited resources.

In my testing, Confluence runs a bit faster, and when looking at the system resources, it’s running with a little more breathing room. On the left is an older Confluence instance running MySQL locally, and on the right is a newer instance with no local MySQL. I’ll keep this configuration for the foreseeable future, probably until the next iteration of the Raspberry Pi is released.

Credits:
Header image via Flickr user Les Pounder.