Link to home
Start Free TrialLog in
Avatar of Adam Bell
Adam BellFlag 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
Avatar of David Favor
David Favor
Flag of United States of America image

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.
Avatar of 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..
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.
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
Avatar of Adam Bell
Adam Bell
Flag of Hong Kong image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial