I'm pulling my hair out trying to decipher why every time I do a fresh install of mysql on OSX and import a database, the minute I restart the mysql instance I'm prompted with the message: Access denied for user 'root'@'localhost' (using password: YES). I've kept the password to defaulted root, and this is quit frustrating. I've tried my hardest to ensure no other instances of mysql are running. The Activity monitor reports no other instances of mysql, and when searching for pids no other instances appear. Does anyone know what is happening?
-
This doesn't help at all. Upgraded after reinstalling mysql one more time (mind you I'm following these instructions for reinstall: link) and then importing the db, I was immediately kicked out of my connection again. I can't log in as ANY of the users I created.– TheCodingArtJul 27, 2013 at 17:30
2 Answers
This error can occur when you import SQL tables that contain the MySQL database itself, this database contains the underlying schema that MySQL itself uses, this can cause serious issues when attempting to get into the database. Make sure that your imported SQL dump only includes the database you wish to import and nothing more.
-
I'm not an SQL guru unfortunately, would you mind giving me a little insight on how I should approach this? Jul 27, 2013 at 17:31
-
I believe I am just importing 3 databases out of an SQL dump. I didn't realize this would cause conflict. I'm not sure how to specify a specific database out of the dump (event though I believe I want all three). I don't specify a db when attempting to connect to mysql. Currently, I'm using Sequel Pro to import the databases view the import option. Jul 27, 2013 at 19:37
-
Make sure that your SQL dump itself does not contain any other databases besides the ones you want, restoring that SQL dump to a specific database will 'install' it to all of them. Jul 27, 2013 at 20:55
-
This isn't 'my' sql dump. This is one provided by the client. I want to avoid asking for a corrected dump at all costs. Is there a way I can specify the databases to import from the dump? I'm not 'restoring' per say, I'm importing. I've done this by either terminal appending via "<" and I've done this via the import option within Sequel Pro. Jul 27, 2013 at 22:51
-
Right now I'm going to try executing the commands here: link to see if this works. Jul 28, 2013 at 0:26
This error could occur in the following cases:
Using an incorrect password. Since you mentioned that you have defaulted the password to root, check if you have the CAPS lock on.
It could occur if you have an incorrect password specified in an option file. Try logging in using:
mysqladmin --no-defaults -u root -p pass
You might have to grant access to root.
Stop MySQL:
/etc/init.d/mysql stop
Start MYSQL skippping grants:
/usr/sbin/mysqld --skip-grant-tables --skip-networking &
Login:
mysql -u root
Flush privileges:
FLUSH PRIVILEGES;
Update Password:
SET PASSWORD FOR root@'localhost' = PASSWORD('password'); UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';
-
In essence, I've already done these steps before.... only a few times did they kind of work but the issue still lies that EVERY time I import this db I will be kicked out of ALL of my accounts. So I don't believe this is the solution for my problem since it only 'sometimes' works and I have 0 access to any of my accounts what-so-ever. Jul 27, 2013 at 17:33