When installing MySQL, you may noticed that it does not ask for a password. This become troublesome when you want to connect your application to MySQL database. In this article, I will show you on how to find MySQL default password.
MySQL Default Password
Well, straight forward, by default the username is root and there is no password setup. You may need to reset the root password.
Also, in case you have accidently put a password during installation process and can’t recall the password, you need to reset the password.
There is no way to view the password as it’s already written as hashed.
How To Reset MySQL Default Password
Windows OS
1. Ensure that your logged on account has administrator rights.
2. On the windows search box, search for services.msc and click to open.
3. Scroll down to all services with its status. Find MySQL services, right-click on it and click stop services.
4. Create a text file which contains the SQL statement in a single line as below:
ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘MyNewPass’;
Change MyNewPass to your new desired password.
5. Save it to a text file. For example, save it as C:\new-password.txt.
6. On the windows search box, search for cmd and click Run as administrator.
7. Start the MySQL with init_file system variable set to text file name created as below:
C:\> cd “C:\Program Files\MySQL\MySQL Server 5.7\bin” C:\> mysqld –init-file=C:\\mysql-init.txt
You may replace your MySQL installation location after cd command.
Linux
1. Open terminal.
2. Stop MySQL server.
sudo service mysql stop
Or
sudo /usr/local/mysql/support-files/mysql.server stop
3. Start MySQL in safe mode.
sudo mysqld_safe –skip-grant-tables
4. Open another terminal and login as root by run below command.
mysql -u root
3. Once MySQL is logged in via terminal, run below queries.
UPDATE mysql.user SET authentication_string=PASSWORD(‘password’) WHERE User=’root’; FLUSH PRIVILEGES;
which be looks like:
mysql>UPDATE mysql.user SET authentication_string=PASSWORD(‘password’) WHERE User=’root’; FLUSH PRIVILEGES;
(Note: In MySQL 5.7, the password field in mysql.user table field was removed, now the field name is ‘authentication_string’.)
If you are using MySQL 5.7 and above you need to run command as below:
mysql> use mysql; mysql>update user set authentication_string=password(‘password’) where user=’root’; FLUSH PRIVILEGES;
4. Now, you can exit MySQL safe mode.
mysqladmin shutdown
If you received error ‘access denied’ you can run below command with the new password:
mysqladmin -u root -p shutdown
5. Start MySQL service again by run below command.
sudo service mysql start
What If Still Failed To Reset MySQL Default Password?
If by using ALTER USER still not able to reset password, you may try to modify the user table directly by repeating the steps above and run below query:
UPDATE mysql.user SET authentication_string = PASSWORD(‘MyNewPass’), password_expired = ‘N’ WHERE User = ‘root’ AND Host = ‘localhost’; FLUSH PRIVILEGES;
Thanks for reading this article. I hope you find it helpful.
