Hands-on guide series
MySQL / MariaDB for Production: A Hands-On Guide
An eight-part, hands-on guide to running MariaDB in production on a single VPS: install and secure, least-privilege users, InnoDB indexing, backups that restore, primary to replica replication, ProxySQL connection pooling, slow-query monitoring, and security hardening. Every command tested on a live Ubuntu 24.04 server.
What you will build
Security is layers, not a switch. Each part closes a different category of attack, from the automated sweep that finds a brand-new server, to the day one gets in anyway. Read them in order, or jump to what you need.
- 1
Install and Secure MariaDBInstall and secure MariaDB on a live Ubuntu 24.04 server, then prove the defaults hold: socket-only root, no anonymous users, and no test database.9 min - 2
Users, Privileges and Least PrivilegeGive each MariaDB login only the privileges its job needs, then watch the app account get refused a schema change and a read-only user refused a write.8 min - 3
InnoDB, Schema and Indexing BasicsSee InnoDB indexing pay off on a live table: EXPLAIN a query before and after adding an index and watch the rows examined fall from twenty thousand to ten.8 min - 4
Backups That Actually RestoreTake a MariaDB backup with mysqldump, then restore it into a fresh database and check the row counts match. Physical copies with mariabackup too.9 min - 5
Replication: Primary to ReplicaSet up MariaDB replication from a primary to a replica on one server, then confirm both threads run and watch a new row cross from primary to replica.9 min - 6
Connection Pooling with ProxySQLPut ProxySQL connection pooling in front of MariaDB so many app connections ride on a few backend ones, then read the pool stats to prove it is working.9 min - 7
Monitoring and Slow QueriesTurn on the MariaDB slow query log, run a query slow enough to be caught, read the entry, then use the process list and status counters to find what to tune.9 min - 8
Security HardeningMariaDB security hardening on a live server: bind to localhost, require TLS for a user, firewall the port, disable symbolic links, and recap least privilege.8 min