MySQL Replication Setup — ตั้งค่า Source-Replica แบบละเอียด

Categories

เมื่อเว็บไซต์หรือแอปพลิเคชันเติบโตขึ้น เซิร์ฟเวอร์ฐานข้อมูลตัวเดียวอาจรับ Load ไม่ไหว การอ่านข้อมูลจำนวนมากจาก Application หลายตัวพร้อมกันทำให้ Response Time ช้าลง นอกจากนี้ถ้าเซิร์ฟเวอร์ตัวเดียวล่ม ระบบทั้งหมดก็หยุดทำงาน

Replication เป็นกลไกที่คัดลอกข้อมูลจากเซิร์ฟเวอร์ต้นทาง (Source) ไปยังเซิร์ฟเวอร์ปลายทาง (Replica) แบบอัตโนมัติ บทความนี้จะแนะนำการตั้งค่า Replication แบบ Source-Replica บน Cloud VPS ตั้งแต่เตรียมเซิร์ฟเวอร์ ตั้งค่า Configuration ไปจนถึงตรวจสอบสถานะและแก้ไขปัญหาเบื้องต้น

Replication คืออะไร และทำงานอย่างไร

Replication คือกระบวนการที่เซิร์ฟเวอร์ Source บันทึกทุก Transaction ที่เปลี่ยนแปลงข้อมูลลงใน Binary Log จากนั้นเซิร์ฟเวอร์ Replica จะดึง Binary Log เหล่านี้มา Replay ซ้ำเพื่อให้ข้อมูลตรงกับ Source

กระบวนการนี้ทำงานแบบ Asynchronous โดย Default หมายความว่า Source ไม่ต้องรอให้ Replica ทำงานเสร็จก่อนถึงจะ Commit Transaction ได้ ข้อดีคือไม่กระทบ Performance ของ Source แต่ข้อเสียคือ Replica อาจมีข้อมูลล่าช้ากว่าเล็กน้อย (Replication Lag)

ประโยชน์ของ Replication

กระจาย Read Load — ส่ง Query ที่อ่านข้อมูล (SELECT) ไปยัง Replica แทน ลดภาระของ Source ที่ต้องรับทั้ง Read และ Write

High Availability — ถ้า Source ล่ม สามารถ Promote Replica ขึ้นเป็น Source ตัวใหม่ได้ ลด Downtime ลงอย่างมาก

Backup โดยไม่กระทบ Production — ทำ Backup จาก Replica แทน Source ทำให้ไม่กระทบ Performance ของระบบหลัก

Analytics แยกเซิร์ฟเวอร์ — รัน Query วิเคราะห์ข้อมูลบน Replica โดยไม่กระทบ Transaction ของผู้ใช้งาน

สิ่งที่ต้องเตรียม

ต้องมี Cloud VPS อย่างน้อย 2 เครื่อง ทั้งสองเครื่องติดตั้งระบบจัดการฐานข้อมูลเวอร์ชันเดียวกัน (แนะนำ 8.0) และเปิด Port 3306 ให้สื่อสารกันได้ ในตัวอย่างนี้จะใช้ IP ดังนี้ — Source: 192.168.1.100, Replica: 192.168.1.200

ตั้งค่าเซิร์ฟเวอร์ Source

แก้ไข Configuration

เปิดไฟล์ Configuration ของ Source

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

เพิ่มหรือแก้ไขค่าต่อไปนี้ใน Section [mysqld]

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
binlog_do_db = myapp_db
bind-address = 0.0.0.0

server-id — ตัวเลขที่ไม่ซ้ำกันในแต่ละเซิร์ฟเวอร์ Source ใช้ 1 เป็น Convention

log_bin — เปิด Binary Log ซึ่งจำเป็นสำหรับ Replication ระบุ Path และ Prefix ของไฟล์ Log

binlog_format = ROW — บันทึกการเปลี่ยนแปลงในระดับแถว ซึ่งปลอดภัยและเชื่อถือได้มากที่สุด

binlog_do_db — ระบุเฉพาะฐานข้อมูลที่ต้องการ Replicate ถ้าต้องการทุกฐานข้อมูลให้ลบบรรทัดนี้ออก

bind-address — เปิดให้รับ Connection จากเครื่องอื่น (ค่า Default คือ 127.0.0.1 ที่รับแค่ localhost)

Restart Service เพื่อให้ค่าใหม่มีผล

sudo systemctl restart mysql

สร้าง Replication User

สร้าง User สำหรับ Replica ใช้เชื่อมต่อเข้ามาดึง Binary Log

CREATE USER 'repl_user'@'192.168.1.200' IDENTIFIED BY 'ReplStrongPass123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.200';
FLUSH PRIVILEGES;

ระบุ IP ของ Replica เพื่อจำกัดให้เฉพาะเซิร์ฟเวอร์ที่ต้องการเท่านั้นที่เชื่อมต่อเข้ามาได้ สิทธิ์ REPLICATION SLAVE อนุญาตให้อ่าน Binary Log ได้เท่านั้น ไม่สามารถแก้ไขข้อมูลบน Source ได้

บันทึก Binary Log Position

ก่อนเริ่ม Replication ต้องทราบตำแหน่งปัจจุบันของ Binary Log เพื่อบอก Replica ว่าจะเริ่มอ่านจากจุดไหน

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

ผลลัพธ์จะแสดง File (เช่น mysql-bin.000001) และ Position (เช่น 154) ให้จดค่าทั้งสองไว้ ห้ามปิด Session นี้จนกว่าจะ Dump ข้อมูลเสร็จ

Dump ข้อมูลจาก Source

เปิด Terminal ใหม่แล้ว Dump ข้อมูลฐานข้อมูลที่ต้องการ Replicate

mysqldump -u root -p --single-transaction --routines --triggers --events myapp_db > myapp_db_dump.sql

หลัง Dump เสร็จ กลับไป Session แรกแล้วปลด Lock

UNLOCK TABLES;

คัดลอกไฟล์ Dump ไปยังเซิร์ฟเวอร์ Replica

scp myapp_db_dump.sql [email protected]:/tmp/

ตั้งค่าเซิร์ฟเวอร์ Replica

แก้ไข Configuration

เปิดไฟล์ Configuration ของ Replica

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

เพิ่มหรือแก้ไขค่าต่อไปนี้

[mysqld]
server-id = 2
relay_log = /var/log/mysql/mysql-relay-bin.log
read_only = 1
bind-address = 0.0.0.0

server-id = 2 — ต้องไม่ซ้ำกับ Source ใช้ตัวเลขที่ต่างจาก 1

relay_log — ระบุ Path ของ Relay Log ที่ Replica ใช้เก็บ Binary Log ที่ดึงมาจาก Source ก่อนนำไป Apply

read_only = 1 — ป้องกันไม่ให้ User ทั่วไปเขียนข้อมูลเข้า Replica เพื่อไม่ให้ข้อมูลต่างจาก Source

Restart Service

sudo systemctl restart mysql

Import ข้อมูลจาก Source

สร้างฐานข้อมูลบน Replica แล้ว Import ข้อมูลที่ Dump มา

mysql -u root -p -e "CREATE DATABASE myapp_db;"
mysql -u root -p myapp_db < /tmp/myapp_db_dump.sql

ตั้งค่า Replication

Login เข้า Database Shell ของ Replica แล้วบอกให้เชื่อมต่อไปยัง Source

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='192.168.1.100',
  SOURCE_USER='repl_user',
  SOURCE_PASSWORD='ReplStrongPass123!',
  SOURCE_LOG_FILE='mysql-bin.000001',
  SOURCE_LOG_POS=154;

แทนค่า SOURCE_LOG_FILE และ SOURCE_LOG_POS ด้วยค่าที่จดไว้จาก SHOW MASTER STATUS บน Source คำสั่ง CHANGE REPLICATION SOURCE TO เป็นไวยากรณ์ใหม่ใน 8.0.23 ขึ้นไป หากใช้เวอร์ชันเก่ากว่าให้ใช้ CHANGE MASTER TO แทน

เริ่มกระบวนการ Replication

START REPLICA;

ตรวจสอบสถานะ Replication

หลังเริ่ม Replication แล้ว ตรวจสอบว่าทำงานถูกต้อง

SHOW REPLICA STATUS\G

ค่าที่ต้องตรวจสอบ — Replica_IO_Running ต้องเป็น Yes แสดงว่า Replica ดึง Binary Log จาก Source ได้สำเร็จ Replica_SQL_Running ต้องเป็น Yes แสดงว่า Replica Apply SQL Statements สำเร็จ Seconds_Behind_Source แสดงระยะเวลาที่ Replica ล่าช้ากว่า Source ค่า 0 หมายความว่า Sync แล้ว Last_Error ถ้าว่างเปล่าแสดงว่าไม่มี Error

ทดสอบ Replication

ทดสอบว่าข้อมูลถูกคัดลอกจริง โดยสร้างตารางทดสอบบน Source

-- รันบน Source
USE myapp_db;
CREATE TABLE repl_test (id INT PRIMARY KEY, name VARCHAR(50));
INSERT INTO repl_test VALUES (1, 'Hello Replication');

จากนั้นตรวจสอบบน Replica

-- รันบน Replica
USE myapp_db;
SELECT * FROM repl_test;

ถ้าเห็นข้อมูล “Hello Replication” แสดงว่าระบบทำงานถูกต้อง ลบตารางทดสอบบน Source เมื่อเสร็จแล้ว

-- รันบน Source
DROP TABLE repl_test;

ใช้ GTID-Based Replication (แนะนำ)

นอกจาก Binary Log Position แบบดั้งเดิม ตั้งแต่เวอร์ชัน 5.6 ขึ้นไปรองรับ GTID (Global Transaction Identifier) ที่ระบุตัวตนของทุก Transaction ด้วย UUID ทำให้การจัดการ Replication ง่ายขึ้นมาก โดยเฉพาะเมื่อมี Replica หลายตัวหรือต้องการ Failover

เพิ่ม Configuration เหล่านี้ทั้งบน Source และ Replica

[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON

Restart ทั้งสองเครื่อง จากนั้นตั้งค่า Replication บน Replica ด้วย GTID แทนการระบุ File และ Position

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='192.168.1.100',
  SOURCE_USER='repl_user',
  SOURCE_PASSWORD='ReplStrongPass123!',
  SOURCE_AUTO_POSITION=1;

START REPLICA;

ข้อดีของ GTID คือไม่ต้องจำ Binary Log File และ Position ระบบจะหาจุดที่ต้อง Sync เองอัตโนมัติ การ Failover และ Switchover ทำได้ง่ายกว่ามาก

ปรับ Firewall สำหรับ Replication

เปิด Port 3306 ให้เฉพาะ IP ของ Replica บน Source และในทางกลับกัน

# บน Source — อนุญาต Replica เข้ามา
sudo ufw allow from 192.168.1.200 to any port 3306

# บน Replica — อนุญาต Source (ถ้าต้องการ)
sudo ufw allow from 192.168.1.100 to any port 3306

sudo ufw reload

ไม่ควรเปิด Port 3306 ให้ทุก IP เพราะเป็นความเสี่ยงด้านความปลอดภัย จำกัดให้เฉพาะเซิร์ฟเวอร์ที่เกี่ยวข้องเท่านั้น

ปัญหาที่พบบ่อยและวิธีแก้ไข

Replica_IO_Running = No — Replica เชื่อมต่อ Source ไม่ได้ ตรวจสอบ Firewall, IP Address, Username/Password และว่า Source เปิด bind-address แล้วหรือไม่

Replica_SQL_Running = No — Replica Apply SQL ไม่สำเร็จ อาจเกิดจากมีข้อมูลซ้ำ (Duplicate Key) หรือตารางไม่ตรงกัน ดูรายละเอียดจาก Last_SQL_Error แล้วแก้ไข อาจต้อง Skip Error นั้นด้วยคำสั่ง

SET GLOBAL sql_replica_skip_counter = 1;
START REPLICA;

Seconds_Behind_Source สูงมาก — Replica ตามไม่ทัน อาจเกิดจาก Replica มี Spec ต่ำกว่า Source, Network ช้า หรือมี Long-running Transaction บน Source พิจารณาอัพเกรด Spec ของ Replica หรือปรับ Parallel Replication

# เปิด Parallel Replication (ใน Configuration)
[mysqld]
replica_parallel_workers = 4
replica_parallel_type = LOGICAL_CLOCK

ข้อมูลไม่ตรงกัน (Data Drift) — อาจเกิดจากมีคนเขียนข้อมูลเข้า Replica โดยตรง วิธีป้องกันคือตั้ง read_only = 1 และ super_read_only = 1 ใน Configuration ของ Replica

Best Practices สำหรับ Replication

ใช้ GTID — ง่ายกว่า Binary Log Position ในทุกสถานการณ์ โดยเฉพาะเมื่อมีหลาย Replica หรือต้อง Failover

Monitor สม่ำเสมอ — ตรวจสอบ Replication Status เป็นประจำ ตั้ง Alert เมื่อ Seconds_Behind_Source เกินค่าที่กำหนด หรือเมื่อ IO/SQL Thread หยุดทำงาน

ตั้ง read_only และ super_read_only บน Replica — ป้องกันไม่ให้ข้อมูลถูกเขียนเข้า Replica โดยตรง ซึ่งจะทำให้ข้อมูลไม่ตรงกับ Source

ใช้ Semi-Synchronous Replication สำหรับ Critical Data — กำหนดให้ Source ต้องรอจนกว่า Replica อย่างน้อย 1 ตัวจะรับ Binary Log ก่อนถึงจะ Commit ลด Risk ของการสูญเสียข้อมูลเมื่อ Source ล่ม

Backup จาก Replica — ทำ Backup จาก Replica แทน Source เพื่อไม่ให้กระทบ Performance ของระบบหลัก

สรุป

Replication เป็นฟีเจอร์พื้นฐานที่สำคัญสำหรับระบบฐานข้อมูลที่ต้องการ High Availability และ Read Scalability การตั้งค่าแบบ Source-Replica ไม่ซับซ้อนมาก สิ่งสำคัญคือ ใช้ GTID เพื่อความสะดวกในการจัดการ ตั้ง read_only บน Replica เพื่อป้องกัน Data Drift ตรวจสอบ Replication Status สม่ำเสมอ และทดสอบ Failover เป็นระยะ เมื่อตั้งค่าเรียบร้อยแล้วจะช่วยเพิ่มทั้งความเร็วและความเสถียรให้กับระบบฐานข้อมูลอย่างมาก

แนะนำบริการ DE

การตั้ง Replication ต้องใช้เซิร์ฟเวอร์อย่างน้อย 2 เครื่องที่สื่อสารกันได้ Cloud VPS ของ DE สามารถสร้างเซิร์ฟเวอร์หลายเครื่องใน Network เดียวกันได้ทันที พร้อม Root Access เต็มรูปแบบสำหรับตั้งค่า Configuration ตามต้องการ และ SSD Storage ที่ให้ IOPS สูงเหมาะกับ Database Workload

สำหรับผู้ที่ต้องการฐานข้อมูลพร้อมใช้งานโดยไม่ต้องจัดการเซิร์ฟเวอร์เอง Cloud Hosting ของ DE มาพร้อมระบบจัดการฐานข้อมูลที่ตั้งค่าให้เรียบร้อย เหมาะกับเว็บไซต์ที่ยังไม่ต้องการ Replication แต่ต้องการความเสถียรสำหรับการใช้งานทั่วไป