Use LVM for MySQL backup and synchronization

If someone asks me about MySQL Backup advice my question would is if they have LVM installed or have some systems WI Th similar features set for the other operation systems. Veritas File System can do it for Solaris. Most SAN Systems would work as.

If someone asks me a question about MySQL backup, I'll ask first whether the operating system has LVM or other similar features installed in the software. The same functionality can be done with the Veritas file system under Solaris. Most SAN systems are also available.

What is really needed are ability to create atomic snapshot of the volume, which can be later mounted same as original file System

Why snapshot based MySQL backups are great?

Why is backup based on MySQL snapshots good?

There are number of reasons:

The reasons are as follows:

Almost hot backup in most cases can perform this type of backup while your the IS application. No need to shut down server and make it read only or anything like it.

Almost hot standby in most cases, backups can be performed while the application is still running. You do not need to shut down, just set to read-only or similar to a read-only limit.

Support for "All" local disk based storage engines it works with MyISAM and Innodb, it BDB also and should, Primext and Falcon storage engines.

Supports all local disk based storage engines it supports MyISAM, Innodb, BDB, and also supports Solid, Primext, and Falcon.

Fast Backup You simply does file copy in the binary form so it's hard to beat in speed.

Fast backups only need to copy binary format files, unmatched in speed.

Low overhead It's simply file copy so overhead to the server is minimal.

Low overhead is just a copy of a file, so the cost to the server is subtle.

Easy to integrate does your want to compress backup? Backup it to tape, FTP or any network backup software-it are easy as you just need to copy files.

Easy to maintain integrity do you want to compress backup files? Backing them up to tape, FTP, or network backup software-is simple because you only need to copy files.

Fast Recovery Recovery time are as fast as putting data back and standard MySQL crash Recovery, and it can be reduced even further. More on this later.

Fast recovery recovery time and standard MySQL crash recovery or data copying back so fast, and possibly even faster, will be quicker in the future.

Free No extra commercial tools as INNODB hot backup are required to perform backup.

No additional commercial software is required for free, just innodb the hot standby tool to perform the backup.

Are there any downsides?

What's the downside?

Need to have snapshot campatibility-this is obvious one.

Need a compatible snapshot-this is obvious.

may need root access in some organizations DBA and System Administrator are different people from different departmnents W Hich might to trade access rights between all other.

Require Superuser (root) in some organizations, DBAs and system administrators from different departments, so permissions vary.

Hard to predict downtime I mentioned this solution are often hot backup, but bad thing the it is Hard to estimate the It is Ho T and when it's Not-flush TABLES with READ LOCK may take quite a while to complete in systems with long queries.

Downtime not expected the method I mentioned usually refers to hot standby, but no one can predict whether it is hot standby-FLUSH TABLES with READ LOCK may take a long time to complete.

Problems with data on multiple volumes If to have logs on separate devices or just your database spanning across multiple Volumes you'll be in trouble as you'll not have consistent snapshot across all the database. Some systems May is able to do atomic snapshot of many.