有效升级 MySQL 表的 ip 字段

在文章IPv6 使用及注意事项 中我们提到了应用程序如果要支持 IPv6, 需要关注相关数据库的修改, 不过由于业务设计的不同, 有些程序可能使用 char , varchar 等存储 ip 字符串, 有些程序也可能考虑到索引效率使用 int unsigned 存储 ip 的整形. 不过对于 IPv6 地址, 由于其 128 bit 的长度, 决定了 IP 地址数量为 (2^128 – 1), 这个值对 64 位的主机而言无法存储, 所以建议使用 varchar 类型存储所有的 IPv6 地址. 下文则主要介绍修改表时需要注意的一些操作事项.

原先用 char 或 varchar 类型存储 ip 信息的表

有效的 IPv6 地址的长度大概为 3 ~ 39, 所以要使字段能够存储 IPv6 地址, 需要修改字段长度为 varchar(39) :

::1 ~ 2001:0db8:1111:1111:1111:1111:1111:1111

原先用 int 类型存储 ip 信息的表

有以下两种方式进行操作, 不过都需要程序端做相关的配合才能做到无缝升级而不影响业务的访问, 两种方式都需要pt-osc 工具进行在线修改表操作.

直接修改 int 为 varchar(39)

如下所示, o 为原表中旧的 ipv4 数据, d 为 pt-osc 修改过程中程序插入的数据, n 为 pt-osc 开始操作后程序新插入的数据.

convert
          int         --------->        varchar
o =>     168562985                     '10.12.17.41'
o =>     168562971                     '10.12.17.27'
o =>     2886729730                    '172.16.0.2'
===================================================   applications(handle data depend on ip column type)
===================================================   befre  pt-osc
d =>     2886736139       ==>           ?
d =>     3232235778       ==>           ?
===================================================   after  pt-osc
===================================================   applications(all data as varchar)
n =>     3401909527       ==>           ?

如果只是单纯的修改 int 为 varchar 类型, 需要进行以下步骤:

1. 使用 pt-osc 开始修改表的过程中, 原有的 int 数据如何转换为有效的 ip 字符串格式?

需要单修改 pt-osc 工具, 将旧的 int 值转为 varchar 类型.

2. 应用程序端需要根据列的类型区别处理更新和查询操作;

3. 使用 pt-osc 开始修改表的过程中, 程序新插入的数据如何转成有效的 ip 格式, 程序如何读取 ip 列相关的信息?

在 pt-osc 修改表的过程中, 对原表的更新和读取操作保持不变, 但需要单独修改 pt-osc 的触发器以修改新表的字段类型;

4. 使用 pt-osc 修改完表结构后, 新的 ip 信息使用什么格式存储?

在 pt-osc 修改完表结构之后需要及时修改程序端以插入 varchar 格式的 ip 信息, 以避免 select 查询等操作的类型转换引起性能骤降的问题. 同时也要单独处理程序修改过程中产生的 int 类型的数据;

5. 修改应用程序, 所有 ip 列数据都以 varchar 格式处理;

如果没有第 2 步操作, 则第 4 个问题将决定无法无缝升级整个业务, 因为ip 列相关查询需要类型转换, 这部分业务容易出现连接吃满, 响应缓慢的问题. 第 1, 3 步 pt-osc 补丁见 pt-osc , 使用 --convert-column 和 --convert-funtion 选项进行操作, 如下所示:

pt-online-schema-change --alter 'modify column ip varchar(39)' A=utf8,h=127.0.0.1,u=root,D=percona,t=login_ip
--ask-pass --convert-column ip --convert-function inet_ntoa --execute

额外增加 ipv6 列

如果单独增加列 varchar(39) , 则处理过程相对简单, 同样需要应用程序做相应的配合. 如下所示:

convert
          int         --------->          ipv4(int),  ipv6(varchar)
o =>     168562985                        168562985,  NULL
o =>     168562971                        168562971,  NULL
o =>     2886729730                       2886729730, NULL
===================================================================   applications(only handle ipv4)
===================================================================   befre  pt-osc
d =>     2886736139       ==>             2886736139,
d =>     3232235778       ==>             3232235778,
===================================================================   after  pt-osc
===================================================================   applications(handle ipv4/ipv6)
n =>     3401909527       ==>             3401909527, NULL
n =>     2001：db8::1     ==>             NULL, '2001:db8::1'

1. 使用 pt-osc 修改表的过程中, 原有的 int 类型不变, ipv6 字段为 NULL 值;

2. 修改应用程序, 仅处理 ipv4 相关的列. 如果程序以数组等方式获取相关信息, 需要修改为按列名获取信息, 以免修改列引起列不匹配问题.

很多程序使用下面的方式获取表中的数据, 这种方式关联了表中所有列的顺序, 额外给表增加列容易引起程序的查询和更新不匹配的问题:

string sql;
array @result;
sql = "select * from  ... where ...";
@result = dbh->selectall_array(sql, parameters);
print result[0], result[1], ....;

sql = "insert into ... values (....)";
int status = dbh->do(sql, parameters);

3. 第 2 步修改完后, 使用 pt-osc 增加新列;

4. pt-osc 修改完表后, 修改程序同时处理 ipv4 和 ipv6 信息, 程序的查询和更新都需要做对应的修改.

如下所示, 同一行中仅有 ipv4 或 ipv6 有值:

CREATE TABLE `login_log` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `user_id` int(10) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `login_ip` int(10) unsigned DEFAULT NULL,
  `login_ipv6` varchar(39) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into login_log(user_id, create_time, login_ip, login_ipv6) values(1239, now(), inet_aton('192.168.10.2'), NULL);
insert into login_log(user_id, create_time, login_ip, login_ipv6) values(1239, now(), NULL, lower('fe80::222:18ff:fe67:4fb1'));
insert into login_log(user_id, create_time, login_ip, login_ipv6) values(1239, now(), NULL, NULL);

select user_id, create_time, ifnull(inet_ntoa(login_ip), login_ipv6) as ip from login_log;        
+---------+---------------------+--------------------------+
| user_id | create_time         | ip                       |
+---------+---------------------+--------------------------+
|    1239 | 2018-07-24 10:03:28 | 192.168.10.2             |
|    1239 | 2018-07-24 10:04:25 | fe80::222:18ff:fe67:4fb1 |
|    1239 | 2018-07-24 10:05:38 | NULL                     |
+---------+---------------------+--------------------------+

单独增加列需要程序做更多的额外配合, 不过同上面的一样可以无缝升级. 相比上面的方式, 无需承担修改 pt-osc 工具的风险, 不过程序端改的较多, 如果表有 ip 相关的索引, 也同样需要增加 ipv6 列的索引.

总结

整体上而言, 如果要达到无缝升级的目的, 原先为 char 或 varchar 类型的表反而更为方便, 直接使用 pt-osc 工具修改长度即可. 而原先为 int 类型的表则需要程序端做很多相应的配合, 两种方式各有利弊, 在修改的 pt-osc 无风险的情况下建议使用第 1 种方式修改表字段到 varchar 类型.