Fix MySQL connection error "Bad Handshake"

This problem has multiple causes. Read the initial instructions under each solution below to ensure you're on the right track.

Maybe you need 5.7 :)

You're working on a legacy project? Check the version of the production database using SELECT VERSION(). If it is indeed 5.7 then this is the right path to follow!

Just run a Bitnami's Docker image of MySQL 5.7 (trust me you don't want to install it bare-metal!). On Ubuntu and derivatives:

1. Create a directory to persist MySQL data, e.g. /your/local/mysql-data. Remember to give it proper permission so the container could access and create files in it:

   sudo mkdir -p /your/local/mysql-data
   sudo chown -R 1001:1001 /your/local/mysql-data

2. Run this and your MySQL 5.7 server should be ready on port 3307 of all network interfaces.

   docker run -d \
     --name mysql57 \
     -p 3307:3306 \
     -v /your/local/mysql-data:/bitnami/mysql \
     -e MYSQL_ROOT_PASSWORD=rootpass \
     -e MYSQL_USER=myuser \
     -e MYSQL_PASSWORD=mypassword \
     -e MYSQL_DATABASE=mydatabase \
     bitnami/mysql:5.7

   Check docker logs mysql57 for details.

Maybe you just need 8.0.28-

If you're using MySQL 8.0.30+ and you're so sure that your project is not legacy, then you might need to revert your MySQL Server to version 8.0.28 or lower. Cf prisma/prisma#16886.

On Ubuntu and derivatives:

1. If necessary, back up all your MySQL databases into one single file for convenience. (cf. this post and its comments)

   mysqldump -v -uroot -p --opt --all-databases --skip-lock-tables --max_allowed_packet=1G --routines --result-file=everything.sql

2. Purge all the MySQL packages. (This will not uninstall MySQL Workbench.)

   sudo apt purge mysql-server*
   sudo apt purge mysql-client*

3. Remove all the MySQL data (remember that we have backup! see step 1!). This is necessary since MySQL 8.0.28 will NOT install properly if you leave that data saved by MySQL 8.0.42 (installation would fail with an error like Cannot start MySQL Community Server...). Run this command:

   sudo rm -r /var/lib/mysql*

4. Install MySQL 8.0.28 and pinpoint the versions to prevent automatic updates (cf. https://askubuntu.com/a/1490025/1726542)

   sudo apt install mysql-server=8.0.28-0ubuntu4 mysql-server-8.0=8.0.28-0ubuntu4 mysql-client=8.0.28-0ubuntu4 mysql-client-8.0=8.0.28-0ubuntu4 mysql-server-core-8.0=8.0.28-0ubuntu4  mysql-client-core-8.0=8.0.28-0ubuntu4
   sudo apt-mark hold mysql-client
   sudo apt-mark hold mysql-client-8.0
   sudo apt-mark hold mysql-client-core-8.0
   sudo apt-mark hold mysql-server
   sudo apt-mark hold mysql-server-8.0
   sudo apt-mark hold mysql-server-core-8.0

5. Restore the previously-backed-up MySQL file (cf. https://stackoverflow.com/a/41738506/13680015)

   $ mysql -uroot -p --default-character-set=utf8
   mysql> SET names 'utf8';
   mysql> SOURCE everything.sql;

6. Re-create the users as well as some other MySQL configurations as needed. You might need this guide. You might not need to re-create the users since it's likely that data has been backed up as well as restored in the 1st and 5th steps above.