mysql ibdata1 过大_mysql文件ibdata1增长过大，导致服务器无法写的问题

背景

由云上的一个服务返回异常触发的，由于最近服务代码未有改动，之前运行正常，所以首先到服务所在的服务器检查服务的状态：

[root@manager-01 ~]# systemctl status

Jul 27 17:51:09 manager-01 node[14705]: { Error: ENOSPC: no space left on device, write errno: -28, code: 'ENOSPC', syscall: 'write' }

Jul 27 17:51:11 manager-01 node[14705]: { Error: ENOSPC: no space left on device, write errno: -28, code: 'ENOSPC', syscall: 'write' }

Jul 27 17:51:21 manager-01 node[14705]: { Error: ENOSPC: no space left on device, write errno: -28, code: 'ENOSPC', syscall: 'write' }

可以发现报错信息很明确：空间不足，无法写入

备注：OS版本是：CentOS 7

定位

首先查看下当前服务器空间使用情况：

[root@manager-01 ~]# df -h

Filesystem Size Used Avail Use% Mounted on

/dev/vda1 40G 38G 0 100% /

devtmpfs 1.9G 0 1.9G 0% /dev

tmpfs 1.9G 0 1.9G 0% /dev/shm

tmpfs 1.9G 384K 1.9G 1% /run

tmpfs 1.9G 0 1.9G 0% /sys/fs/cgroup

/dev/mapper/vg00-data 100G 33M 100G 1% /data

tmpfs 380M 0 380M 0% /run/user/1000

发现根目录/已经没有可用空间了

查看下是哪些目录空间用的最多，逐步排查：

[root@manager-01 /]# sudo du -s -h /* | sort -nr

798M /home

384K /run

372M /app

143M /boot

59M /etc

40K /tmp

35G /var

26M /root

16K /lost+found

4.0K /srv

4.0K /opt

4.0K /mnt

4.0K /media

1.7G /usr

......

可以发现/var目录使用了35G，占了87.5%的空间，按照上述方法逐步排查，最终发现：

[root@manager-01 mysql]# du -s -h ./* | sort -nr

1004K ./mysql

212K ./performance_schema

92K ./Alerts_DB

28G ./ibdata1

16K ./aria_log.00000001

5.0M ./ib_logfile1

5.0M ./ib_logfile0

4.0K ./test

4.0K ./aria_log_control

0 ./mysql.sock

在/var/lib/mysql目录下发现一个超大文件ibdata1，一个文件28G

百度下这个文件是何许内容：ibdata1文件是InnoDB存储引擎的共享表空间文件

该文件中主要存储着下面这些数据：

data dictionary

double write buffer

insert buffer/change buffer

rollback segments

undo space

Foreign key constraint system tables

解决方案

既然定位出是mysql的共享表空间文件增大导致的系统空间被耗完，那么就寻找解决方案吧，摸索过程都是百度各种解决方案，然后再虚机模拟操作，这里直接给出解决方法：

1、修改共享表空间为各表独立空间

这个方案网上有许多案例，这里给出自己的操作过程，供参考

数据库版本：

[t3mgr@manager-01 ~]$ mysql --version

mysql Ver 15.1 Distrib 5.5.65-MariaDB, for Linux (x86_64) using readline 5.1

我这里用的是mariadb 5.5.65版本

停止自己的业务，防止过程中有数据操作(我这是服务基本不可用了，所以没有影响，此处操作请根据自己实际情况判断)

systemctl stop

备份数据库(全库)

[root@manager /]# mysqldump -uroot -p --all-databases --add-drop-table > /data/db_backup.sql

为参数，实际操作请输入对应的密码

/data/db_backup.sql为备份的文件名，根据实际情况找个足够大的空间放置(大于原有mysql目录占用的空间肯定就足够了)

如果备份内容较大，可能会花费一定时间，要耐心等待...

修改mysql(mariadb)配置

配置文件一般为/etc/my.cnf

在[mysqld]下增加下面配置

innodb_file_per_table=1

验证修改是否生效

先将mysql(mariadb)启动

[root@manager /]# systemctl restart mariadb

然后进入mysql视图执行查看变量的命令：show variables like '%per_table%';

#mysql -uroot -p

mysql> show variables like '%per_table%';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| innodb_file_per_table | ON |

+-----------------------+-------+

1 row in set (0.00 sec)

如果对应的值为ON，则表示修改成功。

删除原来的数据库文件

在/var/lib/mysql目录下执行如下命令

[root@manager-01 mysql]# rm -rf ibdata1

[root@manager-01 mysql]# rm -rf ib_logfile*

[root@manager-01 mysql]# rm -rf

为自己的数据库名称

还原数据库

[root@daik-manager /]# mysql -uroot -p < /data/db_backup.sql

数据库较大时，这个步骤也会耗时，请等待...

经过以上几步后，可以看到新的ibdata1文件就只有几十M了，数据及索引都变成了针对单个表的小ibd文件了，它们在相应数据库的文件夹下面

尴尬的是，操作了这么半天，再查看下空间情况，腾挪出了3G空间，还是杯水车薪啊，没办法，继续想辙

2、将数据库挪到其他位置

这个方法主要是将mysql挪到别的有空间的目录，可以根据自己的实际情况，看哪个目录的挂载空间够大，就挪到哪去，这主要还是由于服务部署太随意，没有将mysql规划好，导致空间不足。

停止服务，同上

停止数据库，将mysql目录整体搬迁

[root@manager-01 mysql]#mv /var/lib/mysql /data

或者用如下方法迁移

rsync -avz   /var/lib/mysql /data/

rsync 免除迁移目标目录的目录属主及权限等操作

修改配置文件/etc/my.cnf

[mysqld]

innodb_file_per_table=1

#datadir=/var/lib/mysql

#socket=/var/lib/mysql/mysql.sock

datadir=/data/mysql

socket=/data/mysql/mysql.sock

创建软连接

[root@manager-01 mysql]#ln -s /data/mysql /var/lib/mysql

给目录赋予权限

[root@manager-01 mysql]#chown -R mysql.mysql /home/mysql/

数据库搬迁后，如果用户组和用户名都是mysql，则不用执行此步骤也可以。

重启数据库

[root@manager-01 mysql]#systemctl restart mariadb

至此，才将数据库成功搬迁位置，给系统目录腾出空间：

[root@manager-01 ~]# df -h

Filesystem Size Used Avail Use% Mounted on

/dev/vda1 40G 8.9G 29G 24% /

devtmpfs 1.9G 0 1.9G 0% /dev

tmpfs 1.9G 0 1.9G 0% /dev/shm

tmpfs 1.9G 384K 1.9G 1% /run

tmpfs 1.9G 0 1.9G 0% /sys/fs/cgroup

/dev/mapper/vg00-data 100G 42G 59G 42% /data

tmpfs 380M 0 380M 0% /run/user/1000