Why Facebook finally moved to MySQL 8.0

Facebook's MySQL footprint runs on a heavily customized branch of the database. The team maintains its own patches on top of Oracle's open source code, and each major upstream release means re-basing that work. The move from 5.6 to 8.0 was particularly involved: Facebook's 5.6 branch carried more than 1,700 patches at the outset, and the upgrade skipped 5.7 entirely, meaning APIs deprecated in 5.7 and removed in 8.0 required updates to any application still using them.

The previous major upgrade, to 5.6, took over a year. When 5.7 shipped, the team was mid-way through building MyRocks, its LSM-Tree storage engine, on the 5.6 codebase. Staying on 5.6 let that work finish. By the time MySQL 8.0 was announced, MyRocks was rolling out to the user database (UDB) service tier, and the new version offered features Facebook wanted: writeset-based parallel replication, a transactional data dictionary with atomic DDL, and the 5.7 features it had skipped, including Document Store. Instant DDL in 8.0 promised faster MyRocks schema changes, but only on the 8.0 codebase.

5.6 was also nearing end of life, and Facebook wanted to remain active in the MySQL community, particularly around MyRocks. The decision was made to migrate.

The scope of the port

Porting custom patches was the largest chunk of work. The team set up an 8.0 branch for building and testing, then worked through each 5.6 patch, sorting them into four buckets:

  1. Drop: Features no longer used, or with equivalent functionality in 8.0.
  2. Build/Client: Non-server code for the build environment and MySQL tools, like mysqlbinlog or the async client API.
  3. Non-MyRocks Server: mysqld features unrelated to MyRocks.
  4. MyRocks Server: Features supporting the MyRocks storage engine.

Spreadsheets tracked the status and history of each patch, including the reasoning for any that were dropped. Patches touching the same feature were grouped together, and commits to the 8.0 branch were annotated with the original 5.6 commit information to resolve discrepancies. The client and server categories mapped naturally to release milestones: once all client work was ported, connector and tooling code could move to 8.0; with non-MyRocks server features done, InnoDB servers could deploy 8.0 mysqld; and finishing MyRocks server features enabled updating MyRocks installations.

Some areas were especially painful. Upstream 8.0 binlog event formats were incompatible with Facebook's custom 5.6 modifications, and error codes used by Facebook 5.6 features collided with new ones in upstream 8.0. The team had to patch the 5.6 server itself to be forward-compatible with 8.0. The whole effort ran for a couple of years: in the end, more than 2,300 patches were evaluated and 1,500 ported.

A staged rollout across replica sets

Facebook groups mysqld instances into replica sets — geographically distributed secondaries around a single primary that handles writes and replicates asynchronously. The migration plan moved each replica set through five stages, similar to the earlier UDB MyRocks migration:

  1. Add 8.0 secondaries to the 5.6 replica set via a logical copy with mysqldump; these do not serve application reads.
  2. Enable read traffic on the 8.0 secondaries.
  3. Allow the 8.0 instance to be promoted to primary.
  4. Disable the 5.6 instances for reads.
  5. Remove the 5.6 instances.

Each replica set could move through steps independently, staying on any step as long as needed, with rollback to a previous step possible. Replica sets were grouped into smaller batches, shepherded through each transition, and managed via configuration file changes. Sets with problems could be individually rolled back.

Row-based replication becomes mandatory

As part of the migration, Facebook standardized on row-based replication (RBR). Some 8.0 features require it, and it simplified the MyRocks port. Most replica sets were already on RBR, but those still on statement-based replication (SBR) were difficult to convert — they typically had tables lacking high-cardinality keys. Converting them meant adding primary keys to every table, work that had long been deprioritized.

RBR became a hard requirement for 8.0. After adding keys and switching over the last SBR replica set this year, the transition was complete. RBR also provided an alternative fix for an application issue that surfaced when some replica sets moved to 8.0 primaries.

Testing the Migration Path

Verifying the 8.0 server against Facebook's automation tooling and application workloads accounted for most of the migration effort. As the MySQL fleet expanded, so did the automation used to manage it. To confirm compatibility, engineers built a test environment using replica sets on virtual machines. Integration tests were written to canary each automation component against both 5.6 and 8.0, which surfaced several behavioral differences and bugs.

Validating the infrastructure against 8.0 uncovered a recurring theme: tools that parsed text output from the error log, mysqldump, or server SHOW commands were prone to breakage due to subtle changes in server output. Other issues included:

  1. Collation mismatches arising from 8.0's default utf8mb4_0900 collations. Since 5.6 schemas with utf8mb4_general_ci didn't explicitly specify collation, SHOW CREATE TABLE output generated on 5.6 could produce tables on 8.0 with different collations, breaking replication and schema verification.
  2. Changed error codes for certain replication failures required automation fixes.
  3. The 8.0 data dictionary eliminated table .frm files, which some automation relied on to detect schema changes.
  4. Automation needed updates to support 8.0's dynamic privileges.

Application Compatibility

To keep the application transition transparent, Facebook used its MySQL shadow testing framework—built for the MyRocks migration—to capture production traffic and replay it against 8.0 test instances. The framework logged errors returned by the 8.0 server, revealing several problems. Not all surfaced during testing, however; transaction deadlocks, for instance, were first encountered by applications during the live migration. Those applications were temporarily rolled back to 5.6 while solutions were researched.

Key application-facing incompatibilities included:

  • New reserved keywords such as groups and rank conflicted with popular column names and aliases in queries that didn't escape identifiers with backquotes. Applications using libraries that auto-escaped names were unaffected.
  • A few REGEXP incompatibilities existed between 5.6 and 8.0.
  • Some applications hit repeatable-read transaction deadlocks on INSERT … ON DUPLICATE KEY queries. A 5.6 bug corrected in 8.0 increased deadlock likelihood; lowering the isolation level resolved the issue, made possible by the earlier switch to row-based replication.
  • Custom 5.6 Document Store and JSON functions were incompatible with 8.0's versions. Document Store applications had to convert document types to text; for JSON functions, 5.6-compatible versions were added to the 8.0 server so applications could migrate to the new API later.

Performance testing also identified urgent issues:

  • New mutex contention around the ACL cache when many connections opened simultaneously.
  • Similar contention on binlog index access with many binlog files and high rotation rates.
  • Several temp-table queries returned unexpected errors or timed out.

Memory usage grew relative to 5.6, particularly on MyRocks instances, since InnoDB must be loaded in 8.0. The default performance_schema settings enabled all instruments, consuming significant memory. Limiting instruments and code changes to disable non-toggleable tables helped, but further reduction required modifying InnoDB internal data structures to bring memory down to acceptable levels.

Lessons From Skipping 5.7

The migration has taken several years. Many InnoDB replica sets now run entirely on 8.0, with most others at various stages of the path. With custom features ported, keeping pace with Oracle's minor releases has become comparatively easier.

Skipping 5.7 introduced complications. In-place upgrades were impossible, so logical dump and restore were used to build new servers. For very large instances, this process can take days on production and is fragile enough to be interrupted. Large instances required modifications to backup and restore systems to handle the rebuild.

Detecting API changes was also harder without 5.7's deprecation warnings. Additional shadow testing was needed to catch failures before migrating production workloads. Using client software that automatically escapes schema object names reduced compatibility issues.

Maintaining two major versions within a replica set proved difficult. Once a primary is promoted to 8.0, 5.6 instances should be disabled and removed quickly. Application users tend to adopt 8.0-only features, such as utf8mb4_0900 collations, which can break the replication stream between versions.

Despite the hurdles, benefits are already visible. Some applications opted for early conversion to use Document Store and improved datetime support. There are also ongoing considerations for supporting storage engine features like Instant DDL on MyRocks. The new version significantly expands what's possible with MySQL at Facebook.