為Zabbix MySQL設定獨立表空間innodb_file_per_table

問題：

接到使用者反饋一臺zabbix監控系統後臺使用的MySQL DB宕掉，連上MySQL DB server看到硬碟快用滿了，發現zabbix使用到的MySQL ibdata1檔案有300多G，幾乎佔據了整個硬碟的空間

# 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

這個版本的DB會預設使用共用表空間，估計沒有設定獨立表空間，檢視果然：

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;也無法收縮空間。

臨時改善對策：

因為磁碟已滿，為了讓zabbix監控儘快恢復使用，請使用者對server臨時增加了一塊500G硬碟。把DB資料copy到空間更大的新盤，並重新掛載原來盤為/data1,掛載新盤為原/data

開啟DB服務後，zabbix監控恢復正常

永久改善對策：

Zabbix MySQL DB使用磁碟過大，雖然已經設定了清理歷史分割槽資料任務，但監控資料過多導致現有保持策略還是會用滿硬碟。上面說到共用表空間使用的ibdata1檔案無法回收，只能想辦法刪除一部分歷史資料且改用獨立表空間。

思想：將DB資料匯出備份(大表只備份近期歷史資料)，刪除原有共用表空間ibdata1檔案，修改獨立表空間配置，再匯入備份資料，修改清理歷史分割槽資料策略。

查zabbix DB中各表使用大小：

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服務

# systemctl stop zabbix-server

主要引數說明：

-R 匯出procedure 和function

--ignore-table 指定不想匯出的表名，如果有多個表不想匯出就寫多個--ignore-table

3.匯出zabbix DB history和history_uint 近7天表資料和結構

因為zabbix table中儲存的是時間戳，查出時間對應的時間戳

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

匯出history近7天表資料和結構

匯出history_uint近7天表資料和結構

# mysqldump -h127.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.關閉DB服務

# 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服務後會自動重建一個空的。刪除日誌檔案是為了避免下面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.開啟DB服務

# systemctl start mariadb.service

注，開啟DB後，ibdata1雖然被刪除了，但zabbix DB依然存在，只是DB下面table無法訪問了

8. 匯入上面備份出來的資料

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

至此，共用表空間改為獨立表空間完成，且/data硬碟使用空間大幅收縮(/data1為臨時對策時加的盤，為遷移前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.開啟zabbix服務

# systemctl start mariadb.service

10.最後，記得調整歷史分割槽刪除策略，不然監控資料多了硬碟還是會用完

注：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 ;

後續，因為已經使用了獨立表空間innodb_file_per_table設定，即使硬碟再次被DB用滿，使用drop partition等方式可以釋放OS磁碟空間