Re: cannot connect from mysql client nor PHP, only sudo

On 14.02.2017 02:56, Peter H. Coffin wrote:
> On Sun, 12 Feb 2017 08:10:36 +0100, Jivanmukta wrote:

>> I installed new PHP (5.6).
>> I found no my.ini file. I found mysql.cnf:
>> /etc/mysql/mysql.cnf
>> /etc/mysql/conf.d/mysql.cnf
>> I have mysql root password.

This is *never* a problem.

> Since sudo
> works, but your normal user ID doesn't, it's very likely an "OS
> permissions" issue

I would have agreed to that before I read about MySQL 5.7 now shipping the root_at_localhost user with auth_socket authentication.

In that case MySQL behaves *exactly* as it should. You can connect as root_at_localhost only if you *are authenticated* as user root with the operating system.

The old behavior, that anybody with a shell account on the MySQL machine could connect as MySQL user "root" without a password, was a security problem from day 1. And it was common wisdom and strongly advised, to secure the root_at_localhost MySQL account with a password asap after installing MySQL.

With the general availability of the auth_socket authentication method, this is no longer necessary. Which is a good thing<tm> because it removes an extra step to secure the MySQL server. Also if you never have to set a password, you cannot forget it.

[Quoted] As for the "problem" of the thread-starter: there is no problem here. If you want to be able to do DBA work from your own system account, add a MySQL user with the SUPER privilege for that. Preferably use the auth_socket method for that user, too. Your PHP script should *not* connect as a MySQL user with SUPER privileges. Create another user for the script. Here it makes sense to set a password.

HTH, XL Received on Tue Feb 14 2017 - 10:32:48 CET