mysqldump备份数据出错
收到nagios报警,提示mysql备份失败,线上使用的是逻辑备份,也就是使用mysqldump,由于数据比较小,也就没在乎速度神马的问题。好吧,那就查查是什么原因导致备份失败,由于备份是写成脚本定时执行的,我就抽取里面mysqldump命令执行,看是否会抛什么错误,果不其然,报错如下:
又想到去看看错误日志,是否有异常
[root@localhost data]# tail -n 10 localhost.localdomain.err140320
140320 9:00:00 [ERROR] /usr/local/mysql-5.1.66/libexec/mysqld: Sort aborted 140320 9:00:00 [ERROR] /usr/local/mysql-5.1.66/libexec/mysqld: Sort aborted 140320 9:00:00 [ERROR] /usr/local/mysql-5.1.66/libexec/mysqld: Sort aborted 140320 10:00:00 [ERROR] /usr/local/mysql-5.1.66/libexec/mysqld: Sort aborted 140320 10:00:00 [ERROR] /usr/local/mysql-5.1.66/libexec/mysqld: Sort aborted 140320 10:00:00 [ERROR] /usr/local/mysql-5.1.66/libexec/mysqld: Sort aborted 140320 10:00:00 [ERROR] /usr/local/mysql-5.1.66/libexec/mysqld: Sort aborted 140320 10:00:00 [ERROR] /usr/local/mysql-5.1.66/libexec/mysqld: Sort aborted 140320 10:00:00 [ERROR] /usr/local/mysql-5.1.66/libexec/mysqld: Sort aborted [root@localhost data]#
看的出来是无法排序,mysql在group by,order by的时候,如果无法使用索引排序,就会使用内存临时表,要是超过内存临时表的限制,就会在磁盘上创建临时表,也就是myisam表,默认目录是/tmp
我就郁闷了,group by,order by使用临时表这个知道,难道mysqldump也使用临时表?
mysql> show variables like '%tmpdir%'; +-------------------+-----------+ | Variable_name | Value | +-------------------+-----------+ | slave_load_tmpdir | /data/tmp | | tmpdir | /data/tmp | +-------------------+-----------+ 2 rows in set (0.00 sec) mysql>
可以看见是在/data/tmp 目录,于是看看/data/目录权限,发现权限是root的,mysql根本没法创建,于是手动在/data/目录下创建tmp,并且
chown mysql.mysql tmp
然后再试着执行mysqldump备份一个表,没有报错了,查看错误日志也没有继续报错了,询问了另外一个运维,说是他改的,将临时表目录/tmp改成了/data/tmp,由于没有注意权限问题,才导致备份失败,以及mysql无法排序。
其实我们可以将临时表的目录设置为/dev/shm,该目录是内存文件系统,速度非常快;这也算是一种优化手段
mysql> show variables like '%tmp%'; +-------------------+----------+ | Variable_name | Value | +-------------------+----------+ | max_tmp_tables | 32 | | slave_load_tmpdir | /dev/shm | | tmp_table_size | 16777216 | | tmpdir | /dev/shm | +-------------------+----------+ 4 rows in set (0.01 sec) mysql>