Practical MySQL & MariaDB

คู่มือ mysqldump: วิธีสำรองและกู้คืนฐานข้อมูล MySQL (พร้อมตัวอย่าง)

未分類
目次

1. Introduction

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

2. What Is mysqldump?

2.1 Overview of mysqldump

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

2.2 Common Use Cases

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

3. Basic Usage

3.1 Basic Command Syntax

ไวยากรณ์คำสั่งพื้นฐานสำหรับ mysqldump มีดังนี้

mysqldump -u username -p database_name > output_file.sql
  • -u username : ชื่อผู้ใช้ที่ใช้เข้าถึงฐานข้อมูล
  • -p : ให้ระบบถามรหัสผ่าน
  • database_name : ชื่อฐานข้อมูลที่ต้องการสำรอง
  • > output_file.sql : เส้นทาง/ชื่อไฟล์ปลายทางสำหรับไฟล์ดัมพ์

3.2 User Authentication Options

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

3.3 Example: Back Up an Entire Database

mysqldump -u root -p mydatabase > backup.sql

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

4. Explanation of Key Options

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. Practical Examples

5.1 Dump Only Specific Tables

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

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

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

5.2 Dump Data Only / Schema Only

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

5.3 Conditional Dump

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

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

คำสั่งนี้สำรองข้อมูลเฉพาะที่ created_at อยู่ในหรือหลังวันที่ 1 มกราคม 2023.

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

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

6.1 ไวยากรณ์พื้นฐานของการกู้คืน

mysql -u username -p database_name < dump_file.sql
  • -u username : ชื่อผู้ใช้ที่ใช้เชื่อมต่อกับฐานข้อมูล.
  • -p : ให้คุณใส่รหัสผ่าน.
  • database_name : ชื่อของฐานข้อมูลปลายทาง.
  • < dump_file.sql : ไฟล์ดัมพ์ที่ใช้สำหรับการกู้คืน.

6.2 ตัวอย่าง: เรียกใช้การกู้คืน

mysql -u root -p mydatabase < backup.sql

คำสั่งนี้กู้คืนข้อมูลเข้าสู่ mydatabase จากไฟล์ backup.sql.

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 การจัดเก็บและตรวจสอบการสำรองข้อมูล

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

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

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

  • ข้อผิดพลาด: @@GLOBAL.GTID_PURGED cannot be changed : ข้อผิดพลาดนี้ปรากฏเมื่อมีปัญหาเกี่ยวกับ GTID ใน MySQL 8.0 คุณสามารถหลีกเลี่ยงได้โดยคอมเมนต์การตั้งค่า GTID ด้วยตัวเลือก --set-gtid-purged=COMMENTED 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 และนำไปใช้ในสถานการณ์จริง นอกจากนี้ การทำการสำรองข้อมูลเป็นประจำและตรวจสอบการกู้คืนจะช่วยรักษาความปลอดภัยของฐานข้อมูลและเตรียมพร้อมรับมือกับการสูญเสียข้อมูลที่ไม่คาดคิดได้อย่างมีประสิทธิภาพ.