MySQL Cheatsheet for DevOps

Create User

CREATE USER 'user'@'host' IDENTIFIED WITH mysql_native_password BY 'password';

Grant Privilege Template

GRANT PRIVILEGE ON database.table TO 'user'@'host';

Grant All Privileges

GRANT ALL PRIVILEGES ON *.* TO 'user'@'host' WITH GRANT OPTION;

Full RDS admin grants:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'admin'@'%' WITH GRANT OPTION;

Full RDS user grants:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON *.* TO 'user'@'%';

Show Grants for User

SHOW GRANTS FOR 'user'@'host';

Drop User

DROP USER 'user'@'host';

Exit from MySQL

exit

Login

mysql -u user -h host --port=3306 -p

Backup

mysqldump -u user -h host database > database.sql -p

Restore

mysql -u user -h host database < database.sql -p

Reference:

• DigitalOcean: How To Create a New User and Grant Permissions in MySQL