MariaDB server must be running the version corresponding to the relevant Abiquo API server version.
During this process, the Abiquo platform must be offline and there must be no connections to the DB server.
MariaDB server must allow connections to the MariaDB port from the Abiquo API server.

This guide explains how to configure the Abiquo Server and the accounting integration with a remote MariaDB server. Any other services relying on access to Abiquo DBs must also be updated so they continue working.

Description

This guide shows how to configure Abiquo to use a remote MariaDB server, instead of running it locally on the Abiquo Server itself. Using a remote DB server may help to improve the security and performance of the Abiquo Platform. In this guide, we will assume that the MariaDB server is already installed and running. We will assume the following assumptions for example purposes:

MariaDB server administrator credentials as ADMUSER:ADMPASS
Abiquo API server at IP 10.60.13.5
MariaDB server at IP 10.60.13.39

Configure DBs in MariaDB server

Depending on when the remote MariaDB server is configured, we will be in one of the situations below:

The API server has been just deployed and there is no data yet in the Abiquo DBs. In this case, we need to create new Abiquo DBs.
The API server is already being use, and the Abiquo DBs have already been used. In this case, we have to import already existing Abiquo DBs.

Create new Abiquo DBs

The Abiquo DBs' schemas belong to the abiquo-server package. You will have to import them onto the remote MariaDB server by executing the commands below on the Abiquo Server. Adapt them according to your settings:

mysql -uADMINUSER -pADMINPASS -h10.60.13.39 < /usr/share/doc/abiquo-server/database/kinton-schema.sql
mysql -uADMINUSER -pADMINPASS -h10.60.13.39 < /usr/share/doc/abiquo-server/database/src/accounting/kinton_accounting.sql

Import already existing DBs

Export the Abiquo DBs and import the dump into the remote MariaDB server by adapting the commands below according to your configuration:

mysqldump --routines --triggers --databases kinton kinton_accounting > abiquo.sql
mysql -uADMUSER -pADMPASS -h10.60.13.39 < abiquo.sql

Configure Abiquo credentials

Create a user on the MariaDB server for the Abiquo server and grant it privileges for the Abiquo DBs. Modify the command below according to your settings and execute them at the MariaDB server prompt:

create user 'abquser'@'10.60.13.5';
grant all privileges on kinton.* to 'abquser'@'10.60.13.5' identified by 'abqpass';
grant all privileges on kinton_accounting.* to 'abquser'@'10.60.13.5' identified by 'abqpass';
flush privileges;

Configure Abiquo Server

Change the DB settings in the abiquoDB section of /opt/abiquo/tomcat/conf/Catalina/localhost/api.xml accordingly. In our example:

/opt/abiquo/tomcat/conf/Catalina/localhost/api.xm

<Context>
	...
	<Resource name="jdbc/abiquoDB" 
		...
		username="abquser"
		password="abqpass"
		url="jdbc:mysql://10.60.13.39:3306/kinton?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
	...
</Context>

Configure accounting stored procedures

See Configure accounting section on Abiquo accounting services integration for full details.

By default, Abiquo Accounting stored procedures are executed periodically on the Abiquo Server to update accounting and delete old events. These stored procedures are run by a cron job and access the Abiquo DBs. There are two ways to configure this cron job when using a remote MariaDB server:

Modify the default cronjob on the Abiquo Server
Create a new cronjob on the remote MariaDB server

Modify the default cronjob on the Abiquo Server

Log in to the Abiquo Server and modify the default cron job at /etc/cron.d/abiquo-accounting according to your setup. In our example:

# Runs every hour
0  * * * * root mysql -uabquser -pabqpass -h10.60.13.39 -e "CALL kinton.UpdateAccounting();"

# Runs every Sunday at 12:00 and deletes records older than a week.
0 12 * * 0 root mysql -uabquser -pabqpass -h10.60.13.39 -e "CALL kinton.DeleteOldRegisteredEvents(168);"

Create a new cronjob on the remote MariaDB server

If you do not wish to use the standard cron job on the Abiquo server, you can disable it and create a new cron job on the remote MariaDB server. Log in to the remote MariaDB server and create a /etc/cron.d/abiquo-accounting similar to the one below. Modify the username and password according to your settings. In our example:

# Runs every hour
0  * * * * root mysql -uADMUSER -pADMPASS -hlocalhost -e "CALL kinton.UpdateAccounting();"

# Runs every Sunday at 12:00 and deletes records older than a week.
0 12 * * 0 root mysql -uADMUSER -pADMPASS -hlocalhost -e "CALL kinton.DeleteOldRegisteredEvents(168);"

Restart Abiquo

Once you have changed all DB related settings, start Abiquo services again on the Abiquo Server:

service abiquo-tomcat start

Browser not supported