MariaDB 11.3 membawa banyak improvement performa dan fitur baru. Dalam artikel ini, saya akan berbagi pengalaman menginstal dan mengkonfigurasi MariaDB 11.3 di CentOS 9 Stream untuk kebutuhan production.
Mengapa CentOS 9 Stream?
CentOS 9 Stream menawarkan:
– Rolling release model untuk update yang lebih cepat
– Compatibility dengan RHEL 9
– Security patches yang teratur
– Support untuk software terbaru
Persiapan Server CentOS 9
1. Update Sistem
sudo dnf update -y
sudo dnf upgrade -y
2. Install EPEL dan Tools
sudo dnf install epel-release -y
sudo dnf install wget curl nano vim -y
3. Disable SELinux (Opsional)
Untuk testing environment:
sudo setenforce 0
sudo sed -i 's/^SELINUX=.*/SELINUX=permissive/g' /etc/selinux/config
Untuk production, konfigurasi SELinux policy yang tepat.
Instalasi MariaDB 11.3
1. Tambahkan MariaDB Repository
Buat repository file:
sudo nano /etc/yum.repos.d/mariadb.repo
Isi dengan:
[mariadb]
name = MariaDB
baseurl = https://mirror.mariadb.org/yum/11.3/centos9-amd64
module_hotfixes = 1
gpgkey = https://rpm.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck = 1
2. Install MariaDB
sudo dnf install MariaDB-server MariaDB-client -y
3. Start dan Enable Service
sudo systemctl start mariadb
sudo systemctl enable mariadb
sudo systemctl status mariadb
Konfigurasi Keamanan MariaDB
1. Jalankan mysql_secure_installation
sudo mysql_secure_installation
Konfigurasi:
– Enter current password: (kosong, tekan Enter)
– Set root password: Ya
– Remove anonymous users: Ya
– Disallow root login remotely: Ya
– Remove test database: Ya
– Reload privileges: Ya
2. Konfigurasi Autentikasi
MariaDB 11 menggunakan unix_socket authentication secara default. Untuk production, ganti ke password:
sudo mysql -u root
ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('RootPass123!');
FLUSH PRIVILEGES;
EXIT;
Optimasi Konfigurasi MariaDB
1. Edit Server Configuration
sudo nano /etc/my.cnf.d/server.cnf
Tambahkan di section [mysqld]:
[mysqld]
# Basic Settings
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
InnoDB Settings
innodb_buffer_pool_size = 2G
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
Connection Settings
max_connections = 200
max_allowed_packet = 256M
wait_timeout = 600
interactive_timeout = 600
Query Cache (tidak lagi tersedia di MariaDB 11, gunakan Query Cache Plugin jika perlu)
query_cache_type = 0
query_cache_size = 0
Logging
log_error = /var/log/mariadb/mariadb.log
slow_query_log = 1
slow_query_log_file = /var/log/mariadb/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
Security
local_infile = 0
skip-symbolic-links
bind-address = 127.0.0.1
Performance Schema
performance_schema = ON
2. Buat Log Directory
sudo mkdir -p /var/log/mariadb
sudo chown mysql:mysql /var/log/mariadb
sudo chmod 755 /var/log/mariadb
3. Restart MariaDB
sudo systemctl restart mariadb
Setup Database dan User
1. Login ke MariaDB
mysql -u root -p
2. Buat Database
CREATE DATABASE IF NOT EXISTS app_database
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
3. Buat User Application
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'AppUserPass123!';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_database.* TO 'app_user'@'localhost';
FLUSH PRIVILEGES;
4. Buat User Backup
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'BackupPass123!';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;
Testing Instalasi
1. Test Koneksi
mysql -u app_user -p -e "SELECT 1;"
2. Test Query
USE app_database;
CREATE TABLE test_table (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO test_table (name, email) VALUES
('Test User', '[email protected]'),
('Another User', '[email protected]');
SELECT * FROM test_table;
3. Test Performance
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Queries';
Konfigurasi Firewall
1. Setup Firewalld
sudo firewall-cmd --permanent --add-service=mysql
sudo firewall-cmd --reload
2. Batasi Akses (Opsional)
Jika perlu akses remote dari IP tertentu:
sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.1.100" service name="mysql" accept'
sudo firewall-cmd --reload
Backup Otomatis dengan Cron
1. Buat Script Backup
sudo nano /usr/local/bin/backup_mariadb.sh
#!/bin/bash
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backup/mariadb"
USER="backup_user"
PASSWORD="BackupPass123!"
mkdir -p $BACKUP_DIR
mysqldump -u $USER -p$PASSWORD --all-databases --single-transaction > $BACKUP_DIR/full
backup$DATE.sql
Compress
gzip $BACKUP_DIR/full
backup$DATE.sql
Delete backups older than 7 days
find $BACKUP_DIR -name "full
backup*.sql.gz" -mtime +7 -delete
2. Beri Izin Eksekusi
sudo chmod +x /usr/local/bin/backup_mariadb.sh
3. Setup Cron
sudo crontab -e
Tambahkan:
0 2 * * * /usr/local/bin/backup_mariadb.sh >> /var/log/mariadb_backup.log 2>&1
Troubleshooting
Error: “Can’t connect to local MySQL server”
sudo systemctl status mariadb
sudo systemctl restart mariadb
sudo tail -f /var/log/mariadb/mariadb.log
Error: “Access denied for user”
Reset password root:
sudo systemctl stop mariadb
sudo mysqld_safe --skip-grant-tables --skip-networking &
mysql -u root
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPass123!';
EXIT;
sudo systemctl restart mariadb
Performance Issue
Periksa status resource:
SHOW ENGINE INNODB STATUS;
SHOW FULL PROCESSLIST;
SHOW VARIABLES LIKE '%buffer%';
Kesimpulan
MariaDB 11.3 di CentOS 9 Stream memberikan platform database yang powerful untuk enterprise production. Dengan konfigurasi yang tepat, Anda mendapatkan performa dan keamanan yang optimal.
