linux

在线修改大表结构pt-online-schema-change

发布时间:5年前热度: 5886 ℃评论数:

使用场景


在线数据库的维护中,总会涉及到研发修改表结构的情况,修改一些小表影响很小,而修改大表时,往往影响业务的正常运转,如表数据量超过500W,1000W,甚至过亿时

在线修改大表的可能影响

在线修改大表的表结构执行时间往往不可预估,一般时间较长

由于修改表结构是表级锁,因此在修改表结构时,影响表写入操作

如果长时间的修改表结构,中途修改失败,由于修改表结构是一个事务,因此失败后会还原表结构,在这个过程中表都是锁着不可写入

修改大表结构容易导致数据库CPU、IO等性能消耗,使MySQL服务器性能降低

在线修改大表结构容易导致主从延时,从而影响业务读取

pt-online-schema-change介绍

pt-online-schema-change是percona公司开发的一个工具,在percona-toolkit包里面可以找到这个功能,它可以在线修改表结构


原理:


首先它会新建一张一模一样的表,表名一般是_new后缀

然后在这个新表执行更改字段操作

然后在原表上加三个触发器,DELETE/UPDATE/INSERT,将原表中要执行的语句也在新表中执行

最后将原表的数据拷贝到新表中,然后替换掉原表

使用pt-online-schema-change执行SQL的日志

SQL语句:

ALTER TABLE tmp_task_user ADD support tinyint(1) unsigned NOT NULL DEFAULT '1';


sh pt.sh tmp_task_user "ADD COLUMN support tinyint(1) unsigned NOT NULL DEFAULT '1'"


tmp_task_user

ADD COLUMN support tinyint(1) unsigned NOT NULL DEFAULT '1'

No slaves found.  See --recursion-method if host h=127.0.0.1,P=3306 has slaves.

Not checking slave lag because no slaves were found and --check-slave-lag was not specified.

Operation, tries, wait:

  analyze_table, 10, 1

  copy_rows, 10, 0.25

  create_triggers, 10, 1

  drop_triggers, 10, 1

  swap_tables, 10, 1

  update_foreign_keys, 10, 1

Altering `test_db`.`tmp_task_user`...

Creating new table...

Created new table test_db._tmp_task_user_new OK.

Altering new table...

Altered `test_db`.`_tmp_task_user_new` OK.

2018-05-14T18:14:21 Creating triggers...

2018-05-14T18:14:21 Created triggers OK.

2018-05-14T18:14:21 Copying approximately 6 rows...

2018-05-14T18:14:21 Copied rows OK.

2018-05-14T18:14:21 Analyzing new table...

2018-05-14T18:14:21 Swapping tables...

2018-05-14T18:14:21 Swapped original and new tables OK.

2018-05-14T18:14:21 Dropping old table...

2018-05-14T18:14:21 Dropped old table `test_db`.`_tmp_task_user_old` OK.

2018-05-14T18:14:21 Dropping triggers...

2018-05-14T18:14:21 Dropped triggers OK.

Successfully altered `test_db`.`tmp_task_user`.

好处:


降低主从延时的风险

可以限速、限资源,避免操作时MySQL负载过高

建议:


在业务低峰期做,将影响降到最低

pt-online-schema-change安装

1.去官网下载对应的版本,官网下载地址:https://www.percona.com/downl...


2.下载解压之后就可以看到pt-online-schema-change

2.png

3.该工具需要一些依赖包,直接执行不成功时一般会有提示,这里可以提前yum安装


yum install -y perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-Digest-MD5


pt-online-schema-change使用

1.参数

./bin/pt-online-schema-change --help 可以查看参数的使用,我们只是要修改个表结构,只需要知道几个简单的参数就可以了


--user=        连接mysql的用户名

--password=    连接mysql的密码

--host=        连接mysql的地址

P=3306         连接mysql的端口号

D=             连接mysql的库名

t=             连接mysql的表名

--alter        修改表结构的语句

--execute      执行修改表结构

--charset=utf8 使用utf8编码,避免中文乱码

--no-version-check  不检查版本,在阿里云服务器中一般加入此参数,否则会报错

2.为避免每次都要输入一堆参数,写个脚本复用一下,pt.sh


#!/bin/bash

table=$1

alter_conment=$2


cnn_host='127.0.0.1'

cnn_user='user'

cnn_pwd='password'

cnn_db='database_name'


echo "$table"

echo "$alter_conment"

/root/percona-toolkit-3.2.0/bin/pt-online-schema-change --charset=utf8 --no-version-check --user=${cnn_user} --password=${cnn_pwd} --host=${cnn_host}  P=3306,D=${cnn_db},t=$table --alter 

"${alter_conment}" --execute

3.添加表字段

如添加表字段SQL语句为:

ALTER TABLE tb_test ADD COLUMN column1 tinyint(4) DEFAULT NULL;

那么使用pt-online-schema-change则可以这样写

sh pt.sh tb_test "ADD COLUMN column1 tinyint(4) DEFAULT NULL"


4.修改表字段

SQL语句:

ALTER TABLE tb_test MODIFY COLUMN num int(11) unsigned NOT NULL DEFAULT '0';


pt-online-schema-change工具:

sh pt.sh tb_test "MODIFY COLUMN num int(11) unsigned NOT NULL DEFAULT '0'"


5.修改表字段名

SQL语句:

ALTER TABLE tb_test CHANGE COLUMN age adress varchar(30);


pt-online-schema-change工具:

sh pt.sh tb_test "CHANGE COLUMN age address varchar(30)"


6.添加索引

SQL语句:

ALTER TABLE tb_test ADD INDEX idx_address(address);

pt-online-schema-change工具:

sh pt.sh tb_test "ADD INDEX idx_address(address)"

7.多字段添加


#! /bin/bash

 

stime=`date +%s`

echo "增加字段开始测试时间为:`date +%H:%M:%S`"

 

pt-online-schema-change --alter 'ADD COLUMN col11 BIGINT(20) NULL DEFAULT -1 COMMENT "字段注释1" ,ADD COLUMN col2 INT(11) NULL DEFAULT -1 \

COMMENT "字段注释2" ,ADD COLUMN col3 INT(11) NULL DEFAULT -1 COMMENT "字段注释3";' \

--host=127.0.0.1 --user=root --password=mysqlpass --port=3306 D=db_order,t=tb_tablename \

--charset=utf8mb4 --no-check-replication-filters --max-lag 3 --check-interval 3 \

--chunk-size 1M --set-vars innodb_lock_wait_timeout=10,lock_wait_timeout=60 \

--tries create_triggers:10:1,drop_triggers:10:1,copy_rows:10:1,swap_tables:10:1 \

--recursion-method "dsn=D=test,t=dsns" --max-load Threads_running=25 --execute --statistics

 

etime=`date +%s`

 

aa=`expr $etime - $stime`

echo "结束时间为:`date +%H:%M:%S`"

echo "增加字段测试所需要的时间为:$aa"


其他

pt-online-schema-change工具还有很多其他的参数,可以有很多限制,比如限制CPU、线程数量、从库状态等等,不过我做过一个超过6000W表的结构修改,发现几乎不影响性能,很稳定很流畅的就修改了表结构,所以,对以上常规参数的使用基本能满足业务

一定要在业务低峰期做,这样才能确保万无一失

手机扫码访问