A Bash script to help you re-hash all MySQL passwords that are in mysql_native_password plugin format to caching_sha2_password format.

update_passwords.sh

#!/bin/bash

set -e

if [[ "$#" -ne 1 ]] || [[ $1 = '--help' ]]; then
    echo "This script can be used to update the password type for users with the mysql_native_password plugin."
    echo "Usage: $0 <mysql_container_name> [username]"
    exit 1
fi

container_name=$1

# Function to hash a password using MySQL's native password hashing algorithm
hash_password() {
    local password=$1
    echo "*$(echo -n "$password" | openssl sha1 -binary | openssl sha1 -hex | cut -d' ' -f2 | tr '[:lower:]' '[:upper:]')"
}

# Function to prompt for a password and verify it
verify_password() {
    local user=$1
    local host=$2
    local stored_hash=$3

    while true; do
        read -sp "Enter the password for '$user'@'$host': " password
        echo

        # Hash the entered password
        entered_hash=$(hash_password "$password")

        # Compare the entered hash with the stored hash
        if [ "$entered_hash" == "$stored_hash" ]; then
            echo "Password is correct."
            echo "$password"
            return 0
        else
            echo "Password is incorrect. Try again."
        fi
    done
}

where="1"
if [[ -n "$2" ]]; then
    where="user = '$2'"
fi
mapfile -t users < <(docker exec -i "$container_name" mysql -e "SELECT user, host, authentication_string FROM mysql.user WHERE plugin='mysql_native_password' AND $where;" -s -N)
if [ "${#users[@]}" -eq 0 ]; then
    printf '\u2705  '
    echo "No users found with the mysql_native_password plugin."
    exit 0
fi

# Process each user from the array
for user_info in "${users[@]}"; do
    IFS=$'\t' read -r user host stored_hash <<< "$user_info"
    while true; do
        read -sp "Enter the password for '$user'@'$host': " password
        echo

        # Hash the entered password
        entered_hash=$(hash_password "$password")

        # Compare the entered hash with the stored hash
        if [ "$entered_hash" == "$stored_hash" ]; then
            docker exec -i "$container_name" mysql -e "ALTER USER '$user'@'$host' IDENTIFIED WITH caching_sha2_password BY '$password';"
            printf '\u2705  '
            echo "User '$user'@'$host' password type changed to caching_sha2_password."
            break
        else
            printf '\u274c  '
            read -p "Password is incorrect. Do you want to (d)rop the user '$user'@'$host', (g)enerate a new password or (s)kip? (d/g/s): " choice
            case $choice in
                d|drop)
                    docker exec -i "$container_name" mysql -e "DROP USER '$user'@'$host';"
                    echo "User '$user'@'$host' dropped."
                    break
                    ;;
                g|generate)
                    new_password=$(openssl rand -base64 12)
                    docker exec "$container_name" mysql -e "ALTER USER '$user'@'$host' IDENTIFIED WITH caching_sha2_password BY '$new_password';"
                    echo "New password for '$user'@'$host': $new_password"
                    break
                    ;;
                s|skip)
                    echo "Skipping user '$user'@'$host'."
                    break
                    ;;
                *)
                    echo "Invalid choice."
                    ;;
            esac
        fi
    done
done