MySQL - Access denied for user 'root'@'localhost' (using password: YES)

mysql permissions mac-osx root osx-mountain-lion

6,018

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.

6,018

TheCodingArt

Updated on September 18, 2022

Comments

TheCodingArt over 1 year

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?
- TheCodingArt almost 11 years
  
  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.
TheCodingArt almost 11 years

I'm not an SQL guru unfortunately, would you mind giving me a little insight on how I should approach this?
TheCodingArt almost 11 years

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.
TheCodingArt almost 11 years

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.
Matthew Salsamendi almost 11 years

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.
TheCodingArt almost 11 years

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.
TheCodingArt almost 11 years

Right now I'm going to try executing the commands here: link to see if this works.
TheCodingArt almost 11 years

it looks like importing an individual database may have worked. Would you mind elaborating on why this interfered with my permissions at all if you could? That's what I really need to understand.
Matthew Salsamendi almost 11 years

Yes, but you first have to understand how the MySQL backend works. MySQL primarily operates on the innoDB system engine which is behind everything, but in front of InnoDB is a front-end system that is used for MySQL to manage itself. This primarily takes the form of a MySQL database called "mysql" (SHOW DATABASES;) will shine more light on this. You can get more details by "USE mysql;" "SHOW TABLES;" which will show the data it stores. This data (particularly the "user" table, determines permissions. It is likely that the backup that you received contains this mysql database and is therefore..
Matthew Salsamendi almost 11 years

Overwriting the previous one, this means that your user table is getting completely destroyed by the import, causing all kinds of problems. I would advise you open up the .sql file that you received in something like MySQL workbench on a local installation to see what's really happening in graphical form.
TheCodingArt almost 11 years

Thank you for the great elaboration. I thought something like this may have been happening, but I wasn't sure how or how to explain it. I can't thank you enough for the great explanation.
Matthew Salsamendi almost 11 years

No problem, glad you got it resolved.