MySQLWorkbench在用Load Data Infile导入utf-8文件的时候报错‘’Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement‘’。具体情况是：使用Mac，因为一开始找不到my.cnf文件，在/etc下面自己建了一个my.cnf文件，然后加上了secure-file-priv = ""，但是依然报这个错。求解决方案。

自建my.cnf代码如下

[mysqld]
secure-file-priv = ""
disable-log-bin = 1
skip-name-resolve = 1
performance-schema = 0
local-infile = 0
mysqlx = 0
bind-address = [IPs removed]
open_files_limit = 200000
max_allowed_packet = 256M
sql_mode = "STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

innodb_dedicated_server = 1
innodb_buffer_pool_instances = 48
innodb_log_buffer_size = 64M
innodb_read_io_threads = 12
innodb_write_io_threads = 12
innodb_stats_on_metadata = 0
innodb_file_per_table = 1

max_connections = 500
thread_cache_size = 128
table_definition_cache = 65536
table_open_cache = 65536

wait_timeout = 10
connect_timeout = 5
interactive_timeout = 30

tmp_table_size = 128M
max_heap_table_size = 128M

read_buffer_size = 256K
join_buffer_size = 512K
sort_buffer_size = 512K
read_rnd_buffer_size = 512K

slow-query-log = 1
long_query_time = 2
slow_query_log_file = /var/log/mysql_slow_query.log
log-error = /var/log/mysql/db.[removed].com.err

1. 第一种解决方案

我猜你是想从客户端主机 Mac 加载文件到服务器，如果不是看下面的 “3.” 。客户端导入与服务器参数 secure_file_priv 无关，只与 local_infile 参数有关，该参数需要再客户端和服务器都配置。我看到你的 my.cnf 里的 local_infile=0 ，这是不对的，使用客户端导入必须使用 LOAD DATA LOCAL INFILE SQL 语法，且 MySQL 服务器端和客户端都需要开启 local_infile ，这是个动态全局系统变量，无需重启。这个参数服务器端好配置。

试试开启这个参数，以下是临时开启，如果想要永久开启可以加到配置文件里，也可以使用 SET PERSIST 。

SET GLOBAL @@local_infile=1;

MySQL Workbench 客户端可以使用 OPT_LOCAL_INFILE=1 ，如下图所示：

然后 LOAD DATA LOCAL INFILE ，例如我在 Win10 平台执行：

#Windows 平台二选一
load data local infile 'I:\\Develop\\MySQL\\Workspace\\DataPumpDir\\t1.csv' into table db5.t1 fields terminated by ',';
#这个语句适合 Linux、Mac、Windows 平台
load data local infile 'I:/Develop/MySQL/Workspace/DataPumpDir/t1.csv' into table db5.t1 fields terminated by ',';

2. 第二种解决方案

也可以使用 MySQL Workbench 安装目录下绑定的 mysql 客户端来做，

• 如果在配置文件 my.cnf 中配置了local_infile=1,
  

  

• 如果没在配置文件中配置，也可以使用命令行选项 --local-infile ：
  

  

3. 还是没解决怎么办

如果以上无法解决你的问题，需要你回答：

• 你的 MySQL Server 安装在哪？和 Client （对于你来说也就是 MySQL Workbench） 一样都安装在 Mac 上吗？
• secure_file_priv 真的在 MySQL 配置文件中正确配置并重启了吗？

如果我的回答解决了您的问题，还望点击采纳。

• 这篇博客: mac os上mysql secure-file-priv导入文件权限解决方案中的 第二步：进入/etc目录，创建my.cnf文件 部分也许能够解决你的问题, 你可以仔细阅读以下内容或跳转源博客中阅读:
  

• 首先打开Finder，按住command+Shift+G，输入：/etc，即可前往etc文件夹，查看my.cnf文件是否存在。
  
  博主已经建好了my.cnf文件，故下图有该文件：
  
  如果my.cnf文件不存在（博主就是这种情况），则需新建该文件，步骤如下：
  ①输入：cd /etc
  ②输入：sudo vim my.cnf
  ③输入你电脑的登录密码
  
  ④英文键盘下输入a，即可开始编辑。接着复制如下代码到该文件（my.cnf）：

  # Example MySQL config file for medium systems.  
    #  
    # This is for a system with little memory (32M - 64M) where MySQL plays  
    # an important part, or systems up to 128M where MySQL is used together with  
    # other programs (such as a web server)  
    #  
    # MySQL programs look for option files in a set of  
    # locations which depend on the deployment platform.  
    # You can copy this option file to one of those  
    # locations. For information about these locations, see:  
    # http://dev.mysql.com/doc/mysql/en/option-files.html  
    #  
    # In this file, you can use all long options that a program supports.  
    # If you want to know which options a program supports, run the program  
    # with the "--help" option.  
    # The following options will be passed to all MySQL clients  
    [client]
    default-character-set=utf8
    #password   = your_password  
    port        = 3306  
    socket      = /tmp/mysql.sock   
    # Here follows entries for some specific programs  
    # The MySQL server  
    [mysqld]
    character-set-server=utf8
    init_connect='SET NAMES utf8
    port        = 3306  
    socket      = /tmp/mysql.sock  
    skip-external-locking  
    key_buffer_size = 16M  
    max_allowed_packet = 1M  
    table_open_cache = 64  
    sort_buffer_size = 512K  
    net_buffer_length = 8K  
    read_buffer_size = 256K  
    read_rnd_buffer_size = 512K  
    myisam_sort_buffer_size = 8M  
    character-set-server=utf8  
    init_connect='SET NAMES utf8' 
  # Don't listen on a TCP/IP port at all. This can be a security enhancement,  
  # if all processes that need to connect to mysqld run on the same host.  
  # All interaction with mysqld must be made via Unix sockets or named pipes.  
  # Note that using this option without enabling named pipes on Windows  
  # (via the "enable-named-pipe" option) will render mysqld useless!  
  #   
  #skip-networking  
  
    # Replication Master Server (default)  
    # binary logging is required for replication  
    log-bin=mysql-bin  
  
      # binary logging format - mixed recommended  
      binlog_format=mixed  
  
        # required unique id between 1 and 2^32 - 1  
        # defaults to 1 if master-host is not set  
        # but will not function as a master if omitted  
        server-id   = 1  
  
      # Replication Slave (comment out master section to use this)  
      #  
      # To configure this host as a replication slave, you can choose between  
      # two methods :  
      #  
      # 1) Use the CHANGE MASTER TO command (fully described in our manual) -  
      #    the syntax is:  
      #  
      #    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,  
      #    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;  
      #  
      #    where you replace <host>, <user>, <password> by quoted strings and  
      #    <port> by the master's port number (3306 by default).  
      #  
      #    Example:  
      #  
      #    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,  
      #    MASTER_USER='joe', MASTER_PASSWORD='secret';  
      #  
      # OR  
      #  
      # 2) Set the variables below. However, in case you choose this method, then  
      #    start replication for the first time (even unsuccessfully, for example  
      #    if you mistyped the password in master-password and the slave fails to  
      #    connect), the slave will create a master.info file, and any later  
      #    change in this file to the variables' values below will be ignored and  
      #    overridden by the content of the master.info file, unless you shutdown  
      #    the slave server, delete master.info and restart the slaver server.  
      #    For that reason, you may want to leave the lines below untouched  
      #    (commented) and instead use CHANGE MASTER TO (see above)  
      #  
      # required unique id between 2 and 2^32 - 1  
      # (and different from the master)  
      # defaults to 2 if master-host is set  
      # but will not function as a slave if omitted  
      #server-id       = 2  
      #  
      # The replication master for this slave - required  
      #master-host     =   <hostname>  
      #  
      # The username the slave will use for authentication when connecting  
      # to the master - required  
      #master-user     =   <username>  
      #  
      # The password the slave will authenticate with when connecting to  
      # the master - required  
      #master-password =   <password>  
      #  
      # The port the master is listening on.  
      # optional - defaults to 3306  
      #master-port     =  <port>  
      #  
      # binary logging - not required for slaves, but recommended  
      #log-bin=mysql-bin  
  
        # Uncomment the following if you are using InnoDB tables  
        #innodb_data_home_dir = /usr/local/mysql/data  
        #innodb_data_file_path = ibdata1:10M:autoextend  
        #innodb_log_group_home_dir = /usr/local/mysql/data  
        # You can set .._buffer_pool_size up to 50 - 80 %  
        # of RAM but beware of setting memory usage too high  
        #innodb_buffer_pool_size = 16M  
        #innodb_additional_mem_pool_size = 2M  
        # Set .._log_file_size to 25 % of buffer pool size  
        #innodb_log_file_size = 5M  
        #innodb_log_buffer_size = 8M  
        #innodb_flush_log_at_trx_commit = 1  
        #innodb_lock_wait_timeout = 50  
  
          [mysqldump]  
          quick  
          max_allowed_packet = 16M  
  
            [mysql]  
            no-auto-rehash  
            # Remove the next comment character if you are not familiar with SQL  
            #safe-updates  
            default-character-set=utf8   
  
          [myisamchk]  
          key_buffer_size = 20M  
          sort_buffer_size = 20M  
          read_buffer = 2M  
          write_buffer = 2M  
  
            [mysqlhotcopy]  
            interactive-timeout
  --------------------- 
  作者：jyongchong 
  来源：CSDN 
  原文：https://blog.csdn.net/jyongchong/article/details/77862819 
  版权声明：本文为博主原创文章，转载请附上博文链接！
  

  【重要事情说三遍！！！】以上代码引用自他人，但仅凭上述代码并不能解决问题，故博主查阅资料后在上面代码的末尾添加如下代码：
  【重要事情说三遍！！！】以上代码引用自他人，但仅凭上述代码并不能解决问题，故博主查阅资料后在上面代码的末尾添加如下代码：
  【重要事情说三遍！！！】以上代码引用自他人，但仅凭上述代码并不能解决问题，故博主查阅资料后在上面代码的末尾添加如下代码：

  secure_file_priv=''
  [mysqld]
  local-infile=1
  [mysql]
  local-infile=1
  

• 以下回答由chatgpt基于相关博客总结生成:

  使用MySQLWorkbench导入utf-8文件时遇到Error Code：1290错误，可以按照以下步骤解决：

  1. 打开Finder，按住command+Shift+G，输入：/etc，前往etc文件夹，查看my.cnf文件是否存在。
  2. 如果my.cnf文件不存在，则输入cd /etc在终端进入etc目录，输入sudo vim my.cnf创建并编辑my.cnf文件，添加以下代码：

  [client]
  default-character-set=utf8
  port        = 3306  
  socket      = /tmp/mysql.sock    
  [mysqld]
  character-set-server=utf8
  init_connect='SET NAMES utf8'
  port        = 3306  
  socket      = /tmp/mysql.sock  
  skip-external-locking  
  key_buffer_size = 16M  
  max_allowed_packet = 1M  
  table_open_cache = 64  
  sort_buffer_size = 512K  
  net_buffer_length = 8K  
  read_buffer_size = 256K  
  read_rnd_buffer_size = 512K  
  myisam_sort_buffer_size = 8M  
  character-set-server=utf8  
  init_connect='SET NAMES utf8' 
  secure_file_priv='' #添加该行代码
  [mysqld]
  local-infile=1 #添加该行代码
  [mysql]
  local-infile=1 #添加该行代码
  

  其中，secure_file_priv=''用于解除文件读取路径的限制，local-infile=1用于开启本地文件读取功能。

  1. 保存并退出my.cnf文件，重启MySQL服务，在MySQLWorkbench中导入utf-8文件时即可避免Error Code：1290错误。

my.cnf 修改后，需要重启服务。确保正确重启了。或者用navacat替代

MySQL导入数据时碰到Error Code: 1290 以及解决

可以借鉴下
https://blog.csdn.net/claider/article/details/126847887

secure_file_priv 为 NULL 时，表示限制mysqld不允许导入或导出。
secure_file_priv 为 /tmp 时，表示限制mysqld只能在/tmp目录中执行导入导出，其他目录不能执行。
secure_file_priv 没有值时，表示不限制mysqld在任意目录的导入导出。

你先看下 secure_file_priv 的值，默认为NULL，表示限制不能导入导出。

mysql> show global variables like '%secure_file_priv%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| secure_file_priv | NULL |
+------------------+-------+
1 row in set (0.00 sec)
然后你修改下secure_file_priv=""
你的配置项加了后
改完之后重启下mysql
然后你在mysql终端查看下

show global variables like '%secure_file_priv%';
看是否生效
###然后导出你的数据
SELECT * FROM 表名 INTO OUTFILE '/Users/gaoke/test.csv' FIELDS TERMINATED BY ',';

引用chatGPT作答，根据您的描述，看起来MySQL服务器仍然无法找到您要导入的文件，尽管您已经在my.cnf中设置了secure-file-priv = ""。因此，以下是一些可能有助于解决这个问题的建议：

1.确保my.cnf文件已正确放置
请确保您的my.cnf文件已正确放置在MySQL服务器的默认位置（/etc/my.cnf或/etc/mysql/my.cnf）。在这种情况下，MySQL服务器应该可以找到并读取这个文件。您可以使用以下命令检查MySQL服务器是否已读取您的my.cnf文件：

mysql> SHOW VARIABLES LIKE 'secure_file_priv';

如果输出的值与您在my.cnf中设置的值相同，那么MySQL服务器应该已经读取了您的my.cnf文件。

2.指定文件的完整路径
尝试在LOAD DATA INFILE语句中指定您要导入的文件的完整路径。例如：

LOAD DATA INFILE '/path/to/your/file.csv' INTO TABLE your_table;

请注意，这里的路径必须是MySQL服务器可以访问的路径。如果您不确定MySQL服务器是否可以访问这个路径，请尝试在MySQL服务器上使用相同的路径打开文件。

3.改变secure-file-priv的值
如果MySQL服务器仍然无法找到您要导入的文件，请尝试将secure-file-priv的值设置为MySQL服务器可以访问的目录。例如：

secure-file-priv = /var/lib/mysql-files/

请确保您设置的路径存在并且MySQL服务器可以访问该路径。然后尝试重新启动MySQL服务器，使更改生效。

希望这些建议能够帮助您解决问题。