-1

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?

Sam Cogan
  • 38,736
  • 6
  • 78
  • 114
hkshambesh
  • 63
  • 3
  • 7

6 Answers6

3

Use its replication facilities

2

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.

rkthkr
  • 8,618
  • 28
  • 38
0

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

Max Alginin
  • 3,284
  • 15
  • 11
0

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.

Aaron Digulla
  • 974
  • 3
  • 15
  • 25
  • 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 06 '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 06 '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 06 '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 06 '09 at 15:59
  • Sounds like an ugly hack to me – Tom Leys Oct 06 '09 at 21:44
  • @Tom: Databases must survive a sudden power loss, so it's a creative application of a feature. Many professionals DBs today allow live backups without heavy performance cuts but if yours doesn't, this is a way out. – Aaron Digulla Oct 07 '09 at 07:19
  • doesn't RAID system slow the process down as its working on two database, will this affect the live application?? –  Oct 07 '09 at 08:30
  • A good RAID system will both make your DB faster and more reliable. Writing would make the system slower (since it has to write each block two times) but a) the RAID writes both blocks at the same time in parallel, and b) both the RAID controller and your hard disks have large caches, so most of the time, you get the speed your cables allow. – Aaron Digulla Oct 07 '09 at 08:50
0

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.

longneck
  • 23,082
  • 4
  • 52
  • 86
0

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.

MarkR
  • 2,928
  • 17
  • 13