ตั้งค่า MySQL และ MariaDB Replication แบบ Master-Replica

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

คู่มือนี้ตั้ง Replication แบบหนึ่งต่อหนึ่งด้วยวิธี GTID ซึ่งเป็นวิธีที่แนะนำในปัจจุบัน เพราะจัดการตำแหน่งการจำลองให้เองและกู้คืนง่ายกว่าวิธีเดิมที่ต้องจำชื่อไฟล์และตำแหน่ง

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

• เซิร์ฟเวอร์สองเครื่องที่ติดตั้ง MySQL 8.0 หรือ MariaDB 10.6 ขึ้นไป เวอร์ชันเดียวกันหรือตัวสำรองใหม่กว่า
• เครือข่ายภายในระหว่างกัน และพื้นที่ดิสก์บนตัวสำรองไม่น้อยกว่าตัวหลัก
• ช่วงเวลาที่ปิดการเขียนได้สั้น ๆ สำหรับคัดลอกข้อมูลตั้งต้น

ขั้นตอนที่ 1: ตั้งค่าเครื่องหลัก

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

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
binlog_expire_logs_seconds = 604800
max_binlog_size = 512M

# GTID ทำให้ตัวสำรองรู้เองว่าคัดลอกถึงไหนแล้ว
gtid_mode = ON
enforce_gtid_consistency = ON

# ฟังที่ IP ภายใน ไม่ใช่ทุกการ์ดเครือข่าย
bind-address = 10.0.0.21

สำหรับ MariaDB ใช้ชื่อค่าต่างกันเล็กน้อย

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
expire_logs_days = 7
bind-address = 10.0.0.21

sudo systemctl restart mysql
mysql -e "SHOW VARIABLES LIKE 'log_bin';"
mysql -e "SHOW VARIABLES LIKE 'server_id';"

ขั้นตอนที่ 2: สร้างผู้ใช้สำหรับการจำลอง

sudo mysql

CREATE USER 'repl'@'10.0.0.22' IDENTIFIED BY 'รหัสผ่านที่ยาวและสุ่ม';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.22';
FLUSH PRIVILEGES;
SELECT user, host FROM mysql.user WHERE user = 'repl';

ผู้ใช้นี้ต้องมีสิทธิ์เพียงเท่านี้ อย่าให้สิทธิ์อื่นเพิ่ม และจำกัดโฮสต์ให้ตรงกับ IP ของตัวสำรองเท่านั้น

ขั้นตอนที่ 3: ตั้งค่าเครื่องสำรอง

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

[mysqld]
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
relay_log = /var/log/mysql/relay-bin
bind-address = 10.0.0.22

# ป้องกันการเขียนบนตัวสำรองโดยไม่ตั้งใจ ซึ่งทำให้ข้อมูลสองฝั่งไม่ตรงกัน
read_only = ON
super_read_only = ON

# จำข้อมูลการเชื่อมต่อลงตาราง ทนต่อไฟดับได้ดีกว่าเก็บลงไฟล์
relay_log_recovery = ON

sudo systemctl restart mysql

super_read_only = ON สำคัญมาก มันกันแม้แต่ผู้ใช้ระดับ root ไม่ให้เขียนลงตัวสำรอง ซึ่งเป็นสาเหตุอันดับหนึ่งที่ทำให้การจำลองพังในภายหลัง

ขั้นตอนที่ 4: คัดลอกข้อมูลตั้งต้น

บนเครื่องหลัก ดัมป์ข้อมูลพร้อมข้อมูล GTID

mysqldump --all-databases --single-transaction --routines --triggers --events \
  --source-data=2 --set-gtid-purged=ON \
  | gzip > /tmp/initial.sql.gz

scp /tmp/initial.sql.gz [email protected]:/tmp/

ตัวเลือก --single-transaction ทำให้ดัมป์ได้โดยไม่ล็อกตาราง InnoDB จึงไม่กระทบเว็บที่ใช้งานอยู่ สำหรับฐานข้อมูลขนาดใหญ่มาก พิจารณาใช้ Percona XtraBackup แทนซึ่งเร็วกว่ามาก

บนเครื่องสำรอง นำเข้าข้อมูล

sudo mysql -e "RESET MASTER;"
zcat /tmp/initial.sql.gz | sudo mysql
sudo mysql -e "SELECT @@GLOBAL.gtid_executed;"

ขั้นตอนที่ 5: เริ่มการจำลอง

sudo mysql

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '10.0.0.21',
  SOURCE_PORT = 3306,
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'รหัสผ่านที่ตั้งไว้',
  SOURCE_AUTO_POSITION = 1,
  SOURCE_CONNECT_RETRY = 10,
  SOURCE_RETRY_COUNT = 86400;

START REPLICA;

สำหรับ MariaDB และ MySQL 5.7 ใช้ไวยากรณ์เดิม

CHANGE MASTER TO
  MASTER_HOST = '10.0.0.21',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = 'รหัสผ่าน',
  MASTER_USE_GTID = slave_pos;
START SLAVE;

ขั้นตอนที่ 6: ตรวจสอบสถานะ

sudo mysql -e "SHOW REPLICA STATUS\G" | grep -E 'Replica_IO_Running|Replica_SQL_Running|Seconds_Behind_Source|Last_Error'

สิ่งที่ต้องเห็น

• Replica_IO_Running: Yes เชื่อมต่อกับเครื่องหลักได้
• Replica_SQL_Running: Yes นำข้อมูลที่ได้มาเขียนลงฐานข้อมูลได้
• Seconds_Behind_Source: 0 ตามทันแล้ว
• Last_Error ต้องว่าง

ทดสอบจริงด้วยการเขียนบนเครื่องหลักแล้วอ่านที่ตัวสำรอง

# บนเครื่องหลัก
mysql -e "CREATE DATABASE IF NOT EXISTS repltest; CREATE TABLE repltest.t (id INT); INSERT INTO repltest.t VALUES (1);"

# บนตัวสำรอง
mysql -e "SELECT * FROM repltest.t;"

ขั้นตอนที่ 7: เฝ้าดูความล่าช้า

ความล่าช้าคือความเสี่ยงที่ต้องเฝ้าดูตลอด หากตัวสำรองตามไม่ทันแล้วเครื่องหลักล่ม ข้อมูลช่วงที่ยังตามไม่ทันจะหายไป

watch -n 5 'mysql -e "SHOW REPLICA STATUS\G" | grep Seconds_Behind_Source'

เขียนสคริปต์แจ้งเตือน

#!/bin/bash
LAG=$(mysql -N -e "SHOW REPLICA STATUS\G" | grep Seconds_Behind_Source | awk '{print $2}')
IO=$(mysql -N -e "SHOW REPLICA STATUS\G" | grep Replica_IO_Running | awk '{print $2}')

if [ "$IO" != "Yes" ] || [ "$LAG" = "NULL" ] || [ "$LAG" -gt 300 ]; then
  echo "Replication มีปัญหา: IO=$IO Lag=$LAG" | mail -s "แจ้งเตือน Replication" [email protected]
fi

สาเหตุที่ทำให้ล่าช้าบ่อยที่สุดคือ Query ที่ทำงานนานบนเครื่องหลัก เพราะตัวสำรองต้องทำงานเดียวกันซ้ำและโดยค่าเริ่มต้นทำทีละคำสั่ง แก้ด้วยการเปิดการทำงานขนาน

STOP REPLICA;
SET GLOBAL replica_parallel_workers = 4;
SET GLOBAL replica_parallel_type = 'LOGICAL_CLOCK';
START REPLICA;

ขั้นตอนที่ 8: สลับมาใช้ตัวสำรองเมื่อเครื่องหลักล่ม

# บนตัวสำรอง
STOP REPLICA;
RESET REPLICA ALL;
SET GLOBAL super_read_only = OFF;
SET GLOBAL read_only = OFF;

แล้วชี้แอปพลิเคชันมาที่ IP ของเครื่องนี้ อย่าลืมแก้ read_only ในไฟล์คอนฟิกด้วย ไม่เช่นนั้นเมื่อรีสตาร์ตจะกลับไปอ่านอย่างเดียวอีก

เมื่อเครื่องเดิมกลับมา ให้ตั้งเป็นตัวสำรองของเครื่องใหม่แทน อย่าเปิดให้เขียนได้ทั้งสองเครื่องพร้อมกัน เพราะข้อมูลจะแตกเป็นสองชุดที่รวมกลับไม่ได้

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

Replica_IO_Running: Connecting

เชื่อมต่อไม่ได้ ตรวจสามข้อ เครือข่ายด้วย nc -vz 10.0.0.21 3306 ชื่อผู้ใช้และรหัสผ่าน และ bind-address ของเครื่องหลักว่าไม่ได้ตั้งเป็น 127.0.0.1

Replica_SQL_Running: No พร้อมข้อผิดพลาด Duplicate entry

ข้อมูลสองฝั่งไม่ตรงกัน มักเกิดจากมีคนเขียนลงตัวสำรองโดยตรง วิธีที่ถูกต้องคือสร้างตัวสำรองใหม่จากการดัมป์ล่าสุด การข้ามคำสั่งที่ผิดพลาดด้วย SET GLOBAL sql_slave_skip_counter จะทำให้ข้อมูลต่างกันมากขึ้นเรื่อย ๆ

Seconds_Behind_Source เพิ่มขึ้นเรื่อย ๆ

ตัวสำรองตามไม่ทัน ตรวจว่า CPU และดิสก์ของตัวสำรองไม่ด้อยกว่าเครื่องหลัก เปิดการทำงานขนานตามขั้นตอนที่ 7 และตรวจว่าตารางทุกตารางมี Primary Key เพราะการจำลองแบบ ROW กับตารางที่ไม่มี Primary Key ต้องสแกนทั้งตารางในทุกแถวที่เปลี่ยน

SELECT table_schema, table_name FROM information_schema.tables t
WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('mysql','sys','information_schema','performance_schema')
AND NOT EXISTS (SELECT 1 FROM information_schema.table_constraints c
  WHERE c.table_schema = t.table_schema AND c.table_name = t.table_name
  AND c.constraint_type = 'PRIMARY KEY');

ดิสก์เต็มเพราะ binary log

ตั้ง binlog_expire_logs_seconds ให้เหมาะสม และอย่าลบไฟล์ด้วยมือ ใช้คำสั่ง PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY; ซึ่งจะไม่ลบไฟล์ที่ตัวสำรองยังอ่านไม่เสร็จ

ต้องการวางระบบฐานข้อมูลที่ทนต่อความล้มเหลว ติดต่อ THAI DATA CLOUD ได้ที่ https://thaidata.cloud/contact/