How to Fix MySQL ERROR 2005 When Importing Large Dump Files with BLOB Data on Windows


2 views

When dealing with large MySQL dump files containing BLOB data (especially images), Windows users often encounter the dreaded ERROR 2005 with garbled hostnames appearing in the error message. This typically happens around binary data sections in the dump file.

The core issue stems from several potential factors:

  • Character encoding mismatches between Linux (UTF-8) and Windows systems
  • Line ending conversions corrupting binary data
  • Memory limitations during import
  • Network transfer corruption (especially with large files)

Here are several approaches I've successfully used:

1. Verify and Repair the Dump File

First, properly validate the gzip file:

gzip -t your_dump.sql.gz
# If corrupted, try repairing
gzip -d your_dump.sql.gz
gzip -9 your_dump.sql

2. Use mysqlimport with Binary Mode

For Windows specifically:

mysql -u username -p --binary-mode=1 --max-allowed-packet=1G database_name < dump_file.sql

Key parameters:

  • --binary-mode=1 prevents character interpretation
  • --max-allowed-packet handles large BLOBs

3. Split the Dump File

Use this PowerShell script to split large files:

$i=1; Get-Content large_dump.sql -ReadCount 50000 | %{
  $_ | Out-File "split_$i.sql"
  $i++
}

Then import sequentially:

foreach ($file in Get-ChildItem split_*.sql) {
  mysql -u username -p database_name < $file.FullName
}

4. Alternative Import Methods

For very large files, consider:

Using MySQL Workbench

In the Administration tab:

  1. Select "Data Import/Restore"
  2. Choose "Import from Self-Contained File"
  3. Set "Default Character Set" to binary
  4. Enable "Use Compression" if source was .gz

Using HeidiSQL

Right-click database → Import SQL file → Check "Binary import" option

To inspect the problematic area (line 3118 in your case):

# On Linux/WSL:
sed -n '3110,3125p' dump_file.sql > problem_area.sql

# Or using PowerShell:
Select-String -Path dump_file.sql -LineNumber 3110,3125 > problem_area.sql

Look for:

  • Malformed SQL statements near BLOB data
  • Incomplete hex representations (should be 0x... format)
  • Unexpected line breaks in binary sections

For future dumps:

mysqldump --hex-blob --skip-extended-insert --single-transaction -r backup.sql database_name

Key flags:

  • --hex-blob - Safest binary representation
  • --skip-extended-insert - Easier error recovery
  • --single-transaction - Consistent backup

For non-critical dev environments:

# Create schema-only dump
mysqldump --no-data database_name > schema.sql

# Import schema
mysql -u username -p database_name < schema.sql

# Then import data excluding problematic tables
mysqldump --no-create-info --ignore-table=database_name.problem_table database_name > data.sql
mysql -u username -p database_name < data.sql

When attempting to import a 2GB MySQL dump file containing BLOB data (primarily JPG images) from a Linux server to a Windows development machine, you might encounter this bizarre error:

ERROR 2005 (HY000) at line 3118: Unknown MySQL server host 
'╖?*á±dÆ╦N╪Æ·h^ye\"π╩i╪       Z+-$▼₧╬Y.∞┌|↕╘l∞/l╞⌂î7æ▌X█XE.ºΓ[ ;╦ï♣éµ♂º╜┤║].♂┐φ9dë╟█'╕ÿG∟═0à¡úè♦╥↑ù♣♦¥'╔NÑ' (11004)

This typically occurs due to:

  • Binary data corruption during transfer (especially when moving between Linux/Windows)
  • Character encoding issues when MySQL interprets the dump file
  • Memory limitations during import
  • Line ending conversion problems (\n vs \r\n)

First, verify the gzip file isn't corrupted:

# Linux/Windows (WSL):
gzip -t your_dump.sql.gz

# Windows alternative:
certUtil -hashfile your_dump.sql.gz SHA256

Option 1: Use mysqlimport with --binary-mode

mysql -u username -p --binary-mode=1 --max_allowed_packet=1G database_name < dump.sql

Option 2: Split Large BLOB Data

Extract schema separately:

# Extract schema only
gunzip -c dump.sql.gz | sed -n '/^-- Table structure/,/^-- Dumping data/p' > schema.sql

# Import schema first
mysql -u username -p database_name < schema.sql

Option 3: Hex-encode BLOB Data

Recreate the dump with hex encoding:

mysqldump --hex-blob -u source_user -p source_db | gzip > dump_hex.sql.gz

Option 4: Use MySQL Workaround for Large Files

# In MySQL client:
SET GLOBAL max_allowed_packet=1073741824;
SET GLOBAL net_buffer_length=1000000;

# Then import
source dump.sql;
  • Use PowerShell instead of CMD for better binary handling
  • Disable automatic line ending conversion in Git Bash (if using it): 
    git config --global core.autocrlf false
  • Consider using WSL for Linux-compatible environment

For partially corrupted files, try:

# Skip problematic lines (Linux/WSL)
gunzip -c dump.sql.gz | head -n 3117 > good_part.sql
gunzip -c dump.sql.gz | tail -n +3120 >> good_part.sql

Then inspect the problematic section manually:

gunzip -c dump.sql.gz | sed -n '3115,3125p' > inspect.txt