Secure MariaDB and MySQL Setup on a VPS

A database is the most valuable asset of most web applications. Its compromise means a leak of user data, passwords, and business information. Proper MySQL/MariaDB configuration is critical.

Initial Hardening: mysql_secure_installation

mysql_secure_installation
# Answer the prompts:
# Validate password: Y (enable password complexity check)
# Remove anonymous users: Y
# Disallow root login remotely: Y
# Remove test database: Y
# Reload privilege tables: Y

Creating Users with Minimal Privileges

mysql -u root -p

-- Create a user for a single DB only
CREATE USER 'webapp'@'localhost' IDENTIFIED BY 'StrongPass123!';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'webapp'@'localhost';

-- Read-only (for analytics)
CREATE USER 'analytics'@'localhost' IDENTIFIED BY 'ReadOnlyPass!';
GRANT SELECT ON myapp.* TO 'analytics'@'localhost';

FLUSH PRIVILEGES;
SHOW GRANTS FOR 'webapp'@'localhost';

Configuring my.cnf for Security

# /etc/mysql/mariadb.conf.d/99-security.cnf

[mysqld]
# Bind only to localhost (deny external connections)
bind-address = 127.0.0.1

# Disable LOCAL INFILE (protection against LFI attacks)
local-infile = 0

# Hide the MariaDB version
skip-show-database

# Maximum number of failed connections
max_connect_errors = 100

# Log slow queries
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

Connection Encryption (SSL/TLS)

# Check SSL support
mysql -u root -p -e "SHOW VARIABLES LIKE '%ssl%';"

# Require SSL for the user
ALTER USER 'webapp'@'%' REQUIRE SSL;

# Enable SSL in my.cnf
ssl-ca = /etc/mysql/ssl/ca-cert.pem
ssl-cert = /etc/mysql/ssl/server-cert.pem
ssl-key = /etc/mysql/ssl/server-key.pem

Automatic Encrypted Backups

cat > /etc/cron.daily/mysql-backup << 'SCRIPT'
#!/bin/bash
DATE=$(date +%Y%m%d)
mysqldump -u root -p'RootPass' --all-databases --single-transaction   | gzip | gpg --batch --passphrase "BackupKey123" -c   > /backup/mysql-$DATE.sql.gz.gpg
find /backup/ -name "mysql-*.sql.gz.gpg" -mtime +30 -delete
SCRIPT
chmod +x /etc/cron.daily/mysql-backup

Common security configuration mistakes

Most access errors after hardening come down to a changed authentication method or a user's connection scope.

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

After mysql_secure_installation, root switches to unix_socket authentication on many systems — login only works locally via sudo mysql, not via mysql -u root -p with a password. Check the authentication method: SELECT user, plugin FROM mysql.user WHERE user='root';.

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

The service isn't running, or the socket path in the application's config doesn't match the real one. Check systemctl status mariadb and the socket path in the [client] section of my.cnf.

ERROR 1130 (HY000): Host '10.0.0.5' is not allowed to connect to this MySQL server

The user was only created as 'webapp'@'localhost', but the connection comes from another server or container. Either create the user with the right host or subnet ('webapp'@'10.0.0.%'), or connect through an SSH tunnel without opening the port externally — that's safer than allowing a remote IP directly.

Authentication plugins compared

MariaDB and MySQL default to different plugins — on MariaDB 10.4+ it's usually unix_socket for root, on MySQL 8 it's caching_sha2_password for new users. When migrating a dump between the two, user passwords sometimes need to be reassigned.

When this hardening is overkill or doesn't fit

If the application and the database live on separate servers (for example, one shared DB server for several web servers), the strict bind-address = 127.0.0.1 from the example breaks that architecture — in that case, set up a private network between the servers or an SSH tunnel instead of opening port 3306 to the outside with password-only access. Legacy applications on old PHP or Python versions may not support caching_sha2_password — if you see an authentication compatibility error after an upgrade, temporarily create the user on mysql_native_password rather than rolling back the whole server's security.

How to verify the result

Check more than "the connection worked" — confirm the actual security settings:

# Confirm the user only has the intended privileges
SHOW GRANTS FOR 'webapp'@'localhost';

# Check that the port isn't listening on 0.0.0.0
sudo ss -tlnp | grep 3306

# Check that SSL is actually active for the connection
mysql -u root -p -e "SHOW STATUS LIKE 'Ssl_cipher';"

The ss -tlnp | grep 3306 command should show 127.0.0.1:3306, not 0.0.0.0:3306, if external access isn't needed. An empty Ssl_cipher value means the connection went through unencrypted, even if certificates are configured — the client also has to explicitly request SSL.

Frequently asked questions

Does the default MySQL port need to change for security reasons?

Changing the port by itself doesn't replace real protection — it only makes automated scanning slightly harder, without stopping a targeted attack. It matters far more to restrict bind-address to the local interface and keep access closed off by a firewall from the outside, while the port itself can stay at its default with no meaningful loss of security.

Does the root user need to be deleted entirely, rather than just blocked from remote access?

No, deleting the system root user isn't necessary and usually isn't possible without side effects — many maintenance operations and utilities expect it to exist. The right approach is to block its remote connections and switch its authentication to the local socket, leaving full access available only when logging in directly on the server.

Is it safe to use one database user for several different applications?

No, sharing one user across several applications breaks the principle of least privilege: compromising a single application immediately opens access to every other application's database too. It's better to create a separate user for each application with rights limited to only the database it needs, the same way the example splits write access from read-only access.

What happens if a new database user isn't created after restoring a dump on another server?

A database dump carries over only table structure and content, not system users or their access rights — those need to be created on the new server separately, either by hand or through a separate dump of the privilege tables. Skipping that step means the application on the new server simply can't connect with the same credentials it used before.