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 database replication
Master server configuration
Create a new file under /etc/my.cnf.d/replication.cnf with the next contents:
[server] log-basename=master log-bin binlog-format=row server_id=1
Restart mysql service
# service mysql restart
Grant permissions to replicator user
Execute this query on the master server mysql prompt, changing <slave_ip> for the slave server, and setting a real secret password.
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'<slave_ip>' IDENTIFIED BY 's3cr3t';
Slave server configuration
Create a new file under /etc/my.cnf.d/replication.cnf with the next content:
[server] server_id=2
Restart mysql service
# service mysql restart
Initial replication
Get master binlog position
Flush and lock tables
On master's mysql prompt execute, is importat to keep this terminal open until replication has been completely configured.
FLUSH TABLES WITH READ LOCK;
Get current binlog position
Execute on mysql prompt
SHOW MASTER STATUS;
This will show curren position on master's binfile, we will ned those values later:
MariaDB [(none)]> SHOW MASTER STATUS; +----------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +----------------------+----------+--------------+------------------+ | localhost-bin.000002 | 3712878 | | | +----------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
Copy existing database to slave server
On another terminal (remember we set locks) execute the mysqldump command on kinton database:
mysqldump --databases kinton kinton_accounting --routines > dump.sql
Copy the generated kinton_dump.sql to the slave server using scp or any other method.
Import this dump on your slave server, if this is a new installation you may need to create kinton database.
mysql < dump.sql
Configure slave replication point
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/