Prerequisites
Before starting, ensure you have:
Server Requirements
- • RamNode VPS (1GB+ RAM minimum)
- • Ubuntu 20.04+ or Debian 11+
- • 1+ CPU cores
- • 10GB+ disk space available
- • SSH access to your VPS
Access Requirements
- • Root or sudo access
- • Basic Linux command knowledge
- • Understanding of database concepts
- • SSH client installed
2
Initial Server Setup
Connect to your RamNode VPS and prepare the environment:
Connect via SSH
ssh root@your-server-ipUpdate System Packages
sudo apt update
sudo apt upgrade -yInstall Required Dependencies
sudo apt install -y software-properties-common dirmngr apt-transport-https💡 System Tip: Always ensure your system is up-to-date before installing database software to avoid compatibility issues and security vulnerabilities.
3
Install MariaDB Server
Install MariaDB server and client packages:
Add MariaDB Repository Key
sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] https://mirrors.xtom.nl/mariadb/repo/10.11/ubuntu jammy main'Update Package List
sudo apt updateInstall MariaDB
sudo apt install -y mariadb-server mariadb-clientStart and Enable MariaDB Service
sudo systemctl start mariadb
sudo systemctl enable mariadbVerify Installation
sudo systemctl status mariadb
mysql --versionWhy MariaDB?
MariaDB is a high-performance, open-source relational database that serves as a drop-in replacement for MySQL. It offers enhanced performance, better security features, more storage engines, and active development by the original MySQL creators.
4
Secure MariaDB Installation
Run the security script to secure your MariaDB installation:
Run Security Installation
sudo mysql_secure_installationFollow the prompts and make these security choices:
Security Configuration Prompts
- • Enter current password for root: Press Enter (no password set yet)
- • Set root password: Y (Yes) - Choose a strong password
- • Remove anonymous users: Y (Yes)
- • Disallow root login remotely: Y (Yes, for security)
- • Remove test database: Y (Yes)
- • Reload privilege tables: Y (Yes)
Test Root Login
sudo mysql -u root -pCheck MariaDB Version
-- Check MariaDB version and status
SELECT VERSION();
SHOW STATUS LIKE 'Uptime';
-- Exit MySQL prompt
EXIT;🔐 Security: Store your root password securely! Consider using a password manager for database credentials.
5
Create Database and User
Create a new database and user for your applications:
Login to MariaDB
sudo mysql -u root -pCreate Database and User
-- Create a new database
CREATE DATABASE myapp_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Create a new user with password
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'secure_user_password';
-- Grant privileges to the user
GRANT ALL PRIVILEGES ON myapp_db.* TO 'myapp_user'@'localhost';
-- Flush privileges to ensure they take effect
FLUSH PRIVILEGES;
-- Show databases
SHOW DATABASES;
-- Show users
SELECT User, Host FROM mysql.user;
-- Exit
EXIT;Test New User Connection
mysql -u myapp_user -p myapp_dbTest Database Operations
-- Create a test table
CREATE TABLE test_table (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Insert test data
INSERT INTO test_table (name) VALUES ('Test Entry');
-- Query the data
SELECT * FROM test_table;
-- Drop test table
DROP TABLE test_table;
-- Exit
EXIT;✅ Database and user created successfully! Your applications can now connect to MariaDB.
6
Configure Remote Access
Enable remote connections to your MariaDB server:
Edit MariaDB Configuration
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnfFind and modify the bind-address setting:
Enable Remote Connections
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 0.0.0.0Create Remote User
sudo mysql -u root -pGrant Remote Access
-- Create user for remote access (replace with your IP)
CREATE USER 'myapp_user'@'%' IDENTIFIED BY 'secure_password';
-- Or create user for specific IP range
CREATE USER 'myapp_user'@'192.168.1.%' IDENTIFIED BY 'secure_password';
-- Grant privileges
GRANT ALL PRIVILEGES ON myapp_db.* TO 'myapp_user'@'%';
-- Flush privileges
FLUSH PRIVILEGES;
-- Exit
EXIT;Restart MariaDB
sudo systemctl restart mariadbConfigure Firewall
sudo ufw allow 3306/tcp
sudo ufw reloadTest Remote Connection
mysql -h your-server-ip -u myapp_user -p myapp_db🔒 Security Warning: Only allow remote access from trusted networks. Consider using SSH tunneling or VPN for additional security.
7
Performance Optimization
Optimize MariaDB for your RamNode VPS:
Create Custom Configuration
sudo nano /etc/mysql/mariadb.conf.d/99-custom.cnfMemory Configuration
Optimal Memory Settings
[mysqld]
# Basic Settings
max_connections = 100
thread_cache_size = 8
table_open_cache = 2000
# MyISAM Settings
key_buffer_size = 32M
# InnoDB Settings (adjust based on your RAM)
# For 2GB RAM VPS
innodb_buffer_pool_size = 512M
innodb_log_file_size = 128M
# For 4GB RAM VPS
# innodb_buffer_pool_size = 1G
# innodb_log_file_size = 256M
# For 8GB RAM VPS
# innodb_buffer_pool_size = 2G
# innodb_log_file_size = 512M
# Additional InnoDB Settings
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = 1
# Query Cache (if using older versions)
query_cache_type = 1
query_cache_size = 32M
# Slow Query Log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
# Binary Logging
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 7
max_binlog_size = 100MApply Configuration Changes
sudo systemctl restart mariadb
sudo systemctl status mariadbMonitor Performance
# Check status variables
sudo mysql -u root -p -e "SHOW STATUS LIKE 'Innodb_buffer_pool%';"
# Check process list
sudo mysql -u root -p -e "SHOW PROCESSLIST;"
# Check engine status
sudo mysql -u root -p -e "SHOW ENGINE INNODB STATUS\G"⚡ Performance Tip: Monitor your database performance with tools like mysqltuner to get personalized optimization recommendations.
8
Backup Strategy
Set up automated backups for your MariaDB databases:
Create Backup Directory
sudo mkdir -p /var/backups/mariadb
sudo chown mysql:mysql /var/backups/mariadbCreate Backup Script
sudo nano /usr/local/bin/mariadb_backup.shMariaDB Backup Script
#!/bin/bash
# MariaDB backup script
BACKUP_DIR="/var/backups/mariadb"
DATE=$(date +%Y%m%d_%H%M%S)
MYSQL_USER="root"
MYSQL_PASSWORD="your_root_password"
DATABASES="myapp_db" # Add your database names here
# Create backup directory if it doesn't exist
mkdir -p $BACKUP_DIR
# Backup each database
for DATABASE in $DATABASES; do
echo "Backing up database: $DATABASE"
mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD --single-transaction --routines --triggers $DATABASE | gzip > $BACKUP_DIR/${DATABASE}_backup_$DATE.sql.gz
done
# Backup all databases (alternative)
mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD --all-databases --single-transaction --routines --triggers | gzip > $BACKUP_DIR/all_databases_backup_$DATE.sql.gz
# Clean up old backups (keep 7 days)
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete
echo "Backup completed: $DATE"Make Script Executable and Secure
sudo chmod +x /usr/local/bin/mariadb_backup.sh
sudo chmod 700 /usr/local/bin/mariadb_backup.shTest Backup Script
sudo /usr/local/bin/mariadb_backup.shSchedule Daily Backups
sudo crontab -e
# Add this line for daily backups at 2 AM
0 2 * * * /usr/local/bin/mariadb_backup.sh >> /var/log/mariadb_backup.log 2>&1Backup Restoration
Restore Database from Backup
# Restore a specific database
gunzip -c /var/backups/mariadb/myapp_db_backup_YYYYMMDD_HHMMSS.sql.gz | mysql -u root -p myapp_db
# Restore all databases
gunzip -c /var/backups/mariadb/all_databases_backup_YYYYMMDD_HHMMSS.sql.gz | mysql -u root -p💾 Backup Tip: Test your backup restoration process regularly to ensure your backups are working correctly and your data is protected.
9
Monitoring and Maintenance
Monitor your MariaDB installation:
Useful Monitoring Queries
-- Check database connections
SHOW STATUS LIKE 'Threads_connected';
-- Monitor slow queries
SHOW STATUS LIKE 'Slow_queries';
-- Check database sizes
SELECT
table_schema AS 'Database',
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
GROUP BY table_schema;
-- Monitor InnoDB buffer pool usage
SHOW STATUS LIKE 'Innodb_buffer_pool_%';
-- Check for locked tables
SHOW OPEN TABLES WHERE In_use > 0;
-- View current processes
SHOW PROCESSLIST;
-- Check uptime
SHOW STATUS LIKE 'Uptime';Create Monitoring Script
nano /usr/local/bin/mariadb_monitor.shMariaDB Monitoring Script
#!/bin/bash
echo "=== MariaDB Status ==="
sudo systemctl status mariadb --no-pager -l
echo -e "\n=== Connection Count ==="
mysql -u root -p -e "SHOW STATUS LIKE 'Threads_connected';" 2>/dev/null
echo -e "\n=== Database Sizes ==="
mysql -u root -p -e "SELECT table_schema AS 'Database', ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)' FROM information_schema.tables GROUP BY table_schema;" 2>/dev/null
echo -e "\n=== InnoDB Buffer Pool ==="
mysql -u root -p -e "SHOW STATUS LIKE 'Innodb_buffer_pool_pages_%';" 2>/dev/null
echo -e "\n=== Slow Queries ==="
mysql -u root -p -e "SHOW STATUS LIKE 'Slow_queries';" 2>/dev/null
echo -e "\n=== Disk Usage ==="
df -h | grep -E '(Filesystem|/$|/var)'Make Monitoring Script Executable
chmod +x /usr/local/bin/mariadb_monitor.sh
./usr/local/bin/mariadb_monitor.sh📊 Monitoring Tip: Consider installing MySQLTuner for automated performance analysis: sudo apt install mysqltuner then run mysqltuner.
10
Troubleshooting
Common issues and solutions:
🛠️ Support: For additional help, check MariaDB documentation, community forums, or contact RamNode support for VPS-specific issues.
🎉 MariaDB Successfully Deployed!
Your MariaDB database server is now running on RamNode VPS with enhanced performance, security, and MySQL compatibility. You're ready to deploy production applications with confidence.
Production Ready
Secure Setup
Performance Optimized