MySQL 5.6 Master/Slave 로 구성

디비 2014. 7. 1. 13:53

Master/Slave 구성이란

Master는 원본 데이터에 대한 Read/Write 요청을 처리하며, 

Slave는 Master와 데이터 동기화를 하고, Master 장애시 Slave를 Master로 전환할 수 있는 Failover의 수단이 된다.

또한 Read 요청을 분산시켜 Master의 부하를 줄일 수 있다.


Master 구성

/etc/my.cnf 에 해당 항목 추가

log-bin=로그파일명, servier-id=Master/Slave 군에서 유일한 값 지정

1
2
3
4
5
6
7
$ vi /etc/my.cnf
[mysqld]
#log setting
log-bin = mysql-bin            
 
#Replication for master server   
server-id = 1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
## slave에서 사용할 계정을 등록
## 허용할 slave 서버의 ip 대역을 지정하려면, 'repl'@'10.10.10.%' 와 같이 지정하면 된다.
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '1212';
Query OK, 0 rows affected (0.02 sec)
 
mysql> grant replication slave on *.* to 'repl'@'%';
Query OK, 0 rows affected (0.01 sec)
 
## 테이블 읽기만 허용하도록 잠금후, 덤프
mysql> flush tables with read lock;
mysql> quit;
 
## 특정 DB만 리플리케이션하는 경우
## mysqldump -u root -p [DB명] >dbbackup.db
$ mysqldump -u root -p --all-databases --lock-all-tables > dbbackup.db
Enter password:
 
## mysql에 연결하여 master의 로그 정보를 확인 후, 잠금 해제
$ mysql -uroot -p
mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 496
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
 
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)


Slave 구성

/etc/my.cnf 에 해당 항목 추가

server-id=겹치지 않게 지정

1
2
3
$ vi /etc/my.cnf
#Replication for master server   
server-id = 2
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
## master 에서 백업한 디비 복원
## 특정 DB만 리플리케이션하는 경우
## mysql -u root -p [DB명] < dbbackup.db
$ mysql -u root -p  < dbbackup.db
 
## mysql restart
$ service mysql restart
 
## set master info
$ mysql -uroot -p
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> change master to
    -> MASTER_HOST='172.20.44.156',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='1212',
    -> MASTER_LOG_FILE='mysql-bin.000003',
    -> MASTER_LOG_POS=496;
Query OK, 0 rows affected, 2 warnings (0.07 sec)
 
mysql> start slave;
Query OK, 0 rows affected (0.04 sec)
 
## 잘 되었는지 확인
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.20.44.156
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 120
               Relay_Log_File: localhost-relay-bin.000005
                Relay_Log_Pos: 283
        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: 120
              Relay_Log_Space: 460
              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: 00ef0be4-8fd9-11e3-acdd-0800271a9bb3
             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 the slave I/O thread to update it
           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
1 row in set (0.01 sec)

Master에서 종종 사용하는 명령어

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
## binlog의 상태정보를 보여줌
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: mysql-bin.000004
         Position: 782
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
 
 
##slave의 목록
mysql> SHOW SLAVE HOSTS\G
*************************** 1. row ***************************
 Server_id: 2
      Host:
      Port: 3306
 Master_id: 1
Slave_UUID: 6ef65b93-00f0-11e4-8e51-080027bf5330
1 row in set (0.00 sec)
 
 
## binary log의 목록
mysql> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       143 |
| mysql-bin.000002 |       143 |
| mysql-bin.000003 |       519 |
| mysql-bin.000004 |       782 |
+------------------+-----------+
4 rows in set (0.00 sec)
 
## binary log의 event 목록
##사용법 : SHOW BINLOG EVENTS [IN 'logfile'] [FROM position of logfile] [LIMIT [offset,] rows]
mysql> SHOW BINLOG EVENTS\G
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 120
       Info: Server ver: 5.6.16-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000001
        Pos: 120
 Event_type: Stop
  Server_id: 1
End_log_pos: 143
       Info:
2 rows in set (0.00 sec)
Posted by 약올랑
openclose

티스토리툴바