Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

There is a database A size of 500GB. Tables in database A contains both MyISAM and INNODB tables. MyISAM tables are master tables and Innodb tables are main transaction tables.

Backup and restore using mysql dump taking quit a long time or days.

  • max_allowed_packet=1G
  • foreign_key_checks=0
  • auto_commit = off
share|improve this question
I just want to make sure you are using --opt with the mysqldump utility. This is a HUGE time saver when reloading. without it mysqldump creates the sql file row by row. --opt does: Adds locks, drop and recreate with all create options enabled, temporarily disables the keys for a quick reload and then turns keys back on at the end to rebuild the indexes after the writing is done. It concatenates several rows together in inserts to reduce filesize AND parsing time of each statement (by up to 70%) and enables --quick to reduce the load against the dumping DB when running. reduces load time by 80% –  ppostma1 Aug 7 at 14:15

4 Answers 4

Perhaps you should consider doing parallel mysqldumps of databases or individual tables.

I explained how to do this back on April 17, 2011 : How can I optimize a mysqldump of a large database?

Don't worry about trying to set the options for foreign_key_checks because the header of a standard mysqldump contains directives to set such things :

-- MySQL dump 10.13  Distrib 5.5.23, for Linux (x86_64)
-- Host: localhost    Database: zm3_quizzes
-- ------------------------------------------------------
-- Server version   5.5.23-55

/*!40101 SET NAMES utf8 */;
/*!40103 SET TIME_ZONE='+00:00' */;

It would be simpler for you to add the following to /etc/my.cnf

share|improve this answer

But the link also recommends XtraBackup from Percona, which is faster, no locks on tables that are already in use and gains no time for restoration. For 500GB of data I prefer Percona XtraBackup to be quick and efficient unless you want to convert them to innodb_file_per_table model if it wasn't from the existing DB server. Below link explains on how to proceed.

share|improve this answer

I think the question is about how to restore faster from mysqldump's ceated dump files and not a different backup solution.

One of the ways, you can do this is by creating groups of tables in your schema and create a separate DB user for each group and use MySQL permissions to not allow tables to be inserted to using all but one DB user.

This is a proven, fast, almost parallel technique but not 100% sure how long it will take to restore from 500G. But imho, you need something parallel. See the link below for an example.

Fast, parallel restore from SQL dumps (mysqldump) for MySQL

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.