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

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

Agenda ❏ ❏ ❏ ❏ ❏ ❏ ❏ 2

About Mark • • • 3

About Brian • • • • 4

Concepts

Traditional MySQL replication primer ❏ ❏ ❏ 6

Standard topologies server1 server2 server1 server2 master replica master relay server3 server3 replica replica 7

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

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 9 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.08 sec)

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

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

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

Defining GTID e200c55b-7832-11e5-9d51-00259082ca78:1 13

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 14 COMMIT/*!*/;

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' 15 ### @3=2

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

GTID Sets & Related Variables e200c55b-7832-11e5-9d51-00259082ca78:1-1234 e200c55b-7832-11e5-9d51-00259082ca78:1-1234,1236-1240 - - - - 17

GTID vs Binlog Position - - - 18

Enabling GTIDs in Oracle MySQL 5.6 - - - - - CHANGE MASTER TO MASTER_HOST=’server1’, MASTER_AUTO_POSITION=1; START SLAVE; 19

Potential Replication Conflicts - - - - - 20

Implementation

Percona Server 5.6 implementation ❏ ❏ ❏ 22

Starting topology server1 server2 master replica server3 replica 23

MySQL instance: replication configuration ❏ master_log_file ■ master_log_pos ■ 24

Important 5.6 GTID variables ❏ ■ log-bin, log-slave-updates, enforce-gtid-consistency ■ ❏ ■ ■ ■ ● ● 25

Important GTID variables (cont.) ❏ ■ ● CHANGE MASTER master_log_file, ■ master_log_pos Auto_Position SHOW SLAVE STATUS ■ ■ 26

Important GTID variables (cont.) gtid_deployment_step ON master_auto_position=1 ❏ 27

General steps [mysqld] /etc/mysql/my.cnf ❏ enforce-gtid-consistency = 1 gtid_deploymen_step = on gtid-mode = ON service mysql restart ❏ 28

Prep work: results ❏ gtid_mode=OFF server1 server2 gtid_mode=ON gtid_deployment_step=OFF master replica gtid_deployment_step=ON server3 gtid_mode=ON replica gtid_deployment_step=ON 29

Enable GTIDs: Topology change ❏ server1 server2 server1 server2 replica master master replica server3 server3 replica replica 30

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

Important GTID status info variables ❏ ■ ■ CHANGE MASTER ● RESET SLAVE ● relay-log-recovery ● ❏ ■ ■ SHOW MASTER STATUS ● SHOW SLAVE STATUS ● gtid_executed ● 32

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

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

Maintenance

Maintenance ❏ ❏ ❏ ❏ ❏ 36

Currently writing master ❏ server_uuid server_uuid bd933998-f2c5-11e5-bc9a-021b71 bcc3d83d-f2c5-11e5-bc9a-029 e877a3 server1 server2 Executed_Gtid_Set d985ea7a3 Master_UUID bcc3d83d-f2c5-11e5-bc9a-029d98 master relay 5ea7a3:1-2, bd933998-f2c5-11e5-bc9a-021 b71e877a3 bd933998-f2c5-11e5-bc9a-021b71 e877a3:1-111 server_uuid be2ed142-f2c5-11e5-bc9a-027 4358cd201 server3 Master_UUID replica bcc3d83d-f2c5-11e5-bc9a-029 d985ea7a3 37

GTID set gaps ❏ ❏ ❏ Executed_Gtid_Set bcc3d83d-f2c5-11e5-bc9a-029d985ea7a3:1-2, bd933998-f2c5-11e5-bc9a-021b71e877a3: 1-111:113-120 38

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

Fixing transactions with gtid_next ❏ ❏ ❏ ❏ 40

Faking and skipping transactions ❏ ❏ ❏ set gtid_next ='xxx_gtid_xxx:nnn'; BEGIN; COMMIT; 41

Advanced Concepts

Advanced Concepts ❏ ❏ ❏ … ❏ ❏ 43

GTID Variables ❏ ❏ ❏ ❏ 44

binlog_gtid_simple_recovery ❏ ❏ ❏ ❏ ❏ ❏ ❏ 45

GTID Set functions ❏ ❏ ❏ ❏ ❏ select gtid_subtract(@@global.gtid_executed,@@global.gtid_purged) ❏ ❏ 46

START SLAVE UNTIL ... ❏ ❏ ❏ ❏ 47

SHOW SLAVE STATUS NONBLOCKING ❏ ❏ ❏ ❏ ❏ ❏ 48

5.7 GTID Features

New items or changes in 5.7 ❏ ❏ ❏ 50