CODEWITHSUNDEEP
mysqldatabase
Om3ga
Om3ga

Reputation: 32853

How to find out the MySQL root password

I cannot figure out my MySQL root password; how can I find this out? Is there any file where this password is stored?

I am following this link but I do not have directadmin directory in local.

Upvotes: 230

Views: 932275

Answers (21)

eeezyy
eeezyy

Reputation: 2179

Thanks to @thusharaK I could reset the root password without knowing the old password.

Steps for MySQL 5.7

On Ubuntu, I did the following:

sudo service mysql stop
sudo mysqld_safe --skip-grant-tables --skip-syslog --skip-networking

Then run MySQL in a new terminal:

mysql -u root

And run the following queries to change the password:

UPDATE mysql.user SET authentication_string=PASSWORD('password') WHERE User='root';
FLUSH PRIVILEGES;

In MySQL 5.7, the password field in the mysql.user table was removed; now the field name is authentication_string.

Quit the MySQL safe mode and start the MySQL service by:

mysqladmin shutdown
sudo service mysql start

Update for MySQL 8

For MySQL 8, the PASSWORD() function is deprecated, and you must use this updated query:

UPDATE user 
SET authentication_string=CONCAT('*', UPPER(SHA1(UNHEX(SHA1('NewPassword1234'))))), 
    plugin='mysql_native_password' 
WHERE User='root' AND Host='localhost';

Then flush privileges to apply the changes:

FLUSH PRIVILEGES;

Upvotes: 173

Light
Light

Reputation: 163

On terminal type the following

$ sudo mysql -u root -p

Enter password: *press enter without password*

mysql >

Upvotes: 12

tk_
tk_

Reputation: 17358

You can't view the hashed password; the only thing you can do is reset it!

Stop MySQL:

sudo service mysql stop

or

$ sudo /usr/local/mysql/support-files/mysql.server stop

Start it in safe mode:

$ sudo mysqld_safe --skip-grant-tables

(above line is the whole command)

This will be an ongoing command until the process is finished so open another shell/terminal window, log in without a password:

$ mysql -u root

mysql> UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';

MySQL 5.7 and over:

mysql> use mysql; 
mysql> update user set authentication_string=password('password') where user='root';

Exit the MySQL CLI:

mysql> exit

Restart MySQL in normal mode, first stopping the safe mode instance:

$ mysqladmin -u root -p shutdown   # (when prompted, enter the new password just set)
$ sudo service mysql start

or

$ sudo /usr/local/mysql/support-files/mysql.server start

Your new password is 'password'.

Upvotes: 81

Kit Ramos
Kit Ramos

Reputation: 1859

One thing that tripped me up on a new install of MySQL and wondering why I couldn't get the default password to work and why even the reset methods where not working. Well turns out that on Ubuntu 18 the most recent version of MySQL server does not use password auth at all for the root user by default. So this means it doesn't matter what you set it to, it won't let you use it. It's expecting you to login from a privileged socket.

mysql -u root -p

This will not work, even if you are using the correct password.

Instead, you need to use:

sudo mysql

that will work with out any password. then once you in you need type in

 ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Password you want to use';

Then log out and now it will accept your password.

Upvotes: 85

Mr. J
Mr. J

Reputation: 1839

For MySQL 5.5 on Windows 10

You can't find the password as it is hashed in the table, so resetting it is the only option.

The solution of importing the new password script by .txt file, as offered by Lokesh kumar Chippada, didn't work for me. I found that the command prompt just froze after initiating the import.

I added skip-grant-tables to the my.ini file as per the top the answer on this SO post by tonycoupland.

I was then able to login to mysql from the command line

$> mysql

and then in mysql

mysql> FLUSH PRIVILEGES;

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');

See 'B.3.3.2.3 Resetting the Root Password: Generic Instructions' on mysql dev page. I have now removed skip-grant-tables from the my.ini file, and I can login as a root user using the new password I created.

Upvotes: 0

user3136936
user3136936

Reputation: 311

Answers provided here did not seem to work for me, the trick turned out to be: ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';

(complete answer here: Change user password in MySQL 5.7 with “plugin: auth_socket”)

Upvotes: 1

lanzz
lanzz

Reputation: 43168

You cannot find it. It is stored in a database, which you need the root password to access, and even if you did get access somehow, it is hashed with a one-way hash. You can reset it: How to Reset the Root Password

Upvotes: 19

Ohad Cohen
Ohad Cohen

Reputation: 6144

Using Debian / Ubuntu mysql packages, you can login with user debian-sys-maint, which has all the expected privileges, the password is stored in the file /etc/mysql/debian.cnf

Upvotes: 2

user9869932
user9869932

Reputation: 7337

In case you already set a password in the past the mysql -uroot -p solution will not work,

In my case I used some of the answers above to solve this (Ubuntu 16). The result was:

sudo service mysql stop
sudo mysqld_safe --skip-grant-tables &

if you see this text in the screen: mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists. then do:

sudo mkdir -p /var/run/mysqld
sudo chown mysql:mysql /var/run/mysqld

sudo mysqld_safe --skip-grant-tables & # Look at the & at the end!

Enter other terminal to set your password like this:

sudo mysql -u root

mysql> use mysql;
mysql> SET PASSWORD FOR 'root'@'localhost'=PASSWORD('__NEW__PASSWORD__');
mysql> flush privileges;
mysql> quit;

then restart the service and login

# end mysqld_safe in the other terminal
sudo service mysql start
sudo mysql -h 127.0.0.1 -uroot -p

Upvotes: 0

Ali Havasi
Ali Havasi

Reputation: 681

I was stuck with this problem for a couple of minutes and the following was the only solution that actually worked:
https://phoenixnap.com/kb/access-denied-for-user-root-localhost

  1. sudo mysql
  2. ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'insert_password';
  3. mysql -u root -p

Upvotes: 0

Trevorbest
Trevorbest

Reputation: 59

I solved this a different way, this may be easier for some.

I did it this way because I tried starting in safe mode but cannot connect with the error: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

What I did was to connect normally as root:

$ sudo mysql -u root

Then I created a new super user:

mysql> grant all privileges on *.* to 'myuser'@'%' identified by 'mypassword' with grant option;
mysql> quit

Then log in as myuser

$ mysql -u myuser -p -h localhost

Trying to change the password gave me no errors but did nothing for me so I dropped and re-created the root user

mysql> drop user 'root'@'localhost;
mysql> mysql> grant all privileges on *.* to 'root'@'localhost' identified by 'mypassword' with grant option;

The root user is now working with the new password

Upvotes: 2

CODE-REaD
CODE-REaD

Reputation: 3028

System:

  • CentOS Linux 7
  • mysql Ver 14.14 Distrib 5.7.25

Procedure:

  1. Open two shell sessions, logging in to one as the Linux root user and the other as a nonroot user with access to the mysql command.

  2. In your root session, stop the normal mysqld listener and start a listener which bypasses password authentication (note: this is a significant security risk as anyone with access to the mysql command may access your databases without a password. You may want to close active shell sessions and/or disable shell access before doing this):

    # systemctl stop mysqld
    # /usr/sbin/mysqld --skip-grant-tables -u mysql &

  3. In your nonroot session, log in to mysql and set the mysql root password:

    $ mysql
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)

    mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');
    Query OK, 0 rows affected, 1 warning (0.01 sec)

    mysql> quit;

  4. In your root session, kill the passwordless instance of mysqld and restore the normal mysqld listener to service:

    # kill %1
    # systemctl start mysqld

  5. In your nonroot session, test the new root password you configured above:

    $ mysql -u root -p
    Enter password:
    Welcome to the MySQL monitor. Commands end with ; or \g.
    ...
    mysql>

Upvotes: 0

Deepak Chaudhry
Deepak Chaudhry

Reputation: 219

The default password which worked for me after immediate installation of mysql server is : mysql

Upvotes: 5

user3288185
user3288185

Reputation: 9

Go to phpMyAdmin > config.inc.php > $cfg['Servers'][$i]['password'] = '';

Upvotes: -7

Lokesh kumar Chippada
Lokesh kumar Chippada

Reputation: 1421

Follow these steps to reset password in Windows system

  1. Stop Mysql service from task manager

  2. Create a text file and paste the below statement

MySQL 5.7.5 and earlier:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('yournewpassword');


MySQL 5.7.6 and later:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'yournewpassword';

  1. Save as mysql-init.txt and place it in 'C' drive.

  2. Open command prompt and paste the following

C:\> mysqld --init-file=C:\\mysql-init.txt

Upvotes: 18

user889030
user889030

Reputation: 4754

you can view mysql root password , well i have tried it on mysql 5.5 so do not know about other new version well work or not 

nano ~/.my.cnf

Upvotes: 6

Vlad
Vlad

Reputation: 3571

MySQL 5.7 and above saves root in MySQL log file.

Please try this:

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

Upvotes: 50

nDCasT
nDCasT

Reputation: 71

In your "hostname".err file inside the data folder MySQL works on, try to look for a string that starts with:

"A temporary password is generated for roor@localhost "

you can use

less /mysql/data/dir/hostname.err

then slash command followed by the string you wish to look for 

/"A temporary password"

Then press n, to go to the Next result.

Upvotes: 3

Ohad Cohen
Ohad Cohen

Reputation: 6144

As addition to the other answers, in a cpanel installation, the mysql root password is stored in a file named /root/.my.cnf. (and the cpanel service resets it back on change, so the other answers here won't help)

Upvotes: 8

mszmurlo
mszmurlo

Reputation: 1339

Unless the package manager requests you to type the root password during installation, the default root password is the empty string. To connect to freshly installed server, type:

shell> mysql -u root --password=
mysql>

To change the password, get back the unix shell and type:

shell> mysqladmin -u root --password= password root

The new password is 'root'. Now connect to the server:

shell> mysql -u root --password=
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Oops, the password has changed. Use the new one, root:

shell> mysql -u root --password=root
...
blah, blah, blah : mysql welcome banner
...
mysql>

Bingo! New do something interesting

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

Maurycy

Upvotes: 8

JuanCamilo
JuanCamilo

Reputation: 31

The procedure changes depending the version of MySql. Follow the procedure exactly as described for your version:

  • HINTS - Read before the instructions page for your version of MySql*

  • In step 5: Instead of run CMD, create a shortcut on your desktop calling CDM.exe. Then right-click on the shortcut and select "Execute as Administrator".

  • In step 6: Skip the first proposed version of the command and execute the second one, the one with the --defaults-file parameter

  • Once you execute the command, if everything is ok, the CMD window remains open and the command of step 6 continues executing. Simply close the window (click 'x'), and then force close MySQl from the Task Manager.

  • Delete the file with the SQL commands, and start again MySQL. The password must be changed now.

5.0 http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

5.1 http://dev.mysql.com/doc/refman/5.1/en/resetting-permissions.html

... just change the version in the link (5.5, 5.6, 5.7)

Upvotes: 3

Related Questions