Why self-host MySQL on a VPS
On shared hosting, your MySQL database shares RAM and I/O with hundreds of other sites, and you can change neither innodb_buffer_pool_size, nor the SQL modes, nor the engine version. On a VPS, you have dedicated resources, you choose between MySQL 8.x and MariaDB, and you access the my.cnf file to tune every parameter. It's the right choice for an e-commerce store, a high-traffic CMS, or an API that needs fast transactions and a generous buffer pool. You also manage your own users, your table-level grants, and your master-replica replication strategy.
The concrete benefits of a self-hosted MySQL
- Dedicated resources: the entire InnoDB buffer pool for your data, with no noisy neighbors.
- Full access to
my.cnf:innodb_buffer_pool_size,max_connections, strict SQL modes. - Choice of engine: MySQL 8 for JSON/CTE functions, or MariaDB for its additional engines.
- Configurable master-replica replication for offloaded reads or disaster recovery.
- Logical (
mysqldump) or physical (xtrabackup) backups depending on your volume. - No arbitrary quota on database size or the number of simultaneous connections.
Hardware and software prerequisites
A MySQL instance for a WordPress site or a small application runs comfortably on a VPS with 2 vCPU and 2 to 4 GB of RAM with 30 GB of SSD. For Magento, PrestaShop, or real e-commerce traffic, go up to 4 vCPU and 8 GB of RAM, because the InnoDB buffer pool must be able to hold most of your hot dataset. On the software side: Ubuntu 22.04/24.04 LTS, Docker and Docker Compose v2, a dedicated persistent volume, and a domain (e.g., mysql.mysite.com) only if remote access is required. Also plan disk space for the binlogs if you enable replication or point-in-time recovery.
Deploying MySQL with Docker in production
Secure the VPS
Update the system, install Docker, disable password-based SSH login in favor of keys, and close port 3306 to the outside with ufw deny 3306. MySQL should listen only on the container's internal network.
Define the docker-compose.yml
Declare a mysql:8.4 service, mount a volume on /var/lib/mysql, and pass MYSQL_ROOT_PASSWORD, MYSQL_DATABASE, and MYSQL_USER through a .env file. Also mount a conf.d/custom.cnf file as read-only for your custom settings.
Harden the installation
After docker compose up -d, run the equivalent of mysql_secure_installation: remove anonymous users, forbid remote root login, and create a dedicated application account with a GRANT limited to a single database. Never use the root account from your application.
Optimize InnoDB
In your custom.cnf, set innodb_buffer_pool_size to about 60-70% of the VPS RAM, innodb_flush_log_at_trx_commit=2 for a good performance/durability trade-off, and max_connections according to your real load. Enable the slow query log to track down expensive queries.
Expose over TLS if necessary
If an external application must connect, enable TLS encryption on the MySQL side and place access behind an SSH tunnel or a proxy. Avoid exposing 3306 directly: favor private network access between your VPSs.
Schedule backups
Schedule a daily mysqldump --single-transaction via cron for consistent, lock-free dumps, compress them, and archive them off the VPS. For large databases, switch to Percona XtraBackup for fast physical backups.
Enable the binlogs (log_bin + binlog_format=ROW) from the start: they are the key to point-in-time recovery. Combined with a full weekly mysqldump, they let you replay every transaction up to the second before an incident. And if you set up a replica on a second VPS, those same binlogs feed the replica in real time for your offloaded reads.
