I’m facing an annoying problem on my RHEL 5 system. I have just added a new hard drive dedicated to MySQL, and I’m trying to migrate a huge database of about 500GB using the command:
mysqldump --host otherhost -A | mysql
Although this approach does not seem to be explicitly recommended, I’m going ahead with it. However, the process fails intermittently, reporting that too many files are open. This results in the MySQL server receiving a signal, which causes it to crash and restart. I’ve tried adjusting the limits with ulimit and tweaking sysctl settings, but the issue continues. What steps can I take to resolve this problem?
The direct pipe approach you’re using can be problematic for such large databases because it maintains connections to both source and destination simultaneously. I encountered this exact scenario when moving a 400GB database between servers and found that breaking the process into smaller chunks resolved the file descriptor exhaustion. Instead of dumping everything at once, try using the --databases flag to migrate one database at a time, or use --where clauses to process tables in smaller batches. Another approach that worked well for me was dumping to an intermediate file first, then importing separately - this eliminates the dual connection overhead. You might also want to monitor the actual file descriptor usage with lsof during the process to see exactly where the bottleneck occurs.
I ran into something similar when migrating a large production database a few years back. The issue isn’t just about ulimit settings - MySQL itself has internal file descriptor limits that can cause this problem. Check your MySQL configuration file for the open_files_limit parameter and increase it significantly. I had to set mine to around 65536 for a similar sized database. Also, make sure you’re running the mysqldump with the --single-transaction flag to avoid locking issues, and consider using --quick to retrieve rows one at a time rather than buffering the entire result set. The combination of these MySQL-specific settings along with system-level ulimit adjustments should resolve the crashes you’re experiencing during the migration process.
On old RHEL5 + old MySQL, a 500GB streamed dump (mysqldump … | mysql) almost always blows up because MySQL hits its file-descriptor limit, even if you raised ulimit. MySQL on those versions ignores system limits unless you set them inside my.cnf, so it eventually crashes and restarts.
Set real MySQL limits in /etc/my.cnf: open_files_limit=65535 and a bigger table_open_cache.
Raise system limits in limits.conf + sysctl so the OS can keep up.
Don’t do one giant pipe dump — import DBs or big tables separately with --single-transaction --quick.
If the repeated crashes already caused table corruption, a repair tool like Stellar Repair for MySQL can sometimes recover the broken tables before you re-import.