I forgot the MySQL root password. How do I reset MySQL root password?
It is possible to reset the root password. Use the following procedure for resetting the password for any MySQL root accounts on Unix / Linux / BSD like operating systems.
You need to login as root user under UNIX / Linux.
Step # 1: Stop MySQL
Type the following command to stop mysql server under RHEL / Fedora / CentOS Linux:
# /etc/init.d/mysqld stop
Use the following command if you are using FreeBSD:
# /usr/local/etc/rc.d/mysql-server stop
Use the following command if you are using Debian / Ubuntu Linux:
# /etc/init.d/mysql stop
Alternatively, you can stop the MySQL server by sending a kill to the mysqld process, using the path name of the .pid file in the following bash specific command:
# kill $(cat /var/run/mysqld/mysqld.pid)
OR
# pgrep -u mysql mysqld
# kill PID
Or, simply use killall command under Linux:
# killall mysql
Step # 2: Create new mysql password sql script
Create a sql file called /root/mysql.reset.sql as follows (replace YOUR-NEW-MYSQL-PASSWORD with actual new password you would like to use):
# vi /root/mysql.reset.sql
Append the following code:
UPDATE mysql.user SET Password=PASSWORD('YOUR-NEW-MYSQL-PASSWORD') WHERE USER='root'; FLUSH PRIVILEGES;
Save and close the file.
Step # 3: Set new mysql root password
Type the following command:
# mysqld_safe --init-file=/root/mysql.reset.sql &
Sample output:
nohup: ignoring input and redirecting stderr to stdout Starting mysqld daemon with databases from /var/lib/mysql mysqld_safe[20970]: started
And you are done with resetting operation. Simply, stop server and start again:
# killall mysqld
# /etc/init.d/mysql start
🐧 17 comments so far... add one ↓
| Category | List of Unix and Linux commands |
|---|---|
| Disk space analyzers | df • ncdu • pydf |
| File Management | cat • cp • mkdir • tree |
| Firewall | Alpine Awall • CentOS 8 • OpenSUSE • RHEL 8 • Ubuntu 16.04 • Ubuntu 18.04 • Ubuntu 20.04 |
| Network Utilities | NetHogs • dig • host • ip • nmap |
| OpenVPN | CentOS 7 • CentOS 8 • Debian 10 • Debian 8/9 • Ubuntu 18.04 • Ubuntu 20.04 |
| Package Manager | apk • apt |
| Processes Management | bg • chroot • cron • disown • fg • jobs • killall • kill • pidof • pstree • pwdx • time |
| Searching | grep • whereis • which |
| User Information | groups • id • lastcomm • last • lid/libuser-lid • logname • members • users • whoami • who • w |
| WireGuard VPN | Alpine • CentOS 8 • Debian 10 • Firewall • Ubuntu 20.04 |
In RHEL / Fedora / CentOS Linux yun can type also service mysqld stop
Thank you ! Nice piece of info.
In fact, my colleague did the most stupid thing of his life yesterday by deleting all the users in the mysql table :(
I did get back everything by simply altering the commands in the above given example to insert a user called root, then granting permission to all databases, reset its password etc.
Or you could simply stop mysql then start it without privilege checking like this:
mysqld –skip-grant-tables
connect to it as root, run that update query, then kill the server and start it normally
mysqld –skip-grant-tables method is considered as insecure and in some case it may corrupt the data.
HTH
Excuse me for my bad English, I usually read it all time but rather speak or write it.
Thanks for share this method.
I install mySQL on my NSLU2 this weekend and with the Debian default install there are no root user, but the debconf ask it’s password twice at install time.
I use the skip-grant-tables method, but i was thinking on more secure ways to do it because withe the skip-grant-tables any user potentially can take the control of your databases.
thanks :)
for gui, you can use webmin, it so easy for change password mysql :)
Thanks, very nice instruction. A little typo at the end:
“# /etc/init.d/mysql start” should be “# /etc/init.d/mysqld start”
Thanks a lot for the tips that you have provided and I would like to know the same for debian and Ubuntu systems as in Ubuntu there is no root login as such right?
@V.Balaviswanathan,
MySQL has root user too. Do not confuse it with system root user (to become root user Ubuntu enter sudo -s).
@ Frank,
/etc/init.d/mysql or /etc/init.d/mysqld depends upon your distro.
Thanks a lot Vivek for the tip that you have provided :)
When there is a crash in connecting the MySQL Database do the following procedures
To Stop:
etc/init.d/mysql stop
To Start:
etc/init.d/mysql start
To Restart:
etc/init.d/mysql restart
To Reset The Password:
/etc/init.d/mysql reset-password
To Make Your Database To Get Connected When You Are In A Network:
gedit /etc/mysql/my.cnf
//search for bind address and then
change bind address 0.0.0.0
whoa that’s cool dude
I use Fedora 12 and It doesn’t work for me.
I had problems in Ubuntu, due to apparmor preventing mysql from reading sql files from the root directory.
Placing the init-file in /usr/share/mysql directory solved the problem. Or you can look at /etc/apparmor.d/usr.sbin.mysqld
I NEED THE CURRENT PASSWORD FOR ROOT IN XAMPP INORDER TO CHANGE MY PASSWORD.
THANK YOU!
Thanks for the information, but I can`t go to mysql. this is de error:
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
grettings