Install MySQL or MariaDB and Secure It

On a LochStudios KVM VPS you install and look after the database engine yourself. MariaDB and MySQL both speak the same SQL for typical sites. Pick one, harden it, give each app its own user, and leave the listener on this machine.

This is not the path for shared hosting. Beginner and Standard plans already include unlimited MariaDB. Create those databases in cPanel: Create a MySQL Database and User.

Credentials, the IPv4, and an out-of-band console sit on the VPS in the portal. Sign in at the portal, open the server, and copy the login from there. If SSH from your computer will not connect, use the console.

Need a VPS first? See VPS.

Before you start

1. Sign in at the portal, open the VPS service, and keep that page open.
2. Connect with a sudo user, or root on a fresh box. See Connect to your VPS via SSH from macOS or Linux or from Windows.
3. Patch the OS and prefer a sudo user for daily work: First steps on a new VPS.
4. If you are logged in as root, omit sudo from the commands below.

Choose MariaDB or MySQL

Install one engine. Do not install both.

• MariaDB is the usual pick on our images. It matches the engine on our shared hosting, and Ubuntu, Debian, AlmaLinux, and Rocky Linux all ship it from the distro repos.
• MySQL 8 is fine when an application specifically asks for it.

If you want Apache or Nginx and PHP in the same sitting, use Install a LAMP stack or Install a LEMP stack instead of this article alone.

Install the packages

On Ubuntu or Debian, update the index, then install.

MariaDB:

sudo apt update
sudo apt install -y mariadb-server

MySQL:

sudo apt update
sudo apt install -y mysql-server

On AlmaLinux, Rocky Linux, or similar:

MariaDB:

sudo dnf install -y mariadb-server

MySQL (only if you chose it):

sudo dnf install -y mysql-server

Start the service and enable it on boot

MariaDB:

sudo systemctl enable --now mariadb
sudo systemctl status mariadb

MySQL:

sudo systemctl enable --now mysql
sudo systemctl status mysql

You want active (running). Press q to leave the status view.

If you are not sure which unit landed on this image:

systemctl list-units --type=service --all | grep -E 'mysql|mariadb'

Use that name in the rest of this article.

Run the hardening script

The packages ship with open defaults (anonymous users, a test database, remote root). Close those before you create an app database.

MariaDB:

sudo mariadb-secure-installation

sudo mysql_secure_installation is the same script on many images. MySQL uses that name.

The questions differ slightly by engine and version. Recommended answers:

• Current root password: press Enter if you have not set one yet (usual on a new VPS).
• unix_socket authentication: Y if asked. OS root can then run sudo mysql without a database password.
• Set or change the root password: optional when unix_socket is on. A long unique password is fine if you want one as well.
• Remove anonymous users: Y.
• Disallow remote root login: Y.
• Remove the test database: Y.
• Reload privilege tables: Y.

If the script offers the password-validation plugin, you can enable it. Use a long unique password either way. See Create strong passwords and use a password manager.

Check you can open a prompt

sudo mysql

On some MariaDB images the client is mariadb. Either should give you a mysql> or MariaDB [(none)]> prompt.

SELECT VERSION();
SHOW DATABASES;
EXIT;

EXIT; or exit both leave the prompt.

If this fails after a fresh install, confirm the service is running (sudo systemctl status mariadb or sudo systemctl status mysql). Still stuck? Open a support ticket and paste that status output (no passwords).

Create a database and an app user

Do not point a website at the root account. Create one database and one user per app. Keep the user on localhost so only processes on this VPS can sign in.

sudo mysql

CREATE DATABASE your_app_db;
CREATE USER 'your_user'@'localhost' IDENTIFIED BY 'choose-a-long-unique-password';
GRANT ALL PRIVILEGES ON your_app_db.* TO 'your_user'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Replace:

• your_app_db with the database name
• your_user with the username
• choose-a-long-unique-password with a password you store somewhere safe

That password is not your portal password, and it is not the Linux sudo password.

An older application that cannot speak MySQL 8's default plugin can use:

CREATE USER 'your_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'choose-a-long-unique-password';

Do not create 'your_user'@'%'. That user can try to connect from any host if the listener is public.

Test the app user

mysql -u your_user -p your_app_db

Type the app password when asked. A prompt means the grant worked. EXIT; to leave.

If the client asks for a password on the command line, skip that. A password in the shell history is a gift to the next person on the box.

Keep the engine on localhost

Apps on this VPS (WordPress, Laravel, a local PHP-FPM pool) should use host localhost or 127.0.0.1. They do not need a public database port.

Confirm what is listening:

sudo ss -tlnp | grep 3306

You want 127.0.0.1:3306 (and maybe [::1]:3306). You do not want 0.0.0.0:3306 or *:3306 unless you later pin a remote client on purpose.

If bind-address is wrong, find the config and edit it:

sudo grep -R "bind-address" /etc/mysql /etc/my.cnf /etc/my.cnf.d 2>/dev/null

Typical files:

• Ubuntu or Debian MariaDB: /etc/mysql/mariadb.conf.d/50-server.cnf
• Ubuntu or Debian MySQL: /etc/mysql/mysql.conf.d/mysqld.cnf
• AlmaLinux or Rocky Linux: a file under /etc/my.cnf.d/

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Set:

bind-address = 127.0.0.1

Save (Ctrl + X, then Y, then Enter) and restart the service you found earlier:

sudo systemctl restart mariadb

Or sudo systemctl restart mysql. Check ss again.

UFW already allows loopback. A local-only database does not need a port 3306 rule. See Set up a UFW firewall on Ubuntu.

Remote access (only if you really need it)

Most sites do not. Leave bind-address on 127.0.0.1 and skip this section.

From your laptop, for admin only: keep the listener local and open an SSH tunnel. Then your client talks to 127.0.0.1 on your computer.

ssh -L 3306:127.0.0.1:3306 youruser@203.0.113.10

Use the IPv4 and sudo user from the portal. Stop the tunnel when you are done.

From another of your servers (an app VPS talking to a database VPS):

1. On the database VPS, create the user for that client IPv4 only (not %):

```sql
CREATE USER 'your_user'@'203.0.113.80' IDENTIFIED BY 'choose-a-long-unique-password';
GRANT ALL PRIVILEGES ON yourappdb.* TO 'your_user'@'203.0.113.80';
FLUSH PRIVILEGES;
```

Swap in the real app-server IPv4 from that VPS in the portal.

1. Allow that IPv4 on the host firewall. Do not run sudo ufw allow 3306/tcp (that is the whole internet):

```bash
sudo ufw allow from 203.0.113.80 to any port 3306 proto tcp
```

1. Only then, if ss still shows 127.0.0.1:3306, set bind-address = 0.0.0.0 in the config above, restart the service, and confirm ss and sudo ufw status numbered.

Unsure whether you need any of that? Open a support ticket and we will walk through it with you.

Back up the databases

A dump only on this disk does not survive a wipe. Keep a copy you can download, and test a restore on a spare database before you need it.

Create a directory and a small script. This uses the unix_socket root path from the hardening step, so there is no password on the command line.

sudo mkdir -p /var/backups/mysql
sudo nano /usr/local/bin/backup-mysql.sh

#!/bin/bash
set -euo pipefail
BACKUP_DIR="/var/backups/mysql"
TIMESTAMP="$(date +%Y%m%d_%H%M%S)"
mkdir -p "$BACKUP_DIR"
mysqldump --all-databases --single-transaction --routines --triggers \
  > "$BACKUP_DIR/all_databases_${TIMESTAMP}.sql"
find "$BACKUP_DIR" -type f -name 'all_databases_*.sql' -mtime +7 -delete

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

If root cannot dump without a password, put the credentials in /root/.my.cnf (mode 600, owned by root) and leave them out of the script:

[client]
user=root
password=the-root-database-password

Schedule it as root. Cron on the VPS uses this machine's timezone (timedatectl), which is often UTC on a new image:

sudo crontab -e

20 2 * * * /usr/local/bin/backup-mysql.sh

Run the script once by hand and confirm a .sql file appeared. Copy dumps off the VPS on a rhythm that matches how much data you can afford to redo.

Watch disk: dumps can be large. See Monitor Server Resources.

Restore a single file onto a new empty database only after you have read it (a dump can replace everything if it was taken with --all-databases). If you want us on the call for a restore, open a support ticket.

Everyday SQL

At the mysql> prompt, as a user who is allowed to:

SHOW DATABASES;
SHOW TABLES;
SELECT user, host FROM mysql.user;
ALTER USER 'your_user'@'localhost' IDENTIFIED BY 'a-new-long-unique-password';
DROP USER 'your_user'@'localhost';
DROP DATABASE your_app_db;

Change the application config in the same sitting if you change that password. WordPress stores it in wp-config.php. See Fix "Error Establishing a Database Connection" in WordPress.

If something goes wrong

The service will not stay running

sudo systemctl status mariadb
sudo journalctl -u mariadb -n 50 --no-pager

Use mysql in both commands if that is the unit on this box. Open a support ticket and paste that output (no passwords, no contents of .my.cnf).

sudo mysql asks for a password and will not accept it

unix_socket may be off, or you set a root password and are not invoking the client as OS root. Use sudo mysql -u root -p and the database root password. If you have lost both the socket path and the password, use the console on the VPS in the portal and open a support ticket. Do not leave the server running with grant tables skipped.

The app cannot connect, but sudo mysql works

• Host in the app config must be localhost (or 127.0.0.1) when the site runs on this VPS.
• Username, database name, and password must match what you created. There is no cPanel prefix on a VPS.
• Confirm the user host: SELECT user, host FROM mysql.user;

You opened 3306 and now bots are knocking

sudo ufw delete allow 3306/tcp
sudo ufw status numbered

Put bind-address back to 127.0.0.1, restart the service, and confirm ss. Then open a support ticket if you want us to check the remaining rules with you.

What to do next

• Host firewall: Set up a UFW firewall on Ubuntu
• Automatic package patches: Enable Automatic Security Updates
• WordPress on this VPS: Install WordPress (VPS section)
• TLS for the site in front of the database: Get a Free SSL Certificate with Certbot
• Broader habits: Keep your website secure

Unsure about a step? Open a support ticket and we will walk through it with you. Tell us the VPS service and what you already ran. Do not send database passwords in the ticket.