Every growing application eventually faces the same challenge: your database schema needs to change, but your users can’t afford the downtime. Whether you’re adding a column, creating an index, or restructuring a table with billions of rows, schema changes can lock tables, strain server resources, and disrupt live traffic if handled carelessly.
The good news is that MySQL offers several ways to make these changes while keeping your database online and responsive. This guide breaks down the main causes of schema-change downtime and walks through the tools and techniques — from built-in online DDL to third-party utilities like Percona Toolkit and gh-ost — that let you evolve your database safely without taking your application offline.
If we dig into ancient Greece, many of us will remember a quote or two from our favorite philosophers. According to Heraclitus, “the only constant in life is change” – and it’s hard not to agree with that.
Change is a constant everywhere, including in your databases. Many applications and websites deal with all kinds of data that flows in, out, and is modified from time to time, too. And that leads us on to downtime – the unfortunate reality of some of these modifications, such as schema changes.
Schema changes, especially those performed on databases powering applications dealing with billions of rows, require a certain amount of downtime. This downtime can be a problem for both you and the users using the application. However, there are things you can do to keep this downtime to a minimum.
In this guide, I’ll explain everything you need to know about schema changes in MySQL and how exactly to reduce downtime during these schema changes.
What are database schema changes?
Database schema changes are any changes – or modifications – that happen to the data inside of your database.
There are a few ways to perform schema changes inside your database, and most of them can be done online. This is because not all use cases are based on big data and, if they are, most of us aren‘t modifying all data in our database at the same time!
Additionally, use cases that are based on big data can often be done once the user activity within our applications dies down – such as in the evening and night, depending on your application.
*Changes to database schemas being done “online” refers to changes to data that can be completed without downtime.
Regardless, there will be instances where your database may be unavailable for an extended period of time during a schema change. This is because:
- Index creation and column creation can be a lengthy process, especially if your data is also partitioned and being searched through at the same time.
- Users may choose to actively peruse your application, putting strain on your database as a result.
- Your database may need to be shut down for a while to perform maintenance while you import data or perform schema changes.
MySQL schema comparison for faster, safer deployments
How to make database schema changes without downtime in MySQL
To make changes to a database schema without downtime, it’s important to understand what leads to downtime or lag in the first place.
What causes downtime or lag when making database schema changes?
- Many changes in one go – or big, destructive ones, such as dropping or renaming columns).
- Tables being locked when operations are being performed.
- Long-running transactions that may block or delay one another.
- Operations that necessitate the rewrite or copying of data (e.g.
ALTER TABLEon big columns). - High I/O (input/output) and/or CPU usage prior to operations being performed. In other words, your application being under heavy load once you decide to start operations.
- Application incompatibility, as deploying code that expects a new schema to exist (or vice versa) can cause service disruption.
So, it’s crucial to try and eliminate as many of these as possible before making changes to your database schema.
How to make database schema changes without downtime in MySQL using online DDL
To use online DDL (data definition language) in MySQL, you need to make schema changes while the schema (table/database and tables within) remain available for reads and/or writes as applicable.
Here’s a handy guide on how exactly to achieve that:
| Method for Online DDL | Explanation | Usage |
Running ALTER TABLE using ALGORITHM and, where applicable, LOCK. | Specifying INSTANT as the algorithm will force the database to not make a table copy and complete the action almost instantly – hence the name.INPLACE, meanwhile, will modify the table without creating a copy allowing for reads/writes. | ALTER TABLE `table_name`ADD COLUMN `column_name` TIMESTAMP NULL,ALGORITHM=INSTANT|INPLACE[,LOCK=NONE]; |
| Online DDL when migrating | Add a new schema, deploy compatible code, migrate data, remove the old schema. | See explanation. |
| Online DDL when deploying | Prepare a new database environment/schema beforehand, then switch the traffic to it when necessary. | See explanation. |
| Rolling migrations | Aim to gradually and softly perform updates to your database while maintaining compatibility. | See explanation. |
Important – keep this in mind when performing operations using the ALGORITHM capability in MySQL. If you specify that you want to perform an operation with a specified lock level and/or algorithm and MySQL cannot perform it, the database will return an error.
How to use Percona Toolkit or gh-ost to make database schema changes without downtime in MySQL
Tools are available to perform database schema changes with – Percona Toolkit and GitHub-developed gh-ost being two of these.
Here’s how to perform database schema changes without downtime using either of these tools:
| Tool | Explanation | Usage |
Percona Toolkit (pt-online-schema-change) | The idea of pt-online-schema-change is to alter the database schema without blocking any reads or writes. | pt-online-schema-change — alter “ADD COLUMN x INT” D=database,t=table |
| gh-ost (a tool developed by GitHub) | gh-ost is a tool for online schema changes developed by GitHub. | gh-ost--host=localhost--database=dbname--table=tablename--alter=”ADD COLUMN x INT”--execute |
Replace ADD COLUMN x INT with your statement that modifies the database schema.
In summary: which MySQL database schema change method should I use?
Now that you know what schema changes are and how to perform them without downtime in MySQL, you’re probably wondering which schema change method to use.
To summarize the options we’ve covered in this guide:
| Method | When to Choose? |
Running ALTER TABLE using ALGORITHM and, where applicable, LOCK. | Use when the online DDL is supported by MySQL (i.e. when you’re using MySQL >=8.0 or MariaDB >=10.4.) |
| Online DDL when migrating/deploying. | You find yourself deploying a new version of your website that requires a backward-compatible schema change. |
| Rolling migrations | Your website hangs on multiple instances (e.g. Kubernetes and cloud deployments.) |
Percona Toolkit (pt-online-schema-change) | You cannot use online DDL without locking and/or you have excessively large tables. |
| gh-ost | You are running large production-level databases using row-based binary logging. |
As you now know, there are multiple ways to make changes to your database schema without downtime in MySQL. Ultimately, the exact method you choose depends on you and your use case.
Move fast. Govern at scale.
FAQs: How to make database schema changes without downtime in MySQL
1. What is a database schema change?
It’s any modification to your database’s structure or data — such as adding, removing, or altering columns, tables, or indexes.
2. Can MySQL schema changes really be done with zero downtime?
In many cases, yes. Using online DDL (data definition language) with the right ALGORITHM and LOCK settings, most changes can run while the table stays available for reads and writes. Very large or complex changes may still need extra tooling to avoid disruption, however.
3. What usually causes downtime during a schema change?
Common culprits include table locks, long-running transactions, large-scale data rewrites, high server load at the time of the change, and application code that’s incompatible with the new schema.
4. When should I use Percona Toolkit instead of online DDL?
Reach for pt-online-schema-change when your MySQL version doesn’t support lock-free online DDL for your specific change, or when you’re working with especially large tables.
5. What's the difference between gh-ost and Percona Toolkit?
Both avoid locking tables during changes, but gh-ost is built around row-based binary logging and is designed for large-scale production environments, while Percona Toolkit works more broadly across setups.
6. Do rolling migrations work for cloud or Kubernetes deployments?
Yes. Rolling migrations are especially useful when your application runs across multiple instances, letting you update gradually while maintaining backward compatibility.
This document contains proprietary information and is protected by copyright law.
Copyright © 2026 Red Gate Software Limited. All rights reserved
Load comments