In the production environment, the ddl execution of mysql does not affect the normal use of online, and the tool Pt online is often used

Install Pt Online

• Install DBI

  wget http://www.cpan.org/modules/by-module/DBD/DBI-1.634.tar.gztar -xf DBI-1.634.tar.gzcd DBI-1.634/
  perl Makefile.PLmake && sudo make install

• Install DB-MySQL

  wget http://www.cpan.org/modules/by-module/DBD/DBD-mysql-4.027.tar.gz
  tar -xf DBD-mysql-4.027.tar.gz
  cd DBD-mysql-4.027/
  perl Makefile.PL --mysql_config=/export/servers/mysql/bin/mysql_config --with-mysql=/export/servers/mysql  make && sudo make install 
  

• Install percona Toolkit

  wget https://www.percona.com/downloads/percona-toolkit/3.0.8/binary/tarball/percona-toolkit-3.0.8_x86_64.tar.gztar -xf percona-toolkit-3.0.8_x86_64.tar.gzcd percona-toolkit-3.0.8perl Makefile.PL make && sudo make install 
  

Simple use of Pt online tool

Modify the ddl statement of the table

pt-online-schema-change --alter "add address varchar(255) not NULL default ''" D=test,t='test_type' --execute --print --statistics --no-check-alter --user root --password secret --host 127.0.0.1 --port 3358

Use process analysis of Pt online tool:

• Create new table

CREATE TABLE `test`.`_test_type_new` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12970 DEFAULT CHARSET=utf8

• Add column to new table

ALTER TABLE `test`.`_test_type_new` add address varchar(255) not NULL default ''

• Add delete trigger to old table

CREATE DEFINER=`root`@`%` TRIGGER `pt_osc_test_test_type_del` AFTER DELETE ON `test`.`test_type` FOR EACH ROW DELETE IGNORE FROM `test`.`_test_type_new` WHERE `test`.`_test_type_new`.`id` <=> OLD.`id`

• Add update trigger to old table

CREATE DEFINER=`root`@`%` TRIGGER `pt_osc_test_test_type_upd` AFTER UPDATE ON `test`.`test_type` FOR EACH ROW BEGIN DELETE IGNORE FROM `test`.`_test_type_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `test`.`_test_type_new`.`id` <=> OLD.`id`;REPLACE INTO `test`.`_test_type_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`);END

• Add insert trigger to old table

CREATE DEFINER=`root`@`%` TRIGGER `pt_osc_test_test_type_ins` AFTER INSERT ON `test`.`test_type` FOR EACH ROW REPLACE INTO `test`.`_test_type_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)

• analyze new table
  The function is to analyze keyword distribution: because the data is written in batches, the analyze keyword is needed

ANALYZE TABLE `test`.`_test_type_new` /* pt-online-schema-change *

• rename table

RENAME TABLE `test`.`test_type` TO `test`.`_test_type_old`, `test`.`_test_type_new` TO `test`.`test_type`

• Sweeping work

##Delete the old table drop table if exists' ﹐ test ﹐ type ﹐ old '/ * generated by server * / ﹐ delete trigger if exists ` test'. ` Pt ﹐ OSC ﹐ test ﹐ type ﹐ drop trigger if exists ` test '. ` Pt ﹐ OSC ﹐ test ﹐ type ﹐ drop trigger if exists ` test'. ` Pt ﹐ OSC ﹐ test ﹐ type ﹐ ins`

summary

The above is the principle of adding columns in PT online ddl