How to modify the table structure of MySQL database online

This article brings you relevant knowledge about mysql. MySQL has a lock called MDL metadata lock. When the table is modified, this lock will be automatically added to the table. Locks, that is, you do not need to use them explicitly. Here is an introduction to how to modify the table structure of a MySQL database online. I hope it will be helpful to everyone.

Recommended learning: mysql video tutorial

1. MDL metadata lock

Before modifying the table structure , let’s first look at possible problems.

1. What is MDL lock

MySQL has a lock called MDL metadata lock. When the table is modified, this lock will be automatically added to the table, that is, it will not You need to use it explicitly yourself.

• When adding, deleting, modifying, and querying the table, MDL read locks are added.
• When changes are made to the table structure, MDL write locks are added.

Reading and reading are not mutually exclusive, reading and writing, writing and writing are mutually exclusive, so

• When a thread performs additions, deletions, and changes to the table, Will block other threads' requests to modify the table structure
• When a thread modifies the table structure, it will block other threads' requests to add, delete, modify and check the table

2. MDL lock problem

And once the MDL is locked, it will only be released after the currently requested transaction is submitted. If it is a long transaction, or the amount of online data is large, the table structure will be modified by default. Without the MDL write lock, it will be time-consuming and block other subsequent requests.

Imagine a scenario where A(select), B(alter), C(select), D(select)...are requests for the same MySQL table in sequence. These requests will Form a queue.
When A (select) acquires the MDL read lock of the table, it will block B (alter), because B needs to add the MDL write lock. After B is blocked, it will cause other people in the waiting queue to All requests are blocked, eventually causing MySQL's available connections to be exhausted, request timeouts and other problems.

2. How to modify the MySQL table structure online

In view of the above MDL lock, we know that altering the structure of the table will block other normal requests, so the modification operation should be placed in a non-stop It is done during peak business hours, usually between 2 and 4 in the morning.

Specific steps:

• Add a read-write lock to the table, making the table read-only at this time,
• Copy the physical structure of the original table
• Modify the physical structure of the new table, including adding new fields or modifying other table structures
• Import the table structure into the new table, data synchronization is completed, lock the intermediate table, and delete the original table
• Invert the new table rename is the original table name
• Release the lock

The problem with the above solution is that when the amount of data is large, it takes time to import all the data. During this process, the service is inaccessible .

Improvement:

Create a new table A_new, which has several more fields than the original table. Subscribe to the original table A through data subscription. The data in the online table A is synchronized to the newly created table A_new. This process will continue, and table A can be added, deleted, modified, and checked during this process. There will always be a moment when the data of the two tables are completely synchronized. Yes, there is no difference in the data. At this time, the original table name A is modified, and the new table A_new is modified to the original table A. This operation is a short operation and can be completed instantly without much impact.
Advantages and disadvantages:

• The advantage is that the synchronization process will not affect the normal business of the original.
• The disadvantage is that extra storage space is required to store the new table during the process. After the rename is completed, the old table can be deleted.

Recommended learning: mysql video tutorial

The above is the detailed content of How to modify the table structure of MySQL database online. For more information, please follow other related articles on the PHP Chinese website!