VPS guide

How to Install and Secure MySQL on Ubuntu

By Gander Web · Published 16 October 2026 · 3 min read

These steps are for MySQL 8.0 on Ubuntu 24.04. Debian 12 ships MariaDB instead, which works the same way for almost everything here; the differences are noted.

Install MySQL

sudo apt update
sudo apt install mysql-server
sudo systemctl status mysql

On Debian 12, install mariadb-server instead.

Run the security script

sudo mysql_secure_installation

It offers to turn on password strength checks, then removes anonymous users, blocks remote root logins and deletes the test database. Answer yes to each. On Ubuntu it skips setting a root password, because MySQL's root account authenticates with auth_socket: the system's root user gets in without a password. On Debian, run sudo mariadb-secure-installation.

Log in

sudo mysql

That's how you reach MySQL as root on Ubuntu. Type EXIT; to leave.

Create a database and user for each app

CREATE DATABASE shop CHARACTER SET utf8mb4;
CREATE USER 'shop'@'localhost' IDENTIFIED BY 'use-a-long-random-password';
GRANT ALL PRIVILEGES ON shop.* TO 'shop'@'localhost';

Each app gets a user that can only reach its own database, so a hacked plugin on one site can't read another site's data. Generate passwords with openssl rand -base64 24. utf8mb4 stores every character, including emoji.

Connect from your own computer

MySQL listens on 127.0.0.1 by default, so only programs on the server can connect. To use a desktop tool such as MySQL Workbench or DBeaver, open an SSH tunnel instead of opening MySQL to the internet:

ssh -L 3306:127.0.0.1:3306 user@your-vps-ip

While that's running, connect the tool to 127.0.0.1 port 3306. If MySQL also runs on your computer, use -L 3307:127.0.0.1:3306 and connect to port 3307.

Allow an app on another server

If an app on a different server needs the database, set bind-address = 0.0.0.0 in /etc/mysql/mysql.conf.d/mysqld.cnf (on Debian, /etc/mysql/mariadb.conf.d/50-server.cnf), create the user for that server's IP address, for example 'shop'@'203.0.113.20', and restart MySQL. Then allow only that address through the firewall:

sudo systemctl restart mysql
sudo ufw allow from 203.0.113.20 to any port 3306 proto tcp

Set memory for your VPS

The InnoDB buffer pool caches tables and indexes in RAM, and it's only 128 MB by default. Ideally it holds the data your sites use most. Create a settings file:

sudo nano /etc/mysql/mysql.conf.d/99-tuning.cnf

[mysqld]
innodb_buffer_pool_size = 1G

Leave room for the web server, PHP and the operating system. Restart MySQL and check the value it's using:

sudo systemctl restart mysql
sudo mysql -e "SELECT @@innodb_buffer_pool_size / 1024 / 1024 AS mb;"

On Debian, put the file in /etc/mysql/mariadb.conf.d/ and restart mariadb.

Find slow queries

Add these lines under [mysqld] in the same file and restart MySQL. Every query that takes more than a second is then written to the log:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

Back up the databases

sudo mysqldump --single-transaction --all-databases > all.sql

--single-transaction takes a consistent copy of InnoDB tables without locking your sites. To run it nightly and keep copies off the server, see backing up a Linux server with restic.

Questions

Related guides

Sources

1. Install and configure MySQL (Ubuntu Server docs)
2. mariadb-secure-installation (MariaDB docs)
3. InnoDB buffer pool (MariaDB docs)

Commands are for Ubuntu 24.04, Debian 12 and AlmaLinux 9, as of 16 October 2026. If something here no longer matches what you see, tell us and we'll update it.