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

Mysql5.6 and mysql5.7 have greatly enhanced the function of online DDL, but there is still a situation that the master library executes DDL and the slave library has a large delay, so the current production environment still uses pt-online-schema-change tools to implement online DDL. But is there no limit to the use of pt-online-schema-change?

Let's take a look at the official documentation describing how pt-online-schema-change works:

Pt-online-schema-change works by creating an empty copy of the table to alter, modifying it as desired, and thencopying rows from the original table into the new table. When the copy is complete, it moves away the original tableand replaces it with the new one. By default, it also drops the original table. The data copy process is performed in small chunks of data, which are varied to attempt to make them execute ina specific amount of time (see-chunk-time). This process is very similar to how other tools, such as pt-tablechecksum,work. Any modifications to data in the original tables during the copy will be reflected in the new table,because the tool creates triggers on the original table to update the corresponding rows in the new table. The use oftriggers means that the tool will not work if any triggers are already defined on the table. When the tool finishes copying data into the new table, it uses an atomic RENAME TABLE operation

Next, experiment to see how pt-online-schema-change works, and remember to open mysql's general log. Verify how pt-online-schema-change works by viewing the general log.

Shell > pt-online-schema-change-u linzj-h 192.168.110.131-p linzj-- alter='add column vid3 int'-- execute dumbsbtest

1 create an empty table structure that is the same as the table you want to alter:

11 Query CREATE TABLE `sbtest`.` _ sbtest_ New` (`id` int (10) unsigned NOT NULL AUTO_INCREMENT, `k` int (10) unsigned NOT NULL DEFAULT '0mm, `c` char (120) NOT NULL DEFAULT'', `pad` char (60) NOT NULL DEFAULT'', `vid` int (11) DEFAULT NULL, `vid2` int (11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `k` (`k`) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8

2. Perform table structure modification

170407 15:45:46 11 Query ALTER TABLE `sbtest`.` _ sbtest_ new` add column vid3 int

3. Create a trigger on the original table. If the trigger is already defined in the table, the tool will not work.

11 Query CREATE TRIGGER `sbtest`.`sbtest` FOR EACH ROW DELETE IGNORE FROM `sbtest`.` _ sbtest_ new`WHERE `sbtest`.` _ sbtest_ new`.`OLD.`id`11 Query CREATE TRIGGER `pt_osc_sbtest_sbtest_ upd`AFTER UPDATE ON `sbtest`.`sbtest`.` _ sbtest_ new` (`id`, `k`, `c`, `pad`, `vid`, `vid2`) VALUES (NEW.id`, NEW.k`, NEW.`pad`, NEW.`pad` NEW.`vid2`) 11 Query CREATE TRIGGER `pt_osc_sbtest_sbtest_ ins` AFTER INSERT ON `sbtest`.`sbtest` FOR EACH ROW REPLACE INTO `sbtest`.` _ sbtest_ new` (`id`, `k`, `c`, `pad`, `vid`, `vid2`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`, NEW.`vid`, NEW.`vid2`)

4. Sort by the unique index of the primary key or, and divide it into several chunk for data copy.

11 Query EXPLAIN SELECT * FROM `sbtest`.`sbtest` WHERE 1' 11 Query SELECT / *! 40001 SQL_NO_CACHE * / `id`FROM `sbtest`.`sbtest` FORCE INDEX (`PRIMARY`) ORDER BY `id`LIMIT 1 / * first lower boundary*/ 11 Query SELECT / *! 40001 SQL_NO_CACHE * / `id`FROM `sbtest`.`sbtest` FORCE INDEX (`PRIMARY`) WHERE `id`IS NOT NULL ORDER BY `id` LIMIT 1 / * key_len*/ 11 Query EXPLAIN SELECT / *! 40001 SQL_NO_CACHE * / * FROM `sbtest`.`sbtest`FORCE INDEX (`PRIMARY`) WHERE `id` > ='1' / * key_len*/ 11 Query EXPLAIN SELECT / *! 40001 SQL_NO_CACHE * / `id`FROM `sbtest`.`sbtest` FORCE INDEX (`PRIMARY`) WHERE ((`id` > ='1') ORDER BY `id` LIMIT 999 2 / * next chunk boundary*/ 11 Query SELECT / *! 40001 SQL_NO_CACHE * / `id`FROM `sbtest`.`sbtest` FORCE INDEX (`PRIMARY`) WHERE ((`id` > ='1')) ORDER BY `id` LIMIT 2 / * next chunk boundary*/ 11 Query SHOW WARNINGS 11 Query SHOW GLOBAL STATUS LIKE 'Threads_running' 11 Query EXPLAIN SELECT / *! 40001 SQL_NO_CACHE * / `id`FROM `sbtest`.`sbtest` FORCE INDEX (`PRIMARY`) WHERE ((`id` > =' 1001')) ORDER BY `id` LIMIT 19329 2 / * next chunk boundary*/ 11 Query SELECT / *! 40001 SQL_NO_CACHE * / `id` FROM `sbtest`.`sbtest` FORCE INDEX (`PRIMARY`) WHERE ((`id` > = '1001') ORDER BY `id` LIMIT 19329, 2 / * next chunk boundary*/ 11 Query EXPLAIN SELECT `id`, `k`, `c`, `pad`, `vid` `vid2` FROM `sbtest`.`sbtest` FORCE INDEX (`PRIMARY`) WHERE ((`id` > = '1001')) AND ((`id` =' 1001')) AND ((`id` pt-online-schema-change-u linzj-h 192.168.110.131-p linzj-- alter='add column vid3 int'-- execute Dempsbtestart tpromosbtestFound 2 slaves: mysql2 ansibleWill check slave lag on: mysql2 ansibleOperation, tries, wait: copy_rows, 10,0.25 create_triggers, 10,1 drop_triggers, 10,1swap_tables, 10,1update_foreign_keys, 10 1Altering `sbtest`.`sbtest`.Creating new table...Created new table sbtest._sbtest_new OK.Waiting forever for new table `sbtest`.` _ sbtest_ new`sbtest`.` _ sbtest_ new`OK.2017-04-07T14:52:50 Creating triggers...2017-04-07T14:52:50 Created triggers OK.2017-04-07T14:52:50 Copying approximately 986400 rows...Copying `sbtest`.`sbtest`: 86% 00:04 remain2017-04-07T14:53 27 Copied rows OK.2017-04-07T14:53:27 Swapping tables...2017-04-07T14:53:27 Swapped original and new tables OK.2017-04-07T14:53:27 Dropping old table...2017-04-07T14:53:27 Dropped old table `sbtest`.` _ sbtest_ old` OK.2017-04-07T14:53:27 Dropping triggers...2017-04-07T14:53:27 Dropped triggers OK.Successfully altered `sbtest`.`sbtest`.

Second window:

Root@localhost:mysql3306.sock 15:44: [sbtest] > select count (*) from sbtest;+-+ | count (*) | +-+ | 1000000 | +-+ 1 row in set (0.17 sec) root@localhost:mysql3306.sock 15:44: [sbtest] > update sbtest set id=9999999 where id= 110 Query OK, 1 row affected (1.33 sec) Rows matched: 1 Changed: 1 Warnings: 0root@localhost:mysql3306.sock 15:45: [sbtest] > update sbtest set id=9999998 where id= 111 ash query OK, 1 row affected (0.84 sec) Rows matched: 1 Changed: 1 Warnings: 0root@localhost:mysql3306.sock 15:46: [sbtest] > update sbtest set id=9999997 where id= 112 Query OK, 1 row affected (0.75 sec) Rows matched: 1 Changed: 1 Warnings: 0root@localhost:mysql3306.sock 15:46: [sbtest] > select count (*) from sbtest;+-+ | count (*) | +-+ | 1000003 | +-+ 1 row in set (0.70 sec) root@localhost:mysql3306.sock 15:46: [sbtest] > select * from sbtest order by id desc limit 5 +-+-- + | id | k | c | pad | | vid | vid2 | vid3 | +-+-- + | 9999999 | 0 | | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt | NULL | NULL | NULL | | 9999998 | 0 | | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt | NULL | NULL | NULL | | 9999997 | 0 | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt | NULL | NULL | NULL | | 1000000 | 0 | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt | NULL | NULL | NULL | | 999999 | 0 | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt | NULL | NULL | NULL | + -+ 5 rows in set (0.00 sec) root@localhost:mysql3306.sock 15:46: [sbtest] > select * from sbtest where id in (110111112) +-+-- + | id | k | c | pad | Vid | vid2 | vid3 | +-+-- + | 110 | 0 | | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt | NULL | NULL | NULL | | 111 | 0 | | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt | NULL | NULL | NULL | | 112 | 0 | | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt | NULL | NULL | NULL | +-+-- + 3 rows in set (0.02 sec)

At the same time, if the or unique index of the primary key of the table is modified, it will be found that the new table has more data than the old table. This should be regarded as a bug of the pt-online-schema-change tool, why this happens, please take a closer look at the definition of the 3 triggers created by the pt-online-schema-change tool in the original table can be easily found.

It is recommended that when using pt-online-schema-change, suspend data updates to the unique index column of the table primary key or.

Typical usage of pt_online_schema_change:

1) add a column, which is not really executed

Pt-online-schema-change-alter "add column C1 int" DrammydbCommand tweemytable-dry-run

2) update the storage engine to InnoDB and do not delete the original table

Pt-online-schema-change-alter "ENGINE=InnoDB"-no-drop-old-table-print-statistics-execute dumbmydbauthorship mytable-execute

3) in the replication environment, ignore log filtering and Slave replication delay, and delete table fields

Pt-online-schema-change-no-check-replication-filters-recursion-method=none-alter "drop company_type,drop channel_code" hobby 192.168.10.14, Prun3370, print, user1, pendant, pass1, Dobb1, print, statistics-execute.

4) Update the parent table referenced by the quilt table

Pt-online-schema-change-alter "add newcol int" hobbies 192.168.10.14, statistics 3370, upright user1, pimpspass1, Dobb1, alter-foreign-keys-method auto, print-statistics-execute.

5) in our two-master replication environment, we set to ignore the replication of the mysql library. We don't care much about the replication delay, and sometimes have the influence of foreign keys. We want to keep the original table data as much as possible and delete it if necessary.

Pt-online-schema-change-no-check-replication-filters-recursion-method=none-alter "drop newcol" hobby 192.168.10.14, pendant 3370, upright user1, pawnpass1, Dobbb1, no-drop-old-table, print-print-statistics-execute

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.