原文出自http://www.linuxidc.com/Linux/2014-12/110353.htm

zabbix自動清理30天前的數(shù)據(jù)

zabbix屬于一個細(xì)度化的監(jiān)控工具，其入庫數(shù)據(jù)隨著細(xì)度的增加相應(yīng)的入庫數(shù)據(jù)量也會較大，當(dāng)數(shù)據(jù)量到一定時候的時候其反映速度會比較慢，盡管其監(jiān)控服務(wù)在配置時可以指定數(shù)據(jù)的保存周期， 但是了解下通過直接操作數(shù)據(jù)庫進(jìn)行數(shù)據(jù)刪除還是有必要的。

通過數(shù)據(jù)庫進(jìn)行刪除的腳本如下：

#!/bin/bash

User="root"

Passwd="361way"

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;

"

注：其中histroy是詳細(xì)的歷史數(shù)據(jù)，trends是圖表趨勢數(shù)據(jù)。一般情況下，根據(jù)我的自定義，會將histroy數(shù)據(jù)保留7天，trend數(shù)據(jù)保留365天。

今天我們的zabbix-server機(jī)器根空間不夠了，我一步步排查結(jié)果發(fā)現(xiàn)是/var/lib/mysql/下的libdata1文件過大，已經(jīng)達(dá)到了41G。我立即想到了zabbix的數(shù)據(jù)庫原因，隨后百度、谷歌才知道zabbix的數(shù)據(jù)庫他的表模式是共享表空間模式，隨著數(shù)據(jù)增長，ibdata1 越來越大，性能方面會有影響，而且innodb把數(shù)據(jù)和索引都放在ibdata1下。

共享表空間模式：

InnoDB 默認(rèn)會將所有的數(shù)據(jù)庫InnoDB引擎的表數(shù)據(jù)存儲在一個共享空間中：ibdata1，這樣就感覺不爽，增刪數(shù)據(jù)庫的時候，ibdata1文件不會自動收縮，單個數(shù)據(jù)庫的備份也將成為問題。通常只能將數(shù)據(jù)使用mysqldump 導(dǎo)出，然后再導(dǎo)入解決這個問題。

獨立表空間模式：

優(yōu)點：?
1.每個表都有自已獨立的表空間。?
2.每個表的數(shù)據(jù)和索引都會存在自已的表空間中。?
3.可以實現(xiàn)單表在不同的數(shù)據(jù)庫中移動。?
4.空間可以回收（drop/truncate table方式操作表空間不能自動回收）?
5.對于使用獨立表空間的表，不管怎么刪除，表空間的碎片不會太嚴(yán)重的影響性能，而且還有機(jī)會處理。?
缺點：?
單表增加比共享空間方式更大。

結(jié)論：?
共享表空間在Insert操作上有一些優(yōu)勢，但在其它都沒獨立表空間表現(xiàn)好，所以我們要改成獨立表空間。?
當(dāng)啟用獨立表空間時，請合理調(diào)整一下 innodb_open_files 參數(shù)。

下面我們來講下如何講zabbix數(shù)據(jù)庫修改成獨立表空間模式

1.查看文件大小

[root@localhost ~]#cd /var/lib/mysql

[root@localhost ~]#ls -lh

-rw-rw---- 1 mysql mysql 41G Nov 24 13:31 ibdata1

-rw-rw---- 1 mysql mysql 5.0M Nov 24 13:31 ib_logfile0

-rw-rw---- 1 mysql mysql 5.0M Nov 24 13:31 ib_logfile1

drwx------ 2 mysql mysql 1.8M Nov 24 13:31 zabbix

大家可以看到這是沒修改之前的共享表數(shù)據(jù)空間文件ibdata1大小已經(jīng)達(dá)到了41G

2.清除zabbix數(shù)據(jù)庫歷史數(shù)據(jù)

1)查看哪些表的歷史數(shù)據(jù)比較多

[root@localhost ~]#mysql -uroot -p

mysql > select table_name, (data_length+index_length)/1024/1024 as total_mb, table_rows from information_schema.tables where table_schema='zabbix';

?

+-----------------------+---------------+------------+

| table_name? ? ? ? ? ? | total_mb? ? ? | table_rows |

+-----------------------+---------------+------------+

| acknowledges? ? ? ? ? |? ? 0.06250000 |? ? ? ? ? 0 |

....

| help_items? ? ? ? ? ? |? ? 0.04687500 |? ? ? ? 103 |

| history? ? ? ? ? ? ? | 1020.00000000 |? 123981681 |

| history_log? ? ? ? ? |? ? 0.04687500 |? ? ? ? ? 0 |

...

| history_text? ? ? ? ? |? ? 0.04687500 |? ? ? ? ? 0 |

| history_uint? ? ? ? ? | 3400.98437500 |? 34000562 |

| history_uint_sync? ? |? ? 0.04687500 |? ? ? ? ? 0 |

可以看到history和history_uint這兩個表的歷史數(shù)據(jù)最多。

另外就是trends,trends_uint中也存在一些數(shù)據(jù)。

由于數(shù)據(jù)量太大，按照普通的方式delete數(shù)據(jù)的話基本上不太可能。

所以決定直接采用truncate table的方式來快速清空這些表的數(shù)據(jù)，再使用mysqldump導(dǎo)出數(shù)據(jù)，刪除共享表空間數(shù)據(jù)文件，重新導(dǎo)入數(shù)據(jù)。

2）停止相關(guān)服務(wù)，避免寫入數(shù)據(jù)

[root@localhost ~]#/etc/init.d/zabbix_server stop

[root@localhost ~]#/etc/init.d/httpd stop

3）清空歷史數(shù)據(jù)

[root@localhost ~]#mysql -uroot -p

mysql > use zabbix;

Database changed

?

mysql > truncate table history;

Query OK, 123981681 rows affected (0.23 sec)

?

mysql > optimize table history;

1 row in set (0.02 sec)

?

mysql > truncate table history_uint;

Query OK, 57990562 rows affected (0.12 sec)

mysql > optimize table history_uint;

1 row in set (0.03 sec)

3.備份數(shù)據(jù)庫由于我/下的空間不足所以我掛載了一個NFS過來

[root@localhost ~]#mysqldump -uroot -p zabbix > /data/zabbix.sql

4.停止數(shù)據(jù)庫并刪除共享表空間數(shù)據(jù)文件

1)停止數(shù)據(jù)庫

[root@localhost ~]#/etc/init.d/mysqld stop

2）刪除共享表空間數(shù)據(jù)文件

[root@localhost ~]#cd /var/lib/mysql

[root@localhost ~]#rm -rf ib*

5.增加innodb_file_per_table參數(shù)

[root@localhost ~]#vi /etc/my.cnf

在[mysqld]下設(shè)置

innodb_file_per_table=1

6.啟動mysql

[root@localhost ~]#/etc/init.d/mysqld start

7.查看innodb_file_per_table參數(shù)是否生效

[root@localhost ~]#mysql -uroot -p

mysql> show variables like '%per_table%';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| innodb_file_per_table | ON |

+-----------------------+-------+

1 row in set (0.00 sec)

8.重新導(dǎo)入數(shù)據(jù)庫

[root@localhost ~]#mysqldump -uroot -p zabbix < /data/zabbix.sql

9.最后，恢復(fù)相關(guān)服務(wù)進(jìn)程

[root@localhost ~]#/etc/init.d/zabbix_server start

[root@localhost ~]#/etc/init.d/httpd start

恢復(fù)完服務(wù)之后，查看/分區(qū)的容量就下去了，之前是99%，處理完之后變成了12%。可見其成效

總結(jié)

以上是生活随笔為你收集整理的实战Zabbix-Server数据库MySQL的libdata1文件过大的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。