MySQL Best Practices

A consolidated set of practices for schema design, SQL, security, operations, and performance in MySQL.

Concept

A consolidated set of practices for schema design, SQL, security, operations, and performance in MySQL.

Why It Matters

Following these habits prevents the majority of real-world MySQL incidents.

Syntax

Recommended baseline settings (review for your workload):

[mysqld]
default_storage_engine          = InnoDB
character-set-server            = utf8mb4
collation-server                = utf8mb4_0900_ai_ci
sql_mode                        = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
innodb_flush_log_at_trx_commit  = 1
sync_binlog                     = 1
binlog_format                   = ROW
gtid_mode                       = ON
enforce_gtid_consistency        = ON
require_secure_transport        = ON
local_infile                    = OFF
slow_query_log                  = ON
long_query_time                 = 1
default_time_zone               = '+00:00'

Example

InnoDB only; every table has a short, increasing PK; natural keys UNIQUE; FKs indexed; utf8mb4; smallest correct types; DECIMAL for money; DATETIME beyond 2038; lowercase snake_case names; no reserved words; comments with units

NOT NULL by default; CHECK constraints (8.0.16+); FKs with RESTRICT unless ownership; strict SQL mode

Explicit columns; parameterized queries; sargable predicates; ORDER BY with every LIMIT; keyset pagination; NOT EXISTS instead of NOT IN with nullable columns; window functions instead of user-variable tricks

Short transactions; batch big changes; upserts with ON DUPLICATE KEY UPDATE; avoid REPLACE; LOAD DATA for bulk

Design from top queries; composite equality→range→sort; covering indexes for hot paths; review unused/redundant indexes; invisible before drop

No root for apps; roles; host-restricted accounts; TLS; password policy; least privilege; secrets manager; patch regularly

Automated, encrypted, off-site backups with tested restores; binlogs for PITR; replication monitoring; alerts; change management via migrations; online DDL

Size buffer pool; slow log + sys.statement_analysis; EXPLAIN ANALYZE; histograms; connection pooling; avoid long transactions

Run an LTS release; test upgrades with util.checkForServerUpgrade(); read release notes for removed features

Expected Result

A MySQL environment that is secure by default, predictable in behavior, observable, and recoverable.

Common Mistakes

Treating defaults as tuned production settings.

Turning off strict mode or ONLY_FULL_GROUP_BY to "fix" errors.

Making manual, undocumented changes in production.

Best Practices

This module *is* the checklist — adopt it as a team standard and review it quarterly.

Interview Relevance

Medium: "What MySQL best practices do you follow?", "What would you check first on a new MySQL server?".

Practice Questions

Audit your server against the baseline settings and list differences.

Write a team SQL style guide for MySQL.