How to Create an Index on a Huge MySQL Production Table Without Locking the Table?

Create an Index on a Huge MySQL Production Table Without Table Locking

Problem:

You need to create an index on a massive MySQL table in a production environment without interrupting ongoing operations.

Conventional Wisdom (2015):

In MySQL versions prior to 5.6, creating an index on a MyISAM or InnoDB table blocks write operations.

Online Index Updates in MySQL 5.6 (2017):

MySQL 5.6 and later support online index updates, allowing you to add or drop indexes without blocking reads or writes.

Updating Schema Without Downtime:

If you cannot use MySQL 5.6 or later, you can use the following method to update your schema without downtime:

1. Circular Masters: Bring up a new master server and replicate writes from the existing master.
2. Perform Schema Update on New Master: Make the schema changes on the new master.
3. Swap Clients: Atomically migrate all clients from the old master to the new master.
4. Update Old Master: Repeat the same process with the old master to bring it up to date as a secondary master.

Percona's pt-online-schema-change Tool:

This tool provides an alternative method for altering table schema without blocking operations. It creates a new table with the desired structure, makes the schema changes, and gradually copies data from the original table before replacing it.

Amazon RDS:

Amazon RDS simplifies the process by allowing you to create read replicas, make schema changes on a replica, and then promote the replica to become the new master. This approach minimizes downtime and simplifies the cut-over process.

The above is the detailed content of How to Create an Index on a Huge MySQL Production Table Without Locking the Table?. For more information, please follow other related articles on the PHP Chinese website!