6

I am stuck when trying to access mysql. It's my first time so please be patient with me.

Initially I was try to set up Ruby and Rails and everything worked perfrectly expect access denied when connecting to the server, SO I ran this command. 

mysql -uroot -p

I've tried various passwords including leaving it blank and get this error. 

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

I am assuming I need to reset the password for root user but I just can't seem to get it to work.

Alex M
  • 2,756
  • 7
  • 29
  • 35
Deedub
  • 349
  • 2
  • 7
  • 20

3 Answers3

14

One method:

  1. stop your MySQL server.

  2. start your MySQL server with the --skip-grant-tables option. It allows you to connect to the server without a password.

    /path/to/mysqld --skip-grant-tables &

  3. connect to your server using the mysql client:

    mysql

  4. change the root password (replace NewPassord by what you want):

    UPDATE mysql.user SET password=PASSWORD('NewPassord') WHERE user='root';

  5. restart yout MySQL server.

There are others ways to reset the MySQL root password: http://dev.mysql.com/doc/refman/5.6/en/resetting-permissions.html

Alex M
  • 2,756
  • 7
  • 29
  • 35
Muur
  • 335
  • 3
  • 8
  • 1
    I dont have any database named `mysql`, I have two databases: `information_schema` and `test`. I connected through this command: ` mysql -h127.0.0.1` as via just `mysql` I was getting this err: `ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)` – Saurabh Nov 07 '16 at 08:46
  • 1
    In my case I got the following error : ERROR 1054 (42S22): Unknown column 'password' in 'field list'. For that, I checked user table structure and realised that column name is 'authentication_string'. Followed this link : http://stackoverflow.com/questions/30692812/mysql-user-db-does-not-have-password-columns-installing-mysql-on-osx – Heena Hussain Feb 08 '17 at 07:04
  • I have tried sudo mysqld_safe --skip-grant-tables, but it doesn't work. this comman : /path/to/mysqld --skip-grant-tables & , works for me. Thx, my mac is OSX10.11.6, mysql server5.7.17 – penny chan Mar 27 '17 at 04:30
  • @pennychan you have to replace `/path/to` with the actual path to the `mysqld` file. From the command line you can run `locate mysqld` and it will (probably) show you the path to that file. – b_dubb Jan 08 '18 at 02:46
  • @b_dubb Thx, that's exactly what I mean – penny chan Jan 13 '18 at 01:09
  • /path/to/mysqld --skip-grant-tables & results in: [ERROR] [MY-010123] [Server] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root! – Draif Kroneg May 26 '20 at 07:02
  • uninstalling mysql and installing with an installer helped me resolve it . https://stackoverflow.com/a/67597173/6840615 – Md Sifatul Islam May 19 '21 at 05:03
1

I had a similar issue and this worked like a charm -

Later versions of mysql implement a newer authentication scheme, not all libraries are up to date with this. You can revert to classic authentication by adding the following entry into your my.cnf

[mysqld]

# Only allow connections from localhost
bind-address = 127.0.0.1

# Some libraries not compatible with latest authentication scheme as per the SO article [1].
default-authentication-plugin=mysql_native_password

Simply add the following in /private/etc/my.cnf

# Only allow connections from localhost
bind-address = 127.0.0.1

Reference: https://www.reddit.com/r/mysql/comments/ae7rf4/access_denied_for_user_rootlocalhost_mac_os/

AmolR
  • 71
  • 1
  • 5
0

@Muur the last step did not worked for me(mysql Ver 14.14 Distrib 5.7.19) i had to change query to update user set authentication_string=password('1111') where user='root'; update user set password_expired=N where user='root';

Vijay Surwase
  • 13
  • 5