Zabbix Proxy升级完成后提示MySQL字符集不符合要求

概述

在升级完成Zabbix Proxy服务版本后启动发现日志中提示字符集不为utf8。

12344:20211111:100944.017 Zabbix supports only "utf8,utf8mb3" character set(s). Database "zabbix_proxy" has default character set "latin1"
 12344:20211111:100944.017 Zabbix supports only "utf8_bin,utf8mb3_bin" collation(s). Database "zabbix_proxy" has default collation "latin1_swedish_ci"
 12344:20211111:100944.039 character set name or collation name that is not supported by Zabbix found in 458 column(s) of database "zabbix_proxy"
 12344:20211111:100944.039 only character set(s) "utf8,utf8mb3" and corresponding collation(s) "utf8_bin,utf8mb3_bin" should be used in database

解决办法

修改数据库字符集

查询全部或指定数据库字符集并通过sql命令进行修改

mysql> SELECT SCHEMA_NAME 'database', DEFAULT_CHARACTER_SET_NAME 'charset', DEFAULT_COLLATION_NAME 'collation' FROM information_schema.SCHEMATA;
+--------------------+---------+-------------------+
| database           | charset | collation         |
+--------------------+---------+-------------------+
| information_schema | utf8    | utf8_general_ci   |
| test               | latin1  | latin1_swedish_ci |
| zabbix_proxy       | latin1  | latin1_swedish_ci |
+--------------------+---------+-------------------+
3 rows in set (0.00 sec)

mysql> SELECT DEFAULT_CHARACTER_SET_NAME 'charset', DEFAULT_COLLATION_NAME 'collation' FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'zabbix_proxy';
+---------+-------------------+
| charset | collation         |
+---------+-------------------+
| latin1  | latin1_swedish_ci |
+---------+-------------------+
1 row in set (0.00 sec)


mysql> ALTER DATABASE zabbix_proxy CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 1 row affected (0.01 sec)

修改数据库表字符集

# 查询字符集排序方式不符的
mysql> SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'zabbix_proxy' and TABLE_COLLATION != 'utf8_bin';
# 单个修改
mysql> ALTER TABLE zabbix_proxy.table_name CHARACTER SET utf8 COLLATE utf8_bin;
# 批量查询修改命令
mysql> select CONCAT('alter table zabbix_proxy.',table_name_list.table_name,' convert to character set utf8 collate utf8_bin;') from (select table_name from information_schema.`TABLES` where TABLE_SCHEMA = 'zabbix_proxy' and TABLE_COLLATION != 'utf8_bin') table_name_list;

END