โลกของ MySQL และ MariaDB

mysqldump วิธีใช้และแนวทางปฏิบัติที่ดีที่สุดสำหรับการสำรองฐานข้อมูล MySQL

การสำรองข้อมูลและการกู้คืนใน MySQL
佐川 直弘 | Naohiro Sagawa
By 佐川 直弘 | Naohiro Sagawa (運営者)
※記事内に広告を含みます。Amazonアソシエイトとして適格販売により収入を得ています。　
目次

วิธีการใช้ mysqldump และแนวทางปฏิบัติที่ดีที่สุด

1. บทนำ

การสำรองและกู้คืนฐานข้อมูลเป็นพื้นฐานของการจัดการข้อมูลและจำเป็นอย่างยิ่งสำหรับการดำเนินการที่น่าเชื่อถือ “mysqldump” ของ MySQL เป็นเครื่องมือที่ใช้กันอย่างแพร่หลายในการสำรองฐานข้อมูลอย่างมีประสิทธิภาพและยืดหยุ่น คู่มือนี้จะอธิบายรายละเอียดตั้งแต่การใช้งานพื้นฐานของ mysqldump ไปจนถึงวิธีการใช้ตัวเลือกขั้นสูง วิธีการกู้คืน และการแก้ไขปัญหา ในตอนท้ายของบทความนี้ เราจะแนะนำแนวทางปฏิบัติที่ดีที่สุดและแหล่งข้อมูลอ้างอิง เพื่อให้คุณสามารถนำไปใช้เป็นข้อมูลอ้างอิงในการทำความเข้าใจ mysqldump ได้อย่างเชี่ยวชาญ

2. mysqldump คืออะไร

2.1 ภาพรวมของ mysqldump

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

2.2 สถานการณ์การใช้งาน

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

3. วิธีการใช้งานพื้นฐาน

3.1 รูปแบบคำสั่งพื้นฐาน

รูปแบบคำสั่งพื้นฐานของ mysqldump มีดังนี้:

mysqldump -u ユーザー名 -p データベース名 > 出力ファイル名.sql
  • -u ชื่อผู้ใช้: ชื่อผู้ใช้สำหรับเข้าถึงฐานข้อมูล
  • -p: แจ้งให้ป้อนรหัสผ่าน
  • ชื่อฐานข้อมูล: ชื่อฐานข้อมูลเป้าหมายสำหรับการสำรอง
  • > ชื่อไฟล์เอาต์พุต.sql: ปลายทางในการบันทึกไฟล์ดัมพ์

3.2 ตัวเลือกการยืนยันตัวตนผู้ใช้

  • -h ชื่อโฮสต์: ชื่อโฮสต์ของเซิร์ฟเวอร์ฐานข้อมูล (ค่าเริ่มต้นคือlocalhost)
  • -P หมายเลขพอร์ต: หมายเลขพอร์ตที่จะเชื่อมต่อ (ค่าเริ่มต้นคือ 3306)

3.3 ตัวอย่าง: การสำรองฐานข้อมูลทั้งหมด

mysqldump -u root -p mydatabase > backup.sql

คำสั่งนี้จะสำรองข้อมูลทั้งหมดของ mydatabase ไปยังไฟล์ backup.sql การใส่ วันที่ ในชื่อไฟล์สำรองจะช่วยให้การติดตามประวัติเวอร์ชันทำได้ง่ายขึ้น

4. คำอธิบายตัวเลือกหลัก

4.1 --all-databases (-A)

ตัวเลือกนี้ใช้สำรองฐานข้อมูลทั้งหมดในคราวเดียว สะดวกเมื่อต้องการสำรองเซิร์ฟเวอร์ทั้งหมด

mysqldump -u root -p --all-databases > all_databases_backup.sql

4.2 --no-data (-d)

ใช้เมื่อต้องการสำรองเฉพาะโครงสร้างตารางและไม่รวมข้อมูล เช่น ใช้เมื่อต้องการนำโครงสร้างตารางไปตั้งค่าในสภาพแวดล้อมการพัฒนา

mysqldump -u root -p mydatabase --no-data > schema_only_backup.sql

4.3 --where (-w)

ใช้เมื่อต้องการสำรองเฉพาะข้อมูลที่ตรงตามเงื่อนไขที่กำหนด ตัวอย่างเช่น หากต้องการสำรองเฉพาะเรคคอร์ดที่คอลัมน์ is_active เป็น 1 ให้ใช้:

mysqldump -u root -p mydatabase --where="is_active=1" > filtered_data_backup.sql

4.4 --ignore-table

ใช้เมื่อต้องการยกเว้นตารางที่เฉพาะเจาะจงออกจากการสำรอง มีประโยชน์เมื่อมีตารางที่ไม่ต้องการสำรอง

mysqldump -u root -p mydatabase --ignore-table=mydatabase.table1 > partial_backup.sql

5. ตัวอย่างการใช้งานจริง

5.1 การดัมพ์เฉพาะตารางที่เฉพาะเจาะจง

หากต้องการสำรองเฉพาะตารางที่เฉพาะเจาะจง ให้ระบุชื่อตารางหลังชื่อฐานข้อมูล

mysqldump -u root -p mydatabase table1 > table1_backup.sql

คำสั่งนี้จะบันทึกเฉพาะข้อมูลของ table1 ลงในไฟล์ table1_backup.sql

5.2 การดัมพ์เฉพาะข้อมูล / เฉพาะโครงสร้าง

  • เฉพาะข้อมูล: mysqldump -u root -p mydatabase --no-create-info > data_only_backup.sql สำรองเฉพาะข้อมูล ไม่รวมโครงสร้างตาราง
  • เฉพาะโครงสร้าง:
    bash mysqldump -u root -p mydatabase --no-data > schema_only_backup.sql
    สำรองเฉพาะโครงสร้างของตาราง

5.3 การดัมพ์แบบมีเงื่อนไข

หากต้องการสำรองเฉพาะข้อมูลที่ตรงกับเงื่อนไขที่เฉพาะเจาะจง ให้ใช้ตัวเลือก --where

mysqldump -u root -p mydatabase --where="created_at >= '2023-01-01'" > recent_data_backup.sql

คำสั่งนี้จะสำรองเฉพาะข้อมูลที่ created_at อยู่ตั้งแต่วันที่ 1 มกราคม 2023 เป็นต้นไป

6. วิธีการกู้คืน (Restore)

ในการกู้คืนฐานข้อมูลที่สำรองด้วย mysqldump ให้ใช้คำสั่ง mysql การกู้คืนคือกระบวนการฟื้นฟูสถานะของฐานข้อมูลโดยใช้ไฟล์สำรอง

6.1 รูปแบบคำสั่งพื้นฐานของการกู้คืน

mysql -u ユーザー名 -p データベース名 < ダンプファイル.sql
  • -u ชื่อผู้ใช้: ชื่อผู้ใช้สำหรับเชื่อมต่อฐานข้อมูล
  • -p: แจ้งให้ป้อนรหัสผ่าน
  • ชื่อฐานข้อมูล: ชื่อฐานข้อมูลปลายทางสำหรับการกู้คืน
  • < ไฟล์ดัมพ์.sql: ไฟล์ดัมพ์ที่ใช้สำหรับการกู้คืน

6.2 ตัวอย่าง: การดำเนินการกู้คืน

mysql -u root -p mydatabase < backup.sql

ด้วยคำสั่งนี้ ข้อมูลจะถูกกู้คืนจากไฟล์ backup.sql ไปยัง mydatabase

6.3 ข้อควรระวังในการกู้คืน

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

7. แนวทางปฏิบัติที่ดีที่สุดสำหรับ mysqldump

7.1 การกำหนดตารางเวลาสำหรับการสำรองข้อมูล

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

#!/bin/bash
mysqldump -u root -p'password' --all-databases > /path/to/backup/all_databases_$(date +%F).sql

7.2 การเข้ารหัสไฟล์สำรอง

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

gpg -c /path/to/backup/all_databases_$(date +%F).sql

7.3 ความเข้ากันได้ระหว่างเวอร์ชัน

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

  1. การกู้คืนโครงสร้างตาราง: mysqldump --all-databases --no-data --routines --events > dump-defs.sql คำสั่งนี้จะดัมพ์เฉพาะโครงสร้างตาราง และกู้คืนในสภาพแวดล้อมเวอร์ชันใหม่เพื่อตรวจสอบความเข้ากันได้
  2. การกู้คืนข้อมูล: mysqldump --all-databases --no-create-info > dump-data.sql เมื่อยืนยันแล้วว่าโครงสร้างตารางเข้ากันได้ ให้กู้คืนเฉพาะข้อมูล
  3. การตรวจสอบในสภาพแวดล้อมการทดสอบ:
    เพื่อยืนยันความเข้ากันได้ระหว่างเวอร์ชัน ให้ทำการสำรองและกู้คืนในสภาพแวดล้อมการทดสอบ และยืนยันว่าทำงานได้อย่างถูกต้อง ก่อนที่จะย้ายไปยังสภาพแวดล้อมจริง

7.4 การจัดเก็บและตรวจสอบการสำรอง

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

8. การแก้ไขปัญหา (Troubleshooting)

8.1 ข้อผิดพลาดที่พบบ่อยและวิธีแก้ไข

  • ข้อผิดพลาด: @@GLOBAL.GTID_PURGED cannot be changed:
    ข้อผิดพลาดนี้จะแสดงขึ้นเมื่อเกิดปัญหาเกี่ยวกับ GTID ใน MySQL 8.0 สามารถหลีกเลี่ยงได้โดยการใช้ตัวเลือก --set-gtid-purged=COMMENTED เพื่อคอมเมนต์การตั้งค่า GTID: mysqldump -u root -p mydatabase --set-gtid-purged=COMMENTED > backup.sql
  • ข้อผิดพลาด: พื้นที่ดิสก์ไม่เพียงพอ:
    หากพื้นที่ดิสก์ไม่เพียงพอสำหรับการสำรองฐานข้อมูลขนาดใหญ่ ให้บีบอัดไฟล์สำรองหรือเปลี่ยนตำแหน่งบันทึก ดังตัวอย่างด้านล่างนี้ คือการบีบอัดและสำรองด้วย gzip: mysqldump -u root -p mydatabase | gzip > backup.sql.gz
  • ข้อผิดพลาด: สิทธิ์ไม่เพียงพอ:
    หากผู้ใช้ฐานข้อมูลไม่มีสิทธิ์เพียงพอ การสำรองหรือกู้คืนจะล้มเหลว ให้กำหนดสิทธิ์ที่จำเป็น (เช่น SELECT, LOCK TABLES, SHOW VIEW เป็นต้น) แล้วลองอีกครั้ง

8.2 ปัญหาความเข้ากันได้ของเวอร์ชัน

ปัญหาความเข้ากันได้ระหว่างเวอร์ชัน MySQL ที่แตกต่างกันสามารถแก้ไขได้ด้วยการทดสอบก่อนที่จะอัปเกรดเวอร์ชัน โดยเฉพาะอย่างยิ่งในการย้ายข้อมูลจาก MySQL 5.7 ไปยัง 8.0 แนะนำให้ใช้ตัวเลือก --no-data เพื่อกู้คืนเฉพาะโครงสร้างตารางและตรวจสอบความเข้ากันได้

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

9. สรุป

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

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

10. แหล่งอ้างอิงและแหล่งข้อมูลเพิ่มเติม

โปรดดูแหล่งข้อมูลนี้เพื่อเรียนรู้เพิ่มเติมเกี่ยวกับ mysqldump และนำไปใช้ในการปฏิบัติจริง นอกจากนี้ การสำรองข้อมูลและการตรวจสอบการกู้คืนอย่างสม่ำเสมอจะช่วยรักษาความปลอดภัยของฐานข้อมูลและเตรียมพร้อมสำหรับกรณีที่ข้อมูลสูญหาย