表的数据比较大时,如果直接修改会阻塞表,影响业务数据。有两个工具可以实现:
pt-online-schema-changegh-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)
暂无评论