PT online schema change tool tutorial (modify large table structure online)

Time:2020-1-18

 

Installation and use of Pt online schema change tool in percona Toolkit

Introduction to PT online schema change

Usage scenario: modifying large table structure online

In the maintenance of database, it always involves the modification of table structure in the production environment. The modification of some small tables has little impact, while the modification of large tables often affects the normal operation of the business. For example, the amount of table data exceeds 500W, 1000W, or even 100 million hours

Possible impact of online modification of large tables
(1) The execution time of table structure of online modification of large tables is often unpredictable, and generally takes a long time
(2) Because modifying the table structure is a table level lock, the table write operation is affected when modifying the table structure
(3) If the table structure is modified for a long time and fails to be modified in the middle of the process, the table structure will be restored after the failure because it is a transaction. In this process, the tables are locked and cannot be written
(4) Modifying the large table structure is easy to cause performance consumption of database CPU, IO and so on, resulting in performance degradation of MySQL server
(5) Online modification of large table structure is easy to cause master-slave delay, thus affecting service reading

Tool introduction
PT online schema change is a tool developed by percona company. This function can be found in percona toolkit package. It can modify table structure online

Principle:

Before using, make sure that the table must have a primary key or unique index!!

(1) first, it will create a table that looks exactly the same. The name of the table is usually _new suffix.
(2) Then change the field in the new table
(3) Then add three triggers, delete / update / insert, to the original table to execute the statements in the new table
(4) Finally, copy the data of the original table to the new table, and then replace the original table

There are two problems in this process:
1. trigger
Because the whole process is online, in order to update the original table to the new table at the same time in the process of table modification, a corresponding trigger will be created. Whenever the operation of adding, deleting and modifying the original table occurs, the corresponding operation of the new table will be triggered. Therefore, there can be no other triggers on the original table, that is, if there are triggers on the original table, the OSC will strike.

2. foreign keys
Foreign keys make the operation of changing tables more complicated. If there is a foreign key on the original table, the operation of automatically renaming the original table and the new table cannot proceed smoothly. The foreign key must be updated to the new table after the data copy is completed. The tool has two methods to support this operation, specifically using the parameter (- – alter foreign keys method).

–alter-foreign-keys-method
The correct table must be linked continuously before and after the foreign key changes the table. When the tool rename the original table and replace the original table with a new table, the foreign key must be updated to the new table correctly, and the foreign key in the original table will no longer take effect

Foreign keys complicate the operation of the tool and introduce additional risks. When a foreign key references the table, the atomic operation of renaming the original table and the new table will not work properly. After the table structure is modified, the tool must modify the foreign key to point to the new table. The tool supports two ways to implement this operation. For details, please refer to the document – alter foreign keys method. Foreign keys can also cause some side effects. The final table will have the same foreign key and index as the original table (unless you specify them separately in the alter statement), but the object name may be slightly modified to avoid the object name conflict in MySQL and InnoDB.

For security reasons, the tool does not actually modify tables unless you specify the — execute option, which is not enabled by default. The tool supports a variety of other measures to prevent unexpected loads or other problems, including automatic detection of slave nodes, connecting to them, and using the following security checks:
(1) In most cases, the tool will not operate unless there is a primary key or unique index in the table. See — alter option for details.
(2) If a replication filter is detected, the tool rejects the operation. For details, see the check replication filters option.
(3) The tool stops the data copy operation. If it observes that the master-slave delay is greater than the value of the — Max lag option, the default is 1s.
(4) The tool stops or relinquishes operations if it detects too much load on the server. See the — max load and — critical load options.
(5) The tool will set InnoDB ﹐ lock ﹐ wait ﹐ timeout = 1 and (for MySQL 5.5 and later) lock ﹐ wait ﹐ timeout = 60, so it will be more likely to be a victim of lock competition and less likely to break other transactions. These values can be modified by specifying — set vars.
(6) The tool will refuse to modify the table, if the foreign key constraint references it, unless you specify — alter foreign keys method.

Benefits:

Reduce the risk of master-slave delay

It can limit the speed and resources to avoid excessive MySQL load during operation

Recommendation:

Do it in the low peak period of business to minimize the impact

percona-toolkitinstall

1. Go to the official website to download the corresponding version. Download address:https://www.percona.com/downloads/percona-toolkit/LATEST/

wget https://www.percona.com/downloads/percona-toolkit/3.1/binary/redhat/7/x86_64/percona-toolkit-3.1-1.el7.x86_64.rpm

2. Installation dependency

yum -y install perl-DBI perl-DBD-MySQL perl-Digest-MD5 perl-IO-Socket-SSL perl-TermReadKey

3. installation

rpm -ivh percona-toolkit-3.1-1.el7.x86_64.rpm

Or install directly using yum
Using Yum will automatically install dependency packages, which is relatively simple

yum install -y percona-toolkit-3.0.12-1.el6.x86_64.rpm

Verification

pt-online-schema-change --help

 

PT online schema change use

Modify table structure without blocking read and write operations
. / bin / Pt online schema change — help to view the use of parameters

The common DNS options are: - user = user name to connect to MySQL
--Password = password to connect to MySQL
--Host = address to connect to MySQL
P = 3306 port number of MySQL connection
D = name of the database to which MySQL is connected
T = table name of MySQL connection
--Alter statement to modify table structure
--Execute modify table structure
--Charset = utf8 uses utf8 encoding to avoid Chinese scrambling
--No version check does not check and update the percona toolkit version or MySQL version.

Case study:

The SQL statement for adding table fields is:ALTER TABLE test.liu_test ADD COLUMN liu int(10) DEFAULT NULL;

pt-online-schema-change   --user=root --password=xxx  --host=172.16.xx.xx  P=3306,D=test,t=liu_test  --charset=utf8 --no-version-check --execute --alter "ADD COLUMN  liu int(10) DEFAULT NULL"

Before using, make sure that the table must have a primary key or unique index!!

 

 

 

Refer to the official website: https://www.percona.com/doc/percona-toolkit/latest/pt-online-schema-change.html

Recommended Today

CentOS makes document server based on nginx

CentOS makes document server based on nginx Basic environment for installation yum install gcc-c++ yum install -y pcre pcre-devel yum install -y zlib zlib-devel yum install -y openssl openssl-devel Find the latest stable version of nginx on the official website https://nginx.org/en/download.html Stable version: https://nginx.org/download/nginx-1.18.0.tar.gz cd /home wget -c https://nginx.org/download/nginx-1.18.0.tar.gz Decompress compressed package data tar -zxvf […]