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:
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....
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\appd ata\local\ temp\tmpyz knop.cnf" --protocol=tcp --host=awsendpoint.rds.ama zonaws.com --user=myuser --port=3306 --default-character-set=ut f8 --comments --database=mysql < "C:\\Users\\users\\OneDriv e\\Documen ts\\sqlfil e.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\\s qlfile.sql
Running: mysql.exe --defaults-file="c:\users\user\appda ta\local\t emp\tmpvhi rjz.cnf" --protocol=tcp --host=awsendpoint.rds.ama zonaws.com --user=myuser --port=3306 --default-character-set=ut f8 --comments --database=mysql < "C:\\Users\\user\\OneDrive \\Document s\\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....
ASKER
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...
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:
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 ?
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\appda ta\local\t emp\tmphmp 2sl.cnf" --protocol=tcp --host=127.0.0.1 --user=root --port=3306 --default-character-set=ut f8 --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\appda ta\local\t emp\tmpawi vrr.cnf" --protocol=tcp --host=127.0.0.1 --user=root --port=3306 --default-character-set=ut f8 --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\appda ta\local\t emp\tmphmp 2sl.cnf" --protocol=tcp --host=127.0.0.1 --user=root --port=3306 --default-character-set=ut f8 --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\appda ta\local\t emp\tmpawi vrr.cnf" --protocol=tcp --host=127.0.0.1 --user=root --port=3306 --default-character-set=ut f8 --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 ?
ASKER
Using PHPMyAdmin error is as follows:
Also to note the unzipped file is >128MB
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
ASKER
And here is what comes back when try to create a user for the specific host:
The column miscount appears to occur after the first aborted import segment of the SQL file.
#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...
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...
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.
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"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.
ASKER
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.
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.