Mysql – thesqldump vs thesqlbackup

Take these some points in your consideration they may help you in case of generating the dump and restoring it.

1. Use Extended inserts in dumps.
2. Dump with --tab format so you can use mysqlimport, which is faster than mysql < dumpfile.
3. Import with multiple threads, one for each table.
4. Use a different database engine if possible. importing into a heavily transactional engine like innodb is awfully slow. Inserting into a non-transactional engine like MyISAM is much much faster.
5. Turn off foreign key checks and turn on auto-commit.
6. If you are importing to innodb the single most effective thing you can do is to put innodb_flush_log_at_trx_commit = 2 in your my.cnf, temporarily while the import is running. you can put it back to 1 if you need ACID

Give it a try..

The question essentially equals this one: "what is a fast backup + recovery method for MySQL". This is because setting up MySQL replication is essentially taking a backup of some sorts, and opening it on another machine (and then syncing the slave with the master, which is a simple operation).

So it boils down to the fact mysqldump is too slow for you (makes sense). I've just answered the same here, but here are the highlights:

• Percona Xtrabackup is a common and tested (and free) backup tool. It is of interest to you if you're using InnoDB since it makes a hot backup of the InnoDB tablespace, with small impact on the running server. The resulting backup reflects the time at which the backup process ended, which means shorter initial lag for your slave.

• File system snapshots, via filesystems such as ZFS, or via LVM, or otherwise disk based snapshots, are all good solutions. Some (LVM in particular) making for more load than others.

What's common to the above solutions is that they make for a binary backup, not a logical one.

For your next question - yes. You can use MyDumper, which makes for a multithreaded backup (complemented by the multithreaded MyLoader tool). This backup is logical, and uses mysqldump internally, concurrently. It is typically a few times faster than standard mysqldump.