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

This article mainly introduces MySQL online ddl tool pt-online-schema-change how to use, the text is very detailed, has a certain reference value, interested friends must read!

MySQL ddl's current status

In the operation and maintenance of MySQL database, we always make ddl changes to the data table, modify and add fields or indexes. For MySQL only, ddl is obviously a function that all MySQL dba criticize, because in MySQL, when ddl is performed on the table, the table will be locked. When the table is relatively small, such as less than 1w, the impact on the front end is small. At that time, when tens of millions of tables are encountered, it will affect the front end application's write operation on the table.

The InnoDB engine currently performs DDL through the following steps:

1 Create an invisible temporary table (tmp_table) according to the table structure and DDL statement of the original table (original_table).

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

insert into tmp_table select * from original_table

4 rename original_table and tmp_table, and finally drop original_table

5 Release the write lock.

We can see that when InnoDB executes DDL, the original table can only be read but not written. Perconal introduced a tool for this purpose, pt-online-schema-change, which is characterized by the fact that the modification process does not cause read and write blocking.

How it works:

If the table has foreign keys, the tool does not execute unless a specific value is specified using--alter-foreign-keys-method.

1 Create an empty table structure similar to the table you want to alter.

2 Perform table structure modification, and then copy the data from the original table to the modified table structure.

3 Create triggers on the original table to copy data in the process of updating the original table to the new table.

Note: This tool will not work if triggers are defined in the table.

4 After copy is completed, replace the original table with rename table, and delete the original table by default.

Usage Description:

pt-online-schema-change [OPTIONS] DSN

options You can check help for yourself. DNS is the database and table you want to operate on. There are two parameters that need to be introduced:

--dry-run

This parameter does not create triggers, copy data, or replace original tables. Just create and change new tables.

--execute

This parameter works the same way as the previous description of how it works, creating triggers to ensure that the latest data changes affect the new table. Note: Without this parameter, the tool will exit after performing some checks.

dependent condition

1. The table operated must have primary key, otherwise the following error will be reported.

[root@rac1 bin]# ./ pt-online-schema-change -u root -h 10.250.7.50 -p yang --alter='add column vid int ' --execute D=houyi,t=ga

Cannot connect to D=houyi,h=127.0.0.1,p=..., u=root

Cannot chunk the original table `houyi`.` ga`: There is no good index and the table is oversized. at ./ pt-online-schema-change line 5353.

Test examples:

1 Add fields

[root@rac1 bin]# ./ pt-online-schema-change -u root -h 10.250.7.50 -p yang --alter='add column vid int ' --execute D=houyi,t=ga

Cannot connect to D=houyi,h=127.0.0.1,p=..., u=root

Operation, tries, wait:

copy_rows, 10, 0.25

create_triggers, 10, 1

drop_triggers, 10, 1

swap_tables, 10, 1

update_foreign_keys, 10, 1

Altering `houyi`.` ga`...

Creating new table...

Created new table houyi._ ga_new OK.

Altering new table...

Altered `houyi`.`_ ga_new` OK.

Creating triggers...

Created triggers OK.

Copying approximately 746279 rows...

Copied rows OK.

Swapping tables...

Swapped original and new tables OK.

Dropping old table...

Dropped old table `houyi`.`_ ga_old` OK.

Dropping triggers...

Dropped triggers OK.

Successfully altered `houyi`.` ga`.

2 Add index

[root@rac1 bin]# ./ pt-online-schema-change -u root -h 10.250.7.50 -p yang --alter='add key indx_vid(vid) ' --execute D=houyi,t=ga

3 Delete fields

[root@rac1 bin]# ./ pt-online-schema-change -u root -h 10.250.7.50 -p yang --alter='drop column vid ' --execute D=houyi,t=ga

The above is "MySQL online ddl tool pt-online-schema-change how to use" all the content of this article, thank you for reading! Hope to share the content to help everyone, more relevant knowledge, welcome to pay attention to the industry information channel!

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.