Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

7 Answers 7

up vote 5 down vote accepted

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.

share|improve this answer
    
Those are really good options. Depending on their network bandwidth it may be faster than mine. –  Sekenre Jun 24 '09 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 '09 at 20:15
1  
No, myisam does not support transactions. You really should consider converting to innodb –  Dave Cheney Jun 24 '09 at 23:39

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

mysqldump -u root -p bigdb | bzip2 -c | ssh -T user@server2 "cat > backup.sql.bz2"
share|improve this answer
    
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... –  Server Horror Jun 23 '09 at 20:46
    
Thanks, I'll be trying this out soon. –  calvinf Jun 23 '09 at 23:16

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.

share|improve this answer

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.

share|improve this answer
    
The nfs host should definitely be separate from the two machines, the replication falls apart if a main server has a disk crash. –  Dana the Sane Jun 23 '09 at 20:23

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.

share|improve this answer

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
share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.