[Linux] Mysql Group Replication

  Master                                                                                    

Master 서버 Replication 유저 추가

# mysql -u root -p mysql> grant replication slave on *.* TO [replication_username]@'[private IP of db02]' identified by '[some password]'; mysql> flush privileges; mysql> quit

Master 서버 환경설정 ( /etc/my.cnf 수정 )

 /etc/my.cnf 

log-bin=mysql-bin

server-id=1

bind-address = 0.0.0.0

Master 서버 DB 재시작

# systemctl restart mysqld

Master 서버 데이터 복사

# mysql -u root -p

mysql> FLUSH TABLES WITH READ LOCK;

mysql> SHOW MASTER STATUS;

# mysqldump -u root -p --all-databases --lock-all-tables --events > db_dump.sql 

# mysql -u root -p

mysql> UNLOCK TABLES;

Master 서버 File 및 Position 값 알아내기

mysql> show master status;

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

| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000005 |    1848 |                  |                      |                     |

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

1 row in set (0.00 sec)

slave에 DB 덤프파일 전송

# scp /root/db_dump.sql Slave's IP:경로

  Slave                                                                                    

Slave 서버 환경설정 ( /etc/my.cnf 수정 )

/etc/my.cnf

log-bin=mysql-bin

server-id=2

bind-address = 0.0.0.0

slave-skip-errors=1062

master 서버의 데이터 import

# mysql -u root -p < /root/db_dump.sql

# service mysqld restart

slave 서버 Master 정보 설정

# mysql -u root -p

mysql> CHANGE MASTER TO 

MASTER_HOST='마스터IP',

MASTER_USER='유저아이디',

MASTER_PASSWORD='비밀번호',

MASTER_PORT=3306,

//master에서 show master status 명령으로 알아낸 파일 이름과 position 값을 입력합니다.

MASTER_LOG_FILE='File값',

MASTER_LOG_POS=Position값,

Slave 서버 Replication 시작

mysql> START SLAVE;

Replication 동작 확인

mysql> show slave status\G;

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

           Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.0.104

                  Master_User: slave

                  Master_Port: 3306

                  Connect_Retry: 60

               Master_Log_File: mysql-bin.000005

          Read_Master_Log_Pos: 1374

               Relay_Log_File: localhost-relay-bin.000002

                Relay_Log_Pos: 511

        Relay_Master_Log_File: mysql-bin.000005

             Slave_IO_Running: Yes

            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: 1374

              Relay_Log_Space: 722

              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: 

  Replicate_Ignore_Server_Ids: 

             Master_Server_Id: 1

                  Master_UUID: 4ac962c3-abf0-11e7-a431-0800276968f1

             Master_Info_File: /var/lib/mysql/master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

           Master_Retry_Count: 86400

                  Master_Bind: 

      Last_IO_Error_Timestamp: 

     Last_SQL_Error_Timestamp: 

               Master_SSL_Crl: 

           Master_SSL_Crlpath: 

           Retrieved_Gtid_Set: 

            Executed_Gtid_Set: 

                Auto_Position: 0

         Replicate_Rewrite_DB: 

                 Channel_Name: 

           Master_TLS_Version: 

1 row in set (0.01 sec)

Slave has read all relay log; waiting for more updates라는 메세지가 나오면 정상적으로 실행된거에요

  Test                                                                                     

Master

# mysql -u root -p

mysql> create database group_replication;

mysql> use group_replication;

mysql> create table three(idx int);

mysql> create table five(idx int);

mysql> insert into five values (30);

mysql> show tables;

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

| Tables_in_group_replication |

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

| five                        |

| three                       |

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

2 rows in set (0.00 sec)

mysql> select * from five;

+------+

| idx  |

+------+

|   30 |

+------+

1 row in set (0.00 sec)

mysql> create table second(idx int);

master 에서 데이터베이스를 새로 만들고 테이블과 값들을 추가해줍니다.

Slave

mysql> show tables;

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

| Tables_in_group_replication |

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

| five                         |

| second                    |

| three                       |

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

3 rows in set (0.00 sec)

mysql> select * from five;

+------+

| idx  |

+------+

|   30 |

+------+

1 row in set (0.00 sec)

그리고 나서 slave에서 확인해보면 master에서 추가했던 데이터들이 업데이트 되어있습니다!