Useful commands for MySQL

Some useful MySQL commands that I need from time to time:

List all users

select User, Host from mysql.user;

Remove a user

show grants for 'USERNAME'@'localhost';
revoke all privileges, grant option from 'USERNAME'@'localhost';
drop user 'USERNAME'@'localhost';

List all databases

show databases;

Remove a database

drop database DATABASENAME;

Update password of user

update mysql.user set password=PASSWORD("NEWPASSWORD") where User = 'USERNAME';
flush privileges;

Create a database and user, assign user to database

create database DATABASE default character set utf8 collate utf8_general_ci;
grant all on DATABASE.* to 'USERNAME'@'localhost' identified by 'PASSWORD';
flush privileges;

Dump a database

mysqldump --user="<USERNAME>" --password="<PASSWORD>" <DATABASE> --quick --lock-tables --add-drop-table | gzip > /tmp/<NAME>.sql.gz