How to Install and Secure MySQL on Ubuntu

MySQL is the world's most widely used open-source relational database and the backbone of WordPress, countless web apps, and the classic LEMP/LAMP stacks. This guide installs MySQL 8 on Ubuntu 24.04, secures it properly, and creates an application database and user with least-privilege access.

Install MySQL

MySQL Server is in Ubuntu's default repositories:

sudo apt update
sudo apt install mysql-server -y

The service starts automatically. Confirm it:

systemctl status mysql

Secure the installation

MySQL ships a script that fixes the insecure defaults. Run it:

sudo mysql_secure_installation

Work through the prompts:

• Validate password component: enable it if you want enforced password strength.
• Remove anonymous users: yes.
• Disallow root login remotely: yes — root should only connect locally.
• Remove the test database: yes.
• Reload privilege tables: yes.

This one script closes the holes attackers scan for constantly. Pair it with the broader Linux VPS hardening checklist.

Set a root password / auth method

On Ubuntu, the MySQL root user authenticates via the auth_socket plugin by default — meaning you connect as root only from the system root account, with no password:

sudo mysql

That is actually a secure default. If an application genuinely needs password-based root (usually it should not), you can switch it, but the better pattern is to never use root for apps at all. Instead, create a dedicated user per app.

Create an app database and user

From the MySQL prompt (sudo mysql), create a database and a user scoped to it:

CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'a-strong-password-here';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Two things matter here. utf8mb4 is the correct charset for full Unicode including emoji — never use plain utf8. And 'appuser'@'localhost' restricts the user to local connections; the grant is scoped to appdb.* only, so a compromised app cannot touch other databases.

Test the app user

mysql -u appuser -p appdb

Enter the password and you should land at the mysql> prompt connected to appdb.

Keep it local

By default MySQL 8 on Ubuntu binds to 127.0.0.1, so it is not reachable from the internet — exactly what you want. Verify:

sudo ss -ltnp | grep 3306

You should see it listening on 127.0.0.1:3306. Only bind to a public address if you truly need remote database access, and if so, firewall port 3306 tightly with UFW and require TLS. For most apps, the database and app live on the same VPS and talk over localhost.

Backups

A database with no backup is a disaster waiting to happen. A simple logical backup:

mysqldump -u root appdb > appdb-$(date +%F).sql

Automate it with a nightly cron job.

Tune MySQL for a small VPS

MySQL 8's defaults assume a fairly generous server. On a small VPS you can rein in memory use by adjusting the InnoDB buffer pool — the single most important setting, holding cached data and indexes. Create /etc/mysql/mysql.conf.d/tuning.cnf:

[mysqld]
innodb_buffer_pool_size = 256M
max_connections = 75

Set the buffer pool to roughly half the RAM you want MySQL to use, and cap max_connections so a traffic spike cannot exhaust memory. Restart to apply:

sudo systemctl restart mysql

See what MySQL is doing

To inspect active queries and connections live, connect and run:

SHOW PROCESSLIST;
SHOW STATUS LIKE 'Threads_connected';

The error log at /var/log/mysql/error.log is the first place to look if the service will not start or a query behaves oddly. Right-sizing these values keeps MySQL fast without starving the rest of your stack.

FAQ

MySQL vs MariaDB — does it matter which I install?

MariaDB is a drop-in fork and either works for most apps. Ubuntu ships both; apt install mysql-server gets Oracle's MySQL 8, mariadb-server gets MariaDB. Pick one and stay consistent. This guide uses MySQL 8.

Why can't I log in as root with a password?

Ubuntu configures root to use auth_socket, so you connect with sudo mysql and no password. This is secure by design — create app-specific users rather than switching root to password auth.

Should I choose MySQL or PostgreSQL?

Both are excellent. MySQL is ubiquitous in the PHP/WordPress world; PostgreSQL has richer SQL features and stricter standards compliance. If you are unsure, our PostgreSQL install guide helps you compare.

How do I allow remote connections safely?

Change bind-address in /etc/mysql/mysql.conf.d/mysqld.cnf, create a user scoped to the remote host, restrict port 3306 to specific IPs in UFW, and require TLS. Prefer an SSH tunnel over opening the port whenever possible.

Nxeon VPS plans give you full root and NVMe storage to run MySQL fast and privately alongside your app — with free migration help to move an existing database across.