我试图将MySQL表中的数据输出到文件但获取权限错误:
$pwd
/home/dotancohen
$mkdir in
$chmod 777 in/
$mysql -ugs -p
mysql> USE someDatabase;
mysql> SELECT * FROM data INTO OUTFILE '/home/dotancohen/in/data.csv';
ERROR 1045 (28000): Access denied for user 'gs'@'localhost' (using password: YES)
mysql>
如果有问题的目录是chmodded到777,那么为什么MySQL用户不能写文件?有趣的是,我无法写入/ tmp /.
编辑:
看起来DB用户具有正确的MySQL权限:
mysql> show grants;
+----------------------------------------------------------------------------------+
| Grants for gs@localhost |
+----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'gs'@'localhost' IDENTIFIED BY PASSWORD 'somePassword' |
| GRANT ALL PRIVILEGES ON `gs\_%`.* TO 'gs'@'localhost' |
+----------------------------------------------------------------------------------+
2 rows in set (0.01 sec)
解决方法:
Any file created by INTO OUTFILE or INTO DUMPFILE is writable by all
users on the server host. The reason for this is that the MySQL server
cannot create a file that is owned by anyone other than the user under
whose account it is running. (You should never run mysqld as root for
this and other reasons.) The file thus must be world-writable so that
you can manipulate its contents.
您应该将SELECT INTO OUTFILE输出到/ var / lib / mysql,如下所示
SELECT * FROM data INTO OUTFILE 'data.csv';
当然,您需要确保您对gs @ localhost拥有FILE权限.
有两种方法可以获得此权限
方法#1
GRANT FILE ON *.* TO 'gs'@'localhost';
方法#2
UPDATE mysql.user SET File_priv = 'Y' WHERE user='gs' AND host='localhost';
FLUSH PRIVILEGES;
更新2012-05-01 07:09 EDT
要为自己提供FILE权限,请执行以下操作:
> STEP 01)service mysql restart –skip-networking –skip-grant-tables
>步骤02)mysql
>步骤03)UPDATE mysql.user SET File_priv =’Y’WHERE user =’gs’AND host =’localhost’;
>步骤04)退出
> STEP 05)服务mysql重启
标签:linux,mysql,permissions
来源: https://codeday.me/bug/20190805/1589204.html