GTID마스터 슬레이브

데이터 가져오기

 

-- 복제 중지
STOP SLAVE;
RESET SLAVE;

-- 복제 설정
CHANGE MASTER TO
  MASTER_HOST='mysql-master',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='repl_password',
  MASTER_AUTO_POSITION=1;

 

# 보안을 이유로 repl_user 를 따로 만들어서 필요한 권한만 만들어주는게 좋습니다

# 당연히 유저는 마스터에서 만듭니다.

-- 복제 시작
START SLAVE;

-- 복제 상태 확인
SHOW SLAVE STATUS\G
```

# 컨테이너 상태 확인
docker stats mysql-master mysql-slave
```

추가 팁:
1. Docker 네트워크 생성하여 컨테이너 간 통신 보장
```bash
docker network create mysql-network
docker network connect mysql-network mysql-master
docker network connect mysql-network mysql-slave
```

2. 바이너리 로그 관리
```bash
# 마스터 컨테이너에서 바이너리 로그 확인
docker exec mysql-master mysql -uroot -p -e "SHOW BINARY LOGS"

# 불필요한 바이너리 로그 정리
docker exec mysql-master mysql -uroot -p -e "PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY)"
```

이러한 설정으로 Docker 환경에서 안정적인 MySQL 복제를 구성할 수 있습니다. 문제가 발생하면 로그를 확인하고 필요한 경우 컨테이너를 재시작하거나 재구성할 수 있습니다.

 

 

 

이제 데이터가 가져와졌으니까 적용합니다.

 

 

1. **마스터 컨테이너 설정**
```yaml
# docker-compose.yml의 mysql-master 부분
mysql-master:
    image: mysql:8.0
    container_name: mysql-master
    environment:
      MYSQL_ROOT_PASSWORD: rootpassword
      MYSQL_DATABASE: mydb
      MYSQL_USER: repl_user
      MYSQL_PASSWORD: repl_password
    volumes:
      - ./master//var/lib/mysql
      - ./master/conf:/etc/mysql/conf.d
    ports:
      - "3306:3306"
```

2. **마스터 설정 파일 (master/conf/master.cnf)**
```cnf
[mysqld]
server-id=1
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
binlog_expire_logs_seconds=604800
```

3. **마스터에서 복제 사용자 생성**
```sql
-- 마스터 MySQL에 접속
docker exec -it mysql-master mysql -uroot -p

-- 복제 사용자 생성 및 권한 부여
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;
```

4. **슬레이브 설정 파일 (slave/conf/slave.cnf)**
```cnf
[mysqld]
server-id=2
gtid_mode=ON
enforce-gtid-consistency=ON
```

5. **GTID 기반 복제 설정 (슬레이브에서)**
```sql
-- 슬레이브 MySQL에 접속
docker exec -it mysql-slave mysql -uroot -p

-- 기존 복제 설정 초기화
STOP SLAVE;
RESET SLAVE ALL;

-- GTID 기반 복제 설정
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='mysql-master',
    SOURCE_USER='repl_user',
    SOURCE_PASSWORD='repl_password',
    SOURCE_AUTO_POSITION=1;

-- 복제 시작
START SLAVE;

-- 복제 상태 확인
SHOW SLAVE STATUS\G
```

6. **복제 상태 확인**
```sql
-- 복제 상태 확인
SHOW SLAVE STATUS\G

-- 주요 확인 사항:
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- Last_IO_Error: (비어있어야 함)
-- Last_SQL_Error: (비어있어야 함)
```

7. **문제 해결 시 유용한 명령어**
```sql
-- GTID 상태 확인
SHOW GLOBAL VARIABLES LIKE 'gtid%';

-- 실행된 GTID 트랜잭션 확인
SELECT @@GLOBAL.GTID_EXECUTED;

-- 복제 오류 발생 시 건너뛰기
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;
```

주의사항:
1. SOURCE_AUTO_POSITION=1 사용 시에는 SOURCE_LOG_FILE, SOURCE_LOG_POS 등을 지정하지 않습니다.
2. 복제 설정 전에 마스터와 슬레이브의 데이터가 동기화되어 있어야 합니다.
3. 네트워크 설정이 올바르게 되어 있는지 확인하세요.

추가 팁:
```bash
# 컨테이너 간 네트워크 연결 확인
docker network inspect mysql-network

# 컨테이너 로그 확인
docker logs mysql-master
docker logs mysql-slave
```

 

해보면
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: mysql-master
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 837
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 420
        Relay_Master_Log_File: mysql-bin.000002
             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: 837
              Relay_Log_Space: 630
              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: c8134046-f848-11ef-856a-0242ac120004
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica 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: c8134046-f848-11ef-856a-0242ac120004:1-188,
d4911ee4-f848-11ef-8ea2-0242ac120006:1-176
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

mysql> SHOW TABLES;
+-----------------------+
| Tables_in_flight_data |
+-----------------------+
| booking               |
| flights_data          |
| forprice              |
| user                  |
+-----------------------+
4 rows in set (0.00 sec)

mysql> USE flight_data;
Database changed
mysql> SHOW TABLES;
+-----------------------+
| Tables_in_flight_data |
+-----------------------+
| booking               |
| flights_data          |
| forprice              |
| user                  |
+-----------------------+
4 rows in set (0.00 sec)

mysql> select * from user;
+---------+-----------+-----------+--------------------------------------------------------------+---------------------+--------+---------------+
| user_no | user_name | user_id   | user_pw                                                      | email               | gender | phone         |
+---------+-----------+-----------+--------------------------------------------------------------+---------------------+--------+---------------+
|       1 | ???       | hong123   | password123                                                  | hong@example.com    | ?      | 010-1234-5678 |
|       2 | ???       | kim456    | password456                                                  | kim@example.com     | ?      | 010-8765-4321 |
|       3 | ???       | sonny1234 | $2a$10$GC2lVMMcvULQvNOa2JKZZOag/fgtjM.U4QHD2PLjySReKyliEL0gm | sonny1234@naver.com | M      | 01099991111   |
+---------+-----------+-----------+--------------------------------------------------------------+---------------------+--------+---------------+
3 rows in set (0.00 sec)