注意：zabbix7导入数据之前，如果有zabbix库把这个库删除掉（如果覆盖7的数据导入后会有很多数据问题）。另外不要全库导出，只导出zabbix库即可（不然系统表会丢失infoschema账号）
1.zabbix4上的mysql数据库导出
nohup mysqldump -uroot -pb8Ak1yR7 -B zabbix > /mnt/zabbix/mysql_backup/zabbix20240927new.sql 2>&1 &
2.新的zabbix7导入mysql数据
set global log_bin_trust_function_creators = 1; 导入前打开
source zabbix20240927new.sql
set global log_bin_trust_function_creators = 0; 导入完成关闭
问题排查：
1.如果提示ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint
原因：这个表被一个或多个外键约束所引用 解决1：先禁用外键约束 SET FOREIGN_KEY_CHECKS = 0; 导入完成后启动外键约束 SET FOREIGN_KEY_CHECKS = 1; 解决2（不建议）：删除外键约束：ALTER TABLE film_actor DROP FOREIGN KEY fk_film_actor_actor; 重新添加外键约束：ALTER TABLE film_actor ADD CONSTRAINT fk_film_actor_actor FOREIGN KEY (actor_id) REFERENCES actor(actor_id) ON DELETE CASCADE;
2.访问页面报错The Zabbix database version does not match current requirements. （删除zabbix库再导入不会有此问题）
update dbversion set mandatory=7000000; flush privileges;
3.增加导入速度，调大缓存
SET GLOBAL innodb_buffer_pool_size = 2048 * 1024 * 1024;
4.mysql is not allowed to connect to this myql server
select host from user where user='root'; 只有localhost ，只能本机访问 update user set host = '%' where user ='root';
5.wrong value of "HistoryIndexCacheSize" in config file "/etc/zabbix/zabbix_server.conf"
解决：修改HistoryIndexCacheSize=2048M，原来是4096m
6.修改zabbix登录密码，数据导入后原来的Admin密码失效，需要重置
运行此查询后，用户密码将设置为 zabbix UPDATE users SET passwd = '$2a$10$ZXIvHAEP2ZM.dLXTm6uPHOMVlARXX7cqjbhM6Fn0cANzkCQBWpMrS' WHERE username = 'Admin';
7.修改zabbix字符集脚本(导入后zabbix库和其下的表字符集会变化
--查询字符集 SELECT @@character_set_database, @@collation_database; --调整数据库字符集和排序规则 alter database zabbix character set utf8mb4 collate utf8mb4_bin; --调整表字符集和排序规则 SELECT CONCAT("ALTER TABLE ", TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;") AS ExecuteTheString FROM information_schema.`COLUMNS` WHERE table_schema ="zabbix" AND COLLATION_NAME ="utf8mb3_general_ci";
8.show database提示The user specified as a definer ('mysql.infoschema'@'localhost') does not exists(全库导入报错,如果只导入zabbix库没有此问题)
原因：mysql.user表里面少了一个账号信息：mysql.infoschema 解决1（这个不行）：创建账号CREATE USER 'mysql.infoschema'@'localhost' IDENTIFIED BY '密码'; 会报错mysql8 ERROR 1726 (HY000): Storage engine 'MyISAM' does not support system tables. [mysql.user] ALTER TABLE mysql.user ENGINE = InnoDB; 但是还是一直报错，这个方法不行 解决2（这个可行）： mysql -uroot -p"密码"
use mysql;
INSERT INTO mysql.user(`Host`,`User`,`plugin`,`authentication_string`,ssl_cipher,x509_issuer,x509_subject) VALUES ('localhost','mysql.infoschema','mysql_native_password','*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE','','','');
FLUSH PRIVILEGES;
update user set Select_priv = 'Y' where User = 'mysql.infoschema';
FLUSH PRIVILEGES;
9.初始化zabbix日志报错You do not have the SUPER privilege and binary logging is enabled (you might want
初始化数据库前需要执行该语句，否则会失败： SET GLOBAL log_bin_trust_function_creators = 1;
10.数据库导入后zabbix-server启动报错out of memory
53013:20241011:084208.672 [file:dbconfig.c,line:225] __zbx_shmem_malloc(): out of memory (requested 232 bytes) 53013:20241011:084208.672 [file:dbconfig.c,line:225] __zbx_shmem_malloc(): please increase CacheSize configuration parameter 解决：增加缓存 CacheSize=8G ValueCacheSize=256M VMwareCacheSize=500M
11.zabbix不断重启报错如下
__vm_enough_memory: pid: 4350, comm: zabbix_server, no enough memory for the allocation cannot initialize configuration cache: cannot get private shared memory of size 34359738368 for configuration cache 原因 ：这是因为内核对share memory的限制造成的。 sysctl -a|grep shm 查看共享内存设置 kernel.shmmax = 17179869184 16g 单个段能allocate的内存 kernel.shmall = 4294967296 4g 总共能分配的共享内存 net.ipv6.conf.all.disable_ipv6=1 sysctl -p 生效
