首页 » Zabbix » 正文

zabbix数据库编码问题

部署zabbix之后,在系统信息里提示以下错误

Incorrect default charset for Zabbix database: "utf8mb4" instead "UTF8".

故障原因:zabbix使用MySQL数据库时只支持utf8编码,使用utf8mb4编码虽然也没发现什么问题,但是说不定什么时候就发生了什么故障了,所以还是更改为utf8数据编码。

解决方法:

#备份数据库
mysqldump -uroot -p --databases zabbix > zabbix.sql

#开始直接想到的是直接更改数据库编码为utf8,登录数据库
mysql -uroot -p 
#查看数据库编码
MySQL [(none)]> show create database zabbix;
+----------+--------------------------------------------------------------------+
| Database | Create Database                                                    |
+----------+--------------------------------------------------------------------+
| zabbix   | CREATE DATABASE `zabbix` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+--------------------------------------------------------------------+
1 row in set (0.001 sec)

#更改数据库编码为utf8mb4
MySQL [(none)]> alter database zabbix character set utf8;
Query OK, 1 row affected (0.005 sec)

MySQL [(none)]> show create database zabbix;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| zabbix   | CREATE DATABASE `zabbix` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.002 sec)

刷新zabbix web页面,发现提示以下错误

Unsupported charset or collation for tables: acknowledges, actions, alerts, application_discovery, application_prototype, applications, auditlog, auditlog_details, autoreg_host, conditions, config, config_autoreg_tls, corr_condition_tag, corr_condition_tagpair, corr_condition_tagvalue, correlation, dashboard, dchecks, drules, dservices, event_tag, events, expressions, functions, globalmacro, graph_theme, graphs, graphs_items, group_discovery, group_prototype, history_log, history_str, history_text, host_discovery, host_inventory, host_tag, hostmacro, hosts, housekeeper, hstgrp, httpstep, httpstep_field, httptest, httptest_field, icon_map, icon_mapping, ids, images, interface, interface_snmp, item_condition, item_discovery, item_preproc, item_rtdata, items, lld_macro_path, lld_override, lld_override_condition, lld_override_operation, lld_override_ophistory, lld_override_opperiod, lld_override_optag, lld_override_optrends, maintenance_tag, maintenances, mappings, media, media_type, media_type_message, media_type_param, module, opcommand, opconditions, operations, opmessage, problem, problem_tag, profiles, proxy_autoreg_host, proxy_dhistory, proxy_history, regexps, screens, screens_items, scripts, services, services_times, sessions, slides, slideshows, sysmap_element_url, sysmap_shape, sysmap_url, sysmaps, sysmaps_elements, sysmaps_link_triggers, sysmaps_links, tag_filter, task_data, task_remote_command, task_remote_command_result, task_result, trigger_tag, triggers, users, usrgrp, valuemaps, widget, widget_field.

这个错误是由于之前创建的数据库编码是utf8mb4,直接更改为utf8的话会有个问题,旧的数据的编码并没有发生改变,所以无法识别出来

#解决方法,将导出的zabbix数据库的sql数据文件中的utf8mb4字符修改成utf8之后再导入到zabbix数据库中
cp zabbix.sql zabbix.sql.bak
sed -i 's/utf8mb4/utf8/g' zabbix.sql
#导入数据库
MySQL [(none)]> 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

sDatabase changed
MySQL [zabbix]> source zabbix.sql
.....................................

Query OK, 0 rows affected (0.001 sec)

Query OK, 0 rows affected (0.001 sec)

导入完成后再次刷新页面发现错误提示消失,故障解决

发表评论