List:General Discussion« Previous MessageNext Message »
From:Jay Ess Date:December 17 2009 9:59am
Subject:Re: Importing large databases faster
View as plain text  
Madison Kelly wrote:
> Hi all,
>
> I've got a fairly large set of databases I'm backing up each Friday. 
> The dump takes about 12.5h to finish, generating a ~172 GB file. When 
> I try to load it though, *after* manually dumping the old databases, 
> it takes 1.5~2 days to load the same databases. I am guessing this is, 
> at least in part, due to indexing.
>
> My question is; Given an empty target DB and a dump file generated via:
>
> ssh root@server "mysqldump --all-databases -psecret" > 
> /path/to/backup.sql
I use the "-e -v -f -q -Q -K" parameters for the mysqldump on large 
tables/databases. It does what you are asking for. Disables the key 
generation until all of the data is inserted. It also uses multi insert 
statements and not individual insert statement for every row which 
speeds up things considerable.
Thread
Importing large databases fasterMadison Kelly16 Dec
  • RE: Importing large databases fasterGavin Towey16 Dec
    • Re: Importing large databases fasterMadison Kelly17 Dec
      • RE: Importing large databases fasterGavin Towey17 Dec
  • Re: Importing large databases fasterShawn Green17 Dec
  • Re: Importing large databases fasterJay Ess17 Dec
    • Re: Importing large databases fastermos17 Dec
      • Re: Importing large databases fasterBrent Clark18 Dec