官方有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、先备份数据
一定要先备份数据!!!
一定要先备份数据!!!
一定要先备份数据!!!
2、关闭zabbix 服务
3、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 |
+--------------------------+----------------------+
4、表数据级别字符集排序修改
注: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转换字符集排序:
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';
5、开启zabbix服务
参考文档:http://blog.itpub.net/25583515/viewspace-2730846/
重点:备份很重要!