Forgetting passwords happens to the best of us. If you forget or lose the root password to your MySQL or MariaDB database, you can still gain access and reset the password if you have access to the server and a user account with sudo. How To Reset MariaDB or MySQL Root Password on Ubuntu
Step 1: Check Database Version
mysql --versionMariaDB will output
mysql Ver 15.1 Distrib 10.1.29-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2MySQL will output
mysql Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using EditLine wrapperStep 2: Stop Database Server
For MariaDB using the command below
sudo systemctl stop mariadbFor MySQL using the command below
sudo systemctl stop mysqlStep 3: Restart Database without Permission
Configuring MariaDB to Start Without Grant Tables
Execute the following command which sets the MYSQLD_OPTS environment variable used by MariaDB upon startup. The --skip-grant-tables and --skip-networking options tell MariaDB to start up without loading the grant tables or networking features:
sudo systemctl set-environment MYSQLD_OPTS="--skip-grant-tables --skip-networking"Then start the MariaDB server:
sudo systemctl start mariadbThis command won’t produce any output, but it will restart the database server, taking into account the new environment variable settings.
You can ensure it started with sudo systemctl status mariadb.
Now you should be able to connect to the database as the MariaDB root user without supplying a password:
sudo mysql -u rootYou’ll immediately see a database shell prompt:
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]Configuring MySQL to Start Without Grant Tables
In order to start the MySQL server without its grant tables, you’ll alter the systemd configuration for MySQL to pass additional command-line parameters to the server upon startup.
To do this, execute the following command:
sudo systemctl edit mysqlThis command will open a new file in the nano editor, which you’ll use to edit MySQL’s service overrides. These change the default service parameters for MySQL. This file will be empty, so add the following content:
[Service]
ExecStart=
ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid --skip-grant-tables --skip-networkingThe first ExecStart statement clears the default value, while the second one provides systemd with the new startup command including parameters to disable loading the grant tables and networking capabilities.
Press CTRL-x to exit the file, then Y to save the changes that you made, then ENTER to confirm the file name.
Reload the systemd configuration to apply these changes:
sudo systemctl daemon-reloadNow start the MySQL server:
sudo systemctl start mysqlThe command will show no output, but the database server will start. The grant tables and networking will not be enabled.
Connect to the database as the root user:
sudo mysql -u rootYou’ll immediately see a database shell prompt:
Type 'help;' or '\h' for help. Type '\c' to clear the current input statementStep 4: Changing the Root Password
The database server is now running in a limited mode; the grant tables are not loaded and there’s no networking support enabled. This lets you access the server without providing a password, but it prohibits you from executing commands that alter data. To reset the root password, you must load the grant tables now that you’ve gained access to the server.
Tell the database server to reload the grant tables by issuing the FLUSH PRIVILEGES command.
sudo mysql -u rootFLUSH PRIVILEGES;Changing the MariaDB Password
If you are using MariaDB, execute the following statement to set the password for the root account, making sure to replace new_password with a strong new password that you’ll remember.
UPDATE mysql.user SET password = PASSWORD('new_password') WHERE user = 'root';You’ll see this output indicating that the password changed:
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0The password is now changed. Type exit to exit the MariaDB console and proceed to Step 4 to restart the database server in normal mode.
Changing the MySQL Password
For MySQL, execute the following statement to change the root user’s password, replacing new_password with a strong password you’ll remember:
UPDATE mysql.user SET authentication_string = PASSWORD('new_password') WHERE user = 'root';You’ll see this output indicating the password was changed successfully:
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0Step 5: Set Database Server to Normal Setting
In order to restart the database server in its normal mode, you have to revert the changes you made so that networking is enabled and the grant tables are loaded. Again, the method you use depends on whether you used MariaDB or MySQL.
sudo systemctl unset-environment MYSQLD_OPTSThen, restart the service using systemctl:
sudo systemctl restart mariadbFor MySQL, remove the modified systemd configuration:
sudo systemctl revert mysqlYou’ll see output similar to the following:
Removed /etc/systemd/system/mysql.service.d/override.conf.
Removed /etc/systemd/system/mysql.service.d.Then, reload the systemd configuration to apply the changes:
sudo systemctl daemon-reload
sudo systemctl restart mysqlThe database is now restarted and is back to its normal state. Confirm that the new password works by logging in as the root user with a password:
mysql -u root -pYou’ll be prompted for a password. Enter your new password and you’ll gain access to the database prompt as expected.
Note:
You have restored administrative access to the MySQL or MariaDB server. Make sure the new password you chose is strong and secure and keep it in a safe place.
For more information on user management, authentication mechanisms, or ways of resetting database password for other version of MySQL or MariaDB, please refer to the official MySQL documentation or MariaDB documentation.
If this tutorial could help you, please rate above Star button rating and share to help others find it! Feel free to leave a comment below.
