升级zabbix版本到 4.4.6后有Unsupported charset or collation for tables 警告信息 官方有issue说明 https://support.zabbix.com/browse/ZBX-17357解决方法 https://www.zabbix.com/documentation/4.0/manual/appendix/install/db_charset_coll
主要原因是因为zabbix后台使用的MySQL字符集问题导致，字符排序需修改到utf8_bin 具体修改步骤： 1. 检查当前zabbix使用数据库和表的字符集排序
MariaDB [mysql]> use zabbix Database changed MariaDB [zabbix]> SELECT @@character_set_database, @@collation_database; +--------------------------+----------------------+ | @@character_set_database | @@collation_database | +--------------------------+----------------------+ | utf8 | utf8_general_ci | +--------------------------+----------------------+ 1 row in set (0.01 sec) MariaDB [zabbix]> show create database zabbix; +----------+-----------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------+ | zabbix | CREATE DATABASE `zabbix` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+-----------------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [zabbix]> select * from information_schema.tables where table_schema='zabbix';
可以看到原来的排序是默认的 _general_ci ，所以有警告 报错 2. 关闭zabbix 服务# systemctl stop zabbix-server 3. 以防修改字符集过程出现问题，可先备份数据库 (觉得zabbix数据不重要的可以跳过这步)因为历史数据过大可以先查看大表使用 --ignore-table跳过多个历史数据表和趋势表备份
MariaDB [(none)]> select TABLE_NAME,(sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024/1024 from information_schema.tables where table_schema="zabbix" GROUP BY TABLE_NAME ORDER BY 2 DESC limit 10; +--------------------+------------------------------------------------+ | TABLE_NAME | (sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024/1024 | +--------------------+------------------------------------------------+ | history_uint | 60604.14062500 | | history | 27953.15625000 | | trends_uint | 13042.60937500 | | trends | 2720.98437500 | | history_str | 252.09375000 | | events | 101.04687500 | | items | 51.03125000 | | alerts | 40.15625000 | | items_applications | 15.26562500 | | history_str_test | 14.82812500 | +--------------------+------------------------------------------------+ 10 rows in set (41.27 sec)
执行备份： # mysqldump -h127.0.0.1 -uroot -p'password' --default-character-set=utf8 --databases zabbix -R --ignore-table=zabbix.history --ignore-table=zabbix.history_uint --ignore-table=zabbix.trends_uint --log-error=zabbix_base.log > zabbix_base.sql 4. zabbix 数据库级别字符集排序修改
MariaDB [zabbix]> alter database zabbix character set utf8 collate utf8_bin;
确认是否修改成功
MariaDB [zabbix]> SELECT @@character_set_database, @@collation_database; +--------------------------+----------------------+ | @@character_set_database | @@collation_database | +--------------------------+----------------------+ | utf8 | utf8_bin | +--------------------------+----------------------+
5. 表数据级别字符集排序修改注：alter table table_name character set utf8 collate utf8_bin;命令只是对表定义修改字符排序alter table table_name convert to character set utf8 collate utf8_bin; 会对表定义，字段，现有数据全部修改 字符排序zabbix官方提供了使用 utf8_bin的转换脚本，主要也是使用 convert to 命令转换 表定义，字段和现有数据 utf8_convert.sql 内容：
/* ChangeLog:
2020.08.19 - initial release
2020.09.04 - fixed syntax for running on MySQL
*/
DELIMITER $$
CREATE PROCEDURE zbx_convert_utf8 (
)
BEGIN
declare cmd varchar(255) default "";
declare finished integer default 0;
declare cur_command cursor for
SELECT command
FROM
(/* This 'select' statement deals with 'text' type columns to prevent
their automatic conversion into 'mediumtext' type.
The goal is to produce statements like
ALTER TABLE zabbix.hosts MODIFY COLUMN description text CHARACTER SET utf8 COLLATE utf8_bin not null;
*/
SELECT table_name AS sort1,
'A' AS sort2,
CONCAT('ALTER TABLE ', table_schema, '.', table_name,
' MODIFY COLUMN ', column_name, ' ', column_type,
' CHARACTER SET utf8 COLLATE utf8_bin',
case
when column_default is null then ''
else concat(' default ', column_default, ' ')
end,
case
when is_nullable = 'no' then ' not null '
else ''
end,
';') AS command
FROM information_schema.columns
WHERE table_schema = @ZABBIX_DATABASE
AND column_type = 'text'
UNION
/* This 'select' statement deals with setting character set and collation for
each table and converting varchar fields on a per-table basis.
It is necessary to process all tables (even those with numeric-only columns)
otherwise in future Zabbix upgrades text (e.g. varchar) columns may be added
to these tables or numeric columns can be turned into text ones and
the old character set/collation can reappear again.
The goal is to produce statements like
ALTER TABLE zabbix.hosts CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
*/
SELECT table_name AS sort1,
'B' AS sort2,
CONCAT('ALTER TABLE ', table_schema, '.', table_name,
' CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;') AS command
FROM information_schema.tables
WHERE table_schema = @ZABBIX_DATABASE) s
/* Sorting is important: 'MODIFY COLUMN' statements should precede 'CONVERT TO' ones
for each table. */
ORDER BY sort1, sort2;
declare continue handler for not found set finished = 1;
open cur_command;
cmd_loop: loop
fetch cur_command into cmd;
if finished = 1 then
leave cmd_loop;
end if;
SET @value = cmd;
PREPARE stmt FROM @value;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
end loop cmd_loop;
close cur_command;
END$$
DELIMITER ;
将转换SP导入DB
mysql -uroot -ppassword zabbix < utf8_convert.sql
执行SP前注意如果现有数据很大时，convert 操作执行时间会很长，如果之前使用了分区表方式可以，先清理一下历史分区数据(分区方法可参考文章： http://blog.itpub.net/25583515/viewspace-2638892)，根据情况只保留的最近一周的历史数据和最近一月的历史趋势数据，执行：
MariaDB [zabbix]> CALL partition_maintenance('zabbix', 'history', 7, 24, 7);
MariaDB [zabbix]> CALL partition_maintenance('zabbix', 'history_log', 7, 24, 7);
MariaDB [zabbix]> CALL partition_maintenance('zabbix', 'history_str', 7, 24, 7);
MariaDB [zabbix]> CALL partition_maintenance('zabbix', 'history_text', 7, 24, 7);
MariaDB [zabbix]> CALL partition_maintenance('zabbix', 'history_uint', 7, 24, 7);
MariaDB [zabbix]> CALL partition_maintenance('zabbix', 'trends', 30, 24, 7);
MariaDB [zabbix]> CALL partition_maintenance('zabbix', 'trends_uint', 30, 24, 7);
执行SP转换字符集排序：
MariaDB [mysql]> use zabbix Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [zabbix]> set innodb_strict_mode = OFF; Query OK, 0 rows affected (0.00 sec) MariaDB [zabbix]> SET @ZABBIX_DATABASE = 'zabbix'; Query OK, 0 rows affected (0.00 sec) MariaDB [zabbix]> CALL zbx_convert_utf8(); Query OK, 0 rows affected, 1 warning (1 hours 53 min 17.85 sec) MariaDB [zabbix]> set innodb_strict_mode = ON; Query OK, 0 rows affected (0.00 sec) MariaDB [zabbix]> drop procedure zbx_convert_utf8; Query OK, 0 rows affected (0.04 sec)
操作完成后，可以查看修改后情况
MariaDB [zabbix]> select * from information_schema.tables where table_schema='zabbix';
6. 最后开启zabbix服务# systemctl start zabbix-server
