Change root mysql password and auth plugin (known case)

change_mysql_plugin.sql

-- Plugin can be 'auth_socket', 'mysql_native_password' or 'caching_sha2_password'
SELECT user, authentication_string,plugin,host FROM mysql.user;

-- Change the auth socket plugin to mysql_native_password of root user
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';
FLUSH PRIVILEGES;

-- Create another user that uses caching_sha2_password as plugin
USE mysql;
CREATE USER '{user}'@'localhost' IDENTIFIED WITH caching_sha2_password BY '{password}';
GRANT ALL PRIVILEGES ON *.* TO '{user}'@'localhost';
FLUSH PRIVILEGES;
-- Replace {user} with the user that you want to create and {password} with the password that you want to set.

drop_user.sql

drop user {user}@localhost;

More info about granting and revoking privileges:

• https://chartio.com/resources/tutorials/how-to-grant-all-privileges-on-a-database-in-mysql
• https://www.prisma.io/dataguide/mysql/authentication-and-authorization/privilege-management