我应该如何处理MySQL中的--secure-file-priv？

#1 楼

#2 楼

LOAD DATA LOCAL INFILE "text.txt" INTO TABLE mytable;

#3 楼

[mysqld]
secure-file-priv = ""

#4 楼

mysql> SELECT @@global.secure_file_priv;

#5 楼

#6 楼

LOAD DATA LOCAL INFILE "text.txt" INTO table mytable;

#7 楼

C:/ProgramData/MySQL/MySQL Server 8.0/Uploads

#8 楼

#9 楼

mysql> show variables like "secure_file_priv";  

mysql> show variables like "local_infile";

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+

mysql> set global local_infile = 1;

mysql> load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/file.txt" into table test;

#10 楼

#11 楼

mysql.server stop
mysql.server start --secure-file-priv=''

#12 楼

INSERT INTO <your_table> VALUEs( **CSV LINE **)

"city","city_ascii","lat","lng","country","iso2","iso3","id"
"Tokyo","Tokyo","35.6850","139.7514","Japan","JP","JPN","1392685764",
...

 const express = require('express'),
   cors = require('cors'),
   bodyParser = require('body-parser'),
   cookieParser = require('cookie-parser'),
   session = require('express-session'),
   app = express(),
   port = process.env.PORT || 5000,
   pj = require('./config/config.json'),
   path = require('path');

app.use(bodyParser.json());
app.use(cookieParser());
app.use(cors());


app.use(
   bodyParser.urlencoded({
      extended: false,
   })
);

var Import = require('./routes/ImportRoutes.js');

app.use('/import', Import);
if (process.env.NODE_ENV === 'production') {
   // set static folder
   app.use(express.static('client/build'));

   app.get('*', (req, res) => {
      res.sendFile(path.resolve(__dirname, 'client', 'build', 'index.html'));
   });
}

app.listen(port, function () {
   console.log('Server is running on port: ' + port);
});
 

const express = require('express'),
   cors = require('cors'),
   fs = require('fs-extra'),
   byline = require('byline'),
   db = require('../database/db'),
   importcsv = express.Router();

importcsv.use(cors());

importcsv.get('/csv', (req, res) => {

   function processFile() {
      return new Promise((resolve) => {
         let first = true;
         var sql, sqls;
         var stream = byline(
            fs.createReadStream('../PATH/TO/YOUR!!!csv', {
               encoding: 'utf8',
            })
         );

         stream
            .on('data', function (line, err) {
               if (line !== undefined) {
                  sql = 'INSERT INTO <your_table> VALUES (' + line.toString() + ');';
                  if (first) console.log(sql);
                  first = false;
                  db.sequelize.query(sql);
               }
            })
            .on('finish', () => {
               resolve(sqls);
            });
      });
   }

   async function startStream() {
      console.log('started stream');
      const sqls = await processFile();
      res.end();
      console.log('ALL DONE');
   }

   startStream();
});

module.exports = importcsv;

const Sequelize = require('sequelize');
const db = {};
const sequelize = new Sequelize(
   config.global.db,
   config.global.user,
   config.global.password,
   {
      host: config.global.host,
      dialect: 'mysql',
      logging: console.log,
      freezeTableName: true,

      pool: {
         max: 5,
         min: 0,
         acquire: 30000,
         idle: 10000,
      },
   }
);

db.sequelize = sequelize;
db.Sequelize = Sequelize;

module.exports = db;

#13 楼

$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" -- $other_args >/dev/null &
  wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$?

$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" --secure-file-priv="" $other_args >/dev/null &
  wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$?

#14 楼

/var/lib/mysql-files/* rw

[client]
loose-local-infile = 1

[mysqld]
secure-file-priv = ""

select id from blahs into outfile '/var/lib/mysql-files/blahs';

#15 楼

sudo /usr/local/mysql/support-files/mysql.server start --secure-file-priv='' --local-infile

mysql -u root -p

#16 楼

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  |
+------------------+-------+

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |       |
+------------------+-------+

#17 楼

#18 楼

 sudo /usr/local/mysql/support-files/mysql.server stop

sudo /usr/local/mysql/support-files/mysql.server start --secure-file-priv=YOUR_FILE_DIRECTORY

sudo /usr/local/mysql/support-files/mysql.server start --secure-file-priv=

#19 楼

#20 楼

/etc/mysql/my.cnf

secure-file-priv= <your data path directory like /home/user/data>

volumes:
  - ./persistent:/var/lib/mysql
  - ./conf/my.cnf:/etc/mysql/my.cnf

SHOW VARIABLES LIKE "secure_file_priv";

#21 楼

[client]
default-character-set=utf8

[mysqld]
character-set-server=utf8
secure-file-priv = ""
skip-external-locking

#22 楼

Loading local data is disabled - this must be enabled on both the client and server sides