為Zabbix MySQL修改字符集排序utf8_bin

升級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