DEV Community

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

Posted on • Updated 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

iseecodepeople profile image

Amazon FSx for NetApp ONTAP - Expert Storage for any workload

Varun S -

sakshi360 profile image

Efficiently Deleting Multiple Lakhs of Records from DynamoDB Using Lambda: A Comprehensive Guide

Sakshi Agarwal -

zahraajawad profile image

Building a Jupyter Notebook Environment in Docker for Data Analysis on AWS EC2

Zahraa Jawad -

seifrajhi profile image

Amazon EKS add-ons: A curated set of software for managing your Kubernetes clusters

saifeddine Rajhi -