How to Install MySQL 8 on Ubuntu 24.04

MySQL 8 is the workhorse relational database behind a staggering share of the world's web applications, and installing it on your own Ubuntu 24.04 VPS gives you the same engine that powers WordPress, Magento, Drupal, Joomla, and thousands of SaaS products — without the per-hour charges of managed services like Amazon RDS or Aurora. This guide walks you through a production-grade MySQL 8 deployment on Ubuntu 24.04 LTS: installing from the Oracle APT repository, running mysql_secure_installation, creating scoped users, tuning InnoDB for your hardware, enabling TLS, configuring replication, and setting up nightly backups with mysqldump and Percona XtraBackup.

Skip the setup? Deploy MySQL 8 in one click with our pre-configured database image. Launch a CloudCore Starter VPS now and start querying in under 60 seconds.

Table of Contents

What is MySQL 8?

MySQL is the most widely deployed open-source relational database in the world. MySQL 8.0 is the current long-term support (LTS) release, first shipped in April 2018 and receiving bug fixes and security patches through April 2026. The newer MySQL 8.4 LTS line extends support through 2032. Both lines are based on the same core engine and share the same operational patterns.

MySQL 8 is a major upgrade over the legacy 5.7 series. The headline changes include InnoDB as the default and only fully-supported storage engine (MyISAM is still present for compatibility but should be avoided for new work), transactional data dictionary storing metadata in InnoDB tables instead of .frm files, native JSON data type with indexing and a rich function library, window functions (OVER, PARTITION BY, ROW_NUMBER, RANK, LAG, LEAD) that make analytical queries vastly more expressive, and common table expressions (CTEs) including recursive CTEs for hierarchical data. The default character set moved to utf8mb4 with full 4-byte UTF-8 coverage (emoji, CJK characters, supplementary planes), and the default authentication plugin changed from mysql_native_password to caching_sha2_password — a stronger algorithm that occasionally trips up older client libraries.

MySQL 8 also introduced invisible indexes (test an index's impact before making it visible to the optimizer), descending indexes (genuinely descending rather than just reverse-scanned), instant ADD COLUMN on many tables (no table rebuild), and roles for grouping privileges. Replication gained GTIDs by default, group replication for multi-primary clusters, and the new CHANGE REPLICATION SOURCE TO / SHOW REPLICA STATUS terminology (the legacy CHANGE MASTER TO / SHOW SLAVE STATUS commands still work but are deprecated).

On the application side, MySQL 8 is what sits behind WordPress, WooCommerce, Magento, Drupal, Joomla, phpBB, and the majority of PHP web apps. It is the default database for LAMP and LEMP stacks, a first-class target for every major ORM (Laravel Eloquent, Django ORM, Hibernate, Prisma, SQLAlchemy, ActiveRecord), and the storage layer for countless SaaS products.

Why Self-Host MySQL vs. RDS/Aurora?

Managed database services like Amazon RDS, Aurora, Google Cloud SQL, and Azure Database for MySQL are convenient, but they charge a steep premium for that convenience. A db.t4g.medium RDS instance (2 vCPU, 4 GB RAM) runs roughly $60–75/month before storage, backups, and cross-AZ replication — often pushing the effective monthly cost above $120 for a modest production workload. Aurora's consumption pricing can be even higher for write-heavy apps.

Self-hosting MySQL 8 on a VPS offers concrete advantages:

• 10x lower cost — A CloudCore Starter VPS at EUR 7.99/month runs MySQL comfortably for a small-to-medium production workload. Scale up to CloudCore Professional (EUR 19.99/month) for 12 GB RAM and 6 vCPU, still a fraction of equivalent managed pricing.
• No egress charges — Every byte leaving an RDS instance costs money. A self-hosted VPS includes generous or unmetered bandwidth.
• Full configuration control — Change any my.cnf parameter, install any plugin, use any storage engine, run any MySQL version. No parameter group restrictions, no version lag behind upstream.
• Root OS access — Inspect iostat, read slow query logs with pt-query-digest, run perf top, install Percona Toolkit. On RDS you get Performance Insights and that's it.
• Predictable performance — Dedicated vCPU and local NVMe storage often outperform same-spec managed instances that share hardware and use network-attached storage.
• Data sovereignty — Your data lives on infrastructure you pick, in a jurisdiction you pick, under a privacy policy you write.
• Easy dev/staging parity — Spin up an identical VPS for staging at the same cost as a single RDS instance.

Prerequisites

Before you begin, make sure you have:

• A VPS running Ubuntu 24.04 LTS with root or sudo access
• SSH access to your server (PuTTY on Windows, or the built-in terminal on macOS/Linux)
• At least 2 GB of RAM (4 GB+ recommended for any real workload, 8 GB+ for high-traffic production)
• At least 20 GB of free disk space for the MySQL install and initial data
• A registered domain pointing at the server if you plan to enable TLS with Let's Encrypt certificates (optional)

Recommended Plan: CloudCore Starter

For a small-to-medium MySQL workload (a few hundred concurrent connections, 10–50 GB data), we recommend the CloudCore Starter plan:

- 4 vCPU cores

- 8 GB RAM

- 75 GB NVMe SSD

- Unmetered bandwidth

- EUR 7.99/month

The NVMe SSD is especially valuable for MySQL — InnoDB is IOPS-hungry on write-heavy workloads, and NVMe typically delivers 10–30x the IOPS of spinning disks or cheap SATA SSDs. For larger datasets or replication setups, step up to CloudCore Professional.

Connect to your server via SSH to get started:

ssh root@your-server-ip

Step 1: Update System Packages

Start by updating your package index and upgrading installed packages. This ensures you have the latest security patches and that dependency resolution works correctly during the MySQL install.

sudo apt update && sudo apt upgrade -y

Expected output (abbreviated):

Hit:1 http://archive.ubuntu.com/ubuntu noble InRelease
Hit:2 http://archive.ubuntu.com/ubuntu noble-updates InRelease
Reading package lists... Done
Building dependency tree... Done
0 upgraded, 0 newly installed, 0 to remove and 0 not upgraded.

If your kernel was updated, reboot before continuing:

sudo reboot

Install a few small utilities you will use during setup:

sudo apt install -y wget curl gnupg lsb-release ca-certificates

Step 2: Add the Oracle MySQL APT Repository

Ubuntu 24.04's default repositories ship MySQL 8.0 server under the mysql-server package, which is perfectly fine for most use cases. However, if you want the very latest MySQL 8.4 LTS (or MySQL 9 innovation releases), pull directly from Oracle's official APT repository. The Oracle repo also receives security updates faster than Canonical's universe packages.

Option A: Oracle's Official APT Repository (Recommended)

Download and install the MySQL APT configuration package:

cd /tmp
wget https://dev.mysql.com/get/mysql-apt-config_0.8.33-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.33-1_all.deb

An interactive dialog appears. Select:

If you ever want to switch series later, re-run:

sudo dpkg-reconfigure mysql-apt-config

Update the package index so APT sees the new repository:

sudo apt update

Expected output includes lines like:

Get:5 https://repo.mysql.com/apt/ubuntu noble/mysql-8.4-lts amd64 Packages

Option B: Ubuntu's Default Repository

If you prefer to stay on Canonical's packaging and Ubuntu 24.04's shipped MySQL 8.0, skip Option A entirely. You can still follow the rest of this guide unchanged — mysql-server works the same way either way.

Step 3: Install MySQL Server

Install the server package, the client tools, and the shell:

sudo apt install -y mysql-server mysql-client

The installer takes 30–60 seconds. On the Oracle repository, you may be prompted to choose the default authentication plugin:

• "Use Strong Password Encryption (RECOMMENDED)" — selects caching_sha2_password, MySQL 8's default. Pick this unless you have clients on very old connector libraries that cannot be upgraded.
• "Use Legacy Authentication Method" — falls back to mysql_native_password. Only pick this if you know you need it.

Step 4: Verify the Installation

Check the installed version:

mysql --version

Expected output:

mysql  Ver 8.4.4 for Linux on x86_64 (MySQL Community Server - GPL)

Confirm the service is running:

sudo systemctl status mysql

Expected output:

● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; preset: enabled)
     Active: active (running) since Wed 2026-04-16 10:00:00 UTC; 1min ago
   Main PID: 1234 (mysqld)
     Status: "Server is operational"
      Tasks: 38 (limit: 4554)
     Memory: 365.2M
        CPU: 1.842s
     CGroup: /system.slice/mysql.service
             └─1234 /usr/sbin/mysqld

Connect to the server as root (on a fresh install via the Oracle repo you set a root password during dpkg; on Ubuntu's repo, root uses socket authentication, so just use sudo):

sudo mysql

You should see:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.4.4 MySQL Community Server - GPL
mysql>

Exit with EXIT;.

Step 5: Run mysql_secure_installation

This script locks down the default installation — it sets a strong root password, removes the anonymous user, disallows remote root login, drops the test database, and reloads the grant tables. Run it now:

sudo mysql_secure_installation

Walk through each prompt carefully:

1. VALIDATE PASSWORD COMPONENT

Would you like to setup VALIDATE PASSWORD component?
Press y|Y for Yes, any other key for No: y

Answer y. Then choose the strength level:

There are three levels of password validation policy:
LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2

Pick 2 (STRONG) for production.

2. Root password

Please set the password for root here.
New password: 
Re-enter new password: 

Use a randomly-generated password of at least 20 characters. Store it in your password manager. Never reuse it.

3. Remove anonymous users

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y

Always answer y. Anonymous users let anyone connect without credentials.

4. Disallow root login remotely

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y

Answer y. Root should only ever log in locally via socket or SSH-tunneled TCP. If you ever need root over the network, create a separate dbadmin user with similar privileges from a specific IP.

5. Remove test database

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y

Answer y. The test database is a historical artifact and gives unnecessary attack surface.

6. Reload privilege tables

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y

Answer y to apply all changes immediately.

Your MySQL instance is now minimally hardened. But there is much more to do.

Step 6: Create Application Users and Databases

Never let your application connect as root. Create a dedicated user per application, with privileges scoped to exactly the database(s) that application needs.

Log in as root:

mysql -u root -p

Create a Database with Proper Character Set

CREATE DATABASE myapp
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

The utf8mb4 character set supports the full Unicode range (including emoji and CJK characters beyond the Basic Multilingual Plane). The utf8mb4_unicode_ci collation provides accurate case-insensitive sorting for most languages. MySQL 8's default is already utf8mb4_0900_ai_ci — both are good choices; pick utf8mb4_unicode_ci if you need maximum compatibility with legacy apps.

Create an Application User

CREATE USER 'myapp'@'%' IDENTIFIED BY 'a-long-random-password-here';

The 'myapp'@'%' form means "user myapp from any host." If the app is on the same server as MySQL, restrict it:

CREATE USER 'myapp'@'localhost' IDENTIFIED BY 'a-long-random-password-here';

If the app is on a specific other server:

CREATE USER 'myapp'@'10.0.0.5' IDENTIFIED BY 'a-long-random-password-here';

Grant Scoped Privileges (Not . !)

This is the single most common MySQL security mistake: granting ALL PRIVILEGES ON .. Never do this. Grant only what the user actually needs, scoped to the specific database:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX, REFERENCES
  ON myapp.*
  TO 'myapp'@'%';

For a read-only reporting user:

CREATE USER 'reports'@'%' IDENTIFIED BY 'another-strong-password';
GRANT SELECT ON myapp.* TO 'reports'@'%';

For a backup user:

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'yet-another-strong-password';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, PROCESS, RELOAD, REPLICATION CLIENT
  ON .
  TO 'backup'@'localhost';

Apply the grants:

FLUSH PRIVILEGES;

Verify with SHOW GRANTS

SHOW GRANTS FOR 'myapp'@'%';

Expected output:

+----------------------------------------------------------------------+
| Grants for myapp@%                                                   |
+----------------------------------------------------------------------+
| GRANT USAGE ON . TO myapp@%                                    |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX,    |
|   REFERENCES ON myapp.* TO myapp@%                             |
+----------------------------------------------------------------------+

About caching_sha2_password

MySQL 8's default authentication plugin is caching_sha2_password. It is much stronger than the legacy mysql_native_password, but older clients can hit compatibility problems:

• PHP < 7.2.8 cannot negotiate it at all
• Some MySQL Workbench versions older than 8.0.11 require a manual driver update
• Very old MySQL connectors (Connector/J < 8.0.9, Connector/ODBC < 8.0.11, Connector/NET < 8.0.10, Connector/Python < 8.0.11) do not support it

CREATE USER 'legacyapp'@'%'
  IDENTIFIED WITH mysql_native_password
  BY 'strong-password';

Exit the MySQL shell:

EXIT;

Step 7: Tune the MySQL Configuration

The default MySQL configuration is conservative. To get real performance, edit the main configuration file:

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

Under the [mysqld] section, add or adjust the following:

[mysqld]
--- Network ---
bind-address          = 127.0.0.1           # localhost only by default
bind-address        = 0.0.0.0             # uncomment if apps on other servers need access
port                  = 3306
max_connections       = 200                 # raise if you see "Too many connections"
--- InnoDB (the important part) ---
innodb_buffer_pool_size         = 4G        # 50-70% of total RAM on a dedicated DB server
innodb_buffer_pool_instances    = 4         # 1 instance per ~1 GB buffer pool
innodb_log_file_size            = 512M      # larger = better write throughput, longer crash recovery
innodb_log_buffer_size          = 32M
innodb_flush_log_at_trx_commit  = 1         # 1 = ACID, 2 = fast but loses ~1s on crash, 0 = fastest
innodb_flush_method             = O_DIRECT  # bypass OS cache, better for SSD/NVMe
innodb_file_per_table           = ON        # one .ibd per table (cleaner backups, shrinkable)
innodb_io_capacity              = 2000      # NVMe: 2000-10000, SATA SSD: 1000-2000, HDD: 200
innodb_io_capacity_max          = 4000
--- Binary logging (required for replication and point-in-time recovery) ---
server-id                       = 1
log_bin                         = /var/log/mysql/mysql-bin.log
binlog_format                   = ROW
binlog_expire_logs_seconds      = 604800    # 7 days
sync_binlog                     = 1         # durability, slight write cost
--- Slow query log ---
slow_query_log                  = 1
slow_query_log_file             = /var/log/mysql/mysql-slow.log
long_query_time                 = 1         # log queries taking > 1 second
log_queries_not_using_indexes   = 1
--- Character set defaults ---
character-set-server            = utf8mb4
collation-server                = utf8mb4_unicode_ci
--- Temp tables and sort ---
tmp_table_size                  = 64M
max_heap_table_size             = 64M
sort_buffer_size                = 4M
join_buffer_size                = 4M

Sizing innodb_buffer_pool_size

The buffer pool is where InnoDB caches data and indexes. On a dedicated database server, size it to 50–70% of total RAM:

About the Query Cache

If you are migrating from MySQL 5.7, note: the query cache was removed in MySQL 8.0. Do not set query_cache_size or query_cache_type — MySQL will refuse to start with those parameters present. InnoDB's buffer pool combined with the optimizer and modern proxy-level caches (ProxySQL, application-level Redis) replace it.

Save the file and restart MySQL:

sudo systemctl restart mysql

Verify the new buffer pool size took effect:

mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

Expected output:

+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 4294967296 |
+-------------------------+------------+

Step 8: Configure the Firewall

If your application lives on the same VPS as MySQL, keep bind-address = 127.0.0.1 and do not open port 3306 to the internet at all. Only the local loopback can connect.

If you do need remote access (app server on a different host, analyst connecting from the office), open port 3306 only to specific trusted IPs:

sudo ufw allow from 203.0.113.50 to any port 3306 proto tcp
sudo ufw allow from 10.0.0.0/24 to any port 3306 proto tcp
sudo ufw enable
sudo ufw status verbose

Expected output:

Status: active
To                         Action      From
--                         ------      ----
3306/tcp                   ALLOW IN    203.0.113.50
3306/tcp                   ALLOW IN    10.0.0.0/24

Never run ufw allow 3306 with no source restriction. An open MySQL port is a beacon to credential-stuffing botnets.

If you need wider access, put MySQL behind a VPN (WireGuard, Tailscale) or SSH tunnel instead of exposing it publicly.

Step 9: Enable TLS Connections

MySQL 8 automatically generates a self-signed CA and server certificate on first start. They live in /var/lib/mysql/:

sudo ls -l /var/lib/mysql/*.pem

Expected output:

-rw-------  1 mysql mysql 1676 Apr 16 10:00 ca-key.pem
-rw-r--r--  1 mysql mysql 1112 Apr 16 10:00 ca.pem
-rw-r--r--  1 mysql mysql 1112 Apr 16 10:00 client-cert.pem
-rw-------  1 mysql mysql 1676 Apr 16 10:00 client-key.pem
-rw-------  1 mysql mysql 1676 Apr 16 10:00 private_key.pem
-rw-r--r--  1 mysql mysql 452  Apr 16 10:00 public_key.pem
-rw-r--r--  1 mysql mysql 1112 Apr 16 10:00 server-cert.pem
-rw-------  1 mysql mysql 1676 Apr 16 10:00 server-key.pem

Confirm TLS is active:

SHOW VARIABLES LIKE '%ssl%';

Expected output:

+-------------------------------+-----------------+
| Variable_name                 | Value           |
+-------------------------------+-----------------+
| have_openssl                  | YES             |
| have_ssl                      | YES             |
| ssl_ca                        | ca.pem          |
| ssl_cert                      | server-cert.pem |
| ssl_key                       | server-key.pem  |
+-------------------------------+-----------------+

Require TLS for Specific Users

To force a user to only connect over TLS:

ALTER USER 'myapp'@'%' REQUIRE SSL;
FLUSH PRIVILEGES;

For stronger requirements (mutual TLS with client cert):

ALTER USER 'myapp'@'%' REQUIRE X509;

Client-Side TLS

From the command line:

mysql -u myapp -p --ssl-mode=REQUIRED -h your-server-ip

Check the connection is encrypted:

\s

Look for SSL: Cipher in use is ....

Using a Public CA Certificate (Optional)

The auto-generated CA is self-signed. If you want clients to verify a real CA-issued certificate, obtain one from Let's Encrypt for a hostname that resolves to your server, then point ssl_ca, ssl_cert, and ssl_key in mysqld.cnf at the Let's Encrypt files:

ssl_ca   = /etc/letsencrypt/live/db.example.com/fullchain.pem
ssl_cert = /etc/letsencrypt/live/db.example.com/fullchain.pem
ssl_key  = /etc/letsencrypt/live/db.example.com/privkey.pem

Restart MySQL. Remember to grant the mysql user read access to the Let's Encrypt directory.

Step 10: Configure Replication (Optional)

Replication gives you a hot standby for failover, a read replica for scaling reads, or a dedicated server for backups and analytics.

On the Primary

In mysqld.cnf, ensure binary logging is on (you already did this in Step 7):

server-id = 1
log_bin   = /var/log/mysql/mysql-bin.log
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON

Restart MySQL. Create a replication user:

CREATE USER 'replica'@'%' IDENTIFIED BY 'replica-strong-password';
GRANT REPLICATION SLAVE ON . TO 'replica'@'%';
FLUSH PRIVILEGES;

Take a consistent snapshot of the primary (see the backups section). Copy the dump to the replica.

On the Replica

Install MySQL the same way, then set a different server-id:

server-id = 2
log_bin   = /var/log/mysql/mysql-bin.log
gtid_mode = ON
enforce_gtid_consistency = ON
read_only = ON
super_read_only = ON

Import the dump from the primary, then configure replication:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST     = 'primary.example.com',
  SOURCE_USER     = 'replica',
  SOURCE_PASSWORD = 'replica-strong-password',
  SOURCE_AUTO_POSITION = 1,
  SOURCE_SSL      = 1;
START REPLICA;

Check status:

SHOW REPLICA STATUS\G

Look for:

Replica_IO_Running: Yes
   Replica_SQL_Running: Yes
 Seconds_Behind_Source: 0

Group Replication

For multi-primary active-active clusters of 3–9 nodes, MySQL 8 includes native Group Replication. It uses a Paxos-like consensus protocol to provide automatic failover, conflict detection, and consistent writes across nodes. Group Replication is the foundation of InnoDB Cluster, which bundles it with MySQL Router and MySQL Shell for a complete HA solution. Setting up Group Replication is beyond this guide's scope — see the official Group Replication documentation — but it is the right path for anyone needing true high availability without an external tool like Galera.

Backups with mysqldump and XtraBackup

Logical Backup with mysqldump

mysqldump produces a text file of SQL statements that recreate your database. It is slow for large datasets but universal, portable, and the default starting point.

mysqldump \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --hex-blob \
  --default-character-set=utf8mb4 \
  -u backup -p myapp \
  | gzip > /var/backups/mysql/myapp-$(date +%Y%m%d).sql.gz

Flag breakdown:

• --single-transaction — takes the dump inside a single consistent transaction, no locks needed on InnoDB tables
• --routines — include stored procedures and functions
• --triggers — include triggers
• --events — include scheduled events
• --hex-blob — encode binary columns as hex (safer across encodings)

Nightly Backup Script

Create /usr/local/bin/mysql-backup.sh:

#!/bin/bash
set -euo pipefail
BACKUP_DIR="/var/backups/mysql"
RETENTION_DAYS=14
DATE=$(date +%Y%m%d-%H%M%S)
USER="backup"
PASS="backup-user-password"
mkdir -p "$BACKUP_DIR"
Dump each user database
for DB in $(mysql -u "$USER" -p"$PASS" -e 'SHOW DATABASES;' -s --skip-column-names \
    | grep -Ev '^(information_schema|performance_schema|mysql|sys)$'); do
  mysqldump \
    --single-transaction \
    --routines --triggers --events \
    --hex-blob \
    -u "$USER" -p"$PASS" "$DB" \
    | gzip > "$BACKUP_DIR/${DB}-${DATE}.sql.gz"
done
Delete backups older than retention window
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete

Make it executable and schedule it:

sudo chmod 700 /usr/local/bin/mysql-backup.sh
sudo crontab -e

Add:

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

Physical Backup with Percona XtraBackup

For large databases (50 GB+), mysqldump is too slow. Percona XtraBackup takes hot binary backups of the InnoDB files while the server is running, with negligible performance impact.

Install:

wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo dpkg -i percona-release_latest.generic_all.deb
sudo percona-release enable-only tools release
sudo apt update
sudo apt install -y percona-xtrabackup-80

Take a backup:

sudo xtrabackup --backup \
  --user=backup --password='backup-user-password' \
  --target-dir=/var/backups/xtrabackup/$(date +%Y%m%d)

Prepare the backup (applies the crash-recovery log):

sudo xtrabackup --prepare \
  --target-dir=/var/backups/xtrabackup/20260416

To restore, stop MySQL, move the prepared files into place, and fix ownership:

sudo systemctl stop mysql
sudo rm -rf /var/lib/mysql/*
sudo xtrabackup --copy-back --target-dir=/var/backups/xtrabackup/20260416
sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mysql

mysqlpump for Parallel Logical Dumps

MySQL 8 ships mysqlpump, a parallel version of mysqldump:

mysqlpump --default-parallelism=4 --compress-output=ZLIB \
  -u backup -p \
  --result-file=/var/backups/mysql/full-$(date +%Y%m%d).sql.zlib

It is significantly faster than mysqldump for multi-table databases but lacks --single-transaction semantics across schemas, so use XtraBackup when consistency across databases matters.

Restoring a mysqldump Backup

gunzip < /var/backups/mysql/myapp-20260416.sql.gz \
  | mysql -u root -p myapp

Test your restore procedure on a staging VPS monthly. An untested backup is a hope, not a backup.

Monitoring and Observability

Built-in Tools

See who's connected and what they are running:

SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

Query the performance schema for expensive statements:

SELECT digest_text, count_star, avg_timer_wait/1e9 AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC
LIMIT 10;

Analyse slow queries with pt-query-digest:

sudo apt install -y percona-toolkit
pt-query-digest /var/log/mysql/mysql-slow.log | less

Prometheus with mysqld_exporter

For production monitoring, install mysqld_exporter and scrape it with Prometheus:

sudo useradd --no-create-home --shell /bin/false mysqld_exporter
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.1/mysqld_exporter-0.15.1.linux-amd64.tar.gz
tar xvf mysqld_exporter-*.tar.gz
sudo mv mysqld_exporter-*/mysqld_exporter /usr/local/bin/
sudo chown mysqld_exporter:mysqld_exporter /usr/local/bin/mysqld_exporter

Create a dedicated monitoring user:

CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'exporter-password'
  WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON . TO 'exporter'@'localhost';

Create /etc/.mysqld_exporter.cnf:

[client]
user=exporter
password=exporter-password

And a systemd unit at /etc/systemd/system/mysqld_exporter.service. Pair with the official Grafana MySQL dashboard (ID 7362) for a complete observability stack.

MySQL vs. MariaDB

MariaDB is a drop-in compatible fork of MySQL, created in 2009 by MySQL's original author Monty Widenius after Oracle acquired MySQL AB. The two databases share a common ancestry and, for most applications, are interchangeable: the wire protocol is compatible, the SQL dialect is nearly identical, and most client libraries work against either.

The key differences today:

• Licensing — MariaDB is GPL-only; MySQL Community Edition is GPL but Oracle also sells a proprietary Enterprise Edition with additional features (thread pool, audit plugin, Enterprise Monitor). This matters if you embed the database into a proprietary product.
• Storage engines — MariaDB ships Aria, MyRocks, ColumnStore, and Spider in addition to InnoDB. MySQL sticks to InnoDB.
• JSON handling — MySQL 8 has native JSON with indexing. MariaDB implements JSON as an alias for LONGTEXT with validation functions — usable but not quite the same.
• Window functions and CTEs — Both support them, but MySQL's optimizer typically handles complex analytical queries better.
• Enterprise features — MySQL 8 includes roles, resource groups, and Group Replication out of the box. MariaDB has its own Galera Cluster for HA.

Upgrading Between Versions

Moving from MySQL 8.0 to 8.4 LTS is straightforward when installed from the Oracle APT repo. Switch the repo channel first:

sudo dpkg-reconfigure mysql-apt-config
Select "mysql-8.4-lts" in the MySQL Server & Cluster prompt
sudo apt update

Take a full backup. Then upgrade:

sudo apt upgrade mysql-server mysql-client

MySQL 8.4 runs its own upgrade checks automatically on first start (no more mysql_upgrade binary — it was removed; the server handles upgrades internally). Watch the error log:

sudo tail -f /var/log/mysql/error.log

If everything comes up clean, you are done. If not, restore from backup and investigate.

For major-version jumps (5.7 → 8.0), always dump and reimport rather than in-place upgrade.

Troubleshooting

Viewing Logs

The MySQL error log is the single most useful troubleshooting resource:

sudo tail -f /var/log/mysql/error.log

Enable and review the slow query log (configured in Step 7):

sudo tail -f /var/log/mysql/mysql-slow.log

Use systemctl for service-level issues:

sudo journalctl -u mysql -n 100 --no-pager

FAQ

MySQL vs. PostgreSQL — which should I choose?

Both are excellent. MySQL is the pragmatic choice if you are running WordPress, Magento, Drupal, WooCommerce, or any PHP application where the ecosystem assumes it — plug-ins, themes, and hosting scripts almost universally default to MySQL. It is also generally easier to operate in replicated setups, with simpler primary/replica semantics and more mature tooling (Percona XtraBackup, ProxySQL). PostgreSQL wins on strict SQL standards compliance, richer data types (arrays, range types, hstore, JSONB with full indexing), more sophisticated query planner, and advanced features like partial indexes, exclusion constraints, and materialized views. For new greenfield applications, PostgreSQL is often the better technical choice; for CMS and e-commerce workloads, MySQL is still the default for a reason. See our How to Install PostgreSQL on Ubuntu 24.04 guide for the other side.

MySQL vs. MariaDB?

For most web applications, pick whichever ships easier with your framework. MySQL has stronger JSON handling and Group Replication; MariaDB has more alternative storage engines and a more permissive license. They are interchangeable for WordPress, Drupal, and similar CMS workloads. Pick MySQL if your stack is tested against it (most of the Oracle-world and many SaaS apps). Pick MariaDB if you want to stay in a fully GPL ecosystem or want features like MyRocks.

Do I need to worry about Oracle's licensing?

MySQL Community Edition (what you installed from the Oracle APT repo) is GPLv2 — free to use, modify, and deploy, including commercially. You do not pay Oracle anything for running this on your VPS. MySQL Enterprise Edition is proprietary and paid, but you do not need it for any standard web workload. The only license concern is if you want to redistribute MySQL as part of a proprietary product (then you'd need to think about the GPL's copyleft terms); for running a server, there's nothing to pay.

Should I use InnoDB or MyISAM?

Always InnoDB. It is the default in MySQL 8 and is the only engine that supports transactions, foreign keys, row-level locking, and crash recovery. MyISAM is a legacy engine kept for compatibility with very old applications and has table-level locking (a single write blocks all readers) plus no crash recovery. If you find a MyISAM table in a production database, convert it: ALTER TABLE mytable ENGINE=InnoDB;.

Should I self-host or use a managed service?

Self-host when: cost matters, you want full configuration control, your team is comfortable with Linux ops, or you have strict data-residency requirements. Use managed services (RDS, Cloud SQL, PlanetScale) when: you have no ops capacity, need automated multi-AZ failover out of the box, or your application has extreme scale (10k+ QPS) and you want to offload everything. For DM tenants running WordPress, WooCommerce, or typical SaaS workloads, self-hosting on a CloudCore VPS with nightly backups and a read replica is generally the right economic answer until you exceed $300–500/month in database spend.

What are my HA options for MySQL?

Three common patterns: (1) Primary/replica with manual failover — simplest, covered in Step 10. Good for recovering from data loss; manual failover takes minutes. (2) Primary/replica with Orchestrator or ProxySQL — automated failover with sub-minute RTO. GitHub runs this at scale. (3) Group Replication / InnoDB Cluster — multi-primary, consensus-based, automatic failover, no external tool needed. Best for true HA but more operational complexity. Galera Cluster (popular with MariaDB) is a fourth option but not native to MySQL 8. Start with option 1; graduate when your uptime requirements demand it.

Next Steps

Now that MySQL 8 is running on your VPS, here are recommended next steps to build on your setup:

• Install WordPress — MySQL 8 is the default database for WordPress. See our How to Install WordPress on Ubuntu 24.04 guide to deploy the full LAMP stack with this database.
• Set up a LAMP or LEMP stack — Pair MySQL with Apache (LAMP guide) or Nginx (LEMP guide) and PHP-FPM to run any modern PHP application.
• Try MariaDB for comparison — If you are curious about the MariaDB fork, spin up a second VPS and follow How to Install MariaDB on Ubuntu 24.04. Application compatibility is near-perfect for most workloads.
• Compare against PostgreSQL — For new projects, PostgreSQL is often a stronger choice. See How to Install PostgreSQL on Ubuntu 24.04.
• Install phpMyAdmin or Adminer — Get a browser-based admin UI for MySQL in five minutes. Pair with Nginx auth and IP allowlisting so it is not publicly exposed.
• Set up ProxySQL — For read/write splitting, connection pooling, and query routing across replicas. ProxySQL is the industry standard and runs comfortably on the same VPS for small workloads.
• Enable Prometheus monitoring — Deploy mysqld_exporter and Grafana (dashboard ID 7362) for a complete observability stack, including replication lag alerts.

Skip the Manual Install — Get MySQL 8 Pre-Configured

Our CloudCore Starter VPS can be deployed with MySQL 8, UFW, nightly automated backups, and TLS configured in a single click.

- MySQL 8.4 LTS pre-installed and hardened

- mysql_secure_installation already run with a rotated root password

- InnoDB buffer pool sized to your plan's RAM

- Nightly mysqldump backups with 14-day retention

- UFW locked down to loopback and your IPs only

- Auto-generated TLS certificates with REQUIRE SSL on application users

Deploy Your MySQL VPS Now — CloudCore Starter from EUR 7.99/month.