Retake control of the root user in MySQL

This content originally appeared on DEV Community and was authored by Kyota Nakada

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

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!

This content originally appeared on DEV Community and was authored by Kyota Nakada

Print Share Comment Cite Upload Translate Updates

APA

Kyota Nakada | Sciencx (2024-08-21T21:04:34+00:00) Retake control of the root user in MySQL. Retrieved from https://www.scien.cx/2024/08/21/retake-control-of-the-root-user-in-mysql/

MLA

" » Retake control of the root user in MySQL." Kyota Nakada | Sciencx - Wednesday August 21, 2024, https://www.scien.cx/2024/08/21/retake-control-of-the-root-user-in-mysql/

HARVARD

Kyota Nakada | Sciencx Wednesday August 21, 2024 » Retake control of the root user in MySQL., viewed ,<https://www.scien.cx/2024/08/21/retake-control-of-the-root-user-in-mysql/>

VANCOUVER

Kyota Nakada | Sciencx - » Retake control of the root user in MySQL. [Internet]. [Accessed ]. Available from: https://www.scien.cx/2024/08/21/retake-control-of-the-root-user-in-mysql/

CHICAGO

" » Retake control of the root user in MySQL." Kyota Nakada | Sciencx - Accessed . https://www.scien.cx/2024/08/21/retake-control-of-the-root-user-in-mysql/

IEEE

" » Retake control of the root user in MySQL." Kyota Nakada | Sciencx [Online]. Available: https://www.scien.cx/2024/08/21/retake-control-of-the-root-user-in-mysql/. [Accessed: ]

rf:citation

» Retake control of the root user in MySQL | Kyota Nakada | Sciencx | https://www.scien.cx/2024/08/21/retake-control-of-the-root-user-in-mysql/ |

Please log in to upload a file.

There are no updates yet.
Click the Upload button above to add an update.

You must be logged in to translate posts. Please log in or register.

How to Retake Control of the Root User in MySQL

Related Posts