业务需求:
在线修改一张2000W行表的字段长度。
[root@21db01 ~]# pt-online-schema-change --alter="modify id VARCHAR(64) CHARSET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0' COMMENT 'ID';" --user=root --password=XXXX --host='127.0.0.1' D=users,t=users_dt --charset=utf8 --execute --no-check-alter --no-check-replication-filters
Cannot connect to A=utf8,D=users,h=10.1.4.80,p=...,u=root
Cannot connect to A=utf8,D=users,h=10.1.4.82,p=...,u=root
Cannot connect to A=utf8,D=users,h=10.1.6.7,p=...,u=root
Cannot connect to A=utf8,D=users,h=10.1.4.97,p=...,u=root
Cannot connect to A=utf8,D=users,h=10.1.6.8,p=...,u=root
Cannot connect to A=utf8,D=users,h=10.1.4.81,p=...,u=root
Cannot connect to A=utf8,D=users,h=10.1.4.79,p=...,u=root
No slaves found. See --recursion-method if host 21db01 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 `users`.`users_dt`...
Creating new table...
Created new table users._users_dt_new OK.
Altering new table...
Altered `users`.`_users_dt_new` OK.
2020-09-21T14:17:27 Creating triggers...
2020-09-21T14:17:27 Created triggers OK.
2020-09-21T14:17:27 Copying approximately 26345605 rows...
Copying `users`.`users_dt`: 0% 01:12:38 remain
Copying `users`.`users_dt`: 1% 01:13:24 remain
Copying `users`.`users_dt`: 1% 01:20:53 remain
Copying `users`.`users_dt`: 2% 01:27:24 remain
Copying `users`.`users_dt`: 2% 01:31:22 remain
Copying `users`.`users_dt`: 3% 01:32:27 remain
Copying `users`.`users_dt`: 10% 01:58:39 remain
Copying `users`.`users_dt`: 10% 01:58:30 remain
Copying `users`.`users_dt`: 10% 01:59:12 remain
Copying `users`.`users_dt`: 46% 01:20:35 remain
Copying `users`.`users_dt`: 46% 01:20:22 remain
Copying `users`.`users_dt`: 83% 24:06 remain
Copying `users`.`users_dt`: 98% 01:23 remain
Copying `users`.`users_dt`: 99% 00:37 remain
Copying `users`.`users_dt`: 99% 00:03 remain
2020-09-21T16:49:13 Copied rows OK.
2020-09-21T16:49:13 Analyzing new table...
2020-09-21T16:49:13 Swapping tables...
2020-09-21T16:49:13 Swapped original and new tables OK.
2020-09-21T16:49:13 Dropping old table...
2020-09-21T16:49:50 Dropped old table `users`.`_users_dt_old` OK.
2020-09-21T16:49:50 Dropping triggers...
2020-09-21T16:49:50 Dropped triggers OK.
Successfully altered `users`.`users_dt`.
总共耗时2小时多,普通磁盘。