Module 1 · The server

Install, connect & tools

Beginner 14 min · hands-on Docker route recommended

You need a real MySQL server for this course — SQLite will not show you InnoDB locking or EXPLAIN ANALYZE. Docker is the fastest, cleanest route and leaves nothing behind on your machine.

1. Docker (recommended)

docker run -d --name mysql84 \
  -e MYSQL_ROOT_PASSWORD=learn \
  -e MYSQL_DATABASE=retail \
  -p 3306:3306 \
  -v mysql84-data:/var/lib/mysql \
  mysql:8.4

docker logs -f mysql84          # wait for "ready for connections"
docker exec -it mysql84 mysql -uroot -plearn retail

What each flag does

-v mysql84-data:/var/lib/mysql keeps your data in a named volume, so removing the container does not delete the database. -p 3306:3306 exposes the standard port. On Windows PowerShell, replace the trailing \ line continuations with a backtick `, or put it all on one line.

2. Native install

Windows

MySQL Installer (MSI) from dev.mysql.com → "Server only". It installs a Windows service and optionally Workbench.

macOS

brew install [email protected] then brew services start [email protected], and run mysql_secure_installation.

Linux

Use the official MySQL APT/YUM repository rather than the distro package, which may be MariaDB or an old version.

3. Connecting

mysql -h 127.0.0.1 -P 3306 -u root -p retail     # prompts for the password

SELECT VERSION(), CURRENT_USER(), DATABASE();
SHOW DATABASES;
USE retail;
SHOW TABLES;
DESCRIBE customers;            -- or: SHOW CREATE TABLE customers\G
STATUS;                        -- connection, charset, uptime

\G is your friend

Ending a statement with \G instead of ; prints each row vertically, one column per line. Indispensable for wide output like SHOW ENGINE INNODB STATUS\G or EXPLAIN FORMAT=TREE …\G.

localhost vs 127.0.0.1

The mysql client treats -h localhost as "use the Unix socket file", not TCP. With Docker port-mapping there is no socket on your host, so use -h 127.0.0.1. This also matters for accounts: 'root'@'localhost' and 'root'@'%' are different accounts (lesson 03).

4. From Python

import os
import mysql.connector

con = mysql.connector.connect(
    host="127.0.0.1", port=3306,
    user="root", password=os.environ["MYSQL_PWD"],   # never hard-code passwords
    database="retail",
)
cur = con.cursor(dictionary=True)
cur.execute("SELECT category, COUNT(*) AS n FROM products WHERE unit_price > %s GROUP BY category",
            (1000,))                                   # parameters, never string formatting
for row in cur.fetchall():
    print(row)
con.close()

Autocommit is OFF by default in mysql-connector

Writes are not saved until you call con.commit(). Close the connection without committing and the changes are rolled back — a classic "my INSERT disappeared" moment.

5. Load the course database

The SQL course's retail schema and seed data load unchanged into MySQL:

docker exec -i mysql84 mysql -uroot -plearn retail < sql/project/schema.sql
docker exec -i mysql84 mysql -uroot -plearn retail < sql/project/seed.sql
docker exec -i mysql84 mysql -uroot -plearn retail -e "SELECT COUNT(*) FROM order_items"

docker cp sql/project/schema.sql mysql84:/tmp/schema.sql
docker cp sql/project/seed.sql   mysql84:/tmp/seed.sql
docker exec mysql84 sh -c 'mysql -uroot -plearn retail < /tmp/schema.sql'
docker exec mysql84 sh -c 'mysql -uroot -plearn retail < /tmp/seed.sql'

The MySQL project (lesson 22) generates a much larger version so the performance lessons are visible.

6. Tools worth knowing

Tool	For
`mysql` CLI	Everything; scripts; the one tool always present on a server
MySQL Shell (`mysqlsh`)	JS/Python/SQL modes, dump/load utilities, InnoDB Cluster admin
MySQL Workbench	GUI: queries, visual EXPLAIN, schema design, admin
DBeaver / DataGrip	Multi-database GUIs
Percona Toolkit	`pt-query-digest` for slow logs, `pt-online-schema-change`

7. Where the configuration lives

mysqld --verbose --help | grep -A1 "Default options"
# typically: /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf   (Windows: C:\ProgramData\MySQL\MySQL Server 8.4\my.ini)

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SELECT * FROM performance_schema.variables_info
WHERE  VARIABLE_NAME = 'innodb_buffer_pool_size';   -- shows WHERE the value came from
SET PERSIST max_connections = 300;                   -- change AND survive a restart (8.0+)

Recap

Docker + a named volume is the cleanest way to run MySQL for learning.
Use 127.0.0.1, not localhost, to force TCP to a container.
\G for vertical output; SHOW CREATE TABLE to see real definitions.
Parameterise queries from Python, and remember to commit().
SET PERSIST changes a variable and keeps it across restarts.

Checkpoint

1 · mysql -h localhost -u root -p fails to reach MySQL in Docker, but -h 127.0.0.1 works. Why?

It is a long-standing MySQL client convention. The container exposes a TCP port, not a socket file on your machine, so you need an IP address (or --protocol=TCP).

2 · Rows inserted from Python via mysql-connector are missing after the script ends. Most likely?

mysql-connector-python (per PEP 249) starts a transaction implicitly and does not autocommit by default. Commit explicitly, or use the connection as a context manager / enable autocommit deliberately.