How to deal with the problems in importing ordinary data files into mysql

The following content mainly brings you how to deal with the problems caused by the introduction of ordinary data files into mysql. The knowledge mentioned here is slightly different from books, which are summed up by professional and technical personnel in the process of contact with users, and have a certain value of experience sharing. I hope to bring help to the majority of readers.

I. description of the problem

The data file a.txt is imported into the mysql table.

1. The first question

# mysqlimport-uabc-pendant 123'-h db1-P 3306 DB a.txtERROR 1045 (28000): Access denied for user 'root'@'db1' (using password: YES), when using table:

After excluding the incorrect password and format, check the mysql.user table to confirm that you have File permissions.

It is true that there is no File permission after confirmation. The File permission to update the mysql.user table is'Y. refresh permissions.

> flush privileges

2. Second question

Re-execute the import command

# mysqlimport-uabc-pendant 123'-h db1-P 3306 DB a.txtmysqlimport: Error: 1290, The MySQL server is running with the-- secure-file-priv option so it cannot execute this statement, when using table:

The same error is reported using load data infile.

View-- secure-file-priv parameter

> show global variables like 'secure_file_priv';+-+-+ | Variable_name | Value | +-+-+ | secure_file_priv | / dev/null | +-+-+

This parameter is not a dynamic parameter. A configuration file needs to be added for the restart service to take effect.

1) No restrictions on import and export

# cat / etc/my.cnf [mysqld] secure_file_priv

2) restricted to a specific directory

# cat / etc/my.cnf [mysqld] secure_file_priv = / tmp

-- only data files in the / tmp directory are allowed to be imported, and files in other directories do not have permission to import.

Because we are using Tencent Cloud mysql database, this parameter cannot be modified, but we must have the import and export feature, so we can only build our own instances and make our own restrictions.

3. Third question

# mysqlimport-r-- host= "db1"-- port= "3306"-- user= "abc"-- password= "123"-- fields-terminated-by="\ t "--lines-terminated-by="\ n "--columns=" a DB "/ tmp/a.txt" mysqlimport: Error: 13, Can't get stat of'/ data/codebase/a.txt' (Errcode: 2 "No such file or directory"), when using table: a

The default is to find the files in the / tmp directory of the CVM where the mysql library resides, not the machine that executes the command.

Add the-- local parameter to indicate that the specified import file is the machine that executes the command.

For the above problems about the import of ordinary data files into mysql, if you need to know more, you can continue to pay attention to the innovation of our industry. If you need professional answers, you can contact the pre-sales and after-sales on the official website. I hope this article can bring you some knowledge updates.