[MySQL]복제구축(mysqldump로 백업하기)

1. MySQL복제란?(Replication)

물리적으로 다른 서버의 저장공간안에 동일한 데이터를 복사하는 것

즉, 1개의 Master 서버를 여러개의 Slave 서버에 복제하는 작업

 

2. 왜 사용하는가? 무슨목적으로? 1. 백업 2.부하분산

1) 백업

실시간으로 Master서버에서 변경된 데이터를 Slave서버에 복사하여 백업할 수 있습니다.

그래서 Master서버의 장애가 발생하였을 경우 Slave서버로 변경할 수 있습니다.

2) 부하분산
사용자가 증가하여 1대의 서버에서 읽기 부하가 발생할 경우 유용하게 사용가능

그래서 Master는 INSERT/UPDATE/DELETE 요청을 받아 쓰기전용으로 사용하고,

Slave는 로 SELECT 요청을 받아 읽기전용으로 사용

 

3. 실시간으로 데이터 복제가 가능한거야? NO!

비동기적인 방식으로 마스터의 데이터가 슬레이브로 복사되는 방법이기 때문에

마스터와 슬레이브가 항상 같은 데이터를 가지고 있다고 볼 수없다.

또한 Master서버의 내용을 Slave서버에 복사하기 때문에

Slave서버의 데이터를 변경하면 Master서버에는 반영되지 않습니다.

그래서 마스터는 READ/WRITE가능한데, 슬레이브는 ONLY-READ

 

4. 어떻게 진행되는가? 바이러니 로그와 릴레이 로그에 의해

1. Master에 Session스레드를 통해서 데이터 변경요청을 받으면, 자신의 DB와 바이너리 로그에 저장

2. Slave가 복사하고자 하는 내용을 요청하면, Dump스레드가 Binary Log내용을 읽습니다 (바이너리가 먼저 가는거)

3. Dump 스레드는 읽은 내용을 Slave의 IO스레드에게 전달합니다. 
4. Slave의 IO스레드는 받은 내용을 Relay Log에 저장합니다.

5. Slave의 SQL스레드는 Relay Log를 읽고 자신의 데이터베이스에 저장합니다.

5. MySQL복제구축 방법

 

5-1. MySQL복제구축하기->Master Server 구축

1) my.cnf(설정파일) 수정

my.cnf 파일은 MySQL 서버에 관련된 옵션을 저장하는 단 하나의 설정 파일입니다.
해당 디렉토리에 접근하여 [mysqld]그룹의 기본옵션 중 다음과 같은 3가지를 수정합니다.

1. server_id를 설정합니다. 이것은 MySQL이 내부적으로 식별하는 ID값입니다.
2. log_bin에 바이너리 로그파일명을 설정합니다.
3. binlog_do_db에 Master에서 Slave로 복제할 데이터베이스명을 설정합니다.

 

2) MySQL 재시작
수정한 설정파일을 저장하기 위해 재시작하여 MySQL에 접속합니다.

 

3) Master와 Slave서버를 연결해줄 계정생성 및 권한부여
계정의 이름은 slaveuser2, 계정의 비밀번호는 slavepw2로 설정합니다.

또한 생성한 계정에 Replication Slave, Replication Client, Execute 권한을 부여합니다.
Replication Slave권한으로 복제기능을 수행할 수 있습니다.
Replication Client권한으로 show master status와 show slave status 명령어를 수행할 수 있습니다.

Execute 권한으로 함수나 프로시저를 실행할 수 있습니다.

 

또한, 부여한 권한의 변경사항을 다음과 같은 명령어를 통해 저장합니다.

 

5) Master 확인
Master의 정확한 상태를 확인하기 위해 READ LOCK을 수행합니다.

그래서 Master의 데이터베이스를 복제하는 동안 또 다른 트랜잭션의 읽기 작업을 제한합니다.

Show master status명령어를 통해
Master Server에서 바이너리 로그파일명(File)과 저장된 위치(Position)을 확인합니다.
이후 Slave와 연결하는 계정을 위해 Master의 바이너리 로그의 정보가 사용 될 것입니다.

6) 아카이브 생성 (Master 데이터복사)
mysqldump를 이용해 Slave서버로 복사할 Master데이터를 백업하여 아카이브를 생성합니다.

이때 Slave서버의 SQL스레드를 정지시켜 데이터가 깨지는 문제를 방지할 수 있습니다.

그래서 현재 employees라는 데이터베이스를 dump_0726.sql 파일로 복사하여 저장하였습니다.

mysqldump –u root –P –B 데이터베이스명 > 백업파일명.sql

이처럼 데이터 파일을 덤프 하여 백업을 하면 데이터베이스에 문제가 발생하였을 때 특정 시점으로 복원이 가능합니다. 하지만 덤프 받는 데이터가 많을수록 시간이 오래걸리고 용량이 커질 수 있습니다

+덤프명령어 모음

https://bluebreeze.co.kr/462

mysqldump 옵션 스키마, 데이터만 백업

https://applejara.tistory.com/564

[MYSQL]mysqldump 백업

https://semoa.tistory.com/172

MySQL 백업과 복구 : mysqldump, mysqladmin

https://refe.tistory.com/entry/mysql-%EA%B8%B0%EB%B3%B8-%EB%AA%85%EB%A0%B9%EC%96%B4

mysql 기본 명령어

5-2. MySQL복제구축하기->Slave Server 구축

1) my.cnf(설정파일) 수정
my.cnf 파일은 MySQL 서버에 관련된 옵션을 저장하는 단 하나의의 설정파일입니다.
해당 디렉토리에 접근하여 [mysqld]그룹의 기본옵션 중 다음과 같은 3가지를 수정합니다.

1. server_id를 설정합니다. Master Server와 구별되게 설정합니다.
2. Relay-log에 릴레이 로그파일명을 설정합니다.
read-only를 추가하여 Slave의 수정으로 인한 Master와의 데이터 충돌을 방지합니다.

2) MySQL 재시작
수정한 설정파일을 저장하기 위해 재시작하여 MySQL에 접속합니다.

 

3) Master 데이터적재
Master에서 덤프 한 dump_0726.sql 데이터 파일을 Slave Server로 가지고 옵니다.
mysql–u root –P < 백업파일명.sql

 

4) Slave 중지 및 재설정

5) Master에서 Slave로 복제시작
마스터의 host주소 = 현재 mysql 서버가 저장된 ip주소
master user = slave와 연결할 수 있는 사용자
master password = slave를 생성할 때 만든 비밀번호
master_log_file = master에서 바이너리 로그파일명
master_Log_pos = 바이너리 로그파일의 위치
master_log_file과 master_Log_pos는 앞서 show master staus;에서 확인한 내용입니다.

 

6) Slave 상태확인
Show slave status명령어를 통해
slave와 연결이 된 user정보, 바이너리 로그파일정보, 릴레이 로그파일 정보 등  
slave의 상태를 확인할 수 있습니다.

이때 처음에는 Slave_IO_running, Slave_SQL_running가 ‘NO’임을 확인할 수 있습니다.
이것은 복제를 수행하기 위한 Slave를 생성하기만 하고 아직 데이터를 동기화 하지 않았기 때문입니다.

그래서 START SLAVE명령을 통해 Slave IO스레드와 SQL스레드를 활성화 시킵니다.

 

7) Master에서 Lock해제
UNLOCK TABLES명령을 통해 백업하기 위해 걸었던 READ LOCK를 해제합니다.

 

6. Master -> Slave 데이터 동기화 테스트

1) Master에서 Insert수행
복제를 수행할 employees데이터베이스의 employees테이블에 데이터를 insert합니다.

2) Slave에서 Select수행
복제가 성공적으로 구축 되었다면
Master에서 Insert한 내용을 Slave에서 Select하여 확인 할 수 있습니다.

따라서 Master에서 INSERT/UPDATE/DELETE와 같은 쓰기작업을 수행하고,
Slave에서 SELECT와 같은 읽기 작업을 나누어 처리하면 부하분산이 가능합니다.

 

참고

https://m.blog.naver.com/PostView.nhn?blogId=ncloud24&logNo=221443452960&proxyReferer=http%3A%2F%2Fwww.google.com%2Furl%3Fsa%3Dt%26rct%3Dj%26q%3D%26esrc%3Ds%26source%3Dweb%26cd%3D4%26ved%3D2ahUKEwjMpvaRidLjAhUrE6YKHS3rDpQQFjADegQIBhAB%26url%3Dhttp%253A%252F%252Fm.blog.naver.com%252Fncloud24%252F221443452960%26usg%3DAOvVaw2-GDBVo0ypRGEeOv3QquzR

[리눅스] MySQL Replication 설정 방법

https://gywn.net/2011/12/mysql-replication-1/

MySQL Replication 이해(1) – 개념 | gywndi's database

http://m.blog.daum.net/creazier/15311372?np_nil_b=-2

[펌] MySQL Replication(복제) - 단방향 이중화 / 양방향 이중화