I have a MySQL server instance with 3 databases of total size 120 GB (65 GB, 30 GB and 25 GB). My mysqldump backup completed in just 30 mins. But the import of the same dumpfile on another host took more than 5 hours. I googled for speeding up the import dump by using
SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS=0 still couldn’t observe any improvement. Also I observed in parallel, the individual DB size during import for progress, it reached to 64GB and getting reduced to 50 GB again , then restoring backup to 65 GB. It might sound a very basic question to many - 1.) During import, why this increase/decrease of DB size happens? 2.) Any other way or alternative for speeding up mysql import through dump file? How much time would Xtrabackup take for the same size? My sincere thanks in advance to all MySQLearners for extending help in same.
Hello All,
Anybody with any suggestions please, would be really helpful.
I’m also having this issue. No solutions?
Yes. mysqldump restore will take 5x or 10x times more than creating the dump.
Reg. the size growing and shrinking : IMO, is probably because of mysql using temporary tables
during restoring which get deleted once the table is reconstructed.
Check this link
[url]performance - How long should a 20GB restore take in MySQL? (A.k.a. Is something broken?) - Stack Overflow