How to Install & Optimize MariaDB/MySQL on a VPS (Step-by-Step)

MariaDB and MySQL remain the backbone of modern applications, from WordPress to enterprise SaaS platforms. On a VPS, proper installation and tuning of your database server can make the difference between lightning-fast queries and a bottleneck that slows your entire stack. In this guide, we’ll go far beyond installation — covering security, InnoDB optimization, indexing, caching, replication, and monitoring — to help you run a production-grade MySQL/MariaDB environment on a VPS in 2025.

🔹 Step 1: Install MariaDB/MySQL

On Ubuntu/Debian (MariaDB preferred)

sudo apt update
sudo apt install mariadb-server mariadb-client -y

On CentOS/RHEL (MySQL 8.0)

sudo dnf install @mysql -y

Check version:

mysql -V

MariaDB is often the default in Debian/Ubuntu repos, while MySQL 8.0 is the upstream choice on RHEL. Both are production-ready in 2025.

🔹 Step 2: Secure Initial Configuration

Run the included script:

sudo mysql_secure_installation

Set root password (or socket auth).
Remove anonymous users.
Disallow remote root login.
Remove test databases.

Create a dedicated database user for apps:

CREATE USER 'appuser'@'%' IDENTIFIED BY 'StrongPassw0rd!';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'%';
FLUSH PRIVILEGES;

Always use least-privilege: never give GRANT ALL globally for production apps.

🔹 Step 3: Configure InnoDB for Performance

Edit /etc/mysql/mariadb.conf.d/50-server.cnf or /etc/my.cnf:

[mysqld]
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_file_per_table = 1

innodb_buffer_pool_size: ~60–70% of RAM (critical for caching indexes + data).
innodb_log_file_size: Large enough for write bursts (restart needed after change).
O_DIRECT: Avoids double buffering between OS and InnoDB.

On small VPS (2 GB RAM), set innodb_buffer_pool_size=512M for stability.

🔹 Step 4: Connection & Query Tuning

[mysqld]
max_connections = 300
thread_cache_size = 50
table_open_cache = 4096
query_cache_type = 0
query_cache_size = 0
tmp_table_size = 128M
max_heap_table_size = 128M
join_buffer_size = 4M
sort_buffer_size = 4M

max_connections: Set based on app concurrency + VPS RAM.
query_cache: Disabled in MySQL 8/MariaDB ≥10.2 — rely on app-level or proxy cache (Redis, ProxySQL).
tmp_table_size: Avoids disk temp tables for large joins.

🔹 Step 5: Indexing & Schema Optimization

Indexes are critical for performance. Example:

CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_orders_date_status ON orders(order_date, status);

Use EXPLAIN to check query plans.
Avoid SELECT * — select only needed columns.
Normalize when possible, denormalize for heavy read scaling.

Use pt-query-digest (Percona toolkit) to analyze slow queries:

pt-query-digest /var/lib/mysql/mysql-slow.log

🔹 Step 6: Backups

mysqldump (basic)

mysqldump -u root -p appdb > backup.sql

Percona XtraBackup (hot backup)

xtrabackup --backup --target-dir=/backup/2025-09-24/

Automate with cron + compress backups:

0 2 * * * xtrabackup --backup --target-dir=/backup/$(date +\%F) && tar -czf backup.tar.gz /backup/$(date +\%F)

🔹 Step 7: Replication Basics

Replication improves availability and read scaling. In 2025, GTID-based replication is standard.

[mysqld]
server_id=1
log_bin=mysql-bin
binlog_format=ROW
gtid_mode=ON
enforce_gtid_consistency=ON

On the slave:

CHANGE MASTER TO MASTER_HOST='master_ip',
MASTER_USER='repl',
MASTER_PASSWORD='StrongPass',
MASTER_AUTO_POSITION=1;
START SLAVE;

For strict HA, consider Galera Cluster or ProxySQL with multiple replicas.

🔹 Step 8: Monitoring & Troubleshooting

Install mysqltuner for recommendations:

wget http://mysqltuner.pl -O mysqltuner.pl
perl mysqltuner.pl

Metrics to track:

Connections per second.
Slow query count (long_query_time default 10s → set to 1s).
Buffer pool hit ratio (aim > 95%).

For enterprise monitoring, integrate with Zabbix or Prometheus MySQL exporter.

🔹 Step 9: Hardening Database Security

Enable TLS for client connections (require_secure_transport=ON).
Restrict bind address: bind-address=127.0.0.1 or VPN-only.
Avoid GRANT ALL ON *.* — scope permissions carefully.
Rotate passwords and use Vault/Key Management for apps.

✅ Conclusion

Installing MySQL/MariaDB is easy, but running it in production requires careful tuning. By configuring InnoDB, managing connections, indexing correctly, and applying monitoring and replication strategies, you can scale from a small VPS to enterprise workloads with confidence. At WeHaveServers.com, we deploy optimized database environments for clients running SaaS, e-commerce, and AI workloads — always starting with strong defaults and tuning for real-world workloads.

❓ FAQ

Which is better: MariaDB or MySQL?

MariaDB is faster for some workloads and remains community-driven, while MySQL 8.0 has enterprise features and Oracle support. Both are viable in 2025.

How much RAM should I allocate to InnoDB buffer pool?

60–70% of available RAM. On a 16 GB VPS, allocate ~10 GB for maximum performance.

Should I enable query cache?

No, query cache is deprecated/removed. Use Redis or application-level caching instead.

Is replication enough for HA?

Async replication improves scaling, but not true HA. For mission-critical workloads, use Galera or MySQL Group Replication.

How do I know if my database is tuned well?

Check mysqltuner output, monitor buffer pool hit ratio (>95%), and keep slow query logs under control.