如何为Zabbix MySQL设置独立表空间innodb_file_per_table

2025-03-19 技术教程

如何为Zabbix MySQL设置独立表空间innodb_file_per_table，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。

一台zabbix监控系统后台使用的MySQL DB宕掉，连上MySQL DB server看到硬盘快用满了，发现zabbix使用到的MySQL ibdata1文件有300多G，几乎占据了整个硬盘的空间

#df-hFilesystemSizeUsedAvailUse%Mountedon/dev/sda399G15G79G17%/devtmpfs3.9G03.9G0%/devtmpfs3.9G03.9G0%/dev/shmtmpfs3.9G8.4M3.9G1%/runtmpfs3.9G03.9G0%/sys/fs/cgroup/dev/sda1488M105M348M24%/boot/dev/sda2378G355G4.1G99%/datatmpfs798M0798M0%/run/user/0#lltotal371225844-rw-r-----1mysqlmysql16384Apr1721:42aria_log.00000001-rw-r-----1mysqlmysql52Apr1721:42aria_log_control-rw-rw----1mysqlmysql1224704Apr2222:38ddl_log.log-rw-r-----1mysqlmysql380123480064Apr2313:20ibdata1-rw-r-----1mysqlmysql5242880Apr2313:20ib_logfile0-rw-r-----1mysqlmysql5242880Apr2313:20ib_logfile1drwx------2mysqlmysql4096Apr1721:42mysqldrwx------2mysqlmysql4096Apr1721:42performance_schemadrwx------2mysqlmysql4096Apr2222:38zabbix

一看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会默认使用共用表空间，估计没有设置独立表空间，查看果然：

这里使用了共用表空间，即使对zabbix大表历史数据清理并执行alter table xxx engine=innodb;也无法收缩空间。

临时改善对策：

因为磁盘已满，为了让zabbix监控尽快恢复使用，请用户对server临时增加了一块500G硬盘。把DB数据copy到空间更大的新盘，并重新挂载原来盘为/data1,挂载新盘为原/data

开启DB服务后，zabbix监控恢复正常

永久改善对策：

Zabbix MySQL DB使用磁盘过大，虽然已经设置了清理历史分区数据任务，但监控数据过多导致现有保持策略还是会用满硬盘。上面说到共用表空间使用的ibdata1文件无法回收，只能想办法删除一部分历史数据且改用独立表空间。

思想：将DB数据导出备份(大表只备份近期历史数据)，删除原有共用表空间ibdata1文件，修改独立表空间配置，再导入备份数据，修改清理历史分区数据策略。

查zabbix DB中各表使用大小：

selectTABLE_NAME,(sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024/1024frominformation_schema.tableswheretable_schema="zabbix"GROUPBYTABLE_NAMEORDERBY2DESC

TABLE_NAME(sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024/1024history_uint104518.12500000history24653.62500000trends_uint5394.67187500events2808.06250000event_recovery1188.37500000trends1111.68750000history_str200.14062500

1.停止zabbix服务

#systemctlstopzabbix-server

2.导出zabbix DB除两个最大历史表之外基本表结构和数据
# 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

主要参数说明：

-R 导出procedure 和function

--ignore-table 指定不想导出的表名，如果有多个表不想导出就写多个--ignore-table

3.导出zabbix DB history和history_uint 近7天表数据和结构

因为zabbix table中保存的是时间戳，查出时间对应的时间戳

MariaDB[(none)]>selectunix_timestamp('2020-4-16');+-----------------------------+|unix_timestamp('2020-4-16')|+-----------------------------+|1586966400|+-----------------------------+1rowinset(0.00sec)

导出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.关闭DB服务

#systemctlstopmariadb.service

5. 修改innodb_file_per_table独立表空间参数

#vi/etc/my.cnfinnodb_file_per_table=1

6.删除ibdata1和日志文件(注：操作之前尽量做好备份)

#rm-rfibdata1#rm-rfib_logfile0#rm-rfib_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服务

#systemctlstartmariadb.service

注，开启DB后，ibdata1虽然被删除了，但zabbix DB依然存在，只是DB下面table无法访问了

8. 导入上面备份出来的数据

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

至此，共用表空间改为独立表空间完成，且/data硬盘使用空间大幅收缩(/data1为临时对策时加的盘，为迁移前DB文件大小)

[root@vswhzb01mysql]#du-sh*16Karia_log.000000014.0Karia_log_control128Mibdata164Mib_logfile05.0Mib_logfile0_old64Mib_logfile15.0Mib_logfile1_old1016Kmysql212Kperformance_schema41Gzabbix[root@vswhzb01mysql]#df-hFilesystemSizeUsedAvailUse%Mountedon/dev/sda399G16G79G17%/devtmpfs3.9G03.9G0%/devtmpfs3.9G03.9G0%/dev/shmtmpfs3.9G8.4M3.9G1%/runtmpfs3.9G03.9G0%/sys/fs/cgroup/dev/sda1488M105M348M24%/boottmpfs798M0798M0%/run/user/0/dev/sda2378G55G304G16%/data/dev/sdb1493G355G113G76%/data1

9.开启zabbix服务

#systemctlstartmariadb.service

10.最后，记得调整历史分区删除策略，不然监控数据多了硬盘还是会用完

注：zabbix历史分区删除设定可参考之前文章 http://blog.itpub.net/25583515/viewspace-2638892/

DROPPROCEDUREIFEXISTSzabbix.partition_maintenance_all;DELIMITER$$CREATEPROCEDURE`partition_maintenance_all`(SCHEMA_NAMEVARCHAR(32))BEGINCALLpartition_maintenance(SCHEMA_NAME,'history',30,24,7);CALLpartition_maintenance(SCHEMA_NAME,'history_log',30,24,7);CALLpartition_maintenance(SCHEMA_NAME,'history_str',30,24,7);CALLpartition_maintenance(SCHEMA_NAME,'history_text',30,24,7);CALLpartition_maintenance(SCHEMA_NAME,'history_uint',15,24,7);CALLpartition_maintenance(SCHEMA_NAME,'trends',180,24,7);CALLpartition_maintenance(SCHEMA_NAME,'trends_uint',180,24,7);END$$DELIMITER;

后续，因为已经使用了独立表空间innodb_file_per_table设定，即使硬盘再次被DB用满，使用drop partition等方式可以释放OS磁盘空间

看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注亿速云行业资讯频道，感谢您对亿速云的支持。