问题整理:ZABBIX4.0登录界面出现错误 SQL statement execution has failed “INSERT INTO sessions (sessionid,userid,..
2023-09-27 14:26:45 时间
登录zabbix4.0界面,出现报错:SQL statement execution has failed "INSERT INTO sessions (sessionid,userid,lastaccess,status) VALUES ('6d1a0523bd8cd53179fcdfc3c397d343','1','1608889582','0')".
![](https://img-blog.csdnimg.cn/img_convert/a36eb829d9ee922346ea98d23068d9f2.png)
我遇到的情况是服务器突然断电,再开机后登录zabbix web界面出现此情况
初步判断为mysql数据库文件损坏,而且数据很重要,立马做了以下备份操作
1.发现数据库登录失败,挂掉了,服务已经不存在
日志疯狂报错
![](https://img-blog.csdnimg.cn/img_convert/d3d99b5c7cdc2b120dc59742ff116ee2.png)
进入紧急模式,先导出一份数据出来,修改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服务恢复正常
相关文章
- Spring Boot 进行测试提示 TestContextAnnotationUtils 错误
- Flyway Validate failed: Migration checksum mismatch for migration version 1.0.0.01 错误
- Nvidia 显卡 Failed to initialize NVML Driver/library version mismatch 错误解决方案(不重启方案)【简单方案:重启】
- java.lang.ClassCastException: java.lang.String cannot be cast to java.lang.Integer map里string转integer错误
- 记录一个很低级的错误:command 'usr/local/cuda/bin/vncc' failed with exit status 2
- phpmailer发送邮件出现错误:stream_socket_enable_crypto(): SSL operation failed with code 1.
- 使用CURL出现certificate verify failed错误的解决方法
- mysql 2013错误解决
- jmeter压力测试报Address already in use: connect错误
- 设置 idea 运行前不去检查其他类的错误的方法
- 解决RabbitMq登录时报出Not management user(Login failed)错误以及rabbitmqctl(RabbitMq)新增用户和用户角色权限
- 启动mysql,爆出错误:Job for mysqld.service failed because the control process exited with error code.
- 录像时调用MediaRecorder的start()时发生start failed: -19错误
- FreeBSD下的Apache出现错误:[warn] (2)No such file or directory: Failed to enable the 'httpready' Accept Filter的解决方法
- 【错误记录】git clone 报错 ( fatal: unable to access ‘https...‘:gnutls_handshake() failed: Error in the pu )
- 【错误记录】Android Studio 配置 AspectJ 报错 ( Failed to create Jar file C:xxxaspectjtools-1.8.10.jar. )
- 【错误记录】Android 可执行权限报错 ( Cannot run program “/data/user/0/cn.e/ffmpeg“: error=13,Permission denied )
- wkhtmltopdf错误解决办法
- Nginx: [emerg] getpwnam(“www”) failed错误
- 记因为 NVIDIA 显驱错误而让 WPF 应用启动闪退问题
- 我的Android进阶之旅------>解决如下错误failed to copy 'Settings2.apk' to '/system/app//Settings2.apk': Read-only
- Android系统移植与调试之------->安装apk时出现错误Failure [INSTALL_FAILED_DEXOPT]问题解决的方法