MySQL and MariaDB on a VPS: Setup Basics and Reliable Backups

MySQL and its community fork MariaDB are the databases behind WordPress, Joomla, Magento and countless custom applications. On a VPS you install, secure and back up the database yourself, and the last of those jobs is the one people postpone until the day they need it. This guide covers a safe first setup and a backup routine you can actually trust. The commands use MariaDB on Debian or Ubuntu; MySQL is nearly identical, and where names differ it is noted.

Install and run the first security step

sudo apt update
sudo apt install mariadb-server
sudo mariadb-secure-installation

On MySQL the install package is mysql-server and the script is mysql_secure_installation; older MariaDB releases also use that older script name. The script removes anonymous users and the test database and lets you disallow remote root login. Answer the questions deliberately rather than pressing Enter through them.

Create a database and a dedicated user

Applications should never connect as the database root user. Create one database and one user per application, with permissions limited to that database. On Debian and Ubuntu, sudo mariadb opens a root shell through the local socket without a password:

CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'replace-with-a-long-random-password';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';

Generate the password with a password manager or openssl rand -base64 24 and store it in your application's environment, not in the code. Using utf8mb4 matters because the older utf8 name in MySQL covers only a subset of Unicode and cannot store emoji.

Keep it private

Most applications run on the same server as their database, so the database has no reason to be reachable from the internet. Check the bind address:

sudo ss -tlnp | grep 3306

You want to see 127.0.0.1:3306. The setting is bind-address in the server configuration under /etc/mysql/. If you must allow a remote connection, for example from a separate application server, restrict it with a firewall rule to that one address, create the user as 'appuser'@'203.0.113.20' instead of localhost, and consider requiring TLS. Never expose port 3306 to the whole internet.

Back up and restore

Back up with mysqldump

A logical backup exports the database as SQL statements. For InnoDB tables, the default engine, use a consistent snapshot without locking the whole database:

sudo mariadb-dump --single-transaction --routines --triggers appdb | gzip > /var/backups/appdb-$(date +%F).sql.gz

On MySQL the command is mysqldump. Running it through sudo uses the root socket login, so no password appears on the command line (passwords typed after -p are visible to other users in the process list). For unattended jobs run as a normal user, put credentials in a ~/.my.cnf file readable only by that user (chmod 600). Then schedule the command with cron, as covered in our cron article, and rotate old files so the disk does not fill. This command deletes compressed dumps older than 14 days; check the pattern and folder before you schedule it:

find /var/backups -name 'appdb-*.sql.gz' -mtime +14 -delete

How many days you keep depends on how quickly you would notice a problem. Corruption that goes unnoticed for three weeks will be present in two weeks of backups too, so keep at least one older monthly copy.

Restore, and prove it works

A backup you have never restored is only a hope. To restore into a new, empty database:

sudo mariadb -e "CREATE DATABASE restore_test"
gunzip < /var/backups/appdb-2026-01-15.sql.gz | sudo mariadb restore_test

Use your own file name. Then check row counts in a few key tables, and drop the test database. Do this once when you set up the routine and again every few months, because it is the only real proof.

Off-server copies, tuning and health

Off-server copies and bigger databases

Keep backups somewhere other than the server itself; if the server is lost, so are backups stored on its disk. Copy them to another machine or storage with rsync or a similar tool. For very large databases, dumps become slow, and physical backup tools such as mariabackup (or Percona XtraBackup for MySQL) are the better route. Binary logging lets you replay changes to a point in time between backups, which matters when losing a day of orders is not acceptable.

Basic tuning and health

The setting with the greatest effect on InnoDB is innodb_buffer_pool_size, the memory used to cache data and indexes. A commonly cited starting point on a dedicated database server is a large share of RAM, but on a VPS that also runs a web server and PHP, set it conservatively and watch memory use. Enable the slow query log to find expensive queries, and add indexes where the log points. To turn the slow log on, add these lines under the [mysqld] section of your configuration and restart the service:

slow_query_log = 1
long_query_time = 1

Queries slower than one second are then recorded in the server's slow query log file. Keep the server updated; minor version updates arrive through your package manager.

Finally, keep an eye on disk space, since a full disk can stop the database writing and, in the worst case, damage tables. Our security page covers how backups fit into a wider protection plan.

Databases at Zebra Hosting

On our Plesk-based plans, databases are created and backed up from the control panel, and the team's free migration moves your databases with your site. Learn more about migrating to Zebra Hosting.