iwantcoding.com
🔥 Daily 👥 Rooms 🏆 Top Log in Sign up

Install / mysql CLI

By · Reviewed & last updated · Part of MySQL Tutorial

Installing MySQL or MariaDB locally: Docker, native, or managed. The basics for getting to a working dev database.

MySQL — install

EXAMPLE
# ===== Option 1: Docker =====
docker run -d --name mysql -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=rootpw \
  -e MYSQL_DATABASE=app \
  -e MYSQL_USER=dev -e MYSQL_PASSWORD=dev \
  -v mysql-data:/var/lib/mysql \
  mysql:8

# Connect:
mysql -h 127.0.0.1 -P 3306 -u dev -p app

# ===== Option 2: native =====
# macOS:
brew install mysql
brew services start mysql
mysql_secure_installation   # set root password, drop test DB

# Ubuntu / Debian:
sudo apt update && sudo apt install -y mysql-server
sudo mysql_secure_installation

# Windows: MSI installer from mysql.com (community edition).

# ===== Option 3: MariaDB drop-in =====
# Compatible with MySQL drivers for most workloads.
docker run -d --name mariadb -p 3306:3306 \
  -e MARIADB_ROOT_PASSWORD=rootpw \
  -e MARIADB_DATABASE=app \
  -v mariadb-data:/var/lib/mysql \
  mariadb:11

# ===== Option 4: managed =====
# AWS RDS for MySQL, Google Cloud SQL, Azure Database for MySQL,
# PlanetScale (Vitess-based), DigitalOcean Managed Databases.

# ===== Verify =====
mysql -V
# mysql  Ver 8.x ...

# ===== Hello, database =====
mysql -u dev -p

CREATE DATABASE IF NOT EXISTS app
  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE app;

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(254) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO users (email, name) VALUES ('a@x.io', 'Alex');
SELECT * FROM users;

# ===== Drivers =====
# Node:    npm i mysql2          (better default than mysql)
# Python:  pip install pymysql or mysqlclient
# Go:      github.com/go-sql-driver/mysql
# Java:    com.mysql:mysql-connector-j

# ===== Client tools =====
# mysql       canonical CLI
# mycli       autocomplete + syntax highlighting
# DBeaver     GUI
# TablePlus / Sequel Ace (macOS)

# ===== Patterns to internalise =====
# - utf8mb4 always; never plain utf8 (3-byte broken)
# - InnoDB by default; never MyISAM for new tables
# - Docker for dev; managed for prod where possible
# - Keep schema + data migrations in source control

# ===== Pitfalls =====
# - Default root creds left in dev; risky if the port is exposed
# - VARCHAR(255) on utf8mb4 with InnoDB row format limits
# - 0000-00-00 dates in legacy modes
# - FLOAT/DOUBLE for money -> rounding bugs (use DECIMAL)

Why it matters

For dev MySQL: Docker for ephemeral databases, native install for daily work, managed for production. utf8mb4 + InnoDB + DECIMAL for money + UUID/BIGINT IDs are the four defaults. Get those right and the gotchas mostly disappear.

Tip: Tweak the snippet with Try it Yourself », then sit the quiz at the bottom of the page.

Example

Example
# Ubuntu
sudo apt install mysql-server
# macOS
brew install mysql
# Connect
mysql -u root -p
Try it Yourself »

Discussion

Loading…