在mysql 8.0(mac osx)中将secure\u file\u priv设置为本地文件夹

jfewjypa 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(509)

我正在学习mysql教程的这一部分，从.tsv文件加载数据。
我有这个数据文件：
pet文件

name    owner   species sex birth   death
Fluffy  Harold  cat f   1993-02-04  
Claws   Gwen    cat m   1994-03-17  
Buffy   Harold  dog f   1989-05-13  
Fang    Benny   dog m   1990-08-27  
Bowser  Diane   dog m   1979-08-31  1995-07-29
Chirpy  Gwen    bird    f   1998-09-11  
Whistler    Gwen    bird        1997-12-09  
Slim    Benny   snake   m   1996-04-29

存储在此位置：

/Users/martinfrigaard/MySQLData/pet.txt

我的第一次尝试使用了以下命令，

LOAD DATA INFILE '/pet.txt' INTO TABLE pet
LINES TERMINATED BY '\r';

它带来了以下错误：

ERROR 1148 (42000): The used command is not allowed with this MySQL version

在网上阅读和搜索之后，我发现了一篇关于如何改变现状的帖子 local_infile 设置。

SET GLOBAL local_infile = true;
-- Query OK, 0 rows affected (0.00 sec)

当我检查时：

SHOW GLOBAL VARIABLES LIKE 'local_infile';

这看起来很管用。

+---------------+-------+
  | Variable_name | Value |
  +---------------+-------+
  | local_infile  | ON    |
  +---------------+-------+
  1 row in set (0.00 sec)

但是当我运行 LOAD DATA 再次执行文件命令，

LOAD DATA INFILE '/pet.txt' INTO TABLE pet
LINES TERMINATED BY '\r';

它会导致以下错误。

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

设置全局安全文件priv import设置？

我已在三个位置调整了配置文件中的设置：
根据这篇文章

mmbp:~ martinfrigaard$ cat ~/.my.cnf
[mysqld_safe]
[mysqld]
secure_file_priv="/Users/martinfrigaard/MySQLData"

2）根据这篇文章

mmbp:~ martinfrigaard$ cat .my.cnf
[mysqld_safe]
[mysqld]
secure_file_priv="/Users/martinfrigaard/MySQLData"

3）根据这篇文章

GNU nano 2.9.8 /etc/my.cnf                                                                      

[mysqld]
[mysql]
secure-file-priv = "/Users/martinfrigaard/MySQLData"

但是当我重新启动mysql并检查 secure-file-priv 设置，

mysql> SHOW VARIABLES LIKE 'secure_file_priv';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  |
+------------------+-------+
1 row in set (0.00 sec)

我看这个还是空的。我还缺什么呢？

mysql

来源：https://stackoverflow.com/questions/51996401/setting-secure-file-priv-to-local-folder-in-mysql-8-0-mac-osx

1条答案

按热度按时间

q35jwt9p1#

解决方案：
直接编辑plist文件：sudo nano/library/launchdaemons/com.oracle.oss.mysql.mysqld.plist
添加行： <string>--secure-file-priv=/</string> 到文件：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
        <key>Disabled</key>
        <false/>
        <key>ExitTimeOut</key>
        <integer>600</integer>
        <key>GroupName</key>
        <string>_mysql</string>
        <key>KeepAlive</key>
        <true/>
        <key>Label</key>
        <string>com.oracle.oss.mysql.mysqld</string>
        <key>LaunchOnlyOnce</key>
        <false/>
        <key>ProcessType</key>
        <string>Interactive</string>
        <key>Program</key>
        <string>/usr/local/mysql/bin/mysqld</string>
        <key>ProgramArguments</key>
        <array>
                <string>/usr/local/mysql/bin/mysqld</string>
                <string>--user=_mysql</string>
                <string>--basedir=/usr/local/mysql</string>
                <string>--datadir=/usr/local/mysql/data</string>
                <string>--plugin-dir=/usr/local/mysql/lib/plugin</string>
                <string>--log-error=/usr/local/mysql/data/mysqld.local.err</string>
                <string>--pid-file=/usr/local/mysql/data/mysqld.local.pid</string>
                <string>--keyring-file-data=/usr/local/mysql/keyring/keyring</string>
                <string>--early-plugin-load=keyring_file=keyring_file.so</string>
                <string>--default_authentication_plugin=mysql_native_password</string>
                <string>--secure-file-priv=/</string>
        </array>
        <key>RunAtLoad</key>
        <true/>
        <key>SessionCreate</key>
        <true/>
        <key>UserName</key>
        <string>_mysql</string>
        <key>WorkingDirectory</key>
        <string>/usr/local/mysql</string>
</dict>
</plist>

使用sierra 10.13.6为mysql 8.0工作

赞(0）回复(0）举报 2021-06-20

我来回答

在mysql 8.0(mac osx)中将secure\u file\u priv设置为本地文件夹

设置全局安全文件priv import设置？

1条答案

相关问题

热门标签

最新问答