Access Denied for MYSQL ERROR 1045
Solution 1
Maybe updating the package the updater overwrote the root password.
To restore it:
Stop mysqld deamons.
$ sudo service mysqld stop
Go to mysql/bin directory
$ cd /usr/bin
Start a 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 kill the mysqld_safe process and restart mysqld normally:
$ sudo service mysqld start
Note1: password
is the column name in table mysql.user
prior to version 5.7. After which it became authentication_string
. Change your update statement accordingly.
Solution 2
on Mac OSX 10.9 Mavericks I used the 'mysql.server' script in the support-files 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
Solution 3
I was having a similar issue trying to access MAMP's MySQL through the terminal on Mountain Lion.
The --no-defaults flag solved it for me.
/Applications/MAMP/Library/bin/mysql --no-defaults -u root -proot -h localhost
Solution 4
I want to add that for MySQL 5.7 simply changing the authentication_string column doesn't work. This is because MySQL never actually uses those values for root authentication, it uses a plugin. As far as I can tell this plugin verifies that you are also root on the host account (so you have to 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 to the letter gets you nowhere at all.
Comments
-
jmitchel3 almost 2 years
I just got a new macbook pro (OS X 10.8.2) and am attempting to get mysql set up on it. So far I've been able to get it installed but I cannot get my root user access (or any user for that matter). I plan on using this for
Python
, on my other computer I only use MYSQL (no MAMP) and I prefer to keep 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
ormysql -u root -p
it gives me this: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 phrasing I useMYSQL is running in my system preferences. Thank you for your help.
-
jmitchel3 over 11 years
$ sudo service mysql stop
returnssudo: service: command not found
-
Basav about 10 yearsrunning the daemon in sudo mysqld_safe --skip-grant-tables mode may not be really required, i tried without it and still it solved my problem (this was on mac 10.9)
-
mcn almost 10 yearsthis works for me on OS X Mavericks 10.9 after having a lot of Access Denied when typing in 'mysql -u root -p', thanks!
-
felixwcf over 8 yearsPlease refer to this if you encountered "Unknown column 'password' in 'field list'": stackoverflow.com/a/31122246/1371949
-
Drew almost 8 yearsyes, the column name is
authentication_string
in version 5.7 -
user1283068 over 7 yearsThere is no service called mysqld in Ubuntu, where the correct command would be
service mysql stop
-
user1283068 over 7 yearsIn mysql 14.14 with Ubuntu Xenial, the above doesn't work. Meaning that the original error persists even after the described sequence of commands.
-
Drew over 7 years@user1283068 mysqld? Look it up for your distro?
-
user1283068 over 7 yearsLook what up for my distro? Your comment makes no sense.
-
Drew over 7 years@user1283068 look up how you start and stop services for your distro. I am sorry it makes no sense to you. I cannot tutor everyone new to stuff
-
user1283068 over 7 years@Drew I wasn't asking what the name of the service was, I was just pointing out an error in the reply. As you can see I specifically mention the correct name of the service.
-
Saurabh over 7 yearsI dont have any database named
mysql
, I have two databases:information_schema
andtest
. I connected through this command: ` mysql -h127.0.0.1` as via justmysql
I was getting this err:ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
-
Dave Everitt almost 7 yearsI'm running 5.7* on OS X 10.12.3 and can no longer use the
mysql stop/start
alias, getting the error of the question. Your above answer looks promising, but gives:ERROR 1054 (42S22): Unknown column 'mysql.user' in 'where clause'
? -
saran3h almost 6 yearsIf in case someone gets service command not found error: use this instead: To stop: sudo launchctl unload -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist To start: sudo launchctl load -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist It is recommended to first check the status of your mysql server first. These are the steps for mysql 5.7