Replication و High Availability

۱۱.۱ چرا Replication؟

Replication یعنی کپی کردن داده از یک سرور MySQL به سرور(های) دیگر. کاربردها:

• Read Scaling: توزیع SELECT روی چند سرور
• High Availability: اگر primary خراب شد، replica جایگزین می‌شود
• Backup: backup گرفتن از replica بدون تأثیر روی production
• Geographic: سرور نزدیک به کاربر برای latency کم
• Analytics: اجرای کوئری‌های سنگین روی replica

۱۱.۲ Binary Log – پایه Replication

Binary Log (binlog) فایل لاگی است که تمام تغییرات داده را ثبت می‌کند. Replica این لاگ را می‌خواند و روی خودش apply می‌کند.

my.cnf - Primary📋 کپی
[mysqld]
# === تنظیمات اصلی Replication ===
server-id = 1                          # عدد یکتا برای هر سرور
log-bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW                    # ROW > MIXED > STATEMENT
binlog_row_image = FULL
expire_logs_days = 7                   # حذف خودکار binlog‌های قدیمی

# === GTID (شدیداً توصیه می‌شود) ===
gtid_mode = ON
enforce_gtid_consistency = ON

# === Performance ===
sync_binlog = 1                        # امن‌ترین (آهسته‌تر)
innodb_flush_log_at_trx_commit = 1

سه فرمت binlog

• STATEMENT: خود SQL ثبت می‌شود. کوچک، اما با NOW()/RAND() مشکل دارد
• ROW: تغییرات ردیف به ردیف ثبت می‌شود. امن، توصیه‌شده
• MIXED: پیش‌فرض هوشمند بین دو

SQL📋 کپی
-- بررسی وضعیت
SHOW VARIABLES LIKE 'log_bin';
SHOW MASTER STATUS;
SHOW BINARY LOGS;

-- مشاهده محتوا
SHOW BINLOG EVENTS IN 'mysql-bin.000001' LIMIT 20;

-- یا از CLI
mysqlbinlog /var/log/mysql/mysql-bin.000001 | less

۱۱.۳ Master-Slave Async Replication (Classic)

گام ۱: روی Primary

SQL - Primary📋 کپی
-- ساخت کاربر برای replication
CREATE USER 'repl'@'%' IDENTIFIED BY 'StrongReplPass2026!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

-- گرفتن snapshot
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
-- File: mysql-bin.000003, Position: 4521

-- یادداشت کنید! بعد:
UNLOCK TABLES;

-- یا در یک ترمینال جداگانه با mysqldump
-- mysqldump --all-databases --master-data=2 --single-transaction > dump.sql

گام ۲: انتقال snapshot به Replica

Bash📋 کپی
scp dump.sql user@replica-server:/tmp/
ssh user@replica-server
mysql < /tmp/dump.sql

گام ۳: روی Replica

my.cnf - Replica📋 کپی
[mysqld]
server-id = 2          # متفاوت از Primary
relay_log = /var/log/mysql/relay-bin
read_only = 1
log_slave_updates = 1   # برای chain replication
gtid_mode = ON
enforce_gtid_consistency = ON

SQL - Replica📋 کپی
-- روش قدیمی (با file/position)
CHANGE MASTER TO
    MASTER_HOST='primary-server.example.com',
    MASTER_USER='repl',
    MASTER_PASSWORD='StrongReplPass2026!',
    MASTER_LOG_FILE='mysql-bin.000003',
    MASTER_LOG_POS=4521;

-- روش مدرن با GTID (ترجیحاً)
CHANGE MASTER TO
    MASTER_HOST='primary-server.example.com',
    MASTER_USER='repl',
    MASTER_PASSWORD='StrongReplPass2026!',
    MASTER_AUTO_POSITION = 1;

-- شروع replication
START SLAVE;

-- بررسی وضعیت
SHOW SLAVE STATUSG
-- مهم‌ترین فیلدها:
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- Seconds_Behind_Master: 0

۱۱.۴ GTID - Global Transaction ID

روش مدرن: هر تراکنش یک شناسه یکتا globally unique دارد به فرم uuid:transaction_number. مزایا:

• Failover ساده‌تر؛ نیازی به تطابق file/position نیست
• تشخیص آسان تراکنش‌های از دست رفته
• Reconnect هوشمند بعد از network glitch

SQL📋 کپی
-- نمایش GTID‌های اجرا شده
SHOW VARIABLES LIKE 'gtid%';
SELECT @@global.gtid_executed;

-- اگر تراکنش‌ای روی replica نیاز به skip دارد:
SET GTID_NEXT='UUID:N';
BEGIN; COMMIT;
SET GTID_NEXT='AUTOMATIC';

۱۱.۵ Semi-Synchronous Replication

Async replication ممکن است داده از دست بدهد (اگر primary در میانه crash کند). Semi-Sync نیمه‌راه است:

Primary منتظر می‌ماند که حداقل یک Replica دریافت تراکنش را تأیید کند، سپس COMMIT را به client اعلام می‌کند.

SQL📋 کپی
-- روی Primary
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 1000;  -- ms

-- روی Replica
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

۱۱.۶ MariaDB Galera Cluster - Multi-Master Sync

برخلاف MySQL که async است، Galera سنکرون و multi-master است:

• هر node می‌تواند write بپذیرد
• تراکنش‌ها synchronously روی همه node‌ها replicate می‌شوند
• اگر یک node خراب شود، بقیه ادامه می‌دهند
• توصیه: حداقل ۳ node (برای جلوگیری از split-brain)

my.cnf - Galera Node📋 کپی
[mysqld]
binlog_format = ROW
default-storage-engine = InnoDB
innodb_autoinc_lock_mode = 2
bind-address = 0.0.0.0

# Galera
wsrep_on = ON
wsrep_provider = /usr/lib/galera/libgalera_smm.so
wsrep_cluster_address = "gcomm://node1,node2,node3"
wsrep_cluster_name = "icsd_cluster"
wsrep_node_address = "192.168.1.10"
wsrep_node_name = "node1"
wsrep_sst_method = mariabackup
wsrep_sst_auth = "sst:SecureSstPass!"

Bash - راه‌اندازی📋 کپی
# node اول (bootstrap)
sudo galera_new_cluster

# node‌های بعدی به ترتیب
sudo systemctl start mariadb

# بررسی وضعیت
mysql -e "SHOW STATUS LIKE 'wsrep_cluster_size';"
# Value: 3

۱۱.۷ ProxySQL - Read/Write Splitting

اپلیکیشن نباید بداند کدام سرور Primary و کدام Replica است. ProxySQL middleware این کار را می‌کند:

Application
    │
    ▼
ProxySQL :6033
    │
    ├──► Primary (writes)
    └──► Replica 1, 2, 3 (reads)
    

SQL - ProxySQL📋 کپی
-- اضافه کردن سرورها
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES
    (10, 'primary.example.com', 3306),       -- write group
    (20, 'replica1.example.com', 3306),       -- read group
    (20, 'replica2.example.com', 3306);

-- قانون routing
INSERT INTO mysql_query_rules(rule_id, match_pattern, destination_hostgroup, apply) VALUES
    (1, '^SELECT.*FOR UPDATE$', 10, 1),       -- SELECT FOR UPDATE → primary
    (2, '^SELECT', 20, 1),                     -- بقیه SELECT‌ها → replica
    (3, '^(INSERT|UPDATE|DELETE)', 10, 1);     -- writes → primary

LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;

۱۱.۸ Failover - تبدیل Replica به Primary

روش دستی

SQL📋 کپی
-- روی Replica که می‌خواهیم Primary شود
STOP SLAVE;
RESET SLAVE ALL;          -- پاک کردن تنظیمات replication
SET GLOBAL read_only = OFF;
SET GLOBAL super_read_only = OFF;

-- اپلیکیشن را به این سرور هدایت کنید
-- بقیه replicaها را به این سرور جدید point کنید

ابزارهای خودکار

• MHA (Master High Availability): قدیمی، اما پایدار
• Orchestrator: از GitHub، با UI گرافیکی
• MaxScale: از MariaDB
• Vitess: برای پروژه‌های بزرگ

۱۱.۹ Replication Lag - مشکل اصلی

Replica همیشه چند ثانیه عقب‌تر از Primary است. اگر زیاد شود، اپلیکیشن داده قدیمی نشان می‌دهد.

SQL📋 کپی
-- بررسی lag
SHOW SLAVE STATUSG
-- Seconds_Behind_Master: 0  ← هدف

-- Performance Schema روش دقیق‌تر
SELECT
    PROCESSLIST_TIME AS lag_sec
FROM performance_schema.threads
WHERE NAME = 'thread/sql/slave_sql';

-- Tools حرفه‌ای
-- pt-heartbeat - تشخیص دقیق lag

دلایل رایج Lag

• Single-thread SQL (پیش از MySQL 5.7)
• Query طولانی (مثل ALTER TABLE روی جدول بزرگ)
• دیسک کند replica
• تراکنش‌های بزرگ

راه‌حل‌ها

my.cnf📋 کپی
[mysqld]
# Multi-thread replication
slave_parallel_workers = 8
slave_parallel_type = LOGICAL_CLOCK
slave_preserve_commit_order = ON

۱۱.۱۰ مانیتورینگ Replication

SQL📋 کپی
-- Status کلی
SHOW SLAVE STATUSG

-- در Performance Schema
SELECT * FROM performance_schema.replication_connection_status;
SELECT * FROM performance_schema.replication_applier_status_by_worker;

-- alert ساده (cron)
mysql -e "SHOW SLAVE STATUSG" | grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind"
# اگر هر کدام Yes نبود یا Seconds_Behind > 60: alert

۱۱.۱۱ خلاصه فصل

• Binary Log پایه Replication است؛ ROW format توصیه می‌شود
• GTID جایگزین مدرن file/position است
• Semi-Sync جلوگیری از data loss در سطح متوسط
• Galera برای high availability واقعی multi-master
• ProxySQL برای read/write splitting
• Replication Lag را با parallel worker و monitoring مدیریت کنید