MySQL & PostgreSQL CLI Cheat Sheet

The mysql and psql command-line clients side by side. For SQL itself, see the SQL sheet. In psql, backslash commands need no ;. SQL statements always end with ;.

39 entries
8 sections
Updated 1 Oct 2026
Click a command to see all its options

F

39 entries

On this page

Connect #

Task	MySQL / MariaDB	PostgreSQL
Connect
Remote / port / db
Connection URL	`mysql://user:pw@host:3306/db`
As the system admin (Linux)
Quit	`exit` or `\q`	`\q`
Help	`help`	`\?` (psql) / `\h SELECT` (SQL)

Explore #

Task	MySQL	PostgreSQL
List databases	`SHOW DATABASES;`	`\l`
Switch database	`USE app;`	`\c app`
Current database	`SELECT DATABASE();`	`SELECT current_database();`
List tables	`SHOW TABLES;`	`\dt`
Describe a table	`DESCRIBE users;`	`\d users`
Show CREATE statement	`SHOW CREATE TABLE users;`	`\d+ users`
List users / roles	`SELECT user, host FROM mysql.user;`	`\du`
List indexes	`SHOW INDEX FROM users;`	`\di`
Running queries	`SHOW PROCESSLIST;`	`SELECT * FROM pg_stat_activity;`
Server version	`SELECT VERSION();`	`SELECT version();`

Client Tricks #

Task	MySQL	PostgreSQL
Vertical (row-per-line) output	end with `\G` instead of `;`	`\x` (toggle)
Show query time	shown automatically	`\timing`
Run a .sql file
Edit the query in an editor	`\e`	`\e`
Export to CSV	`SELECT ... INTO OUTFILE '/tmp/x.csv'`	`\copy (SELECT ...) TO 'x.csv' CSV HEADER`
Clear the current input	`\c`	`\r`

Create a Database & User #

-- MySQL
CREATE DATABASE app CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'app'@'%' IDENTIFIED BY 'strong-password';
GRANT ALL PRIVILEGES ON app.* TO 'app'@'%';

-- PostgreSQL
CREATE USER app WITH PASSWORD 'strong-password';
CREATE DATABASE app OWNER app;

Backup & Restore #

Task	MySQL	PostgreSQL
Dump one database
Compressed / custom format
Restore
Restore custom format	—
Dump everything

SQL Dialect Differences #

Feature	MySQL	PostgreSQL
Auto-increment id	`id INT AUTO_INCREMENT PRIMARY KEY`	`id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY` (or `SERIAL`)
Quote identifiers	`order`	`"order"`
Join strings	`CONCAT(a, b)`	`a \|\| b` or `CONCAT(a, b)`
Upsert	`INSERT ... ON DUPLICATE KEY UPDATE x = VALUES(x)`	`INSERT ... ON CONFLICT (id) DO UPDATE SET x = EXCLUDED.x`
JSON	`JSON`	`JSON` / `JSONB` (indexable)
Boolean	`TINYINT(1)` (alias `BOOLEAN`)	`BOOLEAN`
Case-insensitive LIKE	default collation	`ILIKE`
Return inserted row	—	`INSERT ... RETURNING id`

Run It Quickly with Docker #

docker run -d --name pg -e POSTGRES_PASSWORD=secret -p 5432:5432 postgres:17
docker run -d --name mysql -e MYSQL_ROOT_PASSWORD=secret -p 3306:3306 mysql:8.4
docker exec -it pg psql -U postgres
docker exec -it mysql mysql -u root -p

Service Control (Linux) #

Task	MySQL	PostgreSQL
Status
Restart
Config file	`/etc/mysql/my.cnf`	`/etc/postgresql/<version>/main/postgresql.conf`
Allow remote access	`bind-address = 0.0.0.0`	`listen_addresses = '*'` + `pg_hba.conf`

Option	What it does
`-u root`	User name
`-p`	Ask for the password (`-pSecret` without a space works but is unsafe)
`-h db.example.com`	Host (default localhost)
`-P 3306`	Port (capital P)
`-D app` / `app`	Database to use
`-e "SHOW DATABASES;"`	Run a statement and exit
`< dump.sql`	Run a SQL file: `mysql -u root -p app < dump.sql`
`-B` / `-N`	Batch (tab-separated) / no column names (scripts)
`-t`	Table output even when piped
`--protocol=tcp`	Force TCP instead of the local socket
`--ssl-mode=REQUIRED`	Require an encrypted connection
`--defaults-file=my.cnf`	Read options from a file

Option	What it does
`-U postgres`	User name
`-h db.example.com`	Host
`-p 5432`	Port
`-d app`	Database
`"postgresql://user:pw@host:5432/db"`	Connection URL
`-c "SELECT 1"`	Run a command and exit
`-f file.sql`	Run a SQL file
`-W`	Always ask for the password
`-l`	List databases and exit
`-A -t`	Unaligned, tuples only (clean output for scripts)
`-F ","`	Field separator with `-A`
`-x`	Expanded output (one field per line)
`-v ON_ERROR_STOP=1`	Stop a script at the first error
`--csv`	CSV output

Option	What it does
`sudo command`	Run one command as root (asks your password)
`-i`	Root login shell (`exit` to leave)
`-s`	Root shell (non-login, stays in your current folder)
`-u user`	Run as another user: `sudo -u postgres psql`
`-E`	Keep your environment variables
`-l`	List what you are allowed to run
`-v`	Refresh the password timeout
`-k`	Forget the password now (ask again next time)
`-e file`	Edit a file as root safely (`sudoedit`)
`sudo !!`	Run the previous command again as root

Usage	What it does
`source file.sql`	MySQL / MariaDB: run every statement in the file
`\. file.sql`	MySQL: short form
`\i file.sql`	PostgreSQL (`psql`) equivalent
`\ir file.sql`	psql: path relative to the current script
`mysql -u root -p db < file.sql`	Same from your normal terminal (MySQL)
`psql -d db -f file.sql`	Same from your normal terminal (PostgreSQL)

Option	What it does
`-u root -p`	Login
`--all-databases` (`-A`)	Every database
`--databases a b` (`-B`)	Several databases (includes `CREATE DATABASE`)
`--single-transaction`	Consistent dump without locking InnoDB tables
`--no-data` (`-d`)	Structure only
`--no-create-info` (`-t`)	Data only
`--routines --triggers --events`	Include stored code
`--ignore-table=db.logs`	Skip a table
`--where="id > 1000"`	Only some rows
`--add-drop-table`	`DROP TABLE` before every `CREATE` (default on)
`\| gzip > db.sql.gz`	Compress on the fly

Option	What it does
`-U postgres -h host`	Login
`-f file.sql`	Output file (otherwise to the screen)
`-Fc`	Custom format: compressed, restore with `pg_restore`
`-Fd -j 4 -f dir`	Directory format, 4 jobs in parallel
`-s`	Schema only
`-a`	Data only
`-t users` / `-T logs`	Only / skip a table
`-n public`	Only a schema
`--no-owner`	Don't set owners (restore as another user)
`-C`	Include `CREATE DATABASE`
`--clean --if-exists`	Drop objects before recreating them

Command / option	What it does
`status nginx`	Is it running? Recent log lines
`start` / `stop` / `restart`	Start / stop / restart now
`reload`	Reload the config without stopping
`enable` / `disable`	Start / don't start at boot
`enable --now`	Enable at boot and start right away
`is-active` / `is-enabled`	Yes/no answer (for scripts)
`list-units --type=service`	All loaded services
`--failed`	Services that crashed
`daemon-reload`	Re-read service files after editing them
`cat nginx` / `edit nginx`	Show / override a service file
`mask` / `unmask`	Block a service completely / undo
`--user`	Manage your own user services
`--no-pager`, `-l`	Plain output, full lines
`reboot` / `poweroff`	Restart / shut down the machine