问题

生产环境应用响应特别慢，

查看应用日志发现Mysql有事务未提交，导致整个表锁死，

通过应用日志并没有定位到具体问题，(此时并没有想着 去看mysql的日志)

尝试重启Mysql，重启Mysql后，应用恢复正常，

但过了10分钟左右，又出现了同样的问题，

再次尝试重启Mysql，这次Mysql重启失败。error如下：

Mysql-error.log

2021-06-23T06:18:20.638605Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2021-06-23T06:18:20.638730Z 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.7.22-log) starting as process 5277 ...
2021-06-23T06:18:20.642968Z 0 [Note] InnoDB: PUNCH HOLE support available
2021-06-23T06:18:20.642996Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-06-23T06:18:20.643000Z 0 [Note] InnoDB: Uses event mutexes
2021-06-23T06:18:20.643003Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2021-06-23T06:18:20.643006Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2021-06-23T06:18:20.643009Z 0 [Note] InnoDB: Using Linux native AIO
2021-06-23T06:18:20.643231Z 0 [Note] InnoDB: Number of pools: 1
2021-06-23T06:18:20.643318Z 0 [Note] InnoDB: Using CPU crc32 instructions
2021-06-23T06:18:20.644636Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2021-06-23T06:18:20.651997Z 0 [Note] InnoDB: Completed initialization of buffer pool
2021-06-23T06:18:20.653943Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2021-06-23T06:18:20.665811Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2021-06-23T06:18:20.667163Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 6873821297
2021-06-23T06:18:20.667181Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 6873821306
2021-06-23T06:18:20.667188Z 0 [Note] InnoDB: Database was not shutdown normally!
2021-06-23T06:18:20.667192Z 0 [Note] InnoDB: Starting crash recovery.
2021-06-23T06:18:20.676424Z 0 [Note] InnoDB: Transaction 1217045 was in the XA prepared state.
2021-06-23T06:18:20.676923Z 0 [Note] InnoDB: Transaction 1217046 was in the XA prepared state.
2021-06-23T06:18:20.677619Z 0 [Note] InnoDB: Transaction 1217047 was in the XA prepared state.
2021-06-23T06:18:20.677747Z 0 [Note] InnoDB: Transaction 1217048 was in the XA prepared state.
2021-06-23T06:18:20.677762Z 0 [Note] InnoDB: Transaction 1217049 was in the XA prepared state.
2021-06-23T06:18:20.677770Z 0 [Note] InnoDB: Transaction 1217050 was in the XA prepared state.
2021-06-23T06:18:20.677778Z 0 [Note] InnoDB: Transaction 1217051 was in the XA prepared state.
2021-06-23T06:18:20.677785Z 0 [Note] InnoDB: Transaction 1217052 was in the XA prepared state.
2021-06-23T06:18:20.677793Z 0 [Note] InnoDB: Transaction 1217055 was in the XA prepared state.
2021-06-23T06:18:20.677801Z 0 [Note] InnoDB: Transaction 1217056 was in the XA prepared state.
2021-06-23T06:18:20.677809Z 0 [Note] InnoDB: Transaction 1217058 was in the XA prepared state.
2021-06-23T06:18:20.677820Z 0 [Note] InnoDB: Transaction 1217059 was in the XA prepared state.
2021-06-23T06:18:20.677827Z 0 [Note] InnoDB: Transaction 1217061 was in the XA prepared state.
2021-06-23T06:18:20.677834Z 0 [Note] InnoDB: Transaction 1217062 was in the XA prepared state.
2021-06-23T06:18:20.677841Z 0 [Note] InnoDB: Transaction 1217063 was in the XA prepared state.
2021-06-23T06:18:20.703588Z 0 [Note] InnoDB: Transaction 1217064 was in the XA prepared state.
2021-06-23T06:18:20.703641Z 0 [Note] InnoDB: Transaction 1217068 was in the XA prepared state.
2021-06-23T06:18:20.703655Z 0 [Note] InnoDB: Transaction 1217070 was in the XA prepared state.
2021-06-23T06:18:20.703668Z 0 [Note] InnoDB: 18 transaction(s) which must be rolled back or cleaned up in total 0 row operations to undo
2021-06-23T06:18:20.703672Z 0 [Note] InnoDB: Trx id counter is 1217536
2021-06-23T06:18:20.704667Z 0 [Note] InnoDB: Last MySQL binlog file position 0 12032917, file name mysql-bin.000257
2021-06-23T06:18:20.814280Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-06-23T06:18:20.814298Z 0 [Note] InnoDB: Starting in background the rollback of uncommitted transactions
2021-06-23T06:18:20.814345Z 0 [Note] InnoDB: Rollback of non-prepared transactions completed
2021-06-23T06:18:20.814348Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-06-23T06:18:20.820626Z 0 [Warning] InnoDB: 1048576 bytes should have been written. Only 802816 bytes written. Retrying for the remaining bytes.
2021-06-23T06:18:20.820655Z 0 [Warning] InnoDB: Retry attempts for writing partial data failed.
2021-06-23T06:18:20.820664Z 0 [ERROR] InnoDB: Write to file ./ibtmp1failed at offset 8388608, 1048576 bytes should have been written, only 802816 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.
2021-06-23T06:18:20.820677Z 0 [ERROR] InnoDB: Error number 28 means 'No space left on device'
2021-06-23T06:18:20.820682Z 0 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
2021-06-23T06:18:20.820705Z 0 [ERROR] InnoDB: Could not set the file size of './ibtmp1'. Probably out of disk space
2021-06-23T06:18:20.820710Z 0 [ERROR] InnoDB: Unable to create the shared innodb_temporary
2021-06-23T06:18:20.820714Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2021-06-23T06:18:21.323421Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2021-06-23T06:18:21.323446Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2021-06-23T06:18:21.323450Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2021-06-23T06:18:21.323454Z 0 [ERROR] Failed to initialize builtin plugins.
2021-06-23T06:18:21.323457Z 0 [ERROR] Aborting

2021-06-23T06:18:21.323463Z 0 [Note] Binlog end
2021-06-23T06:18:21.323946Z 0 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

分析

2021-06-23T06:18:20.820664Z 0 [ERROR] InnoDB: Write to file ./ibtmp1failed at offset 8388608, 1048576 bytes should have been written, only 802816 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.

linux 磁盘100%满 导致mysql启动失败

解决

删除无用文件腾出磁盘空间后 重启成功

思考

略