노땅의 IT 세계

MySQL Replication을 이용하여 DBMS 단방향 이중화하기

웹서버 부하로 인해 L4를 이용하여 로드밸런싱으로 웹서버의 부하를 해결하였지만, DB 서버의 부하로 인하여 사이트가 느리게 열리는 현상이 발생하게 되었습니다

DB 서버를 이중화하는 방법은 없을까 하여 찾아보니 MySQL의 리플리케이션이라는 기능이 있더군요 이 기능을 이용하면 DB를 이중화 할 수 있는다는 것을 알게 되었습니다

이번 포스팅에서는 MySQL의 리플리케이션은 무엇이고, 리플리케이션을 이용한 DB를 이중화하는 방법을 알아보도록 하겠습니다.

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

Master DBMS 역할 : 

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

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

Slave DBMS 역할 : 

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

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

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의 부하를 분산하는 용도로 사용할 수 있게 됩니다

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

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

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

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

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

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

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

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

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

[Master 서버 DB, 계정정보]

IP : 192.168.65.148(Master), 192.168.65.149(Slave)

DadaBases : repl_db

ID : user1

PW : test123

[Replication 계정 정보]

IP : 192.168.65.148 - (Master)

ID : repl_user

PW : test456

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

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

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

1) DB 생성

2) 계정생성

3) 권한부여

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

3. MySQL 설정 - my.cnf

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

4. MySQL 재시작

5. Master 서버 정보 확인

* File : MySQL 로그파일

* Position : 로그 파일내 읽을 위치

* Binlog_Do_DB : 바이너리(Binary)로그 파일(변경된 이벤트 정보가 쌓이는 파일)

* Binlog_Ignore_DB : 복제 제외 정보

Slave 서버설하기에 앞서 먼저 계정을 생성하겠습니다

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

1) DB 생성

2) 계정생성

3) 권한부여

이제 Slave 서버 설정을 하는 방법 2가지 방법이 있습니다

첫번째는 mysql에 들어가서 설정하는 방법과 mysql 설정파일(my.cnf)에서 설정하는 방법이 있으며, 먼저 mysql에서 설정하는 부분부터 알아보도록 하겠습니다

1. MySQL에 접속하여 설정

1) MySQL 설정 - my.cnf

Server-id : Master 서버의 server-id를 제외하고 1~(2^32)-1내의 숫자로 설정하시면 됩니다

replicate-do-db : 복제하고자 하는 데이터베이스를 의미하며 2개이상의 데이터베이스를 할경우 replicate-do-db를 추가하시면 됩니다

2) MySQL 복원

1) Master DBMS에서 복제할 데이터베이스를 dump하여 복원합니다

3) Master 서버로 연결하기 위한 설정

MASTER_HOST : Mster 서버 IP 입력

MASTER_USER : 리플리케이션 ID

MASTER_PASSWORD : 리플리케이션 PW

MASTER_LOG_FILE : MASTER STATUS 로그파일명

MASTER_LOG_POS : MASTER STATUS에서 position 값

4) MySQL 재시작

2. MySQL에 my.cnf에서 설정하기

1) MySQL 설정 - my.cnf

replicate-do-db : 복제하고자 하는 데이터베이스를 의미하며 2개이상의 데이터베이스를 할경우 replicate-do-db를 추가하시면 됩니다

master-host : Master 서버의 IP를 입력

master-user : Master 서버에 생성한 리플리케이션(Replication) ID 입력

master-password : Master 서버에 생성한 리플리케이션(Replication) PW 입력

master-port : MySQL에서 사용하는 포트 입력

Server-id : Master 서버의 server-id를 제외하고 1~(2^32)-1내의 숫자로 설정하시면 됩니다

2) MySQL 재시작

MySQL 리플리케이션(Replication)이 정상적으로 완료되었다면 이제 상태를 확인해야 되겠죠? 다음과 같이 확인하시면 됩니다.

1. Master 서버 상태보기

1) 쓰레드 상태보기

Master 서버에서 위 내용과 같이 명령어를 입력하면 Id:1 쓰레드의  Slave서버(192.168.65.149)의 repl_user계정으로 연결되어 있는 것을 확인하실수 있습니다

2. Slave 서버 상태보기

1) 쓰레드 상태보기

쓰레드1(ld: 1)에서는 Master 서버와 통신하기 위한 쓰레드이며, 스레드2(ld: 2)는 업데이트된 내용을 처리하기 위한 SQL 쓰레드 입니다 이러한 2개의 쓰레드에서는 오류가 발생하면 안된다고 합니다.

2) 쓰레드 주요인자 상태보기

Slave_IO_State : Master서버의 연결을 시도하고 Master서버로 부터 이벤트를 기다리며, 재연결하는지에 대해 알려줍니다

Master_Host : 연결된 Master서버 호스트 입니다

Master_User : Master서버 연결하는데 사용되는 사용자 입니다

Master_Port : Master서버 연결하는데 사용되는 포트 입니다

Connect_Retry : --master-connect-retry 옵션의 현재 값 입니다

Master_Log_File : I/O 쓰레드에서 현재 읽고 있는 바이너리 로그파일 이름 입니다

        Read_Master_Log_Pos : I/O 쓰레드에서 현재 Master 서버의 바이너리 로그에서 읽은 곳가지의 위치 입니다

             Relay_Log_File : SQL 쓰레드에서 현재 relay 로그파일 이름 입니다

              Relay_Log_Pos : SQL 쓰레드에 의해 Relay 로그에서 읽고 실행한 곳까지의 위치 입니다

      Relay_Master_Log_File : SQL 스레드에 의해 실행된 최근 Master서버의 바이너리 로그 파일의 이름입니다

           Slave_IO_Running : I/O 쓰레드가 시작되어 Master서버의 성공적으로 연결되어있는지 여부 여부 입니다

          Slave_SQL_Running : SQL 쓰레드가 시작되었는지의 여부 입니다

            Replicate_Do_DB : Master서버에서 업데이트된 데이터를 반영될 DB 입니다

        Replicate_Ignore_DB : 생략.

         Replicate_Do_Table : 생략.

     Replicate_Ignore_Table : 생략.

    Replicate_Wild_Do_Table : 생략.

Replicate_Wild_Ignore_Table : 생략.

                 Last_Errno : 가장 최근에 사용된 쿼리의 에러메시지의 번호로 리턴됩니다

                 Last_Error : 가장 최근에 사용된 쿼리의 에러메시지의 번호로 리턴됩니다

               Skip_Counter : 생략.

        Exec_Master_Log_Pos : Master서버의 바이너리 로그의 Relay_Master_Log_File로 부터 SQL쓰레드의 의해 마지막 이벤트의 위치 입니다

            Relay_Log_Space : 존재하는 모든 Relay 로구우ㅏ 전체 사이즈 입니다

            Until_Condition : 생략.

             Until_Log_File : 생략.

              Until_Log_Pos : 생략.

         Master_SSL_Allowed : Master서버에 연결하기 위해 Slave에 의해 사용된 SSL 파라미터 입니다

         Master_SSL_CA_File : Master서버에 연결하기 위해 Slave에 의해 사용된 SSL 파라미터 입니다

         Master_SSL_CA_Path : Master서버에 연결하기 위해 Slave에 의해 사용된 SSL 파라미터 입니다

            Master_SSL_Cert : Master서버에 연결하기 위해 Slave에 의해 사용된 SSL 파라미터 입니다

          Master_SSL_Cipher : Master서버에 연결하기 위해 Slave에 의해 사용된 SSL 파라미터 입니다

             Master_SSL_Key : Master서버에 연결하기 위해 Slave에 의해 사용된 SSL 파라미터 입니다

      Seconds_Behind_Master : Master서버에서 실행된 이벤트의 타임스탬프 이후 경과된 시간(초 단위)의 수 입니다

간혹 위와 같이 에러가 발생하면서 Slave 서버가 작동이 잘안되는 경우가 있습니다

위와 같은 경우는 Slave 서버에 에러가 발생하면 에러가 발생하였던 시점으로 부터Master 서버로 부터 갱신된 쿼리를 실행하지 않게되며, 이경우 에러를 넘겨야 다음 쿼리를 실행하게 됩니다

위와 같은 옵션을 잠시 설정하여 반영하시기 바랍니다. my.cnf에서 설정을 반영하신 후에는 MySQL을 재시작하시기 바랍니다

출처: <http://server-talk.tistory.com/240>

로그 서버를 나누면서 DB를 복제해야할 일이 생김

[테스트 환경]

OS          - CentOS Linux release 7.3.1611 (Core),  64bit

Master     - mariadb-server-5.5.56-2.el7.x86_64   

Slave       - mariadb-server-5.5.56-2.el7.x86_64 

1. Master 설정

우선 MariaDB 환경설정 파일을 수정한다.

# vim /etc/my.cnf

※ 주의, [mysqld] 밑 부분에 추가해야 함, 환경 설정파일 맨 밑부분에 추가해 보면 Master 기능이 제대로 작동 안함

DB에 접속해서 Replication 사용자를 추가한다.

# mysql -u root -p

...

MariaDB [(none)]> CREATE USER 'repl'@'%' IDENTIFIED BY 'password';

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repl IDENTIFILED BY 'password';

MariaDB를 재시작 한 후 복제할 DB의 데이터를 덤프한다.

# systemctl restart mariadb

# mysqldump -u root -p testDB > testDB.sql

※ 이 과정은 Master에 있는 기존 데이터를 Slave에 옮겨야 하는 경우에만 적용한다.

Master의 상태를 확인한다.

# mysql -u root -p

...

MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;

MariaDB [(none)]> SHOW MASTER STATUS;

※ Slave의 경우 Position 값과 앞에 데이터를 덤프한 파일이 필요하다. Position 값은 변화하기 때문에 Slave에서 설정할 때 한 번 더 확인하는 작업이 필요하다.

LOCK의 경우 Replication 종료 또는 서비스 중이 아니라면 'MariaDB [(none)]> UNLOCK TABLES;' 를 통해서 LOCK을 풀면된다.

마지막으로 Slave 의 접근 권한을 허용해야 한다.

# firewall-cmd --permanent --zone=public --add-port=3306/tcp

# firewall-cmd --permanent --zone=public --add-port=3306/udp

# systemctl restart firewalld

2. Slave 설정

Slave 설정이 들어가기 전에 해야 하는 작업이 있다. 복제할 서버(Slave)에 Master 서버의 동일한 DB 또는 Table 구조를 만들어야 한다. 물론 복제할 부분만 있으면 되고 데이터까지 똑같을 필요는 없다.

마찬가지로 MariaDB 환경설정 파일을 수정한다.

# vim /etc/my.cnf

※ Master 와 마찬가지로 [mysqld] 아래 부분에 수정을 해야한다.

만약 하나 이상의 DB 를 복제하려면 'replicate-do-db={DBName}' 설정을 개수만큼 추가하면 된다.

server-id 의 경우 Master 와 겹쳐서는 안된다. 다수의 Slave 가 있다면 모두 각각의 server-id 를 가지고 있어야 한다.

테이블을 복사하려면 'replicate-do-table=db.table 을 하면 된다.

Master에서 덤프한 SQL 데이터를 복구한다.

# mysql -u root -p testDB < testDB.sql

Slave에서 Master로 접속하기 위한 정보를 추가한다.

# mysql -u root -p

MariaDB [(none)]> CHANGE MASTER TO

MASTER_HOST='115.xxx.xxx.xxx',

MASTER_USER='repl',

MASTER_PASSWORD='password',

MASTER_PORT=3306,

MASTER_LOG_FILE='master-bin.000001',

MASTER_LOG_POS=123256,

MASTER_CONNECT_RETRY=10;

MariaDB [(none)]> FLUSH PRIVILEGES;

MariaDB [(none)]> START SLAVE;

작동확인

MariaDB [(none)]> SHOW SLAVE STATUS\G;

에러가 없으면, 정상적으로 INSERT 되고 있는지 SELECT 문을 통해서 확인해보면 된다.

출처: https://knoow.tistory.com/138 [ICT Story]

MySQL 복제를 이용한 실시간 백업

원문: http://www.onlamp.com/pub/a/onlamp/2005/06/16/MySQLian.html

대규모로 운영중인 MySQL 데이터베이스의 문제는 서버를 중단시키지 않고 전체 백업(clean backup)을 하는 것이다. 백업은 시스템을 느리게 만들며, 백업을 수행중인 테이블과 관련된 데이터가 변경될 수 있기 때문에 데이터 일관성을 해칠 수도 있다. 서버를 다운시키면 일관된 데이터를 얻을 수 있지만 이는 사용자에게 서비스 중단을 의미한다. 반드시 필요하고 어쩔 수 없는 경우라면 서버를 다운시킬 수 있지만, 데이터를 백업하기 위해 매일 서버를 중단하는 것은 받아들이기 어려운 일이다. 날마다 서버를 중단하지 않고 안정적인 백업을 받는 방법은 MySQL에 복제(replication)를 설정하는 것이다.

역주1: 업무상 DB 전체 백업에 해당하는 것이 원어에서는 clean backup이다. 따라서 클린 백업 대신 전체 백업으로 옮겼다.

역주2: MySQL 복제 서비스는 MySQL 3.2 부터 지원한다.

복제는 MySQL 서버의 시스템 구성으로 설정할 수 있으며, 마스터 서버는 데이터를 저장하고, 클라이언트 요청을 처리하며, 슬레이브 서버는 마스터 서버 데이터의 완전한 복사본을 갖고 있으며, 마스터 서버에 변경이 일어나자마자 그에 해당하는 모든 SQL 문장을 복제한다. 로드 밸런싱을 위해 복제를 사용하는 경우도 있지만, 여기서는 데이터 백업을 위해 복제를 사용하는 것에만 관심을 둘 것이다. 슬레이브로 사용할 별도의 서버를 설정하고, 매일 전체 백업을 받기 위해 복제를 중단할 수 있다. 전체 백업이 끝난 다음에 복제를 재시작하면 슬레이브에서는 마스터와 연결되어 있지 않은 시간 동안 변경된 내용들을 마스터에 자동으로 요청한다. 복제는 매우 훌륭한 기능이며 MySQL에 있는 기능이다. 여러분이 할 일은 복제를 설정하는 것 뿐이다.

복제 수행 과정

복제를 설정하는 방법을 설명하기 전에 MySQL이 복제 서버를 어떻게 관리하는지부터 간단하게 살펴봐야 한다. MySQL 복제 서버 관리는 MySQL 버전에 따라 다르지만, 대부분의 시스템에서는 최신 버전을 사용하고 있기 때문에 여기서는 MySQL 4.0 이상의 버전에 대해서만 설명할 것이다.

복제를 사용중일 때 마스터 서버에서 SQL 문장이 실행되면 MySQL은 바이너리 로그(bin.log)에 이를 로그 식별 번호와 같이 기록한다. 그러면 슬레이브 서버는 IO 스레드를 사용해서 정기적으로 변경사항을 추적하기 위해 마스터 서버의 바이너리 로그 파일을 읽어들인다.

변경사항이 있으면 릴레이 로그(relay.log)에 문장을 복사하고, 슬레이브 서버에 마스터 파일(master.info)에 새 식별 번호를 기록한다. 슬레이브 서버는 같은 IO 스레드를 사용해서 마스터 서버의 바이너리 로그를 확인한다. 릴레이 로그에 변경된 내용이 있으면 슬레이브 서버는 SQL 스레드를 사용해서 릴레이 로그에 새 SQL 문장을 기록한다. 슬레이브 서버는 안전 장치로 SQL 스레드를 사용해서 슬레이브 서버의 데이터와 마스터 서버의 데이터가 일치하는지 확인하기 위해 마스터 서버에 질의한다. 비교 결과가 일치하지 않는다면 복제는 중단되고 슬레이브 서버의 에러 로그(error.log)에 에러 메시지를 기록한다. 비교 질의 수행 결과가 일치한다면 슬레이브 서버의 릴레리 로그 파일(relay-log.info) 파일에 새 로그 식별 번호를 기록하고, 마스터 서버의 릴레이 로그 파일의 변경을 모니터링한다.복제 과정은 복잡해 보이지만, 모든 과정은 빠르게 수행되며 마스터 서버 자원을 많이 소비하지 않으면서 안정적인 복제를 보장한다. 또한, 복제 서비스는 마스터 서버와 슬레이브 서버의 my.cnf 설정 파일에 옵션을 몇 줄 추가하는 것으로 설정할 수 있으며, 설정과정도 쉽다. 새로운 서버를 설치하는 경우에도 단순히 마스터 서버의 데이터베이스를 슬레이브 서버에 복사하고, 슬레이브 서버에서 복제를 시작하면 된다.

복제 사용자

복제를 설정하기 위해서는 몇 가지 간단한 절차를 수행하면 된다. 첫번째는 복제 용도로만 사용할 사용자 계정을 설정하는 것이다. 보안상 기존 계정은 사용하지 않는 것이 가장 좋다. 사용자 계정을 설정하기 위해 마스터 서버에 다음 명령을 수행한다. 다음 명령은 root나 GRANT OPTION 권한을 가진 사용자 계정으로 로그인해서 수행해야한다.

GRANT REPLICATION SLAVE, REPLICATION CLIENT

      ON *.*

      TO "replicant"@"slave_host"

      IDENTIFIED BY "my_pwd";

SQL 문장에서 사용자 계정 replicant는 복제에 필요한 권한만 설정되었다. 사용자 이름은 어떤 것이든 사용할 수 있다. "replicant" 대신에 호스트 이름이나 IP 주소를 사용할 수도 있다. 슬레이브 서버에서도 위와 같은 질의를 수행시키면 된다. "slave_host"는 마스터 서버의 호스트 이름이나 IP 주소로 변경하면 된다. 이와 같이 설정하면, 마스터 서버에 장애가 발생해서 잠시 사용할 수 없는 경우 사용자를 DNS 설정이나 다른 방법을 사용해서 슬레이브 서버를 이용하게 할 수 있다. 마스터 서버가 복원된 이후에는 슬레이브 서버에 변경된 데이터를 마스터 서버에 반영하기 위해 복제를 사용할 수 있다. 공교롭게도 이전 버전의 MySQL에서 4.0 버전으로 업그레이드한 경우에 mysql 데이터베이스는 업그레이드되지 않기 때문에 위의 GRANT 문장이 실행되지 않는다. 이전 버전에는 복제와 관련된 권한이 없기 때문이다. 이 문제를 해결하기 위해서는 MySQL 문서에서 Grants 테이블 업그레이드하기를 참고하기 바란다.

서버 설정하기

마스터와 슬레이브에 복제 사용자를 모두 설정했으면, 이번에는 두 서버의 MySQL 설정 파일을 수정해야 한다. 운영중인 운영체제의 종류에 따라 설정 파일은 my.cnf 또는 my.ini일 것이다. Unix 계열 운영체제에서 설정 파일은 /etc 디렉터리에 있으며, 윈도우 시스템에서는 c:\ 또는 c:\windows에 있다. 텍스트 편집기를 사용해서 설정 파일에 [mysqld] 그룹 아래에 다음을 추가한다.

server-id = 1

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

서버 식별 번호는 마스터 서버를 식별하기 위한 임의의 숫자다. 대부분의 경우에 어떤 숫자든 사용할 수 있다. 슬레이브 서버에 다른 식별 번호만 부여하면 된다. 두번째 줄은 MySQL에서 바이너리 로그를 수행할 경로와 파일명을 지정하는 것이다. 실제로 사용할 경로와 파일 이름은 원하는 대로 지정할 수 있다. 설정 파일의 디렉터리고 실제로 있는지 확인하고 mysql 사용자가 소유자이거나 또는 디렉터리에 쓰기 권한이 있기만 하면 된다. 또한, 여기서는 파일 이름의 접미어로 ".log"를 사용했지만 서버가 재시작하거나 로그 정보를 다시 시작할 때 ".000001"과 같은 일련번호로 대체된다.

슬레이브 서버의 설정 파일에도 다음을 추가해야 한다. 마스터 서버 연결을 위한 정보와 로그 파일 옵션을 추가한다.

server-id = 2

master-host = mastersite.com

master-port = 3306

master-user = replicant

master-password = my_pwd

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

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

log-error = /var/log/mysql/error.log

relay-log = /var/log/mysql/relay.log

relay-log-info-file = /var/log/mysql/relay-log.info

relay-log-index = /var/log/mysql/relay-log.index

꽤 많은 내용을 추가하는 것 같지만 실제로 들여다보면 어렵지 않게 이해할 수 있을 것이다. 첫번째 줄은 슬레이브 서버의 식별 번호이며, 슬레이브 서버를 여러 대 설정할 경우에는 다른 번호를 부여하면 된다. 그러나 단순히 데이터를 백업하는 것을 원하는 경우에는 슬레이브 서버를 한 대이상 설정할 필요는 없을 것이다. 그 다음 섹션은 마스터 서버의 호스트 이름, IP 주소와 같은 마스터 서버 정보에 대한 것이다. 마스터 포트는 MySQL 기본 설정인 3306 포트를 사용하지만 수행성능이나 보안상의 이유로 다른 포트 번호를 사용할 수도 있다. 그 다음은 마스터 서버에 로그인하기 위한 사용자 이름과 비밀번호다.

다음 두 섹션은 로그를 설정하기 위한 것이다. 처음 섹션은 마스터 서버에서 했던 것처럼 슬레이브 서버에서 바이너리 로그를 기록하기 위한 것이다. 이 설정은 앞에서 얘기한 것처럼 필요시 마스터 서버와 슬레이브 서버간에 역할을 서로 바꿀 수 있게 하기 위한 것이다. 바이너리 로그 인덱스 파일(log-bin.index)는 현재 사용하는 바이너리 로그 파일 이름을 기록하기 위한 것이다. 서버가 재시작되거나 로그들을 정리한 경우에 현재 로그 파일이 변경되며, 변경된 로그 파일 이름이 log-bin.index 파일에 기록된다. log-error 옵션은 에러 로그를 기록한다. 복제와 관련된 문제가 모두 기록되기 때문에 반드시 이 설정을 해야한다. 마지막 섹션은 릴레이 로그와 관련된 파일들을 설정한다. 릴레이 로그는 성능을 위해 마스터 서버 바이너리 로그의 각 항목을 복사한다. relay-log-info-file 옵션은 마스터 서버의 바이너리 로그에 대한 슬레이브 서버의 로그 파일 위치를 설정한다. 릴레이 로그 인덱스 파일은 복제를 위해 현재 사용중인 릴레이 로그 파일 이름을 관리한다.

데이터베이스 복사와 복제 시작하기

새 마스터 서버에 데이터가 없다면 슬레이브 서버를 재시작하면 된다. 그러나, 데이터가 있는 운영중인 서버에 복제를 설정했다면 복제를 위한 데이터베이스 초기 백업을 수행하는 것과 이 백업을 슬레이브 서버에 복사해야 한다. 데이터베이스를 백업하는 방법은 다양하다. 예를들어, mysqldump를 사용해서 운영중인 서버를 백업할 수 있다. 그러나, 여기에는 운영중인 서버의 데이터 일관성 문제가 남는다. 복제를 설정한 후에는 백업을 받기 위해 서버를 중단시키지 않아도 된다는 사실을 생각해보자. 즉, 일관성을 유지하면서 전체 백업을 받기 위해 사용자가 연결하지 못하게 하는 것도 생각해 볼 수 있다. root만 접근할 수 있는 마스터 서버를 만들기 위해 max_connections 변수를 다음과 같이 초기화할 수 있다.

SHOW VARIABLES LIKE "max_connections";

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

| Variable_name   | Value |

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

| max_connections | 100   |

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

SET GLOBAL max_connections = 0;

첫번째 SQL 문장은 필요없지만, 백업이 완료된 후에 max_connections 변수 값을 원래대로 복원하기 위해 초기 값을 알 필요는 있을 것이다. max_connections 값을 0으로 하는 것은 어떤 연결도 허용하지 않지만, 실제로 1개의 연결은 root 사용자를 위해 남겨져 있다. 물론, 이것은 새로운 연결만을 받아들이지 않는다. 따라서 현재 실행중인 연결을 보기 위해서는 SHOW PROCESSLIST;를 입력한다. 수행중인 프로세스를 종료하기 위해 KILL 문장을 사용할 수 있다.

서버에 독점적으로 액세스할 수 있으면 mysqldump는 매우 빠르게 수행된다. 마스터 서버의 명령줄에서 다음 명령을 입력한다.

mysqldump --user=root --password=my_pwd \

         --extended-insert --all-databases \

         --master-data   > /tmp/backup.sql  

위 문장은 모든 데이터베이스와 테이블을 생성하고, 데이터를 생성하는 SQL 문장으로 구성된 텍스트 파일을 생성한다. --extended-insert 옵션은 한번에 여러 줄을 넣을 수 있는 INSERT 문장을 만들어 주기 때문에 결과적으로 보다 빠르게 백업을 수행할 수 있으며, 최소한의 다운 타임을 갖는데 도움이 된다. --master-data 옵션은 덤프하는 동안 데이터가 변경되지 못하게 모든 테이블을 잠그지만, 사용자가 테이블을 읽어들이는 것은 허용한다. 독점적인 액세스 환경인 경우 이 기능은 필요하지 않다. 그러나, 이 옵션은 덤프 파일의 마지막에 다음과 같은 내용을 추가해준다.

--

-- Position to start replication from

--

CHANGE MASTER TO MASTER_LOG_FILE="bin.000846" ;

CHANGE MASTER TO MASTER_LOG_POS=427 ;

슬레이브 서버에서 덤프 파일이 실행될 때, 위 내용은 테이블이 잠겨 있는 동안 마스터 서버의 바이너리 로그 파일 이름과 백업 시점에서의 로그 위치를 기록한다. 복제가 시작될 때 이 로그 파일에 액세스하게 되고, 해당 위치부터 시작해서 기록된 SQL 문장을 수행한다. 즉, 슬레이브 서버가 설정되는 동안 변경된 데이터가 누락되지 않게 해준다. 슬레이브 서버에서 데이터베시으와 데이터를 설정하기 위해 덤프 파일을 실행하기 위해 덤프 파일을 슬레이브 서버에 복사한다. MySQL이 실행중인지 확인하고 슬레이브 서버에서 다음과 같이 입력한다.

mysql --user=root --password=my_pwd < /tmp/backup.sql

위 명령은 덤프 파일에 있는 CREATE, INSERT 문을 포함한 모든 SQL 문장을 실행한다. 백업된 데이터베이스가 슬레이브 서버에 모두 올라온 다음에는 슬레이브에 root로 로그인해서 다음 SQL 문장을 실행한다.

START SLAVE;

이 문장을 실행하면, 슬레이브는 마스터에 연결하고, 백업 이후로 누락된 변경사항을 가져온다. 이 시점부터 마스터 서버의 바이너리 로그 파일을 지속적으로 검사하면서 항상 최신의 상태를 유지한다.

복제를 이용한 백업

복제가 실행중일 때, 데이터 백업을 하는 것은 쉬운 작업이다. 먼저, 슬레이브 서버에 root 또는 SUPER 권한이 있는 사용자로 로그인하여 다음 SQL 문을 입력해서 슬레이버 서버의 복제를 잠시 중단시킨다.

STOP SLAVE;

슬레이브 서버는 마스터 서버의 바이너리 로그에 남겨진 위치를 알고 있다. 따라서, 슬레이브 서버에서 복제된 데이터베이스를 백업하면 된다. 백업 유틸리티 등을 사용하여 백업을 하고, 백업이 모두 완료되면 다음 명령을 실행해서 복제를 재시작하면 된다.

START SLAVE;

위 문장을 입력하면, 슬레이브 서버는 중단된 시점부터의 SQL 문장을 실행하고, 다시 최신의 상태를 유지하게 된다.

백업 자동화하기

복제와 백업이 올바르게 동작한다면 슬레이브 서버에서 복제를 중단하고, 데이터를 백업받고, 다시 슬레이브를 시작하는 간단한 쉘 스크립트를 작성할 수 있다.

#!/bin/sh

date = `date +%Y%m%d`

mysqladmin --user=root --password=my_pwd stop-slave

mysqldump --user=root --password=my_pwd --lock-all-tables \

         --all-databases > /backups/mysql/backup-${date}.sql

mysqladmin --user=root --password=my_pwd start-slave

이 예제에서는 슬레이브에서 복제를 중단하고 시작하기 위해 mysqladmin을 사용했다. 첫번째 줄에서 시스템 함수 date와 적절한 형식(예, 20050615)을 사용한 데이터를 사용해서 스크립트에서 mysqladmin에서 날마다 덤프 파일의 이름을 변경할 수 있게 했다. 물론, 덤파 파일의 경로와 이름을 원하는 대로 설정할 수 있다. date 함수와 서식지정 코드는 (")아 아니라 (`)을 사용한 것에 주의해야한다.

이것은 간단한 스크립트이고, 여러분은 보다 정교한 것을 작성하거나 오류 점검을 할 수 있는 것을 작성할 수도 있다. 공간을 절약하기 위해 덤프 파일을 압축하고, 압축한 파일을 테이프나 CD와 같은 매체에 저장할 수도 있다. 스크립트를 설정했으면 잘 동작하는지 테스트하기 바란다. 스크립트가 잘 동작한다면 crontab이나 서버에서 사용하는 스케줄링 유틸리티를 사용해서 스크립트를 추가하면 된다.

결론

복제는 MySQL에서 유용한 관리 기능이다. 복제는 데이터베이스의 주기적인 백업을 보장하는 훌륭한 방법이다. 복제에 대해 여기서 설명한 것보다 더 많은 옵션과 SQL 문장이 있으며, 이에 대해서는 내가 쓴 MySQL in a Nutshell에서 다루었다. 운영중인 대규모 시스템에서는 보다 강력한 데이터 보호를 위해 슬레이브 서버를 한 대 이상 설정할 수도 있다. 다중 슬레이브 서버 구성은 단일 슬레이브 서버 구성과 마찬가지로 설정과 개념은 동일하다. 극단적으로 많은 트랜잭션이 발생하는 데이터베이스에 대해서는 Emic과 같은 소프트웨어를 고려해볼 수 있다. Emic의 소프트웨어는 비싸지만, 백업과 로드 밸런싱을 위한 슬레이브 서버 관리를 위한 작업을 훌륭하게 수행한다.

백업 . 

mysqldump --compress --max_allowed_packet=500M -h myhost mydb | gzip > /tmp/mydb.sql.gz 

복구

gunzip < /tmp/mydb.sql.gz | mysql --quick --reconnect -u myuser -pmypass -h localhost -D mydb 

출처: <https://m.blog.naver.com/PostView.nhn?blogId=kikikl&logNo=130171785626&proxyReferer=https%3A%2F%2Fwww.google.co.kr%2F>

mysqldump를 이용한 데이터 이관시 속도 개선 방법

Written by 김형진  on 오전 5:56  in mysql  with 댓글 없음

1. mysql 에서 아래 옵션을 변경

myisam_sort_buffer_size = 1000M
myisam_max_sort_file_size = 12G

스토레지 엔진을 myisam으로 변경한 다음 insert 완료후 

alter database **** engine=innodb

2. innodb_flush_log_at_trx_commit 설정 값 변경

innodb_flush_log_at_trx_commit 값이 1인경우 insert 할때 로그파일을 기록한다. 이로인해 초당 몇천건 정도 insert할 내용을 몇십건 정도 insert한다.

show variables like 'innodb_flush_log_at_trx_commit';

DB dump파일을 import 할때는 로그가 요 없으므로 0 으로 설정하고 변경 값 확인후 insert한 다음 추후 설정값을 다시 복구한다.

set global innodb_flush_log_at_trx_commit=0;

3. table 별로 백업 sql 만들기

db_root_pw='비밀번호'

db_list=`echo "show databases;" | mysql -N -uroot -p"$db_root_pw"`
 for db in $db_list ;do
  table_list=`echo "show tables" | mysql -N -uroot -p"$db_root_pw" $db`
  for table in $table_list ; do
    mysqldump -uroot -p"$db_root_pw" $db $table > $db.${table}.sql
  done
 done

4. 옵션을 통해 시간 줄이기

--no-autocommit=1 : autocommit을 끄고 개의 테이블 입력 완료후 commit 수행, 오류 발생시 다시 처음부터 시작함

--single-transaction=1 : 작업 후에 변경 된 데이터의 내역을 다시 적용하지 않는다.

--extended-insert=1 :  INSERT 구문이 늘어나는 것을 막아준다.

- export 할때

mysqldump -hHOST_NAME -uMY_ID -pMY_PASSWORD --databases DB_NAME --tables TABLES_OF_DB_NAME --no-autocommit=1 --single-transaction=1 --extended-insert=1 > DUMP.sql

- import 할때

mysql -hHOST_NAME -uMY_ID -pMY_PASSWORD --database DB_NAME < DUMP.sql

5. txt 파일이용하기

- export 할때

mysqldump DATABASE_NAME > DUMP.txt

- import 할때

mysql -u username -p –database=DATABASE_NAME < DUMP.txt

6. large dump

mysql -h localhost -uroot -pPASSWORD
set global net_buffer_length=1000000;
set global max_allowed_packet=1000000000;
SET foreign_key_checks = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;
source /media/dbdump.sql

완료후

SET foreign_key_checks = 1;
SET UNIQUE_CHECKS = 1;
SET AUTOCOMMIT = 1;

7. 기타

my.cnf 위치찾기

mysql --verbose --help | grep -A 1 'Default options'

/etc/my.cnf 파일에서

innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0

설정 후 와 데이터 입력후 2번 실행

service mysql restart

키워드 : mysql big data fast import

출처: <https://lalwr.blogspot.com/2017/12/mysqldump.html>

그럴 때 필요한 것이 바로 mysqldump 다.

mysqldump --help 라고 치면 모든 OPTION들의 설명이 나와있겠지만,

그래도 아래 3가지 옵션은 알아 두는 것이 좋을 것 같다.

일단, 맨 위의 값들은 mysqldump가 사용하는 변수의 목록이다.

즉, 아무런 값도 치지 않고 (아! 물론  데이터 베이스는 선택해야 한다.)

명령어를 입력하면, 위의 값들을 바탕으로 결과가 만들어진다는 것이지.

그렇지만, 역시나 속도가 생명인 IT의 건아들은 

뭔가 오밀조밀하게 만져서 조금더 빠르게 덤프를 뜨고 싶을 것이다.

**** 속도가 관건인 분에게 아래의 OPTION들을 권하오~~

--no-autocommit=1 : 일단 autocommit을 끄고, 

                           1개의 테이블 입력이 완료될 때 까지

                           기다렸다가 commit을 수행 한다. 요거이 좋다!!!

                           대신 뻑나면, 다시 첨부터~~~ 우어!!!

--single-transaction=1 : 작업 후에 변경 된 데이터의 내역을 다시 적용하지 않는다.

                                 즉, 중간에 값이 바뀌질 않는다면 가능하다는 말씀.

--extended-insert=1 : 요거이 관건인데, 쓸데 없이 INSERT 구문이 늘어나는 것을 

                             막아준다. 가령 

                            ->  INSERT INTO `A` VALUES (1,10),(2,20);

                             이러면 될 것을 , 

                            -> INSERT INTO `A` VALUES (1,10); 

                            -> INSERT INTO `A` VALUES (2,20);

                             으로 늘려준다. 이러면 하루 온 종일 도는 거다 --;;

그럼 덤프 방법을 보자.

리눅스건 윈도우 커맨드건 간에 어차피 양식은 같다.

(아래의 대문자는 당신이 넣어야 할 내용들이다.)

@@@@ 덤프 할 때 @@@@

c:\>mysqldump -hHOST_NAME -uMY_ID -pMY_PASSWORD --databases DB_NAME --tables TABLES_OF_DB_NAME --no-autocommit=1 --single-transaction=1 --extended-insert=1 > c:\DUMP_FILE.sql

요러면, 아무것도 모르고 그냥 뽑아내는 방식보다,

(지가 무슨 고급 승용차인줄 아는지 거의가 풀 옵션이다 --;;)

사이즈가 1/4 정도로 확 줄고,

속도는 광속을 뽐내게 된다.

즉, 관건은, 

덤프의 시간이 아니라 RESTORE의 시간인데,

개인적인 체감 속도로 판단하자면, 

진짜 시간이 1/10 정도는 감소하는 것 같다눙... -0-/

@@@@ 복원 할 때 @@@@

c:\>mysql -hHOST_NAME -uMY_ID -pMY_PASSWORD --database DB_NAME < c:\DUMP_FILE.sql 

mysqldump 옵션

--no-autocommit=1
일단 autocommit을 끄고,
1개의 테이블 입력이 완료될 때 까지
기다렸다가 commit을 수행 한다. 요거이 좋다!!!
대신 뻑나면, 다시 첨부터~~~ 우어!!!

--single-transaction=1
작업 후에 변경 된 데이터의 내역을 다시 적용하지 않는다.
즉, 중간에 값이 바뀌질 않는다면 가능하다는 말씀.

--extended-insert=1
요거이 관건인데, 쓸데 없이 INSERT 구문이 늘어나는 것을
 막아준다. 가령
->  INSERT INTO `A` VALUES (1,10),(2,20);
 이러면 될 것을 ,
-> INSERT INTO `A` VALUES (1,10);
-> INSERT INTO `A` VALUES (2,20);
 으로 늘려준다. 이러면 하루 온 종일 도는 거다 --;;

이 3가지 옵션으로 백업을 한결과.... 놀라움을 극치 못했습니다 연습용 10기가 용량이 육박하는

데이터베이스를 보통 백업을 뜰때 30~40분 정도가 소요됬습니다(서버가 켄츠할배와 같은시기에 나온 제온..)

근데............. 그런데!!!!!!!!!! 저 3가지 옵션을 준후에는 10분 안짝으로 백업이 되는것 입니다..

놀라움을 극치 못하고 그러면 원본 포스팅에서 복원 속도도 체감상 1/10로 줄어들었다는데.......

정말이지 진짜 체감상 1/10로 줄어든 기분입니다.....하~ 이렇게 좋은 옵션이 있을줄은 생각지도 못했네요

일단 대용량 백업을 자주 뜨시는 분들이 계시면 저 옵션도 추천해드리고 실시간 백업을 요하는 분들은

Xtrabackup 솔루션을 추천드립니다 ( 증분 백업이 가능합니다! )

출처: http://dongwonme.tistory.com/entry/MySQL-대용량-백업-복원-빨리하기 [Dongwon.me]

출처: <http://dongwonme.tistory.com/entry/MySQL-%EB%8C%80%EC%9A%A9%EB%9F%89-%EB%B0%B1%EC%97%85-%EB%B3%B5%EC%9B%90-%EB%B9%A8%EB%A6%AC%ED%95%98%EA%B8%B0>

출처: http://blackbull.tistory.com/8 [음머어's 까망별]

출처: <http://blackbull.tistory.com/8>

MySQL 5.6.x Replication(MySQL 5.6 이중화)

참조

MySQL 공식문서

http://dev.mysql.com/doc/refman/5.6/en/replication.html

참고 블로그들

원리부터 설명까지 : http://blog.hibrainapps.net/130

여기도 설명 굿 : http://gywn.net/2012/02/mysql-replication-2/

여기서 설명은 대부분 MySQL 공식문서에서 Setting하는 순서와 여러 블로그 내용을 참조

Step1. Create a Data Snapshot

Replication을 시작하기전에 dump를 이용해 임포트하자.

case1) Using mysqldump

mysqldump -u root -p 'password' --all-databases --master-data=2 > dbdump.sql

mysqldump의 실행결과는 그냥 출력되기에 리다이렉트를 사용해야 한다.

--all-databases

모든 database   

--master-data

Slave가 Replication 프로세스를 시작하기전에 필요로하는 상태인 CHANGE MASTER TO를 자동적으로 추가

--master-data=1

이건 잘 모르겠다. 구글링해봥

--master-data=2

이건 sql문으로 작성된다는데 구글링해봥

그냥 --master-data 설정해놓고 찜찜해서 CHANGE MASTER TO를 그냥 해버렸다

Slave로는 다양한 방법이 있겟지만 scp를 사용해서 전송했다.

또 Slave에서는 다음과 같이 적용시켰다.

case2) Using Raw Data Files

DB가 크다면 Raw Data File들을 복사하는 것이 mysqldump를 사용하는 것 보다 효율적이다.

Step2. Master Configuration

- Enable binary logging

바뀐 데이터를 Master에서 Slaves로 보낸데 있어서 기본이기에 반드시 해야만한다.

- Establish a unique server ID

Replication group의 각각의 Server들은 고유의 Server ID를 가지고 있어야한다.

Server ID값은 1부터 65535까지의 양수가 될 수 있다. 어떻게 부여할지는 전적으로 당신의 몫

- MySQL Server를 설치하고 my.cnf나 my.ini 파일을 수정해야한다.

mysqld 섹션에 다음과 같이 추가한다.(yum으로 깔았더니 /etc/my.cnf 라는 경로를 가졌다.)

[mysqld]

log-bin=mysql-bin

server-id=1

- Debian/Ubuntu 같은 OS는 ski-networking이 "bind-address to 127.0.0.1"로 표기

-- 이 주소에 Slave의 주소나 HostName을 넣어야한다.

아래와 같이 설정했다

참고사항

※ Server-id는 설정하지 않으면 기본값은 0이다.

※ 트랜잭션들과함께 InnoDB를 이용해 Replication을 설정하는데 있어서 가능한한 최대한의 지속성과 일관성을 위해

당신은 innodb_flush_log_at_trx_commit=1 과 sync_binlog=1을 my.cnf파일에 추가해야한다.

※ skip-networking 옵션은 당신의 Replication Master에서 사용할 수 없음을 보장한다.

만일 네트워킹이 중지된다면, 당신의 Slave는 Master와 통신할 수가 없고 Replication은 실패하게 된다.   

※ Master 또한 Slave라면 my.cnf나 my.ini에 "log-slave-updates"라는 구문을 추가해야한다.

※ Binary Log를 남기게 되는 순간부터 datadir에 가보면(/var/lib/mysql)에 보면 다음과 같이 Log들이 남는다.

Step3. Slave Configuration

- Establish a unique server ID.

server-id 값은 IP주소 같은거로 생각하면된다.

이 ID들은 Replication 파트너와의 통신에서 유일하게 각각의 server instance를 구분한다.

server-id 값을 생략하면 기본값은 0이되며, Master로의 연결을 거부하게 된다.

(Step1과 똑같은 파일입니다)

※ Slave들은 binary logging을 할필요가없지만 데이터 백업이나 충돌 회복 용으로도 사용이 가능하다.

물론 또다른 Slave들을 사용하는데에도 사용이 가능하다.

※ my.cnf에 "report-host=hostname"을 넣어주면 Master에서 "show slave hosts" 명령어가 작동한다.

Step4. Create a User for Replication

Slave들이 Master에 접근하기위해서 Master의 User계정이 필요하다. 

어떤 계정이든 상관없고 REPLICATION SLAVE 권한이 필요하다.

Slave마다 다른 계정을 줄 수도 있고 같은 계정을 줄 수도 있다.

어떤 계정을 따로 만들 필요없지만

master.info에 평문으로 User name과 password가 저장된다는 것을 알고 있어야 한다.

그러므로 Replication 작업만을 위한 계정을 따로 생성할 필요가 있다.

mysql> CREATE USER 'repl'@'IP주소 or 호스트이름' IDENTIFIED BY 'slavepass';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';

Step5. Obtaining the Replication Master Binary Log Coordinates

Replication하기전에 Master의 Binary Log File의 이름과 Position(아마 Coordinates가 이건듯)을 알아야한다.

이 정보들은 나중에 Slave들이 Replication하기 전에 CHANGE MASTER TO작업에서 필요하다.

Master가 남긴 수 많은 Binary Log File들 중에 어떤 File을 어디서 부터 읽을 것이냐? 이런걸 표현하는 것 같다.

MySQL 문서를 보면 이 작업하기전에 반드시 Master에서 하고있는 모든 실행 상태를 정지하라고 한다. 내 생각이지만 계속 작업을 진행중이라면 Binary Log File이 계속 수정될 것이고 Position(Coordinates) 또한 그럴 것이기 때문이다.

mysql> SHOW MASTER STATUS;

File명과 Position을 기록하라!

아래 부터는 MySQL 문서를 대강 번역했다.

-------------------------------------------------------------------------------------------------------------------------------

Replication 작업을 시작하기전에 Slave들과 동기화하길 원하는 Master의 Data들이 있다면, 반드시 Master에서의 모든 실행 상태를 정지하고

다시 실행하기 전까지 Master의 현재 Binary Log 좌표(Position)를 얻어라.

만일 실행상태를 멈출수 없다면, 데이터 dump와 당신이 사용할 마스터의 상황 정보는 match되지 않을 것이고(아다리가 안맞는다는말인듯)

Slave에서 불일치하고 퇴폐된 데이터베이스를 가지게될 것이다.

1st) Master에서 Command-line client로 접속함으로써 세션을 시작하고,

모든 테이블들을 flush하고 "FLUSH TABLES WITH READ LOCK"의 실행으로써 Write Statements들을 Block한다.

※ 만약 client로 세션을 끊으면 Lock을 풀리게 된다.

2nd) Master의 또 다른 세션으로 "SHOW MASTER STATUS"을 통해 현재 binary log파일의 이름과 위치를 확인한다.

나중에 Slave를 세팅할때 필요한다음 기록해두어야한다.

그 정보들은 Slave가 Master로부터 새로운 update의 처리를 시작하기 위해 Replication 좌표를 표현한다.

만일 Master가 binary logging enabled없이(Step2.없이) 이때까지 실행되어왔다면,

로그 파일 이름과 포지션 값들은

SHOW MASTER STATUS"과

mysqldump --master-data에서 empty값일 것이다.

(아까 --master-data가 CHANGE TO MASTER 구문을 자동으로 추가한다고 했는데

CHANGE TO MASTER 구문에서는 Binary Log File이름과 Position을 필요로한다.

자동으로 추가된 File 이름과 Position이 빈 값이라는 표현같다.)

이런 경우에는, Slave의 로그파일과 포지션들을 지정할때 당신이 필요로 할 값들은 empty string('') 값이다.

-------------------------------------------------------------------------------------------------------------------------------

Step6. Start Replication!

모든 준비가 끝났다. Replication을 시작하기전에 마지막 세팅을 하면 된다.

※ 위 에서 mysqldump할때 --master-data 자동으로 추가한다는게 CHANGE MASTER TO 였다.

1) 설정을 해보자

※ 빡 침 주 의 ! ! P u t t y 로 작업 요 망 (숫 자 에 는 작 은 따 옴 표 안 붙 음 ! )

mysql> CHANGE MASTER TO

MASTER_HOST = ' IP주소 ',

MASTER_USER = ' MASTER에 접속할 DB 계정 ',

MASTER_PASSWORD = ' MASTER에 접속할 DB계정의 패스워드',

MASTER_PORT = ' MASTER가 있는 DB의 리스터 포트(MySQL은 디폴트 포드가 3306이다) ',

MASTER_LOG_FILE = ' 아까 기록해두었던 Binary Log File의 이름이다. ',

MASTER_LOG_POS = 아까 기록해두었던 Position값이다. ,

MASTER_CONNECT_RETRY = MASTER로의 접속을 하는 주기이다. 양의 정수로 단위는 초(Second)이다.

;

2) SLAVE 시작

mysql > START SLAVE;

3) 상태확인해보자

mysql > SHOW SLAVE STATUS;

※ 캡쳐가 상당히 더럽지만 핵심은 Waiting for master to send event이다.

Slave의 I/O Thread가 Master의 변경 Event를 감지하고 있는 것이다.

※ SHOW SLAVE STATUS \G; 이렇게하면 깔끔하게 나온다!! Shift + Page Down(OR UP) 으로 스크롤 업 다운

4) Master 쪽에서도 확인을 해보자.

mysql > show slaves host;

※) 만일 Slave에서 계속 접속을 시도하고 있다면?

Solution1) 방화벽을 확인(firewall-cmd로 3306포트를 풀어준다.)

중요한 점은 위에서 진행한 것처럼 해도 결국은 에러가 난다.. UUID가 겹친다. Dump로

했으면 에러가 안나는데 전체 복사를 해서 그런 것이다. Auto.cnf 파일 지우면 됩니다.

 Cloning MySQL 5.6 instances

August 23, 2013 by ronald

A tip for all those cloud users that like cloning database servers (as reported in my book Effective MySQL – Replication Techniques in Depth).

Starting with MySQL 5.6, MySQL instances have a UUID. Cloning servers to quickly create slaves will result in the following error message.

mysql> SHOW SLAVE STATUSG
...
  Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
...

The solution is simple. Clear our the file based configuration file (located in the MySQL datadir) and restart the MySQL instance.

$ rm -f /mysql/data/auto.cnf
$ service mysql restart

출처: <http://ronaldbradford.com/blog/cloning-mysql-5-6-instances-2013-08-23/>