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)

 

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)

 

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