5. [MySQL]리플리케이션 방법

1. [MySQL] 설치(CentOS)

2. [MySQL] 기본 명령어 익히기

3. [MySQL] 외부에서 접근하는방법

4. [MySQL]워크벤치 설치하기

5. [MySQL]리플리케이션 방법

6. [MySQL]my.conf 환경 설정하기

7. [MySQL] MySQL 리플리케이션 UUID 관련 에러

8. [MySQL] mysql 데이터샘플 밀어 넣기

9. [MySQL]Workbench & DA# 모델링 그리기

 

1. 리플리케이션(Replication) 이란..

 

리플리케이션(Replication)은 복제를 뜻하며 2대 이상의 DBMS를 나눠서 데이터를 저장하는 방식이며, 사용하기 위한 최소 구성은 Master / Slave 구성을 하여야 됩니다.

 

 

Master DBMS 역할 : 

웹서버로 부터 데이터 등록/수정/삭제 요청시 바이너리로그(Binarylog)를 생성하여 Slave 서버로 전달하게 됩니다

(웹서버로 부터 요청한 데이터 등록/수정/삭제 기능을 하는 DBMS로 많이 사용됩니다)

 

Slave DBMS 역할 : 

Master DBMS로 부터 전달받은 바이너리로그(Binarylog)를 데이터로 반영하게 됩니다

(웹서버로 부터 요청을 통해 데이터를 불러오는 DBMS로 많이 사용됩니다)

 

 

2. 목적 

MySQL 리플리케션(Replication)은 사용목적은 크게 실시간 Data 백업과 여러대의 DB서버의 부하를 분산 시킬수 있습니다

 

1, 데이터의 백업

 

예로 Master 서버를 데이터의 원본서버, Slave서버를 백업서버로 지칭하겠습니다

먼저 Master 서버에 DBMS의 등록/수정/업데이터가 생기는 즉시 Slave 서버의 변경된 데이터를 전달하게 됩니다 이러한 과정으로 데이터의 백업을 할수 있으며, 또한 Master 서버의 장애가 생겼을 경우 Slave 서버로 변경하여 사용할수 있습니다.

 

 

그림으로 표현한다면 먼저 사용자가 사용하는데 발생하는 쿼리를 Master 서버에 요청하며, Master 서버의 발생된 쿼리를 Slave 서버로 전달하게되어 백업의 용도로 사용할수 있습니다

 

 

 

 

2. DBMS의 부화분산

 

사용자의 폭주로 인해 1대의 DB서버로 감당할수 없을때, MySQL 리플리케이션(Replication)을 이용하여 같은 DB 데이터를 여러대를 만들수 있기에 부하를 분산하수 있습니다

 

 

그림으로 표현한다면 Master 서버를 등록/수정/삭제를 사용하는 서버로 사용하고, Slave 서버를 데이터를 읽는용도로 사용하게 되면 DBMS의 부하를 분산하는 용도로 사용할 수 있게 됩니다

 

3. 주의사항 

 

MySQL Replication을 사용시 다음과 같은 주의하여야 될 사항들이 있습니다 반드시 필독 후 하시고 진행하시기 바랍니다.

 

1. 호환성을 위해 Replication을 사용하는 MySQL의 동일하게 맞추는것이 좋습니다

 

2. Replication을 사용하기에 MySQL 버전이 다른 경우 Slave 서버가 상위 버전 이여야 합니다

 

3. Replication을 가동시에 Master 서버, Slave 순으로 가동시켜야 합니다

 

 

이번 리플리케이션 포스팅에서는 2대의 DBMS를 이용하여 등록/수정/삭제를 하는 Master 서버와 Select 를 사용하는 Slave 서버로 하겠으며, 구성은 다음과 같습니다

 

 

위 그림과 같이 웹서버에서 데이터 등록/수정/삭제는 Master 서버로 구성하고, 데이터를 읽을경우 Slave 서버로 구성하여 MySQL DBMS를 이중화 하도록 하겠습니다.

 

여기서 잠깐!! 백업 복구 연습하고 넘어가자!!

[mysql 백업 및 복구형식]

백업형식 : ./mysqldump -u DB계정명 -p 데이터베이스명 테이블명 > 저장할파일명
복구형식 : ./mysql -u DB계정명 -p 데이터베이스명 < 저장한파일명

[mysql 백업방법]
# cd /usr/local/mysql/bin    ->  mysql의 bin 디렉터리로 이동후 아래 명령어 실행
./mysqldump -u root -p -A > alldump.sql    ->  mysql 전부 백업
./mysqldump -u root -p --lock-all-tables -A > test.sql     ->  테이블에 Lock이 걸려서 덤프가 안떠질경우 해당명령어로 백업
./mysqldump -u root -p  DB명 > dump.sql      ->  특정 데이터베이스 백업
./mysqldump -u root -p --databases DB1 DB2 DB3  > dump.sql      ->  다중 데이터베이스 백업 ( 다중디비 백업시에 --databases 명령어 이용 )
./mysqldump -u root -p DB명 TABLE명 > DB1_TABLE1.sql         ->  특정 DB 의 특정TABLE 백업
./mysqldump -u root -p DB명 TABLE1 TABLE2 > DB1_TABLE12.sql         -> 특정DB의 테이블 1,2 백업

[mysql 복구]
mysql  -u root -p < dump.sql        -> 모든DB를 덤프떳을 경우
mysql -u root -p DB명 < dump.sql    -> 특정DB만 덤프떳을 경우 (특정 DB만 복구할때는 같은 이름의 DB를 생성해놓고 해당명령어를 실행해야함)

[위명령어로 안될경우]
dump받은 파일이 있는 위치로 이동한 후 mysql 에 접속하여 아래 해당명령어 실행
mysql > source alldump.sql        -> 해당명령어로 모든 DB 복구 (올덤프 떳을경우)
mysql > use 해당DB명        -> 해당명령어로 복구할 DB선택 이후 아래명령어로 복구진행 (DB가 없다면 생성해야함)
mysql > source dump.sql

4.  마스터 설정하기

이제 MySQL Replication을 설정 해보도록 하겠으며, 먼저 Master 서버의 설정부터 하겠습니다

MySQL 리플리케이션을 사용하기 위해선 먼저 DB, 계정, 리플리케이션 계정을 생성하여 됩니다

구성정보는 아래와 같이 하겠습니다.

[Master 서버 DB, 계정정보]

 

IP : 192.168.56.121(Master), 192.168.56.122(Slave)

DadaBases : repl_db

ID : user2

PW : 1q2w3e4r**

 

[Replication 계정 정보]

 

IP : 192.168.56.121 - (Master)

ID : repl_user

PW : 1q2w3e4r**

 

- Master 서버에 데이터를 Slave 서버로 복제하기 위해선 MySQL 계정이 필요합니다

- MySQL root 계정으로 사용하는것은 보안상 좋지 않기 때문에 복제계정을 생성하는것이 좋습니다

 

1. MySQL DB, 계정생성 및 권한설정

 

1) DB 생성

mysql> create database repl_db default character set utf8;

 

2) 계정생성

mysql> create user user2@'%' identified by '1q2w3e4r**';

 

3) 권한부여

mysql> grant all privileges on repl_db.* to user2@'%' identified by '1q2w3e4r**';

 

 

2. 리플리케이션 계정생성

mysql> grant replication slave on *.* to 'repl_user'@'%' identified by '1q2w3e4r**';

 

 

3. MySQL 설정 - my.cnf

vi /etc/my.cnf [mysqld] log-bin=mysql-bin server-id=1

 

처음 설치시 위와 같은 설정이되어 있으며, 없다면 새로 추가하시면 됩니다

 

 

4. MySQL 재시작

# systemctl restart mysql 또는 service mysqld restart 

 

 

5. Master 서버 정보 확인

* File : MySQL 로그파일
* Position : 로그 파일내 읽을 위치
* Binlog_Do_DB : 바이너리(Binary)로그 파일(변경된 이벤트 정보가 쌓이는 파일)
* Binlog_Ignore_DB : 복제 제외 정보

# 참고 url : RDS yongdev91.tistory.com/10

 

1. [MySQL] 설치(CentOS)

2. [MySQL] 기본 명령어 익히기

3. [MySQL] 외부에서 접근하는방법

4. [MySQL]워크벤치 설치하기

5. [MySQL]리플리케이션 방법

6. [MySQL]my.conf 환경 설정하기

7. [MySQL] MySQL 리플리케이션 UUID 관련 에러

8. [MySQL] mysql 데이터샘플 밀어 넣기

9. [MySQL]Workbench & DA# 모델링 그리기