MySQL Replication(복제)

# MySQL Replication (복제)

MySQL Replication(복제)은 한 개나 2개 이상의 MySQL database server(slave)가 

하나의 MySQL database server(master)로부터 데이터를 복제해 갈 수 있는 기능을 제공한다.

MySQL Replication은 비동기 방식으로 처리된다. 즉 slave는 master로부터 데이터를 받아 복제하기 위해 항상 master에 연결되어 있을 필요가 없다.

MySQL Replication은 Binary logging mechanism을 사용하여 이뤄진다.

Master 서버는(MySQL 인스턴스)는 binary log에 변경된 데이터 정보를 기록하며 이 log를 slave가 읽어서 실행함으로써 복제가 된다.

Master에서 binary logging이 활성화되면 Master의 모든 데이터 구문이 binary log에 저장되며 

slave는 binary log의 모든 내용을 복사해서 읽어온다.

따라서 slave는 log 파일내의 position을 유지할 필요가 있다.

그래야 로그파일 전체를 처음부터 읽지 않고 효과적으로 로그 파일을 운영할 수 있기 때문이다.

(여기서 position은 로그파일 내의 위치를 의미하며 어느 부분부터 읽겠다는 것을 의미한다.)

 

# Master Configuration

1. Replication User 생성

mysql> GRANT REPLICATION SLAVE ON *.* TO '계정ID'@'%' IDENTIFIED BY '계정password';

 

2. Configuration 설정 및 코드 작성(my.ini)

my.ini 위치: 숨김파일보기 설정 후 C:\ProgramData\MySQL\MySQL Server x.x

(예시 : C:\ProgramData\MySQL\MySQL Server 5.7)

 

[mysqld]

log-bin=mysql-bin              

server-id=1  (1~2^32사이 숫자, master와 slave의 server-id를 다르게 입력해줘야 한다.)

(예를 들어, Master 역할을 하는 PC에서 my.ini 의 server-id를 server-id=1와 같이 지정해줬다면,

Slave 역할의 PC에서 my.ini 내의 server-id는 server-id=2와 같이 지정해 줘야한다.)

 

binlog_do_db = DB명          // 복제할 DB 선택(하나)
expire_logs_days = 7         // log보관 주기(일)
max_binlog_size = -M         // 로그파일최대크기 설정

 

기타 옵션

(1) 복제할 DB가 두개 이상일 경우

binlog_do_db = DB명

binlog_do_db = DB명

 

 

my.ini 예시 

# SERVER SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this
# file.
#
# server_type=3
log-bin=mysql-bin              
server-id=1
binlog_do_db = DB명

 

3. MySQL 데몬 다시 시작

다시 시작해야 config 설정이 변경됨

다시 시작 방법: (Windows 검색) 서비스 -> mysql 다시 시작

 

 

4. Master 정보 보기

mysql> SHOW MASTER STATUS;

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

| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000001 |       98   |                 |                     |

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

mysql-bin.000001이 slave가 읽을 파일이름 position이 로그 기록 시작 위치

(만약 master status가 나타나지 않는다면 1.config확인 2.관리자권한으로 실행) 

 

 

# Slave Configuration

1. Configuration 설정

my.ini위치: 숨김파일보기 설정 후 C:\ProgramData\MySQL\MySQL Server x.x

 

[mysqld]

server-id=2  (1~2^32사이 숫자, master와 slave의 server-id를 다르게 입력해줘야 한다.)

(예를 들어, Master 역할을 하는 PC에서 my.ini 의 server-id를 server-id=1와 같이 지정해줬다면,

Slave 역할의 PC에서 my.ini 내의 server-id는 server-id=2와 같이 지정해 줘야한다.)

 

replicate-do-db ='master쪽에서 복제 할 DB명' // DB 전체 복제 시

replicate-do-table =' master쪽에서 복제 할 DB명.Table명' // 해당 DB의 Table 복제 시

 

기타 옵션

(1) 복제할 DB 선택(하나)

replicate-do-db = 'DB명'

(2) 복제할 DB 선택(두개 이상)

replicate-do-db = 'DB명'

replicate-do-db = 'DB명'

(3) 복제할 테이블 선택(하나)

replicate-do-table ='DB명.Table명'

(4) 복제할 테이블을 불러올 때(두개 이상)

replicate-do-table ='DB명.Table명'

replicate-do-table ='DB명.Table명'

 

 

[ my.ini 예시 ]

# SERVER SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this
# file.
#
# server_type=3
[mysqld]
server-id=2
replicate-do-db =' DB명.Table명'
##################################################
########## Slave - Option 설명 ###################
## 복제할 DB 선택(하나)
# replicate-do-db = 'DB명'
 
## 복제할 DB 선택(두개 이상)
# replicate-do-db = 'DB명'
# replicate-do-db = 'DB명'
 
# 복제할 테이블 선택(하나)
# replicate-do-table ='DB명.Table명'
 
## 복제할 테이블을 불러올 때(두개 이상)
# replicate-do-table ='DB명.Table명'
# replicate-do-table ='DB명.Table명'
##################################################

 

2. database dump

Master가 로그를 기록하기 이전 파일들은 dump파일로 복제해야한다.

 

3. CHANGE MASTER TO :: Slave PC에서의 Connect 설정

mysql> CHANGE MASTER TO
MASTER_HOST='Master server host name or Master server IP',     // Master PC IP
MASTER_PORT=----                                               // Master Port 번호
MASTER_USER='replication user',                                // Master에서 생성한 ID
MASTER_PASSWORD='replication password',                        // Master에서 생성한 P/W
MASTER_LOG_FILE='Log File name',                               // Master에서 얻은 파일이름
MASTER_LOG_POS=position,                                       // Master에서 얻은 position

 

예시)

CHANGE MASTER TO
MASTER_HOST='187.1.30.113',
MASTER_USER='test',
MASTER_PASSWORD='test',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=120;

  

4. slave 명령어

-- 상태확인 구문 

mysql> show slave status;
mysql> show master status;

 

-- slave 구동

mysql> start slave;
mysql> stop slave;

 

-- slave server id 설정 부분

set global server_id=2;

 

-- select 구문

select * from num;
select * from sunghan;

-- insert 구문

insert into sunghan values('test1');

 

# 연결 유무 확인 방법

- slave

Mysql> show slave status

Slave_io_running: yes

Slave_sql_running:yes

slave_io_runnning이 connecting으로 나올 경우,  master의 방화벽 풀기

slave_io_runnning이 no로 나올 경우, config server_id확인. mysql에서 변경하는 방법 : set global server_id=2

 

 

# 기타설정

(master)

- master에 position을 구하기 전 추가적인 데이터가 들어오지 않도록 lock걸기

(완전히 동기화 하려면 데이터가 안 들어 올 때 lock을 걸고..position을 구해야 할 듯 합니다.)

mysql> FLUSH TABLES WITH READ LOCK; //락걸기

mysql> UNLOCK TABLES;// 락풀기

 

 

# 동일 버전의 mysql사용 권장

- SQL 문서 참고

Note: 트랜젝션을 사용하는 InnoDB을 이용한 리플리케이션 설정에서 내구성과 일관성을 최대로 확보하기 위해서는, 여러분이 사용하는 마스터 서버의 my.cnf 파일에서 innodb_flush_log_at_trx_commit=1, sync_binlog=1, innodb_safe_binlog (MySQL 5.0.3 이전 버전의 경우)를 사용해야 한다. (innodb_safe_binlog는 5.0.3 이후에는 필요가 없다.)

 

 

 

- 기타 Replication Option 참고 URL

http://www.mysqlkorea.com/sub.html?mcode=manual&scode=01&m_no=21430&cat1=6&cat2=205&cat3=0&lang=k

문서 파일) MySQL Replication방법 정리.pdf

made by 누리텔레콤 해외 AMI P/F팀 인병규, 누리텔레콤 해외 AMI P/F팀 임승한