Saltar al contenido principal

How to change the MariaDB password for root and regular users

· 3 min de lectura
Andres Saar
Customer Care Engineer

Forgot your MariaDB root password? Without it, you won’t be able to manage users, databases, or perform critical configurations. In this article, you’ll learn not only how to quickly reset the MariaDB root password but also how to reset the password for regular users. 

For MySQL please, refer to our separate guide.

info

The root user is the main administrator of the database. They have full access to all data and settings. If you lose this password, you will not be able to change some settings or execute commands.

All operations will be performed via the command line over SSH. You can find detailed instructions on how to connect to your server using SSH in this article. 

Before proceeding, check the contents of the /root/.my.cnf file. Often, it contains the current root password for accessing MariaDB. 

To test the connection, use the command:

mysql -u root -p 

Then, enter the password from the .my.cnf file. 

If the password doesn’t work, follow the instructions below. 

Resetting the password​

info

In most commands below, the mysql command will be used instead of mariadb to interact with the MariaDB server. This is because, on some operating systems, such as RHEL-based distributions (RockyLinux, AlmaLinux, etc.), the mariadb command is unavailable. Instead, the mysql command is used for compatibility with MySQL.

Using the mysql command provides universal compatibility regardless of the distribution or implementation of the server.

Step 1: Stop MariaDB​

To reset the password, first stop the MariaDB server. Enter the following command:

systemctl stop mariadb

 Step 2: Restart the server in safe mode​

  • For Debian and Ubuntu:

Make sure the directory MariaDB will run from exists and has the correct owner:

mkdir -p /var/run/mysqld/ && chown -R mysql: /var/run/mysqld/

 Start the MariaDB server without access control:

mysqld_safe --skip-grant-tables --socket=/var/run/mysqld/mysqld.sock &

If the MariaDB server starts successfully, you will see a message similar to:

2024-11-28T23:50:19.298141Z mysqld_safe Starting mariadb daemon with databases from /var/lib/mysql

To continue working in the command line, press “Ctrl + C”. 

  • For CentOS/RockyLinux/AlmaLinux:

Start the MariaDB server without access control:

mysqld_safe --skip-grant-tables --socket=/var/lib/mysql/mysql.sock &

To continue working in the command line, press “Ctrl + C”.

Step 3: Connect to MariaDB​

  • For Debian and Ubuntu:
mysql --socket=/var/run/mysqld/mysqld.sock
  • For CentOS/RockyLinux/AlmaLinux:
mysql --socket=/var/lib/mysql/mysql.sock

 Step 4: Reset the password​

Execute the following commands sequentially:

FLUSH PRIVILEGES;

 For a local user:

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

For a network user (if it exists):

ALTER USER 'root'@'%' IDENTIFIED BY 'NewPassword';

Then:

FLUSH PRIVILEGES;

Replace NewPassword with your desired new password.

info

'user'@'localhost' — refers to a local user connecting via socket or localhost. 'user'@'%' refers to a network user with access from any IP address.

You can set the same or different passwords for these users. If the network user doesn’t exist, the @'%' command will result in an error.

Exit MariaDB using the exit command or by pressing “Ctrl + D”. 

Step 5: Restart MariaDB​

Stop the MariaDB server:

  • For Debian and Ubuntu:
mysqladmin shutdown --socket=/var/run/mysqld/mysqld.sock -p

Then, enter the previously set root password.

  • For CentOS/RockyLinux/AlmaLinux:
mysqladmin shutdown --socket=/var/lib/mysql/mysql.sock -p

Then, enter the previously set root password.

Start MariaDB in normal mode:

systemctl start mariadb

 How to reset the password for regular users​

If you forget the password for a user other than root, the approach is similar:

  1. Connect to the MariaDB server as root:
mysql -u root -p

 Enter your MariaDB root password.

  1. Execute the command to change the password:

For a local user:

ALTER USER 'username'@'localhost' IDENTIFIED BY 'NewUserPassword';

For a network user (if it exists):

ALTER USER 'username'@'%' IDENTIFIED BY 'NewUserPassword';

Then:

FLUSH PRIVILEGES;

Replace username with the actual username and NewUserPassword with your desired new password.

Exit MariaDB using the exit command or by pressing “Ctrl + D”.

If necessary, you can list all users with the following query:

SELECT User, Host FROM mysql.user;

How to connect to MariaDB locally without entering a password​

To avoid entering the password manually each time you connect to MariaDB from the command line on your server, you can save it in the /root/.my.cnf file. Open the file in a text editor:

nano /root/.my.cnf

Add the following lines:

[client]

user = root
password = YourRootPassword

Replace YourRootPassword with your actual root password.

Save the file by pressing Ctrl + O, then exit the text editor with Ctrl + X. 

For security purposes, set stricter permissions on the .my.cnf file:

chmod 600 /root/.my.cnf

After this, you can connect by simply running the command:

mysql -u root