ibdata1文件太大的解决办法

[TOC]

现状

• 突然收到 zabbix-server服务器的磁盘报警，登录 mysql 看到 zabbix 使用了 63GB，但是 zabbix 目录仅1.9MB，而 ibdata1 却有 84GB

[root@zabbix-server]# cd /var/lib/mysql
[root@zabbix-server]# du -sh *
84G     ibdata1
5.0M    ib_logfile0
5.0M    ib_logfile1
1.9M    zabbix           #.实际63G

发生原因

• 由于未启用独立表空间（即innodb_file_per_table=OFF），导致mysql将数据、索引、元数据全都存入到ibdata1文件中，时间久了这个文件就会变的非常大
• 对一些没用的大表进行清空和优化依然无效果，因为对共享表空间不起作用

MariaDB [zabbix]> truncate table xxx; optimize table xxx;

MariaDB [zabbix]> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+

解决办法

参考如下方法，把数据分别保存在各数据库子目录里的方法，这样新产生的ibdata1文件就不会太大了

• 1、利用 mysqldump 对zabbix库做一次逻辑备份（此mysql也只有zabbix这一个用户库）

mysqldump --single-transaction --master-data=2 --flush-logs --set-gtid-purged=off --triggers --events --routines --skip-tz-utc -uroot -p --databases zabbix > /data/zabbix.20181219.sql

• 2、停止MySQL服务

systemctl stop mariadb.service

• 3、删除ibdata1、ib_logfile*等共享表空间数据文件

cd /var/lib/mysql/
mv ibdata1 ibdata1.$(date +%Y%m%d%H%M%S)
mv ib_logfile0 ib_logfile0.$(date +%Y%m%d%H%M%S)
mv ib_logfile1 ib_logfile1.$(date +%Y%m%d%H%M%S)

• 4、修改my.cnf增加innodb_file_per_table独立表空间参数

vi /etc/my.cnf

在[mysqld]下添加一行
innodb_file_per_table=1

• 5、启动MySQL服务

systemctl start mariadb.service

• 6、查看参数是否生效

MariaDB [zabbix]> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+

• 7、将逻辑备份导入

mysql -uroot -p zabbix < /data/zabbix.20181219.sql