MySQL_GTID 란?
gtid 란?
- Global Transaction IDentifiers
- GTID = source_id:transaction_Id
- source_id = server_uuid
[root@c2b2e13b86b9 data]# cat auto.cnf
[auto]
server-uuid=ba315763-e7e8-11e9-9c29-0242ac110002
- 해당 호스트에서 수행한 트랜잭션에 global 한 id 를 commit 순서에 따라 순차적으로 부여함
ba315763-e7e8-11e9-9c29-0242ac110002:4638
=> ba315763-e7e8-11e9-9c29-0242ac110002 server 에서 수행된 4638번 째 트랜잭션을 의미
- GTID는 master <-> slave 간 복제 시작, 중지의 기준이 되었던 binlog file-offset pairs을 대신함
=> CHANGE MASTER TO 구문의 MASTER_LOG_FILE, MASTER_LOG_POS 필요없음
=> GTID 만 보면 되기 때문에 replication master - slave 간 일관성 확인이 쉬움 - slave에 반영된 gtid 트랜잭션이 mysql.gtid_executed 로 관리되기 때문에 중복 수행 안됨
GTID life cycle
- Master) 트랜잭션 실행 후 커밋되면 GTID를 할당 받고 master의 binlog 에 기록됨
- 할당된 GTID 는 gtid_executed system variable 과 mysql.gtid_executed 에 저장됨
mysql> select @@global.gtid_executed;
+------------------------------------------+
| @@global.gtid_executed |
+------------------------------------------+
| ba315763-e7e8-11e9-9c29-0242ac110002:1-3 |
+------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| ba315763-e7e8-11e9-9c29-0242ac110002 | 1 | 1 |
| ba315763-e7e8-11e9-9c29-0242ac110002 | 2 | 2 |
| ba315763-e7e8-11e9-9c29-0242ac110002 | 3 | 3 |
+--------------------------------------+----------------+--------------+
3 rows in set (0.00 sec)
=> DB에 이 GTID 까지 반영이 되었다는 것을 보장함
- Slave ) master 의 binlog => slave relay log로 저장된 후 slave 는 GTID 를 읽고 gtid_next 값을 설정함
SET @@SESSION.GTID_NEXT= 'ba315763-e7e8-11e9-9c29-0242ac110002:3'/*!*/;
- Slave ) gtid_executed 를 확인하여 해당 GTID가 DBB에 미반영 건임을 확인 후 DBB에 반영함
GTID 적용
- 적용 전 필수 옵션
* gtid_mode
* log-bin
* log_slave_updates
* enforce-gtid-consistency
=> mysql.gtid_executed 테이블 등장 전에는 log_bin, log_slave_updates
옵션이 모든 db에서 필수였지만 등장 후로는 master , master candidate slave서버에서만 켜줘도 됨
- master_log_file / pos 기반 replication
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.0.4
Master_User: replUser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000005
Read_Master_Log_Pos: 195
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 319
Relay_Master_Log_File: binlog.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
- enforce-gtid-consistency 옵션 ON (M/S)
mysql> set global enforce_gtid_consistency=warn;
Query OK, 0 rows affected (0.05 sec)
mysql> create table tb_test2 as select * From tb_test;
Query OK, 112000 rows affected, 1 warning (26.61 sec)
Records: 112000 Duplicates: 0 Warnings: 1
mysql> show warnings;
+---------+------+---------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------+
| Warning | 1786 | Statement violates GTID consistency: CREATE TABLE ... SELECT. |
+---------+------+---------------------------------------------------------------+
1 row in set (0.00 sec)
=> GTID mode 에선 CTAS , CREATE TEMPORARY TABLE(트랜잭션 안에서) 불가 warn 상태에서는 허용하되 warning 남기는데 이런 쿼리들을 전부 수정 후 enforce_gtid_consistency 옵션을 ON 해야함
mysql> set global enforce_gtid_consistency=ON;
Query OK, 0 rows affected (0.01 sec)
mysql> create table tb_test3 as select * From tb_test;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.
- gtid_mode off_permissive 로 전환 ( M/S )
mysql> set global gtid_mode=off_permissive;
Query OK, 0 rows affected (0.01 sec)
=> GTID 트랜잭션이 전달되어도 처리할 수 있는 mode
- gtid_mode on_permissive 로 전환 ( M/S )
mysql> set global gtid_mode=on_permissive;
Query OK, 0 rows affected (0.01 sec)
#200112 8:10:28 server id 64 end_log_pos 687 CRC32 0x089d71a4 GTID last_committed=2 sequence_number=3 rbr_only=no original_committed_timestamp=1578816628033859 immediate_commit_timestamp=1578816628033859 transaction_length=308
SET @@SESSION.GTID_NEXT= 'ba315763-e7e8-11e9-9c29-0242ac110002:4641'/*!*/;
# at 773
#200112 8:10:28 server id 64 end_log_pos 885 CRC32 0x645cb197 Query thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1578816628/*!*/;
insert into gtid_test values(1)
/*!*/;
# at 885
#200112 8:10:28 server id 64 end_log_pos 916 CRC32 0x59107b8a Xid = 32
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
=> SET @@SESSION.GTID_NEXT= ‘ba315763-e7e8-11e9-9c29-0242ac110002:4641’/!/;
binlog 에 기록할 트랜잭션의 GTID 를 설정하고 binlog 기록
SET @@SESSION.GTID_NEXT= ‘AUTOMATIC’ /* added by mysqlbinlog */ /*!*/;
그 후 다시 AUTOMATIC 으로 설정
#200105 6:28:30 server id 64 end_log_pos 155 CRC32 0xcf5b09e0 Previous-GTIDs
# [empty]
#200105 7:19:42 server id 64 end_log_pos 202 CRC32 0xe4311d63 Rotate to mysql-bin.000026 pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
.
.
SET @@SESSION.GTID_NEXT= 'ba315763-e7e8-11e9-9c29-0242ac110002:1'/*!*/;
=> GTID 첫 설정이면 위와 같이 empty 로 보임
- GTID 없는 트랜잭션 확인
mysql> show global status like 'ongoing%';
+----------------------------------------------------+-------+
| Variable_name | Value |
+----------------------------------------------------+-------+
| Ongoing_anonymous_gtid_violating_transaction_count | 0 |
| Ongoing_anonymous_transaction_count | 0 |
| Ongoing_automatic_gtid_violating_transaction_count | 0 |
+----------------------------------------------------+-------+
3 rows in set (0.05 sec)
=> 위 값이 0이면 정상
- gtid_mode on으로 전환 ( M/S )
mysql> set global gtid_mode=on;
Query OK, 0 rows affected (0.01 sec)
=> 모든 트랜잭션이 gtid mode 이며 아닌 트랜잭션은 실행이 안되는 상태
- replication 재설정 ( S )
mysql> change master to
-> master_auto_position=1;
=> master_log_file / master_log_pos 옵션 대신 master_auto_position 사용
replication 실패하면?
- gtid_next 활용
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 1049
Last_Error: Error 'Unknown database 'testdb'' on query. Default database: 'testdb'. Query: 'insert into gtid_test values(1)'
Last_SQL_Errno: 1049
Last_SQL_Error: Error 'Unknown database 'testdb'' on query. Default database: 'testdb'. Query: 'insert into gtid_test values(1)'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 64
Master_UUID: ba315763-e7e8-11e9-9c29-0242ac110002
Master_Info_File: mysql.slave_master_info
Last_SQL_Error_Timestamp: 200112 10:36:35
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: ba315763-e7e8-11e9-9c29-0242ac110002:4641
Executed_Gtid_Set:
Auto_Position: 1
1 row in set (0.01 sec)
=> ba315763-e7e8-11e9-9c29-0242ac110002:4641 트랜잭션을 slave에서 반영하려는데 실패함
mysql> set global sql_slave_skip_counter=1;
ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction
=> GTID 상태에선 sql_slave_skip_counter 수행 불가
mysql> set @@session.gtid_next='ba315763-e7e8-11e9-9c29-0242ac110002:4641';
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> set gtid_next='AUTOMATIC';
Query OK, 0 rows affected (0.01 sec)
=> 4641 transaction 이 실패했기 때문에 4641을 트랜잭션을 empty 트랜잭션 처리하는 방법
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.0.2
Master_User: replUser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 463
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 671
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
.
.
Retrieved_Gtid_Set: ba315763-e7e8-11e9-9c29-0242ac110002:4641
Executed_Gtid_Set: ba315763-e7e8-11e9-9c29-0242ac110002:4641
Auto_Position: 1