découvrez comment changer facilement le mot de passe de l'utilisateur root ou d'autres utilisateurs dans mysql et mariadb. suivez nos étapes simples pour sécuriser vos bases de données et gérer efficacement les accès.

Change the password of the root user or another user in MySQL / MariaDB

by

Mastering password management in MySQL and MariaDB is a key skill for any system administrator or developer working with these popular databases. Whether to ensure security, meet a compliance requirement, or simply correct a forgotten password, changing a user’s password—especially the root password—is an operation that must be clear and rigorous. The open-source database landscape continues to evolve, with MariaDB positioning itself as a 100% free alternative to MySQL, which operates under a dual-licensing model. Through methods accessible both from the command line and via graphical interfaces, this guide will delve into the procedures adapted to the latest stable versions (MySQL 8.0.38 and MariaDB 11.7) and highlight the essential tools to facilitate this access management. Enhanced security in production environments requires not only regular password changes, but also a good understanding of the underlying technical requirements, particularly in relation to the Linux systems on which MySQL and MariaDB frequently run. Handling these passwords, whether for the root user—a key figure in extended privileges—or standard users, must be done carefully and methodically. We will also explore solutions such as phpMyAdmin, Adminer, HeidiSQL, DBeaver, Workbench, Navicat, SQLyog, and Toad, which provide significant convenience in daily management.

Throughout this presentation, several aspects will be detailed: secure database connection, specific SQL commands for modifying credentials, user validity verification before changes, as well as practical implementation through concrete examples and tips for avoiding common mistakes. Particular emphasis will be placed on current hashing and authentication mechanisms, so that administrators can maintain high standards of data protection.

Changing the root or user password in MySQL/MariaDB via the command line

Changing a user’s password in MySQL or MariaDB often begins with a command line operation, a method favored by system administrators because it allows complete and rapid control, particularly on Linux servers. After logging in to the database with an account with sufficient privileges (often the root user), several clearly defined steps must be followed. Secure connection to the database in text mode

Access to the database is performed using the command

mysql -u root -p

, where -u rootdesignates the user and -p prompts for the password. It is important to connect to the correct instance, possibly specifying the mysql database to access the user table: Command to execute: mysql -u root -p mysql

  • The interface then requests the password associated with the root user. You will then receive a mysql prompt where you can type SQL queries.
  • This approach is universal, whether on a local Linux server, a Docker container, or an installation on Windows or macOS. For more information on user management on Linux, it is recommended to consult specialized resources, particularly on the “sudo” and “su” commands used in system administration:
  • Discover these essential Linux commands.

List users and their associated hostsBefore changing a password, verifying the user’s existence and knowing the authorized hosts is essential to avoid making mistakes during the update:

For MariaDB, the query is:

select user,host,password from mysql.user;

  • For MySQL 8+, due to a structural change, we use: SELECT user,host,authentication_string FROM mysql.user;
  • This step ensures that we are targeting the correct user on the correct client server (localhost, %, ip, etc.). For example, a root user can be defined only for 'root'@'localhost'

, meaning that remote login is prohibited, which is a good security feature. Use the ALTER USER command to change the passwordThe central command for changing a password is

ALTER USER

. Here’s the general syntax: ALTER USER ‘username’@’host’ IDENTIFIED BY ‘new_password’;For example, to change the local root password to

  • _o0t#@()é&P$

, the command would be: ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘_o0t#@()é&P$’;After execution, you can rerun the user verification query to see the difference in the values ​​in the

  • authentication_string

or password column (depending on the version). This command also works on MariaDB and MySQL, starting with relatively recent versions. In some cases, depending on the server configuration and security, it may be necessary to force a reload of privileges using the FLUSH PRIVILEGES;

command, although this action is often automatic. Advanced Password Management Settings The ALTER USER syntax also allows you to add password management options to enhance security:VALID UNTIL

to set a password expiration date, useful in a professional environment depending on internal policies.

PASSWORD EXPIRE

  • to force a password change at the next login. FAILED_LOGIN_ATTEMPTS
  • and ACCOUNT LOCK
  • to manage automatic lockouts after several unsuccessful attempts, improving resistance to brute force attacks. This set of parameters varies depending on MySQL or MariaDB, but they contribute to rigorous access management and should be considered when implementing governance rules. For a deeper dive into Linux security, particularly password management at the OS level, reading dedicated resources is essential: Effectively Securing Your Passwords on Linux . https://www.youtube.com/watch?v=E35HkTHuqT8

Changing a MySQL/MariaDB User Password Using Powerful Graphical Tools A lack of familiarity with databases, or a preference for a user-friendly interface, often leads to the use of third-party applications dedicated to graphical MySQL or MariaDB management. These tools greatly facilitate password changes and general administration without using the command line, while offering advanced features suitable for professionals.PhpMyAdmin: an essential web interface

PhpMyAdmin is undoubtedly the most widely used tool for administering MySQL/MariaDB via a browser. Its installation is simple on a LAMP server, and its user-friendly design allows you to manage user accounts and passwords in just a few clicks:

Log in to the dedicated URL, for example, https://192.168.1.111/phpmyadmin/

Log in with an administrator account

Access the “User Accounts” section

Select the username and click “Change password”

  • Enter the new password, with the option to generate a secure password Select the hash algorithm (default = SHA2, often recommended)
  • Apply via “Run”
  • This process is intuitive and guarantees secure switching. To learn more about installing and configuring PhpMyAdmin, useful tutorials are available, notably on Linuxencaja.net, which cover the steps in detail on Debian or other GNU/Linux distributions:
  • Installation and tips for essential tools under Linux
  • .
  • Other popular graphical clients for user management
  • In addition to phpMyAdmin, several software tools enhance the user experience and are suitable for different platforms:

Adminer: A lightweight alternative often prized for its simplicity and speed of execution.HeidiSQL:

Excellent on Windows, a wizard for managing multiple connections.

DBeaver:

  • Multiplatform, it offers a complete and extensible interface, perfect for developers. Workbench:
  • Published by Oracle, the official MySQL tool, complete for administration. Navicat, SQLyog, and Toad: Commercial solutions offering advanced features, particularly useful in professional environments where SLA management and security are critical. These clients also allow password changes via intuitive graphical interfaces, and most include password generators, activity logs, and other tools to facilitate day-to-day management.
  • https://www.youtube.com/watch?v=JU0aTWNwngk Precautions and Best Practices for Managing MySQL/MariaDB Passwords in a Linux Environment
  • Working with MySQL or MariaDB on Linux also requires mastering system security and user management. These two layers work closely together to ensure optimal protection. Poor database password management can quickly become a critical security vulnerability. Protect the root password and restrict access
  • It’s prudent to ensure that the MySQL root user is only accessible from localhost, thus avoiding connections from outside the server. By default, this is often the initial configuration, but it’s important to verify: Check the root user hosts with

SELECT user,host FROM mysql.user WHERE user=’root’;

Block or restrict root user access from unsecured networks

Prefer less-privileged users for remote access

Use secure connections via SSL/TLS when databases are accessed remotely

Furthermore, protecting the root account at the system level is essential: avoid system users with overly broad rights who could exploit MySQL locally without control. Linux offers powerful tools for this, with permission and group management tailored to this topic:

Discover Linux group management here.

  • Change passwords regularly and track access As part of a security policy, changing passwords regularly is an essential measure. A few simple rules to follow:
  • Establish a regular password change schedule
  • Avoid sharing or reusing passwords
  • Use complex passwords, combining special characters, numbers, and letters

Take advantage of advanced features to enforce password expiration and rotation Check access logs and suspicious behavior Finally, it is recommended to run regular audits, also taking into account the rights granted to each user. For a deeper dive into Linux user administration and associated permissions, this link will be very useful:

User Management and Permissions in Linux

.

  • Advanced Management: Forgotten Root Password Reset Scenarios in MySQL/MariaDB
  • The most critical case remains when the root password has been lost or forgotten. Conventional procedures then no longer work, requiring specific modes to regain control of the database. This approach requires precision and elevated system privileges.
  • Restart the MySQL/MariaDB server in safe mode
  • To regain control of the root user without a password, you must stop the MySQL server and restart it in mode without privilege checking:
  • Stop the server using a command such as

sudo systemctl stop mysql orsudo service mysql stop

Start the server with the option

–skip-grant-tables

which temporarily disables access checking

Log in without a password

  • This step exposes the database to a temporary vulnerability; therefore, it is important to ensure the isolation of the environment during the procedure. Once logged in, you can change the root password as explained in the previous section. Update the password and restart normally After the update, a standard reboot with privilege recovery is essential to restore security: Change the root password with the command ALTER USER
  • or, on older MariaDB versions, SET PASSWORD Run
  • FLUSH PRIVILEGES;

to reload the user table

Stop the server and restart it normally

This type of procedure is best performed in an isolated environment before going live to avoid any service interruptions or mishandling. Other tools such as Adminer or DBeaver can be used to validate these changes once the server is back up and running.

  • https://www.youtube.com/watch?v=xvUW3kF2GzI Additional tools for comprehensive MySQL/MariaDB user management and associated security Beyond manual methods, several applications facilitate password management and access control. A range of tools for different profiles
  • Here is a non-exhaustive list of useful software to know in 2025 to manage MySQL/MariaDB users: phpMyAdmin : intuitive and multi-platform web administration.
  • Administer

: lightweight web solution ideal for rapid interventions.

HeidiSQL

: robust Windows client for advanced use.

DBeaver

: powerful multiplatform, supporting multiple bases.

Workbench

  • : official MySQL tool, complete and reliable.Navicat
  • ,SQLyog
  • ,Toad
  • : professional suites offering many advanced features (SLA management, reports, enhanced security).This software includes the ability to easily change user passwords via clear graphical menus, without manual manipulation of SQL queries. They also often offer options for generating secure passwords or managing permissions.
  • Best practices and integration into a secure workflowThe consistent use of these tools must be accompanied by strict discipline around access rights, documentation and monitoring of operations. For example :
  • Restrict access to phpMyAdmin and other interfaces to secure IP addressesImplement accurate audit and log systems Configure alerts on failed login attemptsTrain administrators in user management and anomaly detection A comprehensive guide on deploying these best practices can draw on recommendations available in specialized resources, such as those covering the basic commands to master under Linux:Essential commands to know for Linux

.