How to Install and Configure MySQL 8 on a Linux VPS: Complete Guide — CLIQHOST

MySQL 8 is the world's most popular relational database management system and powers the majority of modern web applications — from WordPress and Magento to custom Python or PHP projects. If you have a SSD VPS or an NVMe VPS and want to run databases fast and securely, this guide walks you through everything you need: installation, hardening, performance tuning, user management, and automated backups.

Why MySQL 8 Over Older Versions?

MySQL 8 brought significant improvements over version 5.7:

Window Functions and Common Table Expressions (CTE) — more complex and readable SQL queries.
Full UTF-8mb4 support — correct Unicode handling by default.
Roles — simplified permission management.
InnoDB improvements — better performance under high concurrency.
Native JSON — extended JSON data support directly in tables.
Enhanced security — caching_sha2_password authentication by default.

On a VPS with dedicated resources, MySQL 8 leverages available hardware more efficiently than older versions.

Prerequisites

Before you begin, make sure you have:

A VPS running Ubuntu 22.04 LTS or Debian 12 (commands are similar for both).
Root access or a user with sudo privileges.
At least 1 GB RAM (2+ GB recommended for production).
A working SSH connection.

If you don't have a server yet, you can choose a suitable SSD VPS plan from CLIQHOST.

Step 1: Update the System

Always start with an up-to-date system:

sudo apt update && sudo apt upgrade -y

This command refreshes the package list and installs the latest security patches.

Step 2: Install MySQL 8

On Ubuntu 22.04, MySQL 8 is available directly from the official repository:

sudo apt install mysql-server -y

Verify the installed version:

mysql --version
# mysql  Ver 8.0.x Distrib 8.0.x, for Linux (x86_64)

Start and enable the MySQL service at boot:

sudo systemctl start mysql
sudo systemctl enable mysql

Check the service status:

sudo systemctl status mysql

You should see active (running) highlighted in green.

Installing from the Official MySQL Repository (Optional)

If you need the absolute latest version directly from MySQL:

wget https://dev.mysql.com/get/mysql-apt-config_0.8.29-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.29-1_all.deb
sudo apt update
sudo apt install mysql-server -y

Step 3: Secure the MySQL Installation

MySQL ships with an interactive security script that you must run:

sudo mysql_secure_installation

The script will ask:

VALIDATE PASSWORD COMPONENT — We recommend enabling it (Y) and choosing level STRONG (2) for production passwords.
Root password — Set a strong password and store it securely.
Remove anonymous users — Type Y (removes anonymous users).
Disallow root login remotely — Type Y (root should never connect remotely).
Remove test database — Type Y (removes the test database).
Reload privilege tables — Type Y.

These settings eliminate the most common attack vectors for an exposed MySQL database.

Step 4: Connect to MySQL as Root

On Ubuntu 22.04, the MySQL root user uses socket authentication:

sudo mysql

Or, if you set a password:

mysql -u root -p

Once inside, you'll see the mysql> prompt.

Step 5: Create Databases and Users

Never run applications as the root user. Create dedicated users with minimal permissions:

-- Create a new database
CREATE DATABASE myapp_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create a new user
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'StrongPassword!2025';

-- Grant privileges only on the specific database
GRANT ALL PRIVILEGES ON myapp_db.* TO 'myapp_user'@'localhost';

-- Apply changes
FLUSH PRIVILEGES;

Verify existing users:

SELECT user, host FROM mysql.user;

Creating a User with Remote Access (If Needed)

If your application runs on a separate server:

CREATE USER 'myapp_user'@'192.168.1.100' IDENTIFIED BY 'StrongPassword!2025';
GRANT ALL PRIVILEGES ON myapp_db.* TO 'myapp_user'@'192.168.1.100';
FLUSH PRIVILEGES;

Important: remote access must be restricted strictly to known IP addresses.

Step 6: Tune MySQL for Performance

The main configuration file is /etc/mysql/mysql.conf.d/mysqld.cnf. Edit it:

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

Add or modify the following settings under the [mysqld] section:

[mysqld]
# InnoDB Buffer Pool (50-70% of available RAM)
innodb_buffer_pool_size = 512M

# Buffer pool instances (1 per GB of buffer pool)
innodb_buffer_pool_instances = 1

# InnoDB log file size
innodb_log_file_size = 128M

# Maximum simultaneous connections
max_connections = 150

# Timeouts
wait_timeout = 300
interactive_timeout = 300

# Thread cache
thread_cache_size = 16

# Table cache
table_open_cache = 2000

# Sort buffer (per thread)
sort_buffer_size = 2M

# Slow query log — useful for debugging
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2

# Default charset
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

After making changes, restart MySQL:

sudo systemctl restart mysql

Practical `innodb_buffer_pool_size` Reference

VPS RAM	Recommended Buffer Pool
1 GB	512 MB
2 GB	1 GB
4 GB	2.5 GB
8 GB	5 GB

On a high-performance NVMe VPS, you can allocate more memory to the buffer pool to fully exploit fast disk speeds.

Step 7: Network-Level Security

By default, MySQL listens on 127.0.0.1 (localhost only). Verify:

sudo ss -tlnp | grep mysql
# LISTEN 0 151 127.0.0.1:3306 0.0.0.0:*

If you don't need remote access, keep bind-address = 127.0.0.1 in your config — this is the safest setup.

If remote access is required and you have UFW active, allow connections only from a specific IP:

sudo ufw allow from 203.0.113.50 to any port 3306

Never expose port 3306 to the public internet (0.0.0.0). Our server management services include MySQL hardening for production environments.

Step 8: Automated Backups with mysqldump

Create a simple backup script:

sudo nano /usr/local/bin/mysql-backup.sh

Script contents:

#!/bin/bash
DATE=$(date +%Y-%m-%d_%H-%M)
BACKUP_DIR="/var/backups/mysql"
MYSQL_USER="root"
MYSQL_PASS="your_root_password"

mkdir -p $BACKUP_DIR

# Backup all databases
mysqldump -u $MYSQL_USER -p$MYSQL_PASS --all-databases --single-transaction \
  --quick --lock-tables=false | gzip > $BACKUP_DIR/all-databases-$DATE.sql.gz

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

echo "Backup completed: $BACKUP_DIR/all-databases-$DATE.sql.gz"

Make the script executable:

sudo chmod +x /usr/local/bin/mysql-backup.sh

Add a cron job for daily backups at 02:00:

sudo crontab -e

0 2 * * * /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

Restoring from a Backup

gunzip < /var/backups/mysql/all-databases-2025-01-15_02-00.sql.gz | mysql -u root -p

Step 9: Monitoring MySQL

Real-time Status Checks

SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW PROCESSLIST;

Checking Active Configuration Variables

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'max_connections';

Install MySQLTuner (Recommended)

MySQLTuner is a Perl script that analyses your configuration and provides concrete recommendations:

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl mysqltuner.pl --user root --pass your_password

The output will show which parameters are worth adjusting based on your real server load.

Step 10: Upgrading from MySQL 5.7 to MySQL 8

If you're migrating from 5.7, the key steps are:

Full backup before anything else:

mysqldump -u root -p --all-databases > backup-pre-upgrade.sql

Check compatibility:

mysqlcheck -u root -p --all-databases --check-upgrade

Update packages and run:
```
sudo mysql_upgrade -u root -p
```
Test your applications — verify that caching_sha2_password is compatible with your drivers. If not, temporarily switch:
```
ALTER USER 'myapp_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
```

Common Mistakes to Avoid

❌ Never leave root with remote access — this is the most common breach vector.
❌ Don't use weak passwords — MySQL 8 rejects them when VALIDATE PASSWORD is active.
❌ Don't ignore the slow query log — slow queries are the primary cause of application sluggishness.
❌ Don't skip backups — a server without backups is not a production server.
❌ Don't grant ALL ON *.* to application users — use minimum required privileges.

Conclusion

MySQL 8 on a Linux VPS is a robust and performant combination for any serious web application. By following the steps in this guide, you'll have a secured installation, tuned for your server's resources, with automated backups in place.

If you'd rather focus on building applications than managing servers, the CLIQHOST team offers managed server administration where we handle MySQL configuration, security, and monitoring for you.

Pick an SSD VPS or NVMe VPS from CLIQHOST that fits your project's needs, and you'll have a solid foundation for any MySQL 8–powered application. For questions or technical support, contact us anytime.

How to Install and Configure MySQL 8 on a Linux VPS: Complete Guide — CLIQHOST