Installing MySQL and Connecting

Pick a version and stick to it#

Use MySQL 8.0 or a later LTS release. This course relies on features that older 5.7 lacks: window functions, CTEs, and CHECK constraints that are actually enforced. If you see a tutorial using 5.7, treat it as dated.

Installing#

macOS (Homebrew):

brew install mysql
brew services start mysql

Ubuntu / Debian:

sudo apt update
sudo apt install mysql-server
sudo systemctl start mysql

Windows: use the MySQL Installer from the official site and choose "Server only" plus Workbench. Remember the root password you set. Nobody can recover it for you without a reset procedure.

Docker (my usual choice for practice, since it's disposable):

docker run --name mysql-lab -e MYSQL_ROOT_PASSWORD=secret \
  -p 3306:3306 -d mysql:8

Connecting with the command-line client#

mysql -u root -p

You'll be prompted for the password. Don't put it on the command line with -psecret; it lands in your shell history and the process list.

Once connected:

SELECT VERSION();
SHOW DATABASES;
SELECT CURRENT_USER(), DATABASE();

DATABASE() returns NULL until you pick one with USE shop;. Forgetting USE is the number one source of "No database selected" errors.

What happens when you connect#

 mysql client ──TCP 3306──▶ mysqld
      │                       │ 1. authenticate user@host
      │                       │ 2. create a thread for the session
      ◀──── result set ───────│ 3. parse, optimize, execute each statement

Each connection gets its own session with its own variables, current database, and transaction state. Closing the client ends the session.

Workbench#

MySQL Workbench is a GUI with a query editor, schema browser, and visual EXPLAIN. Create a connection to 127.0.0.1:3306, user root, then open a SQL tab. Use it to explore, but get comfortable with the CLI. Servers you'll maintain later usually only give you a terminal.

Statements end with a semicolon#

The client waits for ; before sending. If the prompt changes to ->, you left a statement open. Type ; to finish it, or \c to cancel.

Common mistakes#

• Connecting to localhost and wondering why TCP settings are ignored. On Unix, localhost uses a socket file; 127.0.0.1 forces TCP.
• Running everything as root. Fine for practice, wrong for applications (Module 7 fixes this).
• Port 3306 already taken by an older install.

Practice#

Start a MySQL server using whichever method fits your machine. Connect with the CLI, run SELECT VERSION();, then create a database called shop with CREATE DATABASE shop; and switch to it. Confirm SELECT DATABASE(); returns shop. Then connect a second time from Workbench and run SHOW PROCESSLIST; to see both sessions.