When dealing with corrupted MySQL tables (especially large .frm, .MYI, and .MYD files), standard file operations can cripple production servers. The primary issue isn't just the file size (40GB in this case), but how the copy operation interacts with system resources.

The default cp command attempts to load file contents into memory buffers aggressively. For a 10GB file, this creates:

• Excessive memory pressure
• Disk I/O spikes as the kernel flushes buffers
• CPU contention during checksum calculations

Here are proven techniques for local file copying:

1. ionice + cp (Simple but Effective)

ionice -c 3 cp /var/lib/mysql/{database}/table.MYD /home/{myuser}/table.MYD

This sets the I/O priority to idle (class 3), letting other processes take precedence.

2. dd with Throttling

dd if=/var/lib/mysql/{database}/table.MYD of=/home/{myuser}/table.MYD bs=1M count=1000
sleep 5
dd if=/var/lib/mysql/{database}/table.MYD of=/home/{myuser}/table.MYD bs=1M skip=1000 count=1000

Breaks the copy into chunks with sleep intervals. Adjust bs/count values based on your disk performance.

3. rsync Local Copy with Limits

rsync --bwlimit=10M --progress /var/lib/mysql/{database}/table.* /home/{myuser}/

Once files are copied locally, use these rsync flags for efficient cloud transfers:

rsync -azP --bwlimit=20M \
--include='table.*' \
--exclude='*' \
/home/{myuser}/ remoteuser@cloudserver:/repair_location/

Key parameters:

• -z: Compression (works well for text-based .frm files)
• --bwlimit: Prevents network saturation
• -P: Progress/resume capability

If disk space is critical, consider repairing in place:

mysqlcheck --repair --databases {database} --tables {table} \
--tmpdir=/mnt/large_temp_volume/

This requires:

• 2x table size temporary space
• Strict monitoring during operation

Always watch system metrics during file operations:

watch -n 5 "iostat -dxmt 5 3 && free -h"

---

When dealing with corrupt MySQL tables (especially large ones in the 40GB range), the file copy operation itself can become a bottleneck. The standard cp command attempts to maximize throughput by aggressively using system resources, which explains why your production server became unresponsive.

Here are several approaches to minimize resource contention during local file transfers:

# 1. ionice + cp (best for HDD arrays)
ionice -c3 cp source.frm destination.frm

# 2. rsync with throttling
rsync --bwlimit=50000 --progress /var/lib/mysql/database/table.MYD /home/user/

# 3. dd with direct I/O bypassing buffer cache
dd if=source.MYI of=destination.MYI bs=1M iflag=direct oflag=direct

# 4. pv for piping with progress
pv -petra source.MYD > destination.MYD

For the subsequent rsync to your cloud server:

• Compress during transfer: rsync -z --partial --progress table.* user@cloud:/backup/
• Limit bandwidth: --bwlimit=10M (adjust based on your connection)
• Use checksumming: -c flag for reliable transfers

Given your disk space limitations, consider:

# Split large files during transfer
split -b 2G table.MYD table_part_
rsync table_part_* remote:/destination/
# Reassemble on remote: cat table_part_* > table.MYD

Before copying any MySQL files:

# Ensure clean state (if safely possible)
mysqladmin -u root -p flush-tables
FLUSH TABLES WITH READ LOCK;
# (perform your copy)
UNLOCK TABLES;

Always monitor during transfers:

iostat -x 2  # Disk I/O
vmstat 1     # Memory/CPU
iftop        # Network (for cloud transfers)