Install MariaDB on replica server

Install the same MariaDB server package that Abiquo API server is using on the replica server. To do this, install your current abiquo-release package on the replica server and run the command below:

yum -y install MariaDB-server MariaDB-client

Configure DB replication on the main DB server

On the main DB server, create the replication configuration file :

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

Restart the mysql service:

service mysql restart

Create a replicator user and grant it replication permissions by executing the query below on DB prompt. Replace REPLICA_USER, REPLICA_PASS and REPLICA_SERVER with values for your environment:

grant replication slave on *.* to 'REPLICA_USER'@'SLAVE_SERVER' identified by 'REPLICA_PASS';
flush privileges;

Configure DB replication on slave DB 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

To use multiple replica servers, in /etc/my.cnf.d/replication.cnf for each replica server, you must define a different server_id, and it must be greater than the server_id of the main server.

Initial replication

To perform the initial replication, get the current status of the main db, including the file and the current position in the log, and then perform a dump of the database with the following steps

Open a new session on the main DB server at the mysql prompt

Run the following commands to get the file and current position

flush tables with read lock;
show master status;
+----------------------+----------+--------------+------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| master-bin.000001    |  3712878 |              |                  |
+----------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Leave this session open and don’t make any changes until replication is completed.
On the MariaDB main server, open another session and dump the kinton and kinton_accounting databases using --single-transaction option
```
mysqldump --databases kinton kinton_accounting --routines  --single-transaction > dump.sql 
```

Configure slave replication point

Copy dump.sql from the previous step from the main DB server to the replica DB server.
When the copy finishes, import it into replica DB server. If this is a new installation you may need to create the kinton databases.
```
mysql < dump.sql
```

When the import finishes, configure replica DB server replication. On the replica DB server, go to the mysql prompt and run the change master command below

Replace MAIN_SERVER, REPLICA_USER and REPLICA_PASS.

Replace MASTER_LOG_FILE and MASTER_LOG_POS from the show master status above

change master to
  MASTER_HOST='MAIN_SERVER',
  MASTER_USER='REPLICA_USER',
  MASTER_PASSWORD='REPLICA_PASS',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='MASTER_LOG_FILE',
  MASTER_LOG_POS=MASTER_LOG_POS,
  MASTER_CONNECT_RETRY=10;

For example, using the values from above of master-bin.000001 and 3712878

change master to
  MASTER_HOST='services.lab.example.com',
  MASTER_USER='abiquodb',
  MASTER_PASSWORD='abiquodbpassword',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master-bin.000001',
  MASTER_LOG_POS=3712878,
  MASTER_CONNECT_RETRY=10;

On the replica server, at the mysql prompt, start replica DB replication
```
start slave;
```

To check replication status, run the following command on the replica DB

show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
...
1 row in set (0.00 sec)

When Slave_IO_State is Waiting for master to send event, replication is finished

Release locks by closing the first session on master DB server where locks were set and the master status was requested, and you are done!

References

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

Kinton Database replication