Take the 2-minute tour ×
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

3 Answers 3

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 @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

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

[mysqldump]
max_allowed_packet=1G
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.

http://www.percona.com/doc/percona-xtrabackup/innobackupex/creating_a_backup_ibk.html

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

 
discard

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.