Common MySQL Statements for Creating Databases, Users and Granting Privileges

A quick reference for MySQL admin statements: create a utf8mb4 database, add/remove users, change passwords, grant/inspect privileges, and local vs remote login.

Create a database

create database DBNAME default character set utf8mb4 collate utf8mb4_unicode_ci;  -- in utf8mb4
create database if not exists DBNAME default charset utf8 collate utf8_general_ci;  -- utf8 if absent

Create / drop users

create user 'USER'@'localhost' identified by 'PASSWORD';
drop user USER@localhost;
drop user USER@'%';   -- drop a user allowed from any host

Change password

-- Method 1, takes effect immediately:
set password for USER = password('1122');
-- Method 2, needs flush:
update mysql.user set password = password('1234') where user = 'USER';
flush privileges;

Grant privileges

grant PRIV on DB.* to USER@HOST identified by "PASSWORD";
grant all privileges on DB.* to USER@localhost identified by 'PASSWORD';
flush privileges;       -- reload the privilege tables
show grants for USER;   -- view a user''s privileges

Note: localhost means local login only; for remote login change localhost to %, meaning any machine, or specify a particular host.