登录zabbix4.0界面,出现报错：SQL statement execution has failed "INSERT INTO sessions (sessionid,userid,lastaccess,status) VALUES ('6d1a0523bd8cd53179fcdfc3c397d343','1','1608889582','0')".
我遇到的情况是服务器突然断电，再开机后登录zabbix web界面出现此情况
初步判断为mysql数据库文件损坏，而且数据很重要，立马做了以下备份操作
1.发现数据库登录失败，挂掉了，服务已经不存在
日志疯狂报错
进入紧急模式，先导出一份数据出来,修改mariadb 的my.cnf配置
[root@zabbix ~]# vim /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the # instructions in http://fedoraproject.org/wiki/Systemd innodb_force_recovery = 6 innodb_purge_thread = 0 [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid # # include all files from the config directory # !includedir /etc/my.cnf.d
innodb_force_recovery=0 表示当需要恢复时执行所有的恢复操作；
innodb_force_recovery=1 表示忽略检查到的corrupt页；innodb_force_recovery=2 表示阻止主线程的运行，如主线程需要执行full purge操作，会导致crash；innodb_force_recovery=3 表示不执行事务回滚操作；innodb_force_recovery=4 表示不执行插入缓冲的合并操作；innodb_force_recovery=5 表示不查看重做日志，InnoDB存储引擎会将未提交的事务视为已提交；innodb_force_recovery=6 表示不执行前滚的操作，强制重启！ 注：我这里的环境不加innodb_purge_thread = 0 服务依然启动不起来，具体原因不明
重新启动服务
[root@zabbix ~]# systemctl start mariadb [root@zabbix ~]# systemctl status mariadb ● mariadb.service - MariaDB database server Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled) Active: active (running) since 五 2020-12-25 18:06:59 CST; 50s ago Process: 73481 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS) Process: 73444 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS) Main PID: 73480 (mysqld_safe) CGroup: /system.slice/mariadb.service ├─73480 /bin/sh /usr/bin/mysqld_safe --basedir=/usr └─73665 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/maria... 12月 25 18:06:57 zabbix systemd[1]: Starting MariaDB database server... 12月 25 18:06:57 zabbix mariadb-prepare-db-dir[73444]: Database MariaDB is probably initialized in /var/lib/mysql already, nothing is done. 12月 25 18:06:57 zabbix mariadb-prepare-db-dir[73444]: If this is not the case, make sure the /var/lib/mysql is empty before running ma...b-dir. 12月 25 18:06:57 zabbix mysqld_safe[73480]: 201225 18:06:57 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'. 12月 25 18:06:58 zabbix mysqld_safe[73480]: 201225 18:06:58 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 12月 25 18:06:59 zabbix systemd[1]: Started MariaDB database server. Hint: Some lines were ellipsized, use -l to show in full.
导出zabbix数据库的数据到根目录命名为zabbix.sql
[root@zabbix ~]# mysqldump --single-transaction -u root -h 127.0.0.1 --password=vrgv123. zabbix > /zabbix.sql
导出之后，把之前的mariadb数据库重新安装，这里需要注意下，使用yum remove 卸载时，data数据可能还在，需要通过/etc/my.cfg文件查看下data具体路径，删除后，再重新安装mariadb，之后设置zabbix用户权限，导入zabbix.sql文件到zabbix数据库，，重新启动zabbix服务恢复正常