1

Can't connect to mysql on localhost over TCP on a freshly installed server version: 5.7.18-0ubuntu0.17.04.1 (Ubuntu)

This works:

# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 5.7.18-0ubuntu0.17.04.1 (Ubuntu)

This doesn't:

# mysql -h 127.0.0.1
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

From within client I see that mysql is configured to listen on 127.0.0.1:

mysql> show variables like '%bind%';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| bind_address  | 127.0.0.1 |
+---------------+-----------+
1 row in set (0.00 sec)

And it is indeed listening:

# netstat -tl
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 localhost:mysql         0.0.0.0:*               LISTEN

And because of that, PHP also fails with:

Warning: mysqli_connect(): (HY000/1698): Access denied for user 'root'@'localhost'

How to fix that? Maybe there is a setting the disables password based login over TCP?

UPDATE: It appeared that localhost means local socket, so I've created another record for 'root'@'127.0.0.1' for TCP connections.

CREATE USER 'root'@'127.0.0.1';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1';
FLUSH PRIVILEGES;

But it still doesn't work:

# mysql --protocol=TCP --host=127.0.0.1
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

Password is not set.

mysql> SELECT host, user, authentication_string FROM mysql.user;
+-----------+------------------+-------------------------------------------+
| host      | user             | authentication_string                     |
+-----------+------------------+-------------------------------------------+
| localhost | root             |                                           |
| localhost | mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | debian-sys-maint | *4CF7B81390C295E6AF38067F3B621BBF736D3366 |
| 127.0.0.1 | root             |                                           |
+-----------+------------------+-------------------------------------------+
4 rows in set (0.01 sec)

UPDATE 2: I started from scratch, so this is the test that fails:

mysql -h 127.0.0.1

But after recreating user, it starts to work:

DROP USER 'root'@'localhost';
CREATE USER 'root'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Why it doesn't work with the same user out of the box?

Community
  • 1
  • 1
anatoly techtonik
  • 19,847
  • 9
  • 124
  • 140
  • See [this](https://stackoverflow.com/questions/10823854/using-for-host-when-creating-a-mysql-user) post. – Alex Howansky May 07 '17 at 14:34
  • You have a password for the mysql i guess. The root password needs to be passed in both the cases – Exprator May 07 '17 at 14:34
  • If there was password, then `mysql` without `-h` wouldn't work. – anatoly techtonik May 07 '17 at 14:36
  • @RogerRV I am already under root. – anatoly techtonik May 07 '17 at 14:37
  • @AlexHowansky nice lead - `mysql -h localhost` works ok. I would accept an answer with necessary `GRANT` command once I check that it helps. – anatoly techtonik May 07 '17 at 14:48
  • Do you really need root access from PHP? Usually it is bad practice, except you are going to write another phpmyadmin. Instead you should create a new db user with limited permissions to some database as needed by the app. You might be using the auth_socket module. Then the key clause is `IDENTIFIED WITH mysql_native_password` to switch back to password login. See also https://www.percona.com/blog/2016/03/16/change-user-password-in-mysql-5-7-with-plugin-auth_socket/ – Pinke Helga May 07 '17 at 14:56
  • @Quasimodo'sclone yes, this is a single database inside of docker container for a single PHP app. There is no point in securing DB further than local access, because if PHP is hacked no user data will be secure. – anatoly techtonik May 07 '17 at 15:10

2 Answers2

1

Do you have a password for your root user? Try command

mysql -h 127.0.0.1 -u root -p

If you ran the mysql_secure_installation it blocks remote root login. You could try the following to reset the root user.

Stop MySQL service

#/etc/init.d/mysql stop

Exec MySQL server with --skip-grant-tables

#mysqld -u mysql --skip-grant-tables &

Exec mysql client as root

#mysql -u root

Update your password

mysql> update mysql.user set password=password('newpassword') where user='anotheruser';

Reload privileges

mysql> flush privileges;

Kill mysqld

#killall mysql

Start mysql daemon

#/etc/init.d/mysql start
rbur0425
  • 479
  • 8
  • 26
1

This is the solution:

UPDATE mysql.user SET plugin='mysql_native_password' WHERE user='root' AND host ='localhost';
FLUSH PRIVILEGES;

By default 'root'@'localhost' is configured to authenticate over plugin that only works with local unix socket. This command switches to mysql_native_password plugin for user, which works with TCP (needed for my local PHP installation).

After UPDATE 2 I compared mysql.user before and after recreating root@localhost and found the difference in plugin column.

anatoly techtonik
  • 19,847
  • 9
  • 124
  • 140