MySQL什么是物理備份（lvm-snapshot）

1、flush table with read locak;    2、create snapshot   3、show master status;　show slave status; [可選] 4、unlock tables; 5、Copy files from the snapshot 6、Unmount the snapshot.  7、Remove snapshot

快照備份： 1.遷移數(shù)據(jù)到邏輯卷（不是必須，視情況而定） 2.鎖表（時(shí)間） 3.給數(shù)據(jù)庫所在的邏輯卷拍快照 4.解鎖 5.將快照掛載到臨時(shí)掛載點(diǎn)上 6.將快照上的所有數(shù)據(jù)拷貝到相應(yīng)的備份目錄里（不同主機(jī)） 7.卸載快照并刪除

[root@Admin?~]#?pvcreate?/dev/sdb [root@Admin?~]#?vgcreate?vg01?/dev/sdb [root@Admin?~]#?lvcreate?-n?lv_mysql?-L?4G?vg01 [root@Admin?~]#?mkfs.ext4?/dev/mapper/vg01-lv_mysql

1>先停止應(yīng)用 2>停止mysql服務(wù) [root@Admin?~]#?service?mysqld?stop 3>備份所有的數(shù)據(jù)文件到指定的地方 [root@Admin?~]#?tar?-czvf?/tmp/backmysql/mysql.tar.gz?/data/DB/* 4>掛載邏輯卷到當(dāng)前mysql的數(shù)據(jù)目錄里 [root@Admin?~]#?mount?/dev/mapper/vg01-lv_mysql?/data/DB/ 5>將剛剛備份的數(shù)據(jù)解壓到數(shù)據(jù)目錄里 [root@Admin?~]#?tar?xf?/tmp/backmysql/mysql.tar.gz?-C?/data/DB/ [root@Admin?~]#?mv?/data/DB/data/DB/*?/data/DB/?&&?rm?-rf?/data/DB/data/ 6>啟動(dòng)數(shù)據(jù)庫 [root@Admin?~]#?service?mysqld?start 此處啟動(dòng)失敗原因/data/DB/數(shù)據(jù)目錄的權(quán)限變成了root， 更改權(quán)限重新啟動(dòng) [root@Admin?~]#?chown?mysql.?-R?/data/DB/?&&?service?mysqld?start

mysql>?flush?table?with?read?lock;

[root@Admin?~]#?lvcreate?-n?lv_mysql_s?-L?50M?-s?/dev/vg01/lv_mysql [root@Admin?~]#?dmsetup?--tree?ls vg01-lv_mysql?(253:0) ?└─vg01-lv_mysql-real?(253:1) ????└─?(8:16) vg01-lv_mysql_s?(253:3) ?├─vg01-lv_mysql_s-cow?(253:2) ?│??└─?(8:16) ?└─vg01-lv_mysql-real?(253:1) ????└─?(8:16)

[root@Admin?~]#?unlock?tables

[root@Admin?~]#?echo?"flush?tables?with?read?lock;?system?lvcreate?-n?lv_mysql_s?-L?50M?-s?/dev/vg01/lv_mysql;unlock?tables;"?|mysql?-p123

[root@Admin?~]#?mkdir?/mnt/mysql?&&?mount?/dev/vg01/lv_mysql_s?/mnt/mysql/

[root@Admin?~]#?ls?/mnt/mysql/????#?可以看到新的掛載目錄里面的數(shù)據(jù) Admin.pid??db01?????ib_logfile0??mysql?????????????mysql-bin.000003??mysql-bin.000006??mysql-bin.000009??performance_schema auto.cnf???db02?????ib_logfile1??mysql-bin.000001??mysql-bin.000004??mysql-bin.000007??mysql-bin.000010??test binlog?????ibdata1??login????????mysql-bin.000002??mysql-bin.000005??mysql-bin.000008??mysql-bin.index [root@Admin?~]#?mkdir?/backup?&&?rsync?-av?/mnt/mysql?/backup

[root@Admin?~]#?umount?/mnt/mysql/?&&?lvremove?/dev/vg01/lv_mysql_s

????1>我們來點(diǎn)狠的，直接把mysql的數(shù)據(jù)目錄/data/DB/刪除。 ????[root@Admin?~]#?rm?-rf?/data/DB/*?&&?ls?/data/DB/ ????2>刪除后可以看到重啟mysql直接報(bào)錯(cuò)了 ????[root@Admin?~]#?service?mysqld?restart? ????MySQL?server?PID?file?could?not?be?found!??????????????????[失敗] ????Starting?MySQL...The?server?quit?without?updating?PID?file?[失敗]/DB/Admin.pid). ????3>根據(jù)上面的/backup里面?zhèn)浞莸臄?shù)據(jù)進(jìn)行恢復(fù) ????[root@Admin?~]#?mv?/backup/mysql/*?/data/DB/ ????[root@Admin?~]#?ls?/data/DB/ ????auto.cnf??db02?????????ib_logfile1??mysql-bin.000001??mysql-bin.000004??mysql-bin.000007??mysql-bin.000010????test ????binlog????ibdata1??????login????????mysql-bin.000002??mysql-bin.000005??mysql-bin.000008??mysql-bin.index ????db01??????ib_logfile0??mysql????????mysql-bin.000003??mysql-bin.000006??mysql-bin.000009??performance_schema ????4>重新啟動(dòng) ????[root@Admin?~]#?chown?mysql.?/data/DB/?-R? ????[root@Admin?~]#?service?mysqld?restart ????這里權(quán)限更改了如果啟動(dòng)還是報(bào)錯(cuò)的話，查看下是否mysql進(jìn)程還存在，如果存在，將其kill掉再重啟就OK ????[root@Admin?~]#?mysql?-p123 ????mysql>?show?databases; ????+--------------------+ ????|?Database???????????| ????+--------------------+ ????|?information_schema?| ????|?binlog?????????????| ????|?db01???????????????| ????|?db02???????????????| ????|?login??????????????| ????|?mysql??????????????| ????|?performance_schema?| ????|?test???????????????| ????+--------------------+ rows?in?set?(0.00?sec)

#!/bin/bash #LVM BackMysql back_dir=/backup/`date +%F` [ -d $back_dir ]  mkdir -p $back_dir echo "flush tables with read lock; system lvcreate -n lv_mysql_s -L 50M -s /dev/vg01/lv_mysql;unlock tables;" |mysql -p123 mount /dev/vg01/lv_mysql_s /mnt/mysql/ rsync -a /mnt/mysql/ $back_dir if [ $? -eq 0 ];then     umount /mnt/mysql/ && lvremove -f /dev/vg01/lv_mysql_s fi

正常安裝MySQL： 1. 安裝系統(tǒng) 2. 準(zhǔn)備LVM，例如 /dev/vg_back/lv-mysql，mount /usr/local/mysql 3. 源碼安裝MySQL到 /usr/local/mysql  可選操作：　將現(xiàn)在的數(shù)據(jù)遷移到LVM 1. 準(zhǔn)備lvm及文件系統(tǒng) # lvcreate -L 2G -n lv-mysql vg_back # mkfs.ext4 /dev/vg_back/lv-mysql   2. 將數(shù)據(jù)遷移到LVM # service mysqld stop # mount /dev/vg_back/lv-mysql /u01/ //臨時(shí)掛載點(diǎn) # rsync -va /usr/local/mysql/ /u01/ //將MySQL原數(shù)據(jù)鏡像到臨時(shí)掛載點(diǎn)  # umount /u01/ # mount /dev/vg_back/lv-mysql /usr/local/mysql //加入fstab開機(jī)掛載 # df -Th /dev/mapper/vg_back-lv-mysql ext4 2.0G 274M 1.7G 15% /usr/local/mysql # service mysqld start  手動(dòng)基于LVM快照實(shí)現(xiàn)備份： 1. 加鎖 mysql> flush table with read lock;  2.創(chuàng)建快照 # lvcreate -L 500M -s -n lv-mysql-snap /dev/vg_back/lv-mysql # mysql -uroot -p123 -e 'show master status' > /backup/`date +%F`_position.txt 或者 mysql> show master status; +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | mysqld-bin.00003 | 135 | | | | +-------------------+----------+--------------+------------------+-------------------+  3. 釋放鎖 mysql> unlock tables;  4. 從快照中備份 # mount -o ro /dev/vg_back/lv-mysql-snap /u01/ # mkdir /backup/`date +%F` # rsync -a /u01/ /backup/2015-07-02/  5. 移除快照 # umount /u01/ # lvremove -f /dev/vg_back/lv-mysql-snap  腳本 + Cron完成： #!/bin/bash #LVM backmysql... back_dir=/backup/`date +%F`  [ -d $back_dir ] || mkdir -p $back_dir  mysql -uroot -p123 -e 'flush table with read lock' lvcreate -L 500M -s -n lv-mysql-snap /dev/vg_back/lv-mysql mysql -uroot -p123 -e 'show master status' |grep mysql > $back_dir/position.txt mysql -uroot -p123 -e 'flush logs' mysql -uroot -p123 -e 'unlock tables'  mount -o ro /dev/vg_back/lv-mysql-snap /u01  rsync -a /u01/ $back_dir  if [ $? -eq 0 ];then umount /u01/ lvremove -f /dev/vg_back/lv-mysql-snap fi =============================================================== mylvmbackup  功能：利用LVM快照實(shí)現(xiàn)物理備份，即LVM快照備份的自動(dòng)版  安裝perl模塊 1. 在線安裝 http://www.lenzg.net/mylvmbackup 它依賴于perl 模塊，可用以下命令安裝 perl -MCPAN -e 'install Config::IniFiles'  2. 離線安裝 # rpm -ivh mylvmbackup-0.16-0.noarch.rpm warning: mylvmbackup-0.16-0.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID b27291f2: NOKEY error: Failed dependencies: perl(Config::IniFiles) is needed by mylvmbackup-0.16-0.noarch perl(Date::Format) is needed by mylvmbackup-0.16-0.noarch perl(File::Copy::Recursive) is needed by mylvmbackup-0.16-0.noarch  解決： # yum -y localinstall atrpms-77-1.noarch.rpm perl-File-Copy-Recursive-0.38-1.el6.rfx.noarch.rpm perl-IO-stringy-2.110-1.2.el6.rfx.noarch.rpm perl-Config-IniFiles-2.56-1.el6.rf.noarch.rpm  安裝mylvmbackup軟件包 # yum -y install mylvmbackup-0.15-0.noarch.rpm 解決依賴關(guān)系perl-TimeDate  備份方法一： # mylvmbackup --user=root --password=123 --host=localhost --mycnf=/etc/my.cnf --vgname=vg_back --lvname=lv-mysql --backuptype=tar --lvsize=100M --backupdir=/backup  # tar xf backup-20140903_000236_mysql.tar.gz # ls backup backup-cnf-20150702_000236_mysql backup-20150702_000236_mysql.tar.gz backup-pos  備份方法二： # vim /etc/mylvmbackup.conf [mysql] #連接數(shù)據(jù)庫配置 user=root password=123456 host=localhost port=3306 socket=/tmp/mysql.sock mycnf=/etc/my.cnf [lvm] #LVM邏輯卷的配置 vgname=vg_server #卷組名稱 lvname=lv_mysql #邏輯卷名稱 backuplv=mysql_snap #快照卷名稱 lvsize=500M [fs] #文件系統(tǒng)配置 xfs=0 mountdir=/var/tmp/mylvmbackup/mnt/ #掛載目錄 backupdir=/backup #備份目錄，也可以備份到行程主機(jī) [misc] #定義備份選項(xiàng) backuptype=tar #定義備份的類型 backupretention=0 prefix=backup #定義備份文件名前綴 suffix=_mysql #定義備份文件名后綴 tararg=cvf #定義tar參數(shù)，默認(rèn)為cvf tarfilesuffix=.tar.gz #定義備份文件后綴名格式 datefmt=%Y%m%d_%H%M%S #定義備份文件名時(shí)間戳格式 keep_snapshot=0 #是否保留snaphot keep_mount=0 #是否卸載snaphot quiet=0 #定義記錄日志類型 注釋：其他配置保持輸入即可  然后直接執(zhí)行mylvmbackup即可  mylvmbackup 參考示例