问题：
接到用户反馈一台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 -h127.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 -h127.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 -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
# mysql -uroot -ppassword -h127.0.0.1 zabbix
# mysql -uroot -ppassword -h127.0.0.1 zabbix
至此，共用表空间改为独立表空间完成，且/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.最后，记得调整历史分区删除策略，不然监控数据多了硬盘还是会用完
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磁盘空间
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
