Complete MySQL Installation and Configuration Guide for Ubuntu VPS: Production-Ready Database Setup in 2026

MySQL Installation and Configuration: Building Production-Ready Databases

MySQL powers millions of web applications, from simple WordPress blogs to complex e-commerce platforms. How you set up MySQL on your Ubuntu VPS determines whether your database becomes a performance asset or a bottleneck.

This guide covers complete MySQL installation and configuration for Ubuntu VPS environments. You'll learn security hardening, performance tuning, and backup automation that keeps your databases running smoothly under production loads.

Prerequisites and System Requirements

Before installing MySQL, verify your Ubuntu VPS meets these minimum requirements:

• Ubuntu 22.04 LTS or 24.04 LTS (recommended)
• Minimum 2GB RAM for small applications, 4GB+ for production workloads
• At least 20GB available disk space
• Root or sudo access to the server

Check your current system resources with free -h and df -h. MySQL performance correlates directly with available memory.

Plan for your expected database size and concurrent connections.

Update your package list before proceeding: sudo apt update && sudo apt upgrade -y

Installing MySQL Server on Ubuntu VPS

Ubuntu's default repositories include MySQL Server 8.0. This version delivers significant performance improvements over earlier versions.

Install the mysql-server package:

sudo apt install mysql-server -y

The installation creates a mysql user account and starts the MySQL service automatically. Verify the service status:

sudo systemctl status mysql

You should see "active (running)" in the output. If MySQL fails to start, check the error log at /var/log/mysql/error.log for troubleshooting details.

Enable MySQL to start automatically on system boot: sudo systemctl enable mysql

Initial Security Configuration

Fresh MySQL installations require immediate security hardening. The mysql_secure_installation script handles several critical security settings:

sudo mysql_secure_installation

This interactive script prompts you to:

• Set a strong root password (use a password manager)
• Remove anonymous user accounts
• Disable remote root login
• Remove the test database
• Reload privilege tables

Answer "Y" to all security prompts unless you have specific reasons otherwise. For production servers, always enable the validate password plugin.

Choose STRONG password policy for maximum security.

After completing the security installation, test your root access: sudo mysql -u root -p

Essential MySQL Configuration for VPS Performance

MySQL's default configuration targets general compatibility rather than VPS-specific performance. Edit the main configuration file at /etc/mysql/mysql.conf.d/mysqld.cnf:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Key performance settings to modify:

[mysqld]
# InnoDB Buffer Pool - set to 70-80% of available RAM
innodb_buffer_pool_size = 2G

# Query Cache (deprecated in 8.0, but useful for older versions)
query_cache_type = 1
query_cache_size = 256M

# Connection settings
max_connections = 200
connect_timeout = 10

# Logging
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

The innodb_buffer_pool_size setting has the biggest impact on performance. For a 4GB VPS, allocate 2-3GB to InnoDB.

Restart MySQL after configuration changes: sudo systemctl restart mysql

Monitor your configuration changes with SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; from the MySQL prompt.

Creating Database Users and Managing Permissions

Never use the root account for applications. Create dedicated users with specific database permissions:

sudo mysql -u root -p

CREATE DATABASE webapp_production;
CREATE USER 'webapp_user'@'localhost' IDENTIFIED BY 'secure_password_here';
GRANT ALL PRIVILEGES ON webapp_production.* TO 'webapp_user'@'localhost';
FLUSH PRIVILEGES;

For applications requiring remote database connections, specify the application server's IP address instead of 'localhost'. Avoid using wildcards (%) in production environments.

Grant only necessary privileges. For read-only reporting users, use:

GRANT SELECT ON database_name.* TO 'readonly_user'@'localhost';

List current users and their privileges: SELECT User, Host FROM mysql.user;

Optimizing MySQL for Different Workloads

Database performance requirements vary between applications. WordPress sites need different optimization than e-commerce platforms or analytics databases.

For WordPress and content management systems, focus on query cache and connection pooling:

# WordPress-optimized settings
max_connections = 100
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
query_cache_limit = 4M

E-commerce applications with heavy write operations benefit from increased log file sizes and buffer settings:

# E-commerce optimized settings
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2
max_connections = 300

Monitor query performance using the slow query log and MySQL's performance schema. Enable performance monitoring: SET GLOBAL performance_schema = ON;

Setting Up Automated MySQL Backups

Database backups prevent catastrophic data loss. Create a backup script that runs via cron:

sudo nano /usr/local/bin/mysql_backup.sh

Basic backup script content:

#!/bin/bash
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
DATABASE="webapp_production"

mkdir -p $BACKUP_DIR
mysqldump -u backup_user -p'backup_password' $DATABASE > $BACKUP_DIR/${DATABASE}_${DATE}.sql

# Remove backups older than 7 days
find $BACKUP_DIR -name "*.sql" -mtime +7 -delete

Make the script executable: sudo chmod +x /usr/local/bin/mysql_backup.sh

Schedule daily backups with crontab: sudo crontab -e

Add this line for daily 2 AM backups: 0 2 * * * /usr/local/bin/mysql_backup.sh

Monitoring MySQL Performance and Health

Regular monitoring prevents small issues from becoming major problems. MySQL provides built-in status variables for performance tracking:

# Check current connections
SHOW STATUS LIKE 'Threads_connected';

# Monitor slow queries
SHOW STATUS LIKE 'Slow_queries';

# Check buffer pool efficiency
SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';

Install mysqladmin for command-line monitoring: sudo apt install mysql-client -y

Create a monitoring script that alerts you to performance issues:

#!/bin/bash
CONNECTIONS=$(mysql -u monitor_user -p'password' -e "SHOW STATUS LIKE 'Threads_connected';" | tail -1 | awk '{print $2}')

if [ $CONNECTIONS -gt 80 ]; then
    echo "High connection count: $CONNECTIONS" | mail -s "MySQL Alert" admin@example.com
fi

Troubleshooting Common MySQL Issues

MySQL problems usually appear as connection errors, slow queries, or startup failures. Here's how to diagnose and fix common issues:

Connection refused errors typically indicate MySQL isn't running or listening on the wrong port. Check the service status and configuration:

sudo systemctl status mysql
sudo netstat -tlnp | grep 3306

Slow query performance often stems from missing indexes or inefficient queries. Enable the slow query log and analyze problematic queries:

sudo mysqldumpslow /var/log/mysql/slow.log

Out of memory errors suggest insufficient innodb_buffer_pool_size for your workload. Monitor memory usage with free -h.

Adjust MySQL configuration accordingly.

Startup failures require checking the MySQL error log: sudo tail -f /var/log/mysql/error.log

Frequently Asked Questions

How much RAM should I allocate to MySQL on a VPS?

Allocate 70-80% of your available RAM to MySQL's InnoDB buffer pool for database-focused servers. For mixed-use VPS hosting web applications, limit MySQL to 50-60%.

This leaves memory for web servers and other processes.

Should I use MySQL 8.0 or stick with MySQL 5.7?

MySQL 8.0 offers significant performance improvements, better security defaults, and active support. Upgrade to MySQL 8.0 unless you have specific compatibility requirements with legacy applications.

How often should I backup MySQL databases?

Daily backups work for most applications. High-transaction systems may require hourly backups or real-time replication.

Test your backup restoration process regularly to ensure data integrity.

Can I run multiple MySQL instances on one VPS?

Yes, but each instance requires separate configuration files, data directories, and port assignments. This approach works for development environments but complicates production management.

What's the difference between MyISAM and InnoDB storage engines?

InnoDB supports transactions, foreign keys, and crash recovery. MyISAM offers faster read performance but lacks transaction support.

Use InnoDB for modern applications unless you have specific MyISAM requirements.