I have a 350MB msyql dump file that takes over 3 minutes to load. I'd like to speed it up. I've tried using --opt with mysqldump (shaved off 30 seconds). The best I can achieve is 42 seconds faster. Any other techniques to speed up a mysql restore from a dump file?

  • Fundamentally, restore time depends on how fast your server can handle a single thread executing (usually bulk) inserts, but other questions occur to me: How many tables and how many databases are contained in the file? Are the tables MyISAM or InnoDB or something else? Is the restore also overwriting existing tables? Have you considered how much time dropping those tables contributes to the restore? What version of MySQL Server are you using? Are you sure --opt actually changed anything and the timing wasn't coincidental... since --opt is typically already on by default? – Michael - sqlbot Jul 24 '13 at 1:37
  • Not sure if this is already in your mysqldump file: DISABLE KEYS beforehand, then ENABLE KEYS afterwards is much faster than index creation at insertation time. – 0xCAFEBABE Jul 24 '13 at 7:25
  • MySQL version is 5.5.31 (dist from Oracle). We're using all InnoDB tables on this database. Yes, it does overwrite tables if they exist. I've tried --disable-keys on the mysqlump side of the script but really did not get much back in return. – dperry1973 Jul 24 '13 at 19:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.