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

58次阅读

共计 5497 个字符，预计需要花费 14 分钟才能阅读完成。

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

一台 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

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)]  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. 关闭 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 -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 硬盘使用空间大幅收缩 (/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 磁盘空间

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

正文完