MySQL数据盘空间不足及实例启动问题处理记录
文件系统空间不足报错
2020-02-19 10:06:23 18723 [Warning] Disk is full writing '/data/mysql_3306/mysql-bin.002348' (Errcode: 28 - No space lef
t on device). Waiting for someone to free space...
2020-02-19 10:06:23 18723 [Warning] Retry in 60 secs. Message reprinted in 600 secs
2020-02-19 10:06:23 7f5848b2d7002020-02-19 10:06:23 18723 [ERROR] InnoDB: Failure of system call pwrite(). Operating sys
tem error number is 28.
InnoDB: Error number 28 means 'No space left on device'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html
2020-02-19 10:06:23 18723 [ERROR] /usr/local/mysql/bin/mysqld: The table 't_ply_cy_obj_prop' is full
2020-02-19 10:06:27 18723 [ERROR] Error writing file '/data/mysql_3306/slow.log' (errno: 1 - No space left on device)
InnoDB: Fatal error (Out of disk space) in rollback.
InnoDB: Out of tablespace.
InnoDB: Consider increasing your tablespace.
日志提示空间不够。马上清理了两个实例的slow log,指定参数文件重启实例,失败,日志如下:
实例启动日志:
2020-02-19 10:45:18 27127 [Note] Plugin 'FEDERATED' is disabled.
2020-02-19 10:45:18 27127 [Note] InnoDB: Using atomics to ref count buffer pool pages
2020-02-19 10:45:18 27127 [Note] InnoDB: The InnoDB memory heap is disabled
2020-02-19 10:45:18 27127 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-02-19 10:45:18 27127 [Note] InnoDB: Memory barrier is not used
2020-02-19 10:45:18 27127 [Note] InnoDB: Compressed tables use zlib 1.2.3
2020-02-19 10:45:18 27127 [Note] InnoDB: Using Linux native AIO
2020-02-19 10:45:18 27127 [Note] InnoDB: Using CPU crc32 instructions
2020-02-19 10:45:18 27127 [Note] InnoDB: Initializing buffer pool, size = 4.0G
2020-02-19 10:45:18 27127 [Note] InnoDB: Completed initialization of buffer pool
2020-02-19 10:45:18 27127 [Note] InnoDB: Highest supported file format is Barracuda.
2020-02-19 10:45:20 27127 [Note] InnoDB: 128 rollback segment(s) are active.
2020-02-19 10:45:20 27127 [Note] InnoDB: Waiting for purge to start
2020-02-19 10:45:20 27127 [Note] InnoDB: 5.6.35 started; log sequence number 1035198634229
2020-02-19 10:45:20 27127 [ERROR] Aborting
2020-02-19 10:45:20 27127 [Note] Binlog end
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'validate_password'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'partition'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'ARCHIVE'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'INNODB_METRICS'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'INNODB_CMPMEM'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'INNODB_CMP'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'INNODB_LOCKS'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'INNODB_TRX'
2020-02-19 10:45:20 27127 [Note] Shutting down plugin 'InnoDB'
2020-02-19 10:45:20 27127 [Note] InnoDB: FTS optimize thread exiting.
2020-02-19 10:45:20 27127 [Note] InnoDB: Starting shutdown...
2020-02-19 10:45:22 27127 [Note] InnoDB: Shutdown completed; log sequence number 1035198636854
2020-02-19 10:45:22 27127 [Note] Shutting down plugin 'BLACKHOLE'
2020-02-19 10:45:22 27127 [Note] Shutting down plugin 'MEMORY'
2020-02-19 10:45:22 27127 [Note] Shutting down plugin 'MRG_MYISAM'
2020-02-19 10:45:22 27127 [Note] Shutting down plugin 'CSV'
2020-02-19 10:45:22 27127 [Note] Shutting down plugin 'MyISAM'
2020-02-19 10:45:22 27127 [Note] Shutting down plugin 'sha256_password'
2020-02-19 10:45:22 27127 [Note] Shutting down plugin 'mysql_old_password'
2020-02-19 10:45:22 27127 [Note] Shutting down plugin 'mysql_native_password'
2020-02-19 10:45:22 27127 [Note] Shutting down plugin 'binlog'
只报出2020-02-19 10:45:20 27127 [ERROR] Aborting,没有更详细的错误,实例直接关闭。这种情况像是参数配置不对,经检查没有发现异常配置参数。
因为数据库不是双1(是02)怀疑是,文件不一致,再加上实例自动重启(mysqld_safe),导致个别文件损坏
在配置文件中设置innodb_force_recovery=0-6,重启,老样子,没有激起半点涟漪。。。
因为是测试环境,所以没有备份,没有从库,同事提出不行再建个实例,把文件拷贝过去试试。。。只能试试了。。。,同事在努力创建新实例中。。。。。。。。。
再次观察data目录,发现有mysql_upgrade_info文件,说明实例做过升级。
经同事确认,之前做过数据库升级,使用的是二进制包,软件目录在/usr/local/mysql/bin目录,而我们启动的是原来rpm包安装的目录,所以启动失败,但是MySQL什么错误也不报,这个有点坑爹。
最后,我们用升级后的数据库启动实例成功。
后面还有彩蛋:同事启动实例时,加载错了配置文件,导致应用连接报错。。。。。。
教训:
1)测试环境也应该加到监控中,避免发生低级错误
2)测试环境也应该备份,并增加从库
3)安装配置的标准化多么重要
4)仔细观察,如果早注意到mysql_upgrade_info文件,不至于费这么大劲