How to change the MariaDB password for root and regular users
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.
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
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.
'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:
- Connect to the MariaDB server as root:
mysql -u root -p
Enter your MariaDB root password.
- 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