This guide is based on CentOS 6

Install MariaDB

Install in the replica server the same MariaDB server package that Abiquo API server is using. This can be achieve by installing the corresponding abiquo-release package on the replica server and executing the command below:

yum -y install MariaDB-server MariaDB-client

Current reporting design has a problem with concurrent generation of the same report for the same enterprise, please visit Jasper concurrency for a workaround to this issue.

Configure DB replication on master server

Create the replication configuration file in Maria DB master server:

cat <<EOF >/etc/my.cnf.d/replication.cnf
[server]
log-basename=master
log-bin
binlog-format=row
server_id=1
EOF

Restart mysql service:

service mysql restart

Create a replicator user and grant it replication permissions by executing the query below on DB prompt. Replace SLAVE_IP and PASSWORD accordingly:

GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'SLAVE_IP' IDENTIFIED BY 'PASSWORD';

Configure DB replication on slave server

Create the replication configuration file in Maria DB slave server:

cat <<EOF >/etc/my.cnf.d/replication.cnf
[server]
server_id=2
EOF

Restart mysql service:

# service mysql restart

Initial replication

Open a new session on Maria DB master server and execute the command below in MySQL prompt. Keep this session open until replication has been completed.

FLUSH TABLES WITH READ LOCK;

Get current binlog position by executing the command below on Maria DB prompt:

SHOW MASTER STATUS;

This will show current position on master binfile. We will need this value later when replicating the DB in the Maria DB slave server:

MariaDB [(none)]> SHOW MASTER STATUS;
+----------------------+----------+--------------+------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| localhost-bin.000002 |  3712878 |              |                  |
+----------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

On a different session, dump kinton and kinton_accounting databases on Maria DB master server:

mysqldump --databases kinton kinton_accounting --routines > dump.sql

Configure slave replication point

Copy dump.sql from previous step in Maria DB master server to Maria DB slave server.

Once copied, import it on Maria DB slave server. If this is a new installation you may need to create kinton database.

mysql < dump.sql

Once database is on the slave, we should tell mysql to start replicating the master from the frozen binlog position we set before.

On the mysql prompt execute:

CHANGE MASTER TO
  MASTER_HOST='10.60.13.25',
  MASTER_USER='replicator',
  MASTER_PASSWORD='s3cr3t',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='localhost-bin.000002',
  MASTER_LOG_POS=3712878,
  MASTER_CONNECT_RETRY=10;

Notice we set as MASTER_USER the user we grant permission with correspondent password, and also we are using as MASTER_LOG_FILE and MASTER_LOG_POS the data we got previosly.

Start slave

Now you should start slave thread with command:

START SLAVE;

You can check replication status by executing on the slave:

MariaDB [kinton]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.60.13.25
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: localhost-bin.000002
          Read_Master_Log_Pos: 3713036
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 691
        Relay_Master_Log_File: localhost-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3713036
              Relay_Log_Space: 989
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.00 sec)

When the Slave_IO_State is Waiting for master to send event means you replication is working ok.

References

https://mariadb.com/kb/en/setting-up-replication/

Kinton Database replication for Jasper Server