MySQL/MariaDB Root Password Reset Procedure

Overview: This Article provides a comprehensive guide for resetting the MySQL or MariaDB root password. It details how to stop the MySQL/MariaDB service, start it with the --skip-grant-tables option, and connect to the server to update the root password. The guide includes steps to restart the service normally and verify the new password by logging into the database. Follow these instructions to securely reset and manage your root password.

To reset the MySQL or MariaDB password, you need to Stop the MySQL/MariaDB service and Start the service with the "--skip-grant-tables" option. Later Connect to the MySQL/MariaDB server and update the password for the user.

Step 1. First you need to stop MySQL or MariaDB server.

root@localhost:# service mariadb stop

root@localhost:~# service mysql stop

Step 2. Start the MySQL/MariaDB server without loading the grant tables.

root@localhost:~# mysqld_safe --skip-grant-tables &

When the --skip-grant-tables option is used, anyone can connect to the database server without a password and with all privileges granted.

Step 3. Log in to the Mysql or MariaDB shell

root@localhost:~# mysql -u root

Step 4. Set a new password.

Type the following command, replacing "new-password" with the new root password

MariaDB [(none)]> UPDATE mysql.user SET Password=PASSWORD('new-password') WHERE User='root';

MariaDB [(none)]> FLUSH PRIVILEGES;

MariaDB [(none)]> exit;

In old version of mariadb run the following command. Replace 'new password' with the new root password

MariaDB [(none)]> FLUSH PRIVILEGES;

MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

Step 5. Stop the MySQL server using the following command. You will be prompted to enter the new MySQL root password before the MySQL server shuts down

root@localhost:~# mysqladmin -u root -p shutdown

Make sure to kill all the running mariadb process (pkill).

Step 6. Start the MySQL/MariaDB server normally

root@localhost:~# service mariadb start

Step 7. Once started you can check the status of MariaDB service using service mariadb status command as shown below

root@localhost:~#service mariadb status

Step 8. Log in to MariaDB with New Password

Test the new reset MariaDB root password by logging in to the Server using mysql -u root -p command as shown below. Once you provide the root password it should allow you to log in just the way it is described below.

[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.56-MariaDB MariaDB Server

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Basic MySQL commands for troubleshooting database related issues in Ezeelogin

configure jump server to use SSL for MySQL