🐬 MySQL Setup and Administration

What MySQL Is and Why It Matters

MySQL is an open-source relational database management system (RDBMS): software that stores data in structured tables of rows and columns, keeps those tables related to one another, and answers questions about them using SQL (Structured Query Language). It is the "M" in the classic LAMP stack (Linux, Apache, MySQL, PHP) and remains one of the most deployed databases on the planet.

💡 A useful analogy — the digital library. Picture MySQL as a well-run library. The server is the building that controls who gets in. A database is a wing of the library; a table is a themed shelf; a row is one book; and columns are the fields printed on each book's spine (title, author, year). An index is the card catalog that lets you find a book without walking every aisle, and SQL is how you ask the librarian to fetch, add, or reshelve books. As a developer you are both the architect who designs the library and the patron who queries it.

Why developers reach for MySQL

• Mature and reliable — decades of production use across every industry
• Fast — the default InnoDB storage engine is tuned for mixed read/write web workloads
• Scales with you — from a hobby project to replicated multi-server clusters
• Cross-platform & free — the Community Edition runs everywhere at no cost
• Huge ecosystem — first-class support in PHP, Node.js, Python, and every major ORM

Installing MySQL

Pick the path for your operating system. On all three you are installing the MySQL Server 8.0+ and, ideally, a client to talk to it.

Windows

1. Download the MySQL Installer from dev.mysql.com.
2. Choose the Developer Default setup (server, Workbench, and connectors) or Server only if you just want the engine.
3. During configuration, set the root password, keep the default port 3306, and register MySQL as a Windows service so it starts on boot.

macOS (Homebrew — recommended)

# Install Homebrew if you don't have it
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

# Install and start MySQL
brew install mysql
brew services start mysql

# Lock it down (see next section)
mysql_secure_installation

Linux (Ubuntu / Debian)

# Refresh the package index and install the server
sudo apt update
sudo apt install mysql-server

# Start it now and enable it at boot
sudo systemctl start mysql
sudo systemctl enable mysql

# Lock it down
sudo mysql_secure_installation

Verify the install

# Confirm the client version
mysql --version

# Connect as root (you'll be prompted for the password)
mysql -u root -p

Once connected you'll see the mysql> prompt. Try a couple of commands:

SHOW DATABASES;
SELECT VERSION();
EXIT;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

Securing & Configuring the Server

Run the security script first

Before anything else, run mysql_secure_installation. It walks you through setting a strong root password, removing anonymous accounts, disabling remote root login, and dropping the test database — the four settings that cause the most beginner breaches.

The configuration file

MySQL reads its startup options from my.cnf (Linux/macOS) or my.ini (Windows). Common locations are /etc/mysql/my.cnf, /opt/homebrew/etc/my.cnf, or C:\ProgramData\MySQL\MySQL Server 8.0\my.ini.

[mysqld]
# Network
port = 3306
bind-address = 127.0.0.1        # local-only; use 0.0.0.0 to accept remote connections

# Character set — utf8mb4 stores full Unicode incl. emoji
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# InnoDB storage engine
innodb_buffer_pool_size = 512M   # cache; 50-70% of RAM on a dedicated DB server
innodb_redo_log_capacity = 128M  # MySQL 8.0.30+ replaces innodb_log_file_size

# Connections
max_connections = 150
wait_timeout = 600               # drop idle web connections after 10 min

[client]
default-character-set = utf8mb4

SHOW VARIABLES LIKE 'character_set_server';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Users & the Privilege System

Never let your application connect as root. Instead, create a dedicated user with exactly the privileges it needs. MySQL's privilege model is hierarchical — grants can apply globally, per-database, per-table, or even per-column.

Create a least-privilege application user

-- Create the account (localhost = only local connections)
CREATE USER 'webuser'@'localhost' IDENTIFIED BY 'ChangeMe_Str0ng!';

-- Grant only the data operations the app needs on ONE database
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'webuser'@'localhost';

-- Apply the changes
FLUSH PRIVILEGES;

Inspect, modify, and remove users

-- Who exists?
SELECT user, host FROM mysql.user;

-- What can this user do?
SHOW GRANTS FOR 'webuser'@'localhost';

-- Take a privilege away
REVOKE DELETE ON myapp_db.* FROM 'webuser'@'localhost';

-- Rotate a password
ALTER USER 'webuser'@'localhost' IDENTIFIED BY 'NewStr0ng_Pass!';

-- Remove an account entirely
DROP USER 'webuser'@'localhost';

Creating Databases & Tables

Create a database

-- Always specify the character set explicitly
CREATE DATABASE myapp_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE myapp_db;          -- make it the active database
SELECT DATABASE();     -- confirm which one is active

Design related tables

Here is a two-table schema linked by a foreign key: each post belongs to one user, and deleting a user cascades to delete their posts.

CREATE TABLE users (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    username       VARCHAR(50)  NOT NULL UNIQUE,
    email          VARCHAR(100) NOT NULL UNIQUE,
    password_hash  VARCHAR(255) NOT NULL,
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                       ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE posts (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    user_id     INT NOT NULL,
    title       VARCHAR(200) NOT NULL,
    content     TEXT,
    published   BOOLEAN DEFAULT FALSE,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

Modify and inspect tables

ALTER TABLE users ADD COLUMN last_login DATETIME;      -- add a column
ALTER TABLE posts MODIFY title VARCHAR(300) NOT NULL;   -- change a column
CREATE INDEX idx_posts_user_id ON posts(user_id);       -- speed up joins

DESCRIBE users;            -- column summary
SHOW CREATE TABLE posts;   -- the exact DDL
SHOW TABLES;               -- list everything

Backup & Recovery

A database you can't restore is a disaster waiting to happen. The everyday tool for logical backups (a file of SQL statements that rebuilds your data) is mysqldump.

# Back up one database
mysqldump -u root -p myapp_db > myapp_backup.sql

# Back up and compress in one step
mysqldump -u root -p myapp_db | gzip > myapp_backup.sql.gz

# Back up specific tables only
mysqldump -u root -p myapp_db users posts > tables.sql

# Restore into an (already created) database
mysql -u root -p myapp_db < myapp_backup.sql

# Restore from a compressed backup
gunzip < myapp_backup.sql.gz | mysql -u root -p myapp_db

Automate it

On Linux/macOS, a tiny script plus a cron job keeps rolling backups and prunes old ones:

#!/bin/bash
# nightly-backup.sh
DB_USER="backup_user"
DB_NAME="myapp_db"
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y-%m-%d_%H-%M-%S)
FILE="$BACKUP_DIR/$DB_NAME-$DATE.sql.gz"

mkdir -p "$BACKUP_DIR"

# Read the password from an env var, never hardcode it
if mysqldump -u "$DB_USER" -p"$MYSQL_PWD" --single-transaction "$DB_NAME" | gzip > "$FILE"; then
  echo "Backup OK: $FILE"
else
  echo "Backup FAILED" >&2
  exit 1
fi

# Delete backups older than 30 days
find "$BACKUP_DIR" -name "$DB_NAME-*.sql.gz" -mtime +30 -delete

Indexing & Query Analysis

The single biggest performance lever you control is indexing. An index is a sorted lookup structure that lets MySQL jump straight to matching rows instead of scanning the whole table — the card catalog from our library analogy.

CREATE INDEX idx_username ON users(username);            -- single column
CREATE INDEX idx_name ON users(last_name, first_name);   -- composite
CREATE UNIQUE INDEX idx_email ON users(email);           -- also enforces uniqueness

SHOW INDEX FROM users;         -- what indexes exist?
DROP INDEX idx_username ON users;

Write queries that can use indexes

EXPLAIN: see how MySQL runs a query

Prefix any query with EXPLAIN to get the execution plan without running it.

EXPLAIN SELECT u.username, p.title
FROM users u
JOIN posts p ON u.id = p.user_id
WHERE p.created_at > '2026-01-01';

Hands-on Exercise

CREATE DATABASE web_dev_course
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE web_dev_course;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT NOT NULL,
    user_id INT NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE INDEX idx_comments_post_id ON comments(post_id);

CREATE USER 'course_user'@'localhost' IDENTIFIED BY 'S0me_Str0ng!';
GRANT SELECT, INSERT, UPDATE, DELETE ON web_dev_course.* TO 'course_user'@'localhost';
FLUSH PRIVILEGES;

EXPLAIN SELECT p.title, c.content
FROM posts p JOIN comments c ON p.id = c.post_id;

# Backup and restore
mysqldump -u root -p web_dev_course | gzip > course.sql.gz
mysql -u root -p -e "CREATE DATABASE web_dev_restore CHARACTER SET utf8mb4;"
gunzip < course.sql.gz | mysql -u root -p web_dev_restore

Summary & Quiz

📚 Further Reading

• MySQL 8.0 Reference Manual
• MySQL — Optimization & Indexing
• MySQL Workbench (GUI admin tool)

🚀 What's Next?

Your database is running and secured. Next we'll connect it to code: in PHP and MySQL Integration you'll open connections with MySQLi and PDO, run queries, and process results from PHP.