ส่งออกเฉพาะ schema (โครงสร้าง) จาก SQL dump

มีสามสถานการณ์ที่เกิดขึ้นซ้ำๆ:

• ตั้ง schema สำหรับ staging หรือ CI — คุณต้องการโครงสร้างตารางเปล่าเพื่อให้เทสต์รันกับคอลัมน์ index และ constraint จริงได้ แต่คุณไม่อยากส่งข้อมูล production หลายกิกะไบต์เข้าไปในสภาพแวดล้อมแบบใช้แล้วทิ้งเด็ดขาด
• เอาแค่ข้อมูล seed — schema มีอยู่แล้วในฐานข้อมูลปลายทาง และคุณแค่ต้องการแถว INSERT มาเติมข้อมูล โดยไม่มีคำสั่ง CREATE ที่จะชนกับสิ่งที่มีอยู่
• กำจัดสิ่งรกรุงรัง — dump บวมด้วยตาราง logs, sessions, audit_trail หรือ cache ที่มีแถวหลายสิบล้านแถวที่คุณไม่เคยอยากนำเข้าเลย

ทำไม mysqldump --no-data จึงไม่พอ

คำแนะนำคลาสสิกคือ mysqldump --no-data (เฉพาะโครงสร้าง) หรือ mysqldump --no-create-info (เฉพาะข้อมูล) ทั้งคู่เป็น แฟล็กสำหรับส่งออก: มันเปลี่ยนสิ่งที่ dump ใหม่ จากฐานข้อมูลที่กำลังทำงาน จะมี พวกมันไม่ช่วยอะไรกับ dump ที่คุณมีอยู่แล้วเป็นไฟล์ .sql บนดิสก์

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

วิธีทำด้วยมือ

ทางที่เห็นได้ชัดคือตัดบรรทัดออกด้วย grep หรือ sed เพื่อเก็บโครงสร้างและตัดข้อมูลออก:

# เก็บทุกอย่างยกเว้นแถว INSERT
grep -v '^INSERT INTO' dump.sql > schema.sql

# หรือเก็บเฉพาะแถว INSERT สำหรับไฟล์ที่มีแต่ข้อมูล
grep '^INSERT INTO' dump.sql > data.sql

มันดูดีบน dump เล็กๆ ที่เรียบร้อย แล้วก็พังบนของจริง:

• INSERT แบบหลายบรรทัด คำสั่ง INSERT INTO … VALUES เดียวสามารถกินพื้นที่หลายพันบรรทัด grep จับคู่บรรทัดแรกและทิ้งค่าที่เหลือให้ลอยเคว้ง สร้างไฟล์ที่เสีย
• extended insert mysqldump รวมหลายแถวเข้าเป็นคำสั่งยักษ์เดียว ตัวกรองแบบทีละบรรทัดบอกไม่ได้ว่าคำสั่งแบบนั้นจบตรงไหน
• คอมเมนต์และบล็อก บล็อกเงื่อนไข /*! … */, LOCK TABLES และคอมเมนต์ -- แทรกสลับกับข้อมูล ตัวกรองแบบง่ายๆ จึงเก็บขยะไว้ หรือไม่ก็ตัดสิ่งที่การนำเข้าจำเป็นต้องใช้ทิ้งไป
• เฉพาะข้อมูลยิ่งแย่กว่า grep '^INSERT' ล้วนๆ โยนคำสั่ง SET และ LOCK ที่แถวต้องพึ่งพาทิ้งไป และ INSERT ใดที่ไม่ได้เริ่มตรงตำแหน่งคอลัมน์ศูนย์พอดีก็หายไปอย่างเงียบๆ

คุณลงเอยด้วยการดีบักตัวกรองของคุณเอง แทนที่จะได้ schema ของคุณ

เฉพาะโครงสร้างหรือเฉพาะข้อมูลด้วย DumpCleaner

DumpCleaner เข้าใจคำสั่ง SQL แทนที่จะจับคู่ทีละบรรทัด มันจึงแยกโครงสร้างออกจากข้อมูลได้อย่างน่าเชื่อถือ บนไฟล์ทุกขนาด:

1. ลาก .sql dump ของคุณเข้าไปใน DumpCleaner (MySQL, MariaDB หรือ PostgreSQL) มัน parse ไฟล์แบบสตรีม dump หลายกิกะไบต์จึงโหลดด้วยหน่วยความจำคงที่
2. สลับ ชนิดคำสั่ง ที่คุณต้องการ: CREATE TABLE, CREATE INDEX, INSERT INTO, ALTER, DROP, LOCK, SET เอาติ๊กออกจาก INSERT INTO แล้วคุณจะได้เฉพาะโครงสร้าง เอาติ๊กออกจาก CREATE TABLE / CREATE INDEX แล้วคุณจะได้เฉพาะข้อมูล
3. หรือเลือกพรีเซ็ต — เฉพาะโครงสร้าง หรือ เฉพาะข้อมูล — เพื่อตั้งค่าสวิตช์ทุกตัวในขั้นตอนเดียว แล้วปรับละเอียดรายตารางได้ถ้าต้องการ
4. ส่งออก คุณจะได้ไฟล์ schema ที่กระชับสำหรับ staging หรือ CI หรือ dump เฉพาะข้อมูลที่สะอาดสำหรับ seeding

เพราะตัวกรองตระหนักถึงคำสั่ง extended INSERT แบบหลายบรรทัดจึงถูกเก็บหรือทิ้งทั้งก้อน ไม่มีการตัดกลางคัน

พลังที่แท้จริงคือการกรองทำงาน รายชนิดคำสั่ง และ รายตาราง และทั้งสองผสานกันได้ คุณสามารถเก็บโครงสร้างของ ทุก ตาราง แต่รวมข้อมูล INSERT เฉพาะสามตารางที่คุณต้องการจริง โดยปล่อยให้ตาราง logs ที่มี 89 ล้านแถวเป็นเปลือกเปล่า นั่นคือสิ่งที่ไม่มีแฟล็ก --no-data ใดแสดงออกได้

คำถามที่พบบ่อย