Abiquo 2.6


Skip to end of metadata
Go to start of metadata

Documentation requires update:

  • Check grants required for kinton, kinton_accounting, etc.
  • Remove server.xml and update api.xml if required
  • Accounting cron jobs are also out of date

 

 

This guide will show you how to set up a remote database server to use with Abiquo, instead of running MySQL on the Abiquo Server. Using a remote MySQL database server may help to improve the security and performance of the Abiquo Platform.

Example Environment

Server

IP

Abiquo AEE Server

10.10.1.2

MySQL Server (CentOS 5)

10.10.1.23

Configure MariaDB Server

This guide has been tested with a CentOS 5 system with MariaDB 5.5 without any MySQL database components installed. MariaDB 5.5 is fully compatible with MySQL 5.5, which is the only version of MySQL that is tested and supported for use with Abiquo 2.4.

This guide assumes you are starting with an Abiquo 2.4 system that is already using MariaDB 5.5.

Create a file called ‘/etc/yum.repos.d/MariaDB.repo’ with the following content 

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos5-amd64
gpgcheck=1

Install MariaDB and start the service 

# rpm --import http://yum.mariadb.org/RPM-GPG-KEY-MariaDB
# yum -y install MariaDB-server MariaDB-client

Grant privileges to abiquo user from localhost and from Abiquo server:

# mysql  
use kinton
create user 'abiquo'@'localhost' identified by 'mypa55word';
grant all privileges on kinton.* to 'abiquo'@'localhost' identified by 'mypa55word';
create user 'abiquo'@'%' identified by 'mypa55word';"
grant all privileges on kinton.* to 'abiquo'@10.10.1.2 identified by 'mypa55word';
 
# v2.0 accounting
 
grant select on kinton.DAILY_USAGE_SUM_VW to accounting@'%' identified by 'acc0unt1ng';
grant select on kinton.HOURLY_USAGE_MAX_VW to accounting@'%' identified by 'acc0unt1ng';
grant select on kinton.HOURLY_USAGE_SUM_VW to accounting@'%' identified by 'acc0unt1ng';
grant select on kinton.LAST_HOUR_USAGE_IPS_VW to accounting@'%' identified by 'acc0unt1ng';
grant select on kinton.LAST_HOUR_USAGE_STORAGE_VW to accounting@'%' identified by
'acc0unt1ng';
grant select on kinton.LAST_HOUR_USAGE_VLAN_VW to accounting@'%' identified by 'acc0unt1ng';
grant select on kinton.LAST_HOUR_USAGE_VM_VW to accounting@'%' identified by 'acc0unt1ng';
grant select on kinton.MONTHLY_USAGE_SUM_VW to accounting@'%' identified by 'acc0unt1ng';
grant select on kinton.accounting_event_detail to accounting@'%' identified by 'acc0unt1ng';
grant select on kinton.accounting_event_ips to accounting@'%' identified by 'acc0unt1ng';
grant select on kinton.accounting_event_storage to accounting@'%' identified by 'acc0unt1ng';
grant select on kinton.accounting_event_vlan to accounting@'%' identified by 'acc0unt1ng';
grant select on kinton.accounting_event_vm to accounting@'%' identified by 'acc0unt1ng';

# v2.2 accounting
 
grant select on kinton_accounting.DAILY_USAGE_SUM_VW to accounting@'%' identified by 'acc0unt1ng';
grant select on kinton_accounting.HOURLY_USAGE_MAX_VW to accounting@'%' identified by 'acc0unt1ng';
grant select on kinton_accounting.HOURLY_USAGE_SUM_VW to accounting@'%' identified by 'acc0unt1ng';
grant select on kinton_accounting.MONTHLY_USAGE_SUM_VW to accounting@'%' identified by 'acc0unt1ng';
grant select on kinton_accounting.accounting_event_detail to accounting@'%' identified by 'acc0unt1ng';

# v2.4 accounting
 
grant select on kinton_accounting.ACCOUNT_PERIOD_USAGE_VW to accounting@'%' identified by 'acc0unt1ng';
grant select on kinton_accounting.ACCOUNTING_CONFIG_VW to accounting@'%' identified by 'acc0unt1ng';
grant select on kinton_accounting.ACCOUNT_PERIOD_USAGE_VW to accounting@'%' identified by 'acc0unt1ng';
grant select on kinton_accounting.LOG_VW to accounting@'%' identified by 'acc0unt1ng';
grant select on kinton_accounting.LOG_ERRORS_VW to accounting@'%' identified by 'acc0unt1ng';
grant select on kinton_accounting.LOG_WARNINGS_VW to accounting@'%' identified by 'acc0unt1ng';
grant execute on procedure kinton_accounting.ABQ_SET_PARAMETER to accounting@'%' identified by 'acc0unt1ng';
 
# reporting

CREATE USER 'reporter'@'<hostname>' IDENTIFIED BY 'myPassword';
GRANT EXECUTE ON kinton_reports.* TO 'reporter'@'<hostname>'; 
 
# billing 2.2
 
mysql kinton < billing.sql

CREATE USER 'billing'@'%' IDENTIFIED BY 'password';
GRANT SELECT,EXECUTE ON kinton.* TO 'billing'@'%';
GRANT SELECT ON mysql.proc TO 'billing'@'%';
GRANT SELECT ON kinton_accounting.* TO 'billing'@'%';
 
 
# billing 2.4
 
# mysql -e "flush privileges;"

Set a password for the root user

mysqladmin -u root password r00tpa55word

Set your firewall configuration to allow MySQL connections on port 3306

Dump Local Database from Abiquo to MySQL Server

Follow these steps on the Abiquo Server.

On Abiquo Server
# OLD DOC
# service abiquo-tomcat stop
# mysqldump --routines --triggers kinton > kinton.sql
# service mysqld stop

# NEW DOC ????
# service abiquo-tomcat stop 
# mysqldump --databases kinton kinton_accounting kinton_reporting kinton_reports --routines --triggers > db_backup.sql
# service mysqld stop
# cp /etc/my.cnf /etc/my.cnf.abiquo
# scp db_backup.sql root@10.10.1.23:

Later...
# rm -f db_backup.sql

Now, follow these steps on the separate MySQL Server.

On MySQL Server
# mysql -pr00tpa55word kinton < kinton.sql
# rm -f kinton.sql

Configure Abiquo Server

Change the database credentials in /opt/abiquo/config/abiquo.properties

Example abiquo.properties
abiquo.database.user = abiquo
abiquo.database.password = mypa55word
abiquo.database.host = 10.10.1.23

Change the database connection information in the abiquoDB section in /opt/abiquo/tomcat/conf/Catalina/localhost/api.xml

Change mysql://localhost:3306/ and password to your needs. In our example:

abiquoDB in api.xml
[...]
<Resource name="jdbc/abiquoDB" 
      auth="Container" 
      type="javax.sql.DataSource" 
      initialSize="10" 
      suspectTimeout="60" 
      timeBetweenEvictionRunsMillis="30000" 
      minEvictableIdleTimeMillis="60000" 
      maxActive="100" 
      minIdle="10" 
      maxIdle="50" 
      maxWait="10000" 
      removeAbandoned="true" 
      removeAbandonedTimeout="60" 
      logAbandoned="true" 
      username="root" 
      password="" 
      driverClassName="com.mysql.jdbc.Driver" 
      url="jdbc:mysql://localhost:3306/kinton?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
[...]

Configure the Accounting Stored Procedures for Abiquo v2.0

New configuration for Abiquo v2.4. This section is work in progress

By default, Abiquo Accounting has stored procedures that run every hour to update accounting and every week to delete old events. These stored procedures are run by a cron job and access the Abiquo MySQL database. There are two ways to configure the Accounting stored procedures for Abiquo:

  • Modify the default cron job on the Abiquo Server 

OR

  • Create a new cron job on the remote MySQL database server

Modify the default cron job on the Abiquo Server

Log in to the Abiquo Server and modify the default cron job to access the MySQL database server.

The default Abiquo Accounting cron job is:

/etc/cron.d/abiquo-accounting

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

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

To allow access to the your remote MySQL database server, change the file as follows. Change the highlighted values to values appropriate to your system.

Modified /etc/cron.d/abiquo-accounting

#### NEW !!!

# Runs every hour
0 * * * * mysql -uabiquo -h10.10.1.23 -P3306 -pmypa55word -e "CALL kinton.UpdateAccounting();"

# Runs every Sunday at 12:00 and deletes records older than a week.
0 12 * * 0 mysql -uabiquo -h10.10.1.23 -P3306 -pmypa55word -e "CALL kinton.DeleteOldRegisteredEvents(2160);"

Update your system crontab with these changes.

Create a new cron job on the MySQL database 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 database server.

Log in to the MySQL database server and edit /etc/cron.d/abiquo-accounting as follows. Change the highlighted values to values appropriate to your system.

/etc/cron.d/abiquo-accounting

# Runs every hour
0 * * * * mysql -uabiquo -hlocalhost -pmypa55word -e "CALL kinton.UpdateAccounting();"

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

 

#### NEW #### =2.2 and above?

# Runs every hour
0 * * * * mysql -uabiquo -hlocalhost -pmypa55word -e "CALL kinton.UpdateAccounting();"

# Runs every Sunday at 12:00 and deletes records older than 90 days
0 12 * * 0 mysql -uabiquo -hlocalhost -P3306 -Pmypa55word -e "CALL kinton.DeleteOldRegisteredEvents(2160)"

 

Add this to the remote database machine system crontab and then check the crontab.

Restart Abiquo

Start Abiquo Tomcat service:

# service abiquo-tomcat start