MySQL is the most popular open-source relational database management system (RDBMS) for storing and managing structured data. MySQL table migration is always a PITA. As such, several database schema migration tools for MySQL have emerged, and they provide different capabilities and experience.
In this post, we are reviewing several open-source database schema migration tools for MySQL:
gh-ost
gh-ost is a triggerless online schema migration tool for MySQL. It's the successor of trigger-based online schema migration tool pt-online-schema-change¶. Its triggerless enables database schema changes with minimal downtime and impact on the database's availability and performance. It was developed by GitHub and introduced in 2016 as an open-source project.
gh-ost stands for GitHub’s Online Schema Transmogrifier/Transfigurator/Transformer/Thingy
Traditional online schema migration methods often involve long maintenance windows or require taking the database offline. gh-ost aims to address these limitations by providing a non-blocking and online schema change solution.
All existing online schema change tools operate similarly: they create a ghost table in the likeness of your original table, migrate that table while empty, slowly and incrementally copy data from your original table to the ghost table, meanwhile propagating ongoing changes (any INSERT, DELETE, UPDATE applied to your table) to the ghost table. Finally, at the right time, they replace your original table with the ghost table. gh-ost uses the same pattern. However it differs from all existing tools by not using triggers. Instead, it leverages MySQL binlog to capture table changes, and asynchronously applies them onto the ghost table.
Skeema
Skeema is a schema management system for MySQL and MariaDB. It enables management of table definitions and schema changes in a declarative fashion using pure SQL.
Skeema supports a pull-request-based workflow for schema change submission, review, and execution. This permits your team to manage schema changes the same way as you manage code changes.
Skeema has a companion SaaS tool, Skeema Cloud Linter, that provides automatic linting of schema change commits and pull requests.
GitHub uses both Skeema and gh-ost to automate its MySQL schema migration process.
Liquibase
Liquibase is arguably the most well-known product in this segment. It's CLI-based and written in Java.
In Liquibase, the schema migrations are organized as Changeset and Changelog. Probably due to its age and root in Java, the most commonly used form is XML (YAML and JSON support have been added later):
Plain SQL is also supported with proper annotation:
Flyway
Flyway is another open source project with a long history and a large customer base. Its core product includes the CLI and a Java library.
Flyway has been acquired by Redgate, while it still retains a developer-oriented approach. This can be felt from its website.
Liquibase and Flyway are closely matched in their offerings. The key distinction between them is their target audiences: Liquibase is oriented towards serving enterprise clients, whereas Flyway presents itself as more developer-friendly and approachable.
Bytebase
Bytebase is an all-in-one database DevOps and CI/CD solution. It's like GitHub/GitLab that provides a GUI workspace for developers and DBAs to collaborate on database changes. It's written in Go and TypeScript.
It also provides configurable SQL lint rules to detect SQL anti-patterns.
For MySQL, it also provides a GUI wrapper around gh-ost for online schema migration.
Apart from taking care of database change management, Bytebase also provides a SQL Editor with data access control, dynamic data masking and audit logs to control the query path.
Summary
| Name | Interface | Language | MySQL Only | Highlight |
|---|---|---|---|---|
| gh-ost | CLI | Go | Y | Triggerless online schema migration. |
| Skeema | CLI | Go | N (MySQL + MariaDB) | Declarative pure-SQL schema management. |
| Liquibase | CLI | Java | N | Long-standing. |
| Flyway | CLI | Java | N | Developer-friendly. |
| Bytebase | GUI | Go + TypeScript | N | All in one for team collaboration. |
the
