MySQL is a popular database management system that is widely used for various purposes such as web applications and content management systems. As an administrator, you need to have access to the root account to perform different operations like creating new users, managing databases, and resetting passwords. In case you have forgotten your MySQL root password, you may face difficulty accessing the database. However, you can reset the password and regain access. In this article, we will discuss how to reset MySQL root password on different platforms.
Resetting MySQL Root Password on Linux
If you are using a Linux-based operating system, you can reset the MySQL root password by following these steps:
Step 1: Stop MySQL Server
First, you need to stop the MySQL server to prevent any further changes to the database.
To stop the MySQL server, run the following command:
Sudo systemctl stop mysql
Step 2: Start MySQL Server in Safe Mode
Next, you need to start the MySQL server in safe mode. This will allow you to access the database without a password and reset the root password.
To start the MySQL server in safe mode, run the following command:
Sudo mysqld_safe –skip-grant-tables &
Step 3: Connect to MySQL Server
Now, you can connect to the MySQL server without a password by running the following command:
Mysql -u root
Step 4: Reset MySQL Root Password
Once you are connected to the MySQL server, you can reset the root password by running the following command:
UPDATE mysql.user SET authentication_string=PASSWORD(‘new_password’) WHERE User=’root’;
Note: Replace new_password with your desired password.
Step 5: Flush Privileges and Exit MySQL
After resetting the root password, you need to flush privileges and exit MySQL by running the following commands:
FLUSH PRIVILEGES;
Exit;
Step 6: Restart MySQL Server
Now, you can restart the MySQL server by running the following command:
Sudo systemctl start mysql
Resetting MySQL Root Password on Windows
If you are using a Windows operating system, you can reset the MySQL root password by following these steps:
Step 1: Stop MySQL Server
First, you need to stop the MySQL server to prevent any further changes to the database.
To stop the MySQL server, go to the Control Panel and open the Services app. Find the MySQL service and stop it.
Step 2: Create a New File
Next, you need to create a new file called reset_password.txt in the MySQL installation directory (e.g., C:\Program Files\MySQL\MySQL Server 8.0\bin).
Open the file and add the following code:
ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘new_password’;
Note: Replace new_password with your desired password.
Save and close the file.
Step 3: Start MySQL Server in Safe Mode
Now, you need to start the MySQL server in safe mode. To do this, open the Command Prompt as an administrator and run the following command:
“C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe” –defaults-file=”C:\ProgramData\MySQL\MySQL Server 8.0\my.ini” –init-file=”C:\Program Files\MySQL\MySQL Server 8.0\bin\reset_password.txt” –console
Note: Replace the path with your MySQL installation directory.
Step 4: Reset MySQL Root Password
Once the MySQL server is started in safe mode, it will execute the commands in the reset_password.txt file and reset the root password.
Step 5: Stop MySQL Server and Remove reset_password.txt
After resetting the root password, you need to stop the MySQL server and remove the reset_password.txt file.
To stop the MySQL server, press Ctrl+C in the Command Prompt.
To remove the reset_password.txt file, go to the MySQL installation directory and delete the file.
Step 6: Start MySQL Server
Now, you can start the MySQL server as usual and access the database with the new root password.
Resetting MySQL Root Password on macOS
If you are using a macOS operating system, you can reset the MySQL root password by following these steps:
Step 1: Stop MySQL Server
First, you need to stop the MySQL server to prevent any further changes to the database.
To stop the MySQL server, open the Terminal app and run the following command:
Sudo /usr/local/mysql/support-files/mysql.server stop
Step 2: Start MySQL Server in Safe Mode
Next, you need to start the MySQL server in safe mode. To do this, run the following command:
Sudo mysqld_safe –skip-grant-tables &
Step 3: Connect to MySQL Server
Now, you can connect to the MySQL server without a password by running the following command:
Mysql -u root
Step 4: Reset MySQL Root Password
Once you are connected to the MySQL server, you can reset the root password by running the following command:
UPDATE mysql.user SET authentication_string=PASSWORD(‘new_password’) WHERE User=’root’;
Note: Replace new_password with your desired password.
Step 5: Flush Privileges and Exit MySQL
After resetting the root password, you need to flush privileges and exit MySQL by running the following commands:
FLUSH PRIVILEGES;
Exit;
Step 6: Stop MySQL Server and Restart Normally
Now, you can stop the MySQL server in safe mode by pressing Ctrl+C in the Terminal.
To restart the MySQL server normally, run the following command:
Sudo /usr/local/mysql/support-files/mysql.server start
Resetting the MySQL root password is a simple process that can be done on different platforms. By following the steps outlined above, you can easily regain access to the database and perform necessary operations. Remember to choose a strong password and keep it safe to prevent unauthorized access.