unable to reset root password of mysql
Solution 1
First please try using
mysql -u root -p
and enter your password (if you remember) at the prompt to login as the sql-root user (note the switch -p
is for password).
If you really have to reset your root password for mysql, here's an easy way - reconfigure the package with dpkg-reconfigure
.
Easy steps to reset mySQL root password:
-
Check the version of your
mysql-server
;apt-cache policy mysql-server
and see for the line which shows the installed version among other information. e.g. for my install it's:
Installed: 5.5.37-0ubuntu0.12.04.1
(From this I know that I have
mysql-server-5.5
installed in my system.) -
Start the reconfiguration with:
sudo dpkg-reconfigure mysql-server-*.*
where
mysql-server-*.*
should be replaced by the version that you have. (for me it'd bemysql-server-5.5
). This will stop the database daemon. A prompt will then appear where you'd have to enter your new password and confirm the reconfiguration.The daemon will be automatically started after the reconfig completes.
-
You can then log in with:
mysql -u root -p
and start your database admin tasks.
References:
https://help.ubuntu.com/community/MysqlPasswordReset [Which would soon be cleaned up as indicated in the page.]
Ubuntu Server Guide related to your specific version.
Solution 2
Reference taken from this blog:
Step 1: Stop MySQL Service.
sudo service mysql stop
Step 2: Kill all running mysqld.
sudo killall -9 mysqld
Step 3: Starting mysqld in Safe mode.
sudo mysqld_safe --skip-grant-tables --skip-networking &
Step 4: Start mysql client
mysql -u root
Step 5: After successful login, please execute this command to change any password.
FLUSH PRIVILEGES;
Step 6: You can update mysql root password .
UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';
for mysql > 5.7 use this instead of above:
UPDATE mysql.user SET authentication_string=PASSWORD('newpwd') WHERE User='root';
Step 7: Please execute this command.
FLUSH PRIVILEGES;
Step 8: Exit mysql console
exit
Step 9: Kill mysqld_safe and start mysql
sudo killall mysqld_safe && sudo service mysql start
Solution 3
Under Ubuntu 16.04 and mysql-server-5.7
, the correct answer is the last comment of olafure, dpkg-reconfigure mysql-server-5.7
no longer works.
sudo service mysql stop
sudo killall mysqld
sudo mysqld_safe --skip-grant-tables --skip-networking &
mysql -u root
Now in mysql console >mysql
USE mysql;
UPDATE user SET authentication_string=PASSWORD('newpass') WHERE user='root';
FLUSH PRIVILEGES;
\q
Restart the good mysql
process
sudo killall mysqld
sudo service mysql start
Check your new password
mysql -u root -p
Enter password: newpass
mysql>
Solution 4
Starting with MySQL 5.7, during initial installation, if you leave password empty, then for that user, authentication will be based on auth_socket
plugin.
The correct way to change password will be:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';
https://www.percona.com/blog/2016/03/16/change-user-password-in-mysql-5-7-with-plugin-auth_socket/
Solution 5
Nothing from the rest of the answers seemed to work for me. This is my solution:
sudo service mysql stop
sudo mysqld_safe &
sudo mysql -u root
In there:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
FLUSH PRIVILEGES;
QUIT;
Then:
sudo killall mysqld
sudo service mysql start
sudo mysql -u root -p
Related videos on Youtube
![stdcerr](https://i.stack.imgur.com/Y3RU6.png?s=256&g=1)
stdcerr
Coding mostly on embedded systems (24/7/365 uptime) to make a living at daytime, coding on a variety of systems (x86, arm) for fun at nighttime, preferences: Linux, C & C++. Always interested in learning other ways/better ways to solve problems!
Updated on September 18, 2022Comments
-
stdcerr almost 2 years
I need to reset the root password of my local mysql installation but it woudln't let me. I've tried this:
$ sudo /etc/init.d/mysql stop * Stopping MySQL database server mysqld [ OK ] [1]- Exit 1 sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking [2]+ Exit 1 sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking reg@regDesktopHome:~$ sudo mysqld --skip-grant-tables & [1] 13651 reg@regDesktopHome:~$ 140627 19:02:02 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead. reg@regDesktopHome:~$ mysql -u root mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) reg@regDesktopHome:~$ sudo mysql -u root mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) [1]+ Exit 1 sudo mysqld --skip-grant-tables
How can I get the password reset? edit 1 I got this:
$ ps ax| grep mysql 16515 ? Ssl 0:00 /usr/sbin/mysqld 16551 pts/23 S+ 0:00 grep --color=auto mysql reg@regDesktopHome:~/semios/v3upgrade$ sudo kill -9 16515 reg@regDesktopHome:~/semios/v3upgrade$ ps ax| grep mysql 16678 ? Ssl 0:00 /usr/sbin/mysqld 16715 pts/23 S+ 0:00 grep --color=auto mysql reg@regDesktopHome:~/semios/v3upgrade$ mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
looks like a process is automatically restarting mysql as soon as I kill it...
-
bperkins24 over 9 yearsCan confirm that
dpkg-reconfigure mariadb-server-10.1
worked for MariaDB when all other solutions didn't. Thank you for this. -
Gadgetroid over 9 yearsYes this worked - better to understand what is going on - Thanks rusty
-
Meetai.com over 9 yearsDidn't work for me on 14.04LTS. What worked was:
dpkg-reconfigure mysql-server-5.5
-
Unirgy over 8 years
dpkg-reconfigure
didn't prompt for password for me.. is there a way to force it? -
knocte almost 8 yearsshould be
sudo service mysql stop
, notsudo stop mysql
-
knocte almost 8 yearsalso, at the end it's missing the steps
sudo killall mysqld_safe && sudo service mysql start
and then try to log in again -
Tarek Fadel almost 8 yearsFor MYSQL 5.7 the change password syntax has changed to
ALTER USER root IDENTIFIED BY 'NEW_PASSWORD_HERE'
-
Anthony Scaife almost 8 yearsDid not work for me. I used the command as above, and got "Checking if update is needed. This installation of MySQL is already upgraded to 5.7.13, use --force if you still need to run mysql_upgrade". Using --force gave the same response.
-
olafure over 7 yearsNOTE! There is a new beast in MySQL 5.7 called auth_socket. If you install mysql without a root password, the beast will attack you and you'll loose 50 hitpoints. In step 6 above you need to
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'NEWPASS';
. You can read about it here. Theoretically (I didn't try), you should be able to log in to mysql without a password if you "sudo su -" to become root. -
olafure over 7 yearsAnd in case
ALTER USER ...
doesn't work, useUPDATE mysql.user SET authentication_string = PASSWORD('NEWPASS'), plugin = 'mysql_native_password' WHERE User = 'root' AND Host = 'localhost';
-
dpi over 7 yearsApparently this does not work anymore.
-
Paulo Henrique over 7 years@AnthonyScaife did you get it? I have this same output.
-
Anthony Scaife over 7 years@Paulo Henrique No. Given the situation, we decided the best use of our time was a full server reinstall.
-
Chester about 7 yearsAt
mysqld_safe
execution, I get the error:Directory '/var/run/mysqld' for UNIX socket file don't exists.
. So I ransudo mkdir /var/run/mysqld; sudo chown mysql /var/run/mysqld
and the rest of the above answer works. Is this a bug on Ubuntu 16? I've never experienced all these issues updating passwords before. -
cherouvim about 7 yearsThis was my exact case (empty password during installation in mysql 5.7) and this was the only solution for my case.
-
derHugo about 7 yearsThe rest doesn't work for me neither ... nor does your solution unfortunately: Still get this Error
2017-05-30T06:33:22.291126Z mysqld_safe Logging to syslog. 2017-05-30T06:33:22.294375Z mysqld_safe Logging to '/var/log/mysql/error.log'. 2017-05-30T06:33:22.296874Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.
-
derHugo about 7 years@Chester thank you very much!! This was the only way that finally worked now as all other solutions throw an error. (Ubuntu-Server 16.04 here) This should be added to the answer
-
manny almost 7 years@precise how do i can reconfigure with --force, because if i run your command to reconfigure, i am getting always -- This installation of MySQL is already upgraded to 5.7.19, use --force if you still need to run mysql_upgrade
-
user2513149 almost 6 yearsI get the message:
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
-
Barun almost 6 years@user2513149 Please restart your MySQL server without
--skip-grant-tables
option and see this solution works. -
user2513149 almost 6 years@Barun, no, without
--skip-grant-tables
option I can't even log into MySQL as root. It saysERROR 1698 (28000): Access denied for user 'root'@'localhost'
-
user2513149 almost 6 yearsSolved! I had to login to regular MySQL console (not
mysqld_safe
) as root without password. I have Ubuntu 18.04 and mysql-server-5.7. This answer helped me askubuntu.com/a/767252/585252 (see the bottom part) -
twowheeler over 4 yearsThis is the only advice that works for me out of the six or so suggestions I tried, but I added @Chester 's bit above to avoid the "UNIX socket file don't exists" error:
sudo mkdir /var/run/mysqld; sudo chown mysql /var/run/mysqld
This is on mysql 5.7 and ubuntu 18.04.