How to install MariaDB

Hey there! So you need a database server on your Linux VPS? MariaDB is a great choice - it's fast, reliable, and fully compatible with MySQL. Let's get it installed and set up properly.

Step 1: Update Your System

Always start with updated packages:

# For Ubuntu/Debian
sudo apt update && sudo apt upgrade -y

# For CentOS/RHEL
sudo yum update -y

Step 2: Install MariaDB

Ubuntu/Debian:

sudo apt install mariadb-server mariadb-client -y

CentOS/RHEL 8+:

sudo dnf install mariadb-server mariadb -y

CentOS/RHEL 7:

sudo yum install mariadb-server mariadb -y

Step 3: Start and Enable MariaDB

# Start the service
sudo systemctl start mariadb

# Enable auto-start on boot
sudo systemctl enable mariadb

# Check status
sudo systemctl status mariadb

You should see "active (running)" in green.

Step 4: Secure Your Installation

This is important! Run the security script:

sudo mysql_secure_installation

You'll be asked:

1. Enter current password for root: Press Enter (no password yet)

2. Set root password? Type Y and choose a strong password

3. Remove anonymous users? Type Y

4. Disallow root login remotely? Type Y (for security)

5. Remove test database? Type Y

6. Reload privilege tables? Type Y

Step 5: Basic Configuration

Edit MariaDB Config:

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf  # Ubuntu/Debian
# OR
sudo nano /etc/my.cnf.d/server.cnf                 # CentOS/RHEL

Add these under [mysqld] section:

# Basic performance tuning for VPS
innodb_buffer_pool_size = 256M
query_cache_size = 64M
query_cache_limit = 2M
max_connections = 100
wait_timeout = 600

# Character set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

Restart to apply changes:

sudo systemctl restart mariadb

Step 6: Create a Database and User

For security, create separate users for each application:

# Login to MariaDB
sudo mysql -u root -p

# Create database
CREATE DATABASE myapp_db;

# Create user
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'Password';

# Grant privileges
GRANT ALL PRIVILEGES ON myapp_db.* TO 'myapp_user'@'localhost';

# Apply changes
FLUSH PRIVILEGES;

# Exit
EXIT;

Step 7: Test Your Setup

# Test connection with new user
mysql -u myapp_user -p -D myapp_db

# Run a test query
SHOW DATABASES;

# Exit
EXIT;

Step 8: Backup Your Database

Always have backups! Create a simple backup script:

nano ~/backup-mysql.sh

Add:

#!/bin/bash
BACKUP_DIR="/home/$(whoami)/mysql-backups"
mkdir -p $BACKUP_DIR
mysqldump -u root -p --all-databases > $BACKUP_DIR/full-backup-$(date +%Y%m%d).sql

Make it executable:

chmod +x ~/backup-mysql.sh

Common Issues & Solutions

"Can't connect to MariaDB"

# Check if service is running
sudo systemctl status mariadb

# Check error logs
sudo tail -f /var/log/mysql/error.log

"Access denied for user"

Reset password if forgotten:

sudo systemctl stop mariadb
sudo mysqld_safe --skip-grant-tables &
mysql -u root

USE mysql;
UPDATE user SET password=PASSWORD('new_password') WHERE User='root';
FLUSH PRIVILEGES;
EXIT;

sudo systemctl start mariadb

Useful Commands

Check MariaDB Version:

mysql --version

Show Running Processes:

SHOW PROCESSLIST;

Check Database Size:

SELECT table_schema "Database", 
SUM(data_length + index_length)/1024/1024 "Size in MB" 
FROM information_schema.TABLES 
GROUP BY table_schema;

That's it! Your MariaDB server is ready. Remember to regularly update and backup! 🚀