Unable to import SQL file to MySQL on AWS-RDS

Hello,

I am trying to import a SQL file dumped using MySQL administrator from v5.5.12, first using MysQL8 on Windows 10 then using 5.6.41 on AWS/RDS, the error using MySQL Workbench 8 is:

15:25:12 Restoring C:\path\sql\sqlfile.sql
Running: mysql.exe --defaults-file="c:\users\users\appdata\local\temp\tmpyzknop.cnf"  --protocol=tcp --host=awsendpoint.rds.amazonaws.com --user=myuser --port=3306 --default-character-set=utf8 --comments --database=mysql  < "C:\\Users\\users\\OneDrive\\Documents\\sqlfile.sql"
ERROR 1044 (42000) at line 21: Access denied for user 'myuser'@'%' to database 'mysql'

Operation failed with exitcode 1
15:25:17 Restoring C:\Users\abell\OneDrive\Documents\\sqlfile.sql
Running: mysql.exe --defaults-file="c:\users\user\appdata\local\temp\tmpvhirjz.cnf"  --protocol=tcp --host=awsendpoint.rds.amazonaws.com --user=myuser --port=3306 --default-character-set=utf8 --comments --database=mysql  < "C:\\Users\\user\\OneDrive\\Documents\\sqlfile.sql"
ERROR 1045 (28000): Access denied for user 'myuser'@'123.456.78.9' (using password: NO)

Operation failed with exitcode 1

I've established that the username and password with which I logged on to AWS.RDS via Workbench is the master user for this instance, BUT I also notice it doesn't have DBS and maintenance perms and I am not sure if these are required or how to set them from AWS dashboard ? or how to create a new user for the existing RDS instance MYSQL as permission is denied using the master account I created at setup.

I don't see anything obvious but am conscious of syntax differences between Windows and Linux eg paths, but don't think that's the issue as I'm getting consistent permission denial whichever way I go.

It seems  the fundamental problem is authentication, so if I cam using the 'correct' login credentials, either it is not the correct account for AWS ?

Lastly, I am assuming that the account used for import does not need to have the same username and password as the box the SQL file was dumped from ?

Any advice on this enormously appreciated, I am more used to data than sysadmin, have trawled through the various sites, docs, but not getting any progress....
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:
Problem is...

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

Open in new window


You must fix this first.

1) Shell into machine where database instance is running + test your user/pass login.

2) Then test same login from remote.

This will tell you location of your problem.

Keep in mind, once you have this working, be sure to wrap your MySQL connection in SSL or anyone can pull your login credentials off the wire, then hack or steal your data.
Adam BellAuthor Commented:
Hello David

I decided to stop using AWS for now as it adds an extra later of complexity...

I installed WAMP and 5.6.7 MySQL on a local box...

After fresh install can access local instance as root with no password...

Attempted a SQL import...get the following error...

00:22:40 Restoring C:\Program Files\MySQL\MySQL Server 8.0\bin\yfsql.sql
Running: mysql.exe --defaults-file="c:\users\user\appdata\local\temp\tmphmp2sl.cnf"  --protocol=tcp --host=127.0.0.1 --user=root --port=3306 --default-character-set=utf8 --comments  < "C:\\Program Files\\MySQL\\MySQL Server 8.0\\bin\\yfsql.sql"
ERROR 1044 (42000) at line 2701: Access denied for user 'root'@'localhost' to database 'performance_schema'

Operation failed with exitcode 1
00:22:46 Restoring C:\Program Files\MySQL\MySQL Server 8.0\bin\yfsql.sql
Running: mysql.exe --defaults-file="c:\users\user\appdata\local\temp\tmpawivrr.cnf"  --protocol=tcp --host=127.0.0.1 --user=root --port=3306 --default-character-set=utf8 --comments  < "C:\\Program Files\\MySQL\\MySQL Server 8.0\\bin\\yfsql.sql"
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Operation failed with exitcode 1

So used your method and am able to access the SQL server now using -u root and -p {same password as server where SQL was dumped from} ie seems some of the data from the SQL file was imported into the 'MySQL' schema...message on termination 'database may be in an inconsistent state'...

It's a large database so I terminated import because after the initial error message... root@localhost has no perm on performance schema...I had disabled this in SQL config using MySQL Workbench 8.0 and restarted MyQL...BUT...status shows Performance Schema still active

So I disable performance schema in my.cnf and restarted...Workbench now shows Performance Schema inactive..

BUT the following error occurs on import:

00:22:40 Restoring C:\Program Files\MySQL\MySQL Server 8.0\bin\yfsql.sql
Running: mysql.exe --defaults-file="c:\users\user\appdata\local\temp\tmphmp2sl.cnf"  --protocol=tcp --host=127.0.0.1 --user=root --port=3306 --default-character-set=utf8 --comments  < "C:\\Program Files\\MySQL\\MySQL Server 8.0\\bin\\yfsql.sql"
ERROR 1044 (42000) at line 2701: Access denied for user 'root'@'localhost' to database 'performance_schema'

Operation failed with exitcode 1
00:22:46 Restoring C:\Program Files\MySQL\MySQL Server 8.0\bin\yfsql.sql
Running: mysql.exe --defaults-file="c:\users\user\appdata\local\temp\tmpawivrr.cnf"  --protocol=tcp --host=127.0.0.1 --user=root --port=3306 --default-character-set=utf8 --comments  < "C:\\Program Files\\MySQL\\MySQL Server 8.0\\bin\\yfsql.sql"
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Operation failed with exitcode 1

Notice there are two root users...root@% and root@localhost - both can execute SHOW DATABASES - root@localhost has a password and DBA priviledges and ALL Schema priviledges

So having established this, back to the same roadblock...what credentials are required to import the SQL file into the MySQL schema on a fresh WAMP install, how to do it ?
Adam BellAuthor Commented:
Using PHPMyAdmin error is as follows:

Error
SQL query:

--
-- Dumping data for table `cond_instances`
--

/*!40000 ALTER TABLE `cond_instances` DISABLE KEYS */
MySQL said: Documentation

#1044 - Access denied for user 'root'@'localhost' to database 'performance_schema'

Also to note the unzipped file is >128MB
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Adam BellAuthor Commented:
And here is what comes back when try to create a user for the specific host:

#1805 - Column count of mysql.user is wrong. Expected 45, found 42. The table is probably corrupted
CREATE USER 'root'@'mysql' IDENTIFIED WITH mysql_native_password AS '***';GRANT ALL PRIVILEGES ON *.* TO 'root'@'mysql' REQUIRE NONE WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

The column miscount appears to occur after the first aborted import segment of the SQL file.
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
This message...

ERROR 1044 (42000) at line 2701: Access denied for user 'root'@'localhost' to database 'performance_schema'

means your authentication for root@localhost is still failing.

All my systems use passwords, so unsure how exactly this works with an empty password.

You'll just have to experiment to determine what works.

I'd start with this command...

mysql -uroot -p"" -e "SHOW DATABASES"

Open in new window


Before you can proceed you'll have to ensure the above command works, then also supply -p"" to your load command.

So to summarize, just because you've set an empty password, mysql will likely still prompt for the password, so you can't just leave it out + expect your load to work.

As I recall, you must always supply a password, even if it's empty.
Adam BellAuthor Commented:
I repaired the corrupted user table.

root@localhost has full perms (checked) except on performance_schema which is right back where we started ie caused the import script to abort at step one.

i can only think to try to delete the create db script from the SQL file and avoid it making any system schema changes as it seems as if everything else would work now.
Adam BellAuthor Commented:
Reread this with a fresh head...

And do agree..for some reason MySQLWorkbench attempts the import without a password or prompt for one, having authenticated successfully when you access the instance through it.  The password itself is not actually empty at this point, as have confirmed the first attempt to import changed the root password for root@localhost to that of the importing database.

SHOW DATABASE does not display performance schema in the list, and verify root@localhost has no perms on it...this is probably due to the above (version of MySQL CE on which SQL was exported does not use performance schema).

My best idea on way forward...

1. Reinstall MySQL to ensure a clean start, disable performance schema in my.cfg, then try to import tables/indices/data only, if necessary;

2. Try to delete the lines in the SQL file being imported which cause the schema from the old MySQL instance to start to be imported before lack of perms to performance schema on the new instance causes the script to abort, leaving  the new instance in an inconsistent state (hence reinstalled as quickest sure way around).

Any feedback most welcome, otherwise I'll post the result here after the holidays..

Best wishes to all for the festive season
Adam BellAuthor Commented:
This solution came in two parts...the AWS part requires the correct settings RAS for security GROUP policy which can be tricky if you don't have a fixed IP Address from which you are accessing AWS, so I would recommend getting one it makes life easier.  

The SQL part solution can be found in a separate question I posted
Permissions error importing to MySQL 5.6.x
.

Thanks for the pointers that helped me work through the issues and get this workaround.

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.