MySQL GTID Implementation, Maintenance, and Best Practices - The - - PowerPoint PPT Presentation

▶
mysql gtid implementation maintenance and best practices
SMART_READER_LITE
LIVE PREVIEW

MySQL GTID Implementation, Maintenance, and Best Practices - The - - PowerPoint PPT Presentation

Aug 20, 2023 459 likes •1k views

MySQL GTID Implementation, Maintenance, and Best Practices - The Short Version Brian Cain (Dropbox) Mark Filipi (SurveyMonkey) Agenda 2 About Mark 3 About Brian 4

slide-1
SLIDE 1

MySQL GTID Implementation, Maintenance, and Best Practices

  • The Short Version

Brian Cain (Dropbox) Mark Filipi (SurveyMonkey)

slide-2
SLIDE 2

Agenda

❏ ❏ ❏ ❏ ❏ ❏ ❏

2

slide-3
SLIDE 3

About Mark

  • 3
slide-4
SLIDE 4

About Brian

  • 4
slide-5
SLIDE 5

Concepts

slide-6
SLIDE 6

Traditional MySQL replication primer

❏ ❏ ❏

6

slide-7
SLIDE 7

Standard topologies

server1 master server2 replica server3 replica server1 master server2 relay server3 replica

7

slide-8
SLIDE 8

SHOW MASTER STATUS

markf@db-wfcore03-ro [(none)]> show master status; +------------------+-----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+-----------+--------------+------------------+ | mysql-bin.000695 | 264631170 | | | +------------------+-----------+--------------+------------------+ 1 row in set (0.08 sec)

8

slide-9
SLIDE 9

SHOW SLAVE STATUS

markf@db-wfcore03-ro [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.10.23.17 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000751 Read_Master_Log_Pos: 67329044 Relay_Log_File: mysqld-relay-bin.000403 Relay_Log_Pos: 67329189 Relay_Master_Log_File: mysql-bin.000751 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: 67329044 Relay_Log_Space: 67329388 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: 1 row in set (0.08 sec)

9

slide-10
SLIDE 10

SHOW SLAVE STATUS

Log file information Master_Log_File: mysql-bin.000751 Read_Master_Log_Pos: 67329044 Relay_Log_File: mysqld-relay-bin.000403 Relay_Log_Pos: 67329189 Relay_Master_Log_File: mysql-bin.000751 Exec_Master_Log_Pos: 67329044 Relay_Log_Space: 67329388 Binary log file on master, and position it’s read to

10

slide-11
SLIDE 11

SHOW SLAVE STATUS

Log file information Master_Log_File: mysql-bin.000751 Read_Master_Log_Pos: 67329044 Relay_Log_File: mysqld-relay-bin.000403 Relay_Log_Pos: 67329189 Relay_Master_Log_File: mysql-bin.000751 Exec_Master_Log_Pos: 67329044 Relay_Log_Space: 67329388 Position in relay log on replica

11

slide-12
SLIDE 12

SHOW SLAVE STATUS

Log file information Master_Log_File: mysql-bin.000751 Read_Master_Log_Pos: 67329044 Relay_Log_File: mysqld-relay-bin.000403 Relay_Log_Pos: 67329189 Relay_Master_Log_File: mysql-bin.000751 Exec_Master_Log_Pos: 67329044 Relay_Log_Space: 67329388 Position in binary log that SQL thread has executed on replica

12

slide-13
SLIDE 13

e200c55b-7832-11e5-9d51-00259082ca78:1

Defining GTID

13

slide-14
SLIDE 14

Binary Log Contents

Standard replication

# at 2637016 #160307 15:05:42 server id 3031 end_log_pos 2637016 Table_map: `C0070735`.`FormStats` mapped to number 139874072 #160307 15:05:42 server id 3031 end_log_pos 2637088 Update_rows: table id 139874072 flags: STMT_END_F BINLOG ' RgneVhPXCwAAOAAAANg8KAAABhPVggAAAEACUMwMDcwMzczNQAJm9ybVN0YXRzAAQDDAMDAAA= RgneVhjXCwAASAAAACA9KAAABhPVggAAAEABP//8AdPAACwPPLvVIAACgBAAAMAAAA8AdPAACw PPLvVRIAACkBAAAMAAAA '/*!*/; ### UPDATE C0070735.FormStats ### WHERE ### @1=20231 ### @2=2016-03-07 15:00:00 ### @3=296 ### @4=12 ### SET ### @1=20231 ### @2=2016-03-07 15:00:00 ### @3=297 ### @4=12 # at 2637088 #160307 15:05:42 server id 3031 end_log_pos 2637115 Xid = 2004736153 COMMIT/*!*/;

14

slide-15
SLIDE 15

Binary Log Contents

GTID Enabled

#160323 14:37:48 server id 168433453 end_log_pos 41956 CRC32 0xee79822d GTID [commit=yes] SET @@SESSION.GTID_NEXT= '81b0bb5e-f004-11e5-aaa3-b8ca3a676681:100'/*!*/; # at 41956 #160323 14:37:48 server id 168433453 end_log_pos 42033 CRC32 0xdac047b0 Query thread_id=4611 exec_time=0 error_code=0 SET TIMESTAMP=1458769068/*!*/; BEGIN /*!*/; # at 42033 #160323 14:37:48 server id 168433453 end_log_pos 42094 CRC32 0xd3c70a01 Table_map: `C01840587`.`FormStats` mapped to number 74 # at 42094 #160323 14:37:48 server id 168433453 end_log_pos 42166 CRC32 0xa031417e Update_rows: table id 74 flags: STMT_END_F BINLOG ' rAzzVhMtFwoKPQAAAG6kAAAAAEoAAAAAAUMwMTg0MDU4NwAJRm9ybVN0YXRzAAQDEgMDAQAAAQrH0w== rAzzVh8tFwoKSAAAALakAAAAAEoAAA///wBAAAAJmY7uAAAgAAAAIAAADwBAAAAJmY7uAAAwAAAAIAAAB+QTGg '/*!*/; ### UPDATE `C01840587`.`FormStats` ### WHERE ### @1=4 ### @2='2016-03-23 14:00:00' ### @3=2

15

slide-16
SLIDE 16

SHOW SLAVE STATUS

NEW GTID Information

Master_UUID: 81b0bb5e-f004-11e5-aaa3-b8ca3a676681 Retrieved_Gtid_Set: 81b0bb5e-f004-11e5-aaa3-b8ca3a676681:1-51 Executed_Gtid_Set: 81b0bb5e-f004-11e5-aaa3-b8ca3a676681:1-51

16

slide-17
SLIDE 17

e200c55b-7832-11e5-9d51-00259082ca78:1-1234 e200c55b-7832-11e5-9d51-00259082ca78:1-1234,1236-1240

  • 17

GTID Sets

& Related Variables

slide-18
SLIDE 18
  • 18

GTID vs Binlog Position

slide-19
SLIDE 19
  • CHANGE MASTER TO MASTER_HOST=’server1’, MASTER_AUTO_POSITION=1; START SLAVE;

19

Enabling GTIDs in Oracle MySQL 5.6

slide-20
SLIDE 20
  • 20

Potential Replication Conflicts

slide-21
SLIDE 21

Implementation

slide-22
SLIDE 22

Percona Server 5.6 implementation

❏ ❏ ❏

22

slide-23
SLIDE 23

Starting topology

server1 master server2 replica server3 replica

23

slide-24
SLIDE 24

MySQL instance: replication configuration

❏ ■

master_log_file

■

master_log_pos

24

slide-25
SLIDE 25

Important 5.6 GTID variables

❏ ■ ■

log-bin, log-slave-updates, enforce-gtid-consistency

❏ ■ ■ ■

  • 25
slide-26
SLIDE 26

Important GTID variables (cont.)

❏ ■

  • ■

CHANGE MASTER master_log_file, master_log_pos

■

Auto_Position SHOW SLAVE STATUS

■

26

slide-27
SLIDE 27

Important GTID variables (cont.)

❏

gtid_deployment_step ON master_auto_position=1

27

slide-28
SLIDE 28

General steps

❏

[mysqld] /etc/mysql/my.cnf enforce-gtid-consistency = 1 gtid_deploymen_step = on gtid-mode = ON

❏

service mysql restart

28

slide-29
SLIDE 29

Prep work: results

❏

server1 master server2 replica server3 replica

gtid_mode=ON gtid_deployment_step=ON gtid_mode=ON gtid_deployment_step=ON gtid_mode=OFF gtid_deployment_step=OFF

29

slide-30
SLIDE 30

Enable GTIDs: Topology change

❏

server1 master server2 replica server3 replica server1 replica server2 master server3 replica

30

slide-31
SLIDE 31

General steps

❏

[mysqld] /etc/mysql/my.cnf enforce-gtid-consistency = 1 gtid_deployment_step = on gtid-mode = ON

❏

service mysql restart

❏

gtid_deployment_step = off

❏

CHANGE MASTER TO master_auto_position = 1;

31

slide-32
SLIDE 32

Important GTID status info variables

❏ ■ ■

  • CHANGE MASTER
  • RESET SLAVE
  • relay-log-recovery

❏ ■ ■

  • SHOW MASTER STATUS
  • SHOW SLAVE STATUS
  • gtid_executed

32

slide-33
SLIDE 33

GTID slave status

(root@server2) [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_Running: Yes Slave_SQL_Running: Yes ... Master_UUID: cc83d91e-d0e4-11e5-9faf-02cddc874cbb ... Retrieved_Gtid_Set: cc83d91e-d0e4-11e5-9faf-02cddc874cbb:1-107 Executed_Gtid_Set: c866b7ac-d0e4-11e5-9faf-020a6fe2a217:1-442, cc83d91e-d0e4-11e5-9faf-02cddc874cbb:1-107 Auto_Position: 1

33

slide-34
SLIDE 34

GTID master status

(root@server2) [(none)]> show master status\G *************************** 1. row *************************** File: mysql-bin.000002 Position: 1976131 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: c866b7ac-d0e4-11e5-9faf-020a6fe2a217:1-442, cc83d91e-d0e4-11e5-9faf-02cddc874cbb:1-107 1 row in set (0.00 sec)

34

slide-35
SLIDE 35

Maintenance

slide-36
SLIDE 36

Maintenance

❏ ❏ ❏ ❏ ❏

36

slide-37
SLIDE 37

Currently writing master

❏

server1 master server2 relay server3 replica

server_uuid bd933998-f2c5-11e5-bc9a-021b71 e877a3 Executed_Gtid_Set bcc3d83d-f2c5-11e5-bc9a-029d98 5ea7a3:1-2, bd933998-f2c5-11e5-bc9a-021b71 e877a3:1-111 server_uuid bcc3d83d-f2c5-11e5-bc9a-029 d985ea7a3 Master_UUID bd933998-f2c5-11e5-bc9a-021 b71e877a3 server_uuid be2ed142-f2c5-11e5-bc9a-027 4358cd201 Master_UUID bcc3d83d-f2c5-11e5-bc9a-029 d985ea7a3 37

slide-38
SLIDE 38

GTID set gaps

❏ ❏ ❏

Executed_Gtid_Set bcc3d83d-f2c5-11e5-bc9a-029d985ea7a3:1-2, bd933998-f2c5-11e5-bc9a-021b71e877a3: 1-111:113-120

38

slide-39
SLIDE 39

Finding transactions

❏ ❏ ❏ ❏

mysqlbinlog --no-defaults -vvv --base64-output=DECODE-ROWS

  • -include-gtids='bd933998-f2c5-11e5-bc9a-021b71e877a3:112'

/var/lib/mysql/mysql-bin.000002 SET @@SESSION.GTID_NEXT= 'bd933998-f2c5-11e5-bc9a-021b71e877a3:112'/*!*/;

39

slide-40
SLIDE 40

Fixing transactions with gtid_next

❏ ❏ ❏ ❏

40

slide-41
SLIDE 41

Faking and skipping transactions

❏ ❏ ❏

set gtid_next='xxx_gtid_xxx:nnn'; BEGIN; COMMIT;

41

slide-42
SLIDE 42

Advanced Concepts

slide-43
SLIDE 43

Advanced Concepts

❏ ❏ ❏ ❏

…

❏

43

slide-44
SLIDE 44

GTID Variables

❏ ❏ ❏ ❏

44

slide-45
SLIDE 45

binlog_gtid_simple_recovery

❏ ❏ ❏ ❏ ❏ ❏ ❏

45

slide-46
SLIDE 46

GTID Set functions

❏ ❏ ❏ ❏ ❏

select gtid_subtract(@@global.gtid_executed,@@global.gtid_purged)

❏ ❏

46

slide-47
SLIDE 47

START SLAVE UNTIL ...

❏ ❏ ❏ ❏

47

slide-48
SLIDE 48

SHOW SLAVE STATUS NONBLOCKING

❏ ❏ ❏ ❏ ❏ ❏

48

slide-49
SLIDE 49

5.7 GTID Features

slide-50
SLIDE 50

New items or changes in 5.7

❏ ❏ ❏

50

slide-51
SLIDE 51

Enabling GTID online

❏ ❏ ❏ ❏ ❏

51

slide-52
SLIDE 52

mysql.gtid_executed table

❏ ❏ ❏ ❏

52

slide-53
SLIDE 53

❏ ❏

53

slide-54
SLIDE 54

Thank you!