PDA

View Full Version : Logical vs. Raw vs. Snapshot



zmanda_jacob
May 5th, 2008, 03:49 PM
After specifying the MySQL server that you want to backup, and where you would like to backup your data, you are then asked what method of backup you would like to do. Logical and raw are the two choices, and it is important to know the differences between the two in order to determine which type of backup you would like to do.

Logical backups are mysqldumps of the SQL statements contained within the databases. They work well with most storage engines types(except NDB), and they are portable so you can backup from one server and restore to another server of a different MySQL version or operating system. Because a read lock is place on the database for the duration of the backup, the impact of running a logical backup can be severe if the size of your backup is very large.

Raw backups are raw copies of the binary files used by the database that are taken using mysqlhotcopy. They have the benefit of being smaller than logical backups because they are the exact same size as the database. The read lock is still held on the database for the duration of the backup, but this will be smaller as the backup is smaller. One way to have an extremely small read lock regardless of the size of the database is to use raw snapshots.

Snapshot backups are done by a series of events which creates a very short read lock, flushes the binary logs to disk, takes a file system snapshot, and releases the lock on the database. After the database read lock has been lifted, the data from the snapshot is copied to the ZRM server. In ZRM 2.2 there is also an option to leave the snapshot on the MySQL server. A database of even a terabyte in size will have a very short read lock because of the efficiency of snapshots. The ZRM for MySQL currently supports LVM, VSS, ZFS, VxFS, and NTAP snapshots.