為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
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/25583515/viewspace-2730846/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL修改表預設字符集行為MySql
- 修改MySQL字符集MySql
- mysql 字符集修改MySql
- MySQL修改字符集MySql
- mysql 修改字符集為utf8mb4MySql
- mysql字符集和字元排序MySql字元排序
- MySQL DB字符集修改方法MySql
- Ubuntu下修改mysql的字符集UbuntuMySql
- 修改mysql預設字符集的方法MySql
- 【MySQL】批次修改排序規則MySql排序
- mysql亂碼?一勞永逸修改mysql字符集!MySql
- MySQL檢視和修改字符集的方法MySql
- MySQL 檢視與修改預設字符集MySql
- mysql學習之-字符集選定,修改。MySql
- mysql修改表、欄位、庫的字符集MySql
- MySQL修改字符集(mysqldump轉換全庫)MySql
- 關於mysql字符集及排序規則設定MySql排序
- 【實驗】【MySQL】模擬將latin1字符集的資料庫修改為gbk字符集MySql資料庫
- 修改Oracle字符集為ZHS16GBKOracle
- Oracle 字符集修改Oracle
- 修改sqlserver字符集SQLServer
- 修改oracle字符集Oracle
- oracle修改字符集Oracle
- oracle字符集修改Oracle
- oracle 修改字符集Oracle
- MySQL表、欄位、庫的字符集修改方法介紹MySql
- 修改linux字符集Linux
- oracle之修改字符集Oracle
- Windows修改cmd字符集Windows
- Linux編譯安裝MySQL5.6及修改字符集Linux編譯MySql
- 怎樣修改檢視Oracle字符集及怎樣修改字符集 ztOracle
- sybase字符集與排序操作排序
- MySQL監控--zabbixMySql
- zabbix agent(Active)模式 /克隆修改模板模式
- zabbix-agent修改主動模式模式
- MySQL字符集MySql
- Oracle 11g 修改字符集 為 ZHS16GBKOracle
- Oracle修改資料字符集Oracle