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

This article will explain in detail how to use pt-online-schema-change to add fields to large tables in MySQL 5.7. the editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Business requirements:

Add fields to a table with 3000W rows on a line:

In a normal way, it is recommended to use a small table:

ALTER TABLE `idempotent`

ADD COLUMN `orderid` VARCHAR (20) NULL DEFAULT NULL COMMENT 'business document number' AFTER `isdelete`

ALTER TABLE ship_type ADD printer_name VARCHAR (50) COMMENT 'Printer name'

The big watch locks the watch.

For large tables to add fields, it is recommended to use the pt-online-schema-change tool, you can not lock the table.

Add the field orderid to the table idempotent:

Command:

[root@DB001 ~] # pt-online-schema-change-- user=root-- password=123456-- host=192.168.1.18-- alter "ADD COLUMN orderid VARCHAR (20) NULL DEFAULT NULL COMMENT 'Business document No."

Found 1 slaves:

Localhost.localdomain-> 192.168.1.19:socket

Will check slave lag on:

Localhost.localdomain-> 192.168.1.19:socket

Operation, tries, wait:

Analyze_table, 10, 1

Copy_rows, 10, 0.25

Create_triggers, 10, 1

Drop_triggers, 10, 1

Swap_tables, 10, 1

Update_foreign_keys, 10, 1

Altering `ora`.`idempotent`...

Creating new table...

Created new table ora._idempotent_new OK.

Altering new table...

Altered `ora`.` _ idempotent_ new` OK.

2019-10-23T19:01:00 Creating triggers...

2019-10-23T19:01:00 Created triggers OK.

2019-10-23T19:01:00 Copying approximately 24968401 rows...

Copying `ora`.`idempotent`: 4% 10:53 remain

Copying `ora`.`idempotent`: 8% 10:40 remain

Copying `ora`.`idempotent`: 11% 11:49 remain

Copying `ora`.`idempotent`: 14% 12:08 remain

Copying `ora`.`idempotent`: 17% 12:09 remain

Copying `ora`.`idempotent`: 19% 12:15 remain

Copying `ora`.`idempotent`: 22% 12:15 remain

Copying `ora`.`idempotent`: 24% 12:11 remain

Copying `ora`.`idempotent`: 27% 12:09 remain

Copying `ora`.`idempotent`: 29% 12:03 remain

Copying `ora`.`idempotent`: 31% 11:45 remain

Copying `ora`.`idempotent`: 34% 11:23 remain

Copying `ora`.`idempotent`: 37% 11:01 remain

Copying `ora`.`idempotent`: 39% 10:39 remain

Copying `ora`.`idempotent`: 42% 10:11 remain

Copying `ora`.`idempotent`: 44% 09:50 remain

Copying `ora`.`idempotent`: 47% 09:35 remain

Copying `ora`.`idempotent`: 49% 09:14 remain

Copying `ora`.`idempotent`: 52% 08:44 remain

Copying `ora`.`idempotent`: 55% 08:10 remain

Copying `ora`.`idempotent`: 59% 07:17 remain

Copying `ora`.`idempotent`: 63% 06:21 remain

Copying `ora`.`idempotent`: 67% 05:32 remain

Copying `ora`.`idempotent`: 71% 04:44 remain

Copying `ora`.`idempotent`: 75% 03:59 remain

Copying `ora`.`idempotent`: 80% 03:11 remain

Copying `ora`.`idempotent`: 84% 02:28 remain

Copying `ora`.`idempotent`: 88% 01:49 remain

Copying `ora`.`idempotent`: 92% 01:08 remain

Copying `ora`.`idempotent`: 96% 00:28 remain

2019-10-23T19:18:24 Copied rows OK.

2019-10-23T19:18:24 Analyzing new table...

2019-10-23T19:18:24 Swapping tables...

2019-10-23T19:18:24 Swapped original and new tables OK.

2019-10-23T19:18:24 Dropping old table...

2019-10-23T19:18:26 Dropped old table `ora`.` _ idempotent_ old` OK.

2019-10-23T19:18:26 Dropping triggers...

2019-10-23T19:18:26 Dropped triggers OK.

Successfully altered `ora`.`idempotent`.

You have mail in / var/spool/mail/root

The whole process lasts for more than 10 minutes without locking the table.

Field addition is complete.

PS:

Remember to run at the low peak of the business.

Pt-online-schema-change-- user=user-- password=xxx-- host=ip-- port=3306-- alter "add column col1 VARCHAR (64) NULL COMMENT 'order No.'" DempsyLignedl-- execute-- charset=utf8-- nocheck-replication-filters-- max-load= "Threads_running=20"

You can specify a character set to prevent garbled comments in tables with fields added.

Modify the comments:

ALTER TABLE idempotent MODIFY COLUMN `sysno` BIGINT (20) NOT NULL AUTO_INCREMENT COMMENT 'number'

This is the end of this article on "MySQL 5.7 how to use pt-online-schema-change to add fields to large tables". 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 out 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.