What is the default root pasword for MySQL 5.7

Solution 1:

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!

Solution 2:

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

Solution 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

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