Useful MySQL Commands

Setting up this site required a MySQL database, so I’m documenting some commonly used commands.

Connect to MySQL as root user in terminal
mysql -u root -p

Show all databases
SHOW DATABASES;

Create database XXXXX
CREATE DATABASE XXXXX;

Select database XXXXX
USE XXXXX;

Show all tables in current database
SHOW TABLES;

Create new user XXXXX with password YYYYY
CREATE USER 'XXXXX'@'localhost' IDENTIFIED BY 'YYYYY';

Allow user ZZZZZ to access database XXXXX (or * for all databases) and table YYYYY (or * for all tables)
GRANT ALL PRIVILEGES ON XXXXX . YYYYY TO 'ZZZZZ'@'localhost';

Reload permissions
FLUSH PRIVILEGES;