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
Adam BellAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David FavorLinux/LXD/WordPress/Hosting SavantCommented:
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 BellAuthor 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 FavorLinux/LXD/WordPress/Hosting SavantCommented:
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.
Adam BellAuthor 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.
Adam BellAuthor Commented:
In case anyone else needs a quick fix to the data access part of this problem, after much trial and error one solution was to edit the SQL file and remark out the code created by SQL Administrator which tries to recreate PERFORMANCE SCHEMA which exists (although the SQL script had IF NOT EXIST) and then disable MYSQL schema in the catalogs to import in MySQL admin.  I didn't try but I assume the same would work in MySQL Workbench etc.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
AWS

From novice to tech pro — start learning today.