Unable to import SQL file to MySQL on AWS-RDS

Problem is...

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

Select allOpen 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.

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 ?

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

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.

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"

Select allOpen 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.

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.

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