Skip to content
Sign In Get Started

Databases

MySQL and MariaDB: users, indexes, slow queries, backups.

  1. phpMyAdmin without fearphpMyAdmin without fearcPanel opens phpMyAdmin as the database user you choose, not as root. Export a gzip first, then run SELECT with the same WHERE before any UPDATE or DELETE.Updated Aug 29, 2026
  2. MySQL users and least privilegeMySQL users and least privilegeCreate one MySQL user per app, grant rights only on that schema, and avoid FILE, GRANT OPTION, and host wildcards unless you truly need remote access.Updated Aug 29, 2026
  3. Indexes that actually helpIndexes that actually helpA MySQL index is a sorted copy of a column that makes lookups cheap. Run EXPLAIN on the slow query, add the index it needs, and skip the…Updated Aug 29, 2026
  4. The slow query logThe slow query logOn a VPS you can enable the slow query log and set long_query_time. On shared hosting, use Query Monitor or EXPLAIN on staging instead, then fix loops and…Updated Aug 29, 2026
  5. InnoDB buffer poolInnoDB buffer poolThe InnoDB buffer pool is MariaDB’s RAM cache for table and index pages. Size it so hot data fits on a VPS; on shared hosting the pool is…Updated Aug 29, 2026
  6. utf8mb4 character setsutf8mb4 character setsutf8mb4 stores full Unicode in MySQL and MariaDB, including emoji. Align charset and collation, convert on a copy, and avoid mixed collations that break JOINs.Updated Aug 29, 2026
  7. Back up and restore a databaseBack up and restore a databaseUse mysqldump to export your database, gzip the file, and copy it off the server. Restore into a spare schema first so you can verify the data before…Updated Aug 29, 2026
  8. Repair and OPTIMIZE TABLERepair and OPTIMIZE TABLEREPAIR TABLE fixes crashed MyISAM. On InnoDB, OPTIMIZE TABLE often rebuilds and can lock or saturate I/O. Use it after large deletes, not on a nightly plugin schedule.Updated Aug 29, 2026
  9. Remote MySQL: when not toRemote MySQL: when not toRemote MySQL across a continent from PHP is latency you will feel in wp-admin. Use it for a replica or a BI tool, not as everyday architecture for…Updated Aug 29, 2026
  10. MariaDB vs MySQL, in practiceMariaDB vs MySQL, in practiceLogicWeb shared hosting runs MariaDB, and most WordPress sites never notice. Check SELECT VERSION() before you paste MySQL 8 config, and fix slow queries first.Updated Aug 29, 2026
  11. wp_options autoload bloatwp_options autoload bloatRows in wp_options with autoload=yes load on every request. Measure the size, then set autoload to no or remove leftover plugin data on staging first.Updated Aug 29, 2026
  12. EXPLAIN a query before you buy RAMEXPLAIN a query before you buy RAMEXPLAIN shows whether MySQL will scan a table or use an index. Fix full scans and missing indexes before you buy more RAM for a slow site.Updated Aug 29, 2026
  13. Connection limitsConnection limitsmax_connections is a ceiling on open MySQL threads, not throughput. Hitting it looks like the database is down; fix waiting PHP workers and slow queries first.Updated Aug 29, 2026
  14. MySQL slow after a big importMySQL slow after a big importMySQL can stay import slow after a large dump when disk is tight and stats are stale. Free space first, import over SSH, run ANALYZE TABLE, then wait…Updated Aug 29, 2026
  15. Binary logs filling the diskBinary logs filling the diskBinary logs support replication and point-in-time recovery. On a VPS, missing expiry can fill the disk and stop MariaDB. Purge on purpose; they are not backups.Updated Aug 29, 2026
  16. Charset conversion without mojibakeCharset conversion without mojibakeCharset conversion belongs on a copy: dump, inspect, convert, then inspect again. Latin1 holding UTF-8 bytes is double encoding; fix it with a binary round-trip, not a browser…Updated Aug 29, 2026
  17. GRANT USAGE vs ALLGRANT USAGE vs ALLGRANT USAGE lets a user connect; GRANT ALL is far more power than WordPress needs. Give SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, and INDEX on one schema only.Updated Aug 29, 2026
  18. local_infile, probably offlocal_infile, probably offlocal_infile lets a client run LOAD DATA LOCAL INFILE. Leave it off for web apps and WordPress. Use it only as a short, controlled one-off on a VPS…Updated Aug 29, 2026
  19. mysqldump flags that save youmysqldump flags that save youUse mysqldump with --single-transaction and --quick so InnoDB dumps stay consistent without locking the shop. Pipe through gzip, and skip --add-drop-database unless you mean it.Updated Aug 29, 2026
  20. Performance Schema, used lightlyPerformance Schema, used lightlyPerformance Schema is useful for short diagnostic sessions on a VPS. Enable only the consumers you need, capture an hour of data, then turn them down again.Updated Aug 29, 2026