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>

Complain

Answer 1

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.

Complain

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" – Endorsed

Answer 2

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.

Butlery

Worked for me, and IMHO a much better approach than granting an important global permission to one user – Emirate

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. – Scuff

Answer 3

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

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

Flagstaff

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 https://mcmap.net/q/274352/-importing-a-csv-into-mysql-via-command-line mysqlimport – Arissa

@Arissa 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. – Flagstaff

Answer 4

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.

Drew

So "GRANT ALL PRIVILEGES" doesn't include the FILE privilege? – Complain

You're granting ALL PRIVILEGES at the database level, whereas the FILE privilege is at the server level. – Drew

Ah yes, I just figured that out. Thanks for the help! – Complain

Answer 5

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.

Assemblage