MySQL Replication 설정

MySQL Replication

Replication이란 MySQL 서버를 2대 이상으로 놓고 Master(1대) Slave(n대)로 구축한다.

Slave(들)은 Master의 데이터를 실시간으로 동기화하여 이중화 역할을 하며, 각각의 역할 분담으로 부하분산의 효과를 볼 수도 있다.

Master의 경우 INSERT, UPDATE, DELETE를 처리하고 Slave는 SELECT를 처리한다.

 =(어찌보면 당연한 이야기지만 Slave는 Master의 데이터를 가져오는 방식인데 slave에서 데이터의 조작이 이루어지는 INSERT 등을 처리하게 되면

   데이터가 꼬여버린다)

Master에 문제가 생긴다면 Slave가 Master의 역할을 대신하여 승급이 되기도 한다.

아래에 내용에는 MySQL Replication 방법과 함께 dump를 생성하는 방법도 기입했다.

1-1. 구축환경

CentOS 6.7 서버 2개를 구축하여 Master 1대, Slave 1대를 구축하고 mysql은 yum 설치로 진행하였습니다.

1-2. my.cnf 설정

vi /etc/my.cnf 에서 아래와 같이 내용을 수정 및 추가

Master Server

log-bin = mysql-bin              ## 로그파일명

max_binlog_size = 100M        ## 로그파일크기

expire_logs_days = 7             ## 로그보존주기

server-id = 1                       ##서버의 고유 ID로 slave와 다른 숫자여야 한다. 보통 Master를 1, Slave를 2 이상의 값으로 순차적으로 부여

binlog_do_db = db1              ## Replication을 진행할 DB. 해당 옵션 미부여 시 전체 DB 진행

* 기본적으로 파란색으로 표시한 옵션은 가장 기본적인 옵션이다.

my.cnf 설정변경 후 mysql 재시작

# service mysqld restart

1-3. 계정 생성

mysql> GRANT REPLICATION SLAVE ON *.* TO 'ID'@'%' IDENTIFIED BY '비밀번호';

mysql> flush privileges;

* %부분은 접속 허용 대상 IP로, %는 모든 IP 허용을 뜻한다.

1-4. DB값 추가

좀 더 정확한 테스트를 위해서 테스트 용의 값들을 입력 (테스트기 때문에 생략 가능)

mysql> create database world;

mysql> show databases;

mysql> use world;

mysql> create table member(no int NOT NULL, t_name varchar(20), content TEXT(255));

mysql> insert into member values('1', 'kkk', 'hi');

1-5-1. 백업 방법 1

기본적으로 Replication은 Dump를 최소 1번 이상 받아야 한다.

[Master]

mysql> FLUSH TABLES WITH READ LOCK;        ## DB 쓰기 금지

[root@test1 ~]# tar -cvf /tmp/mysqldata.tar .

[Slave]

[root@test2 ~]# tar -xvf /tmp/mysqldata.tar

1-5-2. 백업 방법 2 (추천)

[Master]

mysql> FLUSH TABLES WITH READ LOCK;        ## DB 쓰기 금지

[root@test1 ~]# mysqldump -u root -p world > dump_file.sql

mysql> show master status;

+--------------------+----------+----------------+--------------------+

| File                    |  Position | Binlog_Do_DB | Binlog_Ignore_DB |

+--------------------+----------+----------------+--------------------+

| mysql-bin.000004 |       659 |                    |                         |

+--------------------+----------+----------------+--------------------+

1 row in set (0.00 sec)

* 위 내용에서 File명과 Position을 기록해둔다.

mysql> UNLOCK TABLES;                    ## 쓰기 금지 해제

[root@test1 ~]# scp dump_file.sql root@SlaveIP:/root/

* scp를 이용하여 slave에 dump 파일 전송

[Slave]

** world라는 database를 생성하였기 때문에 slave에도 같은 database를 생성해주어야 한다.

mysql> create database world;

[root@test2 ~]# mysql -u root -p world < dump_file.sql

mysql> select * from member;

+----+--------+---------+

| no  | t_name | content |

+----+--------+---------+

|  1   | kkk     | hi         |

+----+--------+---------+

1 row in set (0.00 sec)

** Master에서만 추가해줬던 필드 내용들이 slave에서도 확인이 된다.

1-6. Master 접속 설정

[Slave]

mysql> CHANGE MASTER TO

-> master_host = 'MASTER IP',

-> master_port = 3306,        ## MySQL 기본 포트. 포트 변경 시 변경한 Port 입력

-> master_user = 'ID',          ## replication 용으로 생성했던 계정명

-> master_password = '비밀번호',

-> master_log_file = 'mysql-bin.000004',        ## 위에서 확인했던 file명

-> master_log_pos = 659;                           ## 위에서 확인했던 Posiotion

mysql> start slave;

2. 확인

** 확인 명령어는 대문자로 입력한다. 소문자로 입력시 출력이 깨지는 현상 발생

[Master]

mysql> SHOW MASTER STATUS\G

*************************** 1. row ***************************

            File: mysql-bin.000004

        Position: 659

    Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)

[Slave]

mysql> SHOW SLAVE STATUS\G

*************************** 1. row ***************************

               Slave_IO_State: Connecting to master

                  Master_Host: 192.168.0.151

                  Master_User: rep

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000004

          Read_Master_Log_Pos: 659

               Relay_Log_File: mysqld-relay-bin.000001

                Relay_Log_Pos: 4

        Relay_Master_Log_File: mysql-bin.000004

             Slave_IO_Running: No

            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: 659

              Relay_Log_Space: 106

              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: NULL

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 2013

                Last_IO_Error: error connecting to master 'rep@192.168.0.151:3306' - retry-time: 60  retries: 86400

               Last_SQL_Errno: 0

               Last_SQL_Error:

1 row in set (0.00 sec)