MySQL Replication 설정 (Master Slave)

시스템 환경 (Master / Slave)

OS : Ubuntu 20.10 x64

mysql : 5.7.33

IP : Master(167.179.93.128), Slave(167.179.71.47)

 

MySQL Replication Master 설정

my.cnf수정

root@masterDB:~# sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

마스터서버 설정에서 제일 중요한 부분 server-id는

server-id = 1
log-bin=mysql-bin

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
# By default we only accept connections from localhost
bind-address    = 0.0.0.0

#서버 식별자(유니크)
server-id = 1
#로그파일명
log-bin=mysql-bin
#로그파일크기
max_binlog_size = 100M
#로그보존주기
expire_logs_days = 7
#리플리케이션DB명(생략시엔 전체DB)
binlog_do_db = master

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

마스터 서버를 재시작한다.

root@masterDB:~# sudo systemctl restart mysql

masterDB에 접속해 테이블을 생성한다. 이 테이블을 이용해 데이터가 동기화되는지 확인한다.

root@masterDB:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.33-log 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> CREATE TABLE user_tb (uid INT PRIMARY KEY AUTO_INCREMENT, user_name VARCHAR(32) NOT NULL) ENGINE=INNODB; DESCRIBE user_tb;
Query OK, 0 rows affected (0.00 sec)

+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| uid       | int(11)     | NO   | PRI | NULL    | auto_increment |
| user_name | varchar(32) | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql>

작업 중 테이블에 락을 걸어 테이터 변경이 이루어지지 않도록 설정한다.

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

mysql>

masterDB가 마스터로 설정되어 정보를 리셋한다.

mysql> reset master;
Query OK, 0 rows affected (0.01 sec)

mysql>

masterDB의 상태 정보를 확인한다.

여기서 중요한 것은 File :mysql-bin.000004, Position : 555를 기억해둔다. 이 정보를 slaveDB에 설정하기 위해서다.

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

mysql>

또 다른 masterDB의 상태 정보를 확인.

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000005
         Position: 555
     Binlog_Do_DB: master
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

동기화할 DB를 덤프(백업) 파일로 출력한다. 

mysqldump -u [사용자 아이디] -p -B [동기화 대상  DB] > [덤프 파일명]

root@masterDB:~# mysqldump -u root -p -B master > dump_data.sql
Enter password:
root@masterDB:~# ls
down  dump_data.sql  snap
root@masterDB:~#

생성된 덤프 파일이다.  cat 명령어로 정보를 확인할 수 있다.

root@masterDB:~# cat dump_data.sql
-- MySQL dump 10.13  Distrib 5.7.33, for Linux (x86_64)
--
-- Host: localhost    Database: master
-- ------------------------------------------------------
-- Server version	5.7.33-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 */;

--
-- Current Database: `master`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `master` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;

USE `master`;

--
-- Table structure for table `user_tb`
--

DROP TABLE IF EXISTS `user_tb`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user_tb` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `user_tb`
--

LOCK TABLES `user_tb` WRITE;
/*!40000 ALTER TABLE `user_tb` DISABLE KEYS */;
/*!40000 ALTER TABLE `user_tb` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2021-03-21 14:11:02
root@masterDB:~#

 

MySQL Replication Slave 설정

my.cnf수정

root@slaveDB:~# sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

이 파일에서 중요한 부분은 역시 server-id는

server-id = 2
log-bin=mysql-bin

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
# By default we only accept connections from localhost
bind-address    = 0.0.0.0

server-id=2
log-bin=mysql-bin

파일을 수정하였다면 mysql 서버를 재시작해준다.

root@slaveDB:~# sudo systemctl restart mysql

masterDB에서 덤프 파일 가져와 slaveDB서버에 업로드 후 복구해준다.

root@slaveDB:~# ls
down  dump_data.sql  snap
root@slaveDB:~# mysql -u root -p < dump_data.sql
Enter password:
root@slaveDB:~#

slaveDB의 mysql서버에 접속해 masterDB에서 가져온 덤프 파일이 적용되었는지 확인한다.

master 데이터베이스가 추가된 것을 확인할 수 있다.

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

이제 본격적으로 slave를 설정을 시작한다. slave 서버를 멈추고 리셋을 해준다.

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql>

slave에서 마스터를 연결하도록 서버 정보, 계정 정보, 로그파일 정보를 설정한다.

기본적으로 masterDB의 mysql  사용자 정보와 외부에서 접속 가능하도록 포트:3306이 열려있어야 한다.

master상태 정보에서 취득한 정보.

    -> MASTER_LOG_FILE='mysql-bin.000004',
    -> MASTER_LOG_POS=555;

 

이 부분은 masterDB의 로그파일과 위치정보이며 이 정보를  mysql 서버가 재시작할 때마다 변경되므로 주의 하길 바란다.

결결이 되지 않는다면 이 부분에서 서로 정보가 다르기 때문에 접속되지 않는 경우가 많다.

mysql> CHANGE MASTER TO
    -> MASTER_HOST='167.179.93.128',
    -> MASTER_USER='tokyoaj',
    -> MASTER_PASSWORD='P@ssw0rd',
    -> MASTER_LOG_FILE='mysql-bin.000004',
    -> MASTER_LOG_POS=555;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

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

 

masterDB서버로 접속한다. 먼저 마스터 테이블에 락을 풀어준다.

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

자 이제까지 설정은 거의 완료되었다. 이제 서버를 재시작을 해주면 된다.

주의!!! 서버 시작은 마스터가 우선적으로 시작이 되어야 한다.

 

masterDB의 mysql 정지.

root@masterDB:~# sudo systemctl stop mysql;
root@masterDB:~#

slaveDB의 mysql 정지.

root@slaveDB:~# sudo systemctl stop mysql
root@slaveDB:~#

masterDB의 mysql 시작.

root@masterDB:~# sudo systemctl start mysql;
root@masterDB:~#

slaveDB의 mysql 시작.

root@slaveDB:~# sudo systemctl start mysql
root@slaveDB:~#

master와 slave 상태 정보를 확인한다.

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000004
         Position: 555
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

mysql>

아래와 같이 정보를 확인한다면 두 개의 서버는 이제 동기화 설정이 완료되었다.

Slave_IO_State: Waiting for master to send event

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 167.179.93.128
                  Master_User: tokyoaj
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 555
               Relay_Log_File: slaveDB-relay-bin.000002
                Relay_Log_Pos: 721
        Relay_Master_Log_File: mysql-bin.000004
             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: 555
              Relay_Log_Space: 930
              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: 5bc4ce2f-8a22-11eb-a47c-5600034284bf
             Master_Info_File: /var/lib/mysql/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:
1 row in set (0.00 sec)

mysql>

master에서 데이터를 1개 입력을 한다. 이 정보가 slave에서 확인되면 모든 작업은 완료가 된 것이다.

mysql> select * from user_tb;
Empty set (0.00 sec)

mysql> insert into user_tb values(1, 'USER_TXT');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user_tb;
+-----+-----------+
| uid | user_name |
+-----+-----------+
|   1 | USER_TXT  |
+-----+-----------+
1 row in set (0.00 sec)

mysql>

 

slave에서 데이터 확인.

mysql> use master
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 user_tb;
+-----+-----------+
| uid | user_name |
+-----+-----------+
|   1 | USER_TXT  |
+-----+-----------+
1 row in set (0.00 sec)

mysql>