如何将ZIP格式MySQL Dump文件转为JSON/CSV并存入MongoDB？

Hey there! Let's break down how to handle this workflow step by step—from receiving the MySQL dump ZIP in your Node.js route all the way to storing structured data in MongoDB. I’ve worked through similar tasks before, so here’s a practical, code-heavy guide tailored to your needs:

First, we’ll use multer to handle file uploads (it’s the most common tool for this in Node.js). Start by installing required dependencies:

npm install multer adm-zip sql.js mongodb

Then configure your Express route to accept ZIP files:

const express = require('express');
const multer = require('multer');
const fs = require('fs');
const app = express();

// Create temp directory if it doesn't exist
if (!fs.existsSync('./temp-uploads')) {
  fs.mkdirSync('./temp-uploads');
}

// Configure multer to store uploaded ZIPs temporarily
const storage = multer.diskStorage({
  destination: (req, file, cb) => cb(null, './temp-uploads'),
  filename: (req, file, cb) => cb(null, `${Date.now()}-${file.originalname}`)
});

const upload = multer({
  storage,
  fileFilter: (req, file, cb) => {
    // Only accept ZIP files
    const allowedTypes = ['application/zip', 'application/x-zip-compressed'];
    if (allowedTypes.includes(file.mimetype)) cb(null, true);
    else cb(new Error('Only ZIP files are allowed!'), false);
  },
  limits: { fileSize: 100 * 1024 * 1024 } // 100MB limit, adjust as needed
});

// Define the upload route
app.post('/import-mysql-dump', upload.single('dumpFile'), async (req, res) => {
  try {
    if (!req.file) return res.status(400).send('No ZIP file uploaded.');
    
    // We'll add the rest of the logic here in the next steps
    res.status(200).send('File received, processing started.');
  } catch (err) {
    res.status(500).send(`Processing failed: ${err.message}`);
  }
});

app.listen(3000, () => console.log('Server running on port 3000'));

Use adm-zip to extract the SQL dump from the ZIP. Add this logic inside your route handler:

const AdmZip = require('adm-zip');

// Inside the /import-mysql-dump route
const zip = new AdmZip(req.file.path);
const sqlEntry = zip.getEntries().find(entry => entry.name.endsWith('.sql'));

if (!sqlEntry) {
  fs.unlinkSync(req.file.path); // Clean up temp ZIP
  return res.status(400).send('No SQL file found in the ZIP.');
}

// Extract the SQL file to temp directory
const sqlFilePath = `./temp-uploads/${Date.now()}-dump.sql`;
zip.extractEntryTo(sqlEntry, './temp-uploads', false, true);

For small to medium-sized dumps, use sql.js—a pure JavaScript SQL engine that doesn’t require a separate MySQL server. Add this code next:

const initSqlJs = require('sql.js');

// Load and execute the SQL dump
const sqlContent = fs.readFileSync(sqlFilePath, 'utf8');
const SQL = await initSqlJs({
  locateFile: file => `node_modules/sql.js/dist/${file}` // Path to sql.js WASM file
});
const db = new SQL.Database();
db.run(sqlContent); // Creates tables and inserts data

// Fetch all table names and their data
const tableNames = db.exec("SELECT name FROM sqlite_master WHERE type='table'")[0].values.map(row => row[0]);
const allTablesData = {};

for (const tableName of tableNames) {
  const result = db.exec(`SELECT * FROM ${tableName}`);
  if (!result.length) continue;

  const columns = result[0].columns;
  allTablesData[tableName] = result[0].values.map(row => {
    const rowObj = {};
    columns.forEach((col, idx) => rowObj[col] = row[idx]);
    return rowObj;
  });
}

// Clean up temp files
fs.unlinkSync(req.file.path);
fs.unlinkSync(sqlFilePath);

For Large Dumps (GB-scale) #

If your dump is too big for sql.js, use a temporary MySQL instance (requires local MySQL or Docker):

const mysql = require('mysql2/promise');
const { exec } = require('child_process');
const util = require('util');
const execPromise = util.promisify(exec);

// 1. Create a temporary database
const conn = await mysql.createConnection({
  host: 'localhost',
  user: 'your-mysql-user',
  password: 'your-mysql-password'
});
await conn.execute('CREATE DATABASE temp_dump_db');
await conn.end();

// 2. Import the SQL dump
await execPromise(`mysql -u your-mysql-user -p'your-mysql-password' temp_dump_db < ${sqlFilePath}`);

// 3. Fetch data from temp DB
const tempConn = await mysql.createConnection({
  host: 'localhost',
  user: 'your-mysql-user',
  password: 'your-mysql-password',
  database: 'temp_dump_db'
});
const [tables] = await tempConn.execute("SHOW TABLES");
const allTablesData = {};

for (const table of tables) {
  const tableName = table[`Tables_in_temp_dump_db`];
  const [rows] = await tempConn.execute(`SELECT * FROM ${tableName}`);
  allTablesData[tableName] = rows;
}

// 4. Clean up
await tempConn.execute('DROP DATABASE temp_dump_db');
await tempConn.end();
fs.unlinkSync(req.file.path);
fs.unlinkSync(sqlFilePath);

Use the official MongoDB driver to insert the parsed JSON data into collections (one collection per MySQL table):

const { MongoClient } = require('mongodb');

// Inside the route, after parsing data
const mongoUri = 'mongodb://localhost:27017'; // Your MongoDB URI
const client = new MongoClient(mongoUri);

await client.connect();
const db = client.db('your-target-db'); // Your MongoDB database name

// Insert data into collections
for (const [tableName, data] of Object.entries(allTablesData)) {
  if (!data.length) continue;

  // Sanitize collection name (MongoDB doesn't allow special chars)
  const collectionName = tableName.replace(/[^a-zA-Z0-9_]/g, '_');
  const collection = db.collection(collectionName);
  
  await collection.insertMany(data);
  console.log(`Inserted ${data.length} documents into ${collectionName}`);
}

await client.close();
res.status(200).send('Successfully imported data to MongoDB!');

• Error Handling: Add more granular try/catch blocks around file operations and database calls to avoid data leaks.
• Data Type Conversion: Map MySQL types (e.g., DATETIME) to MongoDB types (e.g., ISODate) during the JSON conversion step.
• Security: Sanitize table/collection names to prevent injection attacks, and scan uploaded ZIPs for malicious content.

内容的提问来源于stack exchange，提问作者Elli Zorro