关于mysql字符集转换

mysql

前因

随着emoji表情的普遍使用，mysql utf8已经不能满足需求了，MySQL在5.5.3版本之后，引进了新的字符编码utf8mb4，今天我们就来说说，如何将已经是utf8的database切换到utf8mb4字符编码。
维护过mysql的人都知道，数据库操作里面，DDL操作（比如CREATE,DROP,ALTER等）代价是非常高的，特别是在单表上千万的情况下，加个索引或改个列类型，就有可能堵塞整个表的读写。
然而从mysql 5.6 开始，大家期待的Online DDL出现了，可以实现修改表结构的同时，依然允许DML操作(select,insert,update,delete)。
那么以前对于DDL的操作，mysql是如果运行的了？
主要过程如下：
1、首先新建Temp table，表结构是 ALTAR TABLE 新定义的结构
2、然后把原表中数据导入到这个Temp table
3、删除原表
4、最后把临时表rename为原来的表名
为了保持数据的一致性，中间复制数据（Copy Table）全程锁表只读，如果有写请求进来将无法提供服务，连接数爆张。
但是我们通过阅读官方文档介绍的时候，并不是所有的DDL操作都可以允许DML的并发，实际上修改列数据类型、删除主键、变更表字符集，即这些类型操作DDL是不能online的。

既然用不了新特性，那我们还是回归到原来的方式，其实在mysql online ddl出现以前，大家用的更多的工具是percona提供的工具pt-online-schema-change简称pt-osc。

pt-online-schema-change

pt-psc是percona-toolkit提供的系列工具之一，所以我们首先得安装percona-toolkit

原理

pt-online-schema-change通过改进原生ddl的方式，达到不锁表在线修改表结构。
工作原理如下：
1、创建一个和要执行 alter 操作的表一样的新的空表结构(是alter之前的结构)
2、在新表执行alter table 语句（速度应该很快）
3、在原表中创建触发器3个触发器分别对应insert,update,delete操作
4、以一定块大小从原表拷贝数据到临时表，拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表
5、Rename 原表到old表中，在把临时表Rename为原表
6、如果有参考该表的外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理
7、默认最后将旧原表删除

常用参数

--user:
-u,连接的用户名
--password：
-p,连接的密码
--database：
-D,连接的数据库
--port:
-P,连接数据库的端口
--host:
-h，连接的主机地址
--socket:
-S，连接的套接字文件
--ask-pass 
隐式输入连接MySQL的密码
--charset
指定修改的字符集
--defaults-file 
-F，读取配置文件
--alter
结构变更语句，不需要 ALTER TABLE关键字。与原始ddl一样可以指定多个更改，用逗号分隔。
绝大部分情况下表上需要有主键或唯一索引，因为工具在运行当中为了保证新表也是最新的，需要旧表上创建 DELETE和UPDATE 触发器，同步到新表的时候有主键会更快。个别情况是，当alter操作就是在c1列上建立主键时，DELETE触发器将基于c1列。
子句不支持 rename 去给表重命名。
alter命令原表就不支持给索引重命名，需要先drop再add，在pt-osc也一样。(mysql 5.7 支持 RENAME INDEX old_index_name TO new_index_name)
--alter-foreign-keys-method
如何把外键引用到新表?需要特殊处理带有外键约束的表,以保证它们可以应用到新表.当重命名表的时候,外键关系会带到重命名后的表上。
该工具有两种方法,可以自动找到子表,并修改约束关系。
auto： 在rebuild_constraints和drop_swap两种处理方式中选择一个。
rebuild_constraints：使用 ALTER TABLE语句先删除外键约束,然后再添加.如果子表很大的话,会导致长时间的阻塞。
drop_swap： 执行FOREIGN_KEY_CHECKS=0,禁止外键约束,删除原表,再重命名新表。这种方式很快,也不会产生阻塞,但是有风险：
1, 在删除原表和重命名新表的短时间内,表是不存在的,程序会返回错误。
2, 如果重命名表出现错误,也不能回滚了.因为原表已经被删除。
none： 类似"drop_swap"的处理方式,但是它不删除原表,并且外键关系会随着重命名转到老表上面。
但给字段重命名，千万不要drop-add，整列数据会丢失，使用change col1 col1_new type constraint（保持类型和约束一致，否则相当于修改 column type，不能online）
子句如果是add column并且定义了not null，那么必须指定default值，否则会失败。
如果要删除外键（名 fk_foo），使用工具的时候外键名要加下划线，比如--alter "DROP FOREIGN KEY _fk_foo"
--[no]check-replication-filters 
默认yes。如果工具检测到服务器选项中有任何复制相关的筛选，如指定binlog_ignore_db和replicate_do_db此类。发现有这样的筛选，工具会报错且退出。因为如果更新的表Master上存在，而Slave上不存在，会导致复制的失败。使用–no-check-replication-filters选项来禁用该检查。
--default-engine
默认情况下，新的表与原始表是相同的存储引擎，所以如果原来的表使用InnoDB的，那么新表将使用InnoDB的。在涉及复制某些情况下，很可能主从的存储引擎不一样。使用该选项会默认使用默认的存储引擎。
--dry-run
创建和修改新表，但不会创建触发器、复制数据、和替换原表。并不真正执行，可以看到生成的执行语句，了解其执行步骤与细节。--dry-run与--execute必须指定一个，二者相互排斥。和--print配合最佳。
--execute
确定修改表，则指定该参数。真正执行。--dry-run与--execute必须指定一个，二者相互排斥。
--print
打印SQL语句到标准输出。指定此选项可以让你看到该工具所执行的语句，和--dry-run配合最佳。
--progress
复制数据的时候打印进度报告，二部分组成：第一部分是百分比，第二部分是时间。
--quiet
-q，不把信息标准输出。

更过详细参数请看官方文档