MySQL数据盘空间满了

备注:
测试版本MySQL 5.7

一.问题描述

今天遇到一个yum安装的mysql 5.7,批量写入数据的时候磁盘满了。
报错提示:The table‘xxxx’is full

查看磁盘空间,确实满了

[root@10-31-1-122 ~]# df -h
文件系统                 容量  已用  可用 已用% 挂载点
devtmpfs                 3.9G     0  3.9G    0% /dev
tmpfs                    3.9G     0  3.9G    0% /dev/shm
tmpfs                    3.9G   27M  3.8G    1% /run
tmpfs                    3.9G     0  3.9G    0% /sys/fs/cgroup
/dev/mapper/centos-root   50G   50G   20K  100% /
/dev/sda1               1014M  185M  830M   19% /boot
/dev/mapper/centos-home  142G   40M  142G    1% /home
tmpfs                    783M   68K  783M    1% /run/user/0
tmpfs                    783M   12K  783M    1% /run/user/42

二.清理binlog

mysql> purge master logs before '2020-11-19';
Query OK, 0 rows affected, 2 warnings (0.51 sec)

mysql> 

上述命令是将20201119日之前的binlog都清理掉，如果当前使用的binlog小于这个时间，是不会被清理的。

三.更改数据盘

vi /etc/my.cnf
datadir=/home/mysql/data/3306

## 重启mysql服务
service mysqld restart

原有数据目录与新数据目录进行对比

1. binlog被重置了，新数据目录生成了编号为 000001的新的binlog
2. mysql自带的数据库迁移过来了，但是自己创建的test库并没有迁移过来
3. 迁移过来之后权限信息都重置了

-- 原数据目录
[root@10-31-1-122 mysql]# pwd
/var/lib/mysql
[root@10-31-1-122 mysql]# 
[root@10-31-1-122 mysql]# ls -lrth
总用量 976M
-rw-r-----. 1 mysql mysql   56 8月  24 15:57 auto.cnf
-rw-------. 1 mysql mysql 1.7K 8月  24 15:57 ca-key.pem
-rw-r--r--. 1 mysql mysql 1.1K 8月  24 15:57 ca.pem
-rw-------. 1 mysql mysql 1.7K 8月  24 15:57 server-key.pem
-rw-r--r--. 1 mysql mysql 1.1K 8月  24 15:57 server-cert.pem
-rw-------. 1 mysql mysql 1.7K 8月  24 15:57 client-key.pem
-rw-r--r--. 1 mysql mysql 1.1K 8月  24 15:57 client-cert.pem
-rw-r--r--. 1 mysql mysql  452 8月  24 15:57 public_key.pem
-rw-------. 1 mysql mysql 1.7K 8月  24 15:57 private_key.pem
drwxr-x---. 2 mysql mysql 8.0K 8月  24 15:57 performance_schema
drwxr-x---. 2 mysql mysql 8.0K 8月  24 15:57 sys
drwxr-x---. 2 mysql mysql 4.0K 8月  31 10:18 mysql
drwxr-x---. 2 mysql mysql   62 8月  31 11:53 pt
drwxr-x---. 2 mysql mysql  162 11月 18 18:30 test
-rw-r-----  1 mysql mysql 804M 11月 19 09:12 10-31-1-122-bin.000031
-rw-r-----  1 mysql mysql   50 11月 19 09:12 10-31-1-122-bin.index
-rw-r-----. 1 mysql mysql  48M 11月 19 09:12 ib_logfile1
-rw-r-----  1 mysql mysql  213 11月 19 09:12 10-31-1-122-relay-bin.000060
-rw-r-----  1 mysql mysql   62 11月 19 09:12 10-31-1-122-relay-bin.index
-rw-r-----. 1 mysql mysql   73 11月 19 09:12 relay-log.info
-rw-r-----. 1 mysql mysql  131 11月 19 09:12 master.info
-rw-r-----  1 mysql mysql  349 11月 19 09:12 10-31-1-122-relay-bin.000061
-rw-r-----  1 mysql mysql  177 11月 19 09:12 10-31-1-122-bin.000032
-rw-r-----  1 mysql mysql 5.6K 11月 19 09:12 ib_buffer_pool
-rw-r-----. 1 mysql mysql  48M 11月 19 09:12 ib_logfile0
-rw-r-----. 1 mysql mysql  76M 11月 19 09:12 ibdata1

-- 现有数据目录
[root@10-31-1-122 mysql]# cd /home/mysql/data/3306
[root@10-31-1-122 3306]# 
[root@10-31-1-122 3306]# 
[root@10-31-1-122 3306]# ls -lrth
总用量 121M
-rw-r----- 1 mysql mysql  48M 11月 19 09:12 ib_logfile1
-rw-r----- 1 mysql mysql   56 11月 19 09:12 auto.cnf
-rw------- 1 mysql mysql 1.7K 11月 19 09:12 ca-key.pem
-rw-r--r-- 1 mysql mysql 1.1K 11月 19 09:12 ca.pem
-rw------- 1 mysql mysql 1.7K 11月 19 09:12 server-key.pem
-rw-r--r-- 1 mysql mysql 1.1K 11月 19 09:12 server-cert.pem
-rw------- 1 mysql mysql 1.7K 11月 19 09:12 client-key.pem
-rw-r--r-- 1 mysql mysql 1.1K 11月 19 09:12 client-cert.pem
-rw-r--r-- 1 mysql mysql  452 11月 19 09:12 public_key.pem
-rw------- 1 mysql mysql 1.7K 11月 19 09:12 private_key.pem
drwxr-x--- 2 mysql mysql 8.0K 11月 19 09:12 performance_schema
drwxr-x--- 2 mysql mysql 4.0K 11月 19 09:12 mysql
drwxr-x--- 2 mysql mysql 8.0K 11月 19 09:12 sys
-rw-r----- 1 mysql mysql  177 11月 19 09:12 10-31-1-122-bin.000001
-rw-r----- 1 mysql mysql  431 11月 19 09:12 ib_buffer_pool
-rw-r----- 1 mysql mysql   50 11月 19 09:12 10-31-1-122-bin.index
-rw-r----- 1 mysql mysql  154 11月 19 09:12 10-31-1-122-bin.000002
-rw-r----- 1 mysql mysql  12M 11月 19 09:12 ibtmp1
-rw-r----- 1 mysql mysql  12M 11月 19 09:12 ibdata1
-rw-r----- 1 mysql mysql  48M 11月 19 09:12 ib_logfile0

3.1 mysql的权限调整

登陆报错:

-- 居然登陆不上了
[root@10-31-1-122 ~]# mysql -uroot -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

-- 忽略授权表的方式登陆mysql
vi /etc/my.cnf
[mysqld]
skip-grant-tables

-- 重启mysql服务
systemctl restart mysqld

之前创建的账号都不存在了:

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select user,host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)

新增用户并修改授权

-- 一定要先刷新权限，不然后面创建用户语句会报错
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> create user root@'10.31.1.%' identified by 'abc123';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to root@'10.31.1.%';
Query OK, 0 rows affected (0.00 sec)

mysql> alter user root@'localhost' identified by 'abc123';
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

去掉忽略授权表，重启mysql服务

vi /etc/my.cnf
#skip-grant-tables

## 重启mysql服务
systemctl restart mysqld

3.2 原有的数据问题

看了下，test数据库不存在了，新的库不认之前的数据了，这个就悲哀了。
网上找了下资料，如果要迁移数据盘，要先把数据mysqldump下来，然后迁移完数据盘之后，再进行导入。

mysql> use test;
ERROR 1049 (42000): Unknown database 'test'