mysql備份、恢復(fù)之mysqldump

  mysqldump是mysql用于轉(zhuǎn)存儲(chǔ)數(shù)據(jù)庫(kù)的實(shí)用程序。它主要產(chǎn)生一個(gè)SQL腳本，其中包含從頭重新創(chuàng)建數(shù)據(jù)庫(kù)所必需的命令CREATE TABLE INSERT等

 mysqldump 語(yǔ)法 : 

  默認(rèn)配置讀取路徑:  /etc/mysql/my.cnf     /etc/my.cnf    ~/.my.cnf  

   Usage: mysqldump [OPTIONS] database [tables] 

   OR   mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] 

   OR   mysqldump [OPTIONS] --all-databases [OPTIONS]

常用參數(shù) :  
    -u, --user=name         指定用戶(hù) 
    -h, --host=name         指定主機(jī) 
    -p, --password[=name]      指定密碼 
    -A, --all-databases       指定所有庫(kù),-A與--all-databases不同時(shí)使用 
    -B, --databases         只指定--databases表示備份單個(gè)數(shù)據(jù)庫(kù);多數(shù)據(jù)庫(kù)之間使用空格隔開(kāi);
                     如果不指定--databases表示備份整個(gè)數(shù)據(jù)庫(kù);只指定dbname表示備份單張表；多表之間使用空格隔開(kāi)
    --master-data[=#]         0 表示關(guān)閉,默認(rèn)值為 0 
                     1 表示開(kāi)頭打印CHANGE MASTER命令信息,在復(fù)制場(chǎng)景中有用 
                     2 表示添加注釋信息,記錄當(dāng)前所處二進(jìn)制日志及所處位置 
    --single-transaction       如果全部為 InnoDB引擎表,則不需要使用 --lock-all-tables;
                     基于此選項(xiàng)能實(shí)現(xiàn)熱備InnoDB表;會(huì)啟動(dòng)單個(gè)大事務(wù),對(duì)全局服務(wù)影響可能是持久的
    --lock-all-tables         執(zhí)行備份前請(qǐng)求鎖定表,一般只鎖定當(dāng)前要備份的數(shù)據(jù)庫(kù)及表 
    --add-drop-database        在恢復(fù)之前先刪除已經(jīng)存在的對(duì)應(yīng)數(shù)據(jù)庫(kù) 
    --add-drop-table         在恢復(fù)之前先刪除已經(jīng)存在的對(duì)應(yīng)表 
    -C, --compress          將數(shù)據(jù)先壓縮后通過(guò)網(wǎng)絡(luò)傳輸 
    -E, --events           備份數(shù)據(jù)同時(shí)，備份事件調(diào)度器代碼 
    -R, --routines          備份數(shù)據(jù)同時(shí)，備份存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù) 
    -d, --no-data          表示只備份表結(jié)構(gòu)不備份數(shù)據(jù),復(fù)制表結(jié)構(gòu)、空庫(kù)至其它服務(wù)器時(shí)有用.其它任何時(shí)候不可用  
    --opt               同時(shí)啟動(dòng)各種高級(jí)選項(xiàng)

 備份方式 : 

    全量+增量+二進(jìn)制日志 

      前提需要在配置中添加參數(shù) : 

        default-storage-engine = InnoDB       設(shè)置默認(rèn)存儲(chǔ)引擎為InnoDB 

        innodb_file_per_table=1            設(shè)置每表單個(gè)表空間 

        log-bin=mysql-bin                啟用二進(jìn)制功能

備份過(guò)程 : 
    全量備份 : 
      使用InnoDB引擎,備份使用--single-transaction選項(xiàng)不需要鎖定表即實(shí)現(xiàn)熱備;使用非InnoDB引擎,備份需要使用--lock-all-tables選項(xiàng)進(jìn)行請(qǐng)求鎖定所有表后開(kāi)始備份
 
      mysqldump -uroot -pmypass --single-transaction --all-databases --master-data=2 > /backup/mysql_fullbak.sql
 
    增量備份 : 
      mysql>SHOW MASTER STATUS;              查看當(dāng)前處于哪個(gè)日志文件及位置 
         less  /backup/mysql_fullbak.sql     查看從哪個(gè)位置開(kāi)始 
      mysqlbinlog --start-position=4579 --stop-position=4868 mysql-bin.000015 > /backup/increment.sql  指定開(kāi)始及結(jié)束位置導(dǎo)出成增量備份
      
    二進(jìn)制日志備份: 
      mysql>INSERT INTO test (Name,Age) VALUES ('tom',18);       增量備份后,插入一條數(shù)據(jù)
      mysql>DROP DATABASE hello;                                 此時(shí)刪除數(shù)據(jù)庫(kù)
      mysql>SHOW MASTER STATUS;                           查看當(dāng)前處于哪個(gè)日志文件及位置
      mysqlbinlog --start-position=4868 mysql-bin.000015  查看數(shù)據(jù)庫(kù)崩潰前二進(jìn)制日志開(kāi)始位置,結(jié)束位置必須是數(shù)據(jù)庫(kù)刪除之前位置
      mysqlbinlog --start-position=4868 --stop-position=5062 mysql-bin.000015 > /tmp/hello.sql 將二進(jìn)制日志啟動(dòng)時(shí)位置至崩潰前位置數(shù)據(jù)導(dǎo)出

恢復(fù)過(guò)程 : 
    前提 : 清空數(shù)據(jù)目錄下所有文件,重新初始化數(shù)據(jù)庫(kù) 
      mysql>SET GLOBAL sql_log_bin=0;      恢復(fù)時(shí)需要臨時(shí)關(guān)閉二進(jìn)制日志,如不關(guān)閉,恢復(fù)數(shù)據(jù)將記錄進(jìn)二進(jìn)制日志增加日志管理難度
      mysql>FLUSH LOGS;                    手動(dòng)對(duì)二進(jìn)制日志進(jìn)行一次滾動(dòng)
      mysql < /backup/mysql_fullbak.sql    恢復(fù)全量備份
      mysql < /backup/increment.sql        恢復(fù)增量備份
      mysql < /tmp/hello.sql               恢復(fù)崩潰前導(dǎo)出二進(jìn)制數(shù)據(jù)
      mysql>SET GLOBAL sql_log_bin=1;      恢復(fù)完成開(kāi)啟二進(jìn)制日志

  恢復(fù)后需要使用 mysqlcheck -uroot -pmypass -c -all-databases 檢查所有表是否存在問(wèn)題,所有恢復(fù)完成后請(qǐng)記得對(duì)數(shù)據(jù)庫(kù)做一次全量備份

  至此,使用mysqldump備份恢復(fù)完成,如有錯(cuò)誤請(qǐng)大家指教！