Description:
mysql-server & mysql-client have the same version. Problem "[Note] Bad handshake" during connections to mysqld happened, if mysqlD started on 192.168.x.x ip. When I restart this with real external IP - no problem.
Server 1: php, mysql-client, 192.168.0.1
Server 2: mysql-server, mysql-client, 192.168.0.10
Firewall is empty. Checked "nestat -an". Date/time is actiual.
Server 1:
- cmd "telnet 192.168.0.10 3306" - ok
- php script connect - Endless script freeze, no response
- cmd "mysql --ssl-mode=DISABLED" - ok
- cmd "mysql" - Endless program freeze, no response (And found "[Note] Bad handshake" in mysql-server logs)
- cmd "mysql -h .. -u .. -p" - the same freezing
Server 2:
- telnet 192.168.0.10 3306 - ok
- mysql - ok
When I replace "192.168.0.10" to external IP and restart mysqld - all is ok.
Tryed use "mysql_ssl_rsa_setup --uid=mysql -v", but it said:
- Certificate files are present in given dir. Skipping generation
- RSA key files are present in given dir. Skipping generation.
Tryed update packeges "openssl" in Ubuntu, but it is on date.
This story happend today. Without my actions. After 3 year for this project, mysql server and ubuntu servers. Every week all servers is rebooted for backup. Yesterday php was working with mysql on 192.168.x.x.
How to fix SSL problems with local ip 192.168.x.x?
mysql> SHOW VARIABLES LIKE '%ssl%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | ca.pem |
| ssl_capath | |
| ssl_cert | server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | server-key.pem |
+---------------+-----------------+
mysql> \s
mysql Ver 14.14 Distrib 5.7.32, for Linux (x86_64) using EditLine wrapper
Connection id: 14
Current database:
SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.32-0ubuntu0.18.04.1-log (Ubuntu)
Protocol version: 10
Connection: 192.168.0.10 via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 20 min 2 sec
Threads: 6 Questions: 97 Slow queries: 0 Opens: 123 Flush tables: 1 Open tables: 116 Queries per second avg: 0.080
--------------
Server 1: apt list --installed | grep sql
mysql-client/bionic-updates,bionic-security,now 5.7.32-0ubuntu0.18.04.1 all [installed]
mysql-client-5.7/bionic-updates,bionic-security,now 5.7.32-0ubuntu0.18.04.1 amd64 [installed,automatic]
mysql-client-core-5.7/bionic-updates,bionic-security,now 5.7.32-0ubuntu0.18.04.1 amd64 [installed]
mysql-common/bionic,now 5.8+1.0.4 all [installed,automatic]
php7.1-mysql/bionic,now 7.1.33-25+ubuntu18.04.1+deb.sury.org+1 amd64 [installed]
Server 2: apt list --installed | grep sql
libmysqlclient-dev/bionic-updates,bionic-security,now 5.7.32-0ubuntu0.18.04.1 amd64 [installed]
libmysqlclient20/bionic-updates,bionic-security,now 5.7.32-0ubuntu0.18.04.1 amd64 [installed,automatic]
mysql-apt-config/now 0.7.2-1 all [installed,local]
mysql-client/bionic-updates,bionic-security,now 5.7.32-0ubuntu0.18.04.1 all [installed]
mysql-client-5.7/bionic-updates,bionic-security,now 5.7.32-0ubuntu0.18.04.1 amd64 [installed,automatic]
mysql-client-core-5.7/bionic-updates,bionic-security,now 5.7.32-0ubuntu0.18.04.1 amd64 [installed,automatic]
mysql-common/bionic,now 5.8+1.0.4 all [installed,automatic]
mysql-server/bionic-updates,bionic-security,now 5.7.32-0ubuntu0.18.04.1 all [installed]
mysql-server-5.7/bionic-updates,bionic-security,now 5.7.32-0ubuntu0.18.04.1 amd64 [installed,automatic]
mysql-server-core-5.7/bionic-updates,bionic-security,now 5.7.32-0ubuntu0.18.04.1 amd64 [installed,automatic]
Both servers info:
Distributor ID: Ubuntu
Description: Ubuntu 18.04.5 LTS
Release: 18.04
Codename: bionic
Virtualization: kvm
Operating System: Ubuntu 18.04.5 LTS
Kernel: Linux 4.15.0-132-generic
Architecture: x86-64
Local time: Mon 2021-01-18 15:43:47 MSK
Universal time: Mon 2021-01-18 12:43:47 UTC
RTC time: Mon 2021-01-18 12:43:48
Time zone: Europe/Moscow (MSK, +0300)
System clock synchronized: yes
systemd-timesyncd.service active: yes
RTC in local TZ: no
/var/lib/mysql# ls -la
total 602288
drwx------ 10 mysql mysql 4096 Jan 18 15:41 .
drwxr-xr-x 44 root root 4096 Jan 18 15:39 ..
-rw-r----- 1 mysql mysql 178 Dec 2 2018 **.log
drwxr-x--- 2 mysql mysql 77824 Oct 8 2019 **
-rw-r----- 1 mysql mysql 56 Dec 2 2018 auto.cnf
drwxr-x--- 2 mysql mysql 4096 Mar 7 2019 **
drwxr-x--- 2 mysql mysql 4096 Feb 10 2020 **
-rw------- 1 mysql mysql 1680 Nov 19 2019 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 Nov 19 2019 ca.pem
-rw-r--r-- 1 mysql mysql 1112 Nov 19 2019 client-cert.pem
-rw------- 1 mysql mysql 1676 Nov 19 2019 client-key.pem
drwxr-x--- 2 mysql mysql 12288 Oct 26 20:45 **
-rw-r--r-- 1 mysql mysql 0 Oct 28 06:17 debian-5.7.flag
-rw-r----- 1 mysql mysql 894 Jan 18 15:41 ib_buffer_pool
-rw-r----- 1 mysql mysql 262144000 Jan 18 15:41 ib_logfile0
-rw-r----- 1 mysql mysql 262144000 Jan 18 15:41 ib_logfile1
-rw-r----- 1 mysql mysql 79691776 Jan 18 15:41 ibdata1
-rw-r----- 1 mysql mysql 12582912 Jan 18 15:41 ibtmp1
drwxr-x--- 2 mysql mysql 4096 Oct 28 06:18 mysql
-rw-r--r-- 1 mysql mysql 6 Oct 28 06:18 mysql_upgrade_info
drwxr-x--- 2 mysql mysql 4096 Oct 28 06:17 performance_schema
-rw------- 1 mysql mysql 1680 Nov 19 2019 private_key.pem
-rw-r--r-- 1 mysql mysql 452 Nov 19 2019 public_key.pem
-rw-r--r-- 1 mysql mysql 1112 Nov 19 2019 server-cert.pem
-rw------- 1 mysql mysql 1676 Nov 19 2019 server-key.pem
drwxr-x--- 2 mysql mysql 12288 Nov 19 2019 sys
drwxr-x--- 2 mysql mysql 4096 Sep 10 2019 **
ls -la /usr/sbin/mysqld
-rwxr-xr-x 1 root root 24703688 Oct 23 13:48 /usr/sbin/mysqld
How to repeat:
I dont know. In 20 years of using mysql on local addresses, this has not happened.