Change MY-SQL Root Password

by Devansh


Posted on 18 Nov 2018 10:11:45 (1 month ago)




There are many cases in MY-SQL in which we got error. Sometimes we put correct password for MY-SQL root user but we get password error. One of the irritating error is this.

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

We need to change the password. To resolve this error I have used this thread.

https://askubuntu.com/questions/567590/mysql-server-not-recognizing-correct-password

So to change the password for root user we need to use the below commands,

$ sudo mysql --defaults-file=/etc/mysql/debian.cnf
mysql> UPDATE mysql.user SET Password=PASSWORD('root@123') WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> quit;
$ mysql -u root -p

Here, I want to add point that in MY-SQL 5.7. You will get error i.e.,

"ERROR 1054 (42S22) : Unknown Column 'Password' in 'field list'."

So you just need to change the query to,

mysql> UPDATE mysql.user SET authentication_string=PASSWORD('root@123') WHERE User='root';

So updated all commands are as follow,

$ sudo mysql --defaults-file=/etc/mysql/debian.cnf
mysql> UPDATE mysql.user SET authentication_string=PASSWORD('root@123') WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> quit;
$ mysql -u root -p

Now if you able to login with changed password then you have managed to change the password.