ตั้ง MySQL Replication Master-Slave บน VPS

กรกฎาคม 2026 · อ่าน 8 นาที

MySQL Replication คือ mechanism ที่ให้ master database สั่ง slave database ให้ synchronize ข้อมูลไปด้วยแบบ real-time ผ่าน binary log (binlog) Master คือตัวที่ record ทุก query ส่วน Slave อ่าน binlog แล้ว replay query เดียวกัน ประโยชน์หลัก ได้แก่ การสำรองข้อมูลแบบ live ที่ทำงานต่อจากการสูญหาย database ของ master การกระจาย read query ไปยัง slave เพื่อลด load หรือการสร้าง backup source โดยไม่ต้องหยุดการให้บริการของ master

เปิดใช้ Binary Logging บน Master

ก่อนอื่น ต้องเปิดใช้งาน binary logging บน master server ซึ่งเป็นการบันทึกการเปลี่ยนแปลงข้อมูลทั้งหมด edit ไฟล์ /etc/mysql/mysql.conf.d/mysqld.cnf (หรือ /etc/my.cnf ขึ้นอยู่กับการติดตั้ง):

# ในส่วน [mysqld]
[mysqld]
log_bin = /var/log/mysql/mysql-bin.log
server_id = 1
binlog_format = row
expire_logs_days = 10

ค่า server_id ต้องไม่ซ้ำกัน master ใช้ 1 slave ใช้ 2 3 4 ... ตามจำนวน binlog_format = row เป็นรูปแบบที่ safe สำหรับ replication expire_logs_days เป็นจำนวนวันที่เก็บ binlog ก่อนลบอัตโนมัติ restart MySQL:

sudo systemctl restart mysql

ตรวจว่า binary logging เปิดอยู่:

mysql -u root -p -e "SHOW VARIABLES LIKE 'log_bin';"

ต้องเห็น log_bin = ON

สร้างบัญชี Replication User บน Master

Master ต้องมีบัญชี user พิเศษสำหรับ slave เข้ามาอ่าน binlog และ sync ข้อมูล:

mysql -u root -p
CREATE USER 'repl_user'@'SLAVE_IP' IDENTIFIED BY 'repl_password_123';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'SLAVE_IP';
FLUSH PRIVILEGES;

แทน SLAVE_IP ด้วย IP address ของ slave server เช่น 203.0.113.50 ถ้ามี slave หลายตัว สามารถใช้ wildcard 'repl_user'@'%' แต่ไม่แนะนำเพื่อความปลอดภัย สำหรับ MySQL 8.0.20+ สามารถใช้ CREATE USER และ GRANT พร้อมกัน:

CREATE USER 'repl_user'@'SLAVE_IP' IDENTIFIED BY 'repl_password_123' REQUIRE SSL;

Tip: ระวังเก็บ password ที่ซับซ้อนและไม่เก็บไว้ในไฟล์ plain text สามารถใช้ MySQL wallet หรือ mysql_config_editor เก็บ credentials อย่างปลอดภัย

สำรองข้อมูล Master และสกัดจุดเริ่มต้น Replication

ต่อไป ต้องสร้าง backup ของ master database พร้อมบันทึกตำแหน่ง binlog ที่ slave ต้องเริ่ม sync จาก ใช้ mysqldump ด้วยตัวเลือก --master-data (MySQL 5.7 และเก่า) หรือ --source-data (MySQL 8.0.20+):

mysqldump -u root -p \
  --master-data=2 \
  --single-transaction \
  --routines \
  --triggers \
  --all-databases > master_backup.sql

ตัวเลือกสำคัญ:

--master-data=2 — บันทึก CHANGE MASTER TO statement ใน comment (เลขที่ binlog และ position)
--single-transaction — ไม่ lock table ให้ application ใช้ database ต่อได้
--routines — export stored procedures และ functions
--triggers — export triggers
--all-databases — backup ทุก database

ไฟล์ backup จะมี comment แบบนี้อยู่:

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=154;

จำค่า LOG FILE และ LOG POS ไว้สำหรับทำการตั้งค่า slave

คัดลอก Backup ไปยัง Slave แล้วทำการ Restore

ส่ง backup file ไปยัง slave server ด้วย SCP หรือวิธีการอื่น:

scp master_backup.sql user@SLAVE_IP:/tmp/

บน slave import backup:

mysql -u root -p < /tmp/master_backup.sql

ตรวจว่า database ถูก import:

mysql -u root -p -e "SHOW DATABASES;"

ตั้งค่า Slave แล้วเริ่มต้น Replication

ต่อไปต้องแก้ไข slave config เพิ่มเติม edit /etc/mysql/mysql.conf.d/mysqld.cnf บน slave:

[mysqld]
server_id = 2
relay-log = /var/log/mysql/mysql-relay-bin
relay-log-index = /var/log/mysql/mysql-relay-bin.index
read_only = ON

server_id = 2 ต้องไม่ซ้ำกับ master read_only = ON ป้องกัน slave ได้ write (ข้อมูลควรผ่าน master เท่านั้น) restart:

sudo systemctl restart mysql

ตั้งค่า connection ของ slave ไปยัง master ผ่าน CHANGE MASTER TO (MySQL 5.7 และเก่า):

mysql -u root -p
STOP SLAVE;
CHANGE MASTER TO
  MASTER_HOST='MASTER_IP',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='repl_password_123',
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=154;
START SLAVE;

สำหรับ MySQL 8.0.22+ ใช้ syntax ใหม่:

STOP REPLICA;
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='MASTER_IP',
  SOURCE_USER='repl_user',
  SOURCE_PASSWORD='repl_password_123',
  SOURCE_LOG_FILE='mysql-bin.000003',
  SOURCE_LOG_POS=154;
START REPLICA;

แทน MASTER_IP ด้วย IP ของ master MASTER_LOG_FILE และ MASTER_LOG_POS ได้มาจาก backup file comment ที่ดึงมาในขั้นตอนที่แล้ว

Tip: ถ้ามี GTID (Global Transaction ID) เปิดใช้อยู่ ไม่ต้องใส่ MASTER_LOG_FILE และ MASTER_LOG_POS ให้ส่วน GTID ของ MySQL จัดการเอง ใช้ CHANGE MASTER TO MASTER_AUTO_POSITION=1

ตรวจสอบว่า MySQL Replication ทำงานถูกต้อง

เมื่อตั้งค่าเสร็จแล้ว ใช้คำสั่งเหล่านี้ตรวจสอบสถานะ replication:

# MySQL 5.7 และเก่า
mysql -u root -p -e "SHOW SLAVE STATUS\G"

# MySQL 8.0.22+
mysql -u root -p -e "SHOW REPLICA STATUS\G"

ดู output ที่สำคัญ:

Slave_IO_Running หรือ Replica_IO_Running — ต้องเป็น Yes (slave connect ไป master ได้)
Slave_SQL_Running หรือ Replica_SQL_Running — ต้องเป็น Yes (slave replay query ได้)
Seconds_Behind_Master — ระยะเวลาที่ lag ต้องเป็น 0 หรือตัวเลขเล็กน้อย ถ้า NULL แสดงว่า replication ไม่ทำงาน
Last_Error — ถ้ามี error จะแสดงที่นี่

# ตรวจเฉพาะสถานะหลัก
mysql -u root -p -e "SHOW SLAVE STATUS\G" | grep -E "(Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master)"

Tip: ถ้า Seconds_Behind_Master > 0 แสดงว่า slave ตามไม่ทันแบบ real-time ตรวจสอบ slave server load ความเร็ว network และพิจารณาปรับปรุง slave hardware หรือลดจำนวน write query ไปยัง master

แก้ปัญหาที่พบบ่อย

Error: "Access denied for user 'repl_user'@'SLAVE_IP'"

Slave ไม่สามารถเข้าถึง master ด้วยบัญชี replication user ได้ ตรวจสอบ

Password ถูกต้องไหม ลองตั้งค่าใหม่ด้วย GRANT ใหม่บน master
Slave IP ถูกต้องไหม ตรวจว่า GRANT ระบุ IP เดียวกับ slave ที่เชื่อมต่อมา
Network connectivity ดีไหม ลอง ping และ nc ตรวจว่า slave ถึง port 3306 ของ master ได้

# บน master ตรวจบัญชี
mysql -u root -p
SELECT user, host FROM mysql.user WHERE user='repl_user';

# ลบและสร้างใหม่
DROP USER 'repl_user'@'SLAVE_IP';
CREATE USER 'repl_user'@'SLAVE_IP' IDENTIFIED BY 'repl_password_new';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'SLAVE_IP';
FLUSH PRIVILEGES;

Error: "Slave_IO_Running: No" และ "Last_Error: I/O error reading"

Slave socket ไม่สามารถเชื่อมต่อไป master ได้ หรือ master ปฏิเสธการเชื่อมต่อ ตรวจสอบ:

IP master ถูกต้องไหม ลอง telnet หรือ nc ตรวจว่า port 3306 เปิด
MySQL master เปิดอยู่ไหม ลอง systemctl status mysql
Firewall ห้ามไหม ตรวจ iptables/ufw และ cloud provider security group
Slave ตั้งค่า CHANGE MASTER TO ถูกต้องไหม ลอง SHOW SLAVE STATUS ดู MASTER_HOST และ port

# ตรวจ socket บน slave
telnet MASTER_IP 3306
# หรือใช้ nc
nc -zv MASTER_IP 3306

# ลองตั้งค่า CHANGE MASTER TO ใหม่
STOP SLAVE;
CHANGE MASTER TO
  MASTER_HOST='MASTER_IP',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='repl_password_123';
START SLAVE;

Error: "Slave_SQL_Running: No" และ "Last_Error: Error executing query"

Slave กำลัง execute query จาก master แล้วเจอ error เช่น duplicate key หรือ table ไม่มี ลองดู full error:

mysql -u root -p -e "SHOW SLAVE STATUS\G" | grep Last_Error

วิธีแก้:

ถ้า error เป็น duplicate key หลังจาก crash ของ slave ให้ skip query นั้นชั่วคราว:
```
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
START SLAVE;
```
ถ้า table ไม่มี slave ต้อง re-sync ข้อมูล สร้าง backup ใหม่จาก master

Error: "Seconds_Behind_Master: NULL"

Replication ไม่ทำงาน (IO หรือ SQL Running เป็น No) ลองตรวจสอบจากคำสั่ง SHOW SLAVE STATUS ว่าส่วนไหนที่ล้มเหลว ตามขั้นตอน troubleshooting ด้านบน

Error: "Relay log corruption detected"

Relay log ของ slave บ่งชี้ถูกทำลายหรือ corrupt ลองรีสตาร์ท slave หรือลบ relay log ทั้งหมด:

STOP SLAVE;
RESET SLAVE;
# แล้วตั้งค่า CHANGE MASTER TO ใหม่ และ START SLAVE

คำถามที่พบบ่อย

MySQL Replication กับ MariaDB Replication ต่างกันไหม?

MariaDB เป็น fork ของ MySQL ที่ยังคงความเข้ากันได้กับ MySQL replication ทั้งไฟล์ config และ command ส่วนใหญ่เหมือนกัน แต่ MariaDB มี parallelization ดีกว่า (ในการ replay) และ GTID ที่ต่างกันเล็กน้อย ถ้าตั้งค่า MySQL-to-MariaDB ต้องระวังความเข้ากันได้ของ replication format

ความเสี่ยงจากการใช้ Single Master คืออะไร?

Master ยังคงเป็น single point of failure ถ้า master ตาย slave เดิมไม่สามารถ accept write ได้ (เพราะ read_only=ON) ต้องมีขั้นตอน failover เพื่อ promote slave เป็น master ใหม่ หรือ rollback application ไปยัง backup เวลา downtime นาน ถ้าต้องการ HA ที่สูงขึ้น ให้พิจารณา multi-master replication หรือ MySQL InnoDB Cluster

สามารถเปลี่ยน binlog_format จาก ROW เป็น STATEMENT ได้ไหม?

ได้แต่ต้องระวัง STATEMENT format บ้างเหตุการณ์ replication ไม่ safe เช่น non-deterministic function (NOW(), RAND()) ROW format นั้น safe กว่า แต่ใช้พื้นที่ binlog มากกว่า และ replication ช้ากว่า แต่ความปลอดภัย > ประสิทธิภาพ แนะนำใช้ ROW