percona-toolkit之pt-online-schema-change(在线更改表结构)

时间:2022-03-19 18:30:58

        传统方法修改表结构 类似alter table xx modify,在修改表结构时需要锁表,如果表很大,则操作时间会较长。目前,绝大多数业务要求24*7无间断服务,而此过程中,如果造成较长时间数据库无法更新,则会严重影响业务。最近了解到两种可行的方案:1.替换表结构方法,2.采用percona在线修改表结构工具

模拟场景说明:

mysql5.6.25(mysql5.6相对于mysql5.5已经改进很多了,在mysql5.6中参考了percona-toolkit,增加删除索引不会锁表,同一个session里面,增删查改也不受影响,但是不同session是会受影响的。)

在线修改表结构肯定会对实时任务有所影响,作为DBA,最应该做的就是将影响尽可能降低。下面来做个简单的测试。

表test1,结构如下:主要三个字段(主键id,name,age)

 CREATE TABLE `test1` (

  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 

将表中插入100万条数据

mysqlslap -uroot  --number-of-queries=1000000 -c 20 --query="insert into test.test1(name,age) values('fds',21)" --create-schema='test'

实验开始

mysql> select count(*) from test1;
+----------+
| count(*) |
+----------+
|  1000002 |
+----------+

(之前自己插了两条数据的哈)

1.替换表结构方法

给个简单的修改表属性例子

步骤:

①创建一个类似的tmp表,修改对应列属性

②修改表结构必须锁表,执行flush table with readlock

③用临时表结构文件覆盖原来表结构文件,然后释放表所,unlocktables

④插入发现依旧有问题,那是因为没刷新表,flush tables

⑤显示修改后表结构

⑥插入数据无异常

percona-toolkit之pt-online-schema-change(在线更改表结构)

注意:必须是innodb表,myisam的不行哦;改操作对varchar型有效,对其他类型是否有效不一定哦。例如:decimal

优点是:只有在copy表结构的时候需要锁表

2.采用percona工具处理

执行修改表的语句

mysql> alter table test1 add column nickname varchar(10) default '';
Query OK, 0 rows affected (12.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

可以看到操作时间还是有点长的,毕竟我只有100万数据,而且表结构简单。对于percona工具,优点在哪呢,可以先看下操作的流程如下图所示:

percona-toolkit之pt-online-schema-change(在线更改表结构)
可以看出来,在其操作过程中是有创建临时表的,看这个处理时间并不优于之前的,但请看下面:

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.

再给个实验结果:

实验步骤

1.采用两种方式修改表属性:直接修改和用percona工具修改

2.在修改表属性的同时,开另外一个session往表里面插入数据

结果如下:

直接修改

session1:

mysql> alter table test1 modify column nickname varchar(15);
Query OK, 1000002 rows affected (5.98 sec)
Records: 1000002  Duplicates: 0  Warnings: 0

session2:

mysql> insert into test1(name,age,nickname) values('fds',21,'fds');

Query OK, 1 row affected (4.47 sec)

工具修改:

session1:

vagrant@testda:~$ pt-online-schema-change --alter="modify column nickname varchar(20) default ''" --user=root D=test,t=test1 --execute
Cannot connect to D=test,h=testdp,u=root
No slaves found.  See --recursion-method if host testda 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 `test`.`test1`...
Creating new table...
Created new table test._test1_new OK.
Altering new table...
Altered `test`.`_test1_new` OK.
2015-10-15T02:46:50 Creating triggers...
2015-10-15T02:46:50 Created triggers OK.
2015-10-15T02:46:50 Copying approximately 998585 rows...
2015-10-15T02:47:04 Copied rows OK.
2015-10-15T02:47:04 Swapping tables...
2015-10-15T02:47:04 Swapped original and new tables OK.
2015-10-15T02:47:04 Dropping old table...
2015-10-15T02:47:04 Dropped old table `test`.`_test1_old` OK.
2015-10-15T02:47:04 Dropping triggers...
2015-10-15T02:47:04 Dropped triggers OK.
Successfully altered `test`.`test1`.

session2:

insert into test1(name,age,nickname) values('fds',21,'fds');
Query OK, 1 row affected, 1 warning (0.32 sec)


可以看出使用工具修改过程中数据插入并未被阻塞,而直接修改因为锁表了,所以插入操作遇到阻塞


具体可以参考下:https://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html


注意:从上面说明来看,该工具很好,但也并不是没有缺点,①如果修改的表没有主键,则会报错②该操作会创建临时表,如果存在主从复制,而且设定的主从复制表,那么会导致从库同步失败,原因是没有同步临时表③如果字段为not null,但没给default值,也会报错④改工具不是mysql官方工具,所以操作时建议备份,避免意外