mysqlimport: Error: 1045, Access denied

Question

Does anyone know why I get this error when running mysqlimport?

mysqlimport -u someone -pwhatever --columns=a,b,c,d,e bar /var/tmp/baz.sql
mysqlimport: Error: 1045, Access denied for user 'someone'@'%' (using password: YES), when using table: baz

However...

mysql -u someone -pwhatever
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 199
Server version: 5.1.41-3ubuntu12.10 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show grants;
+------------------------------------------------------------------------------------------------------------+
| Grants for someone@%                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'someone'@'%' IDENTIFIED BY PASSWORD '*BLAHBLAHBLAH' |
| GRANT ALL PRIVILEGES ON `bar`.* TO 'someone'@'%'                                          |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

score 32 · Answer 1 · answered Feb 26 '12 at 13:03

32

You can avoid the need for the extra privileges by using the --local parameter to mysqlimport:

--local, -L

           Read input files locally from the client host.

answered Feb 26 '12 at 13:03

Omry Yadan

31,280
18
64
87

2

Worked for me, and IMHO a much better approach than granting an important global permission to one user – happyskeptic Nov 08 '16 at 07:55
This still works on MySQL 8.0 e.g. `mysql -L -f database < dump.sql` and you don't need to use `mysqlimport` command either. – Jesse Nickles Sep 16 '21 at 22:30

Mike Conigliaro · Accepted Answer · 2016-07-27T15:27:25.670

26

OK, it turns out that the FILE privilege is a "global" privilege, which apparently means you can't selectively enable it on certain databases, tables. etc. That's why my previous grant statement on bar.* had no effect:

GRANT ALL PRIVILEGES ON `bar`.* TO 'someone'@'%'

You need to grant FILE privileges on *.*:

GRANT FILE ON *.* to 'someone'@'%';

Hope this helps someone.

edited Jul 27 '16 at 15:27

answered Jul 26 '11 at 22:45

Mike Conigliaro

1,144
1
13
28

If you get 'ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES', after setting different parameters, revert back to what this answer shows: "GRANT FILE ON *.* to user@localhost;" - "the FILE privileges are global and cannot be applied to a single database" – Meetai.com Jun 26 '14 at 02:50

score 13 · Answer 3 · answered Oct 15 '13 at 19:06

13

Some would instead opt for this command, skipping the extra FILE grant.

mysql -u username -p <yourdbname> < yourfile.sql

answered Oct 15 '13 at 19:06

H.Rabiee

4,747
3
23
35

2

how do i get this to work on importing a csv file? Do i just rewrite the csv file in a .sql format and then imprt it? or can I use something like http://stackoverflow.com/a/18469353/2392358 `mysqlimport` – HattrickNZ Nov 02 '15 at 22:35
@HattrickNZ well that command is only if you have statements in your .sql file. If you have .csv I guess you would have to do something different, like posted in your link. – H.Rabiee Nov 03 '15 at 07:03

score 6 · Answer 4 · answered Jul 26 '11 at 22:13

6

mysqlimport is a command-line interface to the LOAD DATA INFILE statement, for which you need the 'FILE' privilege (server level).

From LOAD DATA INFILE syntax:

Also, to use LOAD DATA INFILE on server files, you must have the FILE privilege.

answered Jul 26 '11 at 22:13

Bilal

922
6
7

So "GRANT ALL PRIVILEGES" doesn't include the FILE privilege? – Mike Conigliaro Jul 26 '11 at 22:32
3

You're granting ALL PRIVILEGES at the database level, whereas the FILE privilege is at the server level. – Bilal Jul 26 '11 at 22:35
Ah yes, I just figured that out. Thanks for the help! – Mike Conigliaro Jul 26 '11 at 22:47

score 0 · Answer 5 · answered Sep 10 '22 at 01:08

TLDR: Use the `--set-gtid-purged=OFF` Arg in MySQLDump

When doing mysqldump -u username -p to create the file you're going to import elsewhere, throw in the argument of --set-gtid-purged=OFF.

GTIDs are needed for replication, and probably don't apply to what you're doing if you just want to copy/paste DB 1 to DB 2.

General Debugging Help

My debugging process here was a little bit different than what others have done. I suggest this to debug: Change your .sql to the simplest possible thing, maybe just one single CREATE TABLE statement, and see if it runs.

If it runs, then these are things that you want to remove from your SQL import file:

Any line setting @@GLOBAL.GTID.
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

As you can see, it's a lot of GTID stuff, which is transaction ID info used for doing replication. So, these are important when doing server replication, but not when doing basically a copy-paste of one DB to another DB, and in that case we can drop them.

mysqlimport: Error: 1045, Access denied

5 Answers5

TLDR: Use the `--set-gtid-purged=OFF` Arg in MySQLDump

General Debugging Help

Linked