文章目录

  • （1）首先查看 secure-file-priv 的设置情况
  • （2）修改 secure-file-priv 的值
  • （3）设置MySQL
  • （4）再次查看状态
  • （5）数据的导入/导出
  • （6）注意



今天在操作数据库的时候遇到了数据库导入导出的问题，具体报错内容如下： 

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

我的解决办法：

（1）首先查看 secure-file-priv 的设置情况

在终端进入 MySQL后输入命令show variables like 'secure_file_priv';

secure-file-priv 有三种情况：NULL / ‘path’(设置好的路径) /空。

NULL 表示数据的导入和导出被限制
‘path’ 表示数据的导入和导出都要在这个路径下进行
空 表示数据的导入和导出不受限制

MySQL 中默认情况下 secure-file-priv 的设置为 NULL，如下：

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv  |  NULL |
+------------------+-------+
（2）修改 secure-file-priv 的值

正常情况下应该在 my.cnf 文件中进行配置，但是在电脑里找了好多地方都没有my.cnf文件，因此结合网上的一些说法，我在 /etc/ 下面自己新建了 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   = "机器的mysql密码"
  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' 
  secure_file_priv=''
# 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

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

以上 cnf 文件中的内容是结合了网上资料自己整理的，注意两点：
1）password 处要写自己Mac的mysql密码。
2）secure_file_priv 可根据自己的需求自行设定，此处我选择的是’ '🈳️的形式，即导入和导出不受限制。

tips：在MacOS下，cnf文件用 VSCode 写会方便一些，可以直接复制路径用于之后的操作。涉及到权限问题，可以通过输入电脑密码解决。

（3）设置MySQL

在系统偏好设置中找到 MySQL，在 Configuration 里面，找到 Configuration File，并设置路径为'/private/etc/my.cnf'，也可以点击输入框后面的 select 按钮，进行图形化界面的选取。Configuration File 前面要打勾。完成后选择右下角的 Apply。接下来停止并重新开启MySQL，可以通过在终端输入命令的方式，具体命令见下文；也可以在系统偏好设置中的 MySQL下面的Instances里面点击Stop MySQL Server，再重新Start MySQL Server，两种方式都可以。具体命令如下：

  • 启动：sudo /usr/local/mysql/support-files/mysql.server start
  • 停止：sudo /usr/local/mysql/support-files/mysql.server stop
  • 重启：sudo /usr/local/mysql/support-files/mysql.server restart

在重启MySQL后别忘记在终端也要退出MySQL再重新进入（mysql -uroot -p）

（4）再次查看状态

此时从终端再次进入 MySQL 后查看 secure-file-priv（查询语句上文有提及），此时结果应该为空，如下：

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv  |       |
+------------------+-------+

此时表示修改成功

（5）数据的导入/导出

此时就可以可以进行数据的导入和导出了。虽然secure_file_priv设置为空，理论上可以任意导入导出到任何文件目录下，但经过测试仍然无法把数据导出到桌面或其他路径，具体报错内容如下：

Can't get stat of 'xxxxxxx' (OS errno 13 - Permission denied)
File 'xxxxxxx' not found (OS errno 13 - Permission denied)

结合网上一些分析，这可能与文件的读写权限有关，此处不再做深入讨论（其实也搞不懂hhh🤤），但是在 ‘/tmp/’ 路径下是可以对文件进行导入导出的。胖友们可以在这个目录下导入or导出csv格式的文件。

（6）注意
  • 在导入数据的时候，要注明是 local 文件，即load data local infile xxxxxxxx，在 data 和 infile 中间加上 local。
  • 此时就可以进行数据的导入和导出了，两种操作都是在 ‘/tmp/’ 路径下完成的。

导入导出语句：

导入：
load data local infile "文件名（路径）"
into table 表名
fields terminated by "分隔符"
lines terminated by "\n"
导出：
select ... from 表名
into outfile "文件名（路径）"
fields terminated by "分隔符"
lines terminated by "分隔符";

最后祝大家导入导出开心！