배경
이전 글과 같이 갑작스런 Primary OS crash에 따른 Master-Slave 간 sync_binlog 파라미터의 값에 따라 데이터 정합성에 깨짐이 있는지 확인 해볼 생각입니다.
sync_binlog 관련 내용은 이전 글 또는 docs를 참조해주세요.
[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)
$ 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)
<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 중단
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 |