错误 1006 (HY000) 无法创建数据库 (errno: 13) MySQL 5.6.12

ERROR 1006 (HY000) Can't create database (errno: 13) MySQL 5.6.12

提问by hardy_sandy

I am facing problem creating the database and it results in following error.

我在创建数据库时遇到问题，并导致以下错误。

mysql> show grants for 'admin'@'%';
+---------------------------------------------------------------------------------------------------------------------------------+
| Grants for admin@%                                                                                                              |
+---------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*4ACFE3202A5FF5CF467898FC58AAB1D615029441' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create database abc;
ERROR 1006 (HY000): Can't create database 'abc' (errno: 13)

Here is my users table.

这是我的用户表。

mysql> select host, user from mysql.user;
+-------------+-------+
| host        | user  |
+-------------+-------+
| %           | admin |
| 127.0.0.1   | root  |
| ::1         | root  |
| IVM-MMC-DGW | root  |
| localhost   | admin |
| localhost   | root  |
+-------------+-------+
6 rows in set (0.00 sec)


mysql> show grants;
+-----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for admin@localhost                                                                                                              |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD '*4ACFE3202A5FF5CF467898FC58AAB1D615029441' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

I can create and remove table in the existing database.

我可以在现有数据库中创建和删除表。

The data directory already has mysql:mysql privileges and also the logged in user has privilege to create the new database.

数据目录已经具有 mysql:mysql 权限，并且登录用户也具有创建新数据库的权限。

What configuration is missing here ?

这里缺少什么配置？

回答by Lucas

There may be a permissions issue with the MySQL data directory. You could try setting the permissions as follows (adjust the path to your data directory)

MySQL 数据目录可能存在权限问题。您可以尝试如下设置权限（调整数据目录的路径）

chown -R mysql:mysql /usr/local/mysql/data

回答by jfly

This happens probably due to permission denied by MySQL. Though you set the datadir=/drbd0/mysql/data, but by default MySql set socket = /var/lib/mysql/mysql.sock, so you should have the privileges to that directory.

发生这种情况的原因可能是 MySQL 拒绝了权限。尽管您设置了datadir=/drbd0/mysql/data，但默认情况下是 MySql set socket = /var/lib/mysql/mysql.sock，因此您应该拥有该目录的权限。

less /etc/group

and then

进而

less /etc/passwd

find mysql user and group name, by default, it's mysql user in mysql group.

找到mysql用户和组名，默认是mysql组中的mysql用户。

change to mysql dir, probably /var/lib/mysqland then type cd ..to go up one directory.

更改到 mysql 目录，可能/var/lib/mysql然后键入cd ..上一级目录。

chown -R mysql:mysql ./mysql/

Replace mysql:mysqlwith something different if you group and user privileges are called differenty

mysql:mysql如果您的组和用户权限称为不同，请替换为不同的内容

回答by sandejai

It might be problem with space. Follow this

可能是空间问题。按照这个

1. Check .err logs at /var/lib/mysql

2. if the log says something like "[ERROR] Can't start server: can't create PID file: No space left on device"

3. Check /var size by df -hk /var

4. if used is 100% , then u have to find the files which is geting filled.

5. find large file in /var by find /var/ -type f -size +100000k -exec ls -lh {} \; | awk '{ print $9 ": " $5 }'

6. see which file you can delete and then restart the mysql process by

7. /etc/init.d/mysql restart

1. 检查 /var/lib/mysql 中的 .err 日志

2. 如果日志显示类似“[错误] 无法启动服务器：无法创建 PID 文件：设备上没有剩余空间”

3. 通过 df -hk /var 检查 /var 大小

4. 如果使用的是 100% ，那么你必须找到被填满的文件。

5. 通过 find /var/ -type f -size +100000k -exec ls -lh {} \ 在 /var 中查找大文件 | awk '{ 打印 $9 ": " $5 }'

6. 查看您可以删除哪个文件，然后通过以下方式重新启动 mysql 进程

7. /etc/init.d/mysql 重启

let me know if that worked :)

让我知道这是否有效:)

回答by Harikrishnan

For Mac OS use this command

对于 Mac OS 使用此命令

sudo chown -R mysql:mysql /usr/local/mysql*

User mysqlshould have write access to mysql data directory.

用户mysql应该对 mysql 数据目录具有写访问权限。

回答by Liuda

sudo chown -R mysql:mysql /var/lib/mysql/

回答by bywronski

Turn off SELinux especially if you're on CentOS.

关闭 SELinux，尤其是当您使用 CentOS 时。

Check Turning off or disabling SELinux.

选中关闭或禁用 SELinux。

回答by Guilherme Lopes

if you move the mysql folder you restart mysql, it works

如果您移动 mysql 文件夹重新启动 mysql，它会起作用

回答by Yanhui Zhou

1. Check your mysql data directory permissions.
   The data directory is configured in '/etc/my.conf' by default.
   Or use mysqladmin -uroot -p variables | grep datadirto find the directory.
   It may be /var/lib/mysql/by default, and makesure the owner user and group is mysql. chown -R mysql:mysql /usr/local/mysql/data

2. If it has not been solved by step 1. It may be because of SELinuxin your way, try to turn off it.

   • Test if SELinux is running. selinuxenabled && echo enabled || echo disabled
   • Turning off SELinux temporarily. echo 0 > /selinux/enforce
   • Completely turning off SELinux. Change config SELINUX=disabled, and reboot.

   In different os, the configuration directory of SELinuxmay be different.

1. 检查您的 mysql 数据目录权限。
   数据目录默认配置在“/etc/my.conf”中。
   或者mysqladmin -uroot -p variables | grep datadir用来查找目录。
   它可能是/var/lib/mysql/默认的，并确保所有者用户和组是 mysql。chown -R mysql:mysql /usr/local/mysql/data

2. 如果通过步骤1还没有解决。可能是因为SELinux你的方式，尝试关闭它。

   • 测试 SELinux 是否正在运行。 selinuxenabled && echo enabled || echo disabled
   • 暂时关闭 SELinux。 echo 0 > /selinux/enforce
   • 完全关闭 SELinux。更改配置SELINUX=disabled，然后重新启动。

   在不同的操作系统中，其配置目录 SELinux可能不同。

回答by Flávio Rescia - Darede

Same issue, in my case I changed the default directory on my.cf and the selinux has blocking it.

同样的问题，就我而言，我更改了 my.cf 上的默认目录，而 selinux 阻止了它。

To test:

去测试：

setenforce 0

To fix (CentOS)

修复 (CentOS)

Add context and make it permanent

添加上下文并使其永久化

semanage fcontext -a -s system_u -t mysqld_db_t "/var/data/mysql(/.*)?" restorecon -Rv /var/data/mysql

source: https://naveensnayak.wordpress.com/2016/01/14/changing-mysql-data-directory-centos-7/

来源：https: //naveensnayak.wordpress.com/2016/01/14/changed-mysql-data-directory-centos-7/

回答by Mihai

In /etc/mysql/my.cnf, set datadir = /home/mysql_data, which is the location where the database files actually are on yourmachine

在/etc/mysql/my.cnf中，设置datadir = /home/mysql_data，这是数据库文件实际在你机器上的位置