3

MySQLdump and upload process taking too long time (~8 hr) to complete the whole process.

I am dumping active database into mysqldump.tar file and almost 3gb. When I load into new database its taking 6-8 hr to complete the process (upload into new database).

What will be the recommended solution for me to complete the process?

halfer
  • 19,824
  • 17
  • 99
  • 186
Mahe
  • 29
  • 1
  • 4
  • [See here](http://dba.stackexchange.com/questions/20/how-can-i-optimize-a-mysqldump-of-a-large-database). – asdf Aug 24 '15 at 18:58
  • Offtopic. Not a programming questions. This falls under administration, so try http://dba.stackexchange.com – Marc B Aug 24 '15 at 20:39

1 Answers1

5

If I understand correctly, your main problem is that loading the data into your new database is the step that's taking a lot of time. Besides reading the link provided by asdf in his comment ("How can I optimize a mysqldump of a large database?"), I suggest you some things:

  • Use the --disable-keys option; this will add alter table your_table DISABLE KEYS before the inserts, and alter table your_table ENABLE KEYS after the inserts are done. When I've used this option, the insertion time is about 30% faster
  • If possible, use the --delayed-insert option; whis will use INSERT DELAYED insted of the "normal" INSERT.
  • If possible, dump the data of different tables into different files; that way you may upload them concurrently.

Check the reference manual for further information.

Community
  • 1
  • 1
Barranka
  • 20,547
  • 13
  • 65
  • 83