ดึงตารางเดียวออกจาก MySQL dump

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

เช่นเดียวกันเมื่อคุณกำลัง ดีบักตารางเดียว การจำลองปัญหาข้อมูลใน orders ไม่ต้องใช้ตารางอีก 126 ตาราง หรือเวลาทำ การย้ายบางส่วน (partial migration) ที่มีเพียงไม่กี่ตารางย้ายไปยังบริการใหม่ ในทุกกรณีคำถามเหมือนกัน: ฉันจะดึง เฉพาะตารางเหล่านี้ ออกจาก dump ก้อนยักษ์ได้อย่างสะอาดและรวดเร็วได้อย่างไร?

วิธี sed/grep และเหตุผลที่มันพัง

คำตอบคลาสสิกคือหั่นไฟล์ด้วย sed ระหว่างส่วนหัว (section header) ที่ mysqldump เขียนไว้สำหรับแต่ละตาราง:

# ดึงตาราง users ออกจากไฟล์ mysqldump
sed -n '/-- Table structure for table `users`/,/-- Table structure for table/p' dump.sql > users.sql

มันดูฉลาด และบน dump เล็กๆ ที่เรียบร้อยมันอาจใช้ได้สักครั้ง แต่บนไฟล์ 7 GB ของจริง มันพังลงในหลายจุดที่แนบเนียน:

• INSERT แบบหลายบรรทัด คำสั่ง INSERT INTO ... VALUES เดียวสามารถกินพื้นที่หลายพันบรรทัด เครื่องมือที่ทำงานทีละบรรทัดอย่าง sed และ grep ตัดแถวเหล่านั้นครึ่งกลาง และสร้างไฟล์ที่ parse ไม่ได้
• backtick และอักขระพิเศษ ค่าในคอลัมน์มี --, เซมิโคลอน, backtick, เครื่องหมายคำพูด และขึ้นบรรทัดใหม่อยู่ในสตริง รูปแบบของคุณจึงจับข้อความ ที่อยู่ในข้อมูล ซึ่งไม่เคยตั้งใจให้เป็นตัวคั่น
• ชื่อตารางที่เป็นสตริงย่อย การดึง user จะคว้า user_roles, user_sessions และ password_user มาด้วย เพราะชื่อนั้นปรากฏเป็นสตริงย่อยของตัวอื่น
• คอมเมนต์และส่วนหัวเลื่อนไปมา ข้อความคอมเมนต์ที่แม่นยำต่างกันไปในแต่ละเวอร์ชันของ MySQL, MariaDB และ PostgreSQL และ pg_dump ก็ไม่ได้ใช้ส่วนหัวเดียวกันเลย คำสั่งเดิมจึงคืนค่าว่างเปล่าอย่างเงียบๆ
• ไม่รู้จัก foreign key ถึงคุณจะหั่น orders ได้อย่างสมบูรณ์ แถวของมันก็อ้างอิงถึง users sed ไม่รู้เรื่อง สิ่งที่ดึงออกมาจึงนำเข้าไม่สำเร็จเพราะข้อผิดพลาด foreign key

คุณลงเอยด้วยการซ้อน awk ทับ grep ทับ sed ทดสอบกับชุดข้อมูลย่อย แล้วก็ยังไม่กล้าเชื่อผลลัพธ์อยู่ดี มันมีวิธีที่ดีกว่า

ดึงตารางด้วย DumpCleaner

DumpCleaner parse dump อย่างถูกต้อง มันเข้าใจขอบเขตของคำสั่ง, INSERT แบบหลายบรรทัด, backtick และคอมเมนต์ มันจึงตัดที่บรรทัดถูกต้องเสมอ การดึงตารางที่คุณต้องการใช้เพียงสี่ขั้นตอน:

1. ลาก .sql dump ของคุณเข้าไป DumpCleaner สตรีมไฟล์ dump ขนาด 7 GB จึงเปิดด้วยหน่วยความจำคงที่ แทนที่จะโหลดทั้งก้อน
2. อ่านรายการตารางที่ตรวจจับอัตโนมัติ ทุกตารางถูก parse ออกมาและแสดงพร้อมจำนวนแถวและขนาด ไม่ต้องเลื่อนผ่านหลายล้านบรรทัดเพื่อหาว่ามีอะไรอยู่
3. เลือกตารางที่คุณต้องการ ติ๊ก users, orders และ products หรือพิมพ์รูปแบบ regex / รูปแบบยกเว้น เช่น *_logs เพื่อตัดทั้งกลุ่มในครั้งเดียว
4. รวมตารางแม่ของ foreign key แล้วส่งออก เลือกดึงตารางแม่ที่ถูกอ้างอิงเข้ามาอัตโนมัติได้ตามต้องการ แล้วส่งออกไฟล์ที่สะอาดและนำเข้าได้

มีหลายอย่างที่ทำให้เรื่องนี้ยุ่งยากน้อยกว่าเครื่องมือ shell มาก:

• รูปแบบยกเว้น อย่าง *_logs หรือ *_sessions คัดกรอง audit และ session ที่รกออกไปโดยไม่ต้องเอาติ๊กออกจากตารางเป็นสิบๆ ตารางด้วยมือ
• ตัวกรอง regex ให้คุณจับคู่ตามคำนำหน้า คำต่อท้าย หรือรูปแบบใดก็ได้ เช่น เก็บทุกอย่างที่ตรงกับ shop_*
• การแสดงตัวอย่างแบบสด แสดงให้เห็นชัดเจนว่าตารางไหนจะอยู่ในผลลัพธ์ขณะที่คุณเปลี่ยนการเลือก
• ขนาดผลลัพธ์โดยประมาณ บอกคุณว่าสิ่งที่ดึงออกมาจะใหญ่แค่ไหนก่อนส่งออก คุณจึงรู้ว่ามันจะพอดีกับเครื่องหรือไม่
• การพึ่งพา foreign key สามารถถูกรวมเข้ามาอัตโนมัติ การเลือก orders จึงพา users ตามมาอย่างเงียบๆ และสิ่งที่ดึงออกมาก็นำเข้าได้โดยไม่มีข้อผิดพลาด constraint

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