MYSQL: HowTo Set Up Master Slave Replication

Through MySQL online replication process we could maintain a multiple copy of Master MySQL database machine save in salves MySQL database Machine automatically. This prevent long delay of restoring backup in case of Master MySQL database machine crash. In this post we will see HowTo Set Up Master Slave Replication.

Below are setup details for this Post.

We are using two Hosts srv7-master and srv7-sec

srv7-master — 192.168.122.109
srv7-sec — 192.168.122.121

Master MySQL Server details

[root@srv7-master ~]# cat /etc/redhat-release 
CentOS Linux release 7.3.1611 (Core) 

[root@srv7-master ~]# uname -r
3.10.0-514.26.2.el7.x86_64

[root@srv7-master ~]# mysqlcheck --version
mysqlcheck  Ver 2.5.1 Distrib 5.7.19, for Linux (x86_64)

Slave MySQL Server details

[root@srv7-sec ~]# cat /etc/redhat-release 
CentOS Linux release 7.3.1611 (Core) 

[root@srv7-sec ~]# uname  -r
3.10.0-514.26.2.el7.x86_64

[root@srv7-sec ~]# mysqlcheck --version
mysqlcheck  Ver 2.5.1 Distrib 5.7.19, for Linux (x86_64)

In our earlier post we saw how can we update MySQL on CentOS/RHEL 7 from MySQL yum repository. So we are working on latest version of MySQL for this post.

Configure Master Server

For configure Master MySQL Server, we need to edit my.cnf file, this is configuration file of MySQL. Open /etc/my.cnf file in your editor.

bind-address=0.0.0.0
server-id=1
log_bin=/var/lib/mysql/mysql-bin.log
binlog_do_db=zabbix

In above Derivatives, we tried to implement required parameters for Master MySQL Server. Let’s discuss it.

"bind-address --  0.0.0.0" we bind MySQL process to all Internet protocol address assigned on machine.

"Server-id  --  1" denote that this machine will act as Master Server in replication.

"Log_bin  --  /var/log/mysql/mysql-bin.log" mentioned path and prefix for bin log files. These binary log contains all sql query which change database.

"binlog_do_db  --  zabbix" contain database name that should cover for bin log.

Now we need to restart MySQL Server on Master MySQL machine. You would see log-bin files in mentioned location after this.

[root@srv7-master mysql]# pwd
/var/lib/mysql
[root@srv7-master mysql]# ls -lh mysql-bin.*
-rw-r----- 1 mysql mysql 808K Aug 18 12:57 mysql-bin.000001
-rw-r----- 1 mysql mysql   32 Aug 18 12:22 mysql-bin.index

Now we need to create MySQL user for Salve machine that would send data towards Slave MySQL machine to replicate data on that database.

[root@srv7-master mysql]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 617
Server version: 5.7.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'192.168.122.121' IDENTIFIED BY 'Passw0rd';
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |   389203 | zabbix       |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Don’t exit above MySQL console, Exit it once MySQL Slave configuration is complete .
Now take backup of database from another console of Master Server, so we could restore it in Slave MySQL Machine. Online MySQL replication work only to execute change in table of respective database. It Will not create any database and tables created before or while replication, we have to create same replica of database and tables.

[root@srv7-master ~]# mysqldump -u root -p zabbix > zabbix.sql
Enter password: 
[root@srv7-master ~]# ls -lh zabbix.sql 
-rw-r--r-- 1 root root 12M Aug 18 17:46 zabbix.sql

Configure Slave Server

As of now we have configured Master MySQL Server, Now we need to configure Slave Server for replication. First of all we need to create database on Slave MySQL machine so that we could save changes that replicate from Master Server.

mysql> create database zabbix;

Now we need to edit Slave my.cnf file in such way that we did in Master MySQL server.

server-id=2
relay-log=/var/lib/mysql/mysql-relay-bin.log
log_bin=/var/lib/mysql/mysql-bin.log
binlog_do_db=zabbix

Restore database backup which we had taken above.

[root@srv7-sec ~]#mysql -u root zabbix -p < zabbix.sql
Enter password:

Now restart MySQL process in MySQL Slave server.

[root@srv7-sec ~]# systemctl restart mysql

Now we have to provide user information of replication user that we have created on Master MySQL Server.
Login on Slave mySQL Server

mysql >CHANGE MASTER TO MASTER_HOST='192.168.122.109',MASTER_USER='replicator', MASTER_PASSWORD='Passw0rd', MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=389203;

Now start Slave process

mysql> start slave

Now exit from Master Server MySQL console where we out read lock and check Master status, it should increase position as usual.

Now we should also check Slave MySQL status as well like below

root@srv7-sec ~]# watch -n 0.1 'mysql -e "SHOW SLAVE STATUS\G"'

Below values woudl continually change

        Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 3578115
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 653020
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           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: 3578115
              Relay_Log_Space: 3189605

MySQL replication has lot more option, this is just brief overview on same.we will going to post some other post on same covering other options with details for MySQL replication.