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
Improve this question
1
  • 2
    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
    Commented Aug 7, 2015 at 14:15

4 Answers 4

Reset to default
6

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

Improve this answer
2
  • 1
    The issue with Percona Xtrabackup is that mysqld needs to be stopped in order to restore a database.
    – dr_
    Commented Oct 20, 2016 at 12:16
  • Obviously, you are stopping entire DB instance as it will replace your ibd and logfiles as well. Because you should be lucky if you are able to copy the DB_schema folder and get all mysql check is working without any issues.
    – Mannoj
    Commented Nov 28, 2022 at 13:57
3

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

Improve this answer
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
Improve this answer
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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