Since a WordPress URL change I can't connect to DO Managed MySQL database

So we have a WordPress 5.7 site running on an Ubuntu 16.04.3 x64 droplet, which uses a separate DigitalOcean managed MySQL 8 database. All was fine but I needed to change the site’s url. I did this and suddenly I got the dreaded ‘Error establishing a database connection’ and I can’t access it, or the wp-admin at all.

The WordPress site is a Trellis LEMP stack site. PHP version 7.3.19-1.

Things I have checked/done.

Added the DNS to point to the droplet for the new domain name.

Initially I altered all occurrences of the old domain name with the new domain name, in the database wp_options table, and with a ‘wp search-replace ‘example.dev’ ‘example.com’ --skip-columns=guid’, and updated the etc/nginx/sites-available/my_site_name.conf to match the new domain.

In the .env file DB_NAME, DB_USER, DB_PASSWORD and DB_HOST are correct - I haven’t altered them and have compared with the DO DB connection details page.

I can connect to the DB okay using MySQL Workbench.

If I SSH into the droplet I can run a ‘wp db check’ and everything passes with the credentials it has saved.

I tried a PHP file to check DB connection using mysqli($DB_HOST, $DB_USER, $DB_PASSWORD, $DB_NAME) and this reports (Warning: mysqli::__construct(): The server requested authentication method unknown to the client [caching_sha2_password]).

The user used to connect is set to ‘Legacy MySQL 5.x’ in DO DB control panel.

I tried to create a new user from scratch.

Droplet is added as a trusted source to the DB (I changed nothing in the control panel since it worked)

I rebooted the droplet, and asked DO to reboot the DB instance.

I can SSH into my WP droplet and telnet into the managed db instance fine.

The site is not secured with SSL, but it wasn’t before the name change so I figured that wasn’t the problem.

This is a staging setup so no one is screaming yet, but I need to get this running ASAP!

Any ideas how to troubleshoot this?

Many thanks!