MySQL – Install and Connect

You need two things to follow this track: a MySQL server to connect to, and a client to type into. They are separate installs, and confusing them is the first thing that goes wrong.

The fastest route to a working server is Docker, because it is one command, it does not touch the rest of your machine, and deleting it afterwards is one more command.

A server in one command

docker run --name mysql-play \
  -e MYSQL_ALLOW_EMPTY_PASSWORD=yes \
  -e MYSQL_DATABASE=pizza \
  -p 3308:3306 \
  -d mysql:8.4

Four things worth understanding rather than copying:

• mysql:8.4 pins the LTS release. mysql:latest moves under you.
• MYSQL_ALLOW_EMPTY_PASSWORD is an explicit opt-in — the image refuses to start without a password decision. It is fine for a throwaway container on your laptop and nothing you would ever do elsewhere.
• MYSQL_DATABASE creates an empty database at first boot. Without it you connect successfully and then find there is nothing to connect to.
• -p 3308:3306 maps container port 3306 to 3308 on your machine. If you already have MySQL installed, 3306 is taken and publishing to a busy port fails at startup with a bind error that says nothing about MySQL.

Or, with a compose file, which is how the demo application does it:

services:
  mysql:
    image: mysql:8.4
    ports:
      - "3308:3306"
    environment:
      MYSQL_ALLOW_EMPTY_PASSWORD: "yes"
      MYSQL_DATABASE: pizza
    command:
      # utf8mb4 end to end. MySQL's older `utf8` is a 3-byte encoding that cannot
      # store an emoji, and a menu is exactly where someone will paste one.
      - --character-set-server=utf8mb4
      - --collation-server=utf8mb4_0900_ai_ci
    healthcheck:
      # Without this the container is "up" the instant the process starts, several
      # seconds before MySQL accepts a connection — so an app racing it dies on
      # connection refused.
      test: ["CMD", "mysqladmin", "ping", "-h", "127.0.0.1"]
      interval: 5s
      retries: 20
    volumes:
      - mysql-data:/var/lib/mysql

volumes:
  mysql-data:

The named volume is what makes the data survive docker compose down. Add -v to that command when you actually want it gone.

Installing it directly

If you would rather run it on the machine itself:

# macOS
brew install mysql
brew services start mysql

# Debian / Ubuntu
sudo apt update && sudo apt install mysql-server
sudo systemctl enable --now mysql
sudo mysql_secure_installation

# Windows: use the MySQL Installer from dev.mysql.com, which bundles server,
# client and Workbench.

On Linux, run mysql_secure_installation. It sets a root password and removes the anonymous accounts and the test database that older versions shipped with. On a server that is reachable from anywhere, it is not optional.

Just the client

If the server is elsewhere — a container, a colleague's machine, RDS — you only need the client:

brew install mysql-client              # macOS
sudo apt install mysql-client          # Debian / Ubuntu
docker exec -it mysql-play mysql -u root   # or just use the one inside the container

Connecting

mysql -h 127.0.0.1 -P 3308 -u root pizza

Use -h 127.0.0.1, not -h localhost. They are not synonyms to MySQL: localhost makes the client use a Unix socket file and ignore -P entirely, so it quietly tries the local server instead of your container. 127.0.0.1 forces TCP. A great deal of "the port is right and it still connects to the wrong server" is this.

Add -p (no argument) to be prompted for a password. Putting the password on the command line as -pSecret works and writes it into your shell history and the process list, where anyone on the machine can read it.

Checking it works

Once you are at the mysql> prompt:

SHOW DATABASES;
USE pizza;
SHOW TABLES;

DESCRIBE shows a table's shape, and is the command you will use most while finding your way around an unfamiliar schema:

DESCRIBE product;

+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | bigint       | NO   | PRI | NULL    | auto_increment |
| name          | varchar(120) | NO   | UNI | NULL    |                |
| description   | varchar(500) | YES  |     | NULL    |                |
| type          | varchar(20)  | NO   | MUL | NULL    |                |
| image_url     | varchar(500) | YES  |     | NULL    |                |
| active        | tinyint(1)   | NO   |     | 1       |                |
| display_order | int          | NO   |     | 0       |                |
| created_at    | datetime(6)  | NO   |     | NULL    |                |
| public_id     | char(36)     | NO   | UNI | NULL    |                |
| updated_at    | datetime(6)  | NO   |     | NULL    |                |
| deleted       | tinyint(1)   | NO   | MUL | 0       |                |
+---------------+--------------+------+-----+---------+----------------+

Already worth reading: PRI marks the primary key, UNI a unique constraint, tinyint(1) is how a BOOLEAN is really stored, and datetime(6) keeps microseconds. All of that is covered in data types.

And the "is the data actually there" check:

SELECT COUNT(*) AS products FROM product;

+----------+
| products |
+----------+
|       14 |
+----------+

Loading a schema from a file

mysql -h 127.0.0.1 -P 3308 -u root pizza < schema.sql        # from the shell
docker exec -i mysql-play mysql -u root pizza < schema.sql   # into a container

Note docker exec -i, not -it. The -t allocates a terminal, which is wrong when the input is a file and produces confusing failures.

A GUI, if you want one

MySQL Workbench is free and official; DBeaver and TablePlus are common alternatives. They are genuinely useful for browsing an unfamiliar schema and drawing relationship diagrams.

Learn the command line anyway. It is what exists on a production host at 2am, it is what every script and CI job uses, and every example in this track is written for it.

Common first errors

Next: how an application connects — JDBC URLs, pooling, and the timeouts that cause overnight failures.