If someone asks me about MySQL Backup advice my first question would be if they have LVM installed or have some systems with similar features set for other operation systems. Veritas File System can do it for Solaris. Most SAN systems would work as well.
如果有人问我关于MySQL备份的建议,我会先问操作系统是否安装了LVM或者其他相似功能的软件。Solaris下的Veritas文件系统也可以做到同样的功能。大多数SAN系统也可以。
What is really needed is 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 ?
为什么基于MySQL快照的备份很好?
There are number of reasons:
原因如下几点:
Almost Hot backup In most cases you can perform this type of backup while your application is running. No need to shut down server, make it read only or anything like it.
几乎是热备 在大多数情况下,可以在应用程序仍在运行的时候执行备份。无需关机,只需设置为只读或者类似只读的限制。
Support for all local disk based storage engines It works with MyISAM and Innodb and BDB, It also should work with Solid, PrimeXT and Falcon storage engines.
支持所有基于本地磁盘的存储引擎 它支持MyISAM, Innodb, BDB,还支持 Solid, PrimeXT 和 Falcon。
Fast Backup You simply do file copy in the binary form so it is hard to beat in speed.
快速备份 只需拷贝二进制格式的文件,在速度方面无以匹敌。
Low Overhead It is simply file copy so overhead to the server is minimal.
低开销 只是文件拷贝,因此对服务器的开销很细微。
Easy to Integrate Do you want to compress backup ? Backup it to tape, FTP or any network backup software - it is easy as you just need to copy files.
容易保持完整性 想要压缩备份文件吗?把它们备份到磁带上,FTP或者网络备份软件 -- 十分简单,因为只需要拷贝文件即可。
Fast Recovery Recovery time is as fast as putting data back and standard MySQL crash recovery, and it can be reduced even further. More on this later.
快速恢复 恢复的时间和标准的MySQL崩溃恢复或数据拷贝回去那么快,甚至可能更快,将来会更快。
Free No extra commercial tools as Innodb Hot Backup are required to perform backup.
免费 无需额外的商业软件,只需Innodb热备工具来执行备份。
Are there any downsides ?
有什么缺点吗?
Need to have snapshot campatibility - this is obvious one.
需要兼容快照 -- 这是明显的。
May need root access In some organizations DBA and System Administrator are different people from different departmnents which might not like to trade access rights between each other.
需要超级用户(root) 在某些组织,DBA和系统管理员来自不同部门不同的人,因此权限各不一样。
Hard to predict downtime I mentioned this solution is often hot backup, but bad thing it is hard to estimate when it is hot and when it is not - FLUSH TABLES WITH READ LOCK may take quite a while to complete on systems with long queries.
停工时间无法预计 我提到的这个方法通常指热备,但是谁也无法预料到底是不是热备 -- FLUSH TABLES WITH READ LOCK 可能会需要执行很长时间才能完成。
Problems with data on multiple volumes If you have logs on separate devices or just your database spanning across multiple volumes you will be in trouble as you will not get consistent snapshot across all the database. Some systems may be able to do atomic snapshot of many volumes.