关于mysql:mysql文件ibdata1增长过大导致服务器无法写的问题

由云上的一个服务返回异样触发的，因为最近服务代码未有改变，之前运行失常，所以首先到服务所在的服务器查看服务的状态：

[root@manager-01 ~]# systemctl status <service-name>
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

参考链接：
https://mp.weixin.qq.com/s/KD2qLrmWY80yFxUtxJVNMA

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

这个计划网上有许多案例，这里给出本人的操作过程，供参考
数据库版本：

[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 <service-name>

备份数据库（全库）

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

<password> 为参数，实际操作请输出对应的明码
/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<password>
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 <db-name>

<db-name> 为本人的数据库名称

还原数据库

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

数据库较大时，这个步骤也会耗时，请期待 …
通过以上几步后，能够看到新的 ibdata1 文件就只有几十 M 了，数据及索引都变成了针对单个表的小 ibd 文件了，它们在相应数据库的文件夹上面

难堪的是，操作了这么半天，再查看下空间状况，腾挪出了 3G 空间，还是无济于事啊，没方法，持续想辙

这个办法次要是将 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

关于mysql:mysql文件ibdata1增长过大导致服务器无法写的问题

背景

定位

解决方案

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

2、将数据库挪到其余地位