MySQL Replication

Replication between Master and Slave servers is based on the binary logging mechanism. Master writes updates and changes as events to the binary log. Slaves read the binary log from the master and execute the events.

Install MySQL Server on both master and slave servers as shown at http://anandbitra.blogspot.com/2014/08/installing-mysql-server-on-centos-7.html

Configure the Master Database

Create a pseudo-user for replicating data and grant replication permission.

mysql > CREATE USER 'replica'@'%' IDENTIFIED BY 'password';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';

Update the /etc/my.cnf file.

[mysqld]

server_id = 1

log_bin = /var/lib/mysql/mysql_binlog
binlog_format = mixed

binlog_do_db = <db_name>,<db_name>

#binlog_ignore_db = mysql

innodb_flush_log_at_trx_commit = 1

sync_binlog = 1

Restart MySQL
# service mysqld restart

Acquire a global read lock to get backups.

mysql> FLUSH TABLES WITH READ LOCK;

In another session, obtain the current binary log file and position. This information is needed to setup the slave later.

mysql> SHOW MASTER STATUS;

Backup metastore database.

$ mysqldump -u root -p metastore > metastore.sql

mysql> UNLOCK TABLES;

Configure the Slave Database

  

Create database

mysql> CREATE DATABASE metastore;

Import metastore database exported on master server.

$ mysql -u root -p metastore < /tmp/metastore.sql

Update the /etc/my.cnf file.

[mysqld]
server-id = 2
log_bin = /var/lib/mysql/mysql_binlog
binlog_format = mixed
binlog_do_db = metastore
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
relay_log = /var/lib/mysql/mysql_relaylog

Restart MySQL

# service mysqld restart

Start the replication by setting binlog file and position.

mysql> CHANGE MASTER TO master_host='192.168.1.6', master_user='replica', master_password='vagrant', master_log_file=’mysql_binlog.000002’, master_log_pos=106;

mysql> START SLAVE;

Verify replication status

mysql> SHOW SLAVE STATUS\G