MySQL 5.7 changed the secure model: now MySQL root
login requires a sudo
.
I.e., phpMyAdmin will be not able to use root
credentials.
The simplest, safest and permanent solution will be create a new user and grant required privileges.
1. Connect to mysql
sudo mysql --user=root mysql
2. Create a real password
In the below steps I'll use <please_replace_this>
as a sample password. PLEASE, REPLACE IT BY YOUR PASSWORD! DON'T USE <please_replace_this>
AS PASSWORD!
3. Create a user for phpMyAdmin
Run the following commands (replacing <please_replace_this>
by the desired password):
CREATE USER 'phpmyadmin'@'localhost' IDENTIFIED BY '<please_replace_this>';
GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
If your phpMyAdmin is connecting to localhost, this should be enough.
4. Optional and unsafe: allow remote connections
Remember: allow a remote user to have all privileges is a security concern and this is not required in most of cases.
With this in mind, if you want this user to have the same privileges during remote connections, additionally run (replacing <please_replace_this>
by the password used in Step #2):
CREATE USER 'phpmyadmin'@'%' IDENTIFIED BY '<please_replace_this>';
GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
5. Update phpMyAdmin
Using sudo
, edit /etc/dbconfig-common/phpmyadmin.conf
file updating user/password values in the following sections (replacing <please_replace_this>
by the password used in Step #2):
# dbc_dbuser: database user
# the name of the user who we will use to connect to the database.
dbc_dbuser='phpmyadmin'
# dbc_dbpass: database user password
# the password to use with the above username when connecting
# to a database, if one is required
dbc_dbpass='<please_replace_this>'
Best Answer
Cause of the issue
The default configuration for logging in as
root
user in MySQL 5.7 requires use of an authentication socket. This can be verified via querying theuser
table:From the documentation:
In other words, mysql by default doesn't have root password set - you need to either run phpMyAdmin as root or via
sudo
(both of which are bad idea for security reasons), or you change the authentication method and reset the root password as shown in Digital Ocean tutorial.Note that aside from same name, MySQL users and system users are not the same. You can have a MySQL user
jdoe
and have no such user on the host system. Thus,root
is MySQL's root user, not system user.Steps to change plugin and password:
open terminal and run
sudo mysql -u root
. You should see a greeting message andmysql>
prompt. This is the MySQL shell, which is different from your command-line shell, so only SQL statements are accepted here.Enter the following sequence of SQL queries:
Exit and attempt to sign in:
If everything goes well, you should be able to sign in from phpMyAdmin via the new password. If something goes wrong, attempt to restart the server without permission checking (that's step num. 3 in the Digital Ocean tutorial). For other issues, feel free to ask another question here or on Database Administrators - Stack Exchange