MYSQL No space left on device

1、导入sql文件时mysql 挂了，查看原因为：

2018-08-10T07:26:18.310074Z 1083 [ERROR] InnoDB: Write to file ./everdata_knowledge/cell_v201601.ibdfailed at offset 1318060032, 1048576 bytes should have been written, only 643072 were written. Operating system error number 28. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.
2018-08-10T07:26:18.310121Z 1083 [ERROR] InnoDB: Error number 28 means 'No space left on device'

2、发现问题为没有磁盘空间；

查看系统磁盘：

[root@dmp8 log]# df -h
Filesystem           Size  Used Avail Use% Mounted on
/dev/mapper/cl-root   50G   50G  30k  100% /
devtmpfs              63G     0   63G   0% /dev
tmpfs                 63G     0   63G   0% /dev/shm
tmpfs                 63G   18M   63G   1% /run
tmpfs                 63G     0   63G   0% /sys/fs/cgroup
/dev/sdc             3.6T   94M  3.4T   1% /data2
/dev/sdb             3.6T  338M  3.4T   1% /data1
/dev/sda2           1014M  139M  876M  14% /boot
/dev/mapper/cl-home  3.5T  175G  3.1T   6% /home
tmpfs                 13G     0   13G   0% /run/user/0

3、查看mysql 存储数据目录：

[root@dmp8 log]# find /  -name *.MYD 
/var/lib/mysql/mysql/db.MYD
/var/lib/mysql/mysql/user.MYD
/var/lib/mysql/mysql/func.MYD
/var/lib/mysql/mysql/tables_priv.MYD
/var/lib/mysql/mysql/columns_priv.MYD
/var/lib/mysql/mysql/proc.MYD
/var/lib/mysql/mysql/procs_priv.MYD
/var/lib/mysql/mysql/event.MYD
/var/lib/mysql/mysql/ndb_binlog_index.MYD
/var/lib/mysql/mysql/proxies_priv.MYD
/var/lib/mysql/ever_ta/sys_user_group.MYD
/var/lib/mysql/ever_ta/ea_ball_day.MYD
/var/lib/mysql/ever_ta/sys_user_group_authority.MYD
/var/lib/mysql/ever_ta/ea_log_show.MYD
/var/lib/mysql/ever_ta/user.MYD
/var/lib/mysql/ever_ta/user_role.MYD
/var/lib/mysql/ever_ta/ea_log_click.MYD
/var/lib/mysql/ever_ta/ea_ball_day_city.MYD
/var/lib/mysql/ever_ta/role.MYD
/var/lib/mysql/ever_ta/sys_config.MYD
/var/lib/mysql/ever_ta/ea_ball_h_city.MYD
/var/lib/mysql/ever_ta/regularconfig.MYD
/var/lib/mysql/ever_ta/ea_ggsn_day.MYD
/var/lib/mysql/ever_ta/ea_ball_h.MYD
/var/lib/mysql/ever_ta/ea_channel_h.MYD
/var/lib/mysql/ever_ta/ea_channel_day.MYD
/var/lib/mysql/ever_ta/ea_ggsn_status_h.MYD
/var/lib/mysql/ever_ta/ea_log_visit_detail.MYD
/var/lib/mysql/ever_ta/ea_ggsn_h.MYD
/var/lib/mysql/ever_ta/ea_ball_show_day_type.MYD
/var/lib/mysql/ever_ta/ea_ball_click_day_type.MYD
/var/lib/mysql/ever_ta/ea_ball_show_h_type.MYD
/var/lib/mysql/ever_ta/ea_ball_click_h_type.MYD
/var/lib/mysql/ever_ta/everta_ad.MYD

4、查看文件大小：

[root@dmp8 mysql]# du -sh *
4.0K    auto.cnf
9.1M    everdmp
112K    eversunshine
23M     ever_ta
123M    hive
4.0K    ib_buffer_pool
76M     ibdata1
48M     ib_logfile0
48M     ib_logfile1
12M     ibtmp1
12M     mysql
0       mysql.sock
4.0K    mysql.sock.lock
1.1M    performance_schema
676K    sys
1.2G    everdata_knowledge

5、结论：确实是系统磁盘满了；（磁盘根目录根本找不到占用空间的文件，所以才初次下策，删除自己导入的大的数据库，如果根目录磁盘能找到大的不用的文件，直接删除，重启mysql即可；）删除了everdata_knowledge数据库；

6、重新启动mysql,报错找不到everdata_knowledge的表了；

2018-08-10T09:06:29.398083Z 0 [ERROR] InnoDB: Tablespace 2501 was not found at ./everdata_knowledge/crawler_imei_imeidb.ibd.
2018-08-10T09:06:29.398093Z 0 [ERROR] InnoDB: Set innodb_force_recovery=1 to ignore this and to permanently lose all changes to the tablespace.
2018-08-10T09:06:29.398100Z 0 [ERROR] InnoDB: Tablespace 2502 was not found at ./everdata_knowledge/crawler_vendor.ibd.
2018-08-10T09:06:29.398591Z 0 [ERROR] InnoDB: Cannot continue operation.

7、被迫在/etc/my.cnf 中设置：innodb_force_recovery=6;

    因为日志已经损坏，这里采用非常规手段,首先修改innodb_force_recovery参数，使mysqld跳过恢复步骤，将mysqld 启动,将数据导出来然后重建数据库。

innodb_force_recovery可以设置为1-6,大的数字包含前面所有数字的影响。

  1. (SRV_FORCE_IGNORE_CORRUPT):忽略检查到的corrupt页。
  2. (SRV_FORCE_NO_BACKGROUND):阻止主线程的运行，如主线程需要执行full purge操作，会导致crash。
  3. (SRV_FORCE_NO_TRX_UNDO):不执行事务回滚操作。
  4. (SRV_FORCE_NO_IBUF_MERGE):不执行插入缓冲的合并操作。
  5. (SRV_FORCE_NO_UNDO_LOG_SCAN):不查看重做日志，InnoDB存储引擎会将未提交的事务视为已提交。
  6. (SRV_FORCE_NO_LOG_REDO):不执行前滚的操作。

注意

  a 当设置参数值大于0后，可以对表进行select,create,drop操作,但insert,update或者delete这类操作是不允许的。

8、能正常查询等操作；导出数据库再重建；