Chapter 1: Getting Started with MySQL

September 28, 2025 ยท View on GitHub

Installation Options

Getting MySQL running on your machine is straightforward. You have several paths depending on your preferences and operating system.

Official Installer

The simplest approach for most users is the official installer from dev.mysql.com/downloads. Download MySQL Community Server and follow the installer:

  1. Setup Type: Choose "Developer Default" for a complete development environment
  2. Configuration: Accept default port 3306 unless you have conflicts
  3. Root Password: Set a strong password and store it securely
  4. Windows Service/macOS Launch: Configure MySQL to start automatically

Package Managers

macOS with Homebrew:

brew install mysql
brew services start mysql
mysql_secure_installation

Ubuntu/Debian:

sudo apt update
sudo apt install mysql-server
sudo mysql_secure_installation

CentOS/RedHat/Fedora:

sudo yum install mysql-server
sudo systemctl start mysqld
sudo mysql_secure_installation

The mysql_secure_installation script helps you:

  • Set the root password
  • Remove anonymous users
  • Disable remote root login
  • Remove test database
  • Reload privilege tables

Docker

For isolated development environments:

docker run --name mysql-dev \
  -e MYSQL_ROOT_PASSWORD=yourpassword \
  -p 3306:3306 \
  -d mysql:8.0

# Connect to the container
docker exec -it mysql-dev mysql -p

Cloud Services

AWS RDS, Google Cloud SQL, and Azure Database for MySQL provide managed instances. You get a connection string and skip server management entirely. Great for learning without installation hassles.

Connecting to MySQL

Command Line

The most direct way to interact with MySQL:

mysql -u root -p

You'll see the MySQL prompt:

mysql>

This prompt is where you'll type SQL commands. Every statement ends with a semicolon.

Connection Troubleshooting

"Command not found": Add MySQL's bin directory to your PATH or use the full path:

/usr/local/mysql/bin/mysql -u root -p

"Access denied": Verify your password. If forgotten, you'll need to reset it following MySQL's password recovery procedure for your OS.

"Can't connect to local MySQL server": MySQL isn't running. Start it with:

  • macOS: brew services start mysql or System Preferences
  • Linux: sudo systemctl start mysql
  • Windows: Services panel or net start MySQL80

Creating Your First Database

Let's build a database for a coffee shop. This practical example will demonstrate core concepts:

CREATE DATABASE coffee_shop;

View all databases:

SHOW DATABASES;

You'll see system databases plus your new one:

  • information_schema: Metadata about all databases
  • mysql: User accounts and privileges
  • performance_schema: Performance monitoring
  • sys: Simplified performance schema views
  • coffee_shop: Your database

Select your database:

USE coffee_shop;

Creating Tables

Databases organize data in tables. Let's create a customers table:

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Breaking down the column definitions:

  • INT: Integer numbers
  • AUTO_INCREMENT: Automatically generates incrementing values
  • PRIMARY KEY: Unique identifier for each row
  • VARCHAR(n): Variable-length text up to n characters
  • NOT NULL: Required field
  • UNIQUE: No duplicates allowed
  • TIMESTAMP: Date and time
  • DEFAULT CURRENT_TIMESTAMP: Automatically sets current time

View table structure:

DESCRIBE customers;

Or see the complete CREATE statement:

SHOW CREATE TABLE customers;

Inserting Data

Add your first customer:

INSERT INTO customers (first_name, last_name, email, phone)
VALUES ('Jane', 'Doe', 'jane@example.com', '555-0101');

Insert multiple rows:

INSERT INTO customers (first_name, last_name, email, phone) VALUES
    ('John', 'Smith', 'john@example.com', '555-0102'),
    ('Alice', 'Johnson', 'alice@example.com', '555-0103'),
    ('Bob', 'Williams', 'bob@example.com', NULL);

Note: We don't specify id or created_at - MySQL handles these automatically.

Querying Data

Retrieve all data:

SELECT * FROM customers;

Be selective with columns:

SELECT first_name, last_name, email FROM customers;

Filter results:

SELECT * FROM customers WHERE phone IS NOT NULL;

Sort results:

SELECT * FROM customers ORDER BY last_name, first_name;

Combine conditions:

SELECT first_name, last_name, email
FROM customers
WHERE phone IS NOT NULL
  AND created_at >= '2024-01-01'
ORDER BY last_name;

MySQL Workbench

While the command line is powerful, MySQL Workbench provides a visual interface for database work.

Installation

Download from dev.mysql.com/downloads/workbench. It's free and available for all major platforms.

Key Features

Visual Query Builder: Construct queries by dragging and dropping tables and columns.

Schema Designer: Create entity-relationship diagrams to visualize database structure.

SQL Editor: Syntax highlighting, auto-completion, and query formatting.

Performance Dashboard: Monitor queries, connections, and server health in real-time.

Data Import/Export: Move data between MySQL and CSV, JSON, or SQL files.

Connecting Workbench to MySQL

  1. Click the + next to "MySQL Connections"
  2. Enter connection details:
    • Connection Name: "Local Development"
    • Hostname: localhost
    • Port: 3306
    • Username: root
  3. Test Connection
  4. Save and connect

Understanding MySQL Architecture

Storage Engines

MySQL's pluggable storage engine architecture lets you choose the right engine for your needs:

InnoDB (default and recommended):

  • ACID compliant transactions
  • Foreign key constraints
  • Crash recovery
  • Row-level locking
  • Best for most applications

MyISAM (legacy):

  • Fast reads
  • Table-level locking
  • No transactions
  • Smaller storage footprint

Memory:

  • Data stored in RAM
  • Extremely fast
  • Data lost on restart
  • Good for temporary tables

Check available engines:

SHOW ENGINES;

Data Directory

MySQL stores databases in its data directory:

  • Linux: /var/lib/mysql/
  • macOS: /usr/local/var/mysql/
  • Windows: C:\ProgramData\MySQL\MySQL Server 8.0\Data\

Each database is a subdirectory containing:

  • Table structure definitions
  • Data files
  • Index files

Never modify these files directly - always use SQL commands.

Essential Commands Reference

Database Operations

SHOW DATABASES;                    -- List all databases
CREATE DATABASE dbname;            -- Create new database
USE dbname;                        -- Switch database
DROP DATABASE dbname;              -- Delete database

Table Operations

SHOW TABLES;                       -- List tables
DESCRIBE tablename;                -- Show structure
SHOW CREATE TABLE tablename;       -- Show CREATE statement
ALTER TABLE tablename ...;         -- Modify structure
DROP TABLE tablename;              -- Delete table
TRUNCATE TABLE tablename;          -- Empty table

Data Operations

SELECT ... FROM tablename;         -- Query data
INSERT INTO tablename ...;         -- Add rows
UPDATE tablename SET ...;          -- Modify rows
DELETE FROM tablename WHERE ...;   -- Remove rows

Utility Commands

SELECT VERSION();                  -- MySQL version
SELECT USER();                     -- Current user
SELECT DATABASE();                 -- Current database
SHOW PROCESSLIST;                  -- Active connections
SHOW VARIABLES;                    -- Server configuration
EXIT;                              -- Leave MySQL prompt

Practice Project: Coffee Shop Menu

Let's create a complete menu system:

-- Products table
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category ENUM('coffee', 'tea', 'pastry', 'sandwich') NOT NULL,
    price DECIMAL(5,2) NOT NULL,
    description TEXT,
    available BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert menu items
INSERT INTO products (name, category, price, description) VALUES
    ('Espresso', 'coffee', 2.50, 'Strong Italian coffee'),
    ('Cappuccino', 'coffee', 3.50, 'Espresso with steamed milk foam'),
    ('Latte', 'coffee', 4.00, 'Espresso with steamed milk'),
    ('Americano', 'coffee', 2.75, 'Espresso with hot water'),
    ('Earl Grey', 'tea', 2.00, 'Classic English tea'),
    ('Green Tea', 'tea', 2.00, 'Japanese sencha'),
    ('Croissant', 'pastry', 3.00, 'Buttery French pastry'),
    ('Blueberry Muffin', 'pastry', 3.50, 'Fresh baked daily'),
    ('BLT Sandwich', 'sandwich', 7.50, 'Bacon, lettuce, tomato'),
    ('Grilled Cheese', 'sandwich', 6.00, 'Classic comfort food');

-- Useful queries
-- Available coffee drinks
SELECT name, price, description
FROM products
WHERE category = 'coffee' AND available = TRUE
ORDER BY price;

-- Average price by category
SELECT category, AVG(price) as avg_price, COUNT(*) as item_count
FROM products
GROUP BY category;

-- Most expensive items
SELECT name, category, price
FROM products
ORDER BY price DESC
LIMIT 5;

Common Beginner Issues

Case Sensitivity: SQL keywords are case-insensitive, but table/column names might be case-sensitive depending on your OS. Be consistent.

Quotes:

  • Backticks ` for identifiers: `table`, `column`
  • Single quotes ' for strings: 'value'
  • Double quotes " for strings only if ANSI_QUOTES is off

NULL Handling:

  • NULL represents missing/unknown data
  • Use IS NULL or IS NOT NULL for comparisons
  • NULL is not equal to empty string or zero

Reserved Words: If you must use a reserved word as an identifier, wrap it in backticks:

CREATE TABLE `order` (    -- ORDER is reserved
    `select` VARCHAR(50)  -- SELECT is reserved
);

Next Steps

You now have MySQL running and understand basic operations. You can:

  • Create and manage databases
  • Design and create tables
  • Insert and query data
  • Use both CLI and GUI tools

Chapter 2 will dive deep into SQL, exploring the full power of the query language. You'll learn to write complex queries, understand joins, and manipulate data like a professional.

Before moving on, try these exercises:

  1. Create an orders table linking customers and products
  2. Insert sample orders
  3. Write queries to find:
    • Total sales by category
    • Most frequent customers
    • Orders placed today

Next: Chapter 2: SQL Fundamentals