MySQL uses Pt-online-change-schema tool to modify level 160 million data sheet structure online

Pick to: This paper describes the problemof MySQL DDL , thept-online-schema-change of the work principle , and the actual use of The pt-online-schema-change tool modifies the 160 million-level data table structure on-line in the production environment.

in a software life cycle, we all know that the design of the early table structure is very important. , because when the table data volume comes up, the table structure changes the risk is larger, and the time to operate is also relatively long.

In the author's participation in the project, has encountered such a problem, first went up to look at the table information, there are about 200 million of the data, and every minute to write 40,000 records concurrently, and because the table has a field pre-design too short, resulting in writing to the database, the value of this field has been garbled. Because the table is used in a production environment, it affects the business, the field length needs to be modified in a timely manner, and the service cannot be stopped when the table structure is modified. So how do we solve this problem?

One MySQL DDL the status quo of the problem

Started to think, reduce the amount of data in this table again DDL, back up the table a week before the data to a temporary table, and then delete the table a week before the data.

While in MySQL in the DDL table, will lock table, when the table is smaller, such as less than 1w records, the operation time is short, the impact on the front-end is small, at that time encountered tens of millions or even hundreds of millions of levels of table (retain a week of data volume 160 million), will affect the front-end application to the table write operations

Because the InnoDB engine is currently using the following steps to perform DDL:

1 Create a new non-visible temporary table (tmp_table) According to the table structure and DDL statements of the original table (original_table)

2 Add write lock on the original table to block all update operations (INSERT, delete, UPDATE, etc.)

3 Execute INSERT INTO tmp_table select * from original_table

4 Rename original_table and tmp_table, final drop original_table

5 Release the Write lock.

We can see that when InnoDB executes the DDL, the original table is read-only and cannot be written. To this end, Perconal introduces a tool pt-online-schema-change, which is characterized by no read-write blocking during the modification process.

Second, Pt-online-schema-change Introduction

"Introduction to Tools"

Pt-osc imitate MySQL internal change table way to change the table, but the whole process of the table is through the copy of the original table, that is, in the process of changing the table is not locked the original table, does not affect the read and write operation of the table.

First, the OSC creates a new table with the same data as the original table and modifies the table structure as required, and then copies the data from the original table to the new table in chunk size, and when the copy is complete, the original table and the new table name are modified automatically and the original table is deleted by default

"Tool installation and use"

See the following article below

Linux under Percona-toolkit installation and use of the toolkit (ultra-detailed version)

"How It Works"

1 Create two empty tables with the same table structure that you want to perform the alter operation.

Description: T_ad_req_log is the original table;

_t_ad_req_log_ol is the old table, this table is used to restore the original table structure when you fail to perform;

_t_ad_req_log_new is a new table, this is the table to be modified this time.

2 Perform table structure modification, then from data in the original table to copy to table structure modified table (i.e. _t_ad_req_log_new)

3 When you create a trigger on the original table, the update operation in the original table is updated to the new table during the copy data process.

Note: If a trigger is already defined in the table, the tool will not work.

4 after the copy is completed, the original table is replaced by the rename table, which is deleted by default.

The modified command is as follows:

/usr/local/bin/pt-online-schema-change--user= user name--password= password--host=127.0.0.1--port= port number--charset=utf8-- Nodrop-old-table--alter= "Modify  media_code varchar (+) DEFAULT NULL COMMENT ' current video encoding '" D=ad_api,t=t_ad_req_log-- Exec

Parameter description:

--user= User name     Specify user name--password= user name     Specify user password--port= port number     Specify port number--charset=utf8   Specify character encoding--alter= followed by what needs to be modified , as shown above, is to modify the AD_API Database T_ad_req_log table media_code field length is 64 bits

Let's look at a complete picture below:

Note: If you have questions about installing and using the Percona-toolkit tool, check out these two articles first.

Installation and use of the Linux Percona-toolkit Toolkit (ultra-detailed version)

Pt-online-schema-change interpretation

MySQL uses Pt-online-change-schema tool to modify level 160 million data sheet structure online