1. How To Reset Lost The Root Password In The Mysql Reference Manual Mac Pro
2. How To Reset Lost The Root Password In The Mysql Reference Manual Machine
3. How To Reset Lost The Root Password In The Mysql Reference Manual Mac Book

Apr 26, 2019  If you never set, forgot, or need to change your MySQL password, you're in luck. This article walks you through these steps, so you'll never be at a loss for that database root user password.

Chances are, you have MySQL running somewhere in your data center. If that's the case, there might be a time when you need to set or change the root user password. This can happen when you've forgotten the password or when you're looking to up your security game (remembering you set the original MySQL password to something far too simple).

How To Reset Lost The Root Password In The Mysql Reference Manual Mac Pro

As you might expect, the process is handled entirely through the command line and works with with either MySQL or MariaDB installations. The Linux distribution being used doesn't matter (as long as you have admin access, either by way of su or sudo).

SEE: 20 quick tips to make Linux networking easier (free PDF) (TechRepublic)

A word of warning: Given the current state of attacks, across the landscape of IT, I highly recommend you using seriously strong passwords for your databases. Use a random password generator and then store that in a password manager (instead of using an easily memorized password). Be safer than safe.

With that said, let's get to work.

Setting the password for the first time

Do note, throughout this article, I will refer to MySQL, with the idea that everything will work for both MySQL and MariaDB.

Typically, during the installation of MySQL and MariaDB, you are asked to set an initial password. If, for whatever reason that didn't happen, you will need to set a password for the first time. To do that, open up a terminal window and issue the following command:

Where NEWPASSWORD is the password to be used. Now, when you log into MySQL, with the command mysql -u root -p, you will be prompted to enter the newly configured password.

An alternative method for setting the root password for the first time, one that also adds a bit of security to your MySQL database, is to use the mysql_secure_connection command. Not only will this command set the root user password, but it will allow you to remove anonymous users, disallow remote root login, and remove the test database. To use this command, simply type:

Answer the presented questions and your password will be set (as well as your database being a bit more secure).

SEE: Password management policy (Tech Pro Research)

Changing the MySQL root user password

To reset the password for MySQL you first must create a new file with the following contents:

Where PASSWORD is the new password to be used. Save that file as ~/mysql-pwd.

Next, stop the MySQL daemon with the command:

With the daemon stopped, issue the command:

Once your command prompt is returned, restart the MySQL daemon with the command:

You should now be able to log into the MySQL command prompt with the new admin password like so:

When prompted, type the admin password and you're good to go.

Recover your MySQL password

What if you've forgotten your MySQL root user password? This could be quite the predicament ... had the developers not thought of that eventuality. In order to recover the password, you simply have to follow these steps:

1. Stop the MySQL server process with the command sudo service mysql stop
2. Start the MySQL server with the command sudo mysqld_safe —skip-grant-tables —skip-networking &
3. Connect to the MySQL server as the root user with the command mysql -u root

At this point, you need to issue the following MySQL commands to reset the root password:

Where NEWPASSWORD is the new password to be used.

Restart the MySQL daemon with the command sudo service mysql restart. You should now be able to log into MySQL with the new password.

And that's it. You can now set, reset, and recover your MySQL password.

SEE: The cloud v. data center decision (ZDNet/TechRepublic special report) | Download the free PDF version (TechRepublic)

A second warning

Just to be certain, I wanted to remind you how important it is to set a very difficult password for the MySQL root user. Those databases hold sensitive information, so the last thing you need is to make use of a weak password that can be easily cracked. Use a long password that you cannot memorize and store it in a password vault. You'll be glad you did.

Editor's note: This article was first published in October 2017. The most recent update was published in April 2019.

Open Source Weekly Newsletter

You don't want to miss our tips, tutorials, and commentary on the Linux OS and open source applications. Delivered Tuesdays

Sign up today Sign up today

Also see

• How to become a developer: A cheat sheet (TechRepublic)
• Securing Linux policy (Tech Pro Research)
• The battle between real open source vs. faux open source heats up (ZDNet)
• Best cloud services for small businesses (CNET)
• Microsoft Office vs Google Docs Suite vs LibreOffice (Download.com)
• Linux, Android, and more open source tech: Must-read coverage (TechRepublic on Flipboard)

On this page

1. mysqladmin Command To Change Root Password

This tutorial explains how you can set, change and reset (if you've forgotten the password) MySQL or MariaDB root passwords. Time and again I see problems like mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: YES)'. So I thought it's time to remind you how to solve MySQL related password problems. If you are just looking for a quick fix how to reset a MySQL root password you can find that at the bottom of this tutorial. This tutorial is compatible with all major Linux Distributions incl. CentOS, Debian, Fedora, and Ubuntu.

mysqladmin Command To Change Root Password

Method 1 - Set up a root password for the first time

If you have never set a root password for MySQL, the server does not require a password at all for connecting as root. To set up a root password for the first time, use the mysqladmin command at the shell prompt as follows:

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

If you get...

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

The word 'password' in the above example is part of the command, do not replace that with your password. The word 'newpass' is the new password.

An alternative to using the mysqladmin command when setting the MySQL or MariaDB root password 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 do some other security settings like disabling the test database.

Here is how to use that command:

Answer the questions as shown below:

The above answers are recommendations, you are free to choose other settings e.g. when you prefer to keep the test databases or need remote access for the root user. Note: you do not need remote access to use PHPMyAdmin remotely.

Change MySQL password for other users

To change a normal user password you need to type:

The variables in this example are:

• user-name: The username of the user that you want to change the password for.
• newpass: The new password

The word 'password' is not a variable, so do not replace it. The command will ask for the old password.

Method 2 - Update or change password

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

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

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

3) Change password for a user:

MySQL 5.7.5 and earlier

MySQL 5.7.6 and newer

How To Reset Lost The Root Password In The Mysql Reference Manual Machine

4) Reload privileges:

This method you need to use while using PHP or Perl scripting.

Recover MySQL root password

How To Reset Lost The Root Password In The Mysql Reference Manual Mac Book

You can recover a MySQL database server password with the following five easy steps:

Step # 1: Stop the MySQL server process.

Step # 2: Start the MySQL (mysqld) server/daemon process with the --skip-grant-tables option so that it will not prompt 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 you need to type for each step (log in as the root user):

Step # 1: Stop the MySQL service:

Output:

Step # 2: Start the MySQL server w/o password:

Output:

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

Output:

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

MySQL 5.7.5 and earlier

MySQL 5.7.6 and newer

Step # 5: Stop the MySQL server:

Output:

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

The output might differ based on the Linux distribution. Don't worry unless it reports an error. Start the MySQL server and test it: