MySQL Replication

MySQL Replication

By Joseph JangVieweron May 20, 2008 8:31 AM | PermalinkViewer | Comments (0)Viewer | TrackBacks (0)Viewer

MySQL ReplicationViewer은 MySQL이 기본적으로 지원하는 데이터 복제 방식입니다.

MySQL Replication은 read-write가 가능한 Master와 read-only인 Slave로 구성되는데, Master는 쿼리 로그에 해당하는 binary log를 남기고, Slave는 asynchronous하게 이를 가져가서 반영하는 역할을 합니다. 설정도 간단해, 1-3줄 정도의 설정만 해주면 됩니다. 간단한 동작 방식에 기초해서 Multi-slave, Multi-master, Replication chaining 등의 구성 등이 가능합니다.

MySQL Replication의 문제점들

Master와 Slave가 최소한의 정보만 공유하는 단순한 방식이 장점인 동시에 단점으로 작용합니다.

  1. Master-Slave pair 관리:서버들이 많아질 경우, Master와 Slave의 짝을 관리하는 것이 쉽지는 않습니다. Master와 Slave의 짝을 다른 어디선가 관리해주어야 합니다.
  2. 실패 상황에서의 복구:당연히 자동으로 복구한다든가 하는 기능을 MySQL이 제공하지는 않습니다. Master가 실패했을 경우, Slave를 Master를 바꾼다든가, Slave의 데이터를 Master로 복사해준다든가 하는 작업은 순전히 수작업이 됩니다. Slave가 실패했을 때도, 마찬가지 입니다. Slave의 일시적인 오류라면, Slave는 자신이 반영한 로그의 위치를 기억하고 있긴 하지만, 이에 따른 복구도 사람 손이 갑니다.
  3. binary log의 관리: Master는 Slave에 대해서 don’t care이므로, 언제 binary log를 삭제할 지는 주기 등을 사용합니다. 물론 삭제하는 command가 있지만, Slave들의 정보를 반영해서 자동으로 삭제하는 기능은 없습니다.
  4. asynchronicity: Slave는 Master와 같은 데이터를 가지고 있다고 보장할 수 없으므로, Slave가 Master의 쿼리 처리량을 따라가지 못하게 되면, consistency 문제가 발생할 소지가 있습니다. 따라서, 애플리케이션에서 이러한 점을 신경써주어야 합니다. 이러한 점들 때문에, MySQL Replication을 백업으로 생각하지 말라는 조언들이 등장합니다.

    1, 2, 3번의 경우에는 도구를 이용해서 극복할 수 있습니다. 1번, 2번에 대해서는 어느 정도 도구를 만들어 쓰고 있는데, 인터페이스를 웹 인터페이스로 개선하고 좀 더 자동화할 필요가 있습니다. MaakitViewer이라든가 MySQL Replication ManagerViewer 등을 참고해볼만 합니다.

    4번의 문제에 대해서는 MySQL Replication이 아닌 대안을 찾는 것이 옳을지도 모르겠습니다. 구글에서는 semi-sync 모드를 위한 패치를Viewer 내놓기도 했습니다.

    MySQL Replication의 대안들

    일반적으로 MySQL Replication을 얘기할 때는 다음과 같은 대안들을 함께 얘기합니다.

  • MySQL Cluster: MySQL이 직접 지원하는 것 중에는 MySQL ClusterViewer가 있지만, 아직은 메모리 기반 엔진인 NDB만 지원하기 때문에 메모리의 크기에 제약을 받습니다.
  • DRDB: 디바이스 수준에서 디스크를 분산하는 DRDB를 MySQL과 함께 사용합니다. MySQL Replication보다 failure에 안전한 것은 아니지만, sync가 어긋난 상태는 최소화Viewer됩니다. 하지만, failover node (i.e. slave)는 MySQL 쿼리를 처리할 수 없다는 크나큰 제약이 있습니다. 백업의 용도로는 적절하리라고 생각합니다.

    예전부터 관심을 두고 있던 SequoiaViewer와 같은 데이터베이스 클러스터링 솔루션도 한번 써보고 싶고, 최근에 화제가 되고 있는 MySQL ProxyViewer 와 같은 Proxy들(SQL RelayViewer, dpmViewer, Spock ProxyViewer)을 사용해서 Replication을 잘할 수 없을까 생각이 들기도 하는데, 당장은 시간이 없다는 변명을 하렵니다.

●?Who’s 라미아

원본 위치 <http://www.lamia.kr/entertainment/1673>

mysql replication에서 특정한 position으로 건너뛰고 싶을때
┗ DB

replication서비스를 진행하다가 보면

replication이 아래와 같은 이유등으로 오류가 날 경우가 있습니다.

이때의 데이터가 필요없는 부분이라면 그냥 건너뛰게 하면 되는데요

건너뛰게 하는 방법은 아래와 같습니다.

slave서버에서 멈춘 위치 확인(Exec_master_log_pos: 1033756136)

mysql> show slave status\G

master서버에서 멈춘 위치근처에 어떤 쿼리들이 있는지 확인

mysql> show binlog events in ‘MASTERSERVER-bin.012’ from 1033756136 limit 3\G

오류가 발생한 쿼리는 필요없으므로 다음 쿼리로 이동

mysql> slave stop;

mysql> change master to master_log_file=’MASTERSERVER-bin.012′, master_log_pos=1033756434;

mysql> slave start;

mysql> show slave status\G

———————상세한 내용————————————–

mysql> show slave status\G

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

Master_Host: 192.168.*.*

Master_User: userid

Master_Port: 1111

Connect_retry: 60

Master_Log_File: MASTERSERVER-bin.012

Read_Master_Log_Pos: 1049058337

Relay_Log_File: MEMOINFOS2-relay-bin.011

Relay_Log_Pos: 1033731265

Relay_Master_Log_File: MASTERSERVER-bin.012

Slave_IO_Running: No

Slave_SQL_Running: No

Replicate_do_db:

Replicate_ignore_db:

Last_errno: 1062

Last_error: Error ‘Duplicate entry ‘18462’ for key 1′ on query ‘INSERT INTO dbaaa.TBLEVENT_0818_RECEIVE (EVENTKEY, seq, USERID, SENDERseq, CREATEDATE, UPDATEDATE) VALUES (xxx, 111, ‘222@aaa.com’, ‘4337665’, NOW(), NOW())’. Default database: ‘INFODB’

Skip_counter: 0

Exec_master_log_pos: 1033756136

Relay_log_space: 1049033466

1 row in set (0.00 sec)

master에서 Exec_master_log_pos에 나타난 숫자 뒤에 어떤 이벤트 로그가 있는지 확인

mysql> show binlog events in ‘MASTERSERVER-bin.012’ from 1033756136 limit 3\G

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

Log_name: MASTERSERVER-bin.012

Pos: 1033756136

Event_type: Intvar

Server_id: 1

Orig_log_pos: 1033756136

Info: INSERT_ID=18462

*************************** 2. row ***************************

Log_name: MASTERSERVER-bin.012

Pos: 1033756164

Event_type: Query

Server_id: 1

Orig_log_pos: 1033756164

Info: use INFODB; INSERT INTO INFODB.TBLEVENT_0818_RECEIVE (EVENTKEY, seq, USERID, SENDERCMN, CREATEDATE, UPDATEDATE) VALUES (xxx, 111, ‘222@aaa.com’, ‘4337665’, NOW(), NOW())

*************************** 3. row ***************************

Log_name: MASTERSERVER-bin.012

Pos: 1033756434

Event_type: Query

Server_id: 1

Orig_log_pos: 1033756434

Info: use INFODB; UPDATE dbaaa.aaa SET LOGOUTDATE = NOW(), UPDATEDATE = NOW() WHERE seq = ‘1’

3 rows in set (0.00 sec)

slave stop;

change master to master_log_file=’MASTERSERVER-bin.012′, master_log_pos=1033756434;

slave start;

show slave status\G

출처 : Tong – 항상 열심히~님의 MySQL통

=========================================================================================

요즘은 MySQL Replication 기능을 이용해서 DB System을 구축하는 경우가 많다. 또한 MySQL DB를 사용하면서 백업은 보통 Replication Slave 단에 있는 백업 Server에서 별도로 받게 된다.

백업 서버에서 Dump로 받게 된 Data는 DML 쿼리로 된 text data이며, 이 데이터를 이용해서 신규 장비를 설치하던가, 아니면 기존 장비에 문제가 생겼을 시 복구를 하게 된다.


보통의 MySQL Replication 구성

그런데 복구를 하더라도 Replication이란 문제로 인해 Master DB와 싱크를 맞추는 문제가 발생하게 된다.

따라서 백업 서버에서 백업을 받게 되는 경우, Master DB의 binary log의 포지션을 알아야지만 된다.

이럴 경우에는 다음과 같은 절차를 따라서 Dump를 받으면 된다.

/usr/local/mysql/bin/mysqldump -uroot -p –master-data=2 > /data/…/backup.sql

이 명령어 중에서 “–master-data=2” 옵션을 주게 되면 덤프 시점의 binary log의 포지션 정보가 같이 Dump 되게 된다.

Dump된 파일을 에디터로 열어보면 다음과 같은 내용이 나온다.

CHANGE MASTER TO MASTER_LOG_FILE=’binary파일’, MASTER_LOG_POS=포지션번호;

위 말은 binary파일의 포지션 번호 시점에서 백입이 이루어 졌다는 의미이다.

물론 binary 파일과 포지션은 복구하기 위한 중요한 내용이 되겠지만, 위와 같은 구성에서 복구를 하기 위해서는 조금 애매 보호한 감이 있다.

가령, Backup DB에서 Dump받은 파일을 Slave DB를 복구하기 위해 사용한다던가, 아니면 신규 Slave 장비가 와서 새로운 시스템을 구축할 시에는 Backup DB의 binary 파일명과 포지션은 별로 소용이 없기 때문이다.

따라서 이럴 때에는 다음과 같은 절차로 Master DB의 binary 파일과 포지션을 알아낼 수 있다.

1. Backup DB에서 포지션이 속한 바이너리 로그를 sql 파일로 export 한다. 사용 툴은 mysqlbinlog이다.

/usr/local/mysql/bin/mysqlbinlog –start-datetime=”2008-12-15 02:00:00″–stop-datetime=’2008-12-15 02:30:00′ /data/mysql_data/binary파일 > binlog.sql

2. Master DB에서도 위 방법대로 바이너리 로그를 sql 파일로 export 한다.

/usr/local/mysql/bin/mysqlbinlog –start-datetime=”2008-12-15 02:00:00″–stop-datetime=’2008-12-15 02:30:00′ /data/mysql_data/binary파일 > binlog.sql

3. Backup DB에서 export한 sql 파일을 에디터로 열어서 확인해 본다.

# at 576856551

#060824 2:18:30 server id 1 log_pos 576856551 Intvar

SET INSERT_ID=136;

# at 576856579

#060824 2:18:30 server id 1 log_pos 576856579 Query thread_id=16587969 exec_time=16157 error_code=0

SET TIMESTAMP=1156353510;


# at 576856919

최초에 Backup DB에서 Dump받은 파일에 기록된 포지션 정보를 찾는다.

4. Master DB에서 export 한 sql 파일을 에디터로 열어서 동일한 포지션을 찾는다.

# at 576375776

#060824 2:18:30 server id 1 log_pos 576375776 Intvar

SET INSERT_ID=136;

# at 576375804

#060824 2:18:30 server id 1 log_pos 576375804 Query thread_id=16587969 exec_time=0 error_code=0

SET TIMESTAMP=1156353510;

Backup DB에서 해당 포지션에 기록된 TIMESTAMP값과 동일한 정보를 Master DB에서 export한 sql 파일에서 찾는다.

위의 경우에는 576375776이 포지션 번호가 된다.

5. 최초에 Dump 받은 Dump Data의 바이너리 파일과 포지션을 수정해 준다.

CHANGE MASTER TO MASTER_LOG_FILE=’마스터DB의 binary파일’, MASTER_LOG_POS=576375776;

위와 같이 되었다면, Dump 받은 파일은 언제라도 Master DB에 붙여서 replication을 동기화 시킬 수 있는 준비가 된 상태이다.

만일 새로운 장비를 들여와서 Dump Data로 복구를 했다면, 위 명령어를 내린 후 slave를 start 시키면, Master DB의 해당 포지션 부터 sync를 맞춰가기 시작한다.

Master DB의 포지션을 모두 다 따라갔다고 판단되면 Slave DB를 서비스에 투입시키면 끝이다.

원본 위치 <http://www.4te.co.kr/532>

mysql replication 설정, master-slave 설정하기/장애대응

원본 위치 <http://blog.1day1.org/454>

mysql 리플리케이션 설정은 어렵지 않군.

장애대응이 어려운것 같다.

# 기본 mysql replication 설정.(마스터-슬레이브)

1. mysql config 설정

master , slave 각각 config 파일에 replication 설정을 해준다.

우분투의 경우 /etc/mysql/conf.d 에 replication_slave.cnf (파일명은 임의로 )

/etc/mysql/my.cnf 의 마지막줄에 이렇게 되어 있어서 불러오게 된다

!includedir /etc/mysql/conf.d/

my.cnf 의 [mysqld] 탭에 직접추가해줘도 된다.

내용은 이런식이다. (슬레이브쪽)

[mysqld]

server-id = 2

master-host = master

master-user = replication

master-password = slave

replicate-do-db = master_db

replicate-do-db = other_db

마스터쪽은 다음과 같다.

[mysqld]

server-id = 1

log_bin = /var/log/mysql/mysql-bin.log

binlog_do_db = master_db

binlog_do_db = other_db

binlog_ignore_db = mysql

binlog_ignore_db = information_schema

설정만 보면 이해할 수 있을 것이다.

binlog_do_db 등을 각자의 서버설정에 맞게 바꾸어 주면 된다.

2. replication 접근 권한설정

replication 은 slave 쪽에서 master 의 자료(bin_log)를 참조해서 데이터를 가져가는 것이다.

그래서 슬레이브쪽에서 마스터에 접근할 수 있어야 한다.

그 명령은 다음과 같다.(마스터쪽에서 mysql 명령을 내려준다)

mysql> grant replication slave on *.* to ‘replication‘@192.168.0.1 identified by ‘slave‘;

진한부분이 conf 에서 설정한 master-user / master-host / master-password 에 해당한다.

IP 주소대신 /etc/hosts 에 설정한 호스트명을 적어줘도 된다. ( master-host = master 같은..)

3. 사용 및 장애대응

작동 중지/실행

start slave ;

stop slave ;

슬레이브 상태 보기

mysql> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: master

Master_User: replication

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000018

Read_Master_Log_Pos: 958292

Relay_Log_File: slave-relay-bin.000271

Relay_Log_Pos: 251

Relay_Master_Log_File: mysql-bin.000018

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: master_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: 958292

Relay_Log_Space: 556

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:

1 row in set (0.00 sec)

마스터의 상태를 보려면

mysql> show master status\G

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

File: mysql-bin.000018

Position: 958292

Binlog_Do_DB: master_db

Binlog_Ignore_DB: mysql

1 row in set (0.00 sec)

진하게 표시한 position 이 일치하는지 확인하면 된다.

slave 의 position 값이 일치하지 않는경우.

다음처럼 해준다.

mysql > stop slave;

mysql > reset slave;

mysql > start slave;

cron 등으로 주기적으로 확인해주도록 한다.

Mysql Master-Master Replication

원본 위치 <http://blog.diainfo.co.kr/textyle/8273>

Replication 을 사용 함에 있어서 Master-Slave 구조로 쓰는 것이 일반적일 것이다. 그런데 때에 따라 이런 구조를 사용 하지 못하는 경우가 있다. master에는 Insert,update,create,delete 등 db에 변경이 일어나는 Query 는 master만 사용하고 Slave 에서는 select 와 같은 db 에 변경이 없는 것만 사용 해야 한다. 그런데 현재 사용 해야 하는 웹사이트 등이 그렇지 못하다고 가정하자. 이럴 경우 master-slave 는 사용 하지 못한다. 물론 개발자가 해당 소스를 수정 해주면 간단하지만 사정상 할수가 없는 경우도 있다. 그럴 때 master-master 구조를 사용하면 해결이 된다.

다음은 db가 설치 되어있다는 가정하에 M-M 방식으로 동작하도록 설정하는 방법이다. DB는 2대로 구성하며 IP는 다음과 같다.

DB-1 : 192.168.11.11

DB-2 : 192.168.11.12

# my.cnf 파일을 편집기로 열어 다음과 같이 수정

log-bin

binlog-do-db=TESTDB // Replication 할 DB명

binlog-ignore-db=mysql // Replication 에서 제외할 DB명

# mysql 로 접속하여 slave 권한 부여

mysql> grant replication slave on *.* to replication@192.168.11.12 identified by ‘SLAVE_PASSWORD’;

# mysql restart

DB-1 시작후 DB-2 의 my.cnf에 replication 설정을 하고 시작을 하면 ” mysqld : unknown variable ‘master-host= ” 와 같은 오류가 난다. 이는 mysql 5.5 이상에서 아래 옵션들이 동작 하지 않는다.

–master-host

–master-user

–master-password

–master-port

그리하여 위 옵션은 사용 할수가 없다. 해결책으로 아래 옵션을 사용 한다. 해당 명령어를 실행하고 나면 datadir 에 master.info 가 생성되고 이후로 mysql server를 stop/start 하여도 유지 되니 걱정 하지 말자.

[DB-1]

mysql > SHOW MASTER STATUS;

+————————+—————–+———————+————————-+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+————————+—————–+———————+————————-+

| mysql-bin.000001 | 107 | TESTDB | mysql |

+————————+—————–+———————+————————-+

file 이름과 position 을 확인

[DB-2]

mysql> CHANGE MASTER TO MASTER_HOST=’192.168.11.11′, MASTER_USER=’replication’, MASTER_PASSWORD=’MASTER_PASSWORD’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=107;

mysql > slave start;

아래 명령어로 올바르게 동작 하는지 확인

mysql > show slave status \G

여기까지가 기본적인 Master-Slave 구조 이다.

Master-Master 구조 ( master1/slave2 – master2/slave1 ) 로 가기 위해서는 추가적인 설정을 더해 주면 된다.

[ DB-2 ]

DB-2 의 my.cnf 파일을 열고 다음을 추가 하도록 하자

log-bin

binlog-do-db=TESTDB

# mysql 접속 하여 권한 추가

mysql > grant replication slave on *.* to replication@192.168.11.11 identified by ‘SLAVE_PASSWORD’;

# mysql restart

mysql > show master status;

+————————+—————–+———————+————————-+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+————————+—————–+———————+————————-+

| mysql-bin.000006 | 107 | TESTDB | mysql |

+————————+—————–+———————+————————-+

file 이름과 position 확인

[ DB-1 ]

mysql > CHANGE MASTER TO MASTER_HOST=’192.168.11.12′, MASTER_USER=’replication’, MASTER_PASSWORD=’MASTER_PASSWORD’, MASTER_LOG_FILE=’mysql-bin.000006′, MASTER_LOG_POS=107;

mysql > slave start;

오류 없이 동작 하는지 확인.

mysql > show slave status \G

여기까지 하면 Master-Master 구성이 끝이난다. 설정을 하면서 꼭 Master-Slave 구성후에 show master status 와 show slae status 로 log_file 과 postion 이 일치하는지 확인하고 error 가 없는지도 확인하자.


http://blackbull.tistory.com/m/post/12

Comments

comments

답글 남기기

이메일은 공개되지 않습니다. 필수 입력창은 * 로 표시되어 있습니다.