如何為Zabbix MySQL設置獨立表空間innodb_file_per_tab

如何為Zabbix 設置獨立表空間innodb_file_per_table，很多新手對此不是很清楚，為了幫助大家解決這個(gè)難題，下面小編將為大家詳細講解，有這方面需求的人可以來(lái)學(xué)習下，希望你能有所收獲。

一臺zabbix監控系統后臺使用的MySQL DB宕掉，連上MySQL DB server看到硬盤(pán)快用滿(mǎn)了，發(fā)現zabbix使用到的MySQL ibdata1文件有300多G，幾乎占據了整個(gè)硬盤(pán)的空間

# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda3        99G   15G   79G  17% /
devtmpfs        3.9G     0  3.9G   0% /dev
tmpfs           3.9G     0  3.9G   0% /dev/shm
tmpfs           3.9G  8.4M  3.9G   1% /run
tmpfs           3.9G     0  3.9G   0% /sys/fs/cgroup
/dev/sda1       488M  105M  348M  24% /boot
/dev/sda2       378G  355G  4.1G  99% /data
tmpfs           798M     0  798M   0% /run/user/0
# ll
total 371225844
-rw-r----- 1 mysql mysql        16384 Apr 17 21:42 aria_log.00000001
-rw-r----- 1 mysql mysql           52 Apr 17 21:42 aria_log_control
-rw-rw---- 1 mysql mysql      1224704 Apr 22 22:38 ddl_log.log
-rw-r----- 1 mysql mysql 380123480064 Apr 23 13:20 ibdata1
-rw-r----- 1 mysql mysql      5242880 Apr 23 13:20 ib_logfile0
-rw-r----- 1 mysql mysql      5242880 Apr 23 13:20 ib_logfile1
drwx------ 2 mysql mysql         4096 Apr 17 21:42 mysql
drwx------ 2 mysql mysql         4096 Apr 17 21:42 performance_schema
drwx------ 2 mysql mysql         4096 Apr 22 22:38 zabbix

一看db版本，還是使用的MariaDB 5.5.56

# mysql -V
mysql  Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1

這個(gè)版本的DB會(huì )默認使用共用表空間，估計沒(méi)有設置獨立表空間，查看果然：

MariaDB [(none)]> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF    |
+-----------------------+-------+
1 row in set (0.00 sec)

這里使用了共用表空間，即使對zabbix大表歷史數據清理并執行alter table xxx engine=innodb;也無(wú)法收縮空間。

臨時(shí)改善對策：

因為磁盤(pán)已滿(mǎn)，為了讓zabbix監控盡快恢復使用，請用戶(hù)對server臨時(shí)增加了一塊500G硬盤(pán)。把DB數據copy到空間更大的新盤(pán)，并重新掛載原來(lái)盤(pán)為/data1,掛載新盤(pán)為原/data

開(kāi)啟DB服務(wù)后，zabbix監控恢復正常

永久改善對策：

Zabbix MySQL DB使用磁盤(pán)過(guò)大，雖然已經(jīng)設置了清理歷史分區數據任務(wù)，但監控數據過(guò)多導致現有保持策略還是會(huì )用滿(mǎn)硬盤(pán)。上面說(shuō)到共用表空間使用的ibdata1文件無(wú)法回收，只能想辦法刪除一部分歷史數據且改用獨立表空間。

思想：將DB數據導出備份(大表只備份近期歷史數據)，刪除原有共用表空間ibdata1文件，修改獨立表空間配置，再導入備份數據，修改清理歷史分區數據策略。

查zabbix DB中各表使用大?。?/p>

select TABLE_NAME,(sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024/1024 from information_schema.tables 
where table_schema="zabbix"
GROUP BY TABLE_NAME
ORDER BY 2 DESC

TABLE_NAME (sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024/1024 
history_uint 104518.12500000 
history 24653.62500000 
trends_uint 5394.67187500 
events 2808.06250000 
event_recovery 1188.37500000 
trends 1111.68750000 
history_str 200.14062500

1.停止zabbix服務(wù)

# systemctl stop zabbix-server

2.導出zabbix DB除兩個(gè)最大歷史表之外基本表結構和數據
# mysqldump -h227.0.0.1 -uroot -p'password' --default-character-set=utf8 --databases zabbix -R --ignore-table=zabbix.history --ignore-table=zabbix.history_uint --log-error=zabbix_base.log > zabbix_base.sql

主要參數說(shuō)明：

-R 導出procedure 和function

--ignore-table 指定不想導出的表名，如果有多個(gè)表不想導出就寫(xiě)多個(gè)--ignore-table

3.導出zabbix DB history和history_uint 近7天表數據和結構

因為zabbix table中保存的是時(shí)間戳，查出時(shí)間對應的時(shí)間戳

MariaDB [(none)]> select unix_timestamp('2020-4-16');
+-----------------------------+
| unix_timestamp('2020-4-16') |
+-----------------------------+
|                  1586966400 |
+-----------------------------+
1 row in set (0.00 sec)

導出history近7天表數據和結構

# mysqldump -h227.0.0.1 -uroot -p'password' --default-character-set=utf8 --databases zabbix --tables history --where="clock > 1586966400" --log-error=history.log > history.sql

導出history_uint近7天表數據和結構

# mysqldump -h227.0.0.1 -uroot -p'password' --default-character-set=utf8 --databases zabbix --tables history_uint --where="clock > 1586966400" --log-error=history_uint.log > history_uint.sql

4.關(guān)閉DB服務(wù)

# systemctl stop mariadb.service

5. 修改innodb_file_per_table獨立表空間參數

# vi /etc/my.cnf
innodb_file_per_table=1

6.刪除ibdata1和日志文件(注：操作之前盡量做好備份)

# rm -rf ibdata1 
# rm -rf ib_logfile0
# rm -rf ib_logfile1

注，刪除ibdata1主要為了釋放空間，重啟DB服務(wù)后會(huì )自動(dòng)重建一個(gè)空的。刪除日志文件是為了避免下面error出現：

[Note] InnoDB: The first innodb_system data file 'ibdata1' did not exist. A new tablespace will be created!
[ERROR] InnoDB: redo log file './ib_logfile0' exists. Creating system tablespace with existing redo log files is not recommended. Please delete all redo log files before creating new system tablespace.

7.開(kāi)啟DB服務(wù)

# systemctl start mariadb.service

注，開(kāi)啟DB后，ibdata1雖然被刪除了，但zabbix DB依然存在，只是DB下面table無(wú)法訪(fǎng)問(wèn)了

8. 導入上面備份出來(lái)的數據

# mysql -uroot -ppassword -h227.0.0.1 zabbix < zabbix_base.sql
# mysql -uroot -ppassword -h227.0.0.1 zabbix < history.sql
# mysql -uroot -ppassword -h227.0.0.1 zabbix < history_uint.sql

至此，共用表空間改為獨立表空間完成，且/data硬盤(pán)使用空間大幅收縮(/data1為臨時(shí)對策時(shí)加的盤(pán)，為遷移前DB文件大小)

[root@vswhzb01 mysql]# du -sh *
16K     aria_log.00000001
4.0K    aria_log_control
128M    ibdata1
64M     ib_logfile0
5.0M    ib_logfile0_old
64M     ib_logfile1
5.0M    ib_logfile1_old
1016K   mysql
212K    performance_schema
41G     zabbix
[root@vswhzb01 mysql]# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda3        99G   16G   79G  17% /
devtmpfs        3.9G     0  3.9G   0% /dev
tmpfs           3.9G     0  3.9G   0% /dev/shm
tmpfs           3.9G  8.4M  3.9G   1% /run
tmpfs           3.9G     0  3.9G   0% /sys/fs/cgroup
/dev/sda1       488M  105M  348M  24% /boot
tmpfs           798M     0  798M   0% /run/user/0
/dev/sda2       378G   55G  304G  16% /data
/dev/sdb1       493G  355G  113G  76% /data1

9.開(kāi)啟zabbix服務(wù)

# systemctl start mariadb.service

10.最后，記得調整歷史分區刪除策略，不然監控數據多了硬盤(pán)還是會(huì )用完

注：zabbix歷史分區刪除設定可參考之前文章 http://blog.itpub.net/25583515/viewspace-2638892/

DROP PROCEDURE IF EXISTS zabbix.partition_maintenance_all;
DELIMITER $$ 
CREATE PROCEDURE `partition_maintenance_all`(SCHEMA_NAME VARCHAR(32)) 
BEGIN 
                CALL partition_maintenance(SCHEMA_NAME, 'history', 30, 24, 7); 
                CALL partition_maintenance(SCHEMA_NAME, 'history_log', 30, 24, 7); 
                CALL partition_maintenance(SCHEMA_NAME, 'history_str', 30, 24, 7); 
                CALL partition_maintenance(SCHEMA_NAME, 'history_text', 30, 24, 7); 
                CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 15, 24, 7); 
                CALL partition_maintenance(SCHEMA_NAME, 'trends', 180, 24, 7); 
                CALL partition_maintenance(SCHEMA_NAME, 'trends_uint', 180, 24, 7); 
END$$ 
DELIMITER ;

后續，因為已經(jīng)使用了獨立表空間innodb_file_per_table設定，即使硬盤(pán)再次被DB用滿(mǎn)，使用drop partition等方式可以釋放OS磁盤(pán)空間