Versions Compared

Old Version 16

changes.mady.by.user Former user

Saved on

compared with

New Version Current

changes.mady.by.user MS

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Table of Contents
Info

This guide is based on CentOS 6

Install MariaDB on replica server

Install in the replica server the same MariaDB server package that Abiquo API server is using . This can be achieve by installing the corresponding on the replica server.

  1. On the replica server, Install your abiquo-release package

...

  2. Install MariaDB with this command

    Code Block
    yum -y install MariaDB-server MariaDB-client
Warning

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

...

the main DB server

...

  1. On the main DB server, create the replication configuration file

...

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

  2. Restart the mysql service

...

  1. Code Block
    service mysql restart

  2. Create a replicator user and grant it replication permissions by executing the query below on DB prompt. Replace

...

  1. REPLICA_USER, REPLICA_PASS, and REPLICA_SERVER with values for your environment.

    Code Block

...

  1. grant

...

  1. replication

...

  1. slave

...

  1. on *.*

...

  1. to '

...

  1. REPLICA_USER'@'SLAVE_

...

  1. SERVER'

...

  1. identified

...

  1. by '

...

  1. REPLICA_PASS';
flush privileges;

...

Configure DB replication on

...

replica DB server

  1. Create the replication configuration file in Maria DB

...

  1. replica server

...

  1. .

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

  2. Restart the mysql service

...

  1. .

    Code Block

...

  1. 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.

Code Block
FLUSH TABLES WITH READ LOCK;

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

Code Block
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
Code Block
Warning

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.

...

Perform 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

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

  2. Run the following commands to get the file and current position

    Code Block
    flush tables with read lock;
show master status;
+----------------------+----------+--------------+------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
|

...

  1. master-bin.

...

  1. 000001    |  3712878 |              |                  |
+----------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

...

  2. Leave this session open and don’t make any changes until replication is completed.
  3. On the MariaDB main server, open another session and dump the kinton and kinton_accounting databases 
...
  1. using --single-transaction option
     Code Block
    mysqldump --databases kinton kinton_accounting --routines  --single-transaction > dump.sql 
...
Configure slave replication point
  1. Copy dump.sql from the previous step from 
...
  1. the main DB 
...
  1.  server to 
...
  1. the replica DB 
...
  1.  server. 
...
  1. When the copy finishes, import it into 
...
  1. replica DB 
...
  1.  server. If this is a new installation you may need to create the kinton
...
  1.  databases.
     Code Block
    mysql < dump.sql
...
  1. 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 
    1. Replace MAIN_SERVER, REPLICA_USER and REPLICA_PASS. 
    2. Replace MASTER_LOG_FILE and MASTER_LOG_POS
...
    1.  from the show master status above 
       Code Block
      change master to
  MASTER_HOST='MAIN_SERVER',
  MASTER_USER='REPLICA_USER',
  MASTER_PASSWORD='REPLICA_PASS',
  MASTER_PORT=3306,
  MASTER_LOG_
...
    1. 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
       Code Block
      change master to
  MASTER_HOST='services.lab.example.com',
  MASTER_USER='
...
    1. abiquodb',
  MASTER_PASSWORD='
...
    1. abiquodbpassword',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='
...
    1. master-bin.
...
    1. 000001',
  MASTER_LOG_POS=3712878,
  MASTER_CONNECT_RETRY=10;
...
  1. On the replica server, at the mysql prompt, start replica DB replication
     Code Block
...
  1. start 
...
  1. slave;
...
  2. To check replication status
...
  1. , run the following command on the 
...
  1. replica DB
     Code Block
...
  1. show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
...
  1. 

...
  1. .
...
  1. .
...
  1. .
...
  1. 

...
  1. 1 row in set (0.00 sec)

    When 
...
  1.  Slave_IO_State is Waiting for master to send event
...
  1. , replication is finished
  2. 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/