{"id":112083,"date":"2026-10-12T12:00:00","date_gmt":"2026-10-12T12:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=112083"},"modified":"2026-09-29T15:11:36","modified_gmt":"2026-09-29T15:11:36","slug":"how-to-make-database-schema-changes-without-downtime-in-mysql-complete-guide","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/mysql\/how-to-make-database-schema-changes-without-downtime-in-mysql-complete-guide\/","title":{"rendered":"How to make database schema changes without downtime in MySQL: complete guide"},"content":{"rendered":"\n

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.<\/strong><\/p>\n\n\n\n

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 \u2014 from built-in online DDL to third-party utilities like Percona Toolkit and gh-ost \u2014 that let you evolve your database safely without<\/em> taking your application offline.<\/strong><\/p>\n\n\n\n

If we dig into ancient Greece, many of us will remember a quote or two from our favorite philosophers. According to Heraclitus<\/a>, \u201cthe only constant in life is change”<\/em> – and it’s hard not to agree with that.<\/p>\n\n\n\n

Change is a constant everywhere, including in your databases<\/a>. 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<\/a>.<\/p>\n\n\n\n

Schema changes, especially those performed on databases powering applications dealing with billions<\/em> of rows, require a certain amount of downtime. This downtime can be a problem for both you and<\/em> the users using the application. However, there are things you can do to keep this downtime to a minimum.<\/p>\n\n\n\n

In this guide, I’ll explain everything you need to know about schema changes in MySQL<\/a> and how exactly to reduce downtime during these schema changes.<\/strong><\/p>\n\n\n\n

What are database schema changes?<\/h2>\n\n\n\n

Database schema changes are any changes – or modifications – that happen to the data inside of your database.<\/strong><\/p>\n\n\n\n

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<\/a> and, if they are, most of us aren\u2018t modifying all<\/em> data in our database<\/a> at the same time!<\/p>\n\n\n\n

Additionally, use cases that are<\/em> 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.<\/p>\n\n\n\n

*Changes to database schemas being done \u201conline\u201d refers to changes to data that can be completed without downtime.<\/em><\/p>\n\n\n\n

Regardless, there will be instances where your database may be unavailable for an extended period of time during a schema change. This is because:<\/p>\n\n\n

\n