MariaDB adalah fork dari MySQL yang fully compatible dengan performance lebih baik. Mari pelajari cara installnya.

Installing MariaDB

Ubuntu/Debian

# Update packages
sudo apt update
sudo apt install mariadb-server mariadb-client
Start service
sudo systemctl start mariadb
sudo systemctl enable mariadb

Verify Installation

# Check status
sudo systemctl status mariadb
Check version
mariadb --version

Secure Installation

Run Security Script

sudo mysql_secure_installation

Security Questions

Options:
- Set root password
- Remove anonymous users: Y
- Disallow root login remotely: Y
- Remove test database: Y
- Reload privilege tables: Y

Basic Database Operations

Login to MariaDB

# Login as root
sudo mariadb -u root -p
Or without password if configured
sudo mariadb

Database Commands

-- Show databases
SHOW DATABASES;
-- Create database
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Use database
USE myapp;
-- Drop database
DROP DATABASE myapp;

User Management

Create User

-- Create user
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'securepassword';
-- Grant privileges
GRANT ALL PRIVILEGES ON myapp.* TO 'appuser'@'localhost';
-- Apply changes
FLUSH PRIVILEGES;

Remote Access User

-- Create user with remote access
CREATE USER 'appuser'@'%' IDENTIFIED BY 'securepassword';
-- Grant privileges
GRANT ALL PRIVILEGES ON myapp.* TO 'appuser'@'%';
FLUSH PRIVILEGES;

View Users

SELECT User, Host FROM mysql.user;

Table Operations

Create Table

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CRUD Operations

-- Insert
INSERT INTO users (username, email, password_hash)
VALUES ('john', '[email protected]', 'hashedpassword');
-- Select
SELECT 
 FROM users;
SELECT  FROM users WHERE id = 1;
-- Update
UPDATE users SET email = '[email protected]' WHERE id = 1;
-- Delete
DELETE FROM users WHERE id = 1;

Configuration

Config File Location

# Main config file
/etc/mysql/mariadb.conf.d/50-server.cnf
Or
/etc/my.cnf

Common Settings

[mysqld]
# Character set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
Max connections
max_connections = 150
Buffer pool size (adjust based on RAM)
innodb_buffer_pool_size = 1G
Log slow queries
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

Enable Remote Access

# Comment out or change bind-address
# bind-address = 127.0.0.1
bind-address = 0.0.0.0

# Restart service
sudo systemctl restart mariadb

Backup and Restore

Backup Database

# Single database
mysqldump -u root -p myapp > backup.sql
All databases
mysqldump -u root -p --all-databases > full_backup.sql
With compression
mysqldump -u root -p myapp | gzip > backup.sql.gz

Restore Database

# Restore
mysql -u root -p myapp < backup.sql
From compressed
gunzip < backup.sql.gz | mysql -u root -p myapp

Performance Monitoring

Show Status

-- Connection status
SHOW STATUS LIKE 'Threads%';
-- Query cache status
SHOW STATUS LIKE 'Qcache%';
-- InnoDB status
SHOW ENGINE INNODB STATUS;

Process List

-- Active queries
SHOW PROCESSLIST;
-- Full queries
SHOW FULL PROCESSLIST;

Useful Tools

HeidiSQL

Windows GUI client for MariaDB
- Free and open source
- Multiple connections
- Query builder

DBeaver

Cross-platform database tool
- Supports MariaDB/MySQL
- Visual query builder
- Data export/import

Kesimpulan

MariaDB menyediakan database solution yang reliable dengan compatibility penuh dengan MySQL. Proper configuration dan security setup sangat penting untuk production use.