Set Up Master Slave Replication in MySQL

MySQL replication is a process that allows you to easily maintain multiple copies of a MySQL data by having them copied automatically from a master to a slave database. This can helpful for many reasons including facilating a backup for the data,a way to analyze it without using the main database, or simply as a means to scale out.


The following tutorial aims to provide you a simple step-by-step guide for setting up MySQL (Master-Slave) Replication in CentOS 6,7 and Ubuntu 14.04 

Below are the Specifications and Requirements for Replication Setup :

  • Master IP is 192.168.47.181 (Server22)
  • Slave IP is 192.168.47.136 (Server33)
  • Master and Slave are on the same LAN network.
  • Master and Slave has same MySQL version installed.
  • Master allow remote MySQL connections on port 3306.
  • Install Percona Xtrabackup tool on Both Master and Slave Server.

Assume Mysql Installation has been completed on both the Server’s.

First, proceed with MySQL installation using YUM command. If you already have MySQL installation, you can skip this step.

yum install mysql-server mysql

For Ubuntu 14.04 Server 

sudo apt-get install mysql-server mysql-client

Add the host names in /etc/hosts file 

[root@server22 ~]# vi  /etc/hosts 
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4 server22
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.47.181 server22
192.168.47.136 server33

Step 1: To permit connections on port 3306 (the mysqld default port), add a TCP port 3306 rule with an insert at the last line number in the RH-Firewall-1-INPUT chain

For CentOS 6 :

iptables -I RH-Firewall-1-INPUT 10 -p tcp --dport 3306 -j ACCEPT
iptables -I RH-Firewall-1-INPUT 10 -p udp --dport 3306 -j ACCEPT

For CentOS 7 :

Starting Firewall Service :

sudo systemctl start firewalld.service

We can verify that the service is running and reachable by typing:

firewall-cmd --state

firewall-cmd --get-active-zones
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload

For Ubuntu 14.04 :

sudo ufw status verbose
To allow incoming tcp packets on port 3306, enter:

sudo ufw allow 3306/tcp

Verify it:
sudo ufw status verbose

To allow IP address 192.168.1.10 access to port 3306 for all protocols

sudo ufw allow from 192.168.1.10 to any port 3306

Disable Firewall if Required :

For CentOS 6 :

# service iptables save
# service iptables stop
# chkconfig iptables off

# service ip6tables save
# service ip6tables stop
# chkconfig ip6tables off

For Centos7 :

Stop Firewalld

To stop firewalld, run the following command as root:

systemctl stop firewalld

Disable Firewalld :

To disable firewalld, run the following command as root:

systemctl disable firewalld

Check the Status of FirewalldStop Firewalld

To stop firewalld, run the following command as root:

And finally, to check the status of firewalld, run the following command as root:

systemctl status firewalld

[root@server22 ~]# systemctl is-enabled firewalld
disabled
[root@server22 ~]# systemctl is-active firewalld
unknown
systemctl stop iptables
systemctl disable iptables

For Ubuntu14.04 

sudo ufw disable
# service iptables save
# service iptables stop
# chkconfig iptables off

# service ip6tables save
# service ip6tables stop
# chkconfig ip6tables off

Disable Selinux 

 Edit the /etc/sysconfig/selinux file as below :

 

[root@server22 ~]# cat /etc/sysconfig/selinux
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#       enforcing - SELinux security policy is enforced.
#       permissive - SELinux prints warnings instead of enforcing.
#       disabled - SELinux is fully disabled.
SELINUX=disabled
# SELINUXTYPE= type of policy in use. Possible values are:
#       targeted - Only targeted network daemons are protected.
#       strict - Full SELinux protection.
SELINUXTYPE=targeted

Restart the System and Check :

[root@server22 ~]# sestatus 
SELinux status:                 disabled

Step 2 : Installation of Percona Xtrabackup Tool 

Install the below Packages :

yum install libev

yum install perl-DBD-MySQL

yum install perl-Data-Dumper.x86_64

yum install perl-Test-Simple.noarch

yum install perl

Automatic Install :

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
yum install percona-xtrabackup-24

For Debian/Ubuntu :

$ apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

Add this to /etc/apt/sources.list, replacing VERSION with the name of your distribution:

deb http://repo.percona.com/apt VERSION main
deb-src http://repo.percona.com/apt VERSION main

Remember to update the local cache:

$ apt-get update
Now you can install Percona XtraBackup with:

$ apt-get install percona-xtrabackup

Manual Installation on CentOS6/7

Download the required Package from :

rpm -ivh percona-xtrabackup-24-2.4.5-1.el7.x86_64.rpm

rpm -ivh percona-xtrabackup-test-24-2.4.5-1.el7.x86_64.rpm
[root@perconaserver1 relay]# rpm -q --filesbypkg percona-xtrabackup-24-2.4.5-1.el7.x86_64
percona-xtrabackup-24     /usr/bin/innobackupex
percona-xtrabackup-24     /usr/bin/xbcloud
percona-xtrabackup-24     /usr/bin/xbcloud_osenv
percona-xtrabackup-24     /usr/bin/xbcrypt
percona-xtrabackup-24     /usr/bin/xbstream
percona-xtrabackup-24     /usr/bin/xtrabackup

Step 3:  Configure Master Instance 192.168.47.181 (Server22) for Replication :

Open my.cnf configuration file with VI editor.


Add the following entries under [mysqld] section

server-id = 100

relay-log = /var/lib/mysql/relay/relay-bin
relay-log-index = /var/lib/mysql/relay/relay-bin.index

log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/relay/relay-log.info
log_bin=/opt/mysql/3306/bin-log/binary-log
log-bin-index=/opt/mysql/3306/bin-log/binary-log.index

Comment out the bind-address:

#bind-address = 127.0.0.1


Restart the MySQL service.

service mysql restart
/etc/init.d/mysqld restart


Create a Replication User :

mysql > GRANT FILE, REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'XXXXXX' ;
mysql> FLUSH PRIVILEGES;

Taking Backup :

Method (a) :


Login into MySQL as root user and create the slave user and grant privileges for replication. Replace slave_user with user and your_password with password.

# mysql -u root -p
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'your_password';
mysql> FLUSH PRIVILEGES;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 11128001 | tecmint   |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> quit;

Please write down the File (mysql-bin.000003) and Position (11128001) numbers, we required these numbers later on Slave server. Next apply READ LOCK to databases to export all the database and master database information with mysqldump command.

mysql> UNLOCK TABLES;
mysql> quit;

scp /root/dbdump.db root@192.168.47.136:/root/

Method (b): 


Taking the live dump of Mysql data using master-data 

mysqldump -umysql -pXXXXX -h192.168.47.181 -P3306  --single-transaction --routines --triggers --master-data=1 --databases raw world test1  > replicationbackup-server2212mar_3306.sql 2>> replicationbackup_3306.log

Note down the Binary location 

[root@server22 ~]# head -n24 replicationbackup-server2212mar_3306.sql
-- MySQL dump 10.13  Distrib 5.6.35, for Linux (x86_64)
--
-- Host: 192.168.47.181    Database: raw
-- ------------------------------------------------------
-- Server version 5.6.35-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Position to start replication or point-in-time recovery from
--

CHANGE MASTER TO MASTER_LOG_FILE='binary-log.000018', MASTER_LOG_POS=120;

SCP the file 

scp /root/replicationbackup-server2212mar_3306.sql root@192.168.47.136:/root/

Method (c) :


Hot Backup using Percona Xtrabackup :


Install the below Packages 

yum -y install nc     ;
yum -y install screen.x86_64 ;

Step 1 :  Create a new data directory in Slave instance ( 192.168.47.136 (Server33) ) and initate nc so that listening can happen from Source (192.168.47.181 (Server22))

Note : Make sure Firewall is opened for port 3606 

Connectivity can be tested as below :

 # on target host (192.168.47.136) 
nc -l 3606 > /tmp/test 

# on source host (192.168.47.181 ) 

echo "From new server22" | nc target_host 3606
You should see "From server22" when you cat /tmp/test file

On the Slave run the following :

cd /var/lib/mysql/
mkdir -p data_new
nc -l 3606 | tar xfi - -C /var/lib/mysql/data_new


Step 2 : On Master host (192.168.47.181 (Server22)) we take the snapshot of data using innobackupex 

export PATH=/usr/bin/xtrabackup/bin:$PATH
ulimit -n 65535
 
innobackupex --defaults-file=/etc/my.cnf --no-version-check --safe-slave-backup --stream=tar --tmpdir=/var/lib/mysql/tmp  --slave-info --host=127.0.0.1 --port=3306 --user=mysql --password=XXXX  /etc |nc server33 3606

Step 3 : Once the data streaming is completed to slave instance.Login to the Slave server 192.168.47.136 (Server33) ) and apply-log for data 

export PATH=/usr/bin/xtrabackup/bin:$PATH
 
innobackupex --defaults-file=/etc/my.cnf --apply-log /var/lib/mysql/data_new


Now Shutdown the Slave instance 

service mysql stop

Rename/Delete the present data directory and Rename data_new to data 

cd /var/lib/mysql/

mv data data_old 

mv data_new data 

chown -R mysql:mysql data


Find the Master binlog file and Position as below  :

cd /var/lib/mysql/data 

 cat xtrabackup_binlog_info
binary-log.000001 93772667


Step 4: Configure Slave Instance 192.168.47.136 (Server33) for Replication :

Open my.cnf configuration file with VI editor.


Add the following entries under [mysqld] section.

server-id = 200

relay-log = /var/lib/mysql/relay/relay-bin
relay-log-index = /var/lib/mysql/relay/relay-bin.index

log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/relay/relay-log.info
log_bin=/opt/mysql/3306/bin-log/binary-log
log-bin-index=/opt/mysql/3306/bin-log/binary-log.index

Comment out the bind-address:

#bind-address = 127.0.0.1


Restart the MySQL service.

service mysql restart


Create a Replication User :

mysql > GRANT FILE, REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'XXXXXX' ;
mysql> FLUSH PRIVILEGES;

For Dump Method ( Method (a) and Method (b) ) 


Import the Dump file 

mysql -umysql -pXXXXX -P3306 < replicationbackup-server2212mar_3306.sql

Or 

mysql -umysql -pXXXXX -P3306

mysql >  Source /root/replicationbackup-server2212mar_3306.sql


Login into MySQL as root user and stop the slave. Then tell the slave to where to look for Master log file that we have obtained while backup. You must change IP address of the Master Server, and change the user and password accordingly.


Execute the Change Master command in Slave 

mysql> slave stop;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.47.181', MASTER_USER='repl_user', MASTER_PASSWORD='XXXXXX', MASTER_LOG_FILE='binary-log.000001', MASTER_LOG_POS=93772667;
mysql> slave start;
mysql> show slave status\G
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: server22
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binary-log.000001
          Read_Master_Log_Pos: 93772669
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 284
        Relay_Master_Log_File: binary-log.000018
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: information_schema,performance_schema
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 451
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 555
                  Master_UUID: f2bd0e86-d573-11e6-82c0-000c29bd00ac
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)



Step 5 : Verifying Mysql Replication on Master and Slave Instance :



It’s really very important to know that the replication is working perfectly. On Master server create table and insert some values in it.


On Master Instance:

mysql> select @@hostname ;
+------------+
| @@hostname |
+------------+
| server22   |
+------------+
1 row in set (0.00 sec)

mysql> create database server22 ;
Query OK, 1 row affected (0.09 sec)

mysql> use server22 ;
Database changed
mysql> CREATE TABLE employee (c int);
Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO employee (c) VALUES (1);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM employee;
+------+
| c    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)


On Slave Instance :

mysql> select @@hostname ;
+------------+
| @@hostname |
+------------+
| server33   |
+------------+
1 row in set (0.04 sec)

mysql> use server22 ;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT * FROM employee;
+------+
| c    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)




Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Powered by WordPress.com.

Up ↑

%d bloggers like this: