Access denied for MYSQL ERROR 1045

Question

I got a new macbook pro (OS X 10.8.2) and am trying to configure mysql on it. So far, I could install it, but I cannot access the root user (or any user for that matter). I plan to use this for Python , on my other computer I use only MYSQL (without MAMP), and I prefer to save it that way.

For reference, I did the following:

$ alias mysql=/usr/local/mysql/bin/mysql $ sudo /Library/StartupItems/MySQLCOM/MySQLCOM start $ alias mysqladmin=/usr/local/mysql/bin/mysqladmin

When I enter mysql or mysql -u root -p , it gives me the following:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

or

ERROR 1045 (28000): Access denied for user 'jmitch'@'localhost' (using password: NO) Depending on which phrase I use

MYSQL works in my system settings. Thank you for your help.

Answer 1

Perhaps by updating the package, the updater overwritten the root password.

To restore it:

Stop mysqld deamons.

 $ sudo service mysqld stop 

Go to mysql / bin directory

 $ cd /usr/bin 

Start mysql deamon with this option:

 $ sudo mysqld_safe --skip-grant-tables 

Open another terminal and open a mysql session to execute this:

 $ mysql mysql> use mysql; see Note1 below for next line. mysql> UPDATE user SET password=PASSWORD('YOUR_NEW_PASSWORD_HERE') WHERE user = 'root'; mysql> exit; 

Now start the mysqld_safe process and restart mysqld normally:

 $ sudo service mysqld start 

Note1: password is the column name in the mysql.user table before version 5.7. After that, he became authentication_string . Modify the update instructions accordingly.

Answer 2

on Mac OSX 10.9 Mavericks I used the "mysql.server" script in the support file directory instead of the mysqld_safe and service script.

 $sudo ./mysql.server stop $sudo ./mysql.server start --skip-grant-tables $ mysql mysql> use mysql; mysql> UPDATE user SET password=PASSWORD('YOUR_NEW_PASSWORD_HERE') WHERE user = 'root'; mysql> exit; $sudo ./mysql.server stop $sudo ./mysql.server start 

Answer 3

I had a similar problem trying to access MySQL MAMP through a terminal on Mountain Lion.

The -no-defaults flag solved this for me.

 /Applications/MAMP/Library/bin/mysql --no-defaults -u root -proot -h localhost 

Answer 4

I want to add that for MySQL 5.7, simply changing the authentication_string column does not work. This is due to the fact that MySQL never uses these values ​​for root authentication, it uses a plugin. As far as I can tell, this plugin checks that you are also root on the main account (so you need sudo mysql -u root).

The only way I was able to get this to work was to run this:

 UPDATE mysql.user SET authentication_string=PASSWORD(''), plugin='' WHERE mysql.user = 'root'; 

It should also be noted that the official MySQL documentation for 5.7 never mentions this. Following this documentation in the letter, you will not receive anything.