pt-online-schema-change工具简介

最新推荐文章于 2025-04-27 09:08:25 发布

01ADH

最新推荐文章于 2025-04-27 09:08:25 发布

阅读量248

点赞数 1

CC 4.0 BY-SA版权

文章标签： pt-online-schema-cha

本文链接：https://blog.youkuaiyun.com/jasonlu888/article/details/45097181

本文介绍了一种利用pt-online-schema-change工具在线修改MySQL表结构的方法，该方法能够避免阻塞应用并提高修改效率。通过创建表的拷贝进行结构修改，再通过触发器导入增量数据，最后完成原子性的表切换，确保了业务连续性和数据一致性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、工具简介

pt-online-schema-change是percona提供的一个mysql在线修改表结构的工具

2、使用场景

当需要修改线上数据库中某个表的表结构时，发现这个表的数据量比较大，如果使用alter table语句的话可能会阻塞较长的时间，那么可以考虑使用pt-online-schema-change在线修改表结构，而不用担心阻塞应用

3、工作原理

对于要修改表结构的表，pt-online-schema-change首先会创建一个该表的拷贝，这里只拷贝表结构而不涉及到数据，然后对这个拷贝来修改表结构，由于这个临时表没有数据，且没有应用访问，因此修改会很快且不会影响业务，临时表表结构修改完成后，会将原来的表中的数据拷贝到修改后的临时表中。那么如何保证在开始拷贝数据到数据拷贝完成后这个时间段中原表数据的增量也拷贝到临时表中去呢？pt-online-schema-change通过在原表上创建触发器来实现原表数据的增量导入到临时表中去。当拷贝完成后，通过原子性的rename table操作完成临时表和原表的切换。如下：

原表:test

假设临时表为test_new

原子性的rename table操作：mysql> rename table test to test_old,test_new to test;

当完成原子性表切换之后，pt-online-schema-change会删除原来的表，此时这张表已经没有应用访问了，所以delete对应用是没有影响的

4、使用限制

a、pt-online-schema-change操作的表需要有主键或者是唯一性索引

b、当修改的表中主键是其他表的外键时，默认情况下会失败，需要使用--alter-foreign-keys-method选项来处理

5、应用实例

命令格式如：

pt-online-schema-change -uroot -p123456 --alter='modify name varchar(20) ' --execute D=mydb,t=pt_test

## -u 用户名

## -p 密码

## --alter 修改的内容

## --execute 只有加了这个这条命令才会真正的执行

## D 数据库名

## t 表名

## 有pt_test表，结构如下 
mysql> show create table pt_test\G
*************************** 1. row ***************************
       Table: pt_test
Create Table: CREATE TABLE `pt_test` (
  `id` int(11) DEFAULT NULL,
  `name` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
  
## 将pt_test表的name字段由char修改为varchar
[root@test opt]# pt-online-schema-change -uroot -p123456 --alter='modify name varchar(20) ' --execute D=mydb,t=pt_test
No slaves found.  See --recursion-method if host dcmaster01.dba.com has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `mydb`.`pt_test`...
Creating new table...
Created new table mydb._pt_test_new OK.
Altering new table...
Altered `mydb`.`_pt_test_new` OK.
2015-04-15T16:17:04 Creating triggers...
2015-04-15T16:17:04 Created triggers OK.
2015-04-15T16:17:04 Copying approximately 1 rows...
2015-04-15T16:17:04 Copied rows OK.
2015-04-15T16:17:04 Swapping tables...
2015-04-15T16:17:04 Swapped original and new tables OK.
2015-04-15T16:17:04 Dropping old table...
2015-04-15T16:17:04 Dropped old table `mydb`.`_pt_test_old` OK.
2015-04-15T16:17:04 Dropping triggers...
2015-04-15T16:17:04 Dropped triggers OK.
Successfully altered `mydb`.`pt_test`.
  
## 再次查看pt_test的表结构
mysql> show create table pt_test\G
*************************** 1. row ***************************
       Table: pt_test
Create Table: CREATE TABLE `pt_test` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

针对外键的处理，需要加--alter-foreign-keys-method选项

## 表dept结构 
mysql> show create table dept\G
*************************** 1. row ***************************
       Table: dept
Create Table: CREATE TABLE `dept` (
  `deptno` int(11) NOT NULL,
  `dname` char(20) DEFAULT NULL,
  PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
  
## 表emp结构 
mysql> show create table emp\G
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `empno` int(11) NOT NULL,
  `ename` char(20) DEFAULT NULL,
  `deptno` int(11) DEFAULT NULL,
  PRIMARY KEY (`empno`),
  KEY `_emp_ibfk_1` (`deptno`),
  CONSTRAINT `_emp_ibfk_1` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
  
## 修改表dept的dname字段的类型为varchar
[root@dcmaster01 opt]# pt-online-schema-change -uroot -p123456 --alter='modify dname varchar(20) ' --alter-foreign-keys-method rebuild_constraints --execute D=mydb,t=dept 
No slaves found.  See --recursion-method if host dcmaster01.dba.com has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Child tables: 
  `mydb`.`emp` (approx. 3 rows)
Will use the rebuild_constraints method to update foreign keys.
Altering `mydb`.`dept`...
Creating new table...
Created new table mydb._dept_new OK.
Altering new table...
Altered `mydb`.`_dept_new` OK.
2015-04-15T16:27:14 Creating triggers...
2015-04-15T16:27:14 Created triggers OK.
2015-04-15T16:27:14 Copying approximately 3 rows...
2015-04-15T16:27:14 Copied rows OK.
2015-04-15T16:27:14 Swapping tables...
2015-04-15T16:27:14 Swapped original and new tables OK.
2015-04-15T16:27:14 Rebuilding foreign key constraints...
2015-04-15T16:27:14 Rebuilt foreign key constraints OK.
2015-04-15T16:27:14 Dropping old table...
2015-04-15T16:27:14 Dropped old table `mydb`.`_dept_old` OK.
2015-04-15T16:27:14 Dropping triggers...
2015-04-15T16:27:14 Dropped triggers OK.
Successfully altered `mydb`.`dept`.
  
## 再次查看dept和emp的表结构
mysql> show create table dept\G
*************************** 1. row ***************************
       Table: dept
Create Table: CREATE TABLE `dept` (
  `deptno` int(11) NOT NULL,
  `dname` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
  
mysql> show create table emp\G
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `empno` int(11) NOT NULL,
  `ename` char(20) DEFAULT NULL,
  `deptno` int(11) DEFAULT NULL,
  PRIMARY KEY (`empno`),
  KEY `__emp_ibfk_1` (`deptno`),
  CONSTRAINT `__emp_ibfk_1` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)