MySQL and MariaDB are the most widely used open source relational databases, and the default choice for applications such as WordPress, Laravel or Nextcloud. MariaDB started as a fork of MySQL and remains compatible for most applications: same client protocol, same SQL for everyday use and the same configuration layout on Ubuntu. In this tutorial you will install one of them on Ubuntu 24.04, secure the installation, create a database and an application user, apply basic tuning, optionally allow remote connections and set up daily backups.

Prerequisites

To follow this tutorial you need:

  • A server running Ubuntu 24.04 LTS, for example a CubePath VPS, with a non-root user that has sudo privileges.
  • At least 1 GB of RAM (2 GB or more for production databases) and enough disk space for your data and backups.
  • UFW enabled with SSH allowed (sudo ufw allow OpenSSH).

Step 1 - Choosing and installing the server

Install only one of the two; they use the same port and data directory and conflict with each other.

MySQLMariaDB
Version in Ubuntu 24.048.010.11 LTS
Packagemysql-servermariadb-server
Service namemysqlmariadb (alias mysql)
Main config file/etc/mysql/mysql.conf.d/mysqld.cnf/etc/mysql/mariadb.conf.d/50-server.cnf

Choose MySQL if your application or vendor requires it (some features such as the caching_sha2_password plugin or MySQL-specific JSON functions differ), and MariaDB if you prefer a fully community-developed server. Both are supported with security updates for the life of Ubuntu 24.04.

To install MySQL:

sudo apt update
sudo apt install mysql-server

To install MariaDB instead:

sudo apt update
sudo apt install mariadb-server

The service starts automatically and is enabled at boot. Check it (replace mysql with mariadb if you installed MariaDB):

sudo systemctl status mysql --no-pager

● mysql.service - MySQL Community Server
     Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; preset: enabled)
     Active: active (running) since Fri 2026-09-25 10:05:12 UTC; 20s ago

Connect as the database root user:

sudo mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.
Server version: 8.0.43-0ubuntu0.24.04.1 (Ubuntu)

mysql>

On Ubuntu the database root account authenticates through the Unix socket (auth_socket in MySQL, unix_socket in MariaDB): any user who can run sudo can log in as database root without a password, and nobody can log in as root over the network. Keep it that way. Type exit to leave the client.

Step 2 - Securing the installation

Both servers include a script that removes insecure defaults: anonymous users, the test database and remote root logins. Run it:

sudo mysql_secure_installation

On MariaDB the same script is also available as sudo mariadb-secure-installation.

The MySQL version first asks whether to enable the VALIDATE PASSWORD component, which rejects weak passwords for new users. Answer y and choose level 1 (MEDIUM: at least 8 characters with numbers, mixed case and special characters) unless you have a reason not to. Because root uses socket authentication, the script skips setting a root password. Answer y to the remaining questions:

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
All done!

The MariaDB version first asks for the current root password (press ENTER), then whether to switch to unix_socket authentication (answer n, it is already in use) and whether to change the root password (answer n). Answer y to the rest.

Step 3 - Creating a database and an application user

Applications should never connect as root. Create a dedicated database and a user that can only access it. Open the client:

sudo mysql

Run the following statements, replacing appdb, appuser and your_strong_password with your own values. Generate a random password with openssl rand -base64 24 if you don't have one:

CREATE DATABASE appdb CHARACTER SET utf8mb4;
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'your_strong_password';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';
EXIT;

utf8mb4 stores any Unicode character, including emoji; the older utf8 in MySQL only covers three bytes per character. 'appuser'@'localhost' means the user can only connect from the server itself, which is what you want when the application runs on the same machine.

Verify that the new user can log in and see the database:

mysql -u appuser -p -e "SHOW DATABASES;"

Enter password:
+--------------------+
| Database           |
+--------------------+
| appdb              |
| information_schema |
| performance_schema |
+--------------------+

Step 4 - Tuning the server

The defaults are designed for small machines. The single most important setting is innodb_buffer_pool_size, the memory InnoDB uses to cache data and indexes. On a server dedicated to the database, set it to 50 to 70% of the RAM; if the web server and application run on the same machine, 25 to 40% is safer.

Keep your changes in a separate file so package updates never overwrite them. For MySQL:

sudo nano /etc/mysql/mysql.conf.d/99-custom.cnf

For MariaDB, create /etc/mysql/mariadb.conf.d/99-custom.cnf instead. Files in these directories are read in alphabetical order, so the 99- prefix makes your values win. The following example is for a server with 4 GB of RAM that also runs the application:

[mysqld]
innodb_buffer_pool_size = 1G
max_connections         = 200

# Log queries slower than one second
slow_query_log          = 1
slow_query_log_file     = /var/log/mysql/mysql-slow.log
long_query_time         = 1

max_connections defaults to 151; raise it only if your application uses many concurrent connections, because each one reserves memory. The slow query log is the first place to look when the database becomes a bottleneck.

Restart the server and check the values:

sudo systemctl restart mysql
sudo mysql -e "SELECT @@innodb_buffer_pool_size/1024/1024 AS buffer_pool_mb, @@max_connections, @@slow_query_log;"

+----------------+-------------------+------------------+
| buffer_pool_mb | @@max_connections | @@slow_query_log |
+----------------+-------------------+------------------+
|  1024.00000000 |               200 |                1 |
+----------------+-------------------+------------------+

If the service fails to start, the error is in sudo journalctl -u mysql -n 50 (or -u mariadb), usually a typo in the file.

Step 5 - Allowing remote connections (optional)

By default both servers listen only on 127.0.0.1. Skip this step if your application runs on the same server. If it runs on another machine, ideally connect them over a private network and allow access only from the application server's address.

Open your custom configuration file again and add the address the server should listen on under [mysqld]. Use the server's private IP (your_private_ip), or 0.0.0.0 for all interfaces if you have no private network:

bind-address = your_private_ip

Restart the server and confirm it listens on the new address:

sudo systemctl restart mysql
sudo ss -tlnp | grep 3306

LISTEN 0  151  10.0.0.2:3306  0.0.0.0:*  users:(("mysqld",pid=4211,fd=23))

Open port 3306 only to the application server. Replace 10.0.0.3 with its address:

sudo ufw allow from 10.0.0.3 to any port 3306 proto tcp

Create a user that can only connect from that address and grant it access to the database:

CREATE USER 'appuser'@'10.0.0.3' IDENTIFIED BY 'your_strong_password';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'10.0.0.3';

From the application server, test the connection (install the client there with sudo apt install mysql-client or mariadb-client):

mysql -h 10.0.0.2 -u appuser -p -e "SELECT 1;"

Step 6 - Scheduling daily backups

mysqldump (also available as mariadb-dump on MariaDB) produces a SQL file that can be restored on any version. With --single-transaction, InnoDB tables are dumped consistently without locking them. Create a backup script:

sudo nano /usr/local/sbin/mysql-backup

#!/usr/bin/env bash
set -euo pipefail

backup_dir="/var/backups/mysql"
retention_days=7
timestamp="$(date +%F_%H%M)"

umask 077
mkdir -p "$backup_dir"

databases="$(mysql -N -e "SHOW DATABASES" | grep -Ev '^(information_schema|performance_schema|mysql|sys)$' || true)"

for db in $databases; do
    mysqldump --single-transaction --routines --triggers --events "$db" \
        | gzip > "$backup_dir/${db}_${timestamp}.sql.gz"
done

find "$backup_dir" -name '*.sql.gz' -mtime +"$retention_days" -delete

The script runs as root, so it authenticates through the Unix socket and needs no password stored on disk. It dumps every user database to its own compressed file readable only by root (umask 077) and deletes backups older than seven days. Make it executable and run it once:

sudo chmod 750 /usr/local/sbin/mysql-backup
sudo /usr/local/sbin/mysql-backup
sudo ls -lh /var/backups/mysql/

-rw------- 1 root root 1.2K Sep 25 10:40 appdb_2026-09-25_1040.sql.gz

Schedule it every night at 03:15 with a cron file:

echo '15 3 * * * root /usr/local/sbin/mysql-backup' | sudo tee /etc/cron.d/mysql-backup

To restore a database, create it if it does not exist and pipe the dump into the client:

sudo mysql -e "CREATE DATABASE IF NOT EXISTS appdb CHARACTER SET utf8mb4;"
gunzip < /var/backups/mysql/appdb_2026-09-25_1040.sql.gz | sudo mysql appdb

Troubleshooting

ERROR 1698 (28000): Access denied for user 'root'@'localhost'. You ran mysql -u root -p as a normal user. Root uses socket authentication on Ubuntu; connect with sudo mysql instead, and use a dedicated user with a password for applications and tools.

Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'. The server is not running. Check sudo systemctl status mysql and the last log lines with sudo journalctl -u mysql -n 50. A common cause is a configuration typo or a full disk (df -h /var/lib/mysql).

Remote connection times out. The firewall blocks the port or the server still listens on 127.0.0.1. Check sudo ss -tlnp | grep 3306 and sudo ufw status.

Remote connection returns Host '10.0.0.3' is not allowed to connect. The user exists only as 'appuser'@'localhost'. Create it for the remote host as shown in Step 5.

Your password does not satisfy the current policy requirements. The VALIDATE PASSWORD component in MySQL rejected the password. Use a longer password with digits, mixed case and a special character.

Conclusion

You installed MySQL or MariaDB on Ubuntu 24.04, removed insecure defaults, created a database with a dedicated user, sized the InnoDB buffer pool, learned how to expose the server safely to another machine and scheduled daily backups. As next steps, copy your backups off the server, review the slow query log after a few days of real traffic, and consider replication or a managed database once a single server is no longer enough.