Install MariaDB

Create repository

Create a file called ‘/etc/yum.repos.d/MariaDB.repo’ with the following content 

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos5-amd64
gpgcheck=1

Import repository key

# rpm --import http://yum.mariadb.org/RPM-GPG-KEY-MariaDB

Install MariaDB

...

Install MariaDB on replica server

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

...

Configure

...

DB replication

...

Master server configuration

...

on the main DB server

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

   Code Block
   cat <<EOF >/etc/my.cnf.d/replication.cnf

...

1. [server] log-basename=master log-bin binlog-format=row server_id=1 EOF

2. Restart the mysql service

   Code Block

...

1. service mysql restart

Grant permissions to replicator user

...

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

   Code Block

...

1. grant

...

1. replication

...

1. slave

...

1. on *.*

...

1. to '

...

1. REPLICA_USER'@'

...

1. SLAVE_

...

1. SERVER'

...

1. identified

...

1. by '

...

1. REPLICA_PASS';

...

Slave server configuration

...

1. flush privileges;

...

Configure DB replication on replica DB server

1. Create the replication configuration file in Maria DB replica server.

   Code Block
   cat <<EOF >/etc/my.cnf.d/replication.cnf

...

1. [server] server_id=2 EOF

2. Restart the mysql service.

   Code Block

...

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

...

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)

Copy existing database to slave server

...

1. Leave this session open and don’t make any changes until replication is completed.

2. On the MariaDB main server, open another session and dump the kinton and kinton_accounting databases using --single-transaction option

   Code Block
   mysqldump --databases kinton kinton_accounting --routines --single-transaction > dump.sql

...

...

Configure slave replication point

1. Copy

...

1. dump.sql from the previous step from the main DB server to the

...

1. replica DB server.

2. When the copy finishes, import it into replica DB server. If this is a new installation you may need to create the kinton

...

1. databases.

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

...

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 from the show master status above

      Code Block
      change master to MASTER_HOST='

...

1. 1. MAIN_SERVER', MASTER_USER='

...

1. 1. REPLICA_USER', MASTER_PASSWORD='

...

1. 1. REPLICA_PASS', MASTER_PORT=3306, MASTER_LOG_FILE='

...

1. 1. MASTER_LOG_FILE', MASTER_LOG_POS=

...

1. 1. MASTER_LOG_POS, 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:

...

1. 1. 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='abiquodb', MASTER_PASSWORD='abiquodbpassword', MASTER_PORT=3306, MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=3712878, MASTER_CONNECT_RETRY=10;

2. On the replica server, at the mysql prompt, start replica DB replication

   Code Block
   start slave;

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

   Code Block
   show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event

...

1. .

...

1. .

...

1. .

...

...

1. 1 row in set (0.00 sec)

...

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