Support for MySQL 8.0

ProxySQL fully supports MySQL 8.0 , although there are some limitations prior to version 2.0.2:

  1. Starting with MySQL 8.0.4, the default authentication plugin for MySQL server was changed from mysql_native_password to caching_sha2_password. ProxySQL doesn’t yet support caching_sha2_password.
    For this reason, the MySQL server needs to be configured using mysql_native_password when using ProxySQL 2.0.2 or older. See the doc for further details.
  2. ProxySQL doesn’t accept connections (frontend connections and connections to Admin) if the client tries to use the caching_sha2_password authentication plugin prior to ProxySQL version 2.0.2. If clients are using libmysqlclient from MySQL 8.0 (that defaults to caching_sha2_password), they must explicitly use mysql_native_password.
    You can specify default-auth=mysql_native_password in my.cnf in the section[mysql] or [client], or specify it on the command line.
    For example, to connect to Admin:

    mysql --default-auth=mysql_native_password -h 127.0.0.1 -P6032 -u admin -padmin

Both notes above are not valid anymore for recent versions of ProxySQL newer than 2.0.2

Although, if on the backend the user is configured to use caching_sha2_password, passwords in mysql_users.password need to be in clear text (not hashed), and admin variable admin-hash_passwords needs to be set to false

Collation issues

Before version 2.0.16/2.1.1 , if the client connects to ProxySQL using collation utf8mb4_0900_ai_ci during connection handshake , ProxySQL will incorrectly use collation utf8mb4_general_ci when connecting to backend.
This because for ProxySQL the default collation for character set utf8mb4 is utf8mb4_general_ci: this is true for MySQL before 8.0.1 and for MariaDB (yet not supporting collation utf8mb4_0900_ai_ci).

From version 2.0.16/2.1.1 , ProxySQL will correctly use the collation utf8mb4_0900_ai_ci to connect to backend if the client specified collation utf8mb4_0900_ai_ci.

Although, if the backend doesn’t support the collation specific by the client, the backend connection will use the default collation of the backend.