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

阳光穿透心脏的1/2处 2023-02-20 07:07 17阅读 0赞

# 使用场景 #

*  在线数据库的维护中，总会涉及到研发修改表结构的情况，修改一些小表影响很小，而修改大表时，往往影响业务的正常运转，如表数据量超过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...][https_www.percona.com_downl...]

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

![clipboard.png][]

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

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

# 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-2.2.19/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)"

# 其他 #

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

# 文档 #

1.官方参考:[https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html][https_www.percona.com_doc_percona-toolkit_3.0_pt-online-schema-change.html]  
2.官方下载:[https://www.percona.com/downloads/percona-toolkit/LATEST/][https_www.percona.com_downl...]

文章来源：[https://segmentfault.com/a/1190000014924677][https_segmentfault.com_a_1190000014924677]

相关文章：[几千万记录，数据库表结构如何平滑变更？][Link 1]

[https_www.percona.com_downl...]: https://www.percona.com/downloads/percona-toolkit/LATEST/
[clipboard.png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9zZWdtZW50ZmF1bHQuY29tL2ltZy9iVmJhSkNn?x-oss-process=image/format,png
[https_www.percona.com_doc_percona-toolkit_3.0_pt-online-schema-change.html]: https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html
[https_segmentfault.com_a_1190000014924677]: https://segmentfault.com/a/1190000014924677
[Link 1]: https://zhuanlan.51cto.com/art/201909/603340.htm