MySQL Cheatsheet

Whether you’re new to MySQL or an experienced user, this guide will provide you with the essential commands to make your work more efficient and productive. I’ll update this command list from time to time.

User Management

@'%' -> external access allowed
@'localhost' -> only access on the machine

List users

select * from mysql.user

Create user

CREATE USER 'user_name'@'%' IDENTIFIED BY 'my_password';

Add full permissions for database

GRANT ALL PRIVILEGES ON db_name.* TO 'user_name'@'%';

Read only permissions

GRANT SELECT ON db_name.* TO 'user_name'@'%';

Update host for user

UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='username'

Check permissions for user

SHOW GRANTS for 'user_name'@'%';

Remove permissions

REVOKE ALL PRIVILEGES ON db_name.* FROM 'user_name'@'%';

Delete user

DROP USER 'user_name'@'%';

Flush privileges

FLUSH PRIVILEGES;

Import/Export

Export data