Retake control of the root user in MySQL-Mysql Tutorial-php.cn

Retake control of the root user in MySQL

PHPz

Release： 2024-08-26 06:30:31

Original

177 people have browsed it

Retake control of the root user in MySQL

When 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

Copy after login

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

Copy after login

Step2: Using the main database

use mysql;

Copy after login

Step3: Redefine user root password

UPDATE user SET `authentication_string` = PASSWORD('myNuevoPassword') WHERE `User` = 'root';

Copy after login

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)

Copy after login

And you execute the following command.

UPDATE user SET `authentication_string` = 'myNuevoPassword' WHERE `User` = 'root';

Copy after login

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)

Copy after login

Step5: Erase line from my.cnf

skip-grant-tables

Copy after login

Step6: Restart MySQL service

sudo systemctl restart mysqld

Copy after login

Step7: Connect to mysql with password

mysql -u root -p
Enter password: [new password]

mysql>

Copy after login

When you need to do this action, please use carefully!

The above is the detailed content of Retake control of the root user in MySQL. For more information, please follow other related articles on the PHP Chinese website!