How to Reset MySQL Root Password

Starting and Stopping MySQL on Windows Using the Services Tool

You may be a little confused when you're required to reset the root password for your MySQL installation. If you come from the Windows world, you may not be familiar with the term "root" at all. For Unix users on macOS and Linux who are in the know, you might be concerned this means changing your superuser password. The first step is understanding what, precisely, this MySQL password is.

What Is the MySQL Root Password?

To answer this, let's first define what the system root password is. The root account is the equivalent of an Administrator in Windows, or someone who can change any part of the system. On Unix-like systems, root can indeed do anything, but it's different in that it's created automatically when the system is installed, and can't normally be deleted. Compare this to Windows, where any user can be deemed an Administrator.

In both macOS and many Linux distributions, this user is disabled by default. Instead, you'll normally use the su/sudo commands. These execute your command as if you were a superuser. It's the same as using Run as Administrator to run Windows apps.

Now, consider that MySQL is a multi-user subsystem. You install it on your computer or server and assign it its own users, then assign those users their own permissions. These might be completely different from the users on the system in general, especially on *nix systems like Linux. So just like the overall system has a root user, the MySQL subsystem needs a superuser who can fix it when things go wrong. This is the MySQL root user. And sometimes you may need to change this user's password.

When Is the MySQL Root User's Password Created?

Like the system root user, the MySQL root user is a default account, and is created when you install MySQL. You'll create the initial password for the MySQL root user at this time. When you install the MySQL server package, the installer will ask you for a password, then set it for the root user.

Creating an Init File to Reset MySQL Password

Changing a root user's password in Unix-like systems is generally a serious task. You can't make it easy to do, or else anyone could do it and take control of your system. Normal password-reset tools will work just fine, if you have the current password, but let's assume you don't and you need to reset the password to something new.

The trick we'll use to reset the password is using an init file when we start the MySQL server. This is a file that can contain commands that are executed along with the normal configurations. In this case, one of those commands is going to change the password for the root user.

Regardless if your OS, you can prepare for this by creating a one-line text file containing the following:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'thenewpassword';

You can look at this command and understand what it does pretty easily. It's altering a user called "root" on "localhost," or the machine you're working on. The second half sets the password for this user to "thenewpassword."

When you start the MySQL server and feed it this init file, it will execute the commands inside, and the effect will be updating the root user's password as the server starts up.

Reset MySQL Password by Starting MySQL With the Init File

The basic sequence we'll do is to stop the server if it's running, start it again manually using the init file, stop it again, and finally restart it in the normal fashion. The specific steps are as follows:

  1. Stop MySQL if it's running:

    Starting and Stopping MySQL on Windows Using the Services Tool
    • On Windows, from the Start Menu, open Control Panel, and launch Administrative Tools. This is in the System and Security category if that's how you have Control Panel arranged. Select Services (learn about Windows Services here), then look for MySQL in the list. If it shows as running, select Stop the service to shut it down.
    • On macOS, go to System Preferences > MySQL. The Preferences Pane will appear and show you if the server is running, as well as give you the option to stop it.
    • On Linux, you can issue "sudo service mysqld stop" to shut the server down.
  2. Next, start the server again and point to the init file you created. The command is:

    mysqld --init-file=/path/to/my/init/file

    If you're running Windows, make sure you run the above command from the executable's directory, usually C:\ProgramData\MySQL\MySQL Server 8.0\bin. Also, if you used the MySQL Windows Installer package (.msi), you'll also need to add the "defaults file" to load all other configs MySQL needs: mysqld --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" --init-file=C:\Users\aaron\Desktop\name-of-the-file).

  3. Finally, restart the MySQL server:

    • On Windows, go to the Services screen, then select Start the service for MySQL.
    • On macOS, start the server back up again from MySQL's Preferences screen.
    • On Linux, run sudo service start mysqld.

    While this isn't strictly necessary, it's a good idea to make sure it starts with all the normal configurations.

  4. At this point, try logging into the server with the command line client using the updated password.