Home > Database > Mysql Tutorial > body text

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

Linda Hamilton
Release: 2024-10-31 00:25:02
Original
675 people have browsed it

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!