Table migrations remain a pain point for MySQL DBAs. There are more options than ever for running migrations, with the later versions' in-place alters and new third-party tools (like gh-ost). But with the increase in tools and procedures it's been shown that there is no one-size-fits-all tool. Depending on the table size, available disk space, database traffic, server performance and SLAs, some migration methods make more sense than others.
In this tutorial we will discuss and demonstrate the different tools and methods and the best practices and scenarios for each.
Optional Lab Requirements:
- MacOS or Linux laptop (or VM)
- MySQL Sandbox, Percona Toolkit, gh-ost, sysbench
- MySQL 5.7 generic binary (for MySQL Sandbox)
Migration Concepts and Types
- Straight and in-place ALTER TABLE
- Alter on replicas, then promote
Caveats and Best Practices
- Test each of the migration types in a database cluster
Gillian has been a database infrastructure engineer at GitHub for two years. Her focus has been on performance troubleshooting and observability. Previous employers include Okta, PalominoDB, Oracle and Disney. Gillian is based in Vancouver, BC, Canada.
Brian is a Database Engineer at SurveyMonkey. He has worked with MySQL for the past 6 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.
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.