Kyota NakadaWhen you installed MySQL for the first time, you can't login with a no-password.
You should add the following line in my.cnf to login without a password.
skip-grant-tables
It has a huge security risk, so that you should set a password to prevent external, illegal access.
How to Retake Control of the Root User in MySQL
Step1: Connect to MySQL
mysql
Step2: Using the main database
use mysql;
Step3: Redefine user root password
UPDATE user SET `authentication_string` = PASSWORD('myNuevoPassword') WHERE `User` = 'root';
Caution
If you can't execute the command, you should check authentication_string column of mysql.user table.
SELECT User, authentication_string FROM mysql.user\G
*************************** 1. row ***************************
User: mysql.infoschema
authentication_string: $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED
*************************** 2. row ***************************
User: mysql.session
authentication_string: $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED
*************************** 3. row ***************************
User: mysql.sys
authentication_string: $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED
*************************** 4. row ***************************
User: root
W&2-*D:1FsLlMJEFsvaZNpaAlnJDEyTNo2QO7Eu7P0rhe10psBsC
4 rows in set (0.00 sec)
And you execute the following command.
UPDATE user SET `authentication_string` = 'myNuevoPassword' WHERE `User` = 'root';
Step4: Show mysql.user table if you could change root password
SELECT User, authentication_string FROM mysql.user\G
*************************** 1. row ***************************
User: mysql.infoschema
authentication_string: $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED
*************************** 2. row ***************************
User: mysql.session
authentication_string: $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED
*************************** 3. row ***************************
User: mysql.sys
authentication_string: $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED
*************************** 4. row ***************************
User: root
authentication_string: ZAQ$2wsx2408
4 rows in set (0.00 sec)
Step5: Erase line from my.cnf
skip-grant-tables
Step6: Restart MySQL service
sudo systemctl restart mysqld
Step7: Connect to mysql with password
mysql -u root -p
Enter password: [new password]
mysql>
When you need to do this action, please use carefully!
