Linux

How to Manage, Change and Reset MySQL and MariaDB Root Passwords

How to Manage, Change and Reset MySQL and MariaDB Root Passwords

This tutorial explains how you can set, change, and reset (if you forget the password) MySQL or MariaDB root passwords. Many times I see problems like mysqladmin: connecting to the server on ‘localhost’ failed error: ‘Access is denied to users’ root ‘@’ localhost ‘(using password: YES)’. So I think it’s time to remind you how to solve password problems related to MySQL. If you are just looking for a quick fix how to reset the MySQL root password you can find it at the bottom of this tutorial. This tutorial is compatible with all major Linux distributions included. CentOS, Debian, Fedora, and Ubuntu.

Use the mysqladmin Command to Change the Root Password

Method 1 – Prepare the root password for the first time

If you have never set a root password for MySQL, the server doesn’t need a password at all to connect as root. To set the root password for the first time, use the mysqladmin command at the shell prompt as follows:

mysqladmin -u root password newpass

If you want to change (or update) the root password to the new ‘newpass‘ password, then you need to use the following command:

mysqladmin -u root -p password newpass
Enter password:

If you get …

mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'

then follow the instructions below on how to recover your MySQL password.

The ‘password‘ in the example above is part of the command, don’t replace it with your password. The word ‘newpass‘ is a new password.

1

An alternative to using the mysqladmin command when setting the MySQL or MariaDB root password for the first time is to use the mysql_secure_installation command. This command will not only ask for the old and new MySQL root password, but will also perform several other security settings such as deactivating the test database.

Here’s how to use the command:

mysql_secure_installation

The answer to the question as shown below:

Change the root password? [Y/n] <-- y
New password: <-- Enter a new MySQL root password
Re-enter new password: <-- Repeat the MySQL root password
Remove anonymous users? [Y/n] <-- y
Disallow root login remotely? [Y/n] <-- y
Remove test database and access to it? [Y/n] <-- y
Reload privilege tables now? [Y/n] <-- y 

The answer above is a recommendation, you are free to choose other settings eg. when you prefer to save the test database or need remote access for the root user. Note: You don’t need remote access to use PHPMyAdmin remotely.

2

Change the MySQL password for other users

To change a normal user’s password, you need to type:

mysqladmin -u user-name -p password newpass

The variables in this example are:

  • Username: The username for which you want to change your password.
  • newpass: New password

The password is not a variable, so don’t change it. The command will ask for the old password.

3

Method 2 – Update or change the password

MySQL stores user names and passwords in the user table in the MySQL database. You can update your password directly using the following method to update or change your password:

1) Enter the MySQL server, type the following command at the shell prompt:

mysql -u root -p

2) Use the mysql database (type the command at the mysql> prompt):

mysql> use mysql;

3) Change the password for the user:

MySQL 5.7.5 and earlier

mysql> update user set password=PASSWORD("newpass") where User='ENTER-USER-NAME-HERE';

MySQL 5.7.6 and newer

mysql> SET PASSWORD FOR 'ENTER-USER-NAME-HERE'@'localhost' = PASSWORD("newpass");

4) Reload privileges:

mysql> flush privileges;
mysql> quit

This method you need to use when using PHP or Perl scripts.

Recover lost MySQL root password

You can recover the MySQL database server password in five easy steps:

Step # 1: Stop the MySQL server process.

Step # 2: Start the MySQL server / daemon (mysqld) process with the –skip-grant-tables option so that it won’t ask for a password.

Step # 3: Connect to the MySQL server as the root user.

Step # 4: Set a new root password.

Step # 5: Exit and restart the MySQL server.

Here are the commands that you must type for each step (entered as the root user):

Step # 1: Stop the MySQL service:

service mysql stop

Output:

Stopping MySQL database server: mysqld.

Step # 2: Start the MySQL server without a password:

mysqld_safe --skip-grant-tables &

Output:

[1] 5988
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6025]: started

Step # 3: Connect to the MySQL server using the MySQL client:

mysql -u root

Output:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 56299
Server version: 5.6.34-1 (Debian)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Step # 4: Set a new MySQL root user password:

MySQL 5.7.5 and earlier

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

MySQL 5.7.6 and newer

mysql> use mysql;
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD("newpass");
mysql> flush privileges;
mysql> quit

Step # 5: Stop the MySQL server:

service mysql stop

Output:

Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[6186]: ended

[1]+  Done                    mysqld_safe --skip-grant-tables

Or use this command to stop MySQL if the above command fails to stop it (which command works depends on the MySQL version):

killall mysqld

Output may differ based on Linux distribution. Don’t worry unless reporting an error. Start the MySQL server and test:

service mysql start
mysql -u root -p

Related posts

How to Install the SNMP Cacti Monitoring Tool on Debian 10

Linux

How to Activate Dark Mode on Ubuntu 20.04 LTS

Howto

How to Install and Configure Fail2ban on Ubuntu 20.04

Linux

How to compile the ONLYOFFICE Document Server from source code on Ubuntu

Linux

How to Make a Tar Gz File

Linux

How to Use Sudo Commands on Linux

Linux

How to Set Up an iSCSI Storage Server on Ubuntu 20.04 LTS

Linux

How to Install Puppets on CentOS 8 / RHEL 8

Linux

How to Install Ruby on Rails (RoR) on Debian 10

Linux