I installed a fresh MySQL 5.7 Service on my linux ubuntu server. I'm able to connect to the mysql server via cli by using mysql -u root -p.But if I try to connect via PHP I get this error:
SQLSTATE[HY000] [1698] Access denied for user 'root'@'localhost'
I have installed the MySQL and secured it via command: mysql_secure_installation
Since MySQL 5.7 or newer the root user is using the auth_socket plugin for authentication. In that way the root user does not need a password to connect to MySQL on the localhost.
Unfortunately this causes some problems e.g. if your try to connect to the MySQL Server via an Application (e.g. PhPMyAdmin).
To fix this problem you need to reset the authentication plugin for the root user and add a password.
First of all check if the auth plugin is set to auth_socket instead of mysql_native_password.
$ sudo mysql
Now check the user profiles via:
mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
Output
+------------------+------------------------+-----------------------+-----------+
| user | authentication_string | plugin | host |
+------------------+------------------------+-----------------------+-----------+
| root | | auth_socket | localhost |
| mysql.session | *XXXXX | mysql_native_password | localhost |
| mysql.sys | *XXXXX | mysql_native_password | localhost |
+------------------+------------------------+-----------------------+-----------+
3 rows in set (0.01 sec)
As you can see the root user uses the plugin auth_socket for authentication without password.
First set a new password for the root user:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new-password';
Run FLUSH PRIVILEGES the apply this changes.
mysql> FLUSH PRIVILEGES;
You can now check if the changes are stored:
mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
Output
+------------------+------------------------+-----------------------+-----------+
| user | authentication_string | plugin | host |
+------------------+------------------------+-----------------------+-----------+
| root | *XXXXX | mysql_native_password | localhost |
| mysql.session | *XXXXX | mysql_native_password | localhost |
| mysql.sys | *XXXXX | mysql_native_password | localhost |
+------------------+------------------------+-----------------------+-----------+
3 rows in set (0.01 sec)
You should be now able to connect to your MySQL via Username / Password on your hosts system via localhost or 127.0.0.1:3306.
You can't use the root user in MySQL 5.7 anymore without becoming a sudoer. That means you can't just run mysql -u root anymore and have to do sudo mysql -u root instead.
That also means that it will no longer work if you're using the root user in any non-command line application. You could create a new MySQL user and add the required privileges instead.