Black Friday. WordPress + WooCommerce on a VPS. Homepage loads in twelve seconds—not viral traffic, but an unindexed postmeta table and swapping MySQL disk. Meanwhile, automatic backup was never restored; last dump is six days old. Foundations should have been laid before crisis, not after post-mortem.
MySQL on a dynamic site is not "installed by host, therefore fine." It is your revenue's transactional core—catalog, orders, client accounts. Four pillars hold production up: clean engine, limited accounts, indexed queries, proven backups.
Clean engine and charset
Start by verifying what your application actually uses:
- InnoDB mandatory for business tables — transactions, crash recovery, foreign keys.
- utf8mb4 for emojis and full Unicode; not legacy three-byte
utf8. - Consistent collation across tables — avoids slow implicit joins.
Check legacy tables:
SHOW TABLE STATUS WHERE Engine != 'InnoDB';
Convert MyISAM before the next power cut — not the day a crash corrupts a table with no transaction log.
Accounts and attack surface
A single root user in WordPress or Laravel .env is a common bad practice:
| Account | Rights | Use |
|---|---|---|
app_user | CRUD on app_db only | PHP, WordPress, application |
backup_user | SELECT + LOCK or dump tool | Backup cron |
root | ALL | Local human admin, not in .env |
No % user reachable from the Internet. Bind MySQL to localhost or private network if possible — see VPS firewall. A compromised app account must not DROP DATABASE.
Performance before hardware
Before upgrading the server, measure:
- Slow query log enabled at 1–2 second threshold; analyse weekly.
- Indexes on WHERE and JOIN columns — not every column indexed.
- InnoDB buffer pool ~70% of RAM dedicated to MySQL on a DB-only VPS.
- Query cache — obsolete on MySQL 8; do not try to re-enable it.
Under WordPress, enable object cache (Redis) after cleaning SQL — not before. Cache masking a twelve-second query postpones the problem; it does not fix it.
Backups and proven restore
A backup never tested is not a backup:
- Daily encrypted mysqldump off-site + binlog if point-in-time restore required.
- Snapshot if MySQL runs locally on VPS.
- Quarterly restore test — see testing restore.
- Document MySQL dump version vs restore target.
On shared hosting, exports via panel or cron if SSH available — check max size and exclusions. Compare MySQL and managed MySQL offers via the directory and compare tool if high availability becomes a concern.
Replication and HA — only if needed
Read-only replica for reporting: useful. Automatic failover: significant operational complexity — managed MySQL is often more rational. Do not replicate to "look professional" without a tested failover runbook.
During stack refactor, also evaluate managed Postgres — migration pays off if your team rethinks the schema.
The climax: the database holds until the first real spike
Here is what local development does not reveal.
Lay them before campaign, migration, or sales — not after the first accidental DROP.
Decide and move forward without blind spots
In one day, you can lay foundations:
- Audit engine, charset, and MySQL accounts — convert MyISAM, remove root from
.env. - Enable slow query log and fix the three slowest queries.
- Automate daily encrypted backup and schedule quarterly restore test.
- Isolate MySQL on localhost or private network — close direct Internet access.
- Compare MySQL and managed MySQL hosts via the directory if load or compliance require it.
For general backup framing, follow with backup strategy once automatic dump is in place.
Frequently asked questions
Is MyISAM still acceptable in 2026?
Not for business data. InnoDB everywhere: transactions, crash recovery, foreign keys. Convert legacy tables before the next outage — MyISAM does not log transactions.
Single MySQL root user for the app?
Bad practice. Create an app account with limited rights (SELECT, INSERT, UPDATE, DELETE) on one database; reserve root for local human admin or bastion.
mysqldump backup or snapshot?
Both complement: portable logical dump plus fast disk snapshot. Test restore — a dump never restored is not insurance.
When to move to managed MySQL?
Once high availability, point-in-time restore, or security patching exceed you — or during refactor toward managed Postgres.
MySQL for a dynamic site: not the displayed version — InnoDB, limited accounts, indexes, and proven restore before crisis teaches the vocabulary.