We help IT Professionals succeed at work.

Permissions error importing to MySQL 5.6.x

Adam Bell
Adam Bell asked
on
117 Views
Last Modified: 2018-12-31
Hello

I have setup MySQL 4.6.x on AWS, and created a new DB schema to receive data from a SQL file created using MySQL DUMP.  Using MySQL Workbench 8.0 CE I can establish a connection, but when I import the data (it seems to start with password-yes then switch to password-no after first failure exit code 1) I get the following error:

ERROR 1044 (42000) at line 21: Access denied for user 'admin'@'%' to database 'mysql'

If it is relevant the SQL file was dumped with a different admin account/password on a different SQL box.

I've looked at a lot of articles and believe this is related to logins/perms but I am mostly good with data rather than sysadmin so any help getting started greatly appreciated...

The full error text is..

box1.ap-southeast-1.rds.amazonaws.com
14:26:34 Restoring C:\Program Files\MySQL\MySQL Server 8.0\bin\yfsql.sql
Running: mysql.exe --defaults-file="c:\users\user\appdata\local\temp\tmpgq7exl.cnf"  --protocol=tcp --host=box1.ap-southeast-1.rds.amazonaws.com --user=admin --port=3306 --default-character-set=utf8 --comments  < "C:\\Program Files\\MySQL\\MySQL Server 8.0\\bin\\sql.sql"
ERROR 1044 (42000) at line 21: Access denied for user 'admin'@'%' to database 'mysql'

Operation failed with exitcode 1
14:26:55 Restoring C:\Program Files\MySQL\MySQL Server 8.0\bin\yfsql.sql
Running: mysql.exe --defaults-file="c:\users\user\appdata\local\temp\tmpjzz_nr.cnf"  --protocol=tcp --host=box1.ap-southeast-1.rds.amazonaws.com --user=admin --port=3306 --default-character-set=utf8 --comments  < "C:\\Program Files\\MySQL\\MySQL Server 8.0\\bin\\sql.sql"
ERROR 1045 (28000): Access denied for user 'admin'@'ppp-101-11-11-11.revip6.asianet.co.th' (using password: NO)

Operation failed with exitcode 1
Comment
Watch Question

David FavorFractional CTO
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
This suggests password provided is indeed incorrect.

Shell into your AWS instance (ssh) + ensure this command works...

mysql -uadmin -p$pass -e "SHOW DATABASES"

Open in new window


When you have the correct password, the above command should work.

Then verify the above $pass is actually the one set in MySQL Workbench.

Tip: Create a 16 or 32 byte alphanumeric password (no oddball characters) which can be easily clicked to highly for cut + paste. Many times password problems occur during cut + paste, due to special characters which stop a click to cut operation part way through a string, rather than highlighting the entire string.

Author

Commented:
Hello David

Thanks for the reply.

After digging around it seems AWS doesn't have a user named root per se, however the admin user I created at startup seems to be considered the master account - I say this because I changed the master password in RDS instance settings and it was accepted as the password for the admin user I create subsequently (previously it had a very different password).

So using Workbench 8 I executed SHOW DATABASES and it gave the correct listing.

I did notice that DBA / Maintenance admin / Process admin permissions are not granted on my admin account and perhaps this is where importing the SQL file comes unstuck...

If the import process does not require a MySQL account with the same credentials as that used to DUMP the SQL file originally ?

Then is the lack of DBA permissions holding the import up ? I know I have the correct password, but the users is not ROOT or with full perms..
David FavorFractional CTO
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
There is always a root database user. What this root user is called (root, admin, foo) is different across Distros + Infrastructures (like AWS).

From what you're saying, I'd reset your root user + pass, to avoid endless debugging.

And...

For example, if you install Debian/Ubuntu you'll have a file called /etc/mysql/debian.cnf which contains your root user/pass.

Or with RedHat/CentOS/Fedora you'll have a file called ~root/.my.cnf (sometimes)...

You can also, just stop your database instance + restart with skip grants + reset your root password, so you know for sure the value.

https://help.ubuntu.com/community/MysqlPasswordReset provides good instructions for this process.

Start by resetting your password + verifying that the "SHOW DATABASES" command above works.

Best to always start from a 100% known starting point.

Author

Commented:
Thanks. I think the last message covers the MySQL aspect I will get onto AWS support re root user management and setting dba perms on my adnin user, reason being the SQL script recreates the required schema and data etc on successful import.
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION