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

MySQL GTID Implementation, Maintenance, and Best Practices Brian Cain (Dropbox) Gillian Gunson (GitHub) Mark Filipi (SurveyMonkey)

Agenda Intros ❏ Concepts ❏ Replication overview ❏ GTID Intro ❏ Implementation ❏ Maintenance ❏ New 5.7 Features and Advanced Concepts ❏ 2

About Mark • Works at SurveyMonkey • From Kansas • Formerly of PalominoDB and Garmin and preschool 3

About Gillian • Senior Infrastructure Engineer at GitHub • From Vancouver, BC, Canada • Formerly of Okta, PalominoDB, Oracle, Disney 4

About Brian • Database Engineer, MySQL SRE at Dropbox • From Seattle • Formerly of PalominoDB, Zappos, EMusic, etc • Also from Kansas 5

Tutorial Setup (Hour 2) Collect a DigitalOcean droplet host access card from the front ❏ Connect to wireless and ssh to the droplet host (server1) ❏ Confirm you can ssh to server2 and server3 from server1 ❏ Confirm replication is running ❏ server1 server2 master replica server3 replica 6

Concepts Traditional replication primer and introduction to GTID 7

Traditional MySQL replication primer Standard topologies ❏ SHOW MASTER STATUS ❏ SHOW SLAVE STATUS ❏ 8

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

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) 10

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

SHOW SLAVE STATUS Master information and running status Slave_IO_State: Waiting for master to send event Master_Host: 10.10.23.17 Master_User: repl Master_Port: 3306 Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0 Displays state of replication IO thread - whether logs are being pulled from the master. 12

SHOW SLAVE STATUS Master information and running status Slave_IO_State: Waiting for master to send event Master_Host: 10.10.23.17 Master_User: repl Master_Port: 3306 Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0 Configured host database 13

SHOW SLAVE STATUS Master information and running status Slave_IO_State: Waiting for master to send event Master_Host: 10.10.23.17 Master_User: repl Master_Port: 3306 Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0 MySQL user configured for replication 14

SHOW SLAVE STATUS Master information and running status Slave_IO_State: Waiting for master to send event Master_Host: 10.10.23.17 Master_User: repl Master_Port: 3306 Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0 DB port being connected to (default) 15

SHOW SLAVE STATUS Master information and running status Slave_IO_State: Waiting for master to send event Master_Host: 10.10.23.17 Master_User: repl Master_Port: 3306 Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0 Two replication threads, one reading from master, other executing SQL on replica. 16

SHOW SLAVE STATUS Master information and running status Slave_IO_State: Waiting for master to send event Master_Host: 10.10.23.17 Master_User: repl Master_Port: 3306 Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0 Seconds between timestamp in binlog, and time on replica 17

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 18

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 19

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 20

SHOW SLAVE STATUS SSL information Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: 21

SHOW SLAVE STATUS Filtering information Until_Condition: None Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Filtered replication settings -- Use with caution 22

Defining GTID Global Transaction IDentifier - source_id:transaction_id - e200c55b-7832-11e5-9d51-00259082ca78:1 - source_id - normally the server_uuid of the master - transaction_id - sequential integer (starts at 1) representing the order a transaction - was committed on the source 23

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 24 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' 25 ### @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 26

GTID Sets & Related Variables Rather than just a single transaction_id, an interval is given - A range of transactions - e200c55b-7832-11e5-9d51-00259082ca78:1-1234 - Two ranges with a gap - e200c55b-7832-11e5-9d51-00259082ca78:1-1234,1236-1240 - Commonly used variables related to GTID - - server_uuid - enforce_gtid_consistency - gtid_mode - gtid_next 27

GTID vs Binlog Position Traditional replication coordinates - - MASTER_LOG_FILE - MASTER_LOG_POS GTID replication coordinates - - MASTER_AUTO_POSITION 28

Enabling GTIDs in Oracle MySQL 5.6 Guarantee master and replica(s) are in sync by enabling read_only on the master and - allow replication to catch up Shutdown the master and replica(s) - Add the following to my.cnf - - enforce-gtid-consistency - gtid-mode = ON - skip-slave-start - log-slave-updates - read-only = 1 - Start the master and replica(s) Start replication - CHANGE MASTER TO MASTER_HOST=’server1’, MASTER_AUTO_POSITION=1; START SLAVE; Disable read_only on the master and remove read-only from the my.cnf - 29