Cara Install dan Setup MariaDB di Linux

# Update packages
sudo apt update

# Install MariaDB
sudo apt install mariadb-server mariadb-client

# Start service
sudo systemctl start mariadb
sudo systemctl enable mariadb

# Check status
sudo systemctl status mariadb

# Check version
mariadb --version

sudo mysql_secure_installation

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

# Login as root
sudo mariadb -u root -p

# Or without password if configured
sudo mariadb

-- 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;

-- Create user
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'securepassword';

-- Grant privileges
GRANT ALL PRIVILEGES ON myapp.* TO 'appuser'@'localhost';

-- Apply changes
FLUSH PRIVILEGES;

-- Create user with remote access
CREATE USER 'appuser'@'%' IDENTIFIED BY 'securepassword';

-- Grant privileges
GRANT ALL PRIVILEGES ON myapp.* TO 'appuser'@'%';
FLUSH PRIVILEGES;

SELECT User, Host FROM mysql.user;

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;

-- Insert
INSERT INTO users (username, email, password_hash)
VALUES ('john', 'john@example.com', 'hashedpassword');

-- Select
SELECT * FROM users;
SELECT * FROM users WHERE id = 1;

-- Update
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

-- Delete
DELETE FROM users WHERE id = 1;

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

# Or
/etc/my.cnf

[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

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

# Restart service
sudo systemctl restart mariadb

# 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
mysql -u root -p myapp < backup.sql

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

-- Connection status
SHOW STATUS LIKE 'Threads%';

-- Query cache status
SHOW STATUS LIKE 'Qcache%';

-- InnoDB status
SHOW ENGINE INNODB STATUS;

-- Active queries
SHOW PROCESSLIST;

-- Full queries
SHOW FULL PROCESSLIST;

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

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