Skip to content

Instantly share code, notes, and snippets.

@poudelmadhav

poudelmadhav/change_mysql_plugin.sql

Last active April 10, 2025 11:22
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save poudelmadhav/3a3310a70849c29846e8d81892e2fa9f to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save poudelmadhav/3a3310a70849c29846e8d81892e2fa9f to your computer and use it in GitHub Desktop.
Download ZIP
Change root mysql password and auth plugin (known case)
Raw
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 caching_sha2_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.
-- If you like to create user with auth_socket plugin do
USE mysql;
CREATE USER '{user}'@'localhost' IDENTIFIED WITH auth_socket;
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.
Raw
drop_user.sql
drop user {user}@localhost;
@poudelmadhav
Copy link
Author

poudelmadhav commented Sep 13, 2023 •
edited
Loading

More info about granting and revoking privileges:

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment