MySQL—异常处理

今天在一台测试服务器发现异常，开发人员发来一条sql语句发现无法执行，一直处于等待过程中，sql语句如下：

delete from live.* where _id = 'xxxxx'

登录服务器，连接mysql发现登录不上

查看错误日志，报错如下：

2019-01-07T02:12:40.663747Z 61131 [ERROR] InnoDB: posix_fallocate(): Failed to preallocate data for file /data/mysql/3306/innodb/ibtmp1, desired size 67108864 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/ operating-system-error-codes.html
2019-01-07T02:12:41.120936Z 61131 [Warning] InnoDB: 1048576 bytes should have been written. Only 1032192 bytes written. Retrying for the remaining bytes.
2019-01-07T02:12:41.121006Z 61131 [Warning] InnoDB: Retry attempts for writing partial data failed.
2019-01-07T02:12:41.121023Z 61131 [ERROR] InnoDB: Write to file /data/mysql/3306/innodb/ibtmp1failed at offset 50735349760, 1048576 bytes should have been written, only 1032192 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.
2019-01-07T02:12:41.121056Z 61131 [ERROR] InnoDB: Error number 28 means 'No space left on device'
2019-01-07T02:12:41.121068Z 61131 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
2019-01-07T02:12:41.121078Z 61131 [Warning] InnoDB: Error while writing 67108864 zeroes to /data/mysql/3306/innodb/ibtmp1 starting at offset 50679775232
2019-01-07T02:12:41.121129Z 61131 [ERROR] /usr/local/mysql/bin/mysqld: The table '/data/mysql/3306/tmp/#sql_61f8_5' is full
2019-01-07T02:12:41.137490Z 61133 [ERROR] InnoDB: posix_fallocate(): Failed to preallocate data for file /data/mysql/3306/innodb/ibtmp1, desired size 10502144 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/ operating-system-error-codes.html
2019-01-07T02:12:41.137706Z 61133 [Warning] InnoDB: Retry attempts for writing partial data failed.
2019-01-07T02:12:41.137722Z 61133 [Warning] InnoDB: Error while writing 10502144 zeroes to /data/mysql/3306/innodb/ibtmp1 starting at offset 50736381952
2019-01-07T02:12:41.137761Z 61133 [ERROR] /usr/local/mysql/bin/mysqld: The table '/data/mysql/3306/tmp/#sql_61f8_7' is full
2019-01-07T02:39:40.209105Z 61221 [ERROR] InnoDB: posix_fallocate(): Failed to preallocate data for file /data/mysql/3306/innodb/ibtmp1, desired [root

其中比较关键的这句：

/usr/local/mysql/bin/mysqld: The table '/data/mysql/3306/tmp/#sql_61f8_5' is full

检查服务器磁盘使用情况，发现负载较高，

df -h命令发现磁盘100%使用率，ibtmp1文件占用49G

故障处理：

删除了服务器上的一些日志文件，这时mysql可以登录

运行 show processlist命令

发现有几条sql处于等待状态

正是这几个sql导致的tmp文件暴增

sql语句如下：

desc (SELECT
	lam.anchor_id,
	lam.cus_id,
	lam.anchor_nick_name,
	lam.theme,
	lam.classification,
	lam.live_status,
	times,
	lam.l_group,
	lam.screencap,
	lam.l_record,
	lam.sort,
	cc.ol_number,
	lpd.pic_name,
	lpd.pic_path,
	lam.appllication_time,
	lam.start_broadcasting_time,
	lc.lc_name classificationName
FROM
	live.live_anchor_management lam
	LEFT JOIN live.live_picture_dict lpd ON lam.anchor_id = lpd.reference_id
	AND lpd.pic_property = 0
	LEFT JOIN customer cc ON lam.cus_id = cc.cus_id
	LEFT JOIN live.live_category lc ON lam.classification = lc.lc_id
WHERE
	lam.verify_status = 0
	AND lam.live_status = 1
	AND lam.sort = 1
ORDER BY
	lam.sticky_time DESC
	LIMIT 9999999999 ) UNION
	DISTINCT (
SELECT
	lam.anchor_id,
	lam.cus_id,
	lam.anchor_nick_name,
	lam.theme,
	lam.classification,
	lam.live_status,
	times,
	lam.l_group,
	lam.screencap,
	lam.l_record,
	lam.sort,
	cc.ol_number,
	lpd.pic_name,
	lpd.pic_path,
	lam.appllication_time,
	lam.start_broadcasting_time,
	lc.lc_name classificationName
FROM
	live.live_anchor_management lam
	LEFT JOIN live.live_picture_dict lpd ON lam.anchor_id = lpd.reference_id
	AND lpd.pic_property = 0
	LEFT JOIN customer cc ON lam.cus_id = cc.cus_id
	LEFT JOIN live.live_category lc ON lam.classification = lc.lc_id
WHERE
	verify_status = 0
	AND live_status = 1
	)

重启数据库服务

恢复正常