MySQL runs a huge share of the world's applications. This guide gets it running three ways and covers the administration you'll actually use — plus the errors you'll actually hit.

Prerequisites

  • A Linux machine (or VM) with sudo, plus Docker and a Kubernetes cluster for those sections.
  • Basic SQL comfort (SELECT, CREATE TABLE).

1. On Linux (the traditional way)

sudo apt update && sudo apt install -y mysql-server
sudo systemctl enable --now mysql
sudo mysql_secure_installation      # set root auth, remove test db, disable remote root

Config lives in /etc/mysql/, data in /var/lib/mysql/, logs via journalctl -u mysql. Connect with sudo mysql.

2. In Docker (fast and disposable)

docker run -d --name mysql \
  -e MYSQL_ROOT_PASSWORD=secret \
  -e MYSQL_DATABASE=app \
  -p 3306:3306 \
  -v mysqldata:/var/lib/mysql \
  mysql:8

The named volume is essential — without it, docker rm deletes your data. Ideal for local dev.

3. On Kubernetes

For learning, a StatefulSet + PVC works. For production, use an operator (Oracle's MySQL Operator or Percona's) which handles replication, backups, and failover:

apiVersion: mysql.oracle.com/v2
kind: InnoDBCluster
metadata: {name: app}
spec:
  instances: 3           # primary + replicas with automatic failover
  router: {instances: 1} # MySQL Router load-balances connections
  secretName: mysql-root

Never hand-roll production HA when an operator exists — it encodes the hard-won failover logic.

Everyday administration

Users and privileges (least privilege!):

CREATE USER 'app'@'%' IDENTIFIED BY 'strong-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON app.* TO 'app'@'%';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'app'@'%';

Give each app its own user scoped to its own database — never share root.

Backups (logical):

mysqldump --single-transaction --routines --triggers app > app.sql   # consistent, non-blocking on InnoDB
mysql app < app.sql                                                  # restore

For big databases use Percona XtraBackup (physical, faster, supports incrementals). Whatever you choose, test the restore — an untested backup isn't a backup.

Replication gives a read replica and failover target: enable binary logs on the primary, create a replication user, then on the replica:

CHANGE REPLICATION SOURCE TO SOURCE_HOST='primary', SOURCE_USER='repl',
  SOURCE_PASSWORD='...', SOURCE_AUTO_POSITION=1;
START REPLICA;
SHOW REPLICA STATUS\G     -- Replica_IO_Running and Replica_SQL_Running must both be Yes

Operators automate all of this.

Transactions, isolation & connections

Beyond installing MySQL, two topics separate people who run databases from people who just query them.

Transactions and isolation. InnoDB is transactional: wrap related writes in START TRANSACTION ... COMMIT so they succeed or fail as a unit. MySQL's default isolation level is REPEATABLE READ, which means a transaction sees a consistent snapshot for its whole life. Most bugs here come from long-running transactions holding locks and blocking others — keep transactions short, and never leave one open while waiting on user input or an external API. When two transactions wait on each other you get a deadlock; InnoDB detects it and rolls one back with error 1213, so your app should simply retry the losing transaction.

Connections and pooling. Each MySQL connection costs memory and a thread. Apps that open a connection per request quickly hit Too many connections. The fix is almost never "raise max_connections to 5000" — it's a connection pool (built into most frameworks) or ProxySQL in front, which multiplexes many app connections onto a small, healthy set of database connections. Right- size the pool to a few times your CPU count, not thousands.

Reading a slow query. The workflow is always the same: enable the slow query log, find the offender, and run EXPLAIN on it:

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
-- type: ALL and rows: 2,000,000  → a full table scan; add the index:
CREATE INDEX idx_customer ON orders (customer_id);
-- re-run EXPLAIN → type: ref, rows: 12  → fixed.

type: ALL (full scan) and a huge rows estimate are your red flags; a well-chosen index turns a scan into a lookup. Composite indexes should list the most selective, most-filtered column first.

High availability topologies

A single MySQL server is a single point of failure. Three common ways to fix that, in rising order of automation:

  • Async replication (primary → one or more replicas). Simple, great for read scaling and a warm standby, but failover is manual and a crash can lose the last few unreplicated transactions. Add semi-synchronous replication so the primary waits for at least one replica to acknowledge, tightening the durability gap.
  • InnoDB Cluster (Group Replication) — MySQL's built-in HA: a group of 3+ nodes with automatic failover and a consistent, quorum-based write model, fronted by MySQL Router which redirects apps to the current primary. This is the modern default for self-managed HA.
  • Operator-managed on Kubernetes — the MySQL/Percona operators run InnoDB Cluster for you, handling provisioning, failover, and backups declaratively (the InnoDBCluster manifest earlier).

For a quick primer: pick semi-sync replication for a simple standby, InnoDB Cluster for automatic failover, and an operator when you're on Kubernetes. Whatever you choose, test failover — an untested HA setup is a hope, not a guarantee.

Zero-downtime schema migrations

ALTER TABLE on a large table can lock it for minutes and take your app down. On a busy production database you use online schema change tools instead: pt-online-schema-change (Percona Toolkit) or gh-ost (GitHub). They work by creating a shadow copy of the table, applying the change to it, copying rows in the background while capturing ongoing writes, then swapping the tables atomically:

gh-ost --alter="ADD COLUMN country VARCHAR(2)" \
  --database=app --table=orders --execute

The app keeps serving throughout. The discipline that pairs with this: make schema changes backward compatible and deploy in phases (add a nullable column → backfill → start writing → enforce), so a migration never requires a coordinated app-and-DB flip.

Security hardening

Beyond mysql_secure_installation, production MySQL should:

  • Never expose 3306 to the internet. Keep it on a private network; reach it via the app tier or a bastion/VPN only.
  • Require TLS for client connections so credentials and data aren't sent in clear text.
  • Least-privilege every account — app users scoped to their database and the specific privileges they need; no application using root; separate accounts for backups (read + LOCK) and replication.
  • Rotate credentials and store them in a secrets manager, not in code or my.cnf committed to git.
  • Keep it patched — MySQL security releases matter; operators and managed services handle this, self- hosted means you own it.

Monitoring: the metrics that matter

Watch these and you'll see trouble before users do: connections (approaching max_connections = an imminent outage — pool!), replication lag (Seconds_Behind_Source), buffer pool hit ratio (low = too little RAM for the working set), slow query count, InnoDB row lock waits/deadlocks, and disk usage (binlogs and data). Export them with the Prometheus mysqld_exporter and alert on connection saturation and replica lag especially.

Backups in depth: logical, physical & PITR

"We have backups" means nothing until you've restored one. Know the three levels:

  • Logical (mysqldump / mydumper) exports SQL statements. Portable across versions and machines, easy to inspect, but slow to restore on large databases. Great up to tens of GB.
  • Physical (Percona XtraBackup) copies the actual data files while the server runs. Far faster to back up and restore at scale, supports incrementals (only what changed), and is the right choice for large production databases.
  • Point-in-time recovery (PITR) combines a base backup with the binary log so you can restore to any moment — the thing that saves you after an accidental DROP TABLE at 14:32. Keep binlogs, and you can replay up to 14:31.

The discipline that matters more than the tool: automate backups, store them off the server (another region/object storage), and test a restore on a schedule. A backup you've never restored is a hope, not a plan. Track your RPO (how much data you can lose) and RTO (how fast you must be back) and pick the method that meets them.

Storage engine, character sets & collation

Two settings quietly cause real bugs:

  • Use InnoDB (the default). It's transactional, crash-safe, and row-locking. Avoid MyISAM for anything that matters — it has no transactions and table-level locks. If you inherit MyISAM tables, migrating them to InnoDB is usually a clear win.
  • Use utf8mb4, not utf8. MySQL's legacy utf8 is a 3-byte encoding that cannot store emoji or some characters — a classic "why did this row break" bug. utf8mb4 is real, full UTF-8. Set it at the database, table, and connection level, and pick a sensible collation (e.g. utf8mb4_0900_ai_ci on MySQL 8) for correct sorting and comparison. Getting this right at creation time avoids a painful later migration.

Common production incidents

The MySQL pages you'll actually get, and the first thing to check:

  • Too many connections → almost always a missing/leaking pool, not a real need for thousands of connections. Add a pool / ProxySQL; find the leak.
  • Replica lag climbing → a long single write or a hot table; check Seconds_Behind_Source and consider parallel replication.
  • Disk full, server won't start → binlogs piled up; set binlog_expire_logs_seconds and purge.
  • Sudden slowness after growth → a query that used to scan a small table now scans a large one; find it in the slow log, EXPLAIN, add the index.
  • Deadlocks (error 1213) → normal under concurrency; make the app retry the rolled-back transaction, and keep transactions short and consistently ordered.

Scaling reads and writes

When one MySQL server isn't enough, you scale in two directions — and it's worth knowing the ladder before you need it:

  • Scale reads with replicas. Add read replicas and route read-heavy traffic (reports, search) to them while writes go to the primary. This handles the common case where reads vastly outnumber writes. Mind replication lag: don't read-your-own-write from a replica. A proxy like ProxySQL can split reads and writes automatically and route around a lagging or down replica.
  • Scale writes with sharding — reluctantly. A single primary caps write throughput. When you truly exceed it, you shard: split data across multiple primaries by a shard key (user ID, tenant). Sharding multiplies write capacity but adds real complexity — cross-shard queries, rebalancing, and application awareness — so exhaust vertical scaling, replicas, caching, and query optimization first.

The pragmatic order is almost always: optimize queries and add indexes → add caching (Redis) → add read replicas → scale the instance up → and only then consider sharding. Most applications never need the last step, and reaching for it early buys complexity you'll regret.

Upgrades and version choice

Databases are long-lived, so plan versions deliberately. Prefer a recent LTS/GA major version (MySQL 8.x) for new projects — you get years of security patches and modern features (better JSON, window functions, utf8mb4 defaults). For upgrades, the safe path is: read the release notes for breaking changes, test on a copy of production data with your real queries, upgrade a replica first and validate, then promote or roll the upgrade forward. Never upgrade a production primary in place without a tested rollback. Managed services (RDS/Cloud SQL) make minor-version patching nearly automatic and give guided major-version upgrades, but the "test on a replica first" discipline still applies. Skipping straight across several major versions at once is where upgrades go wrong — step through them.

Tuning basics that matter

  • innodb_buffer_pool_size — the single most important setting; give InnoDB ~60–70% of RAM on a dedicated server.
  • Add indexes for columns in WHERE/JOIN; find slow queries with the slow query log and EXPLAIN.
  • Keep transactions short to avoid lock contention.

Verify it worked

mysqladmin -u root -p status                 # uptime, threads, queries/sec
mysql -u app -p app -e "SELECT 1;"           # the app user can connect and query

Troubleshooting

  • Access denied for user → wrong host part of the account ('app'@'localhost' vs 'app'@'%') or wrong password. SELECT user, host FROM mysql.user;.
  • Too many connections → raise max_connections, but first check for a connection leak or add a pooler (ProxySQL). A leak is the usual cause.
  • Data gone after container restart (Docker) → no named volume. Always mount /var/lib/mysql.
  • Replica lag growing → a long-running write or a single-threaded replica; check SHOW REPLICA STATUS Seconds_Behind_Source, and consider parallel replication.
  • Disk full / server won't start → binary logs piled up. Set binlog_expire_logs_seconds and PURGE BINARY LOGS.
  • Slow queries → EXPLAIN shows a full table scan → add the missing index.

Where to go next

Our free course installs MySQL all three ways, then walks through users, backups and restores, setting up a replica, and reading EXPLAIN to fix a slow query — the core skills of a database administrator, with hands-on labs.