How do I find out my root MySQL password?

Question

I just installed MySQL on Ubuntu and the root user can't log in :)

How can I recover or find out my password? Using blank for password does not work.

score 144 · Accepted Answer · edited Jul 20 '18 at 16:02

144

You can reset the root password by running the server with --skip-grant-tables and logging in without a password by running the following as root (or with sudo):

# service mysql stop
# mysqld_safe --skip-grant-tables &
$ mysql -u root

mysql> use mysql;
mysql> update user set authentication_string=PASSWORD("YOUR-NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

# service mysql stop
# service mysql start
$ mysql -u root -p

Now you should be able to login as root with your new password.

It is also possible to find the query that reset the password in /home/$USER/.mysql_history or /root/.mysql_history of the user who reset the password, but the above will always work.

Note: prior to MySQL 5.7 the column was called password instead of authentication_string. Replace the line above with

mysql> update user set password=PASSWORD("YOUR-NEW-ROOT-PASSWORD") where User='root';

edited Jul 20 '18 at 16:02

TRiG

10,148
7
57
107

answered Apr 15 '11 at 23:08

Benjamin Manns

9,028
4
37
48

By the way, should I be using sudo with these commands like mysqld_safe ? – Genadinik Apr 15 '11 at 23:11
4

@Benjamin I did all the steps in your answer but I still get the Access denied for user 'root'@'localhost' - any ideas why? – Genadinik Apr 15 '11 at 23:24
Hmm, let me try it locally, and I'll update the answer with anything I find. – Benjamin Manns Apr 15 '11 at 23:29
This worked for me. Are you using the password you set in YOUR-NEW-ROOT-PASSWORD when you login after running the commands? – Benjamin Manns Apr 15 '11 at 23:39
@Banjamin yes I set the new root password to just "password" without the quotes so it was hard to get it wrong. Trying again lol. Can it be something with permissions? – Genadinik Apr 15 '11 at 23:42
Also, I am getting this message "Since the script you are attempting to invoke has been converted to an Upstart job, you may also use the stop(8) utility, e.g. stop mysql mysql stop/waiting" - any ideas why that is happening? – Genadinik Apr 15 '11 at 23:44
Also, MySQL is running on port 9681 for me which I think is not typical. Might that have anything to do with it? – Genadinik Apr 15 '11 at 23:52
Ah, that message is encouraging you to use `sudo service mysql start` and `sudo service mysql stop` instead of the older `/etc/init.d/mysqld [command]` syntax. – Benjamin Manns Apr 15 '11 at 23:52
Is your query `update user set password=PASSWORD("password") where User='root';` or `update user set password=PASSWORD(password) where User='root';`? You have to have the quotes around password or it will just rehash the old `password` column from the table. – Benjamin Manns Apr 15 '11 at 23:53
@Benjamin I used single quotes. Now when trying to repeat the process you outlined I get this error: "ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)" after the mysql -u root command, and the terminal just hangs. – Genadinik Apr 15 '11 at 23:59
In my case, I had to replace /etc/init.d/mysql [command] with sudo mysqld [command] – bbrame Aug 12 '13 at 15:17
@moderns make sure you add `-p` to your command, or the MySQL client will assume you don't want to enter a password. Full command: `mysql -u root -p` – Benjamin Manns Jul 15 '14 at 18:14
Note that you have to do all this logged in as root, or 'as administrator' in Windows. Otherwise (in Windows at least) it will appear to work but revert as soon as you disconnect from MySQL. – user207421 Mar 02 '15 at 07:37
4

For MySql 5.7: UPDATE mysql.user SET authentication_string = PASSWORD('MyNewPass'), password_expired = 'N' WHERE User = 'root' AND Host = 'localhost'; – TungstenX Aug 24 '16 at 09:17
22

After trying all of this many times, and the `dpkg` approach below, and getting super frustrated that nothing worked (btw, I'm using 16.04 with `mysql-sever-5.7`), I noticed that mysql would accept my new credentials if I used sudo to connect. i.e. `sudo mysql -u root -p` Without sudo I get `Access denied for user 'root'@'localhost'`. I've never had to use sudo before, and I don't see it mentioned here, but after a frustrating hour that seems to be the solution. – Matt Nov 12 '16 at 15:37
Thank you so much. This is the answer. – Bishal Paudel Jan 11 '17 at 23:43
thank you a lot - worked smoothnessly for me (MySQL 5.7.17 on Ubuntu 14.04) – RaffaeleT Apr 06 '17 at 14:02
1

@Matt same here. I've never had to use `sudo` before when using mysql and now I have to? I don't understand why I have to be the root linux user to access the root account in mysql. This sort of behaviour reminds me of postgresql a bit more. – JMac Jul 12 '17 at 00:42
3

For anyone only able to login as shell root, look at this https://askubuntu.com/questions/766334/cant-login-as-mysql-user-root-from-normal-user-account-in-ubuntu-16-04 – lucaswxp Mar 01 '18 at 14:38
1

You also need to change the "plugin" field to allow connection : `update user set plugin="mysql_native_password" where User="root";` – Goufalite Jan 31 '19 at 20:42

score 23 · Answer 2 · answered Jun 11 '18 at 11:24

23

sudo mysql -u root
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YOUR_PASSWORD_HERE';
FLUSH PRIVILEGES;

mysql -u root -p # and it works

answered Jun 11 '18 at 11:24

Alex Ivasyuv

8,585
17
72
90

2

This is the only solution which worked for me on ubuntu 16.04 LTS – Rajesh Jan 07 '19 at 19:03
Thanks. Your solution worked for me. Author of the post should have specified his MySQL version. Mine is `5.7.27`. – Askar Oct 28 '19 at 07:38

score 20 · Answer 3 · answered May 01 '13 at 05:51

20

I realize that this is an old thread, but I thought I'd update it with my results.

Alex, it sounds like you installed MySQL server via the meta-package 'mysql-server'. This installs the latest package by reference (in my case, mysql-server-5.5). I, like you, was not prompted for a MySQL password upon setup as I had expected. I suppose there are two answers:

Solution #1: install MySQL by it's full name:

$ sudo apt-get install mysql-server-5.5

Or

Solution #2: reconfigure the package...

$ sudo dpkg-reconfigure mysql-server-5.5

You must specific the full package name. Using the meta-package 'mysql-server' did not have the desired result for me. I hope this helps someone :)

Reference: https://help.ubuntu.com/12.04/serverguide/mysql.html

answered May 01 '13 at 05:51

theinfinitenigma

201
2
2

Thank you! I kept trying `sudo dpkg-reconfigure mysql-server`, but that didn't work. – Ross Rogers Aug 12 '14 at 23:25
1

This succeeded but didn't ask me for a root password for mysql in Ubuntu 18.04: `sudo dpkg-reconfigure mysql-server-5.7` – fIwJlxSzApHEZIl Sep 26 '18 at 16:58
yup, also running 5.7 and the reconfigure does NOT ask for a root password...? What gives, and what IS the default? – Arne May 02 '19 at 08:26

eebbesen · Answer 4 · 2014-09-24T22:52:19.007

10

MySQL 5.5 on Ubuntu 14.04 required slightly different commands as recommended here. In a nutshell:

sudo /etc/init.d/mysql stop
sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &
mysql -u root

And then from the MySQL prompt

FLUSH PRIVILEGES;
SET PASSWORD FOR root@'localhost' = PASSWORD('password');
UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';
FLUSH PRIVILEGES;

And the cited source offers an alternate method as well.

edited Sep 24 '14 at 22:52

answered Jul 09 '14 at 01:14

eebbesen

5,070
8
48
70

can you help and provide details on what following exactly does? `--skip-grant-tables --skip-networking &` It was really helpful for me to tackle client request but i'd like to learn about it so i can asses risk. Thanks! – Still Questioning Oct 17 '14 at 04:58
[`skip-grant-tables`](http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_skip-grant-tables) certainly requires caution since anyone with access to the server can access all of the schemas for the instance. [`skip-networking`](http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_skip-networking) is less of a problem I think but you should assess for yourself. If I'm trying to reset the root password without the current root password I consider myself in a security pickle already :). Not sure if there is a more 'secure' approach. – eebbesen Oct 17 '14 at 14:37
1

I got this ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) [1]+ Exit 1 sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking – mercury Apr 30 '17 at 22:07

score 4 · Answer 5 · edited Mar 02 '15 at 07:38

4

For RHEL-mysql 5.5:

/etc/init.d/mysql stop

/etc/init.d/mysql start --skip-grant-tables

 mysql> UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';
 mysql> FLUSH PRIVILEGES;
 mysql> exit;

mysql -uroot -pnewpwd

mysql>

edited Mar 02 '15 at 07:38

user207421

305,947
44
307
483

answered Mar 02 '15 at 06:52

Gurumurthy

41
1

2

You could be a bit more verbose in the answer. Please explain what the commands you suggest do. – Pablo Jomer Mar 02 '15 at 07:11

Irshad Khan · Answer 6 · 2019-11-26T07:09:43.070

Here is the best way to set your root password : Source Link Step 3 is working perfectly for me.

Commands for You

sudo mysql

SELECT user,authentication_string,plugin,host FROM mysql.user;

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

FLUSH PRIVILEGES;

SELECT user,authentication_string,plugin,host FROM mysql.user;

exit

Now you can use the Password for the root user is 'password' :

mysql -u root -p

CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON . TO 'sammy'@'localhost' WITH GRANT OPTION;

FLUSH PRIVILEGES;

exit

Test your MySQL Service and Version:

systemctl status mysql.service
sudo mysqladmin -p -u root version

score 2 · Answer 7 · answered Sep 27 '16 at 17:56

Hmm Mysql 5.7.13 to reset all I did was:

$ sudo service mysql stop To stop mysql

$ mysqld_safe --skip-grant-tables & Start mysql

$ mysql -u root

Just like the correct answer. Then all I did was do what @eebbesen did.

mysql> SET PASSWORD FOR root@'localhost' = PASSWORD('NEW-password-HERE');

Hope it helps anyone out there :)

score 1 · Answer 8 · answered Jun 28 '16 at 08:07

Under MYSQL 5.7, If you are using mysql for development purpose, just :

1.kill mysql :

$ sudo service mysql stop

2.start mysql under --skip-grant-tables mode:

$ sudo mysqld_safe --skip-grant-tables

and, further, you could try to change the user table under "skip-grant-table" mode, however I failed.

so, this is just a workaround.

score 1 · Answer 9 · answered Jan 16 '19 at 19:17

1

There is a simple solution.

MySql 5.7 comes with anonymous user so you need to reconfigure MySQL server.

You can do that with this command

try to find temp pass:

grep 'temporary password' /var/log/mysqld.log

then:

sudo mysql_secure_installation

On this link is more info about mysql 5.7

https://dev.mysql.com/doc/refman/5.7/en/linux-installation-yum-repo.html

answered Jan 16 '19 at 19:17

Stevan Tosic

6,561
10
55
110

I agree with you. – nouatzi Jan 25 '19 at 16:30

score 1 · Answer 10 · answered Aug 24 '19 at 08:17

1

It is actually very simple. You don't have to go through a lot of stuff. Just run the following command in terminal and follow on-screen instructions.

sudo mysql_secure_installation

answered Aug 24 '19 at 08:17

Santhosh Kalisamy

11
1

Bad answer. You don't answer the question. – mentallurg Aug 24 '19 at 08:22

Ronald Johnson · Answer 11 · 2020-12-19T23:24:42.320

I'm going to make a bit of an assumption here because I'm not sure. I don't think my MySQL (running on latest 20.04 upgraded) even has a root. I have tried setting one and I remember having problems. I suspect there is not a root user and it will automatically log you in as the MySQL root user if you're logged in as root.

Why do I think this? Because when I do MySQL -u root -p, it will accept any password and log me in as the MySQL root user when I am logged in as root.

I have confirmed that trying that on a non root user doesn't work.

I like this model.

EDIT 2020.12.19: It is no longer a mystery to me why if you are logged in as the root user you get logged into MySQL as the root user. It has to do with the authentication type. Later versions of MySQL are configured with the MySQL plugin 'auth_socket' (maybe you've noticed the /run/mysqld/mysqld.sock file on your system and wondered about it). The plugin uses the SO_PEERCRED option provided by the library auth_socket.so.

You can revert back to password authentication if desired simply by create/update of the password. Showing both ways and options below to make clear.

CREATE USER 'valerie'@'localhost' IDENTIFIED WITH auth_socket;
CREATE USER 'valerie'@'localhost' IDENTIFIED BY 'password';

score 0 · Answer 12 · answered Jan 02 '23 at 11:26

some time its become hard to find any i tried everthing i could find on the internet. but i could find the mysql server password. i have another way to find it without any command. first you need to install a app named: FileZilla Download from here open the app then: you need to add your host name it can be the ip provide by the hosting server. also add password and port and click on quick connect. paste /etc/mysql in remote directory and find the file named: debian.cnf right click and download it. after downlaoding search file in c drive open in notepad where you can see password and user name.

How do I find out my root MySQL password?

12 Answers12

Linked