Mysql caching_sha2_password Error

[nicad2]

I've started seeing the following error in my multiserver Zoneminder setup, sometime around the Feb 28 version of Zoneminder 1.37.

found in: /var/log/syslog
zmpkg[17103]: ERR [Error reconnecting to db: errstr:Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. error val:]

My setup includes two servers (server1 and server2) with the mysql database hosted on server1. Server1 is able to access the db fine. Server2 gets the above error and fails to connect. Eventually the issue becomes a mysql "Too many connections" error after a while since mysql disables the account for the offending server2. Its not clear whether there was an update to mysql or php that introduced the requirement for caching_sha2_password. From reading existing threads it looks like we normally use the less secure "mysql_native_password"? I tried forcing the db to use the old authentication method, but no luck so far.

[dougmccrary]

pretty sure you need ssl

[nicad2]

Enabling SSL for the mysql connections worked. After struggling with some permissions issues and a strange requirement that the cert and keys for the server had to be in /var/lib/mysql/ its working again. Specifying the paths in /etc/mysql/my.cnf wasn't working for me.