How to increase fast backup and restore of 500GB database using mysqldump?

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

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

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

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

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.