DEV Community

Cover image for Retake control of the root user in MySQL
Kyota Nakada
Kyota Nakada

Posted on • Edited on

Retake control of the root user in MySQL

#mysql #aws

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Step2: Using the main database

use mysql;
Enter fullscreen mode Exit fullscreen mode

Step3: Redefine user root password

UPDATE user SET `authentication_string` = PASSWORD('myNuevoPassword') WHERE `User` = 'root';
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

And you execute the following command.

UPDATE user SET `authentication_string` = 'myNuevoPassword' WHERE `User` = 'root';
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

Step5: Erase line from my.cnf

skip-grant-tables
Enter fullscreen mode Exit fullscreen mode

Step6: Restart MySQL service

sudo systemctl restart mysqld
Enter fullscreen mode Exit fullscreen mode

Step7: Connect to mysql with password

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

mysql>
Enter fullscreen mode Exit fullscreen mode

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

Top comments (0)

Read next

briansuarezsantiago profile image

Entendiendo el Shared Responsibility Model de AWS y por qué es clave para la seguridad en la nube

Brian -

hassan_sherwani_9dd766c43 profile image

Building Serverless Agentic Workflows with Amazon Bedrock

Hassan Sherwani -

globart profile image

S3 native state locking in Terraform

globart -

somilgupta profile image

Understanding Amazon Bedrock's New Feature - "Flows"

Somil Gupta -