MYSQL8 pt-online-schema-change在线修改大表结构--附带Shell脚本实战

一、背景

在生产上对6400万数据的大表修改了表结构，记录一下当时的处理流程，下面我会带大家一步步操作当时的步骤

二、在线修改大表的可能影响

• 在线修改大表的表结构执行时间往往不可预估，一般时间较长
• 由于修改表结构是表级锁，因此在修改表结构时，影响表写入操作
• 如果长时间的修改表结构，中途修改失败，由于修改表结构是一个事务，因此失败后会还原表结构，在这个过程中表都是锁着不可写入
• 修改大表结构容易导致数据库CPU、IO等性能消耗，使MySQL服务器性能降低
• 在线修改大表结构容易导致主从延时，从而影响业务读取（重要，这里锁表了4分钟）

三、pt-online-schema-change介绍

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

原理

• 首先它会新建一张一模一样的表，表名一般是_new后缀
• 然后在这个新表执行更改字段操作
• 然后在原表上加三个触发器，DELETE/UPDATE/INSERT，将原表中要执行的语句也在新表中执行
• 最后将原表的数据拷贝到新表中，然后替换掉原表

四、pt-online-schema-change安装

1. 去官网下载对应的版本，官网下载地址：点击进入官网
2. 该工具需要一些依赖包，直接执行不成功时一般会有提示，这里可以提前yum安装

yum install perl-DBI
yum install perl-DBD-MySQL
yum install perl-Time-HiRes
yum install perl-IO-Socket-SSL
yum install perl-TermReadKey
1
2
3
4
5

3. 服务器执行wget下载，如下所示：

wget https://downloads.percona.com/downloads/percona-toolkit/3.4.0/binary/redhat/8/x86_64/percona-toolkit-3.4.0-3.el8.x86_64.rpm&&rpm -ivh percona-toolkit-3.4.0-3.el8.x86_64.rpm
1

五、pt-online-schema-change使用

1. 参数

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

2. 为避免每次都输入一堆参数，使用Shell来调用，脚本如下：

#!/bin/bash

# use gh-ost to migrate tables with target table file 

function print(){
  echo "`date +\"%Y-%m-%d %H:%M:%S\"` -- " $1
}

function help(){
 echo "    bash $0 -T[file stores the target tables] -u[user] -p[passowrd] -P[dbport] -h[dbhost] -a[action:check|run_gh-ost|run_pt] -t[table_prefix] -d[database] -S[excutel_sql][action=check not required] -D[debug_mode]"
}

function is_continue(){
  read -p "Press [y] to continue..." go_on
  if [[ "${go_on}"x != "y"x ]];then print "user stop" && exit 1;fi
}

## parse command line option
while getopts 'T:u:h:p:P:a:t:d:S:D' c
do
  case $c in
    T) tfile=${OPTARG};;
    u) user=${OPTARG};;
    p) password=${OPTARG};;
    h) host=${OPTARG};;
    P) port=${OPTARG};;
    a) action=${OPTARG};;
    t) table_prefix=${OPTARG};;
    d) database=${OPTARG};;
    S) sql=${OPTARG};;
    D) debug=true;;
  esac
done

## check input params
if [[ $# -lt 3 ]];then
  help
  exit 1
fi


## set default value
tfile=${tfile-"t"}
user=${user-"root"}
password=${password-"root"}
host=${host-"127.0.0.1"}
port=${port-3306}
action=${action-"check"}
table_prefix=${table_prefix-"t"}
database=${database-"t"}
sql=${sql-"select version()"}

print "input params: tfile=$tfile, user=$user, password=$password, host=$host, port=$port, action=$action, table_prefix=$table_prefix, database=$database"
print "input params: sql=$sql"
if [[ $debug ]];then
  read -p "Press [y] to confirm..." go_on
  if [[ "${go_on}"x != "y"x ]];then print "user stop" && exit 1;fi
fi
## do actually work
count=0
cat $tfile|while read TABLE
do
count=`echo $count+1|bc`

if [[ "$action"x == "run_gh-ost"x ]];then
  COMMAND="\
./gh-ost --max-load=Threads_connected=1000  --critical-load=Threads_connected=4000  --chunk-size=10000  --max-lag-millis=1500  --initially-drop-ghost-table --initially-drop-socket-file  --ok-to-drop-table "\
"--verbose --aliyun-rds=true --allow-on-master  --execute --allow-nullable-unique-key --cut-over-lock-timeout-seconds=1200 --default-retries=10000 "\
"--host=$host "\
"--port=$port "\
"--user=$user "\
"--password=${password} "\
"--database=$database "\
"--table=$TABLE "\
"--alter=\"$sql\""

  print "running gh-ost with params:"
  echo "$COMMAND"
  #if [[ $debug ]];then
  #  read -p "Press [y] to continue..." go_on
  #  if [[ "${go_on}"x != "y"x ]];then print "user stop" && exit 1;fi
  #fi
  ./gh-ost --max-load=Threads_connected=1000  --critical-load=Threads_connected=4000  --chunk-size=10000  --max-lag-millis=1500  --initially-drop-ghost-table --initially-drop-socket-file  --ok-to-drop-table \
  --verbose --aliyun-rds=true --allow-on-master  --execute --allow-nullable-unique-key --cut-over-lock-timeout-seconds=1200 --default-retries=10000 --verbose \
  --host=$host \
  --port=$port \
  --user=$user \
  --password="${password}" \
  --database="${database}" \
  --table=$TABLE \
  --alter="$sql" 
elif [[ "${action}"x == "run_pt"x ]];then
  COMMAND="\
pt-online-schema-change  --max-load=Threads_running=70 --critical-load=Threads_running=120 --max-lag=50  --check-interval=2 --chunk-size=300 --charset=utf8mb3\
D=$database,t=$TABLE,P=$port,h=$host,u=$user,p="${password}" \
--alter=\"$sql\" \
--execute"
  print "running pt-online with params:"
  echo "$COMMAND"
  # is_continue

pt-online-schema-change  --max-load=Threads_running=70 --critical-load=Threads_running=120 --max-lag=50  --check-interval=2 --chunk-size=300 --charset=utf8mb3 --recursion-method=none \
D=$database,t=$TABLE,P=$port,h=$host,u=$user,p="${password}" \
--alter="$sql" \
--execute | tee -a running.log

elif [[ "${action}"x == "check"x ]];then
  sql="desc $TABLE"
  COMMAND="mysql -h$host -P$port -u$user -p\"$password\"   $databases  -A -e \"$sql\""
  echo "before alter: "
  mysql -h$host -P$port -u$user -p"$password"  $database  -A -e "$sql"

  pt-online-schema-change  --max-load=Threads_running=70 --critical-load=Threads_running=120 --max-lag=50  --check-interval=2 --chunk-size=300 \
D=$database,t=$TABLE,P=$port,h=$host,u=$user,p="${password}" \
--alter="$sql" \
--dry-run |tee -a running.log
  echo "before alter: after alter"
  mysql -h$host -P$port -u$user -p"$password"  $database  -A -e "$sql"

  # check option exit on the first time
  exit 0
else:
  echo "unknown action=${action}, exists"
fi
done
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125

测试数据表结构如下所示：

mysql> show create table test2;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                        |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test2 | CREATE TABLE `test2` (
  `phoneno` varchar(30) NOT NULL,
  `product_id` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`phoneno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from test2;
+----------+
| count(*) |
+----------+
|  3243582 |
+----------+
1 row in set (0.15 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

3. 开始执行

nohup ./pt_db.sh -T tables.txt  -hlocalhost  -P3306 -uroot -p'gbase' -d test -a run_pt -S "modify column phoneno varchar(40);" &
1

日志如下所示：

[root@hadoop1 ~]# tail -f nohup.out 
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `test`.`test2`...
Creating new table...
Created new table test._test2_new OK.
Altering new table...
Altered `test`.`_test2_new` OK.
2022-09-14T13:58:08 Creating triggers...
2022-09-14T13:58:08 Created triggers OK.
2022-09-14T13:58:08 Copying approximately 3234777 rows...
Copying `test`.`test2`:  21% 01:47 remain
Copying `test`.`test2`:  44% 01:13 remain
Copying `test`.`test2`:  67% 00:42 remain
Copying `test`.`test2`:  90% 00:12 remain
2022-09-14T14:00:20 Copied rows OK.
2022-09-14T14:00:20 Analyzing new table...
2022-09-14T14:00:20 Swapping tables...
2022-09-14T14:00:20 Swapped original and new tables OK.
2022-09-14T14:00:20 Dropping old table...
2022-09-14T14:00:20 Dropped old table `test`.`_test2_old` OK.
2022-09-14T14:00:20 Dropping triggers...
2022-09-14T14:00:20 Dropped triggers OK.
Successfully altered `test`.`test2`.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

修改成功：

mysql> show create table test2;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                        |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test2 | CREATE TABLE `test2` (
  `phoneno` varchar(40) NOT NULL,
  `product_id` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`phoneno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

六、执行遇到的问题

第一次执行报错了

Cannot chunk the original table `test`.`test2`: There is no good index and the table is oversized. at /usr/bin/pt-online-schema-change line 6012.
1

增加索引后解决：

 alter table test2 add primary key(phoneno);
1

七、总结

表数据量 6400w ，变更时长：1h23m， 锁表时间4分钟，大家一定要注意主从同步的延迟时间！！！

文档
1.官方参考：https://docs.percona.com/percona-toolkit/pt-online-schema-change.html
2.参考文章：https://segmentfault.com/a/1190000014924677