一客户的本地数据库发现不能对其中的表进行查询，无法进行任何操作，联系我进行排查：
登录数据库：
[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后，数据库恢复正常。
