11

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
1
  • 1
    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 '15 at 14:15
5

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

1
  • 1
    The issue with Percona Xtrabackup is that mysqld needs to be stopped in order to restore a database. – dr_ Oct 20 '16 at 12:16
2

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

2

Check out mydumper. It's a lot quicker than mysqldump: New mydumper 0.6.1 release offers performance and usability features

You can download from here: https://github.com/maxbube/mydumper

What is mydumper? Why?

  • Parallelism (hence, speed) and performance (avoids expensive character set conversion routines, efficient code overall)
  • Easier to manage output (separate files for tables, dump metadata, etc, easy to view/parse data)
  • Consistency - maintains snapshot across all threads, provides accurate master and slave log positions, etc
  • Manageability - supports PCRE for specifying database and tables inclusions and exclusions
1

Try the following with innobackupex:

innobackupex --parallel=7 --rsync  /var/mysql/backup

You can change the parallel process number as you like. Since you mentioned you have myisam tables, the "rsync" parameter will help speed the backup of those.

Your Answer

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

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