티스토리 뷰

[mysql] mysql 리플리케이션(replication) 설정 방법

 

1. master 서버 작업

 1.1 /etc/mysql.cnf

$ vi /etc/mysql.cnf
…
[mysqld]
# Replication Master Server (default)

# binary logging is required for replication
log-bin=mysql-bin

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id           = 1

// binlog-do-db = 특정디비만

// replicate-ignore-db = 특정db제외

// replicate-wild-ignore-table = 특정 table 제외

 

1.2 mysql 재시작

$ /bin/sh /usr/local/mysql/bin/mysqld_safe &

 

1.3 slave에서 접속 할 계정 생성

$ mysql –u root –p

mysql> use mysql

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repuser'@10.0.10.101' IDENTIFIED BY '123456';

1.4 Slave의 설정을 위한 Master STATUS 정보

(File의 값과 Position의 값 추출)

mysql> FLUSH TABLES WITH READ LOCK;
1 row in set (0.00 sec)

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |   198032 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> UNLOCK TABLES;
1 row in set (0.00 sec)

 

2. slave 서버 작업

2.1 /etc/mysql.cnf

$ vi /etc/mysql.cnf

…
[mysqld]
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
server-id       = 2
slave-skip-errors = all

2.2 mysql 재시작

$ /bin/sh /usr/local/mysql/bin/mysqld_safe &

 

2.3 CHANGE MASTER 세팅

mysql> SLAVE STOP;
1 row in set (0.00 sec)

mysql> CHANGE MASTER TO MASTER_HOST='10.0.10.100', MASTER_USER='repuser', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=198032;
1 row in set (0.00 sec)

mysql> FLUSH PRIVILEGES;
1 row in set (0.00 sec)

mysql> SLAVE START;
1 row in set (0.00 sec)

3. Master-Slaver 연동 확인

 

3.1 Slave 에러 로그 확인

 

3.2 Slave STATUS 메시지 확인

[연동 실패 로그]

mysql> show slave status G;

*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 10.0.10.100
                  Master_User: repuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 328
               Relay_Log_File: sdb-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000008
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              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: 328
              Relay_Log_Space: 106
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1130
                Last_IO_Error: error connecting to master 'repuser@10.0.10.100:3306' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 0
               Last_SQL_Error:

1 row in set (0.00 sec)

[연동 성공 로그]

mysql> show slave status G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.10.100
                  Master_User: repuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 328
               Relay_Log_File: sdb-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000008
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              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: 328
              Relay_Log_Space: 106
              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)
TAG
댓글
댓글쓰기 폼