Coping MySQL DB between two servers: which server shoud initiate the operation?

Question

I want to copy a database from serverA to serverB. The servers can communicate directly with each other. So I'm able to do something like:

ServerA is sending the database to ServerB:

• dbadm1@serverA:~$ mysqldump -C ... database | mysql -h serverB ... database

or: ServerB is ,,draining'' the database from ServerA:

• dbadm2@serverB:~$ mysqldump -C -h serverA ... database | mysql ... database

The result of both commands will be, of course, the same.

1. But which one is better/faster/etc.? Or are they the same?
2. Initiate coping from serverA or from serverB?

Answer 1

The difference should be negligible, assuming you fix one problem with your first example.

ServerA is sending the database to ServerB:

dbadm1@serverA:~$ mysqldump -C ... database | mysql -h serverB ... database

You have the -C (--compress) option in the wrong place, here. Move it to the remote machine:

dbadm1@serverA:~$ mysqldump ... database | mysql -C -h serverB ... database

The --compress option compresses data on the socket, between the utility and the host it is connecting to. Using --compress against whichever machine is local is going to accomplish nothing except waste CPU cycles. The data crossing the | pipe is not compressed by this option.

Note also that this approach is problematic with large databases, because the pipe blocks, which means mysqldump blocks, which means it stops reading from the network, which means the source server eventually blocks, while data is being written to the network. If this ever exceeds the value of net_write_timeout on the source server for one continuous block of time, the source server will throw an error and terminate the connection. Increasing net_write_timeout on the source server is a somewhat-viable workaround, but use caution, since the server blocking while writing to the network can cause other threads to stall while waiting for the table that's being blocked under certain circumstances.

I have an internal tool that eliminates this timeout issue, by implementing something resembling a ring buffer made up of gzipped temporary files, to allow the source server to write as fast as it can while allowing the buffer to drain into the target server as fast as it can (usually slower), while requiring no more temporary space disk than absolutely necessary by freeing each temporary "chunk" as soon as it's been sent down the pipe... it's invoked like mysqldump | my-buffer-tool | mysql... though I don't have it in a releasable state yet. It's also useful for piping mysqldump out to a compression utility without holding up the server.

Answer 2

I think the performance difference will be minor. Do what is more convenient.

If one machine is more powerful, run it on that machine -- Note that you will have 3 processes (mysqldump, mysql, mysqld) on that machine and 1 (mysqld) on the other.