ITPub博客

首页 > 数据库 > MySQL > 为Zabbix MySQL修改字符集排序utf8_bin

为Zabbix MySQL修改字符集排序utf8_bin

原创 MySQL 作者:guocun09 时间:2020-10-29 14:33:08 0 删除 编辑

升级zabbix版本到  4.4.6后有Unsupported charset or collation for tables 警告信息


官方有issue说明 

解决方法  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



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25583515/viewspace-2730846/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
DBA,掌握Oracle,SQLServer,MySQL,hadoop,casscandra等数据库。喜爱深入研究数据库技术原理,擅长管理和优化工作。

注册时间:2011-10-02

  • 博文量
    164
  • 访问量
    593950