Abiquo Documentation Cookies Policy

Our Documentation website uses cookies to improve your experience. Please visit our Cookie Policy page for more information about cookies and how we use them.


Documentation

Skip to end of metadata
Go to start of metadata

Install MariaDB on slave server

Install the same MariaDB server package that Abiquo API server is using on the slave server. 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

Configure DB replication on master DB server

Create the replication configuration file in master DB 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 REPLICA_USER, REPLICA_PASS and SLAVE_SERVER as you wish:

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

Remember that, if you are using multiple slaves, each one must has a different server_id defined in /etc/my.cnf.d/replication.cnf, and it must be greater than the server_id of the master.

 

Initial replication

Open a new session on master DB server and execute the commands below in mysql prompt. This will show master current status. We will need this later when replicating the DB in the slave. Leave this session as it is until replication is completed:

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)

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 from master DB server to slave DB server. Once copied, import it into slave DB server. If this is a new installation you may need to create kinton database.

mysql < dump.sql

Once imported, configure slave DB server replication. Replace MASTER_SERVER, REPLICA_USER, REPLICA_PASS, MASTER_LOG_FILE and MASTER_LOG_POS accordingly to previous steps in command below and execute it in slave server mysql prompt:

change master to
  MASTER_HOST='MASTER_SERVER',
  MASTER_USER='REPLICA_USER',
  MASTER_PASSWORD='REPLICA_PASS',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master-bin.000001',
  MASTER_LOG_POS=3712878,
  MASTER_CONNECT_RETRY=10;

Start slave DB replication from slave mysql prompt:

start slave;

You can check replication status by executing on the slave:

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

Once Slave_IO_State is "Waiting for master to send event" replication is finished. Then, close the first session on master DB server where locks were set and master status was requested to release locks and you are done.

References

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

 

2 Comments

  1. Since 3.8 include watchtower schema we should point to only replicate kinton and kinton_accounting.

    http://forums.mysql.com/read.php?35,369191,370003

  2. Configure your database to fix the issue when creating virtual appliance and multiple simultaneous VMs. This issue occurs due to database logging with binlog_format STATEMENT or MIXED. Fix by changing to ROW. See database documentation for more information, for example, MariaDB documentation https://mariadb.com/kb/en/mariadb/binary-log-formats/