MySQL เป็นระบบจัดการฐานข้อมูลเชิงสัมพันธ์แบบโอเพ่นซอร์สที่ได้รับความนิยมและถูกใช้อย่างกว้างขวางในองค์กรต่างๆ การสำรองข้อมูล MySQL เพื่อให้มั่นใจในความปลอดภัยของข้อมูลควรทำอย่างไร ที่นี่เรานำเสนอ 5 วิธีให้คุณ
Updated by ซี กันตภณ on 2025/10/23
ตามขนาดของฐานข้อมูล จะมีการสำรองข้อมูลอยู่ 4 ประเภท ซึ่งใช้ในโอกาสที่แตกต่างกันออกไป:
การสำรองข้อมูลแบบเต็มรูปแบบ
นี่คือวิธีทั่วไปที่คนส่วนใหญ่ใช้ สามารถสำรองข้อมูลฐานข้อมูลทั้งหมดได้ รวมถึงออบเจกต์ฐานข้อมูลทั้งหมด เช่น ตารางผู้ใช้ ตารางระบบ ดัชนี มุมมอง และขั้นตอนการจัดเก็บ แต่ใช้เวลานานและพื้นที่มาก ดังนั้นโดยทั่วไปแนะนำให้สำรองข้อมูลแบบเต็มรูปแบบสัปดาห์ละครั้ง
การสำรองข้อมูลบันทึกการทำธุรกรรม
บันทึกธุรกรรมเป็นไฟล์แยกต่างหากที่ใช้จดบันทึกการเปลี่ยนแปลงในฐานข้อมูล การสำรองข้อมูลจำเป็นต้องคัดลอกเฉพาะการเปลี่ยนแปลงที่เกิดกับฐานข้อมูลตั้งแต่การสำรองข้อมูลครั้งก่อน จึงใช้เวลาน้อยมาก เพื่อความทนทานของฐานข้อมูล แนะนำให้สำรองข้อมูลบันทึกธุรกรรมทุกชั่วโมงหรือบ่อยกว่านั้น
การสำรองข้อมูลแบบแตกต่างกัน
เป็นอีกวิธีหนึ่งในการสำรองข้อมูลเพียงบางส่วนของฐานข้อมูล โดยไม่ใช้บันทึกธุรกรรม แต่จะใช้ภาพใหม่ของฐานข้อมูลทั้งหมด ซึ่งมีขนาดเล็กกว่าการสำรองแบบเต็มรูปแบบเดิม เนื่องจากมีเพียงฐานข้อมูลที่เปลี่ยนแปลงไปตั้งแต่การสำรองแบบเต็มรูปแบบครั้งก่อนเท่านั้น มีข้อดีคือจัดเก็บและกู้คืนข้อมูลได้อย่างรวดเร็ว แนะนำให้ทำการสำรองแบบต่างระดับทุกวัน
การสำรองข้อมูลไฟล์
ฐานข้อมูลอาจประกอบด้วยไฟล์หลายไฟล์บนดิสก์ ถ้าฐานข้อมูลมีขนาดใหญ่มากและไม่สามารถสำรองข้อมูลได้ภายในคืนเดียว การสำรองข้อมูลแบบไฟล์สามารถใช้เพื่อสำรองข้อมูลบางส่วนของฐานข้อมูลทุกคืน เนื่องจากโดยทั่วไปฐานข้อมูลจะไม่มีขนาดใหญ่จนต้องใช้ที่เก็บไฟล์หลายแห่ง ดังนั้นการสำรองข้อมูลประเภทนี้จึงไม่ค่อยพบเห็นบ่อยนัก
ตามสถานะของฐานข้อมูล สามารถแบ่งออกเป็นสามประเภท:
1. การสำรองข้อมูลแบบโคลด์
การสำรองข้อมูลแบบโคลด์คือการสำรองข้อมูลในขณะที่ฐานข้อมูลปิดอยู่และผู้ใช้ไม่สามารถเข้าถึงได้ เนื่องจากการสำรองข้อมูลจะทำเมื่อฐานข้อมูลปิดอยู่ จึงมักเรียกการสำรองข้อมูลแบบคูลอีกอย่างว่าการสำรองข้อมูลแบบออฟไลน์ การสำรองข้อมูลแบบคูลมักใช้ทรัพยากรน้อยกว่า และเนื่องจากไม่มีข้อมูลใหม่ใด ๆ เพิ่มเข้ามา (ฐานข้อมูลปิดอยู่) กระบวนการสำรองข้อมูลจึงสามารถสำรองข้อมูลทั้งหมดได้ในครั้งเดียว อย่างไรก็ตาม การสำรองข้อมูลแบบคูลมีข้อจำกัดตรงที่ฐานข้อมูลจะไม่สามารถใช้งานได้ในช่วงเวลาที่กำลังดำเนินการสำรองข้อมูล
2. การสำรองข้อมูลแบบฮอต
การสำรองข้อมูลแบบฮอตคือการสำรองข้อมูลในขณะที่ฐานข้อมูลยังทำงานอยู่และผู้ใช้สามารถเข้าถึงได้ การสำรองข้อมูลแบบร้อนมักเป็นวิธีที่สะดวกมาก เนื่องจากไม่จำเป็นต้องปิดระบบ และผลกระทบต่อการทำงานของระบบปฏิบัติการมีเพียงเล็กน้อย แต่ก็มีข้อเสียอยู่บ้าง เช่น หากข้อมูลที่กำลังทำการสำรองถูกเปลี่ยนแปลงไปในระหว่างที่กำลังดำเนินการสำรอง ข้อมูลสำรองนั้นอาจไม่ตรงกับเวอร์ชันสุดท้ายของข้อมูลในฐานข้อมูล
3.การสำรองข้อมูลแบบลอจิคัล
การสำรองข้อมูลแบบลอจิคัลใช้ซอฟต์แวร์ในการดึงข้อมูลจากฐานข้อมูลและเขียนผลลัพธ์ลงในไฟล์
มีการสำรองข้อมูลเชิงตรรกะและการสำรองข้อมูลทางกายภาพสำหรับ MySQL ตามวิธีการกู้คืนข้อมูล การสำรองข้อมูลเชิงตรรกะคือการสำรองคำสั่ง SQL และเมื่อกู้คืนข้อมูลจะรันคำสั่งเหล่านี้เพื่อสร้างข้อมูลในฐานข้อมูลใหม่อีกครั้ง ส่วนการสำรองข้อมูลทางกายภาพคือการคัดลอกไฟล์ฐานข้อมูลในรูปแบบเดียวกันกับที่จัดเก็บไว้บนดิสก์
การสำรองข้อมูลทั้งสองประเภทนี้มีข้อดีและข้อเสียของตนเอง โดยทั่วไปความเร็วในการกู้คืนข้อมูลของการสำรองข้อมูลเชิงกายภาพจะเร็วกว่า แต่ใช้พื้นที่มากกว่า ในขณะที่ความเร็วของการสำรองข้อมูลเชิงตรรกะจะช้ากว่าแต่ใช้พื้นที่น้อยกว่า
mysqldump เป็นเครื่องมือสำรองข้อมูลที่มาพร้อมกับ MySQL อยู่ภายใต้ไดเรกทอรี bin: /usr/local/mysql/bin/mysqldump ซึ่งรองรับการสำรองข้อมูลแบบ hot backup บน innoDB อย่างไรก็ตาม เนื่องจากเป็นการสำรองข้อมูลแบบ logical ความเร็วจึงไม่สูนัก และเหมาะสมกับสถานการณ์ที่ข้อมูลสำรองมีขนาดค่อนข้างเล็ก การสำรองข้อมูลแบบเต็มรูปแบบด้วย mysqldump บวกกับ binary log สามารถทำให้กู้คืนข้อมูลได้ถึงจุดเวลาหนึ่ง (point-in-time recovery)
สำหรับตารางของเครื่องมือจัดเก็บข้อมูล MyISAM สามารถใช้ได้เฉพาะการสำรองข้อมูลขณะทำงาน (hot backup) เท่านั้น ณ จุดนี้ควรป้องกันการเขียนข้อมูล โดยต้องเพิ่มล็อกอ่านก่อน คุณสามารถเข้าสู่ฐานข้อมูลเพื่อเพิ่มล็อกอ่านด้วยตนเองได้ แต่วิธีนี้ยุ่งยาก ทางเลือกที่ดีกว่าคือใช้ตัวเลือกล็อกในเครื่องมือ mysqldump ซึ่งคือ --lock-all-tables เช่น mysqldump --databases test --lock-all-tables --flush-logs > /tmp/backup_test_`date+%F-%H-%M`.sql
หากคุณกำลังสำรองข้อมูลเพียงตารางเดียว ให้เพิ่มชื่อตารางหลังชื่อไลบรารี test
สำหรับตารางที่ใช้ storage engine แบบ innoDB สามารถสำรองข้อมูลขณะระบบยังทำงานอยู่ได้โดยไม่ต้องล็อกฐานข้อมูล เพิ่มตัวเลือกเพื่อทำการสำรองข้อมูลขณะระบบยังทำงานอยู่: --single-transaction ตัวอย่างเช่น: mysqldump --databases test --single-transaction --flush-logs --master-data=2> /tmp/backup_test_`date+%F-%H-%M`.sql
หมายเหตุ: อย่าลืมปิดบันทึกไบนารีขณะกำลังกู้คืน:
mysql> set sql_log_bin=0;
เนื่องจากวิธีนี้อิงจากการสำรองข้อมูลแบบลอจิก การรัน SQL จะทำการแทรกข้อมูลและบันทึกไว้ในบันทึกไบนารี เนื่องจากเป็นการกู้คืนข้อมูล จึงสามารถปิดการบันทึกไบนารีที่ถูกแทรกได้เพื่อลดเวลาในการกู้คืน
คลิกและเรียนรู้เพิ่มเติมเกี่ยวกับ การสำรองข้อมูลและการกู้คืนฐานข้อมูล MySQL ด้วยหรือไม่ใช่ Mysqldump
ในแง่ของการสำรองข้อมูลทางกายภาพ สามารถทำได้โดยอาศัยระบบไฟล์ (LVM snapshot) นอกจากนี้ยังสามารถใช้คำสั่ง เช่น tar เพื่อจัดเก็บและสำรองข้อมูลทั้งไดเรกทอรีของฐานข้อมูลได้โดยตรง แต่วิธีเหล่านี้สามารถใช้ได้เฉพาะการสำรองข้อมูลแบบคอลด์แบ็คอัพเท่านั้น เครื่องมือจัดเก็บข้อมูลชนิดต่าง ๆ จะสำรองข้อมูลเนื้อหาที่แตกต่างกัน MyISAM จะสำรองข้อมูลอัตโนมัติในระดับตาราง ในขณะที่ InnoDB จะสามารถสำรองข้อมูลทั้งฐานข้อมูลได้ก็ต่อเมื่อไม่ได้เปิดใช้งานพื้นที่ตารางแบบอิสระ
ต่อไปนี้เป็นการแนะนำการใช้งานฟังก์ชันสแนปช็อตของ LVM เพื่อการสำรองข้อมูล
1) เพื่อความปลอดภัย ให้ตั้งค่าล็อกการอ่านบนฐานข้อมูลก่อน
mysql>FLUSH TABLES WITH READ LOCK
2) รีเฟรชบันทึกไบนารีสำหรับการกู้คืนข้อมูล ณ จุดเวลา
mysql>FLUSH LOGS
3) แล้วสร้างไดรฟ์สำรองข้อมูล
lvcreate –L 1G –s –n data-snap –p –r /dev/myvg/mydata
4) ป้อนฐานข้อมูลเพื่อปลดล็อกการอ่าน
UNLOCK TABLES
5) ติดตั้งไดรฟ์ภาพถ่ายสำรองเพื่อการสำรองข้อมูล
mount –r /dev/myvg/data-snap /mnt/snap
6) สุดท้ายให้จัดเก็บและสำรองข้อมูลไฟล์ที่อยู่ภายใต้ /mnt/snap
สำหรับการกู้คืน:
1) ปิด mysqld
2) แล้วสำรองบันทึกไบนารี
3) กู้คืนไฟล์สำรองเดิม
4) จากนั้นกู้คืนข้อมูลไปยังจุดเวลาที่เกิดข้อผิดพลาดโดยใช้บันทึกไบนารี (อย่าลืมปิดการใช้งานบันทึกไบนารีชั่วคราวขณะกู้คืนข้อมูลตามจุดเวลานั้น)
ระหว่างปี 2010 ถึง 2012 ได้มีการติดตั้งฐานข้อมูล MySQL ในสภาพแวดล้อมคลาวด์ของ Amazon โดยพวกเขามอบภาพถ่าย LVM ซึ่งสะดวกและรวดเร็วมาก การกู้คืนข้อมูลด้วยภาพถ่าย LVM นี้ยังรวดเร็วเป็นอย่างยิ่งภายใต้เครือข่าย 10G ของ Amazon
เตรียมสำรองข้อมูลครั้งแรกจากฐานข้อมูล ล็อกตารางทั้งหมดชั่วคราว และเปิดหน้าต่าง 1
mysql> flush tables with read lock
คำสั่งสอบถามสำเร็จ ไม่มีแถวที่ได้รับผลกระทบ (0.00 วินาที)
mysql> show master status
+++++ | File | Position | Binlog_Do_DB |Binlog_Ignore_DB | | mysql-bin.000003 | 194554 | | | 1 row in set (0.00 sec)
หน้าต่างนี้ไม่สามารถออกได้ และต้องคงไว้จนกว่ากระบวนการแพ็คเกจ tar จะเสร็จสิ้น
--Re-open a shell window, go to the data file directory tar package open window 2 [root@myfstv_21_11 data]# tar -zcvf mysqla1.tar.gz mysqla tar -zcvf mysqla1.tar.gz mysqla Compressed Switch to window 1 and execute the unlock command
mysql> unlock tables
Query OK, 0 rows affected (0.00 sec) mysql>
คัดลอกแพ็กเกจ tar ไปยังเซิร์ฟเวอร์คลัง MySQL อีกเครื่อง ทับซ้อนไดเรกทอรีข้อมูล จากนั้นรีสตาร์ทบริการฐานข้อมูล MySQL
รองรับการสำรองข้อมูลแบบฮ็อตทางกายภาพของ innoDB รองรับการสำรองข้อมูลแบบเต็มและแบบเพิ่มเติม และมีความเร็วสูง รองรับการโยกย้ายข้อมูลระหว่างฐานข้อมูลที่แตกต่างกันซึ่งเกิดจาก storage ของ innoDB และรองรับการสำรองข้อมูลและการกู้คืนข้อมูลในโหมดการจำลองซ้ำ เพื่อให้ xTraBackup รองรับการขยายฟังก์ชันเพิ่มเติม คุณสามารถตั้งค่าพื้นที่ตารางอิสระและเปิดใช้งานฟังก์ชัน innoDB_file_per_table จากนั้นจะสามารถรองรับการสำรองข้อมูลตารางแต่ละตารางได้
หลักการสำรองข้อมูล xTraBackup
xTraBackup ขึ้นอยู่กับฟังก์ชันการกู้คืนจากความผิดพลาดของ innoDB โดยจะทำการคัดลอกไฟล์ข้อมูลของ innoDB เนื่องจากตารางไม่ถูกล็อก ข้อมูลที่คัดลอกมาอาจไม่สอดคล้องกัน แต่เมื่อกู้คืนข้อมูล ใช้กระบวนการกู้คืนจากความผิดพลาดเพื่อทำให้ข้อมูลสอดคล้องกัน
innoDB จัดการบันทึก redo หรือที่เรียกว่าบันทึกธุรกรรม ซึ่งมีการเปลี่ยนแปลงข้อมูล innoDB ทั้งหมด เมื่อ innoDB เริ่มทำงาน มันจะตรวจสอบไฟล์ข้อมูลและบันทึกธุรกรรมก่อน และจะดำเนินการตามขั้นตอนต่อไปนี้:
เมื่อ xTraBackup กำลังสำรองข้อมูล มันจะคัดลอกหน้าข้อมูล innoDB ทีละหน้าโดยไม่ต้องล็อกตาราง ในเวลาเดียวกัน xTraBackup จะมีเธรดอีกตัวหนึ่งคอยตรวจสอบบันทึกธุรกรรม ทันทีที่มีการเปลี่ยนแปลงในบันทึก มันจะทำการคัดลอกหน้าบันทึกที่เปลี่ยนแปลงออกไป เนื่องจากขนาดของไฟล์บันทึกธุรกรรมมีจำกัด เมื่อเต็มแล้วมันจะเริ่มเขียนใหม่ตั้งแต่ต้น ทำให้ข้อมูลใหม่อาจทับข้อมูลเก่าได้
ในระหว่างกระบวนการจัดเตรียม xTraBackup จะใช้บันทึกการทำธุรกรรมที่ถูกคัดลอกมาเพื่อดำเนินการกู้คืนเมื่อเกิดข้อผิดพลาดบนไฟล์ข้อมูล innoDB ที่สำรองไว้
Vinchin Backup & Recovery มีฟังก์ชันที่ทรงพลังในการปกป้องฐานข้อมูลของคุณทั้งในเครื่องเสมือนและเซิร์ฟเวอร์จริง โดยทำงานร่วมกับการสำรองข้อมูลระดับ VM ได้อย่างมีประสิทธิภาพ ทำให้ผู้ใช้งานสภาพแวดล้อมเสมือนมีความมั่นใจเพิ่มขึ้นในการปกป้องข้อมูลธุรกิจสำคัญและระบบสารสนเทศ Vinchin Backup & Recovery รองรับการปกป้อง Oracle DB, MySQL, SQL Server, PostgreSQL, Postgres Pro และ MariaDB ที่ติดตั้งบนทั้งเครื่องจริงและเครื่องเสมือน ด้วยฟีเจอร์การสำรองและการกู้คืนฐานข้อมูลที่ทรงพลัง นอกจากนี้ยังมีกลยุทธ์การสำรองข้อมูลแบบเต็มรูปแบบ การสำรองข้อมูลเชิงอนุพันธ์ การสำรองข้อมูลแบบเพิ่มเติม และการสำรองบันทึกการทำธุรกรรม เพื่อให้คุณสามารถกำหนดแผนการสำรองข้อมูลได้ตามต้องการ
Vinchin Backup & Recovery รองรับการสำรองข้อมูลแบบฮ็อตอย่างมีประสิทธิภาพสำหรับ MySQL, SQL Server และ Oracle โดยไม่กระทบต่อการทำงานปกติของฐานข้อมูล
การดำเนินงานของ Vinchin Backup & Recovery นั้นง่ายมาก เพียงไม่กี่ขั้นตอนง่ายๆ เท่านั้น
1.เลือกวัตถุสำรองข้อมูล
2. เลือกปลายทางสำรองข้อมูล
3. กำหนดกลยุทธ์การสำรองข้อมูล
4.ตรวจสอบและส่งงาน
คุณสามารถเริ่มใช้ระบบที่ทรงพลังนี้ได้ด้วย ทดลองใช้งานฟรีเต็มรูปแบบ 60 วัน เพียงคลิกที่ปุ่มเพื่อรับแพ็กเกจการติดตั้ง คุณสามารถคลิกที่นี่เพื่อเรียนรู้เพิ่มเติมเกี่ยวกับ วิธีการสำรองข้อมูล MySQL ด้วย Vinchin Backup & Recovery
ฐานข้อมูล MySQL ถูกใช้อย่างแพร่หลายในหลายอุตสาหกรรม เพื่อให้มั่นใจในความปลอดภัย เราจึงขอแบ่งปันวิธีการสำรองข้อมูล MySQL ทั้งหมด 5 วิธีที่แตกต่างกันในบทความนี้ คุณสามารถเลือกวิธีที่เหมาะสมกับสถานการณ์จริงของคุณได้ รออะไรอีก? รีบดำเนินการสำรองฐานข้อมูล MySQL ของคุณเดี๋ยวนี้!
แชร์บน:
