Mysql – How to replicate a MySql server’s data without using thesqldump

backupMySQLmysqldumpreplication

I've got a working server with a lot of data on it. I've built a new server that will be lots faster. Both are running x686 code on Debian Squeeze.

A quick test using one of the tables shows that it takes about ten days to load up
the output of a simple
mysqldump dbname tablename > table.sql
mysql dbname < table.sql

Since I have lots of tables with lots of data, this isn't going to work. It would take until Christmas to load them all up.

Is there any way to just copy the binary files that mysqld is using on the first server to the disks on the second and use them without the dump/reload?

If not, are the dump or load options that will get me a two order of magnitude speed improvement?

A typical table has 10 million to 100 million records. Records lengths vary from 100 bytes to 10K bytes per record.

It would be nice to have the old slow server be a replication client of the new fast one, but I think I have to get the data up first.

Thanks

Best Answer

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.

Related Question