You may be a bit confused when you are prompted 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 in the know, you may be concerned that this means changing your superuser password. The first step is to understand what, precisely, this MySQL password is.
What is 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 do anything, but it is different in that it is created automatically when the system is installed, and normally cannot be removed. Compare this to Windows, where any user can be considered an Administrator.
On both macOS and many Linux distributions, this user is disabled by default. Instead, you will normally use the su/sudo commands. These execute the command as if it were a superuser. It is the same as using Run as administrator to run Windows applications.
Now consider that MySQL is a multi-user subsystem. You install it on your computer or server and assign your own users to it, then assign those users their own permissions. These can be completely different from users on the system in general, especially on *nix systems like Linux. Just as the system in general has a root user, the MySQL subsystem needs a root user who can fix it when things go wrong. This is the MySQL root user. And sometimes it may be necessary to change the password of this user.
When is the MySQL root user password created?
Like the system root user, the MySQL root user is a default account, and is created when MySQL is installed. You will create the initial password for the MySQL root user at this point. When you install the MySQL server package, the installer will ask for a password and then set it to the root user.
Creating an Init file to reset the MySQL password
Changing the password of a root user on Unix-like systems is generally a serious task. You can’t make it easy, or anyone could and take control of your system. Normal password reset tools will work fine if you have the current password, but let’s say you don’t and you need to reset the password to something new.
The trick we will use to reset the password is to use a startup file when we start the MySQL server. This is a file that can contain commands that are run alongside normal configurations. In this case, one of those commands is going to change the password for the root user.
Regardless of your operating system, you can prepare for this by creating a one-line text file containing the following:
Pre data-type=”code”>ALTER USER @’root’@’localhost’ IDENTIFIED BY You can see this command and understand what it does very easily. It is altering a user called “root” on “localhost”, or the machine you are working on. The second half sets the password for this user to “thenewpassword”.
Starting the MySQL server and feeding it this init file will execute the commands inside, and the effect will be to update the root user’s password as the server starts.
Reset MySQL password by starting MySQL with Init file
The basic sequence we’ll do is stop the server if it’s running, start it again manually using the init file, stop it again, and finally restart it the normal way. The specific steps are as follows:
- Stop MySQL if it is running:
- In Windows, from the Start menu, open Control Panel , and run Administrative Tools . This is in the System and Security category if that is how you have your Control Panel organized. 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 Panel will appear and show you if the server is running, as well as giving you the option to stop it.
- On Linux, you can issue ” sudo service mysqld stop ” to shut down the server.
- Then start the server again and point to the startup 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 directory of the executable, typically C:ProgramDataMySQLMySQLMySQL Server 8.0bin . Also, if you used the MySQL Windows Installer (.msi) package, you will also need to add the “defaults file” to load all the other settings MySQL needs: mysqld –defaults-file=”C:ProgramDataMySQLMySQL Server 8.0my.ini” – init-file=C:Usersers{aaron}Desktopame-of-the-file ).
- Finally, restart the MySQL server:
- On Windows, go to the Services screen and select Start the service for MySQL.
- On macOS, start the server backup again from the MySQL Preferences screen.
- On Linux, run sudo service start mysqld .
Although this is not strictly necessary, it is a good idea to ensure that you start with all the normal settings.
- At this point try to login to the server with the command line client using the updated password.