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.4 LTS 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.4 LTS for long-term stability, 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.
Concrete prerequisites before you start
Recommended version: MySQL 8.4 LTS (current release: 8.4.11), the official LTS branch, supported until 2032. Avoid MySQL 8.0 for new installations: active support ends in 2025.
RAM: 1 GB minimum for a test environment or lightweight blog. Plan 2 GB for a WordPress site or small production application, and 4 to 8 GB for Magento, PrestaShop, or a high-traffic API. The baseline rule: allocate 60 to 70% of available RAM to innodb_buffer_pool_size.
CPU: 2 vCPU covers most use cases. Scale to 4 vCPU when queries are frequent or when you enable replication.
Storage: 30 GB SSD NVMe as a starting point; reserve extra space for binlogs if you enable replication or point-in-time recovery.
Ports: MySQL listens on port 3306 by default. This port must never be open on the public interface. The bind-address parameter in my.cnf must point to 127.0.0.1 or the private network address, not 0.0.0.0.
System: Ubuntu 22.04 or 24.04 LTS, Docker and Docker Compose v2, a dedicated persistent volume.
Deploying MySQL 8.4 with Docker in production
Secure the VPS
Update the system (
apt update && apt upgrade -y), install Docker, disable password-based SSH login in favor of keys (PasswordAuthentication noinsshd_config), and close port 3306 to the outside:ufw deny 3306 ufw allow OpenSSH ufw enableMySQL should only listen on the Docker container's internal network.
Define the docker-compose.yml
Declare a
mysql:8.4service, mount a volume on/var/lib/mysql, and pass environment variables through a.envfile:# .env MYSQL_ROOT_PASSWORD=strong_root_password MYSQL_DATABASE=my_database MYSQL_USER=app_user MYSQL_PASSWORD=app_password# docker-compose.yml services: mysql: image: mysql:8.4 restart: unless-stopped env_file: .env volumes: - mysql_data:/var/lib/mysql - ./conf.d:/etc/mysql/conf.d:ro ports: - "127.0.0.1:3306:3306" volumes: mysql_data:Note the
bindon127.0.0.1inports: the port will only be accessible from the VPS itself.Create a database and a dedicated user
After
docker compose up -d, connect to the container:docker exec -it mysql mysql -u root -pThen create the database and the application user:
CREATE DATABASE my_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE USER 'app_user'@'%' IDENTIFIED BY 'app_password'; GRANT SELECT, INSERT, UPDATE, DELETE ON my_database.* TO 'app_user'@'%'; FLUSH PRIVILEGES;Limit grants to the strict minimum: never use
GRANT ALL PRIVILEGESfor an application account.Harden the installation
Run the equivalent of
mysql_secure_installationmanually:-- Remove anonymous users DELETE FROM mysql.user WHERE User=''; -- Forbid remote root login DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1'); -- Drop the test database DROP DATABASE IF EXISTS test; FLUSH PRIVILEGES;Never use the
rootaccount from your application. Verify thatbind-address = 127.0.0.1is present in yourconf.d/custom.cnf.Expose over TLS if necessary
If an external application must connect remotely, never expose port 3306 directly on the Internet. Use an SSH tunnel:
ssh -L 3306:127.0.0.1:3306 [email protected] -NThen connect your MySQL client to
127.0.0.1:3306— traffic travels encrypted over SSH. For a permanent inter-VPS connection, configure MySQL to listen only on the private network interface and allow only the application server's IP in UFW.Schedule backups
Schedule a daily
mysqldump --single-transactionvia cron for consistent, lock-free dumps:# /etc/cron.d/mysql-backup 0 2 * * * root docker exec mysql mysqldump -u root -p"${MYSQL_ROOT_PASSWORD}" --single-transaction --all-databases | gzip > /backups/mysql-$(date +\%Y\%m\%d).sql.gzKeep at least 7 days of retention and archive dumps off the VPS. Test restoration once a month: an untested backup is not a backup. For large databases, switch to Percona XtraBackup for fast physical backups.
Security: essential hardening steps
mysql_secure_installation in a container. The official Docker image does not run it automatically: do it manually after the first startup (step 4 above).
Remove remote root. The root account must only be accessible from localhost. A root account accessible from % is the first vulnerability exploited during port scans.
Dedicated user per application. Each connected application gets its own MySQL account with rights restricted to its own database. A compromised application cannot then read or modify other applications' data.
UFW + bind-address. The double protection is mandatory: ufw deny 3306 at the system firewall level, and bind-address = 127.0.0.1 in my.cnf on the MySQL side. One does not replace the other.
Strong passwords. Use caching_sha2_password (default plugin under MySQL 8.x) and generated passwords (at least 20 characters). Store them in managed secrets, never in source code.
Optimizing InnoDB performance
These parameters go in your conf.d/custom.cnf file mounted read-only in the container.
innodb_buffer_pool_size: the most important parameter. Set it to 60–70% of the VPS RAM. On a 4 GB VPS, that's 2.5 to 2.8 GB. Too small = frequent disk reads; too large = swap.
innodb_flush_log_at_trx_commit: value 2 offers a good performance/durability trade-off for most applications. Value 1 (default) is safer but slower; value 0 is fastest but risky on crash.
max_connections: adjust to your real load. The default (151) is often too low for a high-traffic application, too high for a small blog. Each connection consumes ~1 MB of RAM.
slow_query_log: enable it with slow_query_log = 1 and long_query_time = 1 to capture all queries exceeding 1 second. It's your primary optimization tool.
Sample custom.cnf for a 4 GB VPS:
[mysqld]
innodb_buffer_pool_size = 2G
innodb_flush_log_at_trx_commit = 2
max_connections = 200
slow_query_log = 1
long_query_time = 1
log_bin = /var/lib/mysql/binlog
binlog_format = ROWSecure remote access: the SSH tunnel
Exposing port 3306 on your VPS's public interface is a serious security mistake: automated scanners attempt brute-force attacks on this port continuously. The right approach is the SSH tunnel.
One-time connection from your workstation:
ssh -L 3306:127.0.0.1:3306 [email protected] -NThen open your MySQL client on 127.0.0.1:3306 — traffic travels encrypted over SSH.
Permanent inter-VPS connection: if your application runs on a second VPS on the same private network, configure MySQL to listen only on the private network address (bind-address = 10.0.0.X), and allow only the application server's IP in UFW:
ufw allow from 10.0.0.5 to any port 3306To avoid: bind-address = 0.0.0.0 without a firewall, port 3306 open publicly, or MySQL accounts with Host='%' without IP restriction.
Automated backups: a complete strategy
A MySQL backup strategy relies on two complementary levels.
Level 1 — daily logical dump (mysqldump): consistent thanks to --single-transaction (no read lock on InnoDB), portable between versions. Suitable for databases up to a few tens of GB.
Level 2 — binlogs for point-in-time recovery: enable log_bin from the start. Combined with a full weekly dump, binlogs let you replay every transaction up to the second before an incident.
For large databases — Percona XtraBackup: physical backup without interrupting the service, fast restoration (file copy rather than SQL re-execution).
Retention and archiving: do not store backups only on the VPS. A corrupted VPS takes its own backups with it. Export to object storage or a remote SFTP server, and keep at least 7 days.
Test restoration: an untested backup is not a backup. Schedule a monthly test: restore on a temporary container and verify your data is intact.
# Restore from a compressed dump
zcat /backups/mysql-20260917.sql.gz | docker exec -i mysql mysql -u root -pTroubleshooting: 5 common errors
Access denied for user 'app'@'...'
The account exists but the Host does not match. MySQL checks the exact source IP. If the application connects from a Docker container, the source IP may be 172.17.0.x and not localhost. Check with:
SELECT User, Host FROM mysql.user;Solution: create the account with 'app_user'@'%' (or the exact container IP) and re-run FLUSH PRIVILEGES.
Too many connectionsmax_connections has been reached. Check the number of active connections:
SHOW STATUS LIKE 'Threads_connected';Increase max_connections in custom.cnf and verify your application uses a connection pool.
Table '...' is marked as crashed
InnoDB corruption, often after an abrupt shutdown. Attempt repair:
mysqlcheck -u root -p --auto-repair --all-databasesIf the table cannot be repaired, restore from your last consistent backup.
Can't connect to MySQL server on '127.0.0.1' (111)
MySQL is not listening on the expected interface. Check bind-address in custom.cnf and restart the container. Also verify the container is running with docker compose ps.
ERROR 1819 (HY000): Your password does not satisfy the current policy
MySQL 8's password validation plugin rejects simple passwords. Use a password of at least 8 characters with uppercase letters, digits, and symbols, or set validate_password.policy = LOW in custom.cnf for development environments only.
Enable 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.
