Cannot Change root Password MySQL using MySQL console

Posted on

Having troubled for connecting to MySQL Database Server because the password is cannot be changed ?. If it is not mistaken for an example if the password itself is empty, it will be rejected if it is used in a Laravel based framework  for accessing a database using that kind of configuration connection.

So, there is a need to change the password because it is empty or on the other hand it is not empty and it will be changed to a more powerful combination character for the password itself.

Below is a scenario where the password, in this context is a ‘root’ password by all means or whatsoever is currently empty.

Having a past experience for changing password for several user account with the following command is actually effective :

update user set authentication_string=PASSWORD('mynewpassword') where user='root';

Below is the execution of the above command for changing password of an account named ‘root’ :

user@hostname:~$ mysql -uroot -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.12-0ubuntu1.1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set authentication_string=PASSWORD('P@$$w0rd!') where user='root';
Query OK, 4 rows affected, 1 warning (0,00 sec)
Rows matched: 4  Changed: 4  Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected (0,00 sec)

mysql> 

Don’t forget to try to access MySQL Database again using the changed password defined from the above process as shown below :

user@hostname:~$ mysql -uroot -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 117
Server version: 5.7.12-0ubuntu1.1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> quit
Bye
user@hostname:~$ 

But if in several cases, the above command execution cannot be done or it is not so effective on changing the empty password of an account, the following method can also be executed as follows :

set password for 'root'@'localhost' = PASSWORD('mynewpassword');

Below is the execution of the above command shown below for an example :

user@hostname:~$ mysql -uroot 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 133
Server version: 5.7.12-0ubuntu1.1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set password for 'root'@'localhost' = PASSWORD('mynewpassword');
Query OK, 0 rows affected, 1 warning (0,06 sec)

mysql> set password for 'root'@'127.0.0.1' = PASSWORD('mynewpassword');
Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0,05 sec)

mysql> quit
Bye

user@hostname:~$

Depends on the host which is going to be defined or to be connected, the above command must be adjusted in order to connect to the intended database server for an example in the above context it is ‘localhost’ or ‘127.0.0.1’. Try to connect again using the changed password retrieved from the process above which has already successfully on changing the password of a database user account. On the above command, several references informing that the last command for changing the password can only be done from MySQL version 5.7 and so on.

user@hostname:~$ mysql -uroot -p test
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 134
Server version: 5.7.12-0ubuntu1.1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> quit
Bye

user@hostname:~$

One thought on “Cannot Change root Password MySQL using MySQL console

Leave a Reply