MySQL Reference

MySQL administration and the key differences from PostgreSQL.

Connection and CLI

Connect with the mysql client

# Local connection
mysql -u root -p myapp

# Remote connection
mysql -h db.example.com -u appuser -p myapp

# Execute and exit
mysql -u root -e "SHOW DATABASES;"

Useful commands inside the mysql shell

mysql -u root -p <<'SQL'
SHOW DATABASES;
USE myapp;
SHOW TABLES;
DESCRIBE devices;               -- column names, types, keys
SHOW CREATE TABLE devices\G     -- full CREATE statement (\G vertical)
SHOW INDEX FROM devices;
SHOW PROCESSLIST;
STATUS;                          -- connection info, uptime, version
SQL

\G at the end of a statement displays results vertically — one field per line. Useful for wide tables.

Database and User Management

Create database and user

CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE USER 'appuser'@'%' IDENTIFIED BY 'changeme';
GRANT ALL PRIVILEGES ON myapp.* TO 'appuser'@'%';
FLUSH PRIVILEGES;

Read-only user

CREATE USER 'readonly'@'%' IDENTIFIED BY 'changeme';
GRANT SELECT ON myapp.* TO 'readonly'@'%';
FLUSH PRIVILEGES;

Show grants for a user

SHOW GRANTS FOR 'appuser'@'%';

MySQL vs PostgreSQL Differences

Key syntax differences you will encounter when moving between the two:

AUTO_INCREMENT vs SERIAL

-- MySQL
CREATE TABLE devices (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    hostname    VARCHAR(255) NOT NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- PostgreSQL equivalent
CREATE TABLE devices (
    id          SERIAL PRIMARY KEY,
    hostname    VARCHAR(255) NOT NULL,
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

LIMIT with OFFSET

-- MySQL: LIMIT offset, count
SELECT * FROM devices LIMIT 20, 10;

-- Standard SQL (also works in MySQL 8+): LIMIT count OFFSET skip
SELECT * FROM devices LIMIT 10 OFFSET 20;

String quoting

-- MySQL: backticks for identifiers, single or double quotes for strings
SELECT `hostname` FROM `devices` WHERE `type` = 'switch';

-- PostgreSQL: double quotes for identifiers, single quotes for strings
SELECT "hostname" FROM "devices" WHERE "type" = 'switch';

Storage Engines

InnoDB vs MyISAM

-- Check engine for a table
SHOW TABLE STATUS WHERE Name = 'devices';

-- Convert to InnoDB (always prefer InnoDB)
ALTER TABLE legacy_table ENGINE = InnoDB;

InnoDB: transactions, row-level locking, foreign keys, crash recovery. Use InnoDB for everything. MyISAM is legacy — no transactions, table-level locking, no FK enforcement.

Backup and Restore

mysqldump — logical backup

# Single database
mysqldump -u root -p myapp > myapp_$(date +%F).sql

# Single database, compressed
mysqldump -u root -p myapp | gzip > myapp_$(date +%F).sql.gz

# Schema only
mysqldump -u root -p --no-data myapp > schema.sql

# Specific table
mysqldump -u root -p myapp devices > devices.sql

# All databases
mysqldump -u root -p --all-databases > full_$(date +%F).sql

Restore from dump

mysql -u root -p myapp < myapp_2026-04-10.sql

# Compressed
zcat myapp_2026-04-10.sql.gz | mysql -u root -p myapp

Performance

EXPLAIN — query execution plan

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- With actual execution stats (MySQL 8.0.18+)
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

Key columns in EXPLAIN output:

type: ALL (full scan, bad), ref (index lookup, good), const (single row, best)
key: which index is used, NULL means no index
rows: estimated rows examined

Show slow query log status

SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- Enable temporarily
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- seconds

Variables and Status

Server variables and status counters

-- Current settings
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- Runtime stats
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Uptime';