// DBA Guide

MariaDB DBA Guide

คู่มือสำหรับ Database Administrator — Backup/Restore, Galera Cluster, Monitoring และ Daily Maintenance

// Galera Cluster

Galera Cluster — Multi-Master HA

🌐 Galera Cluster คืออะไร?

Galera Cluster คือ synchronous multi-master replication สำหรับ MariaDB — ทุก node เป็น master สามารถ write ได้ทุก node และ data sync กันทันที รองรับ automatic node join/re-join

# /etc/mysql/mariadb.conf.d/galera.cnf
[galera]
wsrep_on = ON
wsrep_provider = /usr/lib/galera/libgalera_smm.so
wsrep_cluster_name = "MariaDB_Cluster"
wsrep_cluster_address = "gcomm://node1,node2,node3"
wsrep_sst_method = mariabackup
wsrep_node_address = "192.168.1.10"
wsrep_node_name = "node1"

// Backup

Backup & Restore

# mysqldump — Logical backup
mysqldump -u root -p \
  --single-transaction \
  --routines --triggers \
  --all-databases > full_backup.sql

# mariabackup — Physical backup (เร็วกว่า)
mariabackup --backup \
  --target-dir=/backup/$(date +%Y%m%d) \
  --user=root --password=secret

# Restore
mariabackup --prepare --target-dir=/backup/20240101
mariabackup --copy-back --target-dir=/backup/20240101

// Monitoring

Monitoring Queries

-- ดู current connections
SHOW PROCESSLIST;
SHOW STATUS LIKE "Threads_%";

-- InnoDB status
SHOW ENGINE INNODB STATUS\G

-- Buffer pool hit ratio
SELECT (1 - (
  variable_value/SUM(variable_value)
)) * 100 AS hit_ratio
FROM information_schema.global_status
WHERE variable_name IN (
  "Innodb_buffer_pool_reads",
  "Innodb_buffer_pool_read_requests"
);