MySQL GTID Implementation, Maintenance, and Best Practices
Overview: Many MySQL shops have made the transition from MySQL 5.5 to 5.6 in the last two years, but GTID (global transaction identifier) implementation has often been considered too difficult or risky a change. MySQL 5.7 is making GTID implementation easier, but it may not yet be clear why to convert. This 3 hour tutorial will cover GTID concepts, provide hands-on experience for converting from default replication, and demonstrate techniques for being successful with GTID. We will be using Percona Server for the lab exercises. Format: The attendees will be provided EC2 instances to run the labs. Target audience: Intermediate level MySQL DBAs familiar with the general concepts of replication. Agenda: Concepts - Definition - How GTIDs differ from traditional binary log positioning and advantages for converting - GTID sets and how they relate to missing transactions, slave_parallel_workers, and slave_parallel_type - Compare implementation steps between Oracle MySQL and Percona Server - server_id, server_uuid, and how a new server may cause conflicts with GTID Implementation - Converting a running master-slave topology to use GTIDs - Adding a new slave to create a 3-tier topology (master - relay - slave) - Promoting a slave to master and to relay Maintenance - Reviewing GTID sets to determine the currently writing master - Demonstrating GTID set gaps caused by parallelism versus missing transactions - Finding extra or missing transactions within the binary log - Correcting missing data using gtid_next - “Faking” a transaction to correct out of sync issues New 5.7 Features and Advanced Concepts - Replication feature changes in 5.7 - Specific settings to tune or ignore for 5.6 and 5.7
Database Engineer - MySQL SRE, Dropbox
Brian is a Database Engineer MySQL SRE for Dropbox. He has worked with MySQL for the past 5 years supporting multiple client environments as a consultant or FTE. He spends most of his time as an operational DBA automating routine tasks such as instance cloning and master promotions along with enhancing monitoring and alerts to proactively detect upcoming issues. He has a background as a developer, manager, and systems administrator over the past 20+ years. Brian is based out of Seattle, WA.
Senior Database Administrator, SurveyMonkey
Mark's role at SurveyMonkey spans a variety of applications and users that utilize MySQL. Previously at PalominoDB, he worked on everything from startups to video games to presidential elections. Based out of Kansas City, he enjoys very fast internet.
Gillian joined the GitHub infrastructure team in January 2016. She's worked as a DBA at such companies as Okta, PalominoDB/BlackbirdIT, Pythian and Disney, and spent 3 years on the MySQL support team at Oracle. Her home base is Vancouver, BC.