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

MySQL modifies the table structure pt-osc online

It is well known that the DDL operation of MySQL is relatively expensive. Because MySQL blocks any read and write operations during table modification.

Basically, business is paralyzed. If the amount of data is large, it may take several hours to complete, and this operation cannot be tolerated. Percona has developed a series of tool 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, there are other tools such as MySQL5.6 's online ddl and gh-ost. This article focuses on pt-online-schema-change modifying the table structure online.

Principle part

Overview of the environment

Percona-Server-5.7.17-11 Percona-toolkit-3.0.3-1.el7.x86_64

Table structure

CREATE TABLE `test` (`id` int (40) NOT NULL, `name` char (12) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8

Operation to modify non-primary key name field

one. Preparatory work

Set the current call back parameter session level

SHOW VARIABLES LIKE 'innodb\ _ lock_wait_timeout'; SET SESSION innodb_lock_wait_timeout=1SET SESSION lock_wait_timeout=60 SET SESSION wait_timeout=10000innodb_lock_wait_timeout=1 lock_wait_timeout=60 wait_timeout=10000

two。 Collect MySQL information

SHOW VARIABLES LIKE 'version%' SHOW ENGINESSHOW VARIABLES LIKE' innodb_version'SHOW VARIABLES LIKE 'innodb_stats_persistent'SELECT @ @ SERVER_IDSHOW GRANTS FOR CURRENT_USER () SHOW FULL PROCESSLISTSHOW GLOBAL STATUS LIKE' Threads_running'SHOW GLOBAL STATUS LIKE 'Threads_running'SELECT CONCAT (@ @ hostname, @ @ port) SHOW TABLES FROM `test2`LIKE' test1'SHOW TRIGGERS FROM `test2` LIKE 'test1'

Two official start

1. Create a new table exactly like the old one

CREATE TABLE `test2`.` _ test1_ New` (`id` int (30) NOT NULL, `name` char (27) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8

two。 Modify the table structure on the new table

ALTER TABLE `test2`.` _ test1_ new` modify name char (27)

3. Create trigger

CREATE TRIGGER `pt_osc_test2_test1_ del`AFTER DELETE ON `test2`.`test1` FOR EACH ROW DELETE IGNORE FROM `test2`.` _ test1_ new`WHERE `test2`.` _ test1_ new`.`id`OLD.`id`

# delete operation

CREATE TRIGGER `pt_osc_test2_test1_ upd` AFTER UPDATE ON `test2`.`test1` FOR EACH ROW BEGIN DELETE IGNORE FROM `test2`.` _ test1_ new` WHERE! (OLD.`id`NEW.`id`) AND `test2`.` _ test1_ new`.`id`OLD.`id`; REPLACE INTO `test2`.` _ test1_ new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)

# Update operation

CREATE TRIGGER `pt_osc_test2_test1_ ins` AFTER INSERT ON `test2`.`test1` FOR EACH ROW REPLACE INTO `test2`.` _ test1_ new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)

# insert operation

4. Insert into the old table

EXPLAIN SELECT `id`, `name` FROM `test2`.`test1` LOCK IN SHARE MODE IGNORE INTO `test2`.` _ test1_ new` (`id`, `name`) SELECT `id`, `name`FROM `test2`.`test1` LOCK IN SHARE MODE / * pt-online-schema-change 6291 copy table*/

# LOCK IN SHARE MODE with lock operation

The third closing work

SHOW WARNINGSSELECT @ @ SERVER_IDSHOW GRANTS FOR CURRENT_USER () SHOW FULL PROCESSLISTSHOW GLOBAL STATUS LIKE 'Threads_running'ANALYZE TABLE `test2`.` _ test1_ new` / * pt-online-schema-change * / RENAME TABLE `test2`.`test1`TO `test2`.` _ test1_ old` `test2`.` _ test1_ new`DROP TABLE IF EXISTS `test2`.` _ test1_ old`ROP TRIGGER IF EXISTS `test2`.`pt _ osc_test2_test1_ del`DROP TRIGGER IF EXISTS `test2`.`pt _ osc_test2_test1_ upd`DROP TRIGGER IF EXISTS `test2`.`pt _ osc_test2_test1_ ins`SHOW TABLES FROM `test2`LIKE'\ _ test1\ _ new'

Overview

View and collect MySQL information

Create a new table with the same structure as the original table and then change the table structure in the new table.

Create three triggers in the original table. Three triggers correspond to insert update delete operations.

All writes from the original table to the new table are updated to the temporary table.

After the completion of copy, the original rename table is old table, and then the new table rename original table is finally deleted, old table and trigger.

Four points for attention

Read the tool's documentation

Review the tool's known "BUGS"

Test the tool on a non-production server

Backup your production server and verify the backups

Summary first take a look at the tool documentation, before using the test, backup. When performing online changes to the table structure, it is best to choose the business trough and not delete the old table.

Five pt-osc restrictions

In most cases the tool will refuse to operate unless a PRIMARY KEY or UNIQUE INDEX is present in the table. See-- alter for details.

The tool refuses to operate if it detects replication filters. See-[no] check-replication-filters for details.

The tool pauses the data copy operation if it observes any replicas that are delayed in replication. See-- max-lagfor details.

The tool pauses or aborts its operation if it detects too much load on the server. See-max-load and-critical-load for details.

The tool sets innodb_lock_wait_timeout=1 and (for MySQL 5.5and newer) lock_wait_timeout=60 so that it is more likely to be the victim of any lock contention, and less likely to disrupt other transactions. These values can be changed by specifying-- set-vars.

The tool refuses to alter the table if foreign key constraints reference it, unless you specify-- alter-foreign-keys-method.

The tool cannot alter MyISAM tables on "Percona XtraDB Cluster" nodes.

6 matters needing attention

1. Take a look at the tool documentation, test it before using it, and back up the backup.

two。 When performing online changes to the table structure, it is best to choose the business trough and not delete the old table.

3. Must have a primary key, cannot be used, must have a primary key.

If 4.pt-osc changes the foreign key constraint, it refuses to work unless-- alter-foreign-keys-method is specified.

5. When operating, you need to specify a character set to prevent garbled code.

Referenc

Https://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html

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.