MySQL Quick Reference

Shell Commands

Set up security requirements:

$ sudo mysql_secure_installation

Open the MySQL CLI client and login using a password and the specified user:

$ sudo mysql -u {user} -p

MySQL Queries and Commands

Note that MySQL will not execute anything until it encounters a semicolon ; which indicates the end of a statement. This allows us to enter multi-line statements.

Create User

> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Give User Access to a Database

> GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
> FLUSH PRIVILEGES;

Create a database called newStore:

> CREATE DATABASE IF NOT EXISTS newStore;

Create a new table in a database:

> CREATE TABLE IF NOT EXISTS newTable (
>   newTableID INT PRIMARY KEY AUTO_INCREMENT,
>   firstName VARCHAR(255) not null,
>   lastName VARCHAR(255) not null,
>   email VARCHAR(255) not null
> );

List all databases:

> SHOW DATABASES;

Connect to a database:

> USE newStore;

List tables in a database

> SHOW TABLES;

List columns in a table

> SHOW COLUMNS FROM newTable

Execute a sql script from file

> source /path/to/script.sql;

More Resources

• How To Create a New User and Grant Permissions in MySQL
• MySQL Documentation
• MariaDB Documentation