Shulou(Shulou.com)06/01 Report--

As we all know, the most expensive operation in a database is the DDL operation, because MySQL will block any read and write operations during table modification, and basically your business is paralyzed. For a huge project that may take several hours to complete, it is a nightmare and intolerable operation.

Percona has developed a series of tools Percona Toolkit packages, including a tool pt-online-schema-change that can perform DDL operations online without blocking read and write operations and affecting business programs. Of course, MySQL 5.6 also enhances some online DDL features. The following is mainly about pt-online-schema-change changing the table structure online.

Pt-online-schema-change principle

1. If there is a foreign key, detect the table related to the foreign key according to the value of the alter-foreign-keys-method parameter, and do the corresponding setting processing.

2. Create a new table with a modified data table for importing data from the source table to the new table.

3. Create a trigger to record the operations that continue to modify the source data table after copying the data, and to perform these operations after the end of the data copy to ensure that the data will not be lost.

4. Copy the data from the source data table to the new table.

5. Modify the child table related to the foreign key, and modify the child table associated with the foreign key according to the modified data.

6. The name source data table is the old table, the new table rename is the source table name, and the old table is deleted.

7. Delete trigger.

Download the latest version of the rpm package on the official website, and then install it locally and automatically resolve dependencies and other problems. It is best to install it this way. Wget https://www.percona.com/downloads/percona-toolkit/3.0.4/binary/redhat/7/x86_64/percona-toolkit-3.0.4-1.el7.x86_64.rpmyum install percona-toolkit-3.0.4-1.el7.x86_64.rpm execution, such as adding a field to the BorrowOrders table

Time pt-online-schema-change-- host=10.153.1.200-- port=3306-- user=prod-- password= "yourpasswd"-- alter= "ADD COLUMN internalCode VARCHAR (32) DEFAULT'0'"-- execute Downs trademarks tasking BorrowOrders-- set-vars innodb_lock_wait_timeout=50-- no-check-replication-filters-- recursion-method=none

In addition, you can monitor whether there are redundant indexes.

Time pt-duplicate-key-checker-host=10.153.1.200-port=3306-user=prod-password= "yourpasswd", D=trade-tables=Persons

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.