📋Steps in this guide1/29

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

mysql replicationintermediate

by MYSQL

13 views

Overview

How To Configure MySQL Master-Slave Replication? (One-Way Replication) 1. Environment Host Name Public IP Private IP MySQL Version Master Server rac1 192.168.2.101 192.168.0.101 8.0 Slave Server rac2 192.168.2.102 192.168.0.102 8.0 Private IP using for replication — Dedicated IP Master Server Slave Server

Section 2

Private IP using for replication — Dedicated IP On MySQL Master Server 2. Update Master Server config file (Terminal 1)

Code/Command (click line numbers to comment):

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 ~]#

Section 3

Edit /etc/my.cnf file and add below entries. cat /etc/my.cnf | egrep "server-id|bind-address|log-bin" 3. Restart MySQL Server (Terminal 1) systemctl restart mysqld

Code/Command (click line numbers to comment):

1
2
3

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

Section 4

4. Create user for replication (Terminal 1) *** Grant REPLICATION SLAVE privilege to myreplica select host,user from mysql.user where user='replica'; CREATE USER 'replica'@'192.168.0.102' IDENTIFIED WITH mysql_native_password BY 'Mysql@123';

Code/Command (click line numbers to comment):

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

Section 5

select host,user from mysql.user where user='replica'; GRANT REPLICATION SLAVE ON *.* TO 'replica'@'192.168.0.102'; FLUSH PRIVILEGES; FLUSH TABLES WITH READ LOCK; 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)

Section 6

5. Note Down Master Log Position (Terminal 1) SHOW MASTER STATUS; 6. Backup All database of Master (Terminal 2)

Code/Command (click line numbers to comment):

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>

[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]#

Section 7

mysqldump --all-databases --master-data > /u01/mysql/data/backup/alldbdump.sql <---- 7. Unlock Master Database (Terminal 1) UNLOCK TABLES;

Code/Command (click line numbers to comment):

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

mysql>

Section 8

8. Transfer Database Backup Dump File to Slave Server scp alldbdump.sql [email protected]:/u01/mysql/backup On MySQL Slave Server

Code/Command (click line numbers to comment):

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

Section 9

9. Update Slave Server Config File Edit the /etc/my.cnf file and add below entries cat /etc/my.cnf | egrep "server-id|bind-address|log-bin" 10. Restart MySQL Server

Code/Command (click line numbers to comment):

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 ~]#

Section 10

10. Restart MySQL Server systemctl restart mysqld 11. Restore Database Dump into Slave Server

Code/Command (click line numbers to comment):

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

[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]#

Section 11

show databases; 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. stop slave; 12. Setup Slave to Communicate Master Database

Section 12

12. Setup Slave to Communicate Master Database mysql CHANGE MASTER TO MASTER_HOST='192.168.0.101', MASTER_USER='replica',

Code/Command (click line numbers to comment):

[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>

Section 13

MASTER_PASSWORD='Mysql@123', MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=2369; START SLAVE; SHOW PROCESSLIST; Waiting on empty queue

Section 14

Waiting for master to send event Slave has read all relay log; waiting for more updates SHOW SLAVE STATUS \G; Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.101 Master_User: replica Master_Log_File: mysql-bin.000009

Section 15

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates 13. Test Replication and Verify the Results Create Database Operation a1) Create Database Operation on Master

Section 16

a1) Create Database Operation on Master show databases; create database mynsp; use mynsp; show tables;

Code/Command (click line numbers to comment):

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>

Section 17

a2) Verify Create Database Operation on Slave show databases; use mynsp; show tables;

Code/Command (click line numbers to comment):

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>

Section 18

Create Table Operation b1) Create Table Operation on Master create table MYSCB_DBA ( -> ID int, -> NAME varchar(20), -> ROLE varchar(20), -> primary key (ID)) -> ENGINE=InnoDB;

Code/Command (click line numbers to comment):

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>

Section 19

show tables; <------ b2) Verify Create Table Operation on Slave

Code/Command (click line numbers to comment):

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

mysql>

Section 20

MYSCB_DBA <------ Insert Table Operation c1) Insert Table Operation on Master

Section 21

SELECT * FROM MYSCB_DBA; INSERT INTO MYSCB_DBA values(1,'SIRAJ','DBA'); INSERT INTO MYSCB_DBA values(2,'RAGHU','DBA'); INSERT INTO MYSCB_DBA values(3,'VINOD','DBA'); INSERT INTO MYSCB_DBA values(4,'VIJAY','DBA');

Code/Command (click line numbers to comment):

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>

Section 22

INSERT INTO MYSCB_DBA values(5,'SURESH','DBA'); INSERT INTO MYSCB_DBA values(6,'SENTHIL','DBA'); INSERT INTO MYSCB_DBA values(7,'CHANDRA','DBA'); INSERT INTO MYSCB_DBA values(8,'PRASANNA','DBA'); INSERT INTO MYSCB_DBA values(9,'GOVARDINI','DBA'); INSERT INTO MYSCB_DBA values(10,'RAJASEKHAR','L2DBA');

Section 23

INSERT INTO MYSCB_DBA values(11,'RAJKUMAR','MANAGER'); COMMIT; SELECT * FROM MYSCB_DBA; c2) Verify Insert Table Operation on Slave

Code/Command (click line numbers to comment):

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>

Section 24

SELECT * FROM MYSCB_DBA; Update Table Operation d1) Update Table Operation on Master

Code/Command (click line numbers to comment):

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>

Section 25

update MYSCB_DBA set role='L1DBA' where NAME='RAJASEKHAR'; COMMIT; SELECT * FROM MYSCB_DBA WHERE NAME='RAJASEKHAR'; L1DBA <------- d2) Verify Update Table Operation on Slave

Section 26

d2) Verify Update Table Operation on Slave SELECT * FROM MYSCB_DBA WHERE NAME='RAJASEKHAR'; L1DBA <----- Delete Table Operation

Code/Command (click line numbers to comment):

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

mysql>

Section 27

Delete Table Operation e1) Delete Table Operation on Master DELETE FROM MYSCB_DBA WHERE NAME='RAJASEKHAR'; commit;

Code/Command (click line numbers to comment):

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>

Section 28

SELECT * FROM MYSCB_DBA WHERE NAME=' RAJASEKHAR '; <----- e2) Verify Delete Table Operation on Slave

Code/Command (click line numbers to comment):

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

Section 29

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

Comments (0)

Please to add comments

No comments yet. Be the first to comment!