How To Configure MySQL Master-Slave Replication?

How To Configure MySQL Master-Slave Replication? (One-Way Replication)

Table of Contents
___________________________________________________________________________________________________

1. Environment

On MySQL Master Server

2. Update Master Server Config File (Terminal 1)
3. Restart MySQL Server(Terminal 1)
4. Create user for replication on Master side (Terminal 1)
5. Note Down Master Log Position (Terminal 1)
6. Backup  All database of Master (Terminal 2)
7. Unlock Master Database (Terminal 1)
8. Transfer Database Backup Dump File to Slave Server

On MySQL Slave Server

9. Update Slave Server Config File
10. Restart MySQL Server
11. Restore Database Dump into Slave Server
12. Setup Slave to Communicate Master Database
13. Test Replication and Verify the Results

Create Database Operation

   a1) Create Database Operation on Master
   a2) Verify Create Database Operation on Slave

Create Table Operation

    b1) Create Table Operation on Master
    b2) Verify Create Table Operation on Slave

Insert Table Operation

    c1) Insert Table Operation on Master
    c2) Verify Insert Table Operation on Slave

Update Table Operation

    d1) Update Table Operation on Master
    d2) Verify Update Table Operation on Slave

Delete Table Operation

    e1) Delete Table Operation on Master
    e2) Verify Delete Table Operation on Slave
___________________________________________________________________________________________________


1. Environment

Host NamePublic IPPrivate IPMySQL Version
Master Server
rac1192.168.2.101192.168.0.1018.0
Slave Server
rac2192.168.2.102192.168.0.1028.0

Private IP using for replication — Dedicated IP


On MySQL Master Server


2. Update Master Server config file (Terminal 1)

Edit /etc/my.cnf file and add below entries.

server-id=1
bind-address=192.168.0.101
log-bin=mysql-bin

[root@rac1 ~]# cat /etc/my.cnf | egrep "server-id|bind-address|log-bin"
server-id=1
bind-address=192.168.0.101
log-bin=mysql-bin
[root@rac1 ~]#


3. Restart MySQL Server (Terminal 1)

[root@rac1 ~]# systemctl restart mysqld
[root@rac1 ~]#


4. Create user for replication (Terminal 1)

*** Create replica user in Master pointing to slave, it's requires when Slave connects to Mater.

*** Grant REPLICATION SLAVE privilege to myreplica

mysql> select host,user from mysql.user where user='replica';
Empty set (0.00 sec)

mysql> CREATE USER 'replica'@'192.168.0.102' IDENTIFIED WITH  mysql_native_password BY 'Mysql@123';
Query OK, 0 rows affected (0.01 sec)

mysql> select host,user from mysql.user where user='replica';
+---------------+---------+
| host          | user    |
+---------------+---------+
| 192.168.0.102 | replica |
+---------------+---------+
1 row in set (0.00 sec)

mysql>

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'192.168.0.102';
Query OK, 0 rows affected (0.01 sec)

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

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

mysql> 

Note that this lock is released either when you exit the mysql CLI client, or when you issue UNLOCK TABLES. The lock needs to remain in place until the mysqldump  is complete.


5. Note Down Master Log Position (Terminal 1)

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000009 |     2369 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>


6. Backup All database of Master (Terminal 2)

[root@rac1 backup]# pwd
/u01/mysql/data/backup
[root@rac1 backup]# ls -ltr
total 0
[root@rac1 backup]#
[root@rac1 backup]# mysqldump --all-databases --master-data > /u01/mysql/data/backup/alldbdump.sql
[root@rac1 backup]#
[root@rac1 backup]# ls -ltr
total 95480
-rw-r--r--. 1 root root 97769974 Mar 18 19:43 alldbdump.sql <----
[root@rac1 backup]#


7. Unlock Master Database (Terminal 1)

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql>


8. Transfer Database Backup Dump File to Slave Server

[root@rac1 backup]# scp alldbdump.sql root@192.168.0.102:/u01/mysql/backup
root@192.168.0.102's password:
alldbdump.sql                                100%   93MB  51.5MB/s   00:01
[root@rac1 backup]#


On MySQL Slave Server


9. Update Slave Server Config File

Edit the /etc/my.cnf file and add below entries

[root@rac2 ~]# cat /etc/my.cnf | egrep "server-id|bind-address|log-bin"
server-id=2
bind-address=192.168.0.102
log-bin=mysql-bin
[root@rac2 ~]#


10. Restart MySQL Server

[root@rac2 ~]# systemctl restart mysqld
[root@rac2 ~]#


11. Restore Database Dump into Slave Server

[root@rac2 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> exit
Bye
[root@rac2 ~]#

[root@rac2 ~]# cd /u01/mysql/backup
[root@rac2 backup]# ls -ltr
total 95480
-rw-r--r--. 1 root root 97769974 Mar 18 19:44 alldbdump.sql
[root@rac2 backup]#

[root@rac2 backup]# mysql < /u01/mysql/backup/alldbdump.sql 
ERROR 3021 (HY000) at line 24: This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL '' first. 

[root@rac2 backup]# 
[root@rac2 backup]# mysql 
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 17 Server version: 8.0.23 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. 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> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> exit
Bye
[root@rac2 backup]# mysql < /u01/mysql/backup/alldbdump.sql
[root@rac2 backup]#


12. Setup Slave to Communicate Master Database

[root@rac2 backup]# mysql

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.0.101',
    -> MASTER_USER='replica',
    -> MASTER_PASSWORD='Mysql@123',
    -> MASTER_LOG_FILE='mysql-bin.000009',
    -> MASTER_LOG_POS=2369;
Query OK, 0 rows affected, 8 warnings (0.02 sec)

mysql>
mysql> START SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

mysql> SHOW PROCESSLIST;
+----+-----------------+-----------------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User            | Host            | db   | Command | Time | State                                                  | Info             |
+----+-----------------+-----------------+------+---------+------+--------------------------------------------------------+------------------+
|  5 | event_scheduler | localhost       | NULL | Daemon  | 2355 | Waiting on empty queue                                 | NULL             |
| 23 | root            | localhost       | NULL | Query   |    0 | init                                                   | SHOW PROCESSLIST |
| 24 | system user     | connecting host | NULL | Connect |   16 | Waiting for master to send event                       | NULL             |
| 25 | system user     |                 | NULL | Query   |   16 | Slave has read all relay log; waiting for more updates | NULL             |
+----+-----------------+-----------------+------+---------+------+--------------------------------------------------------+------------------+
4 rows in set (0.00 sec)

mysql>

mysql> SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.101
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000009
          Read_Master_Log_Pos: 2369
               Relay_Log_File: rac2-relay-bin.000002
                Relay_Log_Pos: 324
        Relay_Master_Log_File: mysql-bin.000009
             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: 2369
              Relay_Log_Space: 532
              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: 1
                  Master_UUID: 30515002-7f31-11eb-8fab-080027cabe36
             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 more updates
           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
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set, 1 warning (0.01 sec)

ERROR:
No query specified

mysql>


13. Test Replication and Verify the Results


Create Database Operation


a1) Create Database Operation on Master

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| orcl               |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql>

mysql> create database mynsp;
Query OK, 1 row affected (0.01 sec)

mysql>

mysql> use mynsp;
Database changed
mysql>
mysql> show tables;
Empty set (0.00 sec)

mysql>


a2) Verify Create Database Operation on Slave

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mynsp              |
| mysql              |
| orcl               |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql>

mysql> use mynsp;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql>


Create Table Operation


b1) Create Table Operation on Master

mysql> create table MYSCB_DBA (
    -> ID int,
    -> NAME varchar(20),
    -> ROLE varchar(20),
    -> primary key (ID))
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql>


mysql> show tables;
+-----------------+
| Tables_in_mynsp |
+-----------------+
| MYSCB_DBA       |  <------
+-----------------+
1 row in set (0.00 sec)

mysql>


b2) Verify Create Table Operation on Slave

mysql> show tables;
+-----------------+
| Tables_in_mynsp |
+-----------------+
| MYSCB_DBA       | <------
+-----------------+
1 row in set (0.00 sec)

mysql>


Insert Table Operation


c1) Insert Table Operation on Master

mysql> SELECT * FROM MYSCB_DBA;
Empty set (0.00 sec)

mysql>

mysql> INSERT INTO MYSCB_DBA values(1,'SIRAJ','DBA');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO MYSCB_DBA values(2,'RAGHU','DBA');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO MYSCB_DBA values(3,'VINOD','DBA');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO MYSCB_DBA values(4,'VIJAY','DBA');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO MYSCB_DBA values(5,'SURESH','DBA');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO MYSCB_DBA values(6,'SENTHIL','DBA');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO MYSCB_DBA values(7,'CHANDRA','DBA');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO MYSCB_DBA values(8,'PRASANNA','DBA');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO MYSCB_DBA values(9,'GOVARDINI','DBA');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO MYSCB_DBA values(10,'RAJASEKHAR','L2DBA');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO MYSCB_DBA values(11,'RAJKUMAR','MANAGER');
Query OK, 1 row affected (0.00 sec)

mysql>

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql>


mysql> SELECT * FROM MYSCB_DBA;
+----+------------+---------+
| ID | NAME       | ROLE    |
+----+------------+---------+
|  1 | SIRAJ      | DBA     |
|  2 | RAGHU      | DBA     |
|  3 | VINOD      | DBA     |
|  4 | VIJAY      | DBA     |
|  5 | SURESH     | DBA     |
|  6 | SENTHIL    | DBA     |
|  7 | CHANDRA    | DBA     |
|  8 | PRASANNA   | DBA     |
|  9 | GOVARDINI  | DBA     |
| 10 | RAJASEKHAR | L2DBA   |
| 11 | RAJKUMAR   | MANAGER |
+----+------------+---------+
11 rows in set (0.00 sec)

mysql>


c2) Verify Insert Table Operation on Slave

mysql> SELECT * FROM MYSCB_DBA;
+----+------------+---------+
| ID | NAME       | ROLE    |
+----+------------+---------+
|  1 | SIRAJ      | DBA     |
|  2 | RAGHU      | DBA     |
|  3 | VINOD      | DBA     |
|  4 | VIJAY      | DBA     |
|  5 | SURESH     | DBA     |
|  6 | SENTHIL    | DBA     |
|  7 | CHANDRA    | DBA     |
|  8 | PRASANNA   | DBA     |
|  9 | GOVARDINI  | DBA     |
| 10 | RAJASEKHAR | L2DBA   |
| 11 | RAJKUMAR   | MANAGER |
+----+------------+---------+
11 rows in set (0.00 sec)

mysql>


Update Table Operation


d1) Update Table Operation on Master

mysql> update MYSCB_DBA set role='L1DBA' where NAME='RAJASEKHAR';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> SELECT * FROM MYSCB_DBA WHERE NAME='RAJASEKHAR';
+----+------------+-------+
| ID | NAME       | ROLE  |
+----+------------+-------+
| 10 | RAJASEKHAR | L1DBA | <-------
+----+------------+-------+
1 row in set (0.00 sec)

mysql>


d2) Verify Update Table Operation on Slave

mysql> SELECT * FROM MYSCB_DBA WHERE NAME='RAJASEKHAR';
+----+------------+-------+
| ID | NAME       | ROLE  |
+----+------------+-------+
| 10 | RAJASEKHAR | L1DBA | <-----
+----+------------+-------+
1 row in set (0.00 sec)

mysql>


Delete Table Operation


e1) Delete Table Operation on Master

mysql> DELETE FROM MYSCB_DBA WHERE NAME='RAJASEKHAR';
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SELECT * FROM MYSCB_DBA WHERE NAME='RAJASEKHAR';
Empty set (0.00 sec)  <-----

mysql>


e2) Verify Delete Table Operation on Slave

mysql> SELECT * FROM MYSCB_DBA WHERE NAME='RAJASEKHAR';
Empty set (0.00 sec) <-----

mysql>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Leave a Reply

Your email address will not be published. Required fields are marked *