sysown / proxysql

High-performance MySQL proxy with a GPL license.
http://www.proxysql.com
GNU General Public License v3.0
6.01k stars 978 forks source link

Can't make backend SSL connection to work. #3145

Open kriptor opened 3 years ago

kriptor commented 3 years ago

Hi, I'm probably missing or just don't understand something... and would appreciate some guidance. I'm sorry for the humongous post, btw. Thx

This is how to reproduce the issue (from the title):

proxysql.cnf

datadir="/var/lib/proxysql"
restart_on_missing_heartbeats=10
errorlog="/dev/stderr"

admin_variables=
{
  web_enabled=true
  web_port=6080

  read_only=false
  admin_credentials="admin:admin"
  stats_credentials="stats:stats"
  mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
  refresh_interval=2000

  hash_passwords=false

  debug=true
}

mysql_variables=
{
  threads=4
  max_connections=256
  default_query_delay=0
  default_query_timeout=30000
  default_max_latency_ms=1000
  have_compress=true
  poll_timeout=2000
  interfaces="127.0.0.1:6033;/tmp/proxysql.sock"
  default_schema="test"
  stacksize=1048576
  server_version="8.0.20"
  connect_timeout_server=3000
  connect_timeout_server_max=10000

  # make sure to configure monitor username and password
  # https://github.com/sysown/proxysql/wiki/Global-variables#mysql-monitor_username-mysql-monitor_password
  monitor_username="test"
  monitor_password="bujgvocwjzeejidy"
  monitor_history=600000
  monitor_connect_interval=60000
  monitor_ping_interval=10000
  monitor_read_only_interval=1500
  monitor_read_only_timeout=500

  ping_interval_server_msec=120000
  ping_timeout_server=500
  commands_stats=true
  sessions_sort=true
  connect_retries_on_failure=10

  free_connections_pct=10
  long_query_time=500

  shun_on_failures=5
  shun_recovery_time_sec=2
  monitor_slave_lag_when_null=0

  ssl_p2s_ca="/etc/ca-certificate.pem"

  verbose_query_error=true
}

# defines all the MySQL servers
mysql_servers=
(
  {
    address = "test-do-user-5030278-0.b.db.ondigitalocean.com"
    port = 25060
    hostgroup = 0
    status = "ONLINE"
    weight = 1
    use_ssl = 1
    compression = 0
    max_replication_lag = 0
    max_connections = 10
    max_latency_ms = 0
    comment = "managed test db server"
  }
)

# defines all the MySQL users
mysql_users=
(
  {
    username = "test"
    password = "bujgvocwjzeejidy"
    default_hostgroup = 0
    active = 1
    default_schema = "test"
    max_connections = 256
    frontend = 1
    backend = 1
    fast_forward = 0
    comment = "test user"
  }
)

terminal 1 (to start proxysql and monitor its logs)

$ docker run --name proxysql --rm -v /tmp/ca-certificate.pem:/etc/ca-certificate.pem -v /tmp/proxysql.cfg:/etc/proxysql.cnf proxysql/proxysql:2.0.15
2020-11-10 18:45:15 [INFO] Using config file /etc/proxysql.cnf
2020-11-10 18:45:15 [INFO] Using OpenSSL version: OpenSSL 1.1.1d  10 Sep 2019
2020-11-10 18:45:15 [INFO] No SSL keys/certificates found in datadir (/var/lib/proxysql). Generating new keys/certificates.
2020-11-10 18:45:16 [INFO] ProxySQL version 2.0.15-20-g32bb92cd
2020-11-10 18:45:16 [INFO] Detected OS: Linux 7377780ba635 5.4.39-linuxkit #1 SMP Fri May 8 23:03:06 UTC 2020 x86_64
2020-11-10 18:45:16 [INFO] ProxySQL SHA1 checksum: 1035cc730548d1167b73e03173c19dd5d3e363e8
Standard ProxySQL MySQL Logger rev. 2.0.0714 -- MySQL_Logger.cpp -- Wed Oct 28 20:09:40 2020
Standard ProxySQL Cluster rev. 0.4.0906 -- ProxySQL_Cluster.cpp -- Wed Oct 28 20:09:40 2020
Standard ProxySQL Statistics rev. 1.4.1027 -- ProxySQL_Statistics.cpp -- Wed Oct 28 20:09:40 2020
Standard ProxySQL HTTP Server Handler rev. 1.4.1031 -- ProxySQL_HTTP_Server.cpp -- Wed Oct 28 20:09:40 2020
Standard ProxySQL Admin rev. 2.0.6.0805 -- ProxySQL_Admin.cpp -- Wed Oct 28 20:09:40 2020
2020-11-10 18:45:16 [INFO] ProxySQL SHA1 checksum: 1035cc730548d1167b73e03173c19dd5d3e363e8
Standard MySQL Threads Handler rev. 0.2.0902 -- MySQL_Thread.cpp -- Wed Oct 28 20:09:40 2020
Standard MySQL Authentication rev. 0.2.0902 -- MySQL_Authentication.cpp -- Wed Oct 28 20:09:40 2020
2020-11-10 18:45:16 [INFO] Dumping mysql_servers_incoming
+--------------+------------------------------------------------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------------------+
| hostgroup_id | hostname                                       | port  | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment                |
+--------------+------------------------------------------------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------------------+
| 0            | test-do-user-5030278-0.b.db.ondigitalocean.com | 25060 | 0         | 1      | 0      | 0           | 10              | 0                   | 1       | 0              | managed test db server |
+--------------+------------------------------------------------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------------------+
2020-11-10 18:45:16 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming
+-------------+--------------+----------+------+
| mem_pointer | hostgroup_id | hostname | port |
+-------------+--------------+----------+------+
+-------------+--------------+----------+------+
2020-11-10 18:45:16 [INFO] Dumping mysql_servers JOIN mysql_servers_incoming
+--------------+------------------------------------------------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------------------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------------------+
| hostgroup_id | hostname                                       | port  | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment                | mem_pointer | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment                |
+--------------+------------------------------------------------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------------------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------------------+
| 0            | test-do-user-5030278-0.b.db.ondigitalocean.com | 25060 | 0         | 1      | 0      | 0           | 10              | 0                   | 1       | 0              | managed test db server | 0           | 0         | 1      | 0      | 0           | 10              | 0                   | 1       | 0              | managed test db server |
+--------------+------------------------------------------------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------------------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------------------+
2020-11-10 18:45:16 [INFO] Creating new server in HG 0 : test-do-user-5030278-0.b.db.ondigitalocean.com:25060 , gtid_port=0, weight=1, status=0
2020-11-10 18:45:16 [INFO] New mysql_group_replication_hostgroups table
2020-11-10 18:45:16 [INFO] New mysql_galera_hostgroups table
2020-11-10 18:45:16 [INFO] New mysql_aws_aurora_hostgroups table
2020-11-10 18:45:16 [INFO] MySQL_HostGroups_Manager::commit() locked for 2ms
Standard Query Processor rev. 2.0.6.0805 -- Query_Processor.cpp -- Wed Oct 28 20:09:40 2020
In memory Standard Query Cache (SQC) rev. 1.2.0905 -- Query_Cache.cpp -- Wed Oct 28 20:09:40 2020
Standard MySQL Monitor (StdMyMon) rev. 2.0.1226 -- MySQL_Monitor.cpp -- Wed Oct 28 20:09:40 2020
2020-11-10 18:45:19 [INFO] Latest ProxySQL version available: 2.0.15-20-g32bb92c

2020-11-10 18:47:18 MySQL_Monitor.cpp:718:monitor_connect_thread(): [ERROR] Server test-do-user-5030278-0.b.db.ondigitalocean.com:25060 is returning "Access denied" for monitoring user
2020-11-10 18:50:18 MySQL_Monitor.cpp:718:monitor_connect_thread(): [ERROR] Server test-do-user-5030278-0.b.db.ondigitalocean.com:25060 is returning "Access denied" for monitoring user
2020-11-10 18:51:18 MySQL_Monitor.cpp:718:monitor_connect_thread(): [ERROR] Server test-do-user-5030278-0.b.db.ondigitalocean.com:25060 is returning "Access denied" for monitoring user
2020-11-10 18:52:18 MySQL_Monitor.cpp:718:monitor_connect_thread(): [ERROR] Server test-do-user-5030278-0.b.db.ondigitalocean.com:25060 is returning "Access denied" for monitoring user
2020-11-10 18:54:09 MySQL_Monitor.cpp:718:monitor_connect_thread(): [ERROR] Server test-do-user-5030278-0.b.db.ondigitalocean.com:25060 is returning "Access denied" for monitoring user
2020-11-10 18:55:09 MySQL_Monitor.cpp:718:monitor_connect_thread(): [ERROR] Server test-do-user-5030278-0.b.db.ondigitalocean.com:25060 is returning "Access denied" for monitoring user
2020-11-10 18:55:42 mysql_connection.cpp:969:handler(): [ERROR] Failed to mysql_real_connect() on test-do-user-5030278-0.b.db.ondigitalocean.com:25060 , FD (Conn:35 , MyDS:35) , 1045: Access denied for user 'test'@'15A.B.C.D' (using password: YES).
2020-11-10 18:55:43 mysql_connection.cpp:969:handler(): [ERROR] Failed to mysql_real_connect() on test-do-user-5030278-0.b.db.ondigitalocean.com:25060 , FD (Conn:35 , MyDS:35) , 1045: Access denied for user 'test'@'15A.B.C.D' (using password: YES).
2020-11-10 18:55:44 mysql_connection.cpp:969:handler(): [ERROR] Failed to mysql_real_connect() on test-do-user-5030278-0.b.db.ondigitalocean.com:25060 , FD (Conn:35 , MyDS:35) , 1045: Access denied for user 'test'@'15A.B.C.D' (using password: YES).
2020-11-10 18:55:44 mysql_connection.cpp:969:handler(): [ERROR] Failed to mysql_real_connect() on test-do-user-5030278-0.b.db.ondigitalocean.com:25060 , FD (Conn:35 , MyDS:35) , 1045: Access denied for user 'test'@'15A.B.C.D' (using password: YES).
2020-11-10 18:55:44 mysql_connection.cpp:969:handler(): [ERROR] Failed to mysql_real_connect() on test-do-user-5030278-0.b.db.ondigitalocean.com:25060 , FD (Conn:35 , MyDS:35) , 1045: Access denied for user 'test'@'15A.B.C.D' (using password: YES).
2020-11-10 18:55:44 mysql_connection.cpp:969:handler(): [ERROR] Failed to mysql_real_connect() on test-do-user-5030278-0.b.db.ondigitalocean.com:25060 , FD (Conn:35 , MyDS:35) , 1045: Access denied for user 'test'@'15A.B.C.D' (using password: YES).
2020-11-10 18:55:45 mysql_connection.cpp:969:handler(): [ERROR] Failed to mysql_real_connect() on test-do-user-5030278-0.b.db.ondigitalocean.com:25060 , FD (Conn:35 , MyDS:35) , 1045: Access denied for user 'test'@'15A.B.C.D' (using password: YES).
2020-11-10 18:55:45 mysql_connection.cpp:969:handler(): [ERROR] Failed to mysql_real_connect() on test-do-user-5030278-0.b.db.ondigitalocean.com:25060 , FD (Conn:35 , MyDS:35) , 1045: Access denied for user 'test'@'15A.B.C.D' (using password: YES).
2020-11-10 18:55:45 mysql_connection.cpp:969:handler(): [ERROR] Failed to mysql_real_connect() on test-do-user-5030278-0.b.db.ondigitalocean.com:25060 , FD (Conn:35 , MyDS:35) , 1045: Access denied for user 'test'@'15A.B.C.D' (using password: YES).
2020-11-10 18:55:45 mysql_connection.cpp:969:handler(): [ERROR] Failed to mysql_real_connect() on test-do-user-5030278-0.b.db.ondigitalocean.com:25060 , FD (Conn:35 , MyDS:35) , 1045: Access denied for user 'test'@'15A.B.C.D' (using password: YES).
2020-11-10 18:55:46 mysql_connection.cpp:969:handler(): [ERROR] Failed to mysql_real_connect() on test-do-user-5030278-0.b.db.ondigitalocean.com:25060 , FD (Conn:35 , MyDS:35) , 1045: Access denied for user 'test'@'15A.B.C.D' (using password: YES).
2020-11-10 18:56:09 MySQL_Monitor.cpp:718:monitor_connect_thread(): [ERROR] Server test-do-user-5030278-0.b.db.ondigitalocean.com:25060 is returning "Access denied" for monitoring user
2020-11-10 18:57:09 MySQL_Monitor.cpp:718:monitor_connect_thread(): [ERROR] Server test-do-user-5030278-0.b.db.ondigitalocean.com:25060 is returning "Access denied" for monitoring user

terminal 2 (to test the connectivity from inside the same container)

$ docker exec -it proxysql bash
root@7377780ba635:/#
root@7377780ba635:/#
root@7377780ba635:/# wget https://repo.mysql.com/mysql-apt-config_0.8.16-1_all.deb
--2020-11-10 18:46:12--  https://repo.mysql.com/mysql-apt-config_0.8.16-1_all.deb
Resolving repo.mysql.com (repo.mysql.com)... 2.18.69.79
Connecting to repo.mysql.com (repo.mysql.com)|2.18.69.79|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 35528 (35K) [application/x-debian-package]
Saving to: 'mysql-apt-config_0.8.16-1_all.deb'

mysql-apt-config_0.8.16-1_all.de 100%[=========================================================>]  34.70K  --.-KB/s    in 0.06s

2020-11-10 18:46:13 (534 KB/s) - 'mysql-apt-config_0.8.16-1_all.deb' saved [35528/35528]

root@7377780ba635:/#
root@7377780ba635:/#
root@7377780ba635:/# dpkg -i mysql-apt-config_0.8.16-1_all.deb
Selecting previously unselected package mysql-apt-config.
(Reading database ... 8441 files and directories currently installed.)
Preparing to unpack mysql-apt-config_0.8.16-1_all.deb ...
Unpacking mysql-apt-config (0.8.16-1) ...
Setting up mysql-apt-config (0.8.16-1) ...
debconf: unable to initialize frontend: Dialog
debconf: (No usable dialog-like program is installed, so the dialog based frontend cannot be used. at /usr/share/perl5/Debconf/FrontEnd/Dialog.pm line 76.)
debconf: falling back to frontend: Readline
debconf: unable to initialize frontend: Readline
debconf: (Can't locate Term/ReadLine.pm in @INC (you may need to install the Term::ReadLine module) (@INC contains: /etc/perl /usr/local/lib/x86_64-linux-gnu/perl/5.24.1 /usr/local/share/perl/5.24.1 /usr/lib/x86_64-linux-gnu/perl5/5.24 /usr/share/perl5 /usr/lib/x86_64-linux-gnu/perl/5.24 /usr/share/perl/5.24 /usr/local/lib/site_perl /usr/lib/x86_64-linux-gnu/perl-base .) at /usr/share/perl5/Debconf/FrontEnd/Readline.pm line 7.)
debconf: falling back to frontend: Teletype
Configuring mysql-apt-config
----------------------------

MySQL APT Repo features MySQL Server along with a variety of MySQL components. You may select the appropriate product to choose
the version that you wish to receive.

Once you are satisfied with the configuration then select last option 'Ok' to save the configuration, then run 'apt-get update' to
load package list. Advanced users can always change the configurations later, depending on their own needs.

  1. MySQL Server & Cluster (Currently selected: mysql-8.0)  3. MySQL Preview Packages (Currently selected: Disabled)
  2. MySQL Tools & Connectors (Currently selected: Enabled)  4. Ok
Which MySQL product do you wish to configure? 2

This is a collection of tools for the MySQL suite, including connectors, MySQL Workbench, MySQL Utilities and MySQL router.

  1. Enabled  2. Disabled
Enable or disable MySQL tools and utilities 1

MySQL APT Repo features MySQL Server along with a variety of MySQL components. You may select the appropriate product to choose
the version that you wish to receive.

Once you are satisfied with the configuration then select last option 'Ok' to save the configuration, then run 'apt-get update' to
load package list. Advanced users can always change the configurations later, depending on their own needs.

  1. MySQL Server & Cluster (Currently selected: mysql-8.0)  3. MySQL Preview Packages (Currently selected: Disabled)
  2. MySQL Tools & Connectors (Currently selected: Enabled)  4. Ok
Which MySQL product do you wish to configure? 4

Warning: apt-key should not be used in scripts (called from postinst maintainerscript of the package mysql-apt-config)
OK
root@7377780ba635:/#
root@7377780ba635:/#
root@7377780ba635:/# apt update && apt install -y mysql-client
Get:1 http://repo.mysql.com/apt/debian stretch InRelease [21.6 kB]
Get:2 https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/stretch ./ InRelease [2070 B]
Get:3 http://repo.mysql.com/apt/debian stretch/mysql-8.0 Sources [951 B]
Get:4 https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/stretch ./ Packages [3713 B]
Get:5 http://repo.mysql.com/apt/debian stretch/mysql-8.0 amd64 Packages [8008 B]
Get:6 http://repo.mysql.com/apt/debian stretch/mysql-tools amd64 Packages [5403 B]
Get:6 http://repo.mysql.com/apt/debian stretch/mysql-8.0 amd64 mysql-community-client amd64 8.0.22-1debian9 [2820 kB]
Get:7 http://repo.mysql.com/apt/debian stretch/mysql-8.0 amd64 mysql-client amd64 8.0.22-1debian9 [83.5 kB]
Fetched 4910 kB in 8s (561 kB/s)
debconf: delaying package configuration, since apt-utils is not installed
Selecting previously unselected package mysql-common.
(Reading database ... 8446 files and directories currently installed.)
Preparing to unpack .../0-mysql-common_8.0.22-1debian9_amd64.deb ...
Unpacking mysql-common (8.0.22-1debian9) ...
Selecting previously unselected package libgpm2:amd64.
Preparing to unpack .../1-libgpm2_1.20.4-6.2+b1_amd64.deb ...
Unpacking libgpm2:amd64 (1.20.4-6.2+b1) ...
Selecting previously unselected package libncurses5:amd64.
Preparing to unpack .../2-libncurses5_6.0+20161126-1+deb9u2_amd64.deb ...
Unpacking libncurses5:amd64 (6.0+20161126-1+deb9u2) ...
Selecting previously unselected package mysql-community-client-plugins.
Preparing to unpack .../3-mysql-community-client-plugins_8.0.22-1debian9_amd64.deb ...
Unpacking mysql-community-client-plugins (8.0.22-1debian9) ...
Selecting previously unselected package mysql-community-client-core.
Preparing to unpack .../4-mysql-community-client-core_8.0.22-1debian9_amd64.deb ...
Unpacking mysql-community-client-core (8.0.22-1debian9) ...
Selecting previously unselected package mysql-community-client.
Preparing to unpack .../5-mysql-community-client_8.0.22-1debian9_amd64.deb ...
Unpacking mysql-community-client (8.0.22-1debian9) ...
Selecting previously unselected package mysql-client.
Preparing to unpack .../6-mysql-client_8.0.22-1debian9_amd64.deb ...
Unpacking mysql-client (8.0.22-1debian9) ...
Setting up libncurses5:amd64 (6.0+20161126-1+deb9u2) ...
Setting up libgpm2:amd64 (1.20.4-6.2+b1) ...
Setting up mysql-common (8.0.22-1debian9) ...
update-alternatives: using /etc/mysql/my.cnf.fallback to provide /etc/mysql/my.cnf (my.cnf) in auto mode
Setting up mysql-community-client-plugins (8.0.22-1debian9) ...
Processing triggers for libc-bin (2.24-11+deb9u4) ...
Setting up mysql-community-client-core (8.0.22-1debian9) ...
Setting up mysql-community-client (8.0.22-1debian9) ...
Setting up mysql-client (8.0.22-1debian9) ...
root@7377780ba635:/#
root@7377780ba635:/#
root@7377780ba635:/# mysql -S /tmp/proxysql_admin.sock -u admin -padmin --prompt='Admin> '
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 8.0.20 (ProxySQL Admin Module)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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.

Admin> SELECT * FROM global_variables WHERE variable_name LIKE 'mysql%ssl%';
+-------------------------------------+-------------------------+
| variable_name                       | variable_value          |
+-------------------------------------+-------------------------+
| mysql-have_ssl                      | false                   |
| mysql-session_idle_show_processlist | true                    |
| mysql-show_processlist_extended     | 0                       |
| mysql-ssl_p2s_cert                  |                         |
| mysql-ssl_p2s_key                   |                         |
| mysql-ssl_p2s_cipher                |                         |
| mysql-ssl_p2s_ca                    | /etc/ca-certificate.pem |
+-------------------------------------+-------------------------+
7 rows in set (0.00 sec)

Admin> SELECT * FROM runtime_global_variables WHERE variable_name LIKE 'mysql%ssl%';
+-------------------------------------+-------------------------+
| variable_name                       | variable_value          |
+-------------------------------------+-------------------------+
| mysql-have_ssl                      | false                   |
| mysql-session_idle_show_processlist | true                    |
| mysql-show_processlist_extended     | 0                       |
| mysql-ssl_p2s_ca                    | /etc/ca-certificate.pem |
| mysql-ssl_p2s_cert                  |                         |
| mysql-ssl_p2s_key                   |                         |
| mysql-ssl_p2s_cipher                |                         |
+-------------------------------------+-------------------------+
7 rows in set (0.00 sec)

Admin> SELECT * FROM mysql_servers;
+--------------+------------------------------------------------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------------------+
| hostgroup_id | hostname                                       | port  | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment                |
+--------------+------------------------------------------------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------------------+
| 0            | test-do-user-5030278-0.b.db.ondigitalocean.com | 25060 | 0         | ONLINE | 1      | 0           | 10              | 0                   | 1       | 0              | managed test db server |
+--------------+------------------------------------------------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------------------+
1 row in set (0.00 sec)

Admin> SELECT * FROM runtime_mysql_servers;
+--------------+------------------------------------------------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------------------+
| hostgroup_id | hostname                                       | port  | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment                |
+--------------+------------------------------------------------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------------------+
| 0            | test-do-user-5030278-0.b.db.ondigitalocean.com | 25060 | 0         | ONLINE | 1      | 0           | 10              | 0                   | 1       | 0              | managed test db server |
+--------------+------------------------------------------------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------------------+
1 row in set (0.00 sec)

Admin> SELECT * FROM mysql_users;
+----------+------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+-----------+
| username | password         | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment   |
+----------+------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+-----------+
| test     | bujgvocwjzeejidy | 1      | 0       | 0                 | test           | 0             | 1                      | 0            | 1       | 1        | 256             | test user |
+----------+------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+-----------+
1 row in set (0.00 sec)

Admin> SELECT * FROM runtime_mysql_users;
+----------+------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+-----------+
| username | password         | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment   |
+----------+------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+-----------+
| test     | bujgvocwjzeejidy | 1      | 0       | 0                 | test           | 0             | 1                      | 0            | 0       | 1        | 256             | test user |
| test     | bujgvocwjzeejidy | 1      | 0       | 0                 | test           | 0             | 1                      | 0            | 1       | 0        | 256             | test user |
+----------+------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+-----------+
2 rows in set (0.01 sec)

Admin> \q
Bye
root@7377780ba635:/#
root@7377780ba635:/#
root@7377780ba635:/# mysql -S /tmp/proxysql.sock -u test -pbujgvocwjzeejidy
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 8.0.20 (ProxySQL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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 tables;
ERROR 1045 (28000): Access denied for user 'test'@'15A.B.C.D' (using password: YES)
mysql> \q
Bye
root@7377780ba635:/#
root@7377780ba635:/#
root@7377780ba635:/# mysql -u test -pbujgvocwjzeejidy -h test-do-user-5030278-0.b.db.ondigitalocean.com -P 25060 --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/ca-certificate.pem
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4693
Server version: 8.0.20 Source distribution

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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 tables;
ERROR 1046 (3D000): No database selected
mysql> \s
--------------
mysql  Ver 8.0.22 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:      4693
Current database:
Current user:       test@15A.B.C.D
SSL:            Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     8.0.20 Source distribution
Protocol version:   10
Connection:     test-do-user-5030278-0.b.db.ondigitalocean.com via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:       25060
Binary data as:     Hexadecimal
Uptime:         4 hours 36 min 11 sec

Threads: 2  Questions: 25758  Slow queries: 0  Opens: 500  Flush tables: 3  Open tables: 418  Queries per second avg: 1.554
--------------

mysql> \q
Bye
root@7377780ba635:/#
root@7377780ba635:/#
root@7377780ba635:/# ls -l /var/lib/proxysql
total 364
-rw-r--r-- 1 root root   1078 Nov 10 18:45 proxysql-ca.pem
-rw-r--r-- 1 root root   1062 Nov 10 18:45 proxysql-cert.pem
-rw-r--r-- 1 root root   1675 Nov 10 18:45 proxysql-key.pem
-rw-r--r-- 1 root root 196608 Nov 10 18:45 proxysql.db
-rw-r--r-- 1 root root 163840 Nov 10 19:22 proxysql_stats.db
root@7377780ba635:/# ls -l /etc/proxysql.cnf
-rw-r--r-- 1 root root 2063 Nov 10 15:10 /etc/proxysql.cnf
root@7377780ba635:/# ls -l /etc/ca-certificate.pem
-rw-r--r-- 1 root root 1513 Nov 10 15:07 /etc/ca-certificate.pem
root@7377780ba635:/# cat /etc/proxysql.cnf
datadir="/var/lib/proxysql"
restart_on_missing_heartbeats=10
errorlog="/dev/stderr"

admin_variables=
{
  web_enabled=true
  web_port=6080

  read_only=false
  admin_credentials="admin:admin"
  stats_credentials="stats:stats"
  mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
  refresh_interval=2000

  hash_passwords=false

  debug=true
}

mysql_variables=
{
  threads=4
  max_connections=256
  default_query_delay=0
  default_query_timeout=30000
  default_max_latency_ms=1000
  have_compress=true
  poll_timeout=2000
  interfaces="127.0.0.1:6033;/tmp/proxysql.sock"
  default_schema="test"
  stacksize=1048576
  server_version="8.0.20"
  connect_timeout_server=3000
  connect_timeout_server_max=10000

  # make sure to configure monitor username and password
  # https://github.com/sysown/proxysql/wiki/Global-variables#mysql-monitor_username-mysql-monitor_password
  monitor_username="test"
  monitor_password="bujgvocwjzeejidy"
  monitor_history=600000
  monitor_connect_interval=60000
  monitor_ping_interval=10000
  monitor_read_only_interval=1500
  monitor_read_only_timeout=500

  ping_interval_server_msec=120000
  ping_timeout_server=500
  commands_stats=true
  sessions_sort=true
  connect_retries_on_failure=10

  free_connections_pct=10
  long_query_time=500

  shun_on_failures=5
  shun_recovery_time_sec=2
  monitor_slave_lag_when_null=0

  ssl_p2s_ca="/etc/ca-certificate.pem"

  verbose_query_error=true
}

# defines all the MySQL servers
mysql_servers=
(
  {
    address = "test-do-user-5030278-0.b.db.ondigitalocean.com"
    port = 25060
    hostgroup = 0
    status = "ONLINE"
    weight = 1
    use_ssl = 1
    compression = 0
    max_replication_lag = 0
    max_connections = 10
    max_latency_ms = 0
    comment = "managed test db server"
  }
)

# defines all the MySQL users
mysql_users=
(
  {
    username = "test"
    password = "bujgvocwjzeejidy"
    default_hostgroup = 0
    active = 1
    default_schema = "test"
    max_connections = 256
    frontend = 1
    backend = 1
    fast_forward = 0
    comment = "test user"
  }
)
root@7377780ba635:/#
renecannao commented 3 years ago

Hi @kriptor . Please try running this on Admin:

SET admin-hash_passwords='false';
LOAD ADMIN VARIABLES TO RUNTIME;
SAVE ADMIN VARIABLES TO DISK;

then force proxysql to reload the users:

LOAD MYSQL USERS TO RUNTIME;

Finally try again to connect. Let me know if that works

kriptor commented 3 years ago

Hi @renecannao, thx for replying.

I bootstrapped proxysql from the above proxysql.cnf which had hash_passwords=false directive, so I guess proxysql wasn't hashing passwords. If you search my OP for used password bujgvocwjzeejidy, you can see there was the exact same password literal in both mysql_users and runtime_mysql_users table records. This same password was then used with mysql-client (can also be seen from my OP) which worked.

Anyway, I tried what you suggested but unfortunately it didn't change a thing.

What actually did the trick at the end was changing password encryption from Default – MySQL 8+ to Legacy – MySQL 5.x on the side of managed Digitalocean MySQL. It's not perfect, but it works. According to https://proxysql.com/documentation/mysql-8-0/ MySQL 8 should be supported although this doesn't seem to be the case.. at least not in my setup with DO managed MySQL.