The Most Comprehensive MySQL Installation & Configuration Guide: Hands‑On First Steps

1. MySQL Installation Guide

Windows

Download the MSI installer from https://dev.mysql.com/downloads/installer/ and select the Windows (x86, 64-bit) package.

Run the installer, choose an installation type, and select “Developer Default”.

Configuration steps (example):

# Choose configuration type:
- Development Computer
- Server Computer
- Dedicated Computer
# Set the root password
# Set Windows service name (default MySQL80)
# Add the MySQL bin directory to the system PATH (recommended)

ZIP‑archive method:

# 1. Download the ZIP file and extract it
# 2. Create a <strong>my.ini</strong> file
basedir=C:/mysql-8.0.33
datadir=C:/mysql-8.0.33/data
port=3306
# 3. Initialise MySQL
mysqld --initialize-insecure --user=mysql
# 4. Install the service
mysqld --install MySQL80
# 5. Start the service
net start MySQL80

Linux (Ubuntu/Debian)

# 1. Update package index
sudo apt update
# 2. Install MySQL server
sudo apt install mysql-server -y
# 3. Start MySQL service
sudo systemctl start mysql
# 4. Enable autostart on boot
sudo systemctl enable mysql
# 5. Check service status
sudo systemctl status mysql
# 6. Run the security script
sudo mysql_secure_installation
#   Answer the prompts to enable password validation, set root password, remove anonymous users, optionally disable remote root login, delete test database, and reload privilege tables

macOS

Homebrew method (recommended):

# Install Homebrew if missing
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
# Install MySQL
brew install mysql
# Start MySQL service
brew services start mysql
# Run the secure installation script
mysql_secure_installation

DMG package method:

Download the macOS DMG installer.

Open the DMG and follow the wizard to install.

Open System Preferences, locate MySQL, and start the server.

2. Basic Configuration & Security Initialization

First login

# Common for Windows/Linux/macOS
# Log in as root
mysql -u root -p
# If the password is forgotten, stop the service, start MySQL in safe mode, and reset the password:
# Linux/macOS: sudo systemctl stop mysql
# Windows: services.msc → stop MySQL80
mysqld_safe --skip-grant-tables &
mysql -u root

Security configuration

# After logging in, run:
SELECT user, host, authentication_string FROM mysql.user;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'StrongPassword123!';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
DELETE FROM mysql.user WHERE User='';
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\_%';

Configuration file optimisation

# Example my.cnf / my.ini
[mysqld]
# Basic settings
port=3306
socket=/var/lib/mysql/mysql.sock
# Memory optimisation (adjust to server RAM)
innodb_buffer_pool_size=1G
key_buffer_size=256M
max_connections=100
# Logging
log-error=/var/log/mysql/error.log
slow_query_log=1
slow_query_log_file=/var/log/mysql/slow.log
long_query_time=2
# Character set
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
[mysql]
default-character-set=utf8mb4
[client]
default-character-set=utf8mb4

3. First Database and Table Creation

Create a database

SHOW DATABASES;
CREATE DATABASE school_db;
USE school_db;
SELECT DATABASE();
-- DROP DATABASE school_db;  (use with caution)

Create tables

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    date_of_birth DATE,
    enrollment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    gpa DECIMAL(3,2),
    is_active BOOLEAN DEFAULT TRUE,
    INDEX idx_last_name (last_name),
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_code VARCHAR(20) UNIQUE NOT NULL,
    course_name VARCHAR(100) NOT NULL,
    credits INT DEFAULT 3,
    department VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE NOT NULL,
    grade CHAR(2),
    FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE,
    UNIQUE KEY unique_enrollment (student_id, course_id)
);

Insert data

INSERT INTO students (first_name, last_name, email, date_of_birth, gpa) VALUES
('张','三','[email protected]','2000-05-15',3.5),
('李','四','[email protected]','2001-02-28',3.2),
('王','五','[email protected]','1999-11-10',3.8),
('赵','六','[email protected]','2000-08-22',3.1);

INSERT INTO courses (course_code, course_name, credits, department) VALUES
('CS101','计算机科学导论',3,'计算机科学'),
('MATH201','高等数学',4,'数学'),
('ENG102','大学英语',2,'外语'),
('PHY301','大学物理',3,'物理');

INSERT INTO enrollments (student_id, course_id, enrollment_date, grade) VALUES
(1,1,'2023-09-01','A'),
(1,2,'2023-09-01','B+'),
(2,1,'2023-09-01','A-'),
(3,3,'2023-09-01','B'),
(4,4,'2023-09-01','A');

Query data

-- Basic query
SELECT * FROM students;
SELECT first_name, last_name, gpa FROM students WHERE gpa > 3.5;

-- Conditional query
SELECT * FROM students WHERE date_of_birth > '2000-01-01' AND is_active = TRUE ORDER BY last_name ASC;

-- Join query
SELECT s.first_name, s.last_name, c.course_name, e.grade, e.enrollment_date
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_id = c.course_id
WHERE e.grade IS NOT NULL;

-- Aggregation
SELECT department, COUNT(*) AS course_count, AVG(credits) AS avg_credits FROM courses GROUP BY department;

-- Subquery
SELECT first_name, last_name, gpa FROM students WHERE gpa > (SELECT AVG(gpa) FROM students);

Update and delete data

-- Update
UPDATE students SET gpa = gpa + 0.1 WHERE student_id = 1;

-- Delete inactive records
DELETE FROM students WHERE is_active = FALSE;

-- Safe delete (preview then delete)
SELECT * FROM students WHERE gpa < 2.0;
DELETE FROM students WHERE gpa < 2.0;