How To Reset MySQL 5.7 Root Password In Centos Or Ubuntu

Have you forgotten your MySQL root password? (I know, i know 😂😂😂 it's a secret.)

If you do, and trying to find out how to do it, then you are in the right path my friend.

Let's start and let's code pare.

Quick Note:

if you're doing this in your live server with a live users then congratulations you're a PRO developer. 😂 😂 😂

If ever you don't have a choice, make sure to change it late night or when there is not much traffic in your server.

STEP 1

First thing we need to do is login in your server and to stop MySQL service.

if you logged in as root user then type this code

/etc/init.d/mysql stop


# OR
service mysql stop

Incase you're not a root user but have privilege as a root, then just add sudo in the beginning of the command.

sudo /etc/init.d/mysql stop

# OR
sudo service mysql stop

Once you run this code, it will stop your mysql services and no one can connect to your database temporary.

( Make sure to notify your co-developer and the other teams involve or else you will receive a new Ticket Error 😂 )

STEP 2

We need to start our mysql in a safe mode. What is mysqld_safe command means?

mysqld_safe is the recommended way to start a mysqld server on Unix. mysqld_safe adds some safety features such as restarting the server when an error occurs and logging runtime information to an error log. A description of error logging is given later in this section.

Run this code below, when running mysqld_safe with --skip-grant-tables it enables anyone to connect without a password and with all privileges. Together with the ampersand "&" to run the command in the background.

mysqld_safe --skip-grant-tables &

#OR
sudo mysqld_safe --skip-grant-tables &

STEP 3

We're almost there pare, just keep going.

Login in our mysql by running this command, you should see the mysql monitor welcome screen.

mysql -u root

STEP 4

In this step, we will change the password of your mysql root user.

Type this code, to use mysql table and Press enter

use mysql;

Then the code to change your root password is the command below. Make sure to change the "EnterYourSuperCoolPasswordHere" before running it.

Take note, the authentication_string is for mysql 5.7 up version.. incase you have a lower version of mysql you can use password column instead.

UPDATE mysql.user SET authentication_string=PASSWORD('**EnterYourSuperCoolPasswordHere**'), plugin='mysql_native_password' WHERE User='root';

Reload the grant tables to take effect using the command below.

FLUSH PRIVILEGES;

Then exit from mysql command window using

quit;

STEP 5

Start your mysql server

sudo /etc/init.d/mysql start

# OR
sudo service mysql start

And login in your mysql as root user using your new password.

mysql -u root -p

If everything works, then you will see a welcome message in mysql monitor command screen.

COMMON ERRORS

1). Access denied; you need (at least one of) the SHOW DATABASES privilege(s) for this operation.

SOLUTION: Login as mysql root then type this command:

GRANT SELECT, PROCESS ON *.* TO 'username'@'localhost';
FLUSH PRIVILEGES;
quit;

2). mysqld_safe Directory ‘/var/run/mysqld’ for UNIX socket file don’t exists.

SOLUTION:

sudo mkdir /var/run/mysqld
sudo chown mysql:mysql /var/run/mysqld

sudo pkill -9 mysqld

Then try to stop or start again mysql.

3) ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded. Basically we need to set the authorization plugin back to mysql_native_password.

SOLUTION: Login as mysql root then type this command:

use mysql;

update user set plugin="mysql_native_password" where User='root';

flush privileges;

quit;

4) ERROR 1054 (42S22): Unknown column ‘password’ in ‘field list’

SOLUTION:

It means you're using a low version of code for mysql 5.6 below. password column was removed from mysql 5.7 above, As i mention in the tutorial above you need to use authentication_string

Voila

I hope i help you solving your problem. Let me know incase you encounter a different error.

Thanks pare. 👊 👊 👊

Next Featured

Explore our security tips for safety server and process. Explore our security tips for safety server and process

Voila!!!

I hope you enjoy our tutorial, Let me know incase you encounter any error I would love to answer that. Don't forget to subscribe to my Youtube Channel at Let's Code Pare - Youtube Channel

Share Now