Setting, Changing and Resetting MySQL and MariaDB Root Passwords

MySQL and MariaDB are popular open-source relational database management systems used by many websites and applications. Managing the root password is critical for securing your database. This guide will walk you through the steps to set, change, and reset the root password for both MySQL and MariaDB on a Linux server. This is especially useful in environments like Windows VPS UK or other Windows Virtual Private Servers.

Prerequisites

Before starting, ensure you have the following:

• Access to a server running MySQL or MariaDB, which could be hosted on a UK Windows VPS, VPS Windows Servers, or other hosting environment.
• Root or sudo privileges on your server.

Step 1: Setting the MySQL/MariaDB Root Password

If you are setting up MySQL or MariaDB for the first time, you will need to set the root password. You can do this during the initial installation or later. If you have not set the password yet, follow these steps:

sudo mysql_secure_installation

During the mysql_secure_installation process, you will be prompted to set the root password. Follow the prompts to create a secure password. Once completed, your root password will be set.

Step 2: Changing the MySQL/MariaDB Root Password

If you already have a root password but want to change it, you can do so by logging into the MySQL/MariaDB shell with your current root credentials:

sudo mysql -u root -p

Once logged in, you can change the root password by executing the following command:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

Make sure to replace new_password with your desired password. Then, flush the privileges to apply the changes:

FLUSH PRIVILEGES;

Finally, exit the MySQL shell:

EXIT;

Step 3: Resetting the MySQL/MariaDB Root Password

If you have forgotten your MySQL or MariaDB root password, you can reset it by following these steps:

Step 3.1: Stop the MySQL/MariaDB Service

First, stop the MySQL/MariaDB service to start the server in a safe mode:

sudo systemctl stop mysql  # For MySQL
sudo systemctl stop mariadb  # For MariaDB

Step 3.2: Start MySQL/MariaDB in Safe Mode

Next, start the server without password validation:

sudo mysqld_safe --skip-grant-tables &

This will start the MySQL/MariaDB server without loading the grant tables, allowing you to log in without a password.

Step 3.3: Log in and Reset the Root Password

Once the server is running in safe mode, log in as root without a password:

mysql -u root

Then, reset the root password using the following command:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

Afterward, flush the privileges and exit the MySQL shell:

FLUSH PRIVILEGES;
EXIT;

Step 3.4: Restart MySQL/MariaDB Service

Finally, stop the safe mode server and restart the MySQL/MariaDB service normally:

sudo systemctl stop mysql  # For MySQL
sudo systemctl stop mariadb  # For MariaDB
sudo systemctl start mysql  # For MySQL
sudo systemctl start mariadb  # For MariaDB

Step 4: Verifying the New Root Password

To verify that the new root password works, log in again using the updated password:

mysql -u root -p

Enter your new password when prompted, and you should be logged in successfully.

Conclusion

By following these steps, you can easily set, change, or reset the MySQL or MariaDB root password. Proper management of your root password ensures the security of your databases, which is crucial for your server environment, whether hosted on a Windows VPS UK, Windows Virtual Dedicated Server Hosting, or other platforms.