Question

Why can't I connect to a remote MySQL server from a PHP application, but can connect from mysql-client

Posted March 11, 2021 673 views
MySQLPHPUbuntu 20.04

I have installed Ubuntu 18.04.3 LTS on my virtual box:

enter image description here

I have also created an ssh tunnel the following way to connect to my remote db:

admin@admin-VirtualBox:~$ ssh -v -v -v root@xx.xx.xxx.xxx -CNL 13306:localhost:3306
OpenSSH_7.6p1 Ubuntu-4ubuntu0.3, OpenSSL 1.0.2n  7 Dec 2017
debug1: Reading configuration data /etc/ssh/ssh_config
debug1: /etc/ssh/ssh_config line 19: Applying options for *
debug2: resolving "xx.xx.xxx.xxx" port 22
debug2: ssh_connect_direct: needpriv 0
debug1: Connecting to xx.xx.xxx.xxx [xx.xx.xxx.xxx] port 22.
debug1: Connection established.
debug1: identity file /home/admin/.ssh/id_rsa type 0
debug1: key_load_public: No such file or directory
debug1: identity file /home/admin/.ssh/id_rsa-cert type -1
debug1: key_load_public: No such file or directory
debug1: identity file /home/admin/.ssh/id_dsa type -1
debug1: key_load_public: No such file or directory
debug1: identity file /home/admin/.ssh/id_dsa-cert type -1
debug1: key_load_public: No such file or directory
debug1: identity file /home/admin/.ssh/id_ecdsa type -1
debug1: key_load_public: No such file or directory
debug1: identity file /home/admin/.ssh/id_ecdsa-cert type -1
debug1: key_load_public: No such file or directory
debug1: identity file /home/admin/.ssh/id_ed25519 type -1
debug1: key_load_public: No such file or directory
debug1: identity file /home/admin/.ssh/id_ed25519-cert type -1
debug1: Local version string SSH-2.0-OpenSSH_7.6p1 Ubuntu-4ubuntu0.3
debug1: Remote protocol version 2.0, remote software version OpenSSH_8.2p1 Ubuntu-4ubuntu0.1
debug1: match: OpenSSH_8.2p1 Ubuntu-4ubuntu0.1 pat OpenSSH* compat 0x04000000
debug2: fd 3 setting O_NONBLOCK
debug1: Authenticating to xx.xx.xxx.xxx:22 as 'root'
debug3: hostkeys_foreach: reading file "/home/admin/.ssh/known_hosts"
debug3: record_hostkey: found key type ECDSA in file /home/admin/.ssh/known_hosts:1
debug3: load_hostkeys: loaded 1 keys from xx.xx.xxx.xxx
debug3: order_hostkeyalgs: prefer hostkeyalgs: ecdsa-sha2-nistp256-cert-v01@openssh.com,ecdsa-sha2-nistp384-cert-v01@openssh.com,ecdsa-sha2-nistp521-cert-v01@openssh.com,ecdsa-sha2-nistp256,ecdsa-sha2-nistp384,ecdsa-sha2-nistp521
debug3: send packet: type 20
debug1: SSH2_MSG_KEXINIT sent
debug3: receive packet: type 20
debug1: SSH2_MSG_KEXINIT received
debug2: local client KEXINIT proposal
debug2: KEX algorithms: curve25519-sha256,curve25519-sha256@libssh.org,ecdh-sha2-nistp256,ecdh-sha2-nistp384,ecdh-sha2-nistp521,diffie-hellman-group-exchange-sha256,diffie-hellman-group16-sha512,diffie-hellman-group18-sha512,diffie-hellman-group-exchange-sha1,diffie-hellman-group14-sha256,diffie-hellman-group14-sha1,ext-info-c
debug2: host key algorithms: ecdsa-sha2-nistp256-cert-v01@openssh.com,ecdsa-sha2-nistp384-cert-v01@openssh.com,ecdsa-sha2-nistp521-cert-v01@openssh.com,ecdsa-sha2-nistp256,ecdsa-sha2-nistp384,ecdsa-sha2-nistp521,ssh-ed25519-cert-v01@openssh.com,ssh-rsa-cert-v01@openssh.com,ssh-ed25519,rsa-sha2-512,rsa-sha2-256,ssh-rsa
debug2: ciphers ctos: chacha20-poly1305@openssh.com,aes128-ctr,aes192-ctr,aes256-ctr,aes128-gcm@openssh.com,aes256-gcm@openssh.com
debug2: ciphers stoc: chacha20-poly1305@openssh.com,aes128-ctr,aes192-ctr,aes256-ctr,aes128-gcm@openssh.com,aes256-gcm@openssh.com
debug2: MACs ctos: umac-64-etm@openssh.com,umac-128-etm@openssh.com,hmac-sha2-256-etm@openssh.com,hmac-sha2-512-etm@openssh.com,hmac-sha1-etm@openssh.com,umac-64@openssh.com,umac-128@openssh.com,hmac-sha2-256,hmac-sha2-512,hmac-sha1
debug2: MACs stoc: umac-64-etm@openssh.com,umac-128-etm@openssh.com,hmac-sha2-256-etm@openssh.com,hmac-sha2-512-etm@openssh.com,hmac-sha1-etm@openssh.com,umac-64@openssh.com,umac-128@openssh.com,hmac-sha2-256,hmac-sha2-512,hmac-sha1
debug2: compression ctos: zlib@openssh.com,zlib,none
debug2: compression stoc: zlib@openssh.com,zlib,none
debug2: languages ctos: 
debug2: languages stoc: 
debug2: first_kex_follows 0 
debug2: reserved 0 
debug2: peer server KEXINIT proposal
debug2: KEX algorithms: curve25519-sha256,curve25519-sha256@libssh.org,ecdh-sha2-nistp256,ecdh-sha2-nistp384,ecdh-sha2-nistp521,diffie-hellman-group-exchange-sha256,diffie-hellman-group16-sha512,diffie-hellman-group18-sha512,diffie-hellman-group14-sha256
debug2: host key algorithms: rsa-sha2-512,rsa-sha2-256,ssh-rsa,ecdsa-sha2-nistp256,ssh-ed25519
debug2: ciphers ctos: chacha20-poly1305@openssh.com,aes128-ctr,aes192-ctr,aes256-ctr,aes128-gcm@openssh.com,aes256-gcm@openssh.com
debug2: ciphers stoc: chacha20-poly1305@openssh.com,aes128-ctr,aes192-ctr,aes256-ctr,aes128-gcm@openssh.com,aes256-gcm@openssh.com
debug2: MACs ctos: umac-64-etm@openssh.com,umac-128-etm@openssh.com,hmac-sha2-256-etm@openssh.com,hmac-sha2-512-etm@openssh.com,hmac-sha1-etm@openssh.com,umac-64@openssh.com,umac-128@openssh.com,hmac-sha2-256,hmac-sha2-512,hmac-sha1
debug2: MACs stoc: umac-64-etm@openssh.com,umac-128-etm@openssh.com,hmac-sha2-256-etm@openssh.com,hmac-sha2-512-etm@openssh.com,hmac-sha1-etm@openssh.com,umac-64@openssh.com,umac-128@openssh.com,hmac-sha2-256,hmac-sha2-512,hmac-sha1
debug2: compression ctos: none,zlib@openssh.com
debug2: compression stoc: none,zlib@openssh.com
debug2: languages ctos: 
debug2: languages stoc: 
debug2: first_kex_follows 0 
debug2: reserved 0 
debug1: kex: algorithm: curve25519-sha256
debug1: kex: host key algorithm: ecdsa-sha2-nistp256
debug1: kex: server->client cipher: chacha20-poly1305@openssh.com MAC: <implicit> compression: zlib@openssh.com
debug1: kex: client->server cipher: chacha20-poly1305@openssh.com MAC: <implicit> compression: zlib@openssh.com
debug3: send packet: type 30
debug1: expecting SSH2_MSG_KEX_ECDH_REPLY
debug3: receive packet: type 31
debug1: Server host key: ecdsa-sha2-nistp256 SHA256:aaaaaaaaaaaaaaaaaaaaaaaaaaa/aaaaaaaaaaaaaaaaaaaaaaaaaaa
debug3: hostkeys_foreach: reading file "/home/admin/.ssh/known_hosts"
debug3: record_hostkey: found key type ECDSA in file /home/admin/.ssh/known_hosts:1
debug3: load_hostkeys: loaded 1 keys from xx.xx.xxx.xxx
debug1: Host 'xx.xx.xxx.xxx' is known and matches the ECDSA host key.
debug1: Found key in /home/admin/.ssh/known_hosts:1
debug3: send packet: type 21
debug2: set_newkeys: mode 1
debug1: rekey after 134217728 blocks
debug1: SSH2_MSG_NEWKEYS sent
debug1: expecting SSH2_MSG_NEWKEYS
debug3: receive packet: type 21
debug1: SSH2_MSG_NEWKEYS received
debug2: set_newkeys: mode 0
debug1: rekey after 134217728 blocks
debug2: key: /home/admin/.ssh/id_rsa (0x55ba671d36b0), agent
debug2: key: /home/admin/.ssh/id_dsa ((nil))
debug2: key: /home/admin/.ssh/id_ecdsa ((nil))
debug2: key: /home/admin/.ssh/id_ed25519 ((nil))
debug3: send packet: type 5
debug3: receive packet: type 7
debug1: SSH2_MSG_EXT_INFO received
debug1: kex_input_ext_info: server-sig-algs=<ssh-ed25519,sk-ssh-ed25519@openssh.com,ssh-rsa,rsa-sha2-256,rsa-sha2-512,ssh-dss,ecdsa-sha2-nistp256,ecdsa-sha2-nistp384,ecdsa-sha2-nistp521,sk-ecdsa-sha2-nistp256@openssh.com>
debug3: receive packet: type 6
debug2: service_accept: ssh-userauth
debug1: SSH2_MSG_SERVICE_ACCEPT received
debug3: send packet: type 50
debug3: receive packet: type 51
debug1: Authentications that can continue: publickey,password
debug3: start over, passed a different list publickey,password
debug3: preferred gssapi-keyex,gssapi-with-mic,publickey,keyboard-interactive,password
debug3: authmethod_lookup publickey
debug3: remaining preferred: keyboard-interactive,password
debug3: authmethod_is_enabled publickey
debug1: Next authentication method: publickey
debug1: Offering public key: RSA SHA256:aaaaaaaaaaaaaaaaaaaaaaaaaaa /home/admin/.ssh/id_rsa
debug3: send_pubkey_test
debug3: send packet: type 50
debug2: we sent a publickey packet, wait for reply
debug3: receive packet: type 60
debug1: Server accepts key: pkalg rsa-sha2-512 blen 279
debug2: input_userauth_pk_ok: fp SHA256:aaaaaaaaaaaaaaaaaaaaaaaaaaa
debug3: sign_and_send_pubkey: RSA SHA256:aaaaaaaaaaaaaaaaaaaaaaaaaaa
debug3: send packet: type 50
debug3: receive packet: type 52
debug1: Enabling compression at level 6.
debug1: Authentication succeeded (publickey).
Authenticated to xx.xx.xxx.xxx ([xx.xx.xxx.xxx]:22).
debug1: Local connections to LOCALHOST:13306 forwarded to remote address localhost:3306
debug3: channel_setup_fwd_listener_tcpip: type 2 wildcard 0 addr NULL
debug3: sock_set_v6only: set socket 5 IPV6_V6ONLY
debug1: Local forwarding listening on ::1 port 13306.
debug2: fd 5 setting O_NONBLOCK
debug3: fd 5 is O_NONBLOCK
debug1: channel 0: new [port listener]
debug1: Local forwarding listening on 127.0.0.1 port 13306.
debug2: fd 6 setting O_NONBLOCK
debug3: fd 6 is O_NONBLOCK
debug1: channel 1: new [port listener]
debug2: fd 3 setting TCP_NODELAY
debug3: ssh_packet_set_tos: set IP_TOS 0x10
debug1: Requesting no-more-sessions@openssh.com
debug3: send packet: type 80
debug1: Entering interactive session.
debug1: pledge: network
debug3: receive packet: type 80
debug1: client_input_global_request: rtype hostkeys-00@openssh.com want_reply 0
debug3: receive packet: type 4
debug1: Remote: /root/.ssh/authorized_keys:1: key options: agent-forwarding port-forwarding pty user-rc x11-forwarding
debug3: receive packet: type 4
debug1: Remote: /root/.ssh/authorized_keys:1: key options: agent-forwarding port-forwarding pty user-rc x11-forwarding
debug1: Connection to port 13306 forwarding to localhost port 3306 requested.
debug2: fd 7 setting TCP_NODELAY
debug2: fd 7 setting O_NONBLOCK
debug3: fd 7 is O_NONBLOCK
debug1: channel 2: new [direct-tcpip]
debug3: send packet: type 90
debug3: receive packet: type 91
debug2: channel 2: open confirm rwindow 2097152 rmax 32768
debug1: Connection to port 13306 forwarding to localhost port 3306 requested.
debug2: fd 8 setting TCP_NODELAY
debug2: fd 8 setting O_NONBLOCK
debug3: fd 8 is O_NONBLOCK
debug1: channel 3: new [direct-tcpip]
debug3: send packet: type 90
debug3: receive packet: type 91
debug2: channel 3: open confirm rwindow 2097152 rmax 32768
debug3: receive packet: type 96
debug2: channel 3: rcvd eof
debug2: channel 3: output open -> drain
debug2: channel 3: obuf empty
debug2: channel 3: close_write
debug2: channel 3: output drain -> closed
debug2: channel 3: read<=0 rfd 8 len 0
debug2: channel 3: read failed
debug2: channel 3: close_read
debug2: channel 3: input open -> drain
debug2: channel 3: ibuf empty
debug2: channel 3: send eof
debug3: send packet: type 96
debug2: channel 3: input drain -> closed
debug2: channel 3: send close
debug3: send packet: type 97
debug3: channel 3: will not send data after close
debug3: receive packet: type 97
debug2: channel 3: rcvd close
debug3: channel 3: will not send data after close
debug2: channel 3: is dead
debug2: channel 3: garbage collecting
debug1: channel 3: free: direct-tcpip: listening port 13306 for localhost port 3306, connect from 127.0.0.1 port 59650 to 127.0.0.1 port 13306, nchannels 4
debug3: channel 3: status: The following connections are open:
  #2 direct-tcpip: listening port 13306 for localhost port 3306, connect from 127.0.0.1 port 59644 to 127.0.0.1 port 13306 (t4 r0 i0/0 o0/0 fd 7/7 cc -1)
  #3 direct-tcpip: listening port 13306 for localhost port 3306, connect from 127.0.0.1 port 59650 to 127.0.0.1 port 13306 (t4 r1 i3/0 o3/0 fd 8/8 cc -1)

Locally I am using the following mysql-version:

admin@admin-VirtualBox:~$ mysql --version
mysql  Ver 14.14 Distrib 5.7.33, for Linux (x86_64) using  EditLine wrapper

When trying to connect via my local mysql-client it works fine:

admin@admin-VirtualBox:~$ mysql --host=127.0.0.1 --port=13306 mysql -u root -p
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 136
Server version: 8.0.23-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+-----------------------------+
| Database                    |
+-----------------------------+
| information_schema          |
| mysql                       |
| performance_schema          |
| phpmyadmin                  |
| sys                         |
| test_db                     |
+-----------------------------+
7 rows in set (0.03 sec)

mysql> 

Also when using dbeaver 7.3.5 works out fine:

enter image description here

However, when trying to connect via the following php script I get an error in the log of my mysql-db /etc/mysql/mysql.conf.d

I am using locally PHP 7.4.1 and on the remote host PHP 7.4.3.

<?php
$mysqli = new mysqli("127.0.0.1", "root", "myPassword", "test_db", 13306);

/* check connection */
if ($mysqli->connect_errno) {
    mysqli_debug("/home/marcus/Desktop/client.trace");
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

/* check if server is alive */
if ($mysqli->ping()) {
    mysqli_debug("/home/marcus/Desktop/client.trace");
    printf ("Our connection is ok!\n");
} else {
    printf ("Error: %s\n", $mysqli->error);
}

/* close connection */
$mysqli->close();
?>

The error that I get remotely is:

2021-03-08T05:26:49.885317Z 132 [Note] [MY-010914] [Server] Got timeout reading communication packets

I also tried changing $mysqli = new mysqli("localhost", "root", "myPassword", "test_db", 13306); but this does not work as it seems to me that mysqli confuses this with my localhost on my local machine. I only get the above error when using 127.0.0.1.

Any suggestions what I am doing wrong? Why can I connect via my local mysql client and also with dbeaver, BUT not via php?

I think it might be an issue with either my virtualbox connection (less likely as dbever etc is working) or my local php settings.

I really appreciate your replies!

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
Submit an Answer
1 answer

Hi there,

As this is a MySQL 8 server, what I could suggest is change the authentication method for your user from cachingsha2password to mysqlnativepassword.

You can follow the steps on how to do that here:

https://www.digitalocean.com/community/questions/how-to-change-caching_sha2_password-to-mysql_native_password-on-a-digitalocean-s-managed-mysql-database

Also as a side note, I would recommend creating another MySQL user rather than root.

Regards,
Bobby