[BUG] mysql states don't work with auth_plugin = caching_sha2_password but default mysql_native_password is deprecated

Description
Mysql_native_password auth plugin is deprecated in current mysql 8.0 version, but salt doesn't support new default plugin caching_sha2_password. There is possibility to set this plugin via auth_plugin argument, but it doesn't work because this plugin is not supported by salt module. It looks like from code, that there is no support for this plugin, only for old default mysql_native_password.

Setup
Try to add mysql user via salt state like this and set auth_plugin to non-default value caching_sha2_password

foo:
  mysql_user.present:
    - host: localhost
    - password: bobcat
    - auth_plugin: caching_sha2_password

I have played with it. If mysql user exists with this auth plugin already, setting grants (via mysql_grants.present) is failing due to unable to connect to mysql server.
If you have more users/grants, first one looks fine (but is not) and another one are failing.

Expected behavior
Added support for caching_sha2_password auth plugin to mysql module, so salt should be able to create user with new default auth plugin and set grants to him too.

Versions Report

Salt Version:
          Salt: 3006.9

Python Version:
        Python: 3.10.14 (main, Jun 26 2024, 11:44:37) [GCC 11.2.0]

Dependency Versions:
          cffi: 1.14.6
      cherrypy: 18.6.1
  cryptography: 42.0.5
      dateutil: 2.8.1
     docker-py: Not Installed
         gitdb: Not Installed
     gitpython: Not Installed
        Jinja2: 3.1.4
       libgit2: Not Installed
  looseversion: 1.0.2
      M2Crypto: Not Installed
          Mako: Not Installed
       msgpack: 1.0.2
  msgpack-pure: Not Installed
  mysql-python: Not Installed
     packaging: 22.0
     pycparser: 2.21
      pycrypto: Not Installed
  pycryptodome: 3.19.1
        pygit2: Not Installed
  python-gnupg: 0.4.8
        PyYAML: 6.0.1
         PyZMQ: 23.2.0
        relenv: 0.17.0
         smmap: Not Installed
       timelib: 0.2.4
       Tornado: 4.5.3
           ZMQ: 4.3.4

System Versions:
          dist: debian 12 bookworm
        locale: utf-8
       machine: x86_64
       release: 6.1.0-23-amd64
        system: Linux
       version: Debian GNU/Linux 12 bookworm

Additional context
https://dev.mysql.com/doc/refman/8.0/en/native-pluggable-authentication.html

Start by reproducing the failure with the provided mysql_user.present YAML and inspect the Salt MySQL module paths used by mysql_user.present and mysql_grants.present. Trace how auth_plugin is handled when connecting to MySQL, then verify that user creation and grant changes work with caching_sha2_password as well as mysql_native_password.