ในการบำรุงรักษาระบบประจำวัน การส่งออกข้อมูลเป็นสิ่งที่สำคัญ และ mysqldump เป็นเครื่องมือที่ใช้กันบ่อยสำหรับการส่งออกข้อมูล เครื่องมือนี้มาพร้อมพารามิเตอร์ฟังก์ชันในตัวจำนวนมาก บทความนี้จะจัดรายการการดำเนินการที่ใช้บ่อยบางอย่าง
mysqldump คืออะไร
mysqldump เป็นโปรแกรมลูกค้าของ MySQL ที่ใช้สำหรับถ่ายโอนฐานข้อมูล จะสร้างชุดคำสั่ง SQL ที่สามารถบรรจุลงในไฟล์ได้ โดยไฟล์นี้จะมีคำสั่ง SQL ทั้งหมดที่จำเป็นต้องใช้ในการสร้างฐานข้อมูลของคุณขึ้นมาใหม่ เช่น CREATE DATABASE, CREATE TABLE, INSERT เป็นต้น ซึ่งสามารถใช้สำหรับการย้ายหรือกู้คืนฐานข้อมูลอย่างรวดเร็วและเบาะๆ เป็นวิธีหนึ่งที่ใช้สำหรับการสำรองข้อมูลเชิงตรรกะในฐานข้อมูล MySQL
วิธีส่งออกฐานข้อมูล MySQL โดยใช้ mysqldump
1. ส่งออกฐานข้อมูลทั้งหมด
คำสั่งนี้จะส่งออกฐานข้อมูลทั้งหมด รวมถึงฐานข้อมูลระบบด้วย
mysqldump -uroot -proot --all-databases > /tmp/all.sql
2. ส่งออกข้อมูลทั้งหมดจากฐานข้อมูล db1 และ db2
mysqldump -uroot -proot --databases db1 db2 > /tmp/user.sql
ส่งออกฐานข้อมูลหลายรายการ ทุกพารามิเตอร์ที่อยู่หลังตัวเลือกจะถือว่าเป็นชื่อฐานข้อมูล
เพิ่มคำสั่ง DROP DATABASE ก่อนการสร้างฐานข้อมูลแต่ละรายการ
mysqldump -uroot -p --databases test --add-drop-database
3. ส่งออกตาราง a1 และ a2 จาก db1
หมายเหตุ การส่งออกตารางที่ระบุสามารถทำได้เฉพาะฐานข้อมูลเดียวเท่านั้น และเนื้อหาที่ส่งออกไปจะแตกต่างจากการส่งออกฐานข้อมูล เนื้อหาข้อความที่ส่งออกไปสำหรับตารางที่ระบุจะไม่รวมคำสั่งสร้างฐานข้อมูล จะรวมเฉพาะคำสั่ง DROP TABLE, CREATE TABLE, และ INSERT เท่านั้น
mysqldump -uroot -proot --databases db1 --tables a1 a2 > /tmp/db1.sql
แทนที่พารามิเตอร์ --databases และระบุตารางที่ต้องการส่งออก
mysqldump -uroot -p --host=localhost --databases test --tables test
ส่งออก tablespace ทั้งหมด
mysqldump -uroot -p --databases test --all-tablespaces
ไม่รวมข้อมูล tablespace ทั้งหมดจากการส่งออก
mysqldump -uroot -p --databases test --no-tablespaces
เพิ่มคำสั่ง DROP TABLE ก่อนสร้างแต่ละตาราง (เปิดใช้งานโดยค่าเริ่มต้น ใช้ --skip-add-drop-table เพื่อปิดการใช้งาน)
mysqldump -uroot -p --databases test
mysqldump -uroot -p --databases test --skip-add-drop-table
4. การส่งออกแบบมีเงื่อนไข: ส่งออกบันทึกที่มี id=1 จากตาราง a1 ใน db1
สามารถส่งออกแบบมีเงื่อนไขได้เฉพาะกับตารางเดียวเท่านั้น
mysqldump -uroot -proot --databases db1 --tables a1 --where='id=1' > /tmp/a1.sql
ดัมป์เฉพาะระเบียนที่ถูกเลือกโดยเงื่อนไข WHERE ที่กำหนด หากเงื่อนไขมีตัวแปลคำสั่ง ช่องว่าง หรืออักขระพิเศษ จะต้องอยู่ในเครื่องหมายคำพูด
mysqldump -uroot -p --host=localhost --databases db1 --where="user='root'"
5. ส่งออกรูปแบบตารางเท่านั้น โดยไม่รวมข้อมูล
mysqldump -uroot -proot --no-data --databases db1 > /tmp/db1.sql
ส่งออกข้อมูลเท่านั้น โดยไม่เพิ่มคำสั่ง CREATE DATABASE
mysqldump -uroot -p --host=localhost --databases db1 --no-create-db
ส่งออกข้อมูลเท่านั้น โดยไม่เพิ่มคำสั่ง CREATE TABLE
mysqldump -uroot -p --host=localhost --databases db1 --no-create-info
ยกเว้นตารางเฉพาะ ในการเพิกเฉยตารางหลายตาราง ให้ทำซ้ำตัวเลือกสำหรับแต่ละตาราง แต่ละตารางต้องระบุชื่อฐานข้อมูลและชื่อตาราง เช่น --ignore-table=database.table1 --ignore-table=database.table2 ...
mysqldump -uroot -p --host=localhost --databases db1 --ignore-table=mysql.user
ใช้โหมดการแทรกแบบล่าช้า (INSERT DELAYED) เมื่อส่งออกข้อมูล
mysqldump -uroot -p --databases db1 --delayed-insert
ใช้คำสั่ง INSERT พร้อมข้อความ VALUES หลายรายการ สิ่งนี้จะช่วยลดขนาดไฟล์ที่ส่งออกและเร่งความเร็วในการนำเข้า เปิดใช้งานโดยค่าเริ่มต้น ใช้ --skip-extended-insert เพื่อปิดการใช้งาน
mysqldump -uroot -p --databases db1
mysqldump -uroot -p --databases db1 --skip-extended-insert
6. ส่งออกและนำเข้าข้อมูลระหว่างเซิร์ฟเวอร์
mysqldump --host=h1 -uroot -proot --databases db1 | mysql --host=h2 -uroot -proot db2
หมายความว่า การส่งออกข้อมูลทั้งหมดจาก db1 บนเซิร์ฟเวอร์ h1 และนำเข้าไปยัง db2 บน h2 ต้องมีฐานข้อมูล db2 อยู่ก่อนแล้ว มิฉะนั้นจะเกิดข้อผิดพลาด
ใช้ named pipe เพื่อเชื่อมต่อกับ MySQL
mysqldump -uroot -p --host=localhost --all-databases --pipe
mysqldump -uroot -p --host=localhost --all-databases --protocol=tcp
เปิดใช้งานการบีบอัดสำหรับการสื่อสารระหว่างไคลเอนต์และเซิร์ฟเวอร์
mysqldump -uroot -p --all-databases --compress
7. เพิ่มตำแหน่ง binlog และชื่อไฟล์ของฐานข้อมูลหลักในไฟล์ส่งออก
ตัวเลือกนี้ เมื่อรันบนเซิร์ฟเวอร์สลีฟ จะเทียบเท่ากับการดำเนินการ SHOW SLAVE STATUS
การตั้งค่าเป็น 1 จะแสดงผลคำสั่ง CHANGE MASTER ในไฟล์ข้อมูล
การตั้งค่าเป็น 2 จะเพิ่มความคิดเห็นก่อน CHANGE MASTER
ตัวเลือกนี้จะเปิดใช้งาน --lock-all-tables เว้นแต่จะระบุ --single-transaction
หลังจากดำเนินการแล้ว --lock-tables จะถูกปิดใช้งานโดยอัตโนมัติ ค่าเริ่มต้นสำหรับ --dump-slave คือ 1
mysqldump -uroot -proot --dump-slave=1 --database db1 > /tmp/db1.sql
mysqldump -uroot -proot --dump-slave=2 --database db1 > /tmp/db1.sql
ส่งออกเหตุการณ์
mysqldump -uroot -p --all-databases --events
8. ล้างบันทึกก่อนส่งออก
หมายเหตุ: หากส่งออกฐานข้อมูลหลายรายการ (--databases หรือ --all-databases) การล้างบันทึกจะดำเนินการต่อหนึ่งฐานข้อมูล หากใช้ --lock-all-tables หรือ --master-data การล้างบันทึกจะทำเพียงครั้งเดียวโดยล็อกตารางทั้งหมด เพื่อส่งออกและล้างบันทึกพร้อมกัน ให้ใช้ --lock-all-tables หรือ --master-data ร่วมกับ --flush-logs
mysqldump -uroot -p --all-databases --flush-logs
9. ตรวจสอบให้แน่ใจว่าสถานะมีความต่อเนื่องระหว่างการส่งออก
ก่อนการส่งออกข้อมูล ตัวเลือกนี้จะออกคำสั่ง BEGIN ของ SQL BEGIN จะไม่ปิดกั้นแอปพลิเคชันใด ๆ และรับประกันความสอดคล้องของฐานข้อมูลในระหว่างการส่งออก ใช้ได้เฉพาะกับเครื่องมือจัดเก็บแบบหลายเวอร์ชัน (เช่น InnoDB) เท่านั้น ตัวเลือกนี้ขัดแย้งกับ --lock-tables เพราะ LOCK TABLES จะยืนยันการทำธุรกรรมที่ค้างอยู่โดยนัย สำหรับการส่งออกตารางขนาดใหญ่ ให้ใช้ร่วมกับตัวเลือก --quick
mysqldump -uroot -p --host=localhost --all-databases --single-transaction
10. ส่งออกขั้นตอนการจัดเก็บและฟังก์ชัน
mysqldump -uroot -p --host=localhost --all-databases --routines
อีกวิธีหนึ่ง: ใช้ SELECT...INTO OUTFILE เพื่อส่งออกข้อมูล MySQL
SELECT...INTO OUTFILE เป็นคำสั่งซินแท็กซ์ของ MySQL ที่ใช้สำหรับส่งออกผลลัพธ์การค้นหาไปยังไฟล์ โดยใน MySQL คุณสามารถใช้คำสั่งนี้เพื่อส่งออกข้อมูลไปยังไฟล์ข้อความได้อย่างง่ายดาย
การใช้งานพื้นฐาน:
SELECT column1, column2, ...
INTO OUTFILE 'file_path'
FROM your_table
WHERE your_conditions;
ตัวอย่าง:
SELECT id, name, email
INTO OUTFILE '/tmp/user_data.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM users;
ในคำสั่ง SQL ข้างต้น เราได้เลือกคอลัมน์ id, name และ email จากตาราง users และเขียนผลลัพธ์ลงในไฟล์ /tmp/user_data.csv โดย FIELDS TERMINATED BY ',' ระบุตัวคั่นคอลัมน์ (เครื่องหมายจุลภาค) และ LINES TERMINATED BY '\n' ระบุตัวคั่นแถว (บรรทัดใหม่)
หมายเหตุสำคัญ:
การดำเนินการ SELECT...INTO OUTFILE ต้องมีสิทธิ์ที่เหมาะสม และไดเรกทอรีของไฟล์ผลลัพธ์จะต้องเป็นตำแหน่งที่เซิร์ฟเวอร์ MySQL สามารถเขียนไฟล์ได้
ในตัวอย่างต่อไปนี้ เราจะส่งออกข้อมูลจากตาราง runoob_tbl ไปยังไฟล์ /tmp/runoob.txt:
mysql> SELECT * FROM runoob_tbl
-> INTO OUTFILE '/tmp/runoob.txt';
คุณสามารถระบุรูปแบบเอาต์พุตโดยใช้ตัวเลือกคำสั่ง ตัวอย่างต่อไปนี้ส่งออกข้อมูลในรูปแบบ CSV:
mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/runoob.txt'
-> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
-> LINES TERMINATED BY '\r\n';
คุณสมบัติของ SELECT ... INTO OUTFILE
LOAD DATA INFILE เป็นการทำงานที่ตรงข้ามกับ SELECT ... INTO OUTFILE หากต้องการเขียนข้อมูลจากฐานข้อมูลไปยังไฟล์ ให้ใช้ SELECT ... INTO OUTFILE และหากต้องการอ่านไฟล์กลับเข้าสู่ฐานข้อมูล ให้ใช้ LOAD DATA INFILE
คำสั่ง SELECT ในรูปแบบ SELECT...INTO OUTFILE 'file_name' จะเขียนแถวที่ถูกเลือกไปยังไฟล์ เนื่องจากไฟล์จะถูกสร้างบนโฮสต์เซิร์ฟเวอร์ คุณจึงต้องมีสิทธิ์ FILE เพื่อใช้ไวยากรณ์นี้
ไฟล์ผลลัพธ์ต้องไม่มีอยู่ก่อน เพื่อป้องกันการเปลี่ยนแปลงข้อมูล
คุณต้องมีบัญชีที่สามารถเข้าสู่ระบบเซิร์ฟเวอร์ได้เพื่อดึงไฟล์นี้ มิฉะนั้นคำสั่ง SELECT ... INTO OUTFILE จะไม่ทำงาน
ในระบบยูนิกซ์ หลังจากที่ไฟล์ถูกสร้างขึ้นแล้ว ไฟล์จะสามารถอ่านได้ และสิทธิ์การเข้าถึงจะเป็นของเซิร์ฟเวอร์ MySQL ซึ่งหมายความว่า แม้คุณจะสามารถอ่านไฟล์ได้ แต่คุณอาจไม่สามารถลบไฟล์นั้นได้
การป้องกันข้อมูลสูงสุดสำหรับระบบนิเวศฐานข้อมูลของคุณ
ในยุคที่ความยืดหยุ่นของข้อมูลมีความสำคัญ การเลือกวิธีการสำรองข้อมูลที่มีความสามารถในการปรับขนาดระดับโลกและรองรับหลายคลาวด์จึงเป็นกุญแจสำคัญ Vinchin Backup & Recovery โดดเด่นด้วยการปกป้อง MySQL ที่แข็งแกร่ง ซึ่งออกแบบมาโดยเฉพาะสำหรับองค์กรข้ามชาติ:
✅ กลยุทธ์การสำรองข้อมูลอย่างครอบคลุม
รองรับกลยุทธ์การสำรองข้อมูลแบบฮ็อตแบ็กอัพ แบ็กอัพเต็ม แบ็กอัพเพิ่มเติม และการสำรองบันทึกธุรกรรม
✅ การรองรับฐานข้อมูลแบบสากล
รวมการป้องกันไว้ด้วยกันสำหรับ MySQL, Oracle, SQL Server และอื่น ๆ ในสภาพแวดล้อมแบบไฮบริด (กายภาพ/คลาวด์/VM)
ทำให้การจัดการง่ายขึ้นด้วยคอนโซลเดียวสำหรับนโยบายการสำรองข้อมูลฐานข้อมูลหลายตัว
✅ การเสริมความแข็งแกร่งด้านการป้องกันแรนซัมแวร์
การจัดเก็บข้อมูลสำรองแบบเข้ารหัส + คุณสมบัติป้องกันแรนซัมแวร์เพื่อต่อต้านการโจมตีจากภัยคุกคาม
✅ การสนับสนุนระดับโลก ประสิทธิภาพในท้องถิ่น
บริการช่วยเหลือด้านเทคนิคตลอด 24 ชั่วโมงทุกวัน และการให้สิทธิ์ใช้งานที่ประหยัดต้นทุน เหมาะอย่างยิ่งสำหรับวิสาหกิจขนาดกลางและขนาดย่อม รวมถึงทีมงานที่กระจายตัว
ในการสร้างงานสำรองข้อมูลฐานข้อมูล MySQL ด้วย Vinchin โปรดไปที่หน้า การสำรองข้อมูลแบบฟิสิกส์ > การสำรองข้อมูลฐานข้อมูล > การสำรองข้อมูล
1. เลือกฐานข้อมูลที่ต้องการสำรองข้อมูล
2. เลือกโหนดสำรองที่คุณต้องการให้ประมวลผลและจัดเก็บข้อมูลสำรอง
3. กำหนดกลยุทธ์การสำรองข้อมูลตามความต้องการของคุณ
4. ตรวจสอบและยืนยันการตั้งค่า
Vinchin เป็นที่ไว้วางใจของสถาบันการเงินและโครงสร้างพื้นฐานไอทีข้ามชาติในกว่า 60 ประเทศ คลิกปุ่มด้านล่างเพื่อลองใช้งาน ทดลองใช้ฟรี 60 วัน จาก Vinchin เพื่อสัมผัสประสบการณ์โซลูชันสำรองข้อมูลที่มีประสิทธิภาพและเชื่อถือได้
สรุป
ในบทความนี้ เราได้สำรวจตัวเลือกต่างๆ ของ mysqldump สำหรับการส่งออกข้อมูลฐานข้อมูล MySQL อย่างมีประสิทธิภาพ ตั้งแต่การส่งออกฐานข้อมูลทั้งหมด ไปจนถึงการส่งออกที่ละเอียดมากขึ้น เช่น ตารางเดียวหรือระเบียนเฉพาะเจาะจง การเข้าใจคำสั่งเหล่านี้และฟังก์ชันการทำงานจะช่วยให้กระบวนการสำรองข้อมูล การย้าย และกู้คืนฐานข้อมูลเป็นไปอย่างราบรื่น
