close
프로필 배경
프로필 로고

JH 개인 블로그

DataBase/MySQL & MariaDB · 2024. 2. 28. fullscreen 넓게보기

[MySQL & MariaDB - sync_binlog 0 vs 1 트랜잭션 보장 테스트]

 배경

이전 글과 같이 갑작스런 Primary OS crash에 따른 Master-Slave 간 sync_binlog 파라미터의 값에 따라 데이터 정합성에 깨짐이 있는지 확인 해볼 생각입니다.

 

sync_binlog 관련 내용은 이전 글 또는 docs를 참조해주세요.

https://jhdatabase.tistory.com/entry/MySQL-MariaDB-Replication-%EB%B3%B5%EC%A0%9C-%EC%97%90%EB%9F%AC-errorcode-1236

 

[MySQL & MariaDB - Replication 복제 에러 error_code : 1236]

배경 최근 Replication 구조에서 Primary DB가 갑작스레 서버가 죽으며, DB가 내려갔다. 이후 Slave DB에서 replication이 에라 발생과 함께 정상적으로 동작하지 않았다. 발생 에러 ■ Slave status MariaDB [(none)]>

jhdatabase.tistory.com

 

https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html

 

MySQL :: MySQL 8.0 Reference Manual :: 19.1.6.4 Binary Logging Options and Variables

19.1.6.4 Binary Logging Options and Variables You can use the mysqld options and system variables that are described in this section to affect the operation of the binary log as well as to control which statements are written to the binary log. For additi

dev.mysql.com

 

 

 사전 확인

<Master>
MariaDB [(none)]> show master status\G;
*************************** 1. row ***************************
            File: mysql.000001
        Position: 324
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.000 sec)


<Slave>
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.152.128
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 10
               Master_Log_File: mysql.000001
           Read_Master_Log_Pos: 324
                Relay_Log_File: mysql-relay-bin.000002
                 Relay_Log_Pos: 551
         Relay_Master_Log_File: mysql.000001
              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: 324
               Relay_Log_Space: 860
               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_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: No
                   Gtid_IO_Pos:
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.001 sec)

 

 

 테스트 시작

 

<Sync_binlog> =0 일 시
MariaDB [(none)]> show variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 0     |
+---------------+-------+
1 row in set (0.002 sec)

 

ㅁ Master에 데이터 insert 중 OS 종료
$ sh +x kim.sh
...
Inserted value: 3274
Inserted value: 3275
Inserted value: 3276
Inserted value: 3277
Inserted value: 3278
Inserted value: 3279
Inserted value: 3280
Inserted value: 3281
Inserted value: 3282
Inserted value: 3283
Inserted value: 3284
Inserted value: 3285
Inserted value: 3286
Inserted value: 3287
Inserted value: 3288
Inserted value: 3289
Inserted value: 3290
-- OS 중단

 

ㅁ Slave status 

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State:
                   Master_Host: 192.168.152.128
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 10
               Master_Log_File: mysql.000001
           Read_Master_Log_Pos: 568519
                Relay_Log_File: mysql-relay-bin.000002
                 Relay_Log_Pos: 568746
         Relay_Master_Log_File: mysql.000001
              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: 568519
               Relay_Log_Space: 569055
               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: 1236
                 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the first event 'mysql.000001' at 568519, the last event read from 'mysql.000001' at 4, the last byte read from 'mysql.000001' at 4.'
                Last_SQL_Errno: 0
                Last_SQL_Error:
   Replicate_Ignore_Server_Ids:
              Master_Server_Id: 1
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: No
                   Gtid_IO_Pos:
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 1
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 3290
1 row in set (0.000 sec)

 

ㅁ Next Binlog 파일로 change master 

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.002 sec)


MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.152.128', MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_PORT=3306,MASTER_LOG_FILE='mysql.000002', MASTER_LOG_POS=0,MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.005 sec)


MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)


MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.152.128
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 10
               Master_Log_File: mysql.000002
           Read_Master_Log_Pos: 338
                Relay_Log_File: mysql-relay-bin.000002
                 Relay_Log_Pos: 633
         Relay_Master_Log_File: mysql.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: 338
               Relay_Log_Space: 942
               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_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: No
                   Gtid_IO_Pos:
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 1
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 3290
1 row in set (0.000 sec)

 

ㅁ 정합성 비교
오히려 Slave가 더 많다. 
Master에는 disk에 unflushed된 바이너리로그 데이터는 사라지고, Slave는 Master의 메모리의 바이너리 로그까지 반영하였기 때문이다.
<Master>
MariaDB [test]> select count(*) from kim;
+----------+
| count(*) |
+----------+
|     3289 |
+----------+
1 row in set (0.003 sec)


<Slave>
MariaDB [test]> select count(*) from kim;
+----------+
| count(*) |
+----------+
|     3290 |
+----------+
1 row in set (0.003 sec)

 

 

 

 

<Sync_binlog> =1 일 시 
MariaDB [(none)]> show variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 1     |
+---------------+-------+
1 row in set (0.002 sec)

 

 

ㅁ Master에 데이터 insert 중 OS 종료 

$ sh +x kim.sh
...
Inserted value: 2055
Inserted value: 2056
Inserted value: 2057
Inserted value: 2058
Inserted value: 2059
Inserted value: 2060
Inserted value: 2061
Inserted value: 2062
Inserted value: 2063
Inserted value: 2064
Inserted value: 2065
Inserted value: 2066
Inserted value: 2067
-- OS 중단

 

ㅁ Master 기동 이후 특별한 조치 없이 replication 정상 확인 
MariaDB [test]> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.152.128
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 10
               Master_Log_File: mysql.000004
           Read_Master_Log_Pos: 338
                Relay_Log_File: mysql-relay-bin.000006
                 Relay_Log_Pos: 633
         Relay_Master_Log_File: mysql.000004
              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: 338
               Relay_Log_Space: 1237
               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_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: No
                   Gtid_IO_Pos:
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 1
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 6911
1 row in set (0.000 sec)

 

ㅁ 정합성 비교
트랜잭션을 보장해주기 때문에 동일하다
<Master>
MariaDB [test]> select count(*) from kim;
+----------+
| count(*) |
+----------+
|     2067 |
+----------+
1 row in set (0.002 sec)


<Slave>
MariaDB [test]> select count(*) from kim;
+----------+
| count(*) |
+----------+
|     2067 |
+----------+
1 row in set (0.002 sec)

 

저작자표시

'DataBase > MySQL & MariaDB' 카테고리의 다른 글

[MySQL & MariaDB - Replication 복제 에러 error_code : 1236]  (2) 2024.02.28
[MySQL & MariaDB - Federated Engine 사용 테스트]  (7) 2024.02.07
[MySQL & MariaDB - Relay Log 변경 시 replication 복제 에러 해결방법]  (0) 2024.01.05
[MySQL & MariaDB - Partition Exchange]  (0) 2024.01.04
[MySQL - 로컬 컴퓨터 to MySQL 서버 데이터 이관] use.MySQL workbench  (0) 2023.02.10
DataBase/MySQL & MariaDB 관련 글
더 보기

티스토리툴바