Install and Secure MariaDB

This guide explains how to install and securely configure MariaDB on your VPS.

Prerequisites

• SSH root or sudo access
• Up-to-date Debian/Ubuntu system

Installation

1. Install MariaDB:

   sudo apt update
   sudo apt install mariadb-server

2. Secure the installation:

   sudo mysql_secure_installation

   Follow the security steps:

   • Press Enter for the root password (empty by default)
   • Answer security questions:
     • Change the root password? [Y/n] : Y (recommended)
     • Remove anonymous users? [Y/n] : Y
     • Disallow root login remotely? [Y/n] : Y
     • Remove test database? [Y/n] : Y
     • Reload privilege tables? [Y/n] : Y

User configuration

1. Connect to MariaDB:

   sudo mysql

2. Create a new administrator user (replace 'your_user' and 'your_password'):

   CREATE USER 'your_user'@'localhost' IDENTIFIED BY 'your_password';
   GRANT ALL PRIVILEGES ON *.* TO 'your_user'@'localhost' WITH GRANT OPTION;
   FLUSH PRIVILEGES;
   EXIT;

Installation verification

1. Test the connection with the new user:

   mysql -u your_user -p

2. Check the service status:

   sudo systemctl status mariadb

Securing MariaDB port

1. Check the current listening port:

   sudo netstat -tlnp | grep mysql

2. Configure the firewall (UFW):

   # Block default access to MySQL port (3306)
   sudo ufw deny 3306
   
   # If you need to allow remote access from a specific IP
   sudo ufw allow from AUTHORIZED_IP to any port 3306

3. Modify the MariaDB configuration file:

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

   Find and modify these lines:

   # Limit listening to localhost only
   bind-address = 127.0.0.1
   
   # Or specify a specific IP address
   # bind-address = YOUR_SERVER_IP

4. Restart MariaDB to apply changes:

   sudo systemctl restart mariadb

5. Check authorized connections:

   mysql -u root -p
   SELECT user, host FROM mysql.user;

Graphical database administration

Once MariaDB is installed, two graphical tools let you manage your databases without using the command line:

• phpMyAdmin: a web interface installed directly on the server (with Apache and PHP). Accessible from a browser, handy for day-to-day administration.
• MySQL Workbench: a desktop application installed on your own machine. It connects to the server through an SSH tunnel, without opening port 3306 — consistent with the hardening covered above.

phpMyAdmin (web interface via .tar.gz / .zip archive)

This method installs phpMyAdmin from the official archive (often more recent than the apt package), served by Apache.

1. Install Apache, PHP and the required extensions:

   sudo apt update
   sudo apt install apache2 php libapache2-mod-php php-mysqli php-mbstring php-zip php-gd php-curl php-xml

2. Download and extract the official archive. At the time of writing, the latest stable version is phpMyAdmin 5.2.3.

   Option A — .tar.gz archive:

   cd /tmp
   wget https://files.phpmyadmin.net/phpMyAdmin/5.2.3/phpMyAdmin-5.2.3-all-languages.tar.gz
   tar xzf phpMyAdmin-5.2.3-all-languages.tar.gz
   sudo mv phpMyAdmin-5.2.3-all-languages /usr/share/phpmyadmin

   Option B — .zip archive:

   cd /tmp
   sudo apt install unzip
   wget https://files.phpmyadmin.net/phpMyAdmin/5.2.3/phpMyAdmin-5.2.3-all-languages.zip
   unzip phpMyAdmin-5.2.3-all-languages.zip
   sudo mv phpMyAdmin-5.2.3-all-languages /usr/share/phpmyadmin

   Check the latest version

   See the official download page to get the most recent version and adjust the number in the commands.

3. Create the temporary directory and set the permissions:

   sudo mkdir -p /usr/share/phpmyadmin/tmp
   sudo chown -R www-data:www-data /usr/share/phpmyadmin
   sudo chmod 755 /usr/share/phpmyadmin/tmp

4. Create the configuration file from the provided sample:

   sudo cp /usr/share/phpmyadmin/config.sample.inc.php /usr/share/phpmyadmin/config.inc.php

   Generate a secret string (32 characters) for cookie encryption:

   openssl rand -base64 24

   Edit the configuration file:

   sudo nano /usr/share/phpmyadmin/config.inc.php

   Fill in the secret string and the temporary directory:

   $cfg['blowfish_secret'] = 'PASTE_THE_GENERATED_STRING_HERE';
   $cfg['TempDir'] = '/usr/share/phpmyadmin/tmp';

5. Declare phpMyAdmin in Apache:

   sudo nano /etc/apache2/conf-available/phpmyadmin.conf

   Add the following content:

   Alias /phpmyadmin /usr/share/phpmyadmin
   
   <Directory /usr/share/phpmyadmin>
       Options SymLinksIfOwnerMatch
       DirectoryIndex index.php
       Require all granted
   </Directory>
   
   # Deny web access to sensitive directories
   <Directory /usr/share/phpmyadmin/tmp>
       Require all denied
   </Directory>

6. Enable the configuration and reload Apache:

   sudo a2enconf phpmyadmin
   sudo systemctl reload apache2

7. Open the interface in a browser, then log in with the MariaDB user created earlier:

   http://server_ip_or_domain/phpmyadmin

Secure access to phpMyAdmin

phpMyAdmin is a frequent target of automated attacks. It is strongly recommended to:

• enable HTTPS (see the tip below);
• restrict access by IP or add Apache authentication (.htaccess);
• optionally replace the /phpmyadmin alias with a less predictable path.

Enable HTTPS

In production, protect access with a Let's Encrypt SSL certificate:

sudo apt install -y certbot python3-certbot-apache
sudo certbot --apache -d your-domain.com

A certificate requires a domain name pointing to your server's IP — see our guide to create an A record.

MySQL Workbench (desktop client via SSH tunnel)

MySQL Workbench is installed on your own computer (Windows, macOS or Linux) and connects to the server through an SSH tunnel. Port 3306 stays closed on the server and MariaDB keeps listening only on 127.0.0.1: the connection goes through SSH, as if you were local on the server.

1. Download and install MySQL Workbench from the official site on your machine.

2. In Workbench, create a new connection (Database ▸ Manage Connections ▸ New) using the Standard TCP/IP over SSH method and fill in:

   Field	Value
   SSH Hostname	server_ip_address:22 (or your SSH port)
   SSH Username	your SSH user
   SSH Key File / Password	your SSH key or password
   MySQL Hostname	127.0.0.1
   MySQL Server Port	3306
   Username	the MariaDB user (e.g. your_user)
   Password	that user's password

   Why 127.0.0.1?

   The MySQL Hostname/Port fields are evaluated from the server, at the other end of the SSH tunnel. So you enter 127.0.0.1:3306, i.e. MariaDB listening locally on the server.

3. Click Test Connection to validate, then OK to save the connection.

Alternative — manual SSH tunnel

You can also open the tunnel yourself, then point any MySQL client at 127.0.0.1:3307 locally:

ssh -L 3307:127.0.0.1:3306 user@server_ip_address

As long as the SSH session stays open, local port 3307 is linked to the server's port 3306.

MariaDB compatibility

MySQL Workbench is developed for MySQL. With recent MariaDB versions, an "unsupported version" warning may appear: most common operations (browsing data, running SQL, managing users) still work.

Security best practices

• Use strong passwords
• Limit database access
• Perform regular backups
• Regularly update MariaDB
• Limit connections to trusted IPs
• Regularly monitor connection attempts

Troubleshooting

• Check logs: sudo tail -f /var/log/mysql/error.log
• Verify that the service is active: sudo systemctl status mariadb
• Make sure ports are open: sudo netstat -tulpn | grep mysql