# 在线修改表结构DDL
表的数据比较大时，如果直接修改会阻塞表，影响业务数据。有两个工具可以实现：
pt-online-schema-change 和 gh-ost
这次使用pt-online-schema-change
参考：https://segmentfault.com/a/1190000014924677
# 一.安装
进入官网选择好对应的版本，下载到服务器
https://www.percona.com/downloads/percona-toolkit/LATEST/
本次是Ubuntu 16.04
下载完成后安装下
dpkg -i percona-toolkit_3.1.0-2.xenial_amd64.deb
apt-get install percona-toolkit
apt-get -f install
# 二.使用
参考选项
--user= 连接mysql的用户名
--password= 连接mysql的密码
--host= 连接mysql的地址
P=3306 连接mysql的端口号
D= 连接mysql的库名
t= 连接mysql的表名
--alter 修改表结构的语句
--execute 执行修改表结构
--charset=utf8 使用utf8编码，避免中文乱码
--no-version-check 不检查版本，在阿里云服务器中一般加入此参数，否则会报错
写成一个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
本次是增加一个索引，表有2千万行，执行了大概20分钟，由于不影响在线业务，完全可以接受。 执行如下命令
sh pt-online.sh custom_game_score 'ADD INDEX IDX_S_USER_ID(user_id)'
结果
custom_game_score
ADD INDEX IDX_S_USER_ID(user_id)
Cannot connect to A=utf8mb4,P=3017,h=,p=...,u=yewu_custom
No slaves found. See --recursion-method if host h=abc.mysql.rds.aliyuncs.com,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 `yewu`.`custom_game_score`...
Creating new table...
Created new table yewu._custom_game_score_new OK.
Altering new table...
Altered `yewu`.`_custom_game_score_new` OK.
2019-10-29T19:50:33 Creating triggers...
2019-10-29T19:50:33 Created triggers OK.
2019-10-29T19:50:33 Copying approximately 27589521 rows...
Copying `yewu`.`custom_game_score`: 3% 14:17 remain
Copying `yewu`.`custom_game_score`: 6% 13:36 remain
Copying `yewu`.`custom_game_score`: 9% 13:42 remain
Copying `yewu`.`custom_game_score`: 12% 13:34 remain
Copying `yewu`.`custom_game_score`: 15% 13:11 remain
Copying `yewu`.`custom_game_score`: 18% 12:50 remain
Copying `yewu`.`custom_game_score`: 21% 12:31 remain
Copying `yewu`.`custom_game_score`: 24% 12:19 remain
Copying `yewu`.`custom_game_score`: 27% 12:07 remain
Copying `yewu`.`custom_game_score`: 29% 11:42 remain
Copying `yewu`.`custom_game_score`: 32% 11:20 remain
Copying `yewu`.`custom_game_score`: 35% 10:59 remain
Copying `yewu`.`custom_game_score`: 37% 10:37 remain
Copying `yewu`.`custom_game_score`: 40% 10:11 remain
Copying `yewu`.`custom_game_score`: 43% 09:41 remain
Copying `yewu`.`custom_game_score`: 46% 09:16 remain
Copying `yewu`.`custom_game_score`: 49% 08:46 remain
Copying `yewu`.`custom_game_score`: 52% 08:10 remain
Copying `yewu`.`custom_game_score`: 55% 07:41 remain
Copying `yewu`.`custom_game_score`: 58% 07:12 remain
Copying `yewu`.`custom_game_score`: 60% 06:42 remain
Copying `yewu`.`custom_game_score`: 63% 06:15 remain
Copying `yewu`.`custom_game_score`: 66% 05:49 remain
Copying `yewu`.`custom_game_score`: 69% 05:19 remain
Copying `yewu`.`custom_game_score`: 72% 04:48 remain
Copying `yewu`.`custom_game_score`: 75% 04:18 remain
Copying `yewu`.`custom_game_score`: 77% 03:51 remain
Copying `yewu`.`custom_game_score`: 80% 03:22 remain
Copying `yewu`.`custom_game_score`: 83% 02:55 remain
Copying `yewu`.`custom_game_score`: 85% 02:27 remain
Copying `yewu`.`custom_game_score`: 88% 01:56 remain
Copying `yewu`.`custom_game_score`: 91% 01:27 remain
Copying `yewu`.`custom_game_score`: 94% 00:58 remain
Copying `yewu`.`custom_game_score`: 97% 00:31 remain
Copying `yewu`.`custom_game_score`: 99% 00:03 remain
2019-10-29T20:08:46 Copied rows OK.
2019-10-29T20:08:46 Analyzing new table...
2019-10-29T20:08:46 Swapping tables...
2019-10-29T20:08:46 Swapped original and new tables OK.
2019-10-29T20:08:46 Dropping old table...
2019-10-29T20:08:47 Dropped old table `yewu`.`_custom_game_score_old` OK.
2019-10-29T20:08:47 Dropping triggers...
2019-10-29T20:08:47 Dropped triggers OK.
Successfully altered `yewu`.`custom_game_score`.
# 三.triggers触发器问题
由于pt-online-schema-change需要一个自己的触发器，如果表中有触发器了，则不能使用pt-online-schema-change。
处理方法有两个：
1.删除触发器
2.使用其他工具，比如gh-ost
查看一下这个表的触发器
mysql> show triggers like '%custom_game_score%';
+-----------------+--------+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+----------+------------+----------------------+----------------------+--------------------+
| Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation |
+-----------------+--------+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+----------+------------+----------------------+----------------------+--------------------+
| GAME_SCORE_BACK | DELETE | custom_game_score | insert into yewu_bak.custom_game_score (id, user_id, game_id, last_score, score, times, device_type, create_time)
values (old.id, old.user_id, old.game_id, old.last_score, old.score, old.times, old.device_type, old.create_time) | AFTER | NULL | | yewu@% | utf8 | utf8_general_ci | utf8mb4_general_ci |
+-----------------+--------+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+----------+------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
发现是备份数据到另一个库，其实完全不用，故删除之
mysql> DROP TRIGGER GAME_SCORE_BACK;
Query OK, 0 rows affected (0.00 sec)