All Products
Search

This Product

    :Use the Percona Toolkit

    Document Center

    :Use the Percona Toolkit

    Last Updated:Dec 24, 2020

    Disclaimer: This article may contain information about third-party products. Such information is for reference only. Alibaba Cloud does not make any guarantee, express or implied, with respect to the performance and reliability of third-party products, as well as potential impacts of operations on the products.

     

    Introduction

    The Percona Toolkit includes a variety of tools for MySQL database management. This topic describes how to use common pt-online-schema-change and pt-archiver in combination with apsaradb RDS for MySQL.

    Note:

    • To use this option with apsaradb RDS for MySQL instances, both pt-online-schema-change and pt-archiver tools must specify the -- no-version-check option.
    • The sample uses Percona Toolkit 2.2.17 version testing.
    • The examples in this document are only used as examples and do not assume any liability for issues arising therefrom. Refer to the Percona Toolkit documentation for specific operation manuals.

     

    Background

    Alibaba Cloud reminds you that:

    • Before you perform operations that may cause risks, such as modifying instance configurations or data, we recommend that you check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
    • You can modify the configurations and data of instances including but not limited to Elastic Compute Service (ECS) and Relational Database Service (RDS) instances. Before the modification, we recommend that you create snapshots or enable RDS log backup.
    • If you have authorized or submitted security information such as the logon account and password in the Alibaba Cloud Management console, we recommend that you modify such information in a timely manner.

     

    pt-online-schema-change

    pt-online-schema-change allows you to modify the schema of a table online. You can use this function together with RDS MySQL 5.5 to prevent applications from accessing table data from being blocked. RDS MySQL 5.6 support online-ddl, you can perform operations during off-peak hours. You can also use this feature in combination with pt-online-schema-change.

    • You may encounter the RDS MySQL 5.5 when modifying the table schema, regardless of whether you are using pt-online-schema-change 5.6 or waiting for table metadata lock. For more information about how to generate and process the Metadata lock of apsaradb RDS for MySQL instances, see create and process Metadata lock in apsaradb for RDS.
    • You can maintain tables and data during off-peak hours.

     

    Sample table

    CREATE TABLE `x` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `password` varchar(10) DEFAULT NULL,   `recommend_level` double(5,0) DEFAULT NULL,   `name` varchar(30) DEFAULT '101' COMMENT 'change',   PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

     

    Add Column

    1. Log on to the server and run the following command to add the field: 
      pt-online-schema-change --no-version-check --execute --alter "add column c1 int" h=xxx.mysql.rds.aliyuncs.com,P=3306,u=jacky,p=xxx,D=jacky,t=x
      Note:
      • h=xxx.mysql.rds.aliyuncs.com: the IP address of the RDS instance.
      • P=3306: the port number of the RDS instance.
      • u=jacky
      • p=xxx: The password of the RDS instance.
      • D=jacky: the address of the database in the apsaradb for RDS instance.
      • t=x: the name of the RDS instance table.

      The following command output is returned.

    2. Log in to the database and run the following SQL statement to confirm that it was added successfully. 
      show create table x \G
      The following command output is returned.

     

    Delete a field

    1. Log on to the server and run the following command to delete fields: 
      pt-online-schema-change --no-version-check --execute  --alter "drop column c1" h=xxx.mysql.rds.aliyuncs.com,P=3306,u=jacky,p=xxx,D=jacky,t=x
    2. Log on to the database and run the following SQL statement to confirm that the field has been deleted successfully: 
      show create table x \G
      The following command output is returned.

     

    Change the field type

    1. Log on to the server and run the following command to modify the field type: 
      pt-online-schema-change --no-version-check --execute --alter "modify column c1 bigint unsigned" h=xxx.mysql.rds.aliyuncs.com,P=3306,u=jacky,p=xxx,D=jacky,t=x
    2. Log on to the database and run the following SQL statement to confirm that the field types are modified successfully: 
      show create table x \G
      The following command output is returned.

     

    Add index

    1. Log on to the server and run the following command to add an index: 
      pt-online-schema-change --no-version-check --execute --alter "add key idx_c1 (c1)" h=xxx.mysql.rds.aliyuncs.com,P=3306,u=jacky,p=xxx,D=jacky,t=x
    2. Log on to the database and run the following SQL statement to confirm that the index has been added successfully: 
      show create table x \G
      The following command output is returned.

     

    Delete a clustered index

    1. Log on to the server and run the following command to delete the index: 
      pt-online-schema-change --no-version-check --execute --alter "drop key idx_c1" h=xxx.mysql.rds.aliyuncs.com,P=3306,u=jacky,p=xxx,D=jacky,t=x
    2. Log on to the database and run the following SQL statement to confirm that the index has been deleted: 
      show create table x \G
      The following command output is returned.

     

    pt-archiver

    pt-archiver is an archiving tool provided by Percona. You can use it to archive records from a large table to another table or file.

     

    Sample table

    CREATE TABLE 'my_tab ' ( 'id' bigint(11) NOT NULL AUTO_INCREMENT, ' areaid' varchar(50) DEFAULT NULL, 'area' varchar(60) DEFAULT NULL COMMENT 'Chinese annotator test', 'fina' varchar(12) DEFAULT NULL, PRIMARY KEY ('id'), KEY 'idx_area' ('area') ) ENGINE=InnoDB AUTO_INCREMENT=3162 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=8;

     

    Archive to operating system files

    1. Log on to the database and run the following SQL statement to check the number of rows in the source table: 
      select count(*) from my_tab;
      The following command output is returned.

    2. Log on to the server and run the following command to archive the file to the operating system: 
      pt-archiver --source h=xxx.mysql.rds.aliyuncs.com,P=3306,u=jacky,p=xxx,D=jacky,t=my_tab --charset=utf8 --file '/var/tmp/%Y-%m-%d-%D.%t' --where "id > 3008" --limit 1000 --commit-each --no-version-check
      Note:
      • h=xxx.mysql.rds.aliyuncs.com: the IP address of the RDS instance.
      • P=3306: the port number of the RDS instance.
      • u=jacky
      • p=xxx: The password of the RDS instance.
      • D=jacky: the address of the database in the apsaradb for RDS instance.
      • t=my_tab: The table name of the RDS instance.
      • -- source: specify the data source to be archived.
      • -- charset=utf8: specifies the character set to be used. It must be consistent with the character set in the table. Otherwise, specify the -- no-check-charset parameter.
      • -- file: specifies the name of the target operating system file.
      • -- where "id > 3008": specifies the where clause to filter data for archiving.
      • -- limit 1000: specifies the number of data rows that are read and archived by each statement. The default value is 1.
      • -- commit-each: commit each time after data is obtained and archived.
      • -- no-version-check: this parameter is required for apsaradb RDS for MySQL without version check.

    3. After archiving, log on to the database and run the following SQL statement to confirm that the number of data rows has been reduced by 150 rows. 
      select count(*) from my_tab;
      The following command output is returned.

    4. Log on to the server and run the following command. The archived operating system file contains 150 lines of data. 
      wc -l /var/tmp/xxx-jacky.my_tab
      The following command output is returned.

     

    Archive to another table

    You can archive data to tables in different databases under the same instance or to tables under different instances under the same instance. Destination table must exist before archiving.

     

    Archive a table to a database different from the same instance
    1. Log on to the server and run the following command to archive tables in different databases of the same instance: 
      pt-archiver --source h=rds01.mysql.rds.aliyuncs.com,P=3306,u=jacky,p=xxx,D=jacky,t=my_tab --charset=utf8 --dest h=rds01.mysql.rds.aliyuncs.com,P=3306,u=jacky,p=xxx,D=my_db,t=my_tab --where "id > 2000" --limit 1000 --commit-each --no-version-check
      Note: -- dest indicates the name of the destination table to be archived.
    2. Log on to the database and run the following SQL statement. The result shows 1008 rows are added to the target table. 
      select count(*) from my_tab;
      The following command output is returned.

    3. Log on to the database and run the following SQL statement. The Source table contains 1008 rows of data but 2,000 rows of data. 
      select count(*) from jacky.my_tab;
      The following command output is returned.

     

    Archive tables to different instances
    1. Log on to the server and run the following command to archive tables on different instances: 
      pt-archiver --source h=rds01.mysql.rds.aliyuncs.com,P=3306,u=jacky,p=xxx,D=jacky,t=my_tab --charset=utf8 --dest h=rds02.mysql.rds.aliyuncs.com,P=3306,u=jacky,p=xxx,D=jacky,t=my_tab --where "id > 500" --limit 1000 --commit-each --no-version-check
    2. Log on to the database and run the following SQL statement. The result shows that 1,500 rows are added to the target table. 
      select count(*) from my_tab;
      The following command output is returned.

    3. After you run the following SQL statement, the Source table contains only 1,500 rows, and the other 500 rows. 
      select count(*) from jacky.my_tab;
      The following command output is returned.

     

    Accelerate archiving through bulk insert

    When a large amount of data needs to be archived, for example, a file for the first time, you can try bulk insert to accelerate the archiving process. Run the following command:

    pt-archiver --source h=rds01.mysql.rds.aliyuncs.com,P=3306,u=jacky,p=xxx,D=rd_test,t=large_tab_04 --charset=utf8 --dest h=rds02.mysql.rds.aliyuncs.com,P=3306,u=jacky,p=xxx,D=jacky,t=large_tab_04 --where "1=1"  --limit 5000 --commit-each --bulk-insert --no-version-check --statistics

    Note:

    • -- bulk-insert: Inserts data in batches. When -- bulk-delete and -- commit-each are automatically enabled, the number of rows inserted each time is specified by the -- limit option.
    • -- statistics: displays pt-archiver statistics of this operation.

     

    Application scope

    • ApsaraDB RDS for MySQL