How to Install and Secure MySQL on a VPS

What to Check Before Installing MySQL on a VPS

Installing MySQL on a VPS takes a few minutes. The part that matters starts after apt install finishes: who can connect, where the server listens, how the data will be backed up, and what happens when the disk fills up.

Start by matching the operating system to the database package your application actually supports. Ubuntu 22.04 and 24.04 commonly provide MySQL 8.0 packages in their default repositories. Debian 12 normally provides MariaDB 10.11 instead. MariaDB is in the same database family, but it is not interchangeable with MySQL in every configuration or application.

If your application requires MySQL 8.0 or 8.4, check compatibility before choosing the distribution repository, MySQL’s official repository, or a provider image. A random third-party installer run as root may be quick. Finding every file and repository it added later is not.

A short pre-install checklist

• Can you connect over SSH with a separate administrative account?
• Is the operating system up to date, and is the system clock correct?
• Is there enough disk space for the database, binary logs, and backups?
• Will the application and MySQL share the VPS, or will another server connect remotely?
• Does your provider offer snapshots, and have you remembered that a snapshot is not a complete backup?

I install new setups in a Proxmox virtual machine on my Dell OptiPlex 7050 first. If I break bind-address or a systemd override there, no customer site notices. There is nothing romantic about experimenting on production.

Installing MySQL on Ubuntu and Debian

The commands below use Ubuntu’s MySQL package layout. On Debian, check which database server the repository will install before you proceed:

cat /etc/os-release
apt-cache policy mysql-server mariadb-server

The Candidate line shows the package version your configured repositories would select. On Ubuntu, the usual installation is:

sudo apt update
sudo apt full-upgrade -y
sudo apt install -y mysql-server
sudo systemctl enable --now mysql
sudo systemctl status mysql --no-pager

enable --now starts the service immediately and enables it at boot. Seeing active (running) is encouraging. It is not the security review.

Check what is actually running:

mysql --version
sudo mysql -e "SELECT VERSION(), USER(), @@hostname;"

I always record @@hostname. I once mistook a production server for staging while preparing to remove an old log directory. Shell completion displayed the production hostname before I pressed Enter, and I stopped. My production prompts have been red ever since.

If the service does not start, read the logs

Before restarting MySQL repeatedly, find out why it failed. A reboot can hide the useful evidence.

sudo journalctl -u mysql -b --no-pager -n 100
sudo ss -lntp | grep 3306
sudo systemctl is-enabled mysql

journalctl shows messages from the current boot. ss shows which address is listening on TCP port 3306. For a local-only setup, I normally expect 127.0.0.1:3306, and possibly ::1:3306 when IPv6 is configured.

First security steps

Run the package’s security helper after installation:

sudo mysql_secure_installation

The questions vary with the MySQL version and distribution package. They may cover the root authentication method, anonymous users, the test database, and remote root access. Removing anonymous users, removing the test database, and refusing remote root access are sensible choices for most VPS installations.

Ubuntu packages commonly configure the database root account for Unix socket authentication. In that case, connect through the operating-system root account:

sudo mysql

Trying mysql -u root -p may fail, and that does not necessarily indicate a broken installation. Do not give your application the database root account. Create a separate account with only the permissions it needs.

Creating the application database and user

Generate a long, unpredictable password and store it in a password manager. Putting a password directly in a command can leave it in shell history, so I prefer entering it at the client prompt.

sudo mysql

CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
CREATE USER 'shop_app'@'localhost' IDENTIFIED BY 'long-and-random-password';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP
ON shop.* TO 'shop_app'@'localhost';
EXIT;

utf8mb4 is a sensible default for current applications because it supports four-byte Unicode characters. The utf8mb4_0900_ai_ci collation is available in MySQL 8.0, but it is not available in every MariaDB release. Check first if you are not running MySQL.

The application may not need CREATE, ALTER, or DROP after deployment. I often create a separate migration account with those permissions and keep the runtime account narrower. Convenience on day one becomes confusing access control three months later.

Review the result:

sudo mysql -e "SHOW GRANTS FOR 'shop_app'@'localhost';"

Then test the same connection method your application will use:

mysql -u shop_app -p -h 127.0.0.1 -D shop -e "SELECT DATABASE(), CURRENT_USER();"

There is a small difference between localhost and 127.0.0.1. The MySQL client may use a Unix socket for localhost; 127.0.0.1 forces TCP. Test both only if your application configuration might use both.

Should MySQL be exposed to the internet?

Usually, no. If the web application and MySQL run on the same VPS, keep the database on the loopback address. With Ubuntu’s MySQL packages, the setting is usually in:

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

I use a plain .vimrc because the editor I know is the editor I can use on an unfamiliar server. Check the [mysqld] section:

[mysqld]
bind-address = 127.0.0.1

Some packages leave the line commented with #. Validate the configuration before restarting:

sudo mysqld --validate-config
sudo systemctl restart mysql
sudo systemctl status mysql --no-pager
sudo ss -lntp | grep 3306

On the MySQL 8.0 versions I run, a successful mysqld --validate-config returns without output. I still check the service status and journal after the restart. A one-character typo can keep the database from starting.

If an application on another VPS must connect, do not open port 3306 to the entire internet. Allow only the known source address in the firewall:

sudo ufw allow from 203.0.113.25 to any port 3306 proto tcp
sudo ufw status numbered

If that address can change, a private network, VPN, or SSH tunnel may be easier to control. If you do not use UFW, apply the same restriction with nftables or your provider’s security group. A rule for 0.0.0.0/0 is often described as temporary and then forgotten.

When a remote database account is necessary

Limit the MySQL account to the source address instead of creating 'shop_app'@'%':

CREATE USER 'shop_app'@'203.0.113.25' IDENTIFIED BY 'long-and-random-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.*
TO 'shop_app'@'203.0.113.25';

The host portion is part of a MySQL account’s identity. 'shop_app'@'localhost' and 'shop_app'@'203.0.113.25' are different accounts. When a user exists but cannot connect, this usually explains why:

SELECT user, host FROM mysql.user;

Authentication and TLS

MySQL 8.0 uses caching_sha2_password for new users by default. Older PHP versions and client libraries may not support it. Before changing the server to an older authentication method, update the application driver if possible.

Inspect the authentication plugin used by existing accounts:

sudo mysql -e "SELECT user, host, plugin FROM mysql.user;"

For a database connection between separate servers, encrypt the traffic and make the client verify the server certificate. You can inspect some TLS-related settings with:

sudo mysql -e "SHOW VARIABLES LIKE 'require_secure_transport';"
sudo mysql -e "SHOW VARIABLES LIKE 'tls_version';"

Certificate paths, ownership, and client options depend on the distribution and MySQL version. A self-signed certificate without client-side CA verification can make traffic look encrypted while leaving the server identity unverified.

If remote access is required, configure CA verification in the application and require TLS for the account:

ALTER USER 'shop_app'@'203.0.113.25' REQUIRE SSL;

If the account does not exist, this command fails as it should. ALTER USER modifies an existing account; it does not create one. Compare the user and host values with your own installation first.

Resource usage and basic MySQL tuning

A common VPS mistake is assigning nearly all available RAM to MySQL. If nginx, PHP-FPM, and Redis share the machine, leave room for them and for the operating system. The right innodb_buffer_pool_size depends on the workload, connection count, and query patterns.

On a 4 GB VPS running a small application, starting around 1 GB and watching memory pressure is safer than assigning 2 GB by habit. I collect metrics first and tune second. Fancy numbers do not replace measurements.

Find the default option files and inspect the current values:

sudo mysqld --verbose --help 2>/dev/null | grep -A 1 "Default options"
sudo mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
sudo mysql -e "SHOW VARIABLES LIKE 'max_connections';"

Back up the configuration file before changing it:

sudo cp -a /etc/mysql/mysql.conf.d/mysqld.cnf 
  /etc/mysql/mysql.conf.d/mysqld.cnf.$(date +%F)

For a small installation, a starting point might look like this:

[mysqld]
innodb_buffer_pool_size = 1G
max_connections = 100
slow_query_log = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1

A high max_connections value does not create capacity. Every connection consumes memory, and the setting needs to fit the PHP-FPM process count and the application’s connection behavior. Slow query logging is useful, but it also needs rotation and disk monitoring.

After editing:

sudo mysqld --validate-config
sudo systemctl restart mysql
sudo systemctl --no-pager --full status mysql
sudo journalctl -u mysql -b -n 50 --no-pager

If the service fails, restore the configuration backup. I once spent longer admiring a tuning change than I spent checking whether the service could start with it. A database that will not boot is not tuned.

Backups must include a restore test

A provider snapshot is useful, but it is not a complete MySQL backup. Writes may continue while the snapshot is taken, and the snapshot normally stays with the same provider or failure domain. Keep database backups in separate storage as well.

For a small database, mysqldump is a reasonable starting point:

sudo install -d -m 700 /var/backups/mysql
sudo sh -c 'mysqldump --single-transaction --routines --triggers --databases shop > /var/backups/mysql/shop-$(date +%F).sql'

The shell redirection is inside sudo sh -c deliberately. If you write sudo mysqldump ... > /var/backups/..., your normal shell tries to create the file before sudo takes effect.

--single-transaction gives a consistent dump for InnoDB tables without holding a long table lock. Very large databases may need a physical backup tool such as Percona XtraBackup or MySQL Enterprise Backup instead.

Do not leave the only dump on the VPS. I copy backups to a separate machine with rsync and keep versioned, encrypted copies with borgbackup. Once a month I restore one. An untested backup is a file with good intentions.

On an Ubuntu package using socket authentication, a dump made with --databases shop can be restored in a test environment with:

sudo mysql < /path/to/shop-2026-08-30.sql

Use a test database or test VPS, not the live database. Check the character set, stored procedures, user privileges, application connectivity, and the age of the dump.

Checks to keep after installation

After MySQL is running, I monitor a few things continuously:

• The service state
• Disk usage and inode availability
• Connection counts and rejected connections
• InnoDB buffer pool usage
• Slow query volume
• The timestamp of the last successful backup

On my home Proxmox system, every machine runs node_exporter for Prometheus. Disk and RAM alerts have warned me about serious problems before I added detailed MySQL metrics. A MySQL exporter can help, but its account should have only the read permissions it needs. Monitoring does not need root.

For a quick disk check, I still reach for ncdu:

sudo ncdu -x /var/lib/mysql
sudo du -sh /var/log/mysql /var/lib/mysql

The -x flag keeps the scan on one filesystem. That matters when a mounted backup directory would otherwise distort the result.

Pay attention to retention when binary logging is enabled. Binary logs support point-in-time recovery, but unlimited retention can fill a VPS before the next alert reaches you. Set retention as part of the backup plan, not as an afterthought.

Security check commands

sudo ss -lntp | grep 3306
sudo mysql -e "SELECT user, host FROM mysql.user;"
sudo mysql -e "SHOW VARIABLES LIKE 'local_infile';"
sudo ufw status verbose

If the application does not need local_infile, consider disabling it. Changing the SSH port is not security by itself; strong authentication, current packages, firewall rules, least privilege, and monitored logs do more.

For the operating-system side, see 10 Essential Steps to Secure and Harden Your Linux Server. If you are preparing a fresh machine, Launch a Secure VPS in 30 Minutes | Pro Admin Guide is a useful companion for putting the setup in the right order.

Connecting the application to MySQL

Your application configuration contains the database host, port, name, username, and password. Keep the password out of Git repositories, error messages, and world-readable .env files. Check the file’s owner and permissions too:

sudo chown root:www-data /var/www/shop/.env
sudo chmod 640 /var/www/shop/.env
sudo -u www-data test -r /var/www/shop/.env && echo "ok"

This assumes the web process runs as www-data; verify the service user on your distribution before copying the command.

When the web server and database share a VPS, keep the connection on loopback. On separate servers, use a private network, firewall restrictions, and TLS together. Several small boundaries are easier to inspect than one large assumption.

If you run WordPress, Joomla, or Drupal, look at query caching, PHP-FPM, and page caching alongside database settings. I have seen people debate TTFB by the millisecond while the obvious page cache was disabled. Measure first, then fix the bottleneck you can actually see.

Final checks for a MySQL VPS

A green service state is satisfying, but the real test arrives a few days later. Is disk growth monitored? Did the backup complete? Can you restore it? Is port 3306 closed from the public internet? If you cannot answer those questions, the setup is still unfinished.

My minimum setup is an individual application account, MySQL listening on loopback, a provider firewall, backups stored elsewhere, a tested restore, and alerts for service and disk problems. Larger systems may add replication, point-in-time recovery, a separate database server, and connection pooling, but the basic discipline stays the same.

Before I close the terminal, I run SELECT @@hostname; once more. The two-second pause has saved me from at least one dangerous command. Which command on your server deserves the same pause?

Frequently asked questions

How do I set a MySQL root password on a VPS?

Ubuntu packages commonly configure root with Unix socket authentication, allowing access through sudo mysql. Create a separate account for applications instead of giving them root. If password-based root access is genuinely required, change the authentication method only after understanding the access path and its risks.

Should I expose MySQL port 3306 to the internet?

Not when the application and MySQL share the same VPS; use bind-address = 127.0.0.1. If remote access is unavoidable, allow only specific source IPs in the firewall, restrict the MySQL account by host, and use TLS with certificate verification.

Should I choose MySQL or MariaDB?

Choose according to the versions supported by your application and its plugins. Debian’s default MariaDB package can be mistaken for MySQL, causing compatibility problems later. Check apt-cache policy and the application documentation before installing.

How should I test a MySQL VPS backup?

Restore the dump or physical backup to a separate test VPS and verify that the application can connect. Check the restore time, missing tables, user privileges, character sets, and the actual age of the backup.