Import 50GB MySQL SQL Files in 2.5 Hours: Split, Tune, Parallelize & Resume

Why Large .sql Imports Are Painful

Compared to CSV, .sql files present four core problems: (1) huge files with tens of thousands of INSERT statements exhaust memory during parsing; (2) each INSERT flushes logs individually, multiplying disk I/O by 10x; (3) any error mid‑import forces a full restart, wasting hours; (4) verbose USE, SET, and comment lines slow parsing.

Pre‑Import Checks (Save 80% Debug Time)

Test with a sample: Extract 100 lines and run a trial import to verify syntax and schema compatibility.

head -n 100 /data/big_data.sql > /data/test.sql
mysql -u root -p123456 test_db < /data/test.sql

Inspect the file: Count INSERT statements and check for redundant USE/SET lines.

grep -c "INSERT INTO" /data/big_data.sql
grep "USE db_name" /data/big_data.sql | head -n 10

Four‑Step High‑Performance Import

Step 1: Split the Large File

Use split to break the .sql file into manageable chunks (100,000 lines or 500 MB each), enabling parallel processing and resume capability.

# By line count (recommended)
split -l 100000 /data/big_data.sql /data/split_sql/data_ --suffix-length=3 --numeric-suffixes
# By size (for very long single statements)
split -b 500M /data/big_data.sql /data/split_sql/data_ --suffix-length=3 --numeric-suffixes

Step 2: Clean Up Noise

Run a script to strip comments, empty lines, USE and SET NAMES statements from every split file.

#!/bin/bash
SQL_DIR="/data/split_sql"
for file in $SQL_DIR/data_*.sql; do
  sed -i '/^--/d; /^$/d; /^USE/d; /^SET NAMES/d' $file
  echo "Cleaned: $file"
done

Step 3: Tune MySQL Temporarily

Log into MySQL and apply session‑global settings that trade temporary durability for speed. Revert all after import.

-- 1. Increase InnoDB buffer pool to 70% of RAM (e.g., 12G on 16G server)
SET GLOBAL innodb_buffer_pool_size = 12G;
-- 2. Larger log files and buffer to reduce flush frequency
SET GLOBAL innodb_log_file_size = 4G;
SET GLOBAL innodb_log_buffer_size = 1G;
-- 3. Disable synchronous flush (restore to 1 after import)
SET GLOBAL innodb_flush_log_at_trx_commit = 0;
-- 4. Disable autocommit for batch commits
SET GLOBAL autocommit = 0;
-- 5. Disable foreign key checks and secondary indexes (rebuild later)
SET FOREIGN_KEY_CHECKS = 0;
-- 6. Turn off slow query log and binary log
SET GLOBAL slow_query_log = 0;
SET SQL_LOG_BIN = 0;
-- 7. Extend timeout to prevent disconnects
SET GLOBAL wait_timeout = 86400;

Step 4: Parallel Import with Resume & Retry

The core script import_sql.sh processes split files concurrently (default 4 workers, adjustable to 1.5× CPU cores), skips already‑imported files (tracked via a backup directory), retries failed files up to 3 times with 10‑second delays, and logs every action to /var/log/mysql_import.log.

#!/bin/bash
DB_HOST="localhost"
DB_USER="root"
DB_PASS="123456"
DB_NAME="big_data_db"
SQL_DIR="/data/split_sql"
BACKUP_DIR="/data/imported_sql"
CONCURRENT=4
RETRY=3
LOG_FILE="/var/log/mysql_sql_import.log"

mkdir -p $BACKUP_DIR

import_single_sql() {
  local file=$1
  local filename=$(basename $file)
  local count=0
  # Skip if already imported (file exists in backup dir)
  if [ -f "$BACKUP_DIR/$filename" ]; then
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] Already imported, skip: $filename" >> $LOG_FILE
    return 0
  fi
  # Retry loop
  while [ $count -lt $RETRY ]; do
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] Attempt $(($count+1)): $filename" >> $LOG_FILE
    mysql -h$DB_HOST -u$DB_USER -p$DB_PASS -D $DB_NAME --skip-interactive < $file
    if [ $? -eq 0 ]; then
      echo "[$(date +'%Y-%m-%d %H:%M:%S')] Success: $filename" >> $LOG_FILE
      mv $file $BACKUP_DIR/
      return 0
    else
      count=$(($count+1))
      echo "[$(date +'%Y-%m-%d %H:%M:%S')] Attempt $count failed, retry in 10s..." >> $LOG_FILE
      sleep 10
    fi
  done
  echo "[$(date +'%Y-%m-%d %H:%M:%S')] $filename failed after $RETRY retries" >> $LOG_FILE
  return 1
}

export -f import_single_sql
export DB_HOST DB_USER DB_PASS DB_NAME BACKUP_DIR RETRY LOG_FILE

find $SQL_DIR -name "data_*.sql" | xargs -I {} -P $CONCURRENT bash -c 'import_single_sql "$@"' _ {}
echo "[$(date +'%Y-%m-%d %H:%M:%S')] All import tasks submitted!" >> $LOG_FILE

Run it in the background and tail the log:

chmod +x import_sql.sh
nohup ./import_sql.sh &
tail -f /var/log/mysql_import.log

Post‑Import Cleanup (Mandatory)

Restore MySQL defaults: Re‑enable flush‑at‑commit, autocommit, foreign keys, slow log, binary log.

SET GLOBAL innodb_flush_log_at_trx_commit = 1;
SET GLOBAL autocommit = 1;
SET FOREIGN_KEY_CHECKS = 1;
SET GLOBAL slow_query_log = 1;
SET SQL_LOG_BIN = 1;

Verify data integrity: Compare row counts in the target table with the number of INSERT statements in the split files.

mysql -u root -p123456 -e "SELECT COUNT(*) FROM big_data_db.table_name;"
grep -c "INSERT INTO" /data/split_sql/*.sql

Experience‑Based Tips

Don’t over‑parallelize: 1.5× CPU cores (e.g., 12 on 8‑core) is optimal; more causes contention.

Drop secondary indexes before import, rebuild after; primary keys stay.

Run during off‑peak hours to avoid saturating I/O for production workloads.

Keep import logs for at least 7 days for troubleshooting.

Bonus: Pure INSERT Files → CSV + mysqlimport

If the .sql contains only INSERT INTO ... VALUES statements, convert to CSV and use mysqlimport for up to 3× further speedup.

# Extract VALUES to CSV (example columns: id,user_id,content)
grep -o "VALUES (.*)" /data/split_sql/data_001.sql | sed 's/VALUES (//; s/)$//' > /data/csv_data/data_001.csv
# Import with mysqlimport
mysqlimport -u root -p123456 --local --fields-terminated-by=',' --lines-terminated-by='
' big_data_db /data/csv_data/data_001.csv

Real‑world result: a 50 GB file with nearly 100 million rows dropped from 12+ hours to under 2.5 hours using this combined approach.