4

Server #1 is a MySQL database server running on Debian which contains numerous tables and one particular table that is over 100GB.

Server #2 is used as a MySQL slave for replication, but now it needs to be reset and the replication reinitialized due to issues that arose.

There is not presently enough room on the hard drive of server #1 to do a full database dump (i.e. less than 100GB of free space). Aside from upgrading the hardware which would require downtime, what would be the best way to get the database dump from server #1 to server #2 intact, without corruption, and without filling up the hard drive on the server #1 in the process?

7 Answers 7

6

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.

3
  • Those are really good options. Depending on their network bandwidth it may be faster than mine.
    – Sekenre
    Jun 24, 2009 at 12:44
  • From reading the MySQL docs, single-transaction doesn't work the same w/ MyISAM (dev.mysql.com/doc/refman/5.0/en/…), but using the other info you've provided this strategy should still work as long as the tables are locked.
    – calvinf
    Jun 24, 2009 at 20:15
  • 1
    No, myisam does not support transactions. You really should consider converting to innodb Jun 24, 2009 at 23:39
6

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

mysqldump -u root -p bigdb | bzip2 -c | ssh -T user@server2 "cat > backup.sql.bz2"
1
  • I'd even pipe it directly into the sql slave server aside from that I think that's probably the easiest way to recover a state to start replicating again... Jun 23, 2009 at 20:46
1

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.

0

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.

1
  • The nfs host should definitely be separate from the two machines, the replication falls apart if a main server has a disk crash. Jun 23, 2009 at 20:23
0

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.

0

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
0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .