MySQL - How to generate a hashing password with salt in caching_sha2_password format

Question

In MySQL 5.7, I used to create a hashing password in Ruby by

def generate_mysql_password(plaintext_password)
  digest = OpenSSL::Digest.new('sha1', OpenSSL::Digest.new('sha1', plaintext_password).digest)
  return "\*" + digest.hexdigest.upcase
end

This returns something like *E3CCCB8781234C456895AD6B60B5A57FA1B3B8B1

And create a MySQL user in script by

CREATE USER IF NOT EXISTS 'test1'@'%' IDENTIFIED WITH mysql_native_password AS '*E3CCCB8781234C456895AD6B60B5A57FA1B3B8B1';

This is working!

Since upgrading to 8.0, the authentication_plugin has changed to caching_sha2_password by default. I know we can set it back to use mysql_native_password, but I prefer to use the new method.

However, I can't seem to generate a hashing password with salt that matches with caching_sha2_password format.

Here is my code:

def generate_mysql_password_hash(password, salt, iterations = 5000)
  digest = OpenSSL::Digest.new('sha256')
  hashed_password  = OpenSSL::PKCS5.pbkdf2_hmac(password, salt, iterations, salt.length, digest)
  salted_hashed_password = "$A$005$#{salt}$#{Base64.strict_encode64(hashed_password).chomp}"
end

password = "my_strong_password"
salt = OpenSSL::Random.random_bytes(20).unpack('H*')[0]
hashed_password = generate_mysql_password_hash(password, salt)

When run the code , it returns something like $A$005$c2befcadb7d6ad2609316ab86ea06b6494a2882b$OP8gdn9nX/IhtkvVKq6kQ4MSdKs7DhMNSze5nJKphpQGOu0unz6oBg==

And run the MySQL command to create the test user with the hashed password

CREATE USER IF NOT EXISTS 'test1'@'%' IDENTIFIED WITH caching_sha2_password AS '$A$005$c2befcadb7d6ad2609316ab86ea06b6494a2882b$OP8gdn9nX/IhtkvVKq6kQ4MSdKs7DhMNSze5nJKphpQGOu0unz6oBg==';

It returns ERROR 1827 (HY000): The password hash doesn't have the expected format.

I have tried several ways to generate a hashing password with salt length is 20 and 5000 iterations but those didn't seem to work at all.

If we run the command:

CREATE USER IF NOT EXISTS 'test1'@'%' IDENTIFIED WITH caching_sha2_password BY "my_strong_password";

This works and I can see the authentication_string format looks like $A$005${salt}{digest}. Not sure what I missed here. Has anyone knows how to generate a hashing password with Salt in Ruby? Really appreciate your help.