What is the default root pasword for MySQL 5.7 [closed]

Question

Cannot login to MySQL database after fresh install with root ID and empty/no password like other older MySQL versions do.

Pegg

root no longer has one because it does not use one. By default the auth method is auth socket. use sudo to access with root access, change the auth method to password and set a password if you need a root lmysql oogin without root system access. – Spirt

@Spirt would you mind expanding this comment into an answer with more detailed steps? I think this is the solution I want to use but don't know how to change the auth method. (sudo mysql -u root does work for me - I want to change it so I can just do mysql -u root with no password) – Marla

Sorry You probably already figured this out but I found out my post only applies to debian distros. If its still an issue (doubt it) I can provide more detail. – Spirt

Answer 1

After you installed MySQL-community-server 5.7 from fresh on linux, you will need to find the temporary password from /var/log/mysqld.log to login as root.

1. grep 'temporary password' /var/log/mysqld.log
2. Run mysql_secure_installation to change new password

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

Pegg

Thank you! Kind of crazy to find this information here instead of the MySQL 5.7 doc from Oracle. – Anisotropic

This saves my life... been searching for solution everywhere and this solves all my problem. Even the document from mysql website didn't mention about this. This is crazy man. Thanks! – Thomas

Where would you look on a Windows machine? – Oneeyed

@rotaercz try following this guide? dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html – Pegg

/var/log/mysqld.log does not exist for me (mariadb on mint) – Guinevere

thank you ! solved my problem here!! never knew about this "temporary password"!! – Refractive

unbelievable I know I shouldn't add this kind of "me too!" comments but holy crap the official documentation would not even come up first for this information – Wulfe

It's not there. – Araucania

In MySQL 5.6, you may find the password in a hidden file in root's home directory. – Antisana

For those who can't find the /var/log/mysqld.log, just run sudo mysql_secure_installation. Remmember to run that command with sudo or you will get the "Access denied" error. – Journalize

sudo mysql_secure_installation will do ABSOLUTELY NOTHING if the MySQL root account makes use of the "auth_socket" plugin. I elaborate in my answer below – Leyla

Answer 2

There's so many answers out there saying to reinstall mysql or use some combo of

mysqld_safe --skip-grant-tables

and / or

UPDATE mysql.user SET Password=PASSWORD('password')

and / or something else ...

... None of it was working for me

---

Here's what worked for me, on Ubuntu 18.04, from the top

With special credit to this answer for digging me out of the frustration on this ...

$ sudo apt install mysql-server
$ sudo cat /etc/mysql/debian.cnf

Note the lines which read:

user     = debian-sys-maint
password = blahblahblah

Then:

$ mysql -u debian-sys-maint -p
Enter password: // type 'blahblahblah', ie. password from debian.cnf

mysql> USE mysql
mysql> SELECT User, Host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| User             | Host      | plugin                |
+------------------+-----------+-----------------------+
| root             | localhost | auth_socket           |
| mysql.session    | localhost | mysql_native_password |
| mysql.sys        | localhost | mysql_native_password |
| debian-sys-maint | localhost | mysql_native_password |
+------------------+-----------+-----------------------+
4 rows in set (0.00 sec)

mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> COMMIT;  // When you don't have auto-commit switched on

Either:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

Or:

// For MySQL 5.7+
UPDATE mysql.user SET authentication_string=PASSWORD('new_password') where user='root';

Then:

mysql> FLUSH PRIVILEGES;
mysql> COMMIT;  // When you don't have auto-commit switched on
mysql> EXIT

$ sudo service mysql restart
$ mysql -u root -p
Enter password: // Yay! 'new_password' now works!

Wiener

I've followed these steps and it doesn't work for me. Everything looks good, but as soon as I restart MySQL, the plugin value gets reset to auth_socket. Any idea what's missing? – Bassist

@MattRaible I don't know to be honest. Perhaps check the answer I credited / linked above? – Wiener

Didn't work for me initially. Then I tried issuing a COMMIT before EXIT and it worked! – Ordonnance

@Ordonnance Good to know. The above method relies on auto-commit being configured by default. – Wiener

Worked for me but instead of using: mysql -u debian-sys-maint -p , I had to use: sudo mysql -u root . When I used the first one the changes were not being saved even with COMMIT – Coryden

Need to do a commit; before FLUSH PRIVILEGES;. – Nadianadine

After adding a COMMIT; after FLUSH PRIVILEGES;, this did the trick. Thanks! – Ferrite

Some have suggested adding COMMIT before FLUSH PRIVILEGES; some have suggested adding it after. I had auto-commit for my CLI, so I'm not sure where it should go, or if it matters. I've added it after in an edit. – Wiener

Tested this. Mysql 5.7+ which is installed by default with 18.04 LTS changes password with UPDATE mysql.user SET authentication_string=PASSWORD('new_password') where user='root'; so, you need to run that instead of ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password'; – Revulsive

@Revulsive The code as posted (except for the comments) is exactly the code as used in real life. Please leave it alone! – Wiener

@Wiener you can just reject my edit changes without that comment. Your solution didn't work for me completely. – Revulsive

@message, your workaround worked like a charm for me! Thanks – Aspersion

Thank you very much, after about an hour of trying this worked for me! – Imagine

Not work for me, magic thing happens when I try ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your new password'; from percona.com/blog/2016/03/16/… – Ima

I can confirm that on 5.7 this command ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password'; doesn't work. You can see from the output as it says that 0 rows affected. The one mentioned for 5.7+ does work as long as you don't forget to Commit; and FLUSH PRIVILEGES – Enterprise

MAN THANK YOU. I have been scratching my head to figure out whats wrong, following so many other steps, but this one is perfect – Iniquitous

Everything was fine until I restarted and tried to login in the last step. I had to use the command like: sudo mysql -u root -p Without sudo it was not working. – Belgian

I cant believe connecting to a brand new mysql installation is so hard and not documented at all – Whereto

Answer 3

MySQL 5.7 changed the secure model: now MySQL root login requires a sudo

The simplest (and safest) solution will be create a new user and grant required privileges.

1. Connect to mysql

sudo mysql --user=root mysql

2. Create a user for phpMyAdmin

CREATE USER 'phpmyadmin'@'localhost' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Reference - https://askubuntu.com/questions/763336/cannot-enter-phpmyadmin-as-root-mysql-5-7

Bubbler

To be honest I discarded this answer as pretty stupid at first and tried tons of other stuff including searching for random generated passwords in files which did not exist on my local Ubuntu Machine. However a simple SUDO and a blank password just did it. Have an upvote! – Knout

Answer 4

MySQL server 5.7 was already installed by default on my new Linux Mint 19.

But, what's the MySQL root password? It turns out that:

The default installation uses auth_socket for authentication, in lieu of passwords!

It allows a password-free login, provided that one is logged into the Linux system with the same user name. To login as the MySQL root user, one can use sudo:

sudo mysql --user=root

But how to then change the root password? To illustrate what's going on, I created a new user "me", with full privileges, with:

mysql> CREATE USER 'me'@'localhost' IDENTIFIED BY 'my_new_password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'me'@'localhost' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

Comparing "me" with "root":

mysql> SELECT user, plugin, HEX(authentication_string)  FROM mysql.user WHERE user = 'me' or user = 'root';
+------+-----------------------+----------------------------------------------------------------------------+
| user | plugin                | HEX(authentication_string)                                                 |
+------+-----------------------+----------------------------------------------------------------------------+
| root | auth_socket           |                                                                            |
| me   | mysql_native_password | 2A393846353030304545453239394634323734333139354241344642413245373537313... |
+------+-----------------------+----------------------------------------------------------------------------+

Because it's using auth_socket, the root password cannot be changed: the SET PASSWORD command fails, and mysql_secure_installation desn't attain anything...

==> To zap this alternate authentication mode and return the MySQL root user to using passwords:

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

A good explanation.

More details from the MySQL manual.

Leyla

Answer 5

In case you want to install mysql or percona unattended (like in my case ansible), you can use following script:

# first part opens mysql log
# second part greps lines with temporary password
# third part picks last line (most recent one)
# last part removes all the line except the password
# the result goes into password variable

password=$(cat /var/log/mysqld.log | grep "A temporary password is generated for" | tail -1 | sed -n 's/.*root@localhost: //p')

# setting new password, you can use $1 and run this script as a file and pass the argument through the script

newPassword="wh@teverYouLikE"

# resetting temporary password

mysql -uroot -p$password -Bse "ALTER USER 'root'@'localhost' IDENTIFIED BY '$newPassword';"

Dinadinah

cat: /var/log/mysqld.log: No such file or directory I use ubuntu 18.04, and I installed it via tasksel. Do you have a suggestion? – Maladjusted

I would search for it: find / -name mysqld 2>/dev/null – Dinadinah

Answer 6

It seems things were designed to avoid developers to set the root user, a better solution would be:

sudo mysql -u root

Then create a normal user, set a password, then use that user to work.

create user 'user'@'localhost' identified by 'user1234';
grant all on your_database.* to 'user'@'localhost';
select host, user from mysql.user;

Then try to access:

mysql -u user -p

Boom!

Hussey

Nope. It was designed to set a password for root instead of empty password. – Pegg

By default it is designed to connect only via local socket with root. Either way is a bad practice to use root to connect to MySQL even for dev, and my answer describes how to use a good practice of creating a non root user that actually works. – Hussey

Answer 7

MySQL 5.7 or newer generates a default temporary password after fresh install.

To use MySQL first you would be required to get that password from the log file which is present at the /var/log/mysqld.log. So follow the following process:

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

2. mysql_secure_installation

The second command is required to change the password for MySQL and also to make certain other changes like removing temporary databases, allow or disallow remote access to root user, delete anonymous users etc…

Prosaism

Answer 8

None of these answers worked for me on Ubuntu Server 18.04.1 and MySQL 5.7.23. I spent a bunch of time trying and failing at setting the password and auth plugin manually, finding the password in logs (it's not there), etc.

The solution is actually super easy:

sudo mysql_secure_installation

It's really important to do this with sudo. If you try without elevation, you'll be asked for the root password, which you obviously don't have.

Eladiaelaeoptene

Answer 9

After a lot of try, I could reset the default password with the following commands (Ubuntu and derivatives):

sudo -i
mkdir -p /var/run/mysqld
chown mysql:mysql /var/run/mysqld
/etc/init.d/mysql stop
mysqld_safe --skip-grant-tables &
mysql -uroot
use mysql;
update user set authentication_string=password('YOURPASSWORD') where user='root';
update user set plugin="mysql_native_password" where User='root';  
flush privileges;
quit;
sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start

Sometimes, even after typed in the terminal

mkdir -p /var/run/mysqld
chown mysql:mysql /var/run/mysqld
/etc/init.d/mysql stop
mysqld_safe --skip-grant-tables &

I got the error that the mysqld don't exists. So, quit, and type the same commands again.

And the final command

sudo /etc/init.d/mysql start

Sometimes doesn't work. Only after restart the computer.

Moraine

Worked on Ubuntu Bionic 18.04 Mysql 5.7.29. Just thought I'd add the versions. Thank you. – Allembracing

Answer 10

As of Ubuntu 20.04 with MySql 8.0 : you can set the password that way:

1. login to mysql with sudo mysql -u root

2. change the password:

USE mysql;
UPDATE user set authentication_string=NULL where User='root';
FLUSH privileges;
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'My-N7w_And.5ecure-P@s5w0rd';
FLUSH privileges;
QUIT

now you should be able to login with mysql -u root -p (or to phpMyAdmin with username root) and your chosen password.

P,S:

You can also login with user debian-sys-maint, the password is written in the file /etc/mysql/debian.cnf

Inessive

Thank for sharing – Officiary

If the root password is set and you don't have it, you can edit /etc/my.cnf and add skip-grant-tables #turn off password check to the [msyqld] block to be able to login – Breban

Answer 11

I just installed Linux Mint 19 (based on Ubuntu 18.04) on my machine. I installed MySQL 5.7 from the repo (sudo apt install mysql-server) and surprisingly during installation, the setup didn't prompt to enter root password. As a result I wasn't able to login into MySQL. I googled here and there and tried various answers I found on the net, including the accepted answer above. I uninstalled (purging all dpkgs with mysql in its name) and reinstalled again from the default Linux Mint repositories. NONE works.

After hours of unproductive works, I decided to reinstall MySQL from the official page. I opened MySQL download page (https://dev.mysql.com/downloads/repo/apt) for apt repo and clicked Download button at the bottom right.

Next, run it with dpkg:

sudo dpkg -i mysql-apt-config_0.8.10-1_all.deb

At the installation setup, choose the MySQL version that you'd like to install. The default option is 8.0 but I changed it to 5.7. Click OK to quit. After this, you have a new MySQL repo in your Software Sources.

Update your repo:

sudo apt update

Finally, install MySQL:

sudo apt install mysql-server

And now I was prompted to provide root password! Hope it helps for others with this same experience.

Exercitation

I spent days solving it while installing mysql-server from ubuntu repo but at the end official, the one MySQL provides, works. – Aliquant

Answer 12

To do it in non interactive mode (from a script):

systemctl start mysqld
MYSQL_ROOT_TMP_PSW=$(grep 'temporary password' $logpath/mysqld.log |sed "s|.*: ||")

## POPULATE SCHEMAS WITH ROOT USER
/usr/bin/mysql --connect-expired-password -u root -p${MYSQL_ROOT_TMP_PSW} < "$mysql_init_script"

Here's the head of the init script

SET GLOBAL validate_password_policy=LOW;
FLUSH privileges;

SET PASSWORD = PASSWORD('MYSQL_ROOT_PSW');
FLUSH privileges;

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
FLUSH privileges;

...

Then restart the service systemctl restart mysqld

Bac

Answer 13

I to was experiencing the same problem and the only thing I was able to do to make it work was to go this route:

drop user admin@localhost;
flush privileges;
create user admin@localhost identified by 'admins_password'

This allowed me to recreate my username and enter a password for the user name.

Mukul

Answer 14

In my case the data directory was automatically initialized with the --initialize-insecure option. So /var/log/mysql/error.log does not contain a temporary password but:

[Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

What worked was:

shell> mysql -u root --skip-password
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

Details: MySQL 5.7 Reference Manual > 2.10.4 Securing the Initial MySQL Account

Pleuropneumonia