zabbix升级 mysql优化

676用填3

./configure --enable-server --enable-agent --with-mysql --enable-ipv6 --with-net-snmp --with-libcurl --with-libxml2

apt-get install libmysqlclient-dev

--aptitude search snmp | grep dev

apt-get install  libxml2 libxml2-dev

apt-get install libsnmp-dev

apt-get install libcurl4-gnutls-dev

fuser -k 80/tcp


mysql -u zabbix -p zabbix < /usr/local/zabbix-3.2.0alpha1/database/mysql/schema.sql
mysql -u zabbix -p zabbix < /usr/local/zabbix-3.2.0alpha1/database/mysql/images.sql
mysql -u zabbix -p zabbix < /usr/local/zabbix-3.2.0alpha1/database/mysql/data.sql

apt-get dist-upgrade -u

--default-character-set=UTF8

*************************************************************************************************************************

wget http://repo.zabbix.com/zabbix/3. ... -1+wheezy_amd64.deb

dpkg -i zabbix-server-mysql_3.0.4-1+wheezy_amd64.deb

apt-get update


apt-get install zabbix-server-mysql zabbix-frontend-php
********************************************************************************************************************************************************************

统计数据库大小
SELECT table_schema , sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB"  FROM information_schema.TABLES GROUP BY table_schema;

统计表大小并排序
SELECT table_name AS "Tables",
round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"
FROM information_schema.TABLES
WHERE table_schema = 'zabbix'
ORDER BY (data_length + index_length) DESC;


use zabbix;
truncate table history;
optimize  table history;
truncate table history_str;
truncate table history_uint;

**********************************************************************
#!/bin/bash

# Author: ZhiPeng Wang.
# Last Modified: 2014/6/2

User="root"
Passwd="wangzhipeng"
Date=`date -d $(date -d "-30 day" +%Y%m%d) +%s` #取30天之前的时间戳

$(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;
"