zabbix迁移后提示“Unsupported charset or collation for tables”警告

今天把zabbix从原来的一台服务器迁移到另外一个，很久没折腾还是弄了好久，主要还是数据库格式的问题。zabbix 从4.4.6开始数据库只支持 character set utf8 collate utf8_bin。所以如果当初创建数据库时如果没配置好，在升级到4.4.6或升级到 5.0 之后就会出现“Unsupported charset or collation for tables”

解决办法

首先修改数据库默认字符集及字符校验集

ALTER DATABASE `zabbix` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

然后修改表

# 找出字符检验集不对的表
SELECT CONCAT("ALTER TABLE ", TABLE_NAME," CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;") AS ExecuteTheString
 FROM information_schema.`COLUMNS`
 WHERE table_schema = "zabbix" AND COLLATION_NAME = "utf8_general_ci";

复制输出结果，并执行。例如以下两张表，一般会有很多。

ALTER TABLE acknowledges CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; 
ALTER TABLE actions CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;