pt-osc实战运用
1、安装pt-osc,解压即可用
安装包在：10.135.2.217:data/online/software/percona-toolkit-3.0.12.tar.gz
tar -zxvf percona-toolkit-3.0.12.tar.gz
cd percona-toolkit-3.0.12/bin
cp pt-online-schema-change /usr/bin/
2、常用命令
pt-online-schema-change
--host=10.249.5.39
--port=3306
--user=xxxx
--password=xxxx
--alter "add column c4 varchar(8) not null default '' "
D=darren
t=t_user
--execute
3、常用参数解释
--alter-foreign-keys-method=s
设置修改外键的方式，一共有如下4个值：
auto：优先选择rebuild_constraints方式处理外键，如果该方式不可用才选择drop_swap；
rebuild_constraints：在最后rename表后，删除子表上的外键，并进行重新建立到新表的外键。
drop_swap：拷贝数据后，设置SET foreign_key_checks=0，直接将原表drop掉，然后rename临时表为新表
none：表示强制不考虑子表外键约束，设置SET foreign_key_checks=0，最终会导致子表外键约束到_table_old这个已删除的表上，不建议使用
--max-load string
string是状态表达式，默认Threads_running=25，当设置多个状态值，用逗号分隔，如'Threads_running=100,Threads_connected=500'，当超过该值，迁移暂停等待，通过sleep方式暂停
--critical-load
与max-load不同的是，当超过该值，迁移直接停止并退出，默认值是Threads_running=50
--max-lag=m
默认1s，检查slave延迟的值，超过1秒则暂停复制数据
4、输出日志分析
2018-10-17T13:43:01.414577+08:00 5892279 Connect dbadmin@10.249.5.39 on test using TCP/IP
2018-10-17T13:43:01.415106+08:00 5892279 Query set autocommit=1
2018-10-17T13:43:01.415645+08:00 5892279 Query SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
2018-10-17T13:43:01.416889+08:00 5892279 Query SET SESSION innodb_lock_wait_timeout=1
2018-10-17T13:43:01.417276+08:00 5892279 Query SHOW VARIABLES LIKE 'lock\_wait_timeout'
2018-10-17T13:43:01.418441+08:00 5892279 Query SET SESSION lock_wait_timeout=60
2018-10-17T13:43:01.418874+08:00 5892279 Query SHOW VARIABLES LIKE 'wait\_timeout'
2018-10-17T13:43:01.419934+08:00 5892279 Query SET SESSION wait_timeout=10000
2018-10-17T13:43:01.420280+08:00 5892279 Query SELECT @@SQL_MODE
2018-10-17T13:43:01.420588+08:00 5892279 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO'*/
2018-10-17T13:43:01.420904+08:00 5892279 Query SELECT @@server_id /*!50038 , @@hostname*/
2018-10-17T13:43:01.422513+08:00 5892280 Connect dbadmin@10.249.5.39 on test using TCP/IP
2018-10-17T13:43:01.422827+08:00 5892280 Query set autocommit=1
2018-10-17T13:43:01.423249+08:00 5892280 Query SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
2018-10-17T13:43:01.424386+08:00 5892280 Query SET SESSION innodb_lock_wait_timeout=1
2018-10-17T13:43:01.424691+08:00 5892280 Query SHOW VARIABLES LIKE 'lock\_wait_timeout'
2018-10-17T13:43:01.425711+08:00 5892280 Query SET SESSION lock_wait_timeout=60
2018-10-17T13:43:01.425999+08:00 5892280 Query SHOW VARIABLES LIKE 'wait\_timeout'
2018-10-17T13:43:01.426917+08:00 5892280 Query SET SESSION wait_timeout=10000
2018-10-17T13:43:01.427248+08:00 5892280 Query SELECT @@SQL_MODE
2018-10-17T13:43:01.427518+08:00 5892280 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO'*/
2018-10-17T13:43:01.427815+08:00 5892280 Query SELECT @@server_id /*!50038 , @@hostname*/
2018-10-17T13:43:01.428328+08:00 5892279 Query SHOW VARIABLES LIKE 'wsrep_on'
2018-10-17T13:43:01.429502+08:00 5892279 Query SHOW VARIABLES LIKE 'version%'
2018-10-17T13:43:01.430939+08:00 5892279 Query SHOW ENGINES
2018-10-17T13:43:01.431887+08:00 5892279 Query SHOW VARIABLES LIKE 'innodb_version'
2018-10-17T13:43:01.433792+08:00 5892279 Query SHOW VARIABLES LIKE 'innodb_stats_persistent'
2018-10-17T13:43:01.435051+08:00 5892279 Query SELECT @@SERVER_ID
2018-10-17T13:43:01.435445+08:00 5892279 Query SHOW GRANTS FOR CURRENT_USER()
2018-10-17T13:43:01.435940+08:00 5892279 Query SHOW FULL PROCESSLIST
2018-10-17T13:43:01.437507+08:00 5892281 Connect dbadmin@10.249.5.39 on using TCP/IP
2018-10-17T13:43:01.437790+08:00 5892281 Query set autocommit=1
2018-10-17T13:43:01.438283+08:00 5892281 Query SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
2018-10-17T13:43:01.439414+08:00 5892281 Query SET SESSION innodb_lock_wait_timeout=1
2018-10-17T13:43:01.439725+08:00 5892281 Query SHOW VARIABLES LIKE 'lock\_wait_timeout'
2018-10-17T13:43:01.440783+08:00 5892281 Query SET SESSION lock_wait_timeout=60
2018-10-17T13:43:01.441155+08:00 5892281 Query SHOW VARIABLES LIKE 'wait\_timeout'
2018-10-17T13:43:01.442144+08:00 5892281 Query SET SESSION wait_timeout=10000
2018-10-17T13:43:01.442410+08:00 5892281 Query SELECT @@SQL_MODE
2018-10-17T13:43:01.442677+08:00 5892281 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO'*/
2018-10-17T13:43:01.442971+08:00 5892281 Query SELECT @@SERVER_ID
2018-10-17T13:43:01.443218+08:00 5892281 Quit
2018-10-17T13:43:01.443878+08:00 5892279 Query SHOW GLOBAL STATUS LIKE 'Threads_running'
2018-10-17T13:43:01.444693+08:00 5892279 Query SHOW GLOBAL STATUS LIKE 'Threads_running'
2018-10-17T13:43:01.445606+08:00 5892279 Query SELECT CONCAT(@@hostname, @@port)
2018-10-17T13:43:01.446302+08:00 5892279 Query SHOW TABLES FROM `test` LIKE 'foo'
2018-10-17T13:43:01.446768+08:00 5892279 Query SELECT VERSION()
2018-10-17T13:43:01.447179+08:00 5892279 Query SHOW TRIGGERS FROM `test` LIKE 'foo'
2018-10-17T13:43:01.448039+08:00 5892279 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2018-10-17T13:43:01.448296+08:00 5892279 Query USE `test`
2018-10-17T13:43:01.448577+08:00 5892279 Query SHOW CREATE TABLE `test`.`foo`
2018-10-17T13:43:01.449391+08:00 5892279 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2018-10-17T13:43:01.450129+08:00 5892279 Query EXPLAIN SELECT * FROM `test`.`foo` WHERE 1=1
2018-10-17T13:43:01.450998+08:00 5892279 Query SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='test' AND referenced_table_name='foo'
2018-10-17T13:43:01.462372+08:00 5892279 Query EXPLAIN SELECT * FROM `test`.`bar` WHERE 1=1
2018-10-17T13:43:01.463628+08:00 5892279 Query SHOW VARIABLES LIKE 'wsrep_on'
2018-10-17T13:43:01.464892+08:00 5892279 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2018-10-17T13:43:01.465176+08:00 5892279 Query USE `test`
2018-10-17T13:43:01.465476+08:00 5892279 Query SHOW CREATE TABLE `test`.`foo`
2018-10-17T13:43:01.465817+08:00 5892279 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2018-10-17T13:43:01.466230+08:00 5892279 Query CREATE TABLE `test`.`_foo_new` (
`foo_id` int(11) NOT NULL,
PRIMARY KEY (`foo_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
2018-10-17T13:43:01.487029+08:00 5892279 Query ALTER TABLE `test`.`_foo_new` engine=innodb
2018-10-17T13:43:01.521293+08:00 5892279 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2018-10-17T13:43:01.521614+08:00 5892279 Query USE `test`
2018-10-17T13:43:01.522134+08:00 5892279 Query SHOW CREATE TABLE `test`.`_foo_new`
2018-10-17T13:43:01.522487+08:00 5892279 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2018-10-17T13:43:01.523664+08:00 5892279 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'DELETE' AND ACTION_TIMING = 'AFTER' AND TRIGGER_SCHEMA = 'test' AND EVENT_OBJECT_TABLE = 'foo'
2018-10-17T13:43:01.525314+08:00 5892279 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'UPDATE' AND ACTION_TIMING = 'AFTER' AND TRIGGER_SCHEMA = 'test' AND EVENT_OBJECT_TABLE = 'foo'
2018-10-17T13:43:01.526610+08:00 5892279 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'INSERT' AND ACTION_TIMING = 'AFTER' AND TRIGGER_SCHEMA = 'test' AND EVENT_OBJECT_TABLE = 'foo'
2018-10-17T13:43:01.527913+08:00 5892279 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'DELETE' AND ACTION_TIMING = 'BEFORE' AND TRIGGER_SCHEMA = 'test' AND EVENT_OBJECT_TABLE = 'foo'
2018-10-17T13:43:01.529203+08:00 5892279 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'UPDATE' AND ACTION_TIMING = 'BEFORE' AND TRIGGER_SCHEMA = 'test' AND EVENT_OBJECT_TABLE = 'foo'
2018-10-17T13:43:01.530485+08:00 5892279 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'INSERT' AND ACTION_TIMING = 'BEFORE' AND TRIGGER_SCHEMA = 'test' AND EVENT_OBJECT_TABLE = 'foo'
2018-10-17T13:43:01.531854+08:00 5892279 Query CREATE TRIGGER `pt_osc_test_foo_del` AFTER DELETE ON `test`.`foo` FOR EACH ROW DELETE IGNORE FROM `test`.`_foo_new` WHERE `test`.`_foo_new`.`foo_id` <=> OLD.`foo_id`
2018-10-17T13:43:01.536997+08:00 5892279 Query CREATE TRIGGER `pt_osc_test_foo_upd` AFTER UPDATE ON `test`.`foo` FOR EACH ROW BEGIN DELETE IGNORE FROM `test`.`_foo_new` WHERE !(OLD.`foo_id` <=> NEW.`foo_id`) AND `test`.`_foo_new`.`foo_id` <=> OLD.`foo_id`;REPLACE INTO `test`.`_foo_new` (`foo_id`) VALUES (NEW.`foo_id`);END
2018-10-17T13:43:01.541525+08:00 5892279 Query CREATE TRIGGER `pt_osc_test_foo_ins` AFTER INSERT ON `test`.`foo` FOR EACH ROW REPLACE INTO `test`.`_foo_new` (`foo_id`) VALUES (NEW.`foo_id`)
2018-10-17T13:43:01.548005+08:00 5892279 Query EXPLAIN SELECT * FROM `test`.`foo` WHERE 1=1
2018-10-17T13:43:01.549823+08:00 5892279 Query EXPLAIN SELECT `foo_id` FROM `test`.`foo` LOCK IN SHARE MODE /*explain pt-online-schema-change 31654 copy table*/
2018-10-17T13:43:01.550622+08:00 5892279 Query INSERT LOW_PRIORITY IGNORE INTO `test`.`_foo_new` (`foo_id`) SELECT `foo_id` FROM `test`.`foo` LOCK IN SHARE MODE /*pt-online-schema-change 31654 copy table*/
2018-10-17T13:43:01.554366+08:00 5892279 Query SHOW WARNINGS
2018-10-17T13:43:01.554959+08:00 5892279 Query SHOW GLOBAL STATUS LIKE 'Threads_running'
2018-10-17T13:43:01.556226+08:00 5892279 Query ANALYZE TABLE `test`.`_foo_new` /* pt-online-schema-change */
2018-10-17T13:43:01.561668+08:00 5892279 Query RENAME TABLE `test`.`foo` TO `test`.`_foo_old`, `test`.`_foo_new` TO `test`.`foo`
2018-10-17T13:43:01.583926+08:00 5892279 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2018-10-17T13:43:01.584256+08:00 5892279 Query USE `test`
2018-10-17T13:43:01.584536+08:00 5892279 Query SHOW CREATE TABLE `test`.`bar`
2018-10-17T13:43:01.584904+08:00 5892279 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2018-10-17T13:43:01.585580+08:00 5892279 Query ALTER TABLE `test`.`bar` DROP FOREIGN KEY `__bar_ibfk_1`, ADD CONSTRAINT `bar_ibfk_1` FOREIGN KEY (`foo_id`) REFERENCES `test`.`foo` (`foo_id`)
2018-10-17T13:43:01.618352+08:00 5892279 Query DROP TABLE IF EXISTS `test`.`_foo_old`
2018-10-17T13:43:01.643179+08:00 5892279 Query DROP TRIGGER IF EXISTS `test`.`pt_osc_test_foo_del`
2018-10-17T13:43:01.645349+08:00 5892279 Query DROP TRIGGER IF EXISTS `test`.`pt_osc_test_foo_upd`
2018-10-17T13:43:01.646829+08:00 5892279 Query DROP TRIGGER IF EXISTS `test`.`pt_osc_test_foo_ins`
2018-10-17T13:43:01.649537+08:00 5892279 Query SHOW TABLES FROM `test` LIKE '\_foo\_new'
2018-10-17T13:43:01.650436+08:00 5892280 Quit
2018-10-17T13:43:01.651462+08:00 5892279 Quit