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.

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

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 mysql.user table field was removed, now the field name is 'authentication_string'.

Quit the mysql safe mode and start mysql service by:

mysqladmin shutdown
sudo service mysql start

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'; 

Start MySQL:

sudo mysql start

or

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

Your new password is 'password'.

MySQL 5.7 and above saves root in MySQL log file.

Please try this:

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

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.

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

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';

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

4. Open command prompt and paste the following

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

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

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)

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

This worked for me:

On terminal type the following

$ sudo mysql -u root -p

Enter password://just press enter

mysql>

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

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)

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.

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

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”)

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

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>

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

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

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.

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