mysqldump备份数据出错

浏览数：24 / 时间：2015年06月12日

收到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>