首页 > 其他分享 >5.pt-online-schema

5.pt-online-schema

时间：2022-10-30 22:01:08浏览次数：88  
标签：10 00 pt 16 30T19 08 2022 online schema

pt-online-schema-change alters a table’s structure without blocking reads or writes. Specify the database and table in the DSN. Do not use this tool before reading its documentation and checking your backups carefully.(主要作用是：在不阻塞读写的情况下进行修改表的结构)

主要工作流程：

  • 创建一个跟原表结构一样的新表(作为临时表)
  • 修改新表的结构
  • 在原表中创建insert、update、delete这3中类型的触发器，用于增量数据的迁移；注意：触发器对新表的修改操作和原sql语句在同一个事务中
  • 会以一定块大小(chunck-size)从原表拷贝数据到新表中；注意这里使用insert low_priority ignore into _table_new  select  from 旧表 lock in share mode的方式进行拷贝
  • 数据拷贝完成之后，rename表，原表被命名为旧表，新表命名为原表
  • 删除旧表和触发器

看日志：　

2022-10-30T19:16:08.005175+08:00           35 Query     SET SESSION innodb_lock_wait_timeout=1
2022-10-30T19:16:08.007054+08:00           35 Query     SHOW VARIABLES LIKE 'lock\_wait_timeout'
2022-10-30T19:16:08.010484+08:00           35 Query     SET SESSION lock_wait_timeout=60
2022-10-30T19:16:08.011057+08:00           35 Query     SHOW VARIABLES LIKE 'wait\_timeout'
2022-10-30T19:16:08.012646+08:00           35 Query     SET SESSION wait_timeout=10000
2022-10-30T19:16:08.013158+08:00           35 Query     SELECT @@SQL_MODE
2022-10-30T19:16:08.020918+08:00           35 Query     SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
2022-10-30T19:16:08.025225+08:00           35 Query     SELECT @@server_id /*!50038 , @@hostname*/
2022-10-30T19:16:08.027894+08:00           36 Connect   root@localhost on t using Socket
2022-10-30T19:16:08.030233+08:00           36 Query     SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
2022-10-30T19:16:08.033612+08:00           36 Query     SET SESSION innodb_lock_wait_timeout=1
2022-10-30T19:16:08.034247+08:00           36 Query     SHOW VARIABLES LIKE 'lock\_wait_timeout'
2022-10-30T19:16:08.035848+08:00           36 Query     SET SESSION lock_wait_timeout=60
2022-10-30T19:16:08.036444+08:00           36 Query     SHOW VARIABLES LIKE 'wait\_timeout'
2022-10-30T19:16:08.038423+08:00           36 Query     SET SESSION wait_timeout=10000
2022-10-30T19:16:08.038911+08:00           36 Query     SELECT @@SQL_MODE
2022-10-30T19:16:08.039659+08:00           36 Query     SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
2022-10-30T19:16:08.040325+08:00           36 Query     SELECT @@server_id /*!50038 , @@hostname*/
2022-10-30T19:16:08.040880+08:00           35 Query     SHOW VARIABLES LIKE 'wsrep_on'
2022-10-30T19:16:08.042914+08:00           35 Query     SHOW VARIABLES LIKE 'version%'
2022-10-30T19:16:08.045307+08:00           35 Query     SHOW ENGINES
2022-10-30T19:16:08.046561+08:00           35 Query     SHOW VARIABLES LIKE 'innodb_version'
2022-10-30T19:16:08.049400+08:00           35 Query     SHOW VARIABLES LIKE 'innodb_stats_persistent'
2022-10-30T19:16:08.051305+08:00           35 Query     SELECT @@SERVER_ID
2022-10-30T19:16:08.052026+08:00           35 Query     SHOW GRANTS FOR CURRENT_USER()
2022-10-30T19:16:08.052929+08:00           35 Query     SHOW FULL PROCESSLIST
2022-10-30T19:16:08.053692+08:00           35 Query     SHOW SLAVE HOSTS
2022-10-30T19:16:08.055006+08:00           35 Query     SHOW GLOBAL STATUS LIKE 'Threads_running'
2022-10-30T19:16:08.056895+08:00           35 Query     SHOW GLOBAL STATUS LIKE 'Threads_running'
2022-10-30T19:16:08.058533+08:00           35 Query     SELECT CONCAT(@@hostname, @@port)
2022-10-30T19:16:08.059512+08:00           35 Query     SHOW TABLES FROM `t` LIKE 't1'
2022-10-30T19:16:08.060075+08:00           35 Query     SELECT VERSION()
2022-10-30T19:16:08.060583+08:00           35 Query     SHOW TRIGGERS FROM `t` LIKE 't1'
2022-10-30T19:16:08.067575+08:00           35 Query     /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2022-10-30T19:16:08.068141+08:00           35 Query     USE `t`
2022-10-30T19:16:08.068568+08:00           35 Query     SHOW CREATE TABLE `t`.`t1`
2022-10-30T19:16:08.074985+08:00           35 Query     /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2022-10-30T19:16:08.081045+08:00           35 Query     EXPLAIN SELECT * FROM `t`.`t1` WHERE 1=1
2022-10-30T19:16:08.082280+08:00           35 Query     SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='t' AND referenced_table_name='t1'
2022-10-30T19:16:08.140201+08:00           35 Query     SHOW VARIABLES LIKE 'wsrep_on'
2022-10-30T19:16:08.162191+08:00           35 Query     /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2022-10-30T19:16:08.164151+08:00           35 Query     USE `t`
2022-10-30T19:16:08.164700+08:00           35 Query     SHOW CREATE TABLE `t`.`t1`
2022-10-30T19:16:08.165389+08:00           35 Query     /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2022-10-30T19:16:08.166674+08:00           35 Query     CREATE TABLE `t`.`_t1_new` (
  `id` int(11) NOT NULL,
  `date` datetime NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
2022-10-30T19:16:08.208210+08:00           35 Query     ALTER TABLE `t`.`_t1_new` drop column name
2022-10-30T19:16:08.237364+08:00           35 Query     /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2022-10-30T19:16:08.237870+08:00           35 Query     USE `t`
2022-10-30T19:16:08.238457+08:00           35 Query     SHOW CREATE TABLE `t`.`_t1_new`
2022-10-30T19:16:08.239126+08:00           35 Query     /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2022-10-30T19:16:08.241847+08:00           35 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 = 't'    AND EVENT_OBJECT_TABLE = 't1'
2022-10-30T19:16:08.243339+08:00           35 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 = 't'    AND EVENT_OBJECT_TABLE = 't1'
2022-10-30T19:16:08.245271+08:00           35 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 = 't'    AND EVENT_OBJECT_TABLE = 't1'
2022-10-30T19:16:08.247396+08:00           35 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 = 't'    AND EVENT_OBJECT_TABLE = 't1'
2022-10-30T19:16:08.248784+08:00           35 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 = 't'    AND EVENT_OBJECT_TABLE = 't1'
2022-10-30T19:16:08.250296+08:00           35 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 = 't'    AND EVENT_OBJECT_TABLE = 't1'
2022-10-30T19:16:08.251249+08:00           35 Query     CREATE TRIGGER `pt_osc_t_t1_del` AFTER DELETE ON `t`.`t1` FOR EACH ROW DELETE IGNORE FROM `t`.`_t1_new` WHERE `t`.`_t1_new`.`id` <=> OLD.`id`
2022-10-30T19:16:08.256740+08:00           35 Query     CREATE TRIGGER `pt_osc_t_t1_upd` AFTER UPDATE ON `t`.`t1` FOR EACH ROW BEGIN DELETE IGNORE FROM `t`.`_t1_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `t`.`_t1_new`.`id` <=> OLD.`id`;REPLACE INTO `t`.`_t1_new` (`id`, `date`, `time`) VALUES (NEW.`id`, NEW.`date`, NEW.`time`);END
2022-10-30T19:16:08.262094+08:00           35 Query     CREATE TRIGGER `pt_osc_t_t1_ins` AFTER INSERT ON `t`.`t1` FOR EACH ROW REPLACE INTO `t`.`_t1_new` (`id`, `date`, `time`) VALUES (NEW.`id`, NEW.`date`, NEW.`time`)
2022-10-30T19:16:08.269085+08:00           35 Query     EXPLAIN SELECT * FROM `t`.`t1` WHERE 1=1
2022-10-30T19:16:08.270932+08:00           35 Query     EXPLAIN SELECT `id`, `date`, `time` FROM `t`.`t1` LOCK IN SHARE MODE /*explain pt-online-schema-change 5982 copy table*/
2022-10-30T19:16:08.271775+08:00           35 Query     INSERT LOW_PRIORITY IGNORE INTO `t`.`_t1_new` (`id`, `date`, `time`) SELECT `id`, `date`, `time` FROM `t`.`t1` LOCK IN SHARE MODE /*pt-online-schema-change 5982 copy table*/
2022-10-30T19:16:08.274726+08:00           35 Query     SHOW WARNINGS
2022-10-30T19:16:08.275775+08:00           35 Query     SHOW GLOBAL STATUS LIKE 'Threads_running'
2022-10-30T19:16:08.279752+08:00           35 Query     SHOW VARIABLES LIKE 'version%'
2022-10-30T19:16:08.284708+08:00           35 Query     SHOW ENGINES
2022-10-30T19:16:08.285603+08:00           35 Query     SHOW VARIABLES LIKE 'innodb_version'
2022-10-30T19:16:08.289180+08:00           35 Query     ANALYZE TABLE `t`.`_t1_new` /* pt-online-schema-change */
2022-10-30T19:16:08.296379+08:00           35 Query     RENAME TABLE `t`.`t1` TO `t`.`_t1_old`, `t`.`_t1_new` TO `t`.`t1`
2022-10-30T19:16:08.330268+08:00           35 Query     DROP TABLE IF EXISTS `t`.`_t1_old`
2022-10-30T19:16:08.341534+08:00           35 Query     DROP TRIGGER IF EXISTS `t`.`pt_osc_t_t1_del`
2022-10-30T19:16:08.345909+08:00           35 Query     DROP TRIGGER IF EXISTS `t`.`pt_osc_t_t1_upd`
2022-10-30T19:16:08.352083+08:00           35 Query     DROP TRIGGER IF EXISTS `t`.`pt_osc_t_t1_ins`
View Code

该工具的限制：

  • 原表中必须要有主键索引或者唯一索引
  • 如果你添加一列时没有指定一个默认(default)值而是给它置为not null时，这个工具可能执行会失败，因为它不会试图为你去猜一个默认值，因此你必须要执行这个默认
  • 不支持rename语句对表进行重命名操作
  • 列不能通过删除 + 删除的方式来重命令，这样将不会copy原有列的数据到新列；
  • 如果删除外键，需要对外键名加下划线，如果删除外键fk_uid，修改语句为'drop foreign key _fk_uid'

用法：

Usage: pt-online-schema-change [OPTIONS] DSN
OPTIONS：
　　--user=xxx
　　--password=xxx
　　--socket=xxxx
　　--chunk-size=xxx :该参数默认是1000，表示每个拷贝块拷贝1000行
　　--chunk-time=xxx:动态调整块的大小，默认值是0.5
   --critical-load = A ：在每次执行后检查show global status 块，如果负载太高则中止(默认Threads_running=50)
　　--max-load=xxx:每一块执行之后检查show global status，默认thread_running=25,一旦大于25会暂缓pt任务的执行，可以视情况调大
 　
pt-online-schema-change   --user=root  --password=123  --socket=/data/3306/mysqld_3306.sock   --alter 'add  column name varchar(20) not null default ""' D=t,t=t1  --print --execute

　或者：

pt-online-schema-change --user= --password= --host= D=,t= --sock=/tmp/mysql.sock --alter '' --charset=utf8 --alter-foreign-keys-method=auto --max-load='Threads_running=35,Threads_connected=10000' --critical-load='Threads_running=200,Threads_connected=10000' --no-check-replication-filters -recursion-method=none --print --execute

使用该命令的风险：

　　1.给表增加唯一索引时一定要确保该列没有重复数据，否则会丢失数据，这里可以用count(distinct(c1))的值来判断

　　2.可能有几率触发死锁的情况

　　3.消费binlog可能有异常，要及时通知消费端

　　4.高负载情况下慎用

pt-osc强制中止清理

　　1.kill  pt-osc相关进程

　　2.drop triggers,如果出现MDL锁要进行MDL锁定位(一般为慢查询导致)

  参考：https://docs.percona.com/percona-toolkit/pt-online-schema-change.html#

　　　  https://www.cnblogs.com/danhuangpai/p/16111199.html

标签：10,00,pt,16,30T19,08,2022,online,schema
From： https://www.cnblogs.com/zmc60/p/16842379.html

相关文章

  • ES6与JavaScript学习总结
    菜鸟的ES6与JavaScript学习总结说明一、概述（1）什么是javascript?（2）什么是ES6（3）ES6和JavaScript的关系（4）ES6的浏览器兼容情况二、语法（1）变量（2）箭头函数（3）解构赋值（4）数组操作（5）字符串......
  • typescript number 转 string（转）
    转自：number转string一、双点解析10..toString();二、括号先计算再转换(10).toString();三、加空串10+''转自：number转string......
  • 【笔记09】Javascript - 函数 - 闭包
    【笔记09】Javascript-基本概念-（闭包）内部函数被return 到外部。functiona(){functionb(){varbbb=234;console.log(aaa);}varaaa=......
  • 【李宏毅机器学习】Domain Adaptation 域适应
    note：在工程很常见的现象，训练好的模型用在model未见过域的样本时，表现效果较差，解决domainshift可以使用迁移学习transferlearning，而domainadaptation是transferlearning的......
  • JavaScript是什么？
    从头认识JavaScriptJavaScript简称‘JS’，是一款用来在网页中添加一些动态效果与交互功能的客户端语言，虽然如今看来这是前端工程师的必修课，但其实这在前后端还没有分离的......
  • Let’s Encrypt Challenge and acme.sh Issue Mode
    Basicdnscname:将域名指向另外一个域名；txt：存储一个512长度内的文本，通常作SPF记录（SenderPolicyFramework反垃圾邮件）；ns:将子域名指定其他的DNS服务器解析；dig​​linuxd......
  • Istio(十三)：Istio项目实际案例——Online Boutique
    目录一.模块概览二.系统环境三.创建Kubernetes(k8s)集群3.1创建Kubernetes(k8s)集群3.2Kubernetes集群环境四.安装istio4.1安装Istio五.部署onlineBoutique应用5.1部......
  • JavaScript--Express框架重构项目逻辑
     1.Express框架介绍 *Express是高度包容、快速而极简的Node.js-Web框架   中间件  上手简单，学习门槛低具有丰富的基础API支持强大的路由功能灵活的......
  • C++——智能指针unique_ptr
    独占指针：unique_ptrunique_ptr在任何给定的时刻，只能有一个指针管理内存当指针超出作用域时，内存将自动释放该类型指针不可Copy，只可以Move运行结果没有运行delete......
  • JavaScript 使用 Notification 发送系统通知
    使用Notification可以在系统级别发送页面外部显示的桌面通知，即使浏览器在后台运行也可以向用户发出消息检查权限发送通知需要用户授权，通过只读属性Notification.per......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99