MySQL is an open‑source RDBMS built on the client / server model. Its core is the mysqld server process that manages:

• Connection handling & authentication.
• SQL parsing, optimisation, and execution.
• Transactional InnoDB storage engine.
• Pluggable engines (e.g. NDB, MyRocks).

Latest versions · LTS branch 8.4 (8.4.4, Jan 21 2025) · Innovation branch 9.2 (9.2.0, Jan 21 2025)

2.1  Linux (APT / YUM)

# Debian/Ubuntu
$ sudo apt update
$ sudo apt install mysql-server
# RHEL/Rocky
$ sudo dnf install @mysql

2.2  macOS (Homebrew)

$ brew install mysql
$ brew services start mysql

2.3  Initial Secure Setup

mysql_secure_installation

2.4  Key my.cnf Options

• datadir – data files location.
• innodb_buffer_pool_size – cache for data + indexes.
• max_connections – concurrency cap.
• log_error, slow_query_log.

Common interfaces:

• mysql CLI  · Batch: mysql < dump.sql
• MySQL Shell (mysqlsh) JSON/X API.
• libmysqlclient (C), Connector/C++, mysql-connector-python, Node‑MySQL2.

// Node.js example
import mysql from 'mysql2/promise';
const db = await mysql.createConnection({
    host: '127.0.0.1',
    user: 'user',
    password: 'secret',
    database: 'shop'
});
const [rows] = await db.query('SELECT * FROM products;');

4.1  Numeric

Type	Bytes	Range / Precision
TINYINT	1	‑128 … 127
INT	4	‑2 147 483 648 …
DECIMAL(p,s)	var	Exact fixed‑point

4.2  Date & Time

DATE, DATETIME(6), TIMESTAMP, AUTO now().

4.3  Strings & JSON

• VARCHAR(n), TEXT, BLOB.
• CHARSET & COLLATE for Unicode.
• JSON – binary‑optimised since 5.7.

5.1  Databases & Tables

CREATE DATABASE app CHARACTER SET utf8mb4;
CREATE TABLE users(
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE,
    created TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB;

5.2  Views & Materialised Alternatives

CREATE VIEW active_users AS …

5.3  Sequences (8.0+)

Generation with CREATE SEQUENCE.

INSERT INTO orders(user_id, total) VALUES (?,?);
SELECT name, SUM(total) AS spend
    FROM orders
    JOIN users USING(id)
    WHERE created > CURDATE() ‑ INTERVAL 30 DAY
    GROUP BY user_id
    HAVING spend > 1000
    ORDER BY spend DESC
    LIMIT 10;

Tip – Use EXPLAIN ANALYZE for runtime plans.

7.1  B‑tree, Full‑text, Spatial

ALTER TABLE posts
    ADD FULLTEXT(title, body),
    ADD SPATIAL(location);

7.2  Composite Key Order

Left‑most prefix rule – index (A,B) can filter on A alone.

7.3  Optimizer Hints

SELECT /*+ INDEX(t idx_date) */ … FROM t;

8.1  Common Table Expressions

WITH RECURSIVE nums AS(
    SELECT 1 AS n
    UNION ALL
    SELECT n+1 FROM nums WHERE n<10
) SELECT * FROM nums;

8.2  Windows

SELECT user_id,
       SUM(total) OVER(PARTITION BY user_id
                       ORDER BY created
                       ROWS BETWEEN UNBOUNDED PRECEDING
                       AND CURRENT ROW) AS running_total
FROM orders;

DELIMITER $$
CREATE PROCEDURE add_user(IN email VARCHAR(255))
BEGIN
    INSERT INTO users(email) VALUES(email);
END$$
DELIMITER ;

Triggers  · Event Scheduler for cron‑like tasks.

• REPEATABLE READ default (MVCC).
• SERIALIZABLE for strict consistency.
• START TRANSACTION WITH CONSISTENT SNAPSHOT.

Note – XA 2‑PC for distributed transactions.

CREATE USER 'app'@'%' IDENTIFIED BY 'secret';
GRANT SELECT,INSERT,UPDATE ON shop.* TO 'app'@'%';
ALTER USER 'app'@'%' REQUIRE SSL;

caching_sha2_password is default auth plugin since 8.0.

12.1  Logical

# Dump
mysqldump -u root -p --single-transaction shop > shop.sql
# Restore
mysql < shop.sql

12.2  Physical (Hot Backup)

Use mysqlbackup (Enterprise) or Percona XtraBackup for open‑source.

• Async BINLOG – classic master‑slave.
• Group Replication – Paxos‑like quorum.
• InnoDB Cluster + MySQL Router.
• NDB Cluster for 99.999 % availability.

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='primary', SOURCE_PORT=3306,
    SOURCE_USER='rep', SOURCE_PASSWORD='***',
    SOURCE_AUTO_POSITION=1;

14.1  Metrics

• performance_schema, sys schema views.
• SHOW STATUS, INFORMATION_SCHEMA.
• MySQL Enterprise Monitor.

14.2  Buffer Pool Sizing

SELECT ROUND((innodb_buffer_pool_bytes_data/
              innodb_buffer_pool_bytes_total)*100,2) AS pct_used
FROM performance_schema.global_status
WHERE VARIABLE_NAME LIKE 'Innodb_buffer_pool%';

15.1  JSON

SELECT JSON_EXTRACT(doc, '$.price') AS price
FROM products WHERE JSON_CONTAINS(tags, '"sale"');

15.2  Spatial (GIS)

Spatial reference privileges added 9.2.0 .

15.3  Generated Columns & Virtual Indexes

ALTER TABLE … ADD COLUMN total DECIMAL(10,2) AS (qty*price) STORED;

Use RANGE, HASH, or KEY partitions; or orchestrate sharding with Vitess / ProxySQL.

1. Read release notes for deprecated syntax.
2. Run mysqlsh upgrade checker.
3. Test on staging, ensure full backup.

LTS → LTS (8.0 → 8.4) recommended; innovation lines jump annually.

• Workbench – visual modelling.
• MySQL Shell – admin / dump / upgrade.
• Percona Toolkit – checksum, pt‑online‑schema‑change.

• Parallel read/clone improvements (8.2).
• Privilege CREATE_SPATIAL_REFERENCE_SYSTEM (9.2).
• Instant ADD COLUMN for many DDL cases (8.3).
• Optimiser cost model refreshed; EXPLAIN ANALYZE JSON output.
• LTS cadence: 8.4 LTS (Jan 2025) with 3‑year support window.

• Always keep innodb_file_per_table=ON.
• Prefer utf8mb4 over legacy utf8.
• Use parameterised queries to prevent SQL injection.
• Monitor replication lag: SHOW REPLICA STATUS\G.
• Official docs: dev.mysql.com/doc