Setting up WordPress + MySQL with Docker Compose - 'Error establishing a database connection'

Question

I am attempting to set up WordPress + MySQL locally with Docker Compose, and I keep getting the infamous 'Error establishing a database connection' error. I'm on a Mac running Mojave, and have 127.0.0.1 mapped to mysite.local under /etc/hosts. The following is my docker-compose.yml (obviously 'mysite' and the password entries are substitutions):

version: '3'

services:
  mysite-wp-db:
    image: mysql:latest
    volumes:
      - ./db/initdb.d:/docker-entrypoint-initdb.d
    restart: always
    ports: 
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: root_pwd
      MYSQL_DATABASE: wordpress
      MYSQL_USER: some_user
      MYSQL_PASSWORD: pwd

  phpmyadmin:
    image: phpmyadmin/phpmyadmin
    environment:
      PMA_HOST: mysite-wp-db
      MYSQL_ROOT_PASSWORD: root_pwd
      PMA_ABSOLUTE_URI: http://mysite.local/
    ports:
      - "8080:80"
    restart: always
    depends_on: 
      - mysite-wp-db

  mysite-wp:
    depends_on:
      - mysite-wp-db
    image: wordpress:latest
    restart: always
    expose:
      - "80"
    environment:
      - VIRTUAL_PORT=80
      - VIRTUAL_HOST=mysite.org,mysite.com,mysite.local
    volumes:
      - ./wp:/var/www/html

networks:
  default:
    external:
      name: mysite-nginx-proxy

I am running this using the popular jwilder/nginx-proxy container. Also you can see I am also running phpmyadmin, and that is working fine - I can log in using the passwords specified in the compose file, and I can see that the database has the right name and gets populated from the sql dump file under ./db/initdb.d. Also docker exec -it <container_id> bin/bash shows me that the wp volume has been mounted into the container, and wp-config.php file is there. Originally I was just mounting the wp-contents folder, but even mounting the entire wp folder gives me the same error. I have also tried various version of mySQL (5.6, 5.7, latest). All give the same error. Very frustrating.

The even more frustrating part is that I had this all working a few months ago. It worked virtually first time. Nothing has really changed, although there is now much more content, so the db dump file is much larger (c. 14MB). What could I possibly be doing wrong?

If I access the mysite.local I get'Error establishing a database connection'. If I access mysite.local/wp/ I get Apache 'Internal Server Error'

N.B: The initial development of the site occurred under an AMPPS WordPress installation, on 127.0.0.1. I simply exported the db in a dump file, and changed all occurrences of 127.0.0.1 to mysite.local.

UPDATE: Setting WP_DEBUG to true gives me the following browser error message:

Warning: mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password] in /var/www/html/wp-includes/wp-db.php on line 1531

And this is what I have for lines 1530-33 in wp-includes/wp-db.php:

if ( WP_DEBUG ) {
  mysqli_real_connect( $this->dbh, $host, $this->dbuser, $this->dbpassword, null, $port, $socket, $client_flags );
} else {
  @mysqli_real_connect( $this->dbh, $host, $this->dbuser, $this->dbpassword, null, $port, $socket, $client_flags );
}

Answer 1

OK I fixed this by downgrading to MySQL 5.7 (I mentioned that version in my post but I was getting other errors at that time). Everything is working as expected now. Seems related to this issue.