MySQL database backup over network

Quick and dirty way (Starting from Server #1):

mysqldump -u root -p bigdb | bzip2 -c | ssh -T user@server2 "cat > backup.sql.bz2"

You can do this without an intermediary file, as well as resetting the replication pointers in the process, so you don't miss any updates (and have to resync again)

1. Stop replication the slave

   slave> mysql 'slave stop;'

2. dump the master to the slave, using the --master-data=1 flag

   master> mysqldump -e --master-data=1 --single-transaction $DATABASE | ssh -C user@slave 'mysql $DATABASE'

3. start replication on the slave

   slave> mysql 'slave start'

--master-data=1 causes mysqldump to emit the CHANGE MASTER TO ... settings at the top of the dump to set the replication binlog and offset to the exact point in the masters binlog at the time the dump was taken

-e uses the extended output format, basically multiple sets of value per insert statement, which is more efficent both on the wire, and when being applied to the slave.

--single-transation tells mysql to open a transaction over the whole dump, rather than using LOCK TABLES.

You can dump mysql databases from a remote host, just use the --host or -h argument with mysqldump

server2# mysqldump -h server1 -u root -p --opt | gzip > database.sql.gz
server2# zcat database.sql.gz | mysql -u root -p

You could obviously skip the dump to disk, but imports tend to be slower than dumping. If your CPU on server2 is a bottleneck and your disk is fast then you may want to skip the gzip step, so you minimise the downtime on your master server.

Obviously, my answer skips the details to do with recording replication details and making sure you have a consistent dump for replication, as these are dealt with in the MySQL manual.

Obviously NFS, if there is no firewall between servers. If you have a firewall, you may need to reconfigure it to allow some additional traffic for NFS to work.

Similar solution, a bit more complicated, would be to use smbmount on server1 and smbd on server2.

If you don't want to mess with firewall (or you do not want send unencrypted data between servers), I would recommend sshfs.

If you're using strictly myisam tables, shutdown mysql and cp the data files from server1 to server2. You can also keep mysql up and put in read-only with global read lock and then cp the tables over.

Editing for clarity & adding:

You can also mysqldump the tables on server1 to stdout and pipe to mysql connected to server2. But with 100gb table that might take some time & would still require you to put it read-only if you want any consistency.

Your best bet, if replication is not an option and the local server does not have enough disk space, is to suffer the downtime and do a full sync from the live database files.

1. Gracefully shut down MySQL
2. Remount the MySQL partition as read-only (this helps if you cock anything up)
3. Use rsync, wget or something similar to grab an entire copy of the dataset and transfer it to your second server
4. Remount the MySQL partition as read-write
5. Restart MySQL

Depending on the speed of the network connection, that will probably result in less downtime than installing a new disk.

Alternatively, consider adding a temporary USB Mass Storage Device to the server and use that to grab a full database dump. That should result in zero downtime.

If the boxes are within reasonable physical distance to eachother, you could attach a usb-drive and do your databasedump there.

Another alternative could be ssh/pipes if this runs on a nix-box, although depending on what backend you're using for storage, the table lock could be painful for the large table if trying to leave the mysqlprocess running.

From mysqlbox2, something like:

ssh mysqlbox1 "mysqldump <options for username/pw/tables-to-dump>" > /path/to/spacious/fs/dbdump