Mysql Replication - Master, Slave 설정

Master Server

 

OS : CentOS release 5.4 (Final)

 

Mysql : mysql-5.1.33

 

 

 

Master my.cnf 수정

 

 

[root@master ~]# vi /etc/my.cnf

 

server-id       = 1

 

log-bin=mysql-bin

 

 

 

Master Mysql 계정 생성

 

 

master 서버에 Replicaion slave 권한을 가진 a1net 계정을 생성 % 부분은

 

 

slave 호스트를 등록해주셔도 됩니다.(현재는 모두접속 가능하게)

 

 

mysql> grant replication slave on *.* to 'a1net'@'%' identified by 'a1net';

 

 

 

LOCK 을 건 후 로그파일 확인

 

 

mysql> flush tables with read lock;

 

 

 

mysql> show master status;        // 굵은 글씨 부분 메모장등에 적어놓아야 됩니다.

 

 

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

 

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

 

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

 

| mysql-bin.000002 |      577 |              |                  |

 

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

 

 

 

전체 DB 백업

 

 

-       백업한 파일은 slave로 복사 scp, ftp등등..

 

 

-       데이터파일들을 옮겨가도 됩니다.

 

 

[root@master ~]# /usr/local/mysql/bin/mysqldump -u root -p --all-databases >all.sql

 

 

 

UNLOCK

 

mysql> unlock tables;

 

 

 

Mysql 재시작

 

 

[root@master ~]# /usr/local/mysql/bin/mysqladmin -u root -p shutdown

 

[root@master ~]# /usr/local/mysql/bin/mysqld_safe &

 

 

 

Slave Server

 

OS : CentOS release 5.4 (Final)

 

Mysql : mysql-5.1.33

 

 

 

Slave my.cnf 수정

 

 

[root@slave ~]# vi /etc/my.cnf

 

server-id       = 2

 

slave-skip-errors = all

 

 

 

DB복원

 

 

[root@slave ~]# /usr/local/mysql/bin/mysql -u root -p <all.sql

 

 

 

Master에 연결 설정

 

 

mysql> change master to

 

    -> master_host='192.168.2.153',         // master 서버 ip

 

 

    -> master_user='a1net',                // replication 권한 생성 계정

 

 

    -> master_port=3306,                 // master 서버 mysql 포트번호

 

 

    -> master_password='a1net',           // password

 

    -> master_log_file='mysql-bin.000002',  // master 서버 show master status; 에 file

 

 

    -> master_log_pos=577;               // master 서버 show master status; 에 Position

 

 

Query OK, 0 rows affected (0.19 sec)

 

 

 

Slave Start

 

mysql> start slave;

 

 

 

 

 

 

 

 

 

확인 (붉은글씨 부분 위주로 확인)

 

 

Master Server

 

mysql> show processlist\G

 

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

 

     Id: 1

 

   User: root

 

   Host: localhost

 

     db: NULL

 

Command: Query

 

   Time: 0

 

  State: NULL

 

   Info: show processlist

 

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

 

     Id: 2

 

   User: a1net

 

   Host: 192.168.2.154:51523

 

     db: NULL

 

Command: Binlog Dump

 

   Time: 377

 

  State: Has sent all binlog to slave; waiting for binlog to be updated

 

   Info: NULL

 

2 rows in set (0.00 sec)

 

 

 

mysql> show master status\G

 

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

 

            File: mysql-bin.000004

 

        Position: 106

 

 

    Binlog_Do_DB:

 

Binlog_Ignore_DB:

 

1 row in set (0.00 sec)

 

 

 

 

 

Slave Server

 

mysql> show processlist\G

 

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

 

     Id: 2

 

   User: root

 

   Host: localhost

 

     db: NULL

 

Command: Query

 

   Time: 0

 

  State: NULL

 

   Info: show processlist

 

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

 

     Id: 3

 

   User: system user

 

   Host:

 

     db: NULL

 

Command: Connect

 

   Time: 332

 

  State: Waiting for master to send event

 

   Info: NULL

 

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

 

     Id: 4

 

   User: system user

 

   Host:

 

     db: NULL

 

Command: Connect

 

   Time: 331

 

  State: Has read all relay log; waiting for the slave I/O thread to update it

 

   Info: NULL

 

3 rows in set (0.00 sec)

 

 

 

mysql> show slave status\G

 

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

 

               Slave_IO_State: Waiting for master to send event

 

                  Master_Host: 192.168.2.153

 

                  Master_User: a1net

 

                  Master_Port: 3306

 

                Connect_Retry: 60

 

              Master_Log_File: mysql-bin.000004

 

          Read_Master_Log_Pos: 106

 

 

               Relay_Log_File: slave-relay-bin.000004

 

                Relay_Log_Pos: 251

 

        Relay_Master_Log_File: mysql-bin.000004

 

             Slave_IO_Running: Yes  // 마스터 바이너리 로그를 읽기 위한 IO스레드가 구동중인지 표시

 

 

            Slave_SQL_Running: Yes  // 릴레이 로그에 있는 이벤트를 처리하기 위한 SQL 쓰레드가 구동 중에 있는지를 표시

 

 

              Replicate_Do_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: 106

 

              Relay_Log_Space: 551

 

              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)

 

 

 

기타

 

 

로그포지션 정보는 Slave Server아래 파일들에서 확인 가능하다.

 

 

[root@slave ~]# cat /usr/local/mysql/var/master.info

 

15

 

mysql-bin.000004

 

678

 

192.168.2.153

 

a1net

 

a1net

 

3306

 

…

 

[root@slave ~]# cat /usr/local/mysql/var/relay-log.info

 

./slave-relay-bin.000004

 

740

 

mysql-bin.000004

 

595

 

 

 

 

 

전체 DB를 Replication하지 않고 특정 DB만 원할 경우에는

 

 

my.cnf 파일에

 

 

master

 

binlog-do-db = 데이터베이스명

 

 

 

 

slave

 

replicate-do-db = 가져올 데이터베이스명

 

 

설정해주면 됩니다.

 

 

 

 

 

 

슬레이브에서 Replication중단

 

 

mysql> STOP SLAVE;

 

슬레이브에서 Replication RESET

 

 

mysql> reset slave;

 

슬레이브에서 Replication 시작

 

 

mysql> START SLAVE;

 

 

 

 

 

가능하면 Master 와 Slave Mysql 버전은 동일버전 사용을 권장

 

 

Slave 서버에 Master 서버 변경하기

 

 

 

 

Slave는 CHANGE MASTER TO 명령문을 사용해서 자신의 마스터를 변경 가능합니다.

 

 

 

 

 

 

 

 

 

 

 

 

참고 : http://www.mysqlkorea.co.kr/sub.html?mcode=develop&scode=01_1&lang=k&