MySQL Replication 구성

직접 테스트한 결과를 토대로 작성한 문서입니다.

해당 문서에서는 Master, Slave의 단방향 Replication 구성에 관한 내용입니다.

감사합니다.

DB version : MySQL 5.5.45

OS version : Centos 6

1. MySQL Replication 구성

1.1 Master DB의 백업본을 Slave DB로 복구

## Master -> Slave로 복제를 위한 계정 생성 ##

mysql> grant replication salve on *.* to 'replication'@'192.168.56.112' identified by 'replication';

- Slave DB쪽에서 Master DB로 접속하여 변경 된 정보를 가져가야하기때문에 Master DB에서 계정 생성

- 보안상 문제로 계정을 생성 할 때는 'replication'@'%' 말고 해당 Slave DB 서버의 ip를 넣어 생성 (%는 모든 ip에서 접속 가능)

## Master DB 전체 백업 ##

[root@master mysql]# mysqldump -uroot -p --socket=/mysql/tmp/mysql.sock --all-databases --events --master-data=2 > full.dmp

Enter password:

[root@master mysql]# ls -alrt

total 572

drwxr-xr-x.  5 mysql mysql   4096 Feb 12 14:49 data

drwxr-xr-x.  2 mysql mysql   4096 Feb 12 14:50 log

dr-xr-xr-x. 23 root  root    4096 Feb 12 14:50 ..

drwxr-xr-x.  5 mysql mysql   4096 Feb 12 15:45 .

drwxr-xr-x.  2 mysql mysql   4096 Feb 12 15:53 tmp

-rw-r--r--.  1 root  root  564538 Feb 12 15:53 full.dmp

- my.cnf 변경 후 mysqld_safe로 서비스를 띄워줬기 때문에 mysqldump 옵션에 --socket 옵션을 추가하여 해당 인스턴스로 붙게 설정

- master-data 옵션의 값을 2로줌으로써, Master DB의 MASTER_LOG_FILE, MASTER_LOG_POS 부분에 주석처리 ( Relication을 구성 할 때 필요한 부분)

## 맨 하단 부분에 MASTER_LOG_FILE과 MASTER_LOG_POS의 값 확인 ##

## Slave DB에 백업 본 복구 ##

[root@slave tmp]# mysql -uroot -p --socket=/mysql/tmp/mysql.sock < full.dmp

## Replication 구성을 위한 작업 ( Slave DB에서 실행 )

mysql> change master to

    -> master_host='192.168.56.111',

    -> master_user='replication',

    -> master_password='replication',

    -> master_port=3306,

    -> master_log_file='mysql_master-bin.000006',

    -> master_log_pos=181568,

    -> master_connect_retry=1;

Query OK, 0 rows affected (0.04 sec)

 

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.56.111

                  Master_User: replication

                  Master_Port: 3306

                Connect_Retry: 1

              Master_Log_File: mysql_master-bin.000006

          Read_Master_Log_Pos: 181810

               Relay_Log_File: relay-bin.000002

                Relay_Log_Pos: 502

        Relay_Master_Log_File: mysql_master-bin.000006

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

              Relay_Log_Space: 652

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

1 row in set (0.00 sec)

- Slave DB쪽도 Master DB와 같이 my.cnf 수정 후 mysqld_safe로 서비스를 올렸기 때문에 --socket 옵션을 사용

- Slave DB쪽에서 CHANGE MASTER TO 를 입력하여 Master와의 Replication 인식

- START SLAVE 명령어로 Replication 구동

- SHOW SLAVE STATUS\G 명령어를 이용하여 Replication 구동 확인 

  (Slave_IO_Running 부분과 Slave_SQL_Running 부분이 yes로 되어있는지 확인)

## Master DB에 데이터 생성 ##

mysql> create table kdh (a varchar(10), b int(10));

Query OK, 0 rows affected (0.02 sec)

mysql> CREATE PROCEDURE test_insert()

    -> BEGIN

    -> DECLARE i INT DEFAULT 1;

    -> WHILE i <= 1000 DO

    -> INSERT INTO test.kdh VALUES ('Seoul',i);

    -> SET i = i + 1;

    -> END WHILE;

    -> END$$

Query OK, 0 rows affected (0.03 sec)

mysql> select * from kdh;

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

| a     | b    |

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

| Seoul |    1 |

| Seoul |    2 |

| Seoul |    3 |

| Seoul |    4 |

| Seoul |    5 |

| Seoul |    6 |

| Seoul |    7 |

| Seoul |    8 |

| Seoul |    9 |

| Seoul |   10 |

| Seoul |   11 |

| Seoul |   12 |

| Seoul |   13 |

| Seoul |   14 |

| Seoul |   15 |

| Seoul |   16 |

| Seoul |   17 |

| Seoul |   18 |

| Seoul |   19 |

| Seoul |   20 |

- Slave DB쪽에서 복제가 잘 되는지 확인 하기 위하여 TEST 테이블 생성 및 프로시져를 이용한 데이터 생성

## Slave DB 복제 확인 ##

mysql> select * from kdh;

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

| a     | b    |

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

| Seoul |    1 |

| Seoul |    2 |

| Seoul |    3 |

| Seoul |    4 |

| Seoul |    5 |

| Seoul |    6 |

| Seoul |    7 |

| Seoul |    8 |

| Seoul |    9 |

| Seoul |   10 |

| Seoul |   11 |

| Seoul |   12 |

| Seoul |   13 |

| Seoul |   14 |

| Seoul |   15 |

| Seoul |   16 |

| Seoul |   17 |

| Seoul |   18 |

| Seoul |   19 |

| Seoul |   20 |

## Error 발생 시 처리 방법 ##

mysql> set sql_log_bin=OFF;

Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to 'error'@'%' identified by 'error';

Query OK, 0 rows affected (0.00 sec)

 

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> set sql_log_bin=ON;

Query OK, 0 rows affected (0.00 sec)

mysql> drop user 'error'@'%';

- 해당 부분은 Master DB에서 실행

- set sql_log_bin=OFF 는 세션의 갱신을 binary 로그에 기록하지 않겠다는 부분으로 이후에 적용 된 쿼리는 Slave DB에 복제 되지 않음

- grant all on *.* to 'error'@'%' identified by 'error' 명령어로 생성 된 error 계정은 Master DB에서만 생성

- 마지막엔 Slave DB에도 적용 될 수 있도록 set sql_log_bin=ON으로 해주고 error 계정을 drop

## Slave 상태 확인 ##

mysql> show slave status\G;

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.56.111

                  Master_User: replication

                  Master_Port: 3306

                Connect_Retry: 1

              Master_Log_File: mysql_master-bin.000006

          Read_Master_Log_Pos: 362383

               Relay_Log_File: relay-bin.000002

                Relay_Log_Pos: 180920

        Relay_Master_Log_File: mysql_master-bin.000006

             Slave_IO_Running: Yes

            Slave_SQL_Running: No

              Replicate_Do_DB:

          Replicate_Ignore_DB:

           Replicate_Do_Table:

       Replicate_Ignore_Table:

      Replicate_Wild_Do_Table:

  Replicate_Wild_Ignore_Table:

                   Last_Errno: 1396

                   Last_Error: Error 'Operation DROP USER failed for 'error'@'%'' on query. Default database: ''. Query: 'drop user 'error'@'%''

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 362228

              Relay_Log_Space: 181225

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

                Last_IO_Error:

               Last_SQL_Errno: 1396

               Last_SQL_Error: Error 'Operation DROP USER failed for 'error'@'%'' on query. Default database: ''. Query: 'drop user 'error'@'%''

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 111

1 row in set (0.00 sec)

 

ERROR:

No query specified

- Slave_SQL_Running 부분이 No로 표시

- Last_Error: Error 'Operation DROP USER failed for 'error'@'%'' on query. Default database: ''. Query: 'drop user 'error'@'%'' 부분에 Error 정보 표시

- 해당 Slave DB에는 error 계정이 없었으므로 drop user를 했을 시 에러가 발생한 걸 확인 할 수 있음

## Error 처리 ##

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

 

mysql> set global sql_slave_skip_counter=1;

Query OK, 0 rows affected (0.00 sec)

 

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

- stop slave로 Replication 동작을 멈추고 set global sql_slave_skip_counter 를 이용하여 해당 error 부분을 skip

- error 스킵 후 start slave를 하면 해당 error 부분을 skip하고 재동작 하는 걸 확인 할 수 있다.

## Slave 상태 확인 ##

mysql> show slave status\G;

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.56.111

                  Master_User: replication

                  Master_Port: 3306

                Connect_Retry: 1

              Master_Log_File: mysql_master-bin.000006

          Read_Master_Log_Pos: 362383

               Relay_Log_File: relay-bin.000003

                Relay_Log_Pos: 260

        Relay_Master_Log_File: mysql_master-bin.000006

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

              Relay_Log_Space: 181378

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

1 row in set (0.00 sec)

 

ERROR:

No query specified