LibraHostDocumentation

Documentation

Complete guides for all our hosting services

Navigation

🛡️ Install MySQL / MariaDB on a VPS#

Complete and detailed guide to install, secure and optimize MariaDB on a VPS server. This comprehensive tutorial guides you step by step for a professional configuration suitable for web hosting, business applications, e-commerce or game servers.

🎯 Guide objectives#

  • ✅ Clean installation of MariaDB on Ubuntu/Debian
  • 🔒 Advanced security with strong authentication and service isolation
  • ⚡ Performance optimization for production use
  • 🔧 Fine configuration adapted to your workload
  • 🛠️ Troubleshooting the most common issues

1️⃣ System preparation#

📦 Complete system update#

# Update package list
sudo apt update

# Upgrade all installed packages
sudo apt upgrade -y

🔧 Essential tools installation#

# Basic tools for administration
sudo apt install -y curl wget gnupg2 software-properties-common apt-transport-https ca-certificates

🕒 Timezone configuration#

# View current time
date

# Configure timezone (important for logs)
sudo timedatectl set-timezone Europe/Paris

# Verify configuration
timedatectl status

2️⃣ Detailed MySQL or MariaDB installation#

Method 1: From Ubuntu/Debian repositories#

# MariaDB installation
sudo apt install -y mariadb-server mariadb-client

# Start and enable
sudo systemctl start mariadb
sudo systemctl enable mariadb

# Verification
sudo systemctl status mariadb

Method 2: From official MariaDB repository (recommended)#

# Add MariaDB GPG key
sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'

# Add repository
curl -LsSO https://r.mariadb.com/downloads/mariadb_repo_setup | bash

# Installation
sudo apt update
sudo apt install -y mariadb-server

✅ Installation verification#

# Installed version
mariadb --version

# Running processes
ps aux | grep mysql

# Listening ports
sudo netstat -tlnp | grep :3306

# Basic connection test
sudo mysql -u root

3️⃣ Advanced installation security#

🔒 Security assistant#

sudo mysql_secure_installation

Detailed option configuration:

  1. Validate Password Plugin: Y

    • Security level: 2 (STRONG)
    • Minimum length: 12 characters
    • Mix of uppercase/lowercase/numbers/symbols

  2. Root password: Create a strong password

    • Example: MyS3cur3P@ssw0rd!2024
    • Avoid dictionary words
    • Store it in a password manager

  3. Remove anonymous users: Y

  4. Disable remote root login: Y

  5. Remove test database: Y

  6. Reload privileges: Y

4️⃣ User and privilege management#

👤 Creating databases and local users#

Connect as root#

sudo mysql -u root -p

Database creation#

-- Create databases
CREATE DATABASE production_app CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE staging_app CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Create users for local access only#

-- Application user (limited privileges)
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'AppStr0ng!Pass2024';
GRANT SELECT, INSERT, UPDATE, DELETE ON production_app.* TO 'app_user'@'localhost';

-- Backup user (read-only)
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'BackupSecur3!2024';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON production_app.* TO 'backup_user'@'localhost';

-- Admin user (extended privileges)
CREATE USER 'admin_user'@'localhost' IDENTIFIED BY 'AdminP0w3r!2024';
GRANT ALL PRIVILEGES ON production_app.* TO 'admin_user'@'localhost';
GRANT ALL PRIVILEGES ON staging_app.* TO 'admin_user'@'localhost';

-- Monitoring user (system read privileges)
CREATE USER 'monitor_user'@'localhost' IDENTIFIED BY 'M0nit0r!Secur3';
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitor_user'@'localhost';

-- Apply changes
FLUSH PRIVILEGES;

-- Verify created users
SELECT User, Host FROM mysql.user WHERE Host = 'localhost';
EXIT;

5️⃣ Remote access configuration (optional)#

⚠️ Warning: Remote access presents security risks. Only enable if necessary and always with IP restrictions.

🔧 Step 1: MariaDB server configuration#

Modify configuration to listen on all interfaces#

# Edit configuration file
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Find the bind-address line and modify it:

# BEFORE (local access only)
bind-address = 127.0.0.1

# AFTER (access from all interfaces)
bind-address = 0.0.0.0

Restart MariaDB to apply changes#

sudo systemctl restart mariadb

# Verify server listens on all interfaces
sudo netstat -tlnp | grep :3306
# You should see: 0.0.0.0:3306 instead of 127.0.0.1:3306

🔐 Step 2: Create users for remote access#

# Reconnect to MariaDB
sudo mysql -u root -p

-- User with specific IP (recommended)
CREATE USER 'remote_admin'@'192.168.1.100' IDENTIFIED BY 'RemoteStr0ng!2024';
GRANT SELECT, INSERT, UPDATE, DELETE ON production_app.* TO 'remote_admin'@'192.168.1.100';

-- Apply changes
FLUSH PRIVILEGES;

-- Verify remote users
SELECT User, Host FROM mysql.user WHERE Host != 'localhost';
EXIT;

🛡️ Step 3: Firewall configuration#

# Allow access from specific IP
sudo ufw allow from 192.168.1.100 to any port 3306

# Check firewall rules
sudo ufw status numbered

🧪 Step 4: Test remote connection#

# From another server, test connection
mysql -h 163.5.xxx.xxx -u remote_user -p production_app

# Or with telnet to test connectivity
telnet 163.5.xxx.xxx 3306

6️⃣ Advanced performance optimization#

📊 Tuning tools#

MySQLTuner installation#

# Installation from repositories
sudo apt install -y mysqltuner

# Or download latest version
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
chmod +x mysqltuner.pl

# Run analysis
sudo mysqltuner
# or
sudo perl mysqltuner.pl

7️⃣ Backup and restore strategies#

💾 Backups with mysqldump#

Automated backup script#

# Create backup directory
sudo mkdir -p /backup/mysql
sudo chown mysql:mysql /backup/mysql

# Create backup script
sudo nano /usr/local/bin/mysql-backup.sh

#!/bin/bash
# Automated MySQL backup script

# Configuration
BACKUP_DIR="/backup/mysql"
MYSQL_USER="backup_user" # Replace with your user
MYSQL_PASSWORD="BackupSecur3!2024" # Replace with your password
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7

# Create directory with date
mkdir -p "$BACKUP_DIR/$DATE"

# List of databases to backup
DATABASES=("production_app" "staging_app")

for db in "${DATABASES[@]}"; do
    echo "Backing up $db..."

    # Complete backup with structure and data
    mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD \
        --single-transaction \
        --routines \
        --triggers \
        --events \
        --hex-blob \
        --lock-tables=false \
        $db > "$BACKUP_DIR/$DATE/${db}_full.sql"

    # Compress backup
    gzip "$BACKUP_DIR/$DATE/${db}_full.sql"

    echo "Backup of $db completed: ${db}_full.sql.gz"
done

# Backup users and privileges
echo "Backing up users and privileges..."
mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SELECT User, Host FROM mysql.user;" > "$BACKUP_DIR/$DATE/users_list.txt"

# Remove old backups
find $BACKUP_DIR -type d -mtime +$RETENTION_DAYS -exec rm -rf {} +

echo "Backup completed on $(date)"

# Make script executable
sudo chmod +x /usr/local/bin/mysql-backup.sh

# Test script
sudo /usr/local/bin/mysql-backup.sh

Schedule backups with cron#

# Edit crontab
sudo crontab -e

# Add these lines for automatic backups
# Daily backup at 2 AM
0 2 * * * /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

# Weekly complete backup on Sunday at 1 AM
0 1 * * 0 /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup-weekly.log 2>&1

🔄 Backup restoration#

Complete database restoration#

# Decompress if necessary
gunzip /backup/mysql/20241201_020000/production_app_full.sql.gz

# Restoration
mysql -u admin_user -p'AdminP0w3r!2024' production_app < /backup/mysql/20241201_020000/production_app_full.sql

Selective table restoration#

# Extract only one table from backup
sed -n '/^-- Table structure for table `my_table`/,/^-- Table structure for table `/p' backup.sql > my_table.sql

# Restore this specific table
mysql -u admin_user -p production_app < my_table.sql

🔍 Monitoring with system tools#

Fail2Ban configuration for MySQL#

# Create filter for MySQL connection attempts
sudo nano /etc/fail2ban/filter.d/mysql-auth.conf

[Definition]
failregex = ^%(__prefix_line)s.*\[Warning\] Access denied for user '.*'@'<HOST>'.*$
ignoreregex =

# Configuration in jail.local
sudo nano /etc/fail2ban/jail.local

[mysql-auth]
enabled = true
filter = mysql-auth
logpath = /var/log/mysql/error.log
maxretry = 3
bantime = 3600
findtime = 600

# Restart Fail2Ban
sudo systemctl restart fail2ban
sudo fail2ban-client status mysql-auth

8️⃣ Common troubleshooting#

🚨 Authentication errors#

"Access denied for user 'root'@'localhost'"#

# Solution 1: Reset root password
sudo systemctl stop mysql
sudo mysqld_safe --skip-grant-tables &
mysql -u root

# In MySQL:
USE mysql;
UPDATE user SET authentication_string=PASSWORD('NewPassword') WHERE User='root';
FLUSH PRIVILEGES;
EXIT;

# Restart MySQL normally
sudo systemctl start mysql

🌐 Connectivity issues#

Network configuration verification#

# Check listening ports
sudo netstat -tlnp | grep :3306

# Test local connectivity
telnet localhost 3306

# Check firewall rules
sudo ufw status
sudo iptables -L

Connection diagnostics#

-- View active connections
SHOW PROCESSLIST;

-- View connection variables
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE '%timeout%';

-- Connection status
SHOW STATUS LIKE 'Connections';
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Aborted_connects';

🎉 Conclusion#

Your MySQL/MariaDB server is now professionally configured with:

  1. ✅ Enhanced security - Strong authentication, dedicated users, encryption
  2. ✅ Optimized performance - Fine configuration according to your usage
  3. ✅ Reliable backups - Automated backup/restore strategy
  4. ✅ Error resolution - Complete guide to common issues

This configuration guarantees you a stable, secure and performant database for your production applications. Don't forget to keep your system updated and regularly monitor performance!

Contact support