公司有一台zabbix服务器,服务器配置不是很高,只是用作监控服务器使用,可是时间一长数据库越来越大线上磁盘爆满,然后我就开始清理数据库的工作。

清理数据库之前要把zabbix_server服务停止

/etc/init.d/zabbix_server stop

然后用mysqldump先把数据库备份一下防止意外出现。

用shell脚本清理zabbix 本人1.8G的数据清理完成后只有1.66M 清理效果非常好。

方法一:

#!/bin/bash
User=”zabbix”
Passwd=”123456″
Date=`date -d $(date -d “-20 day” +%Y%m%d) +%s` #取20天之前的时间戳
$(which mysql) -u${User} -p${Passwd} -e “
use zabbix;
DELETE FROM history WHERE ‘clock’ < $Date;
optimize table history;
DELETE FROM history_str WHERE ‘clock’ < $Date;
optimize table history_str;
DELETE FROM history_uint WHERE ‘clock’ < $Date;
optimize table history_uint;
DELETE FROM  trends WHERE ‘clock’ < $Date;
optimize table  trends;
DELETE FROM trends_uint WHERE ‘clock’ < $Date;
optimize table trends_uint;
DELETE FROM events WHERE ‘clock’ < $Date;
optimize table events;

方法二:

use zabbix;
truncate table history;
optimize table history;
truncate table history_str;
optimize table history_str;
truncate table history_uint;
optimize table history_uint;
truncate table trends;
optimize table trends;
truncate table trends_uint;
optimize table trends_uint;
truncate table events;
optimize table events;

执行脚本:注意执行这个脚本运行时间很长请耐心等待;

清理完成后把数据在导入数据库然后启动zabbix_server服务

但是服务一直显示没有启动看错误日志出现以下信息

339:20160413:152019.443 [Z3005] query failed: [1146] Table ‘zabbix.httptest’ doesn’t exist [select min(t.nextcheck) from httptest t,hosts h where t.hostid=h.hostid and mod(t.httptestid,1)=0 and t.status=0 and h.proxy_hostid is null and h.status=0 and (h.maintenance_status=0 or h.maintenance_type=0)]   1317:20160413:152022.023 [Z3001] connection to database ‘zabbix’ failed: [1049] Unknown database ‘zabbix’   1317:20160413:152022.024 Cannot connect to the database. Exiting…   1311:20160413:152022.025 One child process died (PID:1317,exitcode/signal:1). Exiting …   1311:20160413:152024.026 [Z3001] connection to database ‘zabbix’ failed: [1049] Unknown database ‘zabbix’   1311:20160413:152024.026 Cannot connect to the database. Exiting…

显示的是找不到zabbix数据库,我用zabbix用户登录到数据库查看

显示有这个数据库,再看报错

不能插入数据httpstest表为空 解决办法

插入提示报错的sql语句成功解决问题,服务终于启动了

启动成功!!!!

 

欢迎留言