Summary: mysql-server 8.0.16 is running inside a Docker container. php 7 + Apache2 + myslnd 5.0.12 + mysqld 5.6.22 are running outside Docker. In PHP, mysqli fails thus: mysqli_connect('127.0.0.1', ...

PHP-mysqli can’t connect to MySQL 8.0.16 running in a Docker

Summary:

mysql-server 8.0.16 is running inside a Docker container.
php 7 + Apache2 + myslnd 5.0.12 + mysqld 5.6.22 are running outside Docker.

In PHP, mysqli fails thus:

mysqli_connect('127.0.0.1', 'test', 'test', 'test', 8016)

ERR: 2054 : The server requested authentication method unknown to the client

Meanwhile mysql can connect thus from outside Docker:

mysql  -P 8016 -h 127.0.0.1 -u test -ptest test

Question: How can I get mysqli_connect working?

Details:

Here are some of the relevant GRANT entries.

mysql> select host, user, db, select_priv, grant_priv
                  from mysql.db where user='test';
+------------+------+------+-------------+------------+
| host       | user | db   | select_priv | grant_priv |
+------------+------+------+-------------+------------+
| 172.17.0.1 | test | test | Y           | N          |

mysql> select host, user, plugin, select_priv, grant_priv
                    from mysql.user where user='test';
+------------+------+-----------------------+-------------+------------+
| host       | user | plugin                | select_priv | grant_priv |
+------------+------+-----------------------+-------------+------------+
| 172.17.0.1 | test | mysql_native_password | N           | N          |

I found the /etc/my.cnf inside the docker image and enabled

default-authentication-plugin=mysql_native_password

That allowed even an old (5.6) mysql to connect thus:

mysql  -P 8016 -h 127.0.0.1 -u test -ptest test

And, yes, it came back with a heading including

Server version: 8.0.16 MySQL Community Server - GPL

It seems that the commandline mysql knows about mysql_native_password, but mysqlnd does not.

More version info:

PHP Version 7.0.33-0ubuntu0.16.04.6
Apache/2.4.18 (Ubuntu)

phpinfo says this about mysqlnd:

Version:         mysqlnd 5.0.12-dev - 20150407 - $Id: b5c5906d452ec590732a93b051f3827e02749b83 $
Loaded plugins:  mysqlnd,debug_trace,auth_plugin_mysql_native_password,auth_plugin_mysql_clear_password,auth_plugin_sha256_password
API Extensions:  mysqli,pdo_mysql

#docker ps (shows port mapping):

CONTAINER ID        IMAGE                       COMMAND                  CREATED             STATUS                  PORTS                               NAMES
99193bedb36c        mysql/mysql-server:8.0.16   "/entrypoint.sh my..."   8 weeks ago         Up 12 hours (healthy)   33060/tcp, 0.0.0.0:8016->3306/tcp   o8016

PHP-CLI also fails to connect.

(Yes, I ran FLUSH PRIVILEGES; at the appropriate time.)

Answer

You’ve tried setting default-authentication-plugin in my.cnf – perhaps try to do it as a command arg instead? I had similar problems trying to auth, but this works for me in my docker-compose.yml – I never tried setting it in my.cnf

mysql:
    container_name: myapp-db
    image: mysql:8.0
    command: --default-authentication-plugin=mysql_native_password
    environment:
     (snip)

(command taken from the Docker docs page for MySQL).

Advertisement

Answer