Anyone who has set up a Zabbix server has encountered the encoding issue in the Web GUI interface, where all Russian text is displayed as question marks. The problem lies in the default character sets of the database tables, which we will modify to fix it.
We need to convert the character set of the tables and their data from latin1 to UTF-8.
First, stop the zabbix-server:
|
1
2
3
4
5
|
/etc/init.d/zabbix-server stop
or
systemctl stop zabbix-server
or
service zabbix-server stop
|
Next, you need to dump the database.
|
1
|
mysqldump -u root -p --ignore-table=zabbix.history --ignore-table=zabbix.history_uint --ignore-table=zabbix.images --default-character-set=latin1 -c --insert-ignore --skip-set-charset -r z1.sql zabbix
|
As you can see, we excluded 3 tables from the dump because they do not contain any rows, and the MySQL server crashed during the import of the modified dump when dealing with the images table.
We place these three tables into a separate dump, which we will not modify.
|
1
|
mysqldump -u root -p --default-character-set=latin1 -c --insert-ignore --skip-set-charset -r z4.sql zabbix history history_uint images
|
We strongly recommend saving the original dumps to enable rollback if necessary.
Change the encoding of all records in the z1.sql dump.
|
1
|
iconv -f ISO8859-1 -t UTF-8 z1.sql > z2.sql
|
Replace all encoding references in the z2.sql dump.
|
1
|
cat z2.sql |sed -e 's/ CHARSET=latin1/ CHARSET=utf8/g' > z3.sql
|
We recreate the database to clear it of all tables and create it with the required encoding.
|
1
|
mysql -u root -p --execute="DROP DATABASE zabbix; CREATE DATABASE zabbix CHARACTER SET utf8 COLLATE utf8_general_ci;"
|
Load the z4.sql dump into the database.
|
1
|
mysql -u root --max_allowed_packet=16M -p --default-character-set=utf8 zabbix < z4.sql
|
Load the z3.sql dump into the database
|
1
|
mysql -u root --max_allowed_packet=16M -p --default-character-set=utf8 zabbix < z3.sql
|
And restart the Zabbix server
|
1
2
3
4
5
|
/etc/init.d/zabbix-server start
or
systemctl start zabbix-server
or
service zabbix-server start
|
Verify the correct operation of the Zabbix server WEB interface. In case of problems, revert to the z1.sql and z4.sql dumps.