pt-online-schema-change工具的一个选项
使用pt-online-schema-change变更数据表某字段长度, 完成后, 查看表结构定义, 却出现了乱码...
数据表zzzz的表结构.
mysql> show create table zzzz \G
*************************** 1. row ***************************
Table: zzzz
Create Table: CREATE TABLE `zzzz` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL DEFAULT '' COMMENT '物品名称',
`category` varchar(10) NOT NULL DEFAULT '' COMMENT '物品分类',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Product infomation'
1 row in set (0.00 sec)
变更字段name的长度为20.
$ pt-online-schema-change --alter "modify name varchar(20) not null default '' comment '物品名称'" --nocheck-replication-filters --recursion-method=processlist h=192.168.4.45,P=3316,u=mha_mgr,p=123456,D=test,t=zzzz --execute
Found 1 slaves:
db02 -> 192.168.4.46:3316
Will check slave lag on:
db02 -> 192.168.4.46:3316
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`.`zzzz`...
Creating new table...
Created new table test._zzzz_new OK.
Waiting forever for new table `test`.`_zzzz_new` to replicate to db02...
Altering new table...
Altered `test`.`_zzzz_new` OK.
2018-01-15T17:31:27 Creating triggers...
2018-01-15T17:31:27 Created triggers OK.
2018-01-15T17:31:27 Copying approximately 1 rows...
2018-01-15T17:31:28 Copied rows OK.
2018-01-15T17:31:28 Analyzing new table...
2018-01-15T17:31:28 Swapping tables...
2018-01-15T17:31:28 Swapped original and new tables OK.
2018-01-15T17:31:28 Dropping old table...
2018-01-15T17:31:28 Dropped old table `test`.`_zzzz_old` OK.
2018-01-15T17:31:28 Dropping triggers...
2018-01-15T17:31:29 Dropped triggers OK.
Successfully altered `test`.`zzzz`.
完成后, 查看表结构, 发现字段的中文注释都成了乱码.
mysql> show create table zzzz \G
*************************** 1. row ***************************
Table: zzzz
Create Table: CREATE TABLE `zzzz` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL DEFAULT '' COMMENT '?‰??"??§°',
`category` varchar(10) NOT NULL DEFAULT '' COMMENT '????',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Product infomation'
1 row in set (0.00 sec)
解决方法, 使用选项--charset(其文档说明: Default character set. If the value is utf8, sets Perl's binmode on STDOUT to utf8, passes the mysql_enable_utf8 option to DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. Any other value sets binmode on STDOUT without the utf8 layer, and runs SET NAMES after connecting to MySQL.), 指明字符集为utf8mb4.
$ pt-online-schema-change --alter "modify name varchar(20) not null default '' comment '物品名称'" --charset=utf8mb4 --nocheck-replication-filters --recursion-method=processlist h=192.168.4.45,P=3316,u=mha_mgr,p=123456,D=test,t=zzzz --execute
瞧, 正常了.
mysql> show create table zzzz \G
*************************** 1. row ***************************
Table: zzzz
Create Table: CREATE TABLE `zzzz` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL DEFAULT '' COMMENT '物品名称',
`category` varchar(10) NOT NULL DEFAULT '' COMMENT '????',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Product infomation'
1 row in set (0.00 sec)
关于pt-online-schema-change工具的更多使用细节, 可参考InnoDB Online DDL续.
