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.
多卷上的數據問題 如果你把日志放在獨立的設備上或者你的數據庫分布在多個卷上，這就比較麻煩了，因為無法得到全部數據庫的一致性快照。不過有些系統可能能自動做到多卷快照。
Lets speak a bit about how LVM and snapshotting in general works. Really there are different implementations but the sake of them is to provide you with volume which consistently matches state of the volume at the time storage is created. In LVM it is implementeed as copy on write. Special storage area allocated on device where old version of changed pages are stored. You can think about it as about simplified form of versioning like in Innodb if it is closer to you. In other cases snapshot may be implemented by tripple-mirroring. Ie you have RAID1 volume but there are 3 copIEs of data rather than 2. So you can move one devices out of mirror and use it as snapshot while still having your data safe and secure.
現在講講LVM和通常情況下的快照。確實，它們的實現的方式不同，但是目的都是讓卷和存儲卷創建的數據保持一致。LVM是邊寫邊讀。設備中專門分配了一個專用的區域用於保存老版本的內存頁變化情況。你可以把它想象成為一個簡單的版本管理形式，就像Innodb。另一方面，快照實現的方式就像三重鏡像。如果你有RAID1卷，不過數據拷貝的分數是3而不是2。因此可以把一個設備從鏡像中去掉，把它作為數據快照，並且還能保持數據安全。
There are two types of snapshots - some of them are read-only while others can be read-write. read-only snapshots may sound good enough as you're only going to read data anyway, but in reality read-write snapshots have number of benefits. First no extra handling is needed for journaling file sytems - you can simply do journal recovery on snapshot. With read-only snapshot you need to make sure filesystem synchronizes device before snapshot is taken so no journal replay is needed.
快照有2種方式 -- 一種是只讀另一種是可讀寫。如果你只須拷貝數據，那麼只讀快照看起來不錯，不過可讀寫快照則有好幾個優點。首先是無需額外處理日至文件系統 -- 你可以快照上簡單地實現日志恢復。只讀快照則必須保證文件系統在開始快照之前就得和設備同步，因此需要日志重現。
The other benefit of read-write snapshot is you can actually start MySQL Server on it and perform recovery, check tables or do whatever else you might need to do to ensure your backup is consistent. Backing up database which was already corrupted is very nasty problem you want to avoid.
可讀寫快照的另一個好處是可以啟動MySQL服務器，執行恢復、檢察數據表或者其他任何要保證備份一致性所需的操作。必須避免備份已經損壞的數據庫。
Let's now see what exactly you need to do to perform backup of MySQL Database (or create slave) using LVM2 on Linux.
現在讓我們來看看Linux上用LVM2備份MySQL數據庫(或者它的slave)所需執行的操作：
1) Connect to MySQL and run FLUSH TABLES WITH READ LOCK
Note - this command may take a while to complete if you have long running queries. The catch here is FLUSH TABLES WITH READ LOCK actually waits for all statements to complete, even selects. So be careful if you have any long running querIEs. If you're using only Innodb tables and do not need to synchronize binary log position with backup you can skip this step.
1) 連接到MySQL上，運行 FLUSH TABLES WITH READ LOCK
注意 -- 如果你當前正在執行一個較長時間的查詢，那麼這個命令可能需要較長時間才能完成。在這裡 FLUSH TABLES WITH READ LOCK 需要捕獲所有的語句都完成了，甚至是 SELECT。因此如果有較長時間的查詢時要小心。如果你只使用到Innodb表，就無需同步二進制日志可直接略過這個步驟。
2) While holding connection open run: lvcreate -L16G -s -n dbbackup /dev/Main/Data - This will create snapshot named dbbackup for Logical Volume Main/Data . You should specify enough of undo space to hold modifications during backup process - I've specifIEd 16GB in this case. If your undo size is not large enough snapshot will get invalidated and backup will be aborted.
2) 保持連接，運行 lvcreate -L16G -s -n dbbackup /dev/Main/Data -- 它會創建本地卷 Main/Data 的快照，命名為 dbbackup。備份過程中務必指定足夠大的撤銷空間用於保存發生變化的東西 -- 我指定了16GB。如果撤銷空間不夠大，快照就會無效並且備份就終止了。
Sometimes you might run into the errors on this step, The most common one I've resently seen is: snapshot: Required device-mapper target(s) not detected in your kernel - This means snapshot module is not loaded in your kernel by default and you need to load it, which is done by running modprobe dm-snapshot
在這個步驟中，你有時候會碰到錯誤，最近我經常碰到的一個是：snapshot: Required device-mapper target(s) not detected in your kernel -- 意思是內核默認沒有加載快照模塊，需要你自己加載，運行命令 modprobe dm-snapshot 即可。
3) Now you have created logical volume and can unlock the tables, but before that you should probably record binary log position which is done by running SHOW MASTER STATUS - This is binary log position you'll need to point your MySQL Slaves created from this snapshot.
3) 現在已經創建完本地邏輯卷，可以釋放表鎖了，不過在這之前，需要記錄一下二進制日志的位置，運行 SHOW MASTER STATUS 可以看到 -- 這個位置在MySQL slave上創建快照時需要用到。。
4) Snapshot created, now you want to let MySQL Server to continue, which is done by running UNLOCK TABLES or simply closing connection.
4) 快照創建完畢，運行 UNLOCK TABLES 釋放鎖或者關閉連接，讓MySQL服務器繼續運行。
5) Mount backup Filesystem: mount /dev/Main/dbbackup /mnt/backup
5) 掛載備份文件系統：mount /dev/Main/dbbackup /mnt/backup
6) Copy data to backup. Normally you can skip slow query logs and error log while taking backup. You also can skip most of binary logs - however if some of your slaves are far behind you might want to keep some of last binary logs just in case, or you can assume in case of recovery from the backup you will need to restore slaves as well and skip binary logs in your backup process.
6) 拷貝備份數據。通常備份時可以略過慢查詢日志和錯誤日志。也可以略過大部分的二進制日志 -- 然而如果有些slave遠遠落後於master的話，就必須保留所需的二進制日志了，或者你可以假設這種情況下在slave上從備份上恢復數據也可以忽略掉二進制日志。
7) Unmount filesystem umount /mnt/backup
7) 卸載文件系統：umount /mnt/backup
8) Remove snapshot: lvremove -f /dev/Main/dbbackup
8) 刪除快照：lvremove -f /dev/Main/dbbackup
If you want to create slave based on such snapshot you need to perform couple of more simple steps
如果你想創建基於slave的快照，就需要多做2個步驟。
9) Extract/Copy database to the slave database directory.
9) 提取/拷貝數據庫到slave的數據庫目錄下。
10) Start MySQL Server. Wait for it to perform recovery.
10) 啟動MySQL服務器，等待執行恢復。
11) Use CHANGE MASTER TO to point slave to saved binary log position:
11) 用 CHANGE MASTER TO 告訴slave要保存的二進制日志位置:
PLAIN TEXT
SQL:
CHANGE
master
TO
master_host="master", master_user="user", master_password="passWord", master_log_file="host-bin.000335", master_log_pos=401934686;
12) Run SLAVE START to restart replication.
12) 運行 SLAVE START 重啟復制。
With slightly modifIEd process you can clone slaves from the slaves without stopping them - you just need to use SHOW SLAVE STATUS instead of SHOW MASTER STATUS to find out appropriate binary log position. Be careful however - cloning slave from the slave also clones inconsistences in data which slave could have accomulated - especially if you use slave_skip_errors or sql_slave_skip_counter. Cloning master you're starting from consistent copy.
使用稍微修改過的進程，就能無需停止slave的情況下克隆它 -- 運行 SHOW SLAVE STATUS 而不是 SHOW MASTER STATUS，找出合適的二進制日志位置。不過要小心 -- 克隆slave的時候也會把它積累的不一致的數據也克隆了 -- 尤其是使用 slave_skip_errors 或 sql_slave_skip_counter時。克隆master就可以拷貝保持一致的數據。
If you're interested in ready script you can try mylvmbackup by Lenz Grimmer
如果你對上面的過程有興趣，可以試試 Lenz Grimmer 的 mylvmbackup。
