在线修改数据库表的编码格式

mrnx2004
1,933 阅读4分钟

pt-online-schema-change works by creating an empty copy of the table to alter, modifying it as desired, and then copying rows from the original table into the new table. When the copy is complete, it moves away the original table and replaces it with the new one. By default, it also drops the original table.

传统修改大表可能碰到的问题

本章节参考文章：mysql在线修改表结构大数据表的风险与解决办法归纳

在线DDL操作的主要流程如下：

  1. 对表加锁(表此时只读)
  2. 复制原表物理结构
  3. 修改表的物理结构
  4. 把原表数据导入中间表中，数据同步完后，锁定中间表，并删除原表
  5. rename中间表为原表
  6. 刷新数据字典，并释放锁

这个过程需要锁表，可能会造成用户体验不佳。

解决方案

本章节参考文章:pt-online-schema-change介绍

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

其原理如下：

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

安装过程

去官网下载对应的版本，官网下载地址。

使用方法

  1. 参数 ./bin/pt-online-schema-change --help 可以查看参数的使用，我们只是要修改个表结构，只需要知道几个简单的参数就可以了
--user=        连接mysql的用户名
--password=    连接mysql的密码
--host=        连接mysql的地址
P=3306         连接mysql的端口号
D=             连接mysql的库名
t=             连接mysql的表名
--alter        修改表结构的语句
--execute      执行修改表结构
--charset=utf8 使用utf8编码，避免中文乱码
--no-version-check  不检查版本，在阿里云服务器中一般加入此参数，否则会报错
  1. 为避免每次都要输入一堆参数，写个脚本复用一下，pt.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
  1. 添加表字段 如添加表字段SQL语句为:
ALTER TABLE tb_test ADD COLUMN column1 tinyint(4) DEFAULT NULL;

那么使用pt-online-schema-change则可以这样写

sh pt.sh tb_test "ADD COLUMN column1 tinyint(4) DEFAULT NULL"
  1. 修改表字段 SQL语句：
ALTER TABLE tb_test MODIFY COLUMN num int(11) unsigned NOT NULL DEFAULT '0';

pt-online-schema-change工具:

sh pt.sh tb_test "MODIFY COLUMN num int(11) unsigned NOT NULL DEFAULT '0'"
  1. 修改表字段名 SQL语句:
ALTER TABLE tb_test CHANGE COLUMN age adress varchar(30);

pt-online-schema-change工具:

sh pt.sh tb_test "CHANGE COLUMN age address varchar(30)"

6.添加索引 SQL语句:

ALTER TABLE tb_test ADD INDEX idx_address(address);

pt-online-schema-change工具:

sh pt.sh tb_test "ADD INDEX idx_address(address)"

实际经验

参考文章：Mysql编码引起的两个问题
参考文章：修改Mysql字符集为utf8mb4

原有的项目编码设置为UTF8，所以某些生僻字无法正常保存到数据库中，但是需要修改编码的那张表的数据量大概有500W，直接在生产环境进行DDL操作可能导致网站卡死，影响用户体验。故采用pt-online-schema-change修改表的编码。

具体步骤：

  1. 修改数据库配置文件，将utf8的选项修改为utf8mb4，记得备份旧的配置文件。
  2. 重启mysql服务，我这边直接使用mysqladmin来shutdown，然后使用mysqld_safe启动mysql进程。
  3. 在mysql客户端中直接修改database的编码，经测试修改database的编码不会锁表。
  4. 使用pt-online-schema-change修改指定表的编码。
sh pt.sh tablename "convert to character set utf8mb4 collate utf8mb4_unicode_ci"

注意事项

依赖安装

我这边公司内网是离线环境，所以安装PT-online-schema-change会碰到缺少依赖的问题。

你可以到这里下载对应的rpm安装包。
当然你也可以找一台能够连接到外网的linux服务器，下载对应的rpm安装包即可。

yum -y install --downloadonly --downloaddir=/home packageName

修改编码报错

报错信息Character set 'utf8mb4' is nota a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file。对应的解决方法就是修改/usr/share/mysql/charsets/Index.xml文件，复制一份utf8的配置信息，将名称改为utf8mb4即可。

在运行pt-online-schema-change过程中出现死锁

参考文章：Online-schema-change出现死锁
主要的原因是由于触发器导致的死锁，报错信息“execute failed: Deadlock found when trying to get lock; try restarting transaction for statement”，具体原理参考文章Mysql数据库死锁原理探究

avatar
文章
阅读
粉丝