默认临时目录带来的权限问题-优快云博客

一客户的本地数据库发现不能对其中的表进行查询，无法进行任何操作，联系我进行排查：
登录数据库：

[root@localhost tmp]# mysql -u root -pxxxxx
Welcome TO the MySQL monitor.  Commands END WITH ; OR \g.
Your MySQL connection id IS 2
Server version: 5.0.77 SOURCE distribution
 
TYPE 'help;' OR '\h' FOR help. TYPE '\c' TO clear the buffer.
 
mysql> USE customperformance2;
DATABASE changed
mysql> SELECT COUNT(*) FROM erp_user;
ERROR 1033 (HY000): Incorrect information IN file: '.\customperformance2\erp_user.frm'
mysql> DESC erp_user;         
ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_13b3_0.MYI' (Errcode: 13)

$perror 13
OS error code  13:  Permission denied

为什么会出现这样的错误，为什么会报没有权限的错误？由于用户环境还不是很熟悉，先要了解一下用户数据库的安装情况：

[root@localhost tmp]# lsof -nc mysqld | grep -vE '(\.so(\..*)?$|\.frm|\.MY?|\.ibd|ib_logfile|ibdata|TCP)'
COMMAND    PID  USER   FD   TYPE             DEVICE     SIZE     NODE NAME
mysqld_sa 5002  root  cwd    DIR              253,0     4096        2 /
mysqld_sa 5002  root  rtd    DIR              253,0     4096        2 /
mysqld_sa 5002  root  txt    REG              253,0   801512   262190 /bin/bash
mysqld_sa 5002  root  mem    REG              253,0 56458704 59476863 /usr/lib/locale/locale-archive
mysqld_sa 5002  root  mem    REG              253,0    25464 59572719 /usr/lib64/gconv/gconv-modules.cache
mysqld_sa 5002  root    0r   CHR                1,3              2127 /dev/NULL
mysqld_sa 5002  root    1w   CHR                1,3              2127 /dev/NULL
mysqld_sa 5002  root    2w   CHR                1,3              2127 /dev/NULL
mysqld_sa 5002  root  255r   REG              253,0    13073 59486803 /usr/bin/mysqld_safe
mysqld    5043 mysql  cwd    DIR              253,0     4096 63013326 /var/lib/mysql
mysqld    5043 mysql  rtd    DIR              253,0     4096        2 /
mysqld    5043 mysql  txt    REG              253,0  7721832 59480637 /usr/libexec/mysqld
mysqld    5043 mysql    0r   CHR                1,3              2127 /dev/NULL
mysqld    5043 mysql    1w   REG              253,0   405427 62980617 /var/log/mysqld.log
mysqld    5043 mysql    2w   REG              253,0   405427 62980617 /var/log/mysqld.log
mysqld    5043 mysql    4u  unix 0xffff81031a97b1c0             22639 /var/lib/mysql/mysql.sock
mysqld    5043 mysql   18u  unix 0xffff8103312ecf00             22662 /var/lib/mysql/mysql.sock

用户的数据目录/var/lib/mysql：

[root@localhost log]# ls -lh /var/lib/mysql
总计 208G
drwxrwxrwx 2 mysql mysql  16K 05-10 01:22 customperformance2
-rwxrwxrwx 1 mysql mysql 208G 09-29 19:17 ibdata1
-rwxrwxrwx 1 mysql mysql 5.0M 09-29 19:17 ib_logfile0
-rwxrwxrwx 1 mysql mysql 5.0M 09-29 19:17 ib_logfile1
drwxrwxrwx 2 mysql mysql 4.0K 2011-05-30 keepsession
drwxrwxrwx 2 mysql mysql 4.0K 2011-05-30 mysql
-rwxrwxrwx 1 mysql mysql    1 07-07 17:08 mysql-bin.INDEX
srwxrwxrwx 1 mysql mysql    0 09-29 16:05 mysql.sock
drwxrwxrwx 2 mysql mysql 4.0K 2011-12-14 receiver_analyse
drwxrwxrwx 2 mysql mysql 4.0K 2011-05-30 test
drwxrwxrwx 2 mysql mysql  20K 2011-12-30 weibopromotion
You have NEW mail IN /var/spool/mail/root

可以看到目录的权限是正确的，这个时候在看看数据库的errorlog中有没有开排查的信息：

vi /var/log/mysqld.log
120929 15:57:15  mysqld ended
120929 15:57:15  mysqld started
^G/usr/libexec/mysqld: Can't create/write to file '/tmp/ibKbaUfl' (Errcode: 13)
120929 15:57:16  InnoDB: Error: unable to create temporary file; errno: 13

查看/tmp目录的权限
[root@localhost tmp]# ls  -lh /tmp/
总计 20K
drwx------ 2 root root 16K 09-29 11:37 lost+found
-rw-r--r-- 1 root root   0 09-29 12:00 tmp_domain

这里已经很明确了，mysql没有权限对/tmp进行操作：
这个时候我们可以有两种办法，一种为改变/tmp的权限，另一种为改变数据库的临时目录；我采取了修改数据库临时目录的方法：

vi /etc/my.cnf
# Point the following paths TO different dedicated disks
# tmpdir          = /tmp
#log-UPDATE     = /path-to-dedicated-directory/hostname

可以看到用户的tmpdir变量并没有指定，被注释掉，所以数据库会默认指定到/tmp目录；
修改其临时目录到用户的数据目录：

# Point the following paths TO different dedicated disks
 tmpdir          = /var/lib/mysql/
#log-UPDATE     = /path-to-dedicated-directory/hostname

重启mysql后，数据库恢复正常。