MySQL Commands Cheatsheet

Here’s a MySQL Cheatsheet with commonly used commands for managing users, passwords, privileges, and checking permissions.

User Management

Create a User

CREATE USER 'username'@'host' IDENTIFIED BY 'password';
  • Replace username with the desired username.
  • Replace host with localhost, % (all hosts), or a specific IP/hostname.
  • Replace password with the desired password.

Change Password

ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

Delete a User

DROP USER 'username'@'host';

Grant and Revoke Privileges

Grant Privileges to a User

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';
  • Grants all privileges on database_name.

Grant Specific Privileges

GRANT SELECT, INSERT ON database_name.* TO 'username'@'host';
  • Grants only SELECT and INSERT privileges.

Revoke Privileges

REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'host';

Apply Changes to Privileges

FLUSH PRIVILEGES;

Checking Permissions

Show Privileges for a Specific User

SHOW GRANTS FOR 'username'@'host';

Show All Users and Their Hosts

SELECT user, host FROM mysql.user;

Check Database Privileges

SELECT User, Host, Db, Select_priv, Insert_priv, Update_priv, Delete_priv
FROM mysql.db
WHERE Db = 'database_name';

Check Table Privileges

SELECT * FROM mysql.tables_priv WHERE Db = 'database_name';

Password and Authentication

View Stored Password Hashes

SELECT user, host, authentication_string FROM mysql.user;
  • Displays hashed passwords; plain-text passwords are not retrievable.

Reset Password (If Forgotten)

ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;

Backup and Restore

Backup a Database

mysqldump -u root -p database_name > backup_file.sql

Restore a Database

mysql -u root -p database_name < backup_file.sql

Host Management

Change a User’s Host

Replace the old host (host1) with a new host (host2):

UPDATE mysql.user
SET Host = 'host2'
WHERE Host = 'host1' AND User = 'username';
FLUSH PRIVILEGES;

Duplicate User for a New Host

INSERT INTO mysql.user (Host, User, authentication_string, plugin)
SELECT 'host2', User, authentication_string, plugin
FROM mysql.user
WHERE Host = 'host1';
FLUSH PRIVILEGES;

Troubleshooting

Access Denied Error

Check if the user exists:

SELECT User, Host FROM mysql.user WHERE User = 'username';

If the user doesn’t exist, create it:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

Check MySQL Version

SELECT VERSION();

Show Current User

SELECT USER();

Security Tips

  • Avoid Plain-Text Passwords: Store credentials securely using a secrets manager.
  • Restrict Hosts: Use specific IP addresses or localhost instead of % for better security.
  • Backup Regularly: Always back up user data (mysql.user) before making changes.

This cheatsheet covers essential commands for managing users, privileges, and permissions in MySQL.