The Zabbix of MySQL character set problem

MySQL Character set problem is mainly two concepts, one is character sets, one is collations, the former is the character content and encoding, the latter is the former to compare the rules of the operation. These two parameter sets can be specified at four levels: database instance, single database, table, column, etc.

Zabbix does not have the function of data storage, so we need to use a third-party database to complete the data storage, the more popular is using MySQL, and about her character set is a small problem, we come to solve it;

One: Solve the character set problem frequently encountered in Zabbix monitoring

1. Display Chinese garbled characters in graphics

2. Garbled characters at the historical record

II: Change the operation of the MySQL character set

1. View the current default character set

2. Set the character set before installing

3. Change when no data is stored after installation

4. With a certain amount of data stored after the change

One: Character set problems often encountered in Zabbix monitoring

1. Display Chinese garbled characters in graphics

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/74/6B/wKioL1Yc6S6zRpmbAAJOkMnPcAM756.jpg "title=" 5.PNG " Width= "660" height= "border=" 0 "hspace=" 0 "vspace=" 0 "style=" width:660px;height:120px; "alt=" Wkiol1yc6s6zrpmbaajokmnpcam756.jpg "/>

Yum installed Zabbix fonts are not under/usr/share/zabbix/fonts, and under/usr/share/zabbix/fonts/dejavu, the configuration can be configured by viewing the configuration file:/usr/share/zabbix/ include/defines.inc.php, although there is a dejavusans.ttf in this directory, it doesn't work.

We need to have a favorite Chinese font put here, such as from the C:\windows\fonts\ directory on Windows to copy files Simfang.ttf to/usr/share/zabbix/fonts/dejavu directory, and then modify the name

MV Dejavusans.ttf DejaVuSans.ttf.bak

MV Simfang.ttf Dejavusans.ttf

Restart:/etc/init.d/zabbix-server restart

2. A question mark is garbled at the historical record

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/74/6F/wKiom1Yc6gSS3u3GAACxANnT0Ns153.jpg "title=" 555. PNG "alt=" Wkiom1yc6gss3u3gaacxannt0ns153.jpg "/>

This is because the character set used by the Zabbix database is not UTF8 in Chinese, and the workaround is as follows:

Backing Up the Zabbix database

Mysqldump-uroot-p 123456 Zabbix > Zabbix.sql

Modify Backup Files

Sed-i ' s/latin1/utf8/g ' Zabbix.sql

Delete Zabbix Database

mysql> drop Database Zabbix;

Log out of MySQL database, set default character sets

Vim/etc/my.cnf

[Mysqld]

Log-bin

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

Default-character-set = UTF8 #添加该项

Start MySQL and restore the Zabbix database

mysql-uroot-p123456 Zabbix < Zabbix.sql

II: Change the operation of the MySQL character set

1. View the current default character set

Mysql> Show CREATE Database Zabbix;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/74/6F/wKiom1Yc6iTy2_LwAADPBEl0now942.jpg "style=" float: none; "title=" 5555.PNG "alt=" Wkiom1yc6ity2_lwaadpbel0now942.jpg "/>

Mysql> Show variables like ' character% ';

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/74/6F/wKiom1Yc6iTyP6ERAAEl1eZI3CQ615.jpg "style=" float: none; "title=" 555555.PNG "alt=" Wkiom1yc6ityp6eraael1ezi3cq615.jpg "/>

2. Setup before installation, applicable to source package installation

we can compile MySQL by default_charset= UTF8

Default_collation=utf8_general_ci these two parameters;

When compiling, specify--with-charset=utf8--with-collation=utf8_general_ci) to specify the default character set as

UTF8, this is the most permanent way.

3. When there is no data after installation

/etc/my.cnf

Modify the character set key values in MySQL My.ini or my.cnf files

Add under [mysqld]

Default-character-set=utf8 (MySQL version 5.5 added Character-set-server=utf8)

Add under [client]

Default-character-set=utf8

Restart: Service MySQL restart

View:mysql> show VARIABLES like ' character% '

However, this change to the client connection is not useful, the client at this time generally need to specify UTF8 mode connection to avoid garbled. That is the legendary setnames command.

Execution on the server:

Set names UTF8 It's all right, but every time you boot up,

In fact the set names UTF8 command corresponds to the server side of the following several commands

SET character_set_client = UTF8;

SET character_set_results = UTF8;

SET character_set_connection = Xutf8;

Because these three parameters are not written in the configuration file MY.CNF. Can only be modified dynamically by the set command. But we can use Init_connect, this command means that each user connected to the time will trigger execution, you can add the following line in the [mysqld] section

[Mysqld]

Init_connect = ' SET NAMES UTF8 '

About this setting, some documents on this basis plus a few commands, on the three set on the addition of a few, your really not enough words plus try it

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/74/6C/wKioL1Yc63mC_YgPAAEYkv6X2F0901.jpg "title=" 55555555.PNG "alt=" Wkiol1yc63mc_ygpaaeykv6x2f0901.jpg "/>

The Zabbix of MySQL character set problem