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.

Backing up large database with large amount of data in a small time limit then restoring it within a small time of limit?

Any useful ideas?

share|improve this question

closed as unclear what you're asking by Ward, Michael Hampton Sep 27 '13 at 23:55

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

    
how about knowing what DB, Operating System, Tools, data size? –  Martín Marconcini Oct 6 '09 at 11:51
    
Mind indicating the scale of time? 1 ms? 1s? 1m? 10m? 1h? –  sybreon Oct 6 '09 at 13:18
    
I'm assuming this is a rephrased version of serverfault.com/questions/71678/… would be nice to know what is meant by large and as sybreon posted, time scale? –  Rodent43 Oct 6 '09 at 14:02
    
i meant by large- a database contains millions of data rows which needs to backed-up and restored in every day basis. –  hkshambesh Oct 6 '09 at 14:28
    
Can you explain what you're trying to do. Why do you need to backup and restore every day? –  David Pashley Oct 6 '09 at 15:42

6 Answers 6

up vote 2 down vote accepted

mylvmbackup is a tool for quickly creating backups of a MySQL server's data files. To perform a backup, mylvmbackup obtains a read lock on all tables and flushes all server caches to disk, creates a snapshot of the volume containing the MySQL data directory, and unlocks the tables again. The snapshot process takes only a small amount of time. When it is done, the server can continue normal operations, while the actual file backup proceeds.

share|improve this answer

Use its replication facilities

share|improve this answer
    
+1 The most feasible suggestion. 0 downtime. –  Tom Leys Oct 6 '09 at 21:42

It would be nice to know what OS we're dealing with, but assuming it's a recent Linux distro, your best bet will be using LVM snapshots. The net is full of relevant recipes and tools, here's one link that lists the most popular ones:

http://forums.mysql.com/read.php?28,204733,204733

share|improve this answer

Put the database on a hardware RAID system which allows mirroring.

For the backup, simply disable the mirroring. That will give you two exact copies of the database. Keep one "live". Run the rebuild on the other one (to clean up the possibly inconsistent state by "switching it off") and then back it up. You can take your time since the live DB still answers requests.

Enable the mirror to pull in any updates that have happened while you did a backup.

For a restore, also disable the mirror and restore into the "dead" part. After the restore has finished, enable the mirror again in such a way that the "live" database gets overwritten with the restore. You will have a few seconds of downtime for the DB server at this point since you must turn the "live" DB off and point the server to the restored DB. There is one issue to be aware of: Any changes made to the live database will be lost. Usually, this is not a bit problem, though: You must do the restore because the database is corrupt.

So you either have no live database at all or you have to run it in a "read only" state or something. If the database is not completely corrupt, you can restore the data into a third one and simply copy the few tables you need (or use the "dead" part of the mirror as a scrap space).

This approach gives you the least possible amount of downtime.

share|improve this answer
    
But you have to be sure that the state of the database is valid to be copied while the DB is running. –  Martin Beckett Oct 6 '09 at 13:27
    
@mgb: That's the job of the RAID system. It has to make sure that the two copies of the data are always the same. Therefore, the DB is always in the same state on both copies. –  Aaron Digulla Oct 6 '09 at 15:34
    
yes, the disks are in the same state. but the database is not in a CONSISTENT state if you are using a non-transactional database engine, such as myisam. –  longneck Oct 6 '09 at 15:48
    
@longneck: It's consistent in the live copy. In the detached mirror copy, you must run a rebuild but since that doesn't influence the live copy, customers won't notice. Then you can do a backup. This can take some time but again, the live copy isn't affected. –  Aaron Digulla Oct 6 '09 at 15:59
    
Sounds like an ugly hack to me –  Tom Leys Oct 6 '09 at 21:44

if you want a complete copy of an entire mysql server instance with the smallest downtime using only features present in mysql server, then do the following:

  1. send FLUSH TABLES WITH READ LOCK to close all the table handles and stop new ones from being created.
  2. make a copy the entire mysql data directory using your OS copy functions or a backup utility.
  3. send UNLOCK TABLES.

if you're using a filesystem that supports volume snapshots, then you can replace #2 with a snapshot instead.

share|improve this answer
    
I don't think this will work with InnoDB tables. –  Travis Beale Oct 6 '09 at 13:19
    
It will work fine with InnoDB tables provided you use a filesystem snapshot. –  MarkR Oct 6 '09 at 13:30

Use a filesystem snapshot of your Innodb database*. You will get a consistent copy of the files on restore, after InnoDB has finished rolling back any transactions which were in progress etc.

To backup, you'll need to :

  1. Take a filesystem snapshot OR if your filesystem doesn't support it, take a block device snapshot
  2. If necessary, mount the fs or block device snapshot, allowing for some filesystem repair time if needed (the db will still be online at this point)
  3. Back up the mounted snapshot using your normal backup tools
  4. Unmount and/or delete the snapshot as necessary.

To restore it, shut down mysql, wipe out your old data files, then restore the files into the mysql data directory. When you restart the mysql instance, it will recover from any transactions which were in progress at the time of the backup (by rolling them back) then come up normally. This may take a while of course.

* of course you'll have some MyISAM tables in the "mysql" system database. Make sure they aren't changing during the snapshot.

share|improve this answer

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