Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use the pt-osc tool in mysql. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

How to use it:

Pt-online-schema-change hackers * paired pictures * drop-old-table *-- drop-old-table [--sleep10]-- alter "add is_sign_1 int (11)"

The core of pt-online-schema-change 's implementation of changing the table structure online is as follows:

(note: three tables are involved in the follow-up process: the original table, tmp_table is the temporary table as the derived data of the original table, and old_table is the result table of the last rename original table)

1 、

CREATE TABLE `$db`.` $tmp_ tbl`LIKE` $db`.` $tbl` "

Create a tmp_table with the same table structure as the original table

2 、

Change the table structure to the desired table structure on tmp_table

3 、

Create three triggers on the original table, as follows:

(1) CREATETRIGGER mk_osc_del AFTER DELETE ON $table "" FOR EACH ROW "

"DELETE IGNORE FROM $new_table"WHERE$new_table.$chunk_column = OLD.$chunk_column"

(2) CREATETRIGGER mk_osc_ins AFTER INSERT ON $table "" FOR EACH ROW "

"REPLACEINTO $new_table ($columns)"

"VALUES ($new_values)"

(3) CREATETRIGGER mk_osc_upd AFTER UPDATE ON $table "" FOR EACH ROW "

"REPLACE INTO $new_table ($columns)"VALUES ($new_values)"

We can see that these three triggers correspond to INSERT, UPDATE and DELETE operations respectively.

(1)

Mk_osc_del,DELETE operation, we pay attention to DELETEIGNORE, when there is new data, we only operate, that is, in the subsequent import process, if the deleted data has not been imported into the new table, then we can not perform the operation on the new table, because in the subsequent import process, the data in the original table has been deleted and there is no data, so it will not be imported into the new table.

(2)

Mk_osc_ins,INSERT operation, all INSERT INTO are converted to REPLACEINTO, in order to ensure data consistency, when new data is inserted into the original table, if the trigger has not synchronized the original table data to the new table, this data has been imported into the new table, then we can use replaceinto to overwrite it, so that the data is consistent.

(3)

Mk_osc_upd

UPDATE operation, all UPDATE are also converted to REPLACEINTO, because when the row with the new data is not synchronized to the new table, the new table does not have this record, so we can only insert this piece of data. If it has been synchronized to the new table, then we can also do overwrite insertion, and all the data is consistent with the original table.

We can also see the essence of the above-mentioned replaceinto operations, and it is precisely because of these replaceinto that data consistency can be ensured.

4 、

Copy the original table data to the temporary table and use the following statement in the script

INSERT IGNORE INTO $to_table ($columns) "

"SELECT $columns FROM $from_table"WHERE ($chunks- > [$chunkno])", we can see that he imports the data into the new table in batches through some queries (basic primary key, unique key value). Before import, we can control the number of rows per import through the parameter-chunk-size, which has reduced the locking time to the original table, and during import, we can control it through the-sleep parameter. Sleep for a while after each chunk import and before the start of the next chunk import, the longer the sleep time, the smaller the impact on the disk IO

5 、

Rename original table to old table, in the temporary table Rename as the original table

"RENAME TABLE `$db`.` $tmp_ tbl`TO` $db`.` $tbl`". In fact, in the rename process, we still cause write read blocking, so strictly speaking, our OSC does not have any impact on the online environment, but because the rename operation is only a process of changing the name, it only changes some table information, which is basically instantaneous, so it has little impact on the online environment.

6 、

Clean up data that is no longer used in the above process, such as OLD tables

The above is the whole Percona OSC process. We can see that the essential part is the trigger, but there are still many details I haven't covered, such as foreign keys, recording binlog (binlog is not recorded by default), and so on. Due to the complexity of the environment, this tool still has many risks, such as the following problems or some problems that need to be avoided:

1 、

This tool is not an atomic operation, if it fails at some point, it will not only leave a lot of junk files of intermediate processes, but these files are difficult to clean up completely, and if these files exist, you cannot perform the OSC operation again.

2 、

In the execution, try to avoid the batch update of this table, lock the table, optimize the table operation, we can imagine, if there is a lock table, optimized table, then OSC can still be executed normally?

3 、

If there is a master-slave structure, then try to execute it first in the slave library, because if you execute to the slave library after the execution of the master database, can we imagine that there will be a problem if the fields of the master library are synchronized to the slave database?

4 、

It must be a single column primary key or a single unique key, so can we handle the amount of insert select * from sharding better?

5 、

Do not have foreign keys, although the script has been strictly tested, but whether there is still bug, it is also unknown, will the foreign keys of the table cause more problems?

6 、

Should we evaluate the disk capacity before implementing it? Because OSC uses more than twice the space of the table.

This is the end of this article on "how to use pt-osc tools in mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.