คู่มือ MySQL Replication: ตั้งค่า, แก้ปัญหา, จัดการ

1. MySQL Replication คืออะไร? ภาพรวมและการใช้งาน

ภาพรวมของ MySQL Replication

การใช้งาน MySQL Replication

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

ประเภทของ Replication

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

2. แนวคิดพื้นฐานของการทำสำเนา MySQL

บทบาทของมาสเตอร์และสเลฟ

บันทึกไบนารีและบันทึกรีเลย์

1. บันทึกไบนารี（Binary Log）

• บันทึกไบนารีเป็นบันทึกการอัปเดตข้อมูลบนเซิร์ฟเวอร์มาสเตอร์ (เช่น INSERT、UPDATE、DELETE) ซึ่งทำให้เซิร์ฟเวอร์สเลฟสามารถรักษาสถานะข้อมูลเช่นเดียวกับมาสเตอร์ได้。

1. บันทึกรีเลย์（Relay Log）

• บันทึกรีเลย์เป็นบันทึกที่เซิร์ฟเวอร์สเลฟบันทึกบันทึกไบนารีที่ได้รับจากมาสเตอร์ไว้ในระบบของตนเอง เธรด SQL ของสเลฟจะดำเนินการบันทึกรีเลย์นี้ตามลำดับเพื่อสะท้อนการเปลี่ยนแปลงของข้อมูล。

GTID（Global Transaction ID） คืออะไร

ข้อดีของ GTID

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

3. ขั้นตอนการตั้งค่า MySQL Replication

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

1. แก้ไขไฟล์ตั้งค่า

• เพิ่มการตั้งค่าดังต่อไปนี้ในส่วน [mysqld] และตั้งค่า Server ID เป็นค่าที่ไม่ซ้ำ (เช่น 1)

   [mysqld]
   server-id=1
   log-bin=mysql-bin

• server-id ต้องระบุหมายเลขที่ไม่ซ้ำกันสำหรับแต่ละเซิร์ฟเวอร์ และ log-bin หมายถึงการเปิดใช้งาน binary log

1. สร้างผู้ใช้สำหรับ Replication

• สร้างผู้ใช้เฉพาะสำหรับ Replication บนเซิร์ฟเวอร์ Master และมอบสิทธิ์ที่จำเป็น

   CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
   GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
   FLUSH PRIVILEGES;

• ผู้ใช้นี้จำเป็นสำหรับให้เซิร์ฟเวอร์ Slave เข้าถึงข้อมูลของ Master

1. ตรวจสอบสถานะของ Master

• ตรวจสอบไฟล์ binary log ปัจจุบันและตำแหน่ง (position) ของมัน ข้อมูลนี้จำเป็นสำหรับการตั้งค่าเซิร์ฟเวอร์ Slave

   SHOW MASTER STATUS;

• ค่า File (ชื่อไฟล์ log) และ Position (ตำแหน่ง) ที่แสดงจากคำสั่งนี้จะใช้ในการตั้งค่า Slave

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

1. แก้ไขไฟล์ตั้งค่า

• เซิร์ฟเวอร์ Slave ก็ต้องตั้งค่า server-id ให้เป็นค่าที่ไม่ซ้ำ (เช่น 2) โดยต้องแตกต่างจาก Server ID ของ Master

   [mysqld]
   server-id=2

• เพื่อป้องกันการเขียนข้อมูลบน Slave มักตั้งค่า read_only=ON ด้วย

1. ตั้งค่าข้อมูล Master บน Slave

• บนเซิร์ฟเวอร์ Slave ให้รันคำสั่งต่อไปนี้เพื่อระบุ hostname ของ Master, ผู้ใช้, ชื่อไฟล์ binary log และตำแหน่ง

   CHANGE MASTER TO
       MASTER_HOST='master_host',
       MASTER_USER='repl',
       MASTER_PASSWORD='password',
       MASTER_LOG_FILE='mysql-bin.000001',
       MASTER_LOG_POS=123;

• ใส่ค่า MASTER_LOG_FILE และ MASTER_LOG_POS ที่ได้จากการตรวจสอบบน Master ก่อนหน้านี้

1. เริ่มต้น Replication

• บนเซิร์ฟเวอร์ Slave ให้รันคำสั่งต่อไปนี้เพื่อเริ่ม Replication

   START SLAVE;

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

• ตรวจสอบสถานะของ Master

  SHOW MASTER STATUS;

• ตรวจสอบสถานะของ Slave

  SHOW SLAVE STATUSG;

• หากค่า Slave_IO_Running และ Slave_SQL_Running แสดงเป็น Yes แสดงว่า Replication ทำงานอย่างปกติ

4. ประเภทและการประยุกต์ใช้การทำสำเนา

ความแตกต่างระหว่างการทำสำเนาแบบอะซิงโครนัสและการทำสำเนาแบบกึ่งซิงโครนัส

1. การทำสำเนาแบบอะซิงโครนัส

2. การทำสำเนาแบบกึ่งซิงโครนัส

การทำสำเนาโดยใช้ GTID

ข้อดีของ GTID

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

การตั้งค่า GTID Replication

[mysqld]
server-id=1
log-bin=mysql-bin
gtid_mode=ON
enforce_gtid_consistency=ON

[mysqld]
server-id=2
gtid_mode=ON
enforce_gtid_consistency=ON
read_only=ON

5. การบำรุงรักษาและการตรวจสอบการทำสำเนา

วิธีตรวจสอบสถานะการทำสำเนา

ตรวจสอบสถานะของมาสเตอร์

SHOW MASTER STATUS;

• File：ชื่อไฟล์บันทึกไบนารีปัจจุบันที่มาสกำลังเขียน
• Position：ตำแหน่งปัจจุบันในบันทึกไบนารี
• Binlog_Do_DB และ Binlog_Ignore_DB：ฐานข้อมูลที่เป็นเป้าหมายของการทำสำเนา

ตรวจสอบสถานะของสเลฟ

SHOW SLAVE STATUSG;

• Slave_IO_Running และ Slave_SQL_Running：หากทั้งสองเป็น Yes แสดงว่าสเลฟทำงานอย่างปกติ
• Seconds_Behind_Master：แสดงจำนวนวินาทีที่สเลฟล่าช้ากว่ามาสเตอร์ โดยค่าที่เป็น 0 ถือเป็นสภาพที่ดีที่สุด

การแก้ไขปัญหาการทำสำเนา

1. ข้อผิดพลาดการเชื่อมต่อ

• ตรวจสอบชื่อโฮสต์หรือที่อยู่ IP ของเซิร์ฟเวอร์มาสเตอร์：ยืนยันว่าที่อยู่ของมาสเตอร์ถูกต้อง
• ตรวจสอบการตั้งค่าไฟร์วอลล์：ยืนยันว่าพอร์ตที่จำเป็น (โดยทั่วไปคือ 3306) เปิดอยู่

2. ความไม่สอดคล้องของข้อมูล

STOP SLAVE;
# หลังการแก้ไขให้เริ่มใหม่
START SLAVE;

3. การแก้ไขความล่าช้า

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

1. Slave_IO_Running ที่หยุดทำงาน

• ปัญหาเครือข่าย：หากการเชื่อมต่อเครือข่ายมีปัญหา สเลฟจะไม่สามารถเข้าถึงมาสเตอร์ได้ ตรวจสอบการตั้งค่าไฟร์วอลล์และยืนยันว่าสามารถเข้าถึงมาสเตอร์ได้。
• การตั้งค่าชื่อโฮสต์หรือ IP ของมาสเตอร์ผิดพลาด：กรุณาตรวจสอบว่าชื่อโฮสต์หรือ IP ที่ระบุใน CHANGE MASTER TO ถูกต้องหรือไม่。
• ปัญหาสิทธิ์ผู้ใช้：หากผู้ใช้ replication ที่ตั้งค่าบนมาสเตอร์ไม่มีสิทธิ์เพียงพอ การเชื่อมต่อจะล้มเหลว ตรวจสอบว่ามีการให้สิทธิ์ที่ถูกต้องด้วย GRANT REPLICATION SLAVE

2. ข้อมูลไม่สอดคล้องของสเลฟ

• การแก้ไขข้อมูลด้วยตนเอง：หากเกิดความไม่สอดคล้อง ให้หยุดสเลฟและแก้ไขธุรกรรมที่มีปัญหาโดยมือ หลังจากแก้ไขแล้วเริ่มสเลฟใหม่เพื่อคืนการทำงานของ replication ให้เป็นปกติ STOP SLAVE; # แก้ไขข้อมูลตามต้องการ START SLAVE;
• การซิงโครไนซ์ข้อมูลใหม่：หากเกิดความไม่สอดคล้องในระดับใหญ่ ให้ทำการสำรองข้อมูลเต็มจากมาสเตอร์แล้วซิงโครไนซ์ใหม่กับสเลฟเพื่อแก้ไข

3. ความล่าช้าของการทำซ้ำ

• ประสิทธิภาพฮาร์ดแวร์ของสเลฟ：หากสเปคของเซิร์ฟเวอร์สเลฟประมวลผลอาจไม่ทันทำให้เกิดความล่าช้า การอัปเกรดฮาร์ดแวร์เป็นวิธีที่ได้ผล
• การปรับแต่งคิวรี：หากคิวรีที่ส่งจากมาสเตอร์ใช้เวลานานในการดำเนินการบนสเลฟ จะทำให้เกิดความล่าช้า การเพิ่มดัชนีและปรับแต่งคิวรีจะช่วยลดเวลาในการประมวลผล

4. ข้อผิดพลาดสิทธิ์ของผู้ใช้ replication

• การให้สิทธิ์ใหม่โดยการตั้งค่าใหม่：ตรวจสอบว่ามีผู้ใช้ที่มีสิทธิ์เหมาะสมบนมาสเตอร์หรือไม่ และหากจำเป็นให้ทำการตั้งค่าใหม่ GRANT REPLICATION SLAVE ON *.* TO 'repl'@'IPของสเลฟ'; FLUSH PRIVILEGES;

5. การขยายใหญ่ของบันทึกไบนารี

• การหมุนเวียน Binary Log：การลบหรือเก็บถาวร Binary Log อย่างสม่ำเสมอจะช่วยป้องกันการขยายใหญ่ การตั้งค่า expire_logs_days จะทำให้ลบล็อกที่เกินระยะเวลาที่กำหนดโดยอัตโนมัติ SET GLOBAL expire_logs_days = 7; # ลบล็อกที่เกิน 7 วัน

7. สรุป

การทบทวนประเด็นสำคัญ

1. ประเภทและการเลือก Replication

• การทำ Replication แบบอะซิงโครนัสมีความเร็วในการตอบสนองสูงและเหมาะสำหรับการกระจายภาระงาน แต่หากต้องการความน่าเชื่อถือ ควรใช้ Replication แบบกึ่งซิงโครนัส เลือกวิธีที่เหมาะสมตามความต้องการของระบบ

1. การใช้ประโยชน์จาก GTID

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

1. การตรวจสอบสถานะเป็นประจำ

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

1. การเรียนรู้การแก้ไขปัญหาทั่วไป

• การเชื่อมต่อสลาวผิดพลาด, ข้อมูลไม่สอดคล้อง, ความล่าช้า ฯลฯ เป็นปัญหาที่มักเกิดขึ้นกับ MySQL Replication การเข้าใจวิธีแก้ไขพื้นฐานสำหรับแต่ละปัญหาจะทำให้การจัดการปัญหาในระหว่างการดำเนินงานเป็นไปอย่างราบรื่น

1. การจัดการบันทึกไบนารี

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