Avatar of Adam Bell
Adam Bell
Flag for Hong Kong asked on

Permissions error importing to MySQL 5.6.x

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
AWSMySQL Server

Avatar of undefined
Last Comment
Adam Bell

8/22/2022 - Mon
David Favor

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.
Adam Bell

ASKER
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 Favor

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Adam Bell

ASKER
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.
ASKER CERTIFIED SOLUTION
Adam Bell

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question