mysqlimport: Error: 1045, Access denied

user_115635 0
Viewed 34777

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>
mysqlimport mysql-error-1045 mysql
5 Answers

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.

user_24305490
Related