How do I find out my root MySQL password?

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.

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

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

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

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.

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>  

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

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

Commands for You

> 1. sudo mysql > 2. SELECT user,authentication_string,plugin,host FROM mysql.user; > 3. ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; > 4. FLUSH PRIVILEGES; > 5. SELECT user,authentication_string,plugin,host FROM mysql.user; > 6. exit

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

> 1. mysql -u root -p > 2. CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password'; > 3. GRANT ALL PRIVILEGES ON . TO 'sammy'@'localhost' WITH GRANT OPTION; > 4. FLUSH PRIVILEGES; > 5. exit

Test your MySQL Service and Version:

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

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.

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

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

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