[mysql] 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.

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

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

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

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

one thing that tripped me up on a new install of mySQL and wonder 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. so

mysql -u root -p

will not work at all, even if you are using the correct password!!! it will deny access no matter what you put in.

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 the bloody thing will finally accept your password

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

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

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

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>

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.

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

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

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)

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: https://www.percona.com/blog/2016/03/16/change-user-password-in-mysql-5-7-with-plugin-auth_socket/)

This worked for me:

On terminal type the following

$ sudo mysql -u root -p

Enter password://just press enter

mysql>

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: http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

MySQL 5.7 and above saves root in MySQL log file.

Please try this:

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