mysql常用的備份命令與備份策略

標籤：mysql   備份   恢複   

MySQL常用的備份命令

mysqldump備份常用方法：
　1.匯出一個資料庫
    mysqldump -u使用者名稱 -p密碼 資料庫名 > 匯出的檔案名稱
    例如
     # mysqldump -hhostname -uroot -ppwd dataname >dataname.sql 

  2.備份伺服器上所有資料庫
　　 例如
      # mysqldump --all-databases > allbackupfile.sql
  3.備份一個表
　　mysqldump -u使用者名稱 -p密碼 資料庫名 表名> 匯出的檔案名稱
    例如：
　# mysqldump -hhostname -uroot -ppwd dataname specific_tabname1 specific_tabname2> dataname_specific_tabname.sql
　4.匯出一個資料庫結構
    例如
　　 # mysqldump -hhostname -uroot -ppwd  -–add-drop-table --no-data dataname > dataname_nodata.sql

　　--no-data:沒有資料
    –-add-drop-table 在每個create語句之前增加一個drop table命令

  5.直接將MySQL資料庫壓縮備份
    例如
　　# mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz

  6.同時備份多個MySQL資料庫
　　例如
   mysqldump -hhostname -uusername -ppwd --databases dbname1 dbname2 dbname3 > multibackupfile.sql

  7.對於支援事務級的儲存引擎，為了保證資料一致性可以採用選項“--single-transaction”
    例如
 
 # mysqldump -hlocalhost -uroot -p backup --single-transaction > /tmp/backup3.sql
 
  8.匯出特定格式的純文字檔案(產生兩個.sql和txt兩個檔案)
    例如：
 mysqldump -root -p -T /tmp dbname tablename --fields-enclosed-by=\" --fields-terminated-by=,
     
   --fields-enclosed-by：將欄位的內容封裝起來的符號
   --fields-terminated-by：兩個欄位的分隔字元
  
  如果一次dump多個表的話，每個表都會有sql和txt兩個檔案；這種特定格式比較適合備份
 
  產生特定格式除了mysqldump外，還可以使用如下語句：
  > select ... into outfile from ...
  例如：
     mysql> select * into outfile ‘/tmp/t2.txt1‘
    -> fields terminated by  ‘,‘           
    -> optionally enclosed by ‘"‘  
    -> lines terminated by ‘\n‘
    -> from t2 limit 10;                  

   optionally enclosed by：可以將欄位的內容“封裝”起來的，如果不使用“OPTIONALLY”則包括數字類型的所有類型資料都會被“封裝”，使用“OPTIONALLY”之後，則數字類型的資料不會被指 定字元“封裝”。
   lines terminated by：每條記錄結束的時候需要添加什麼字元 

  fields terminated by：設定每兩個欄位之間的分隔字元   
           
mysqldump的邏輯備份恢複方法

   1.如果是insert的語句，直接運行該指令碼即可
     方法一
  # mysql -hlocalhost -uroot -p backup < /tmp/t2.sql
  當還原表時，一定要指明把表還原到哪個資料庫，這裡是backup庫
 
  方法二
  在mysql裡用source運行指令碼
  mysql> source /tmp/t2.sql
 
   2. 如果備份是以特殊分隔字元分隔的純資料文字檔
   方法一
   把/tmp/t2.txt還原到庫backup
   # mysqlimport -uroot -p backup --fields-enclosed-by=\" --fields-terminated-by=, /tmp/t2.txt
 
    方法二
  mysql> load data infile ‘/tmp/t2.txt‘ into table t2 fields terminated by ‘"‘ enclosed by ‘,‘; 
 

mysqlhotcopy的備份方法
mysqlhotcopy 是一個用perl 編寫的使用程式，其主要實現原理實際上就是通過先LOCK
住表，然後執行FLUSH TABLES 動作，該正常關閉的表正常關閉，將該fsync 的資料都fsync，
然後通過執行OS 層級的複製（cp 等）命令，將需要備份的表或者資料庫的所有物理檔案都
複製到指定的備份組位置。

mysqlhotcopy db_name[./table_regex/] [new_db_name | directory]

例如：

複製資料庫
mysqlhotcopy  backup backup4

備份整庫
mysqlhotcopy  backup /tmp/

備份庫的部分表
mysqlhotcopy --allowold backup./t*/ /tmp/  
  
mysqlhotcopy的恢複方法
mysqlhotcopy 備份出來的是整個資料庫目錄，使用時可以直接拷貝到 mysqld 指定的 datadir目錄下即可，
同時要注意許可權的問題，(將 db_name 目錄的屬主改成 mysqld 運行使用者)。此命令只能恢複資料庫到最後
一次備份時的狀態。

 
mysql的不同情境備份策略

備份策略一：

直接拷貝資料庫檔案（不推薦），也稱為冷物理備份，即要停止mysql資料庫，然後copy物理檔案，
速度非常快，恢複也非常方便和快捷，直接把備份檔案原樣copy回來即可，但沒有應用允許停庫備份的。
這裡不存在資料一致性的問題，庫都停了，當然可以保證一致性了；如果不停庫的話，那就要鎖住所有
的表以保證資料的一致性
         FLUSH TABLES WITH READ LOCK;
   這個sql是把記憶體中的資料都重新整理到磁碟中，同時鎖定資料表，以保證拷貝過程中不會有新的資料寫入。
   
    恢複：此方案的資料恢複也很簡單，直接拷貝回原來的資料庫目錄下即可
   
備份策略二：

使用mysqlhotcopy備份資料庫（完全備份，適合小型Database Backup），這種情況對於資料一致性要求非常嚴的
系統來對，就是一個無效的備份，因為沒辦法滿足資料的一致完整性條件約束。它是備份資料庫或單個表的最
快的途徑，但它只能運行在資料庫檔案（包括資料表定義檔案、資料檔案、索引檔案）所在的機器上，並
且mysqlhotcopy 只能用於備份 MyISAM表。
            
     恢複：此方案的資料恢複也很簡單，直接拷貝回原來的資料庫目錄下即可
   
   

備份策略三：

使用mysqldump備份資料庫（完全+增量備份，適合中型Database Backup），mysqldump 是採用SQL
層級的備份機制，它將資料表導成 SQL 指令檔，在不同的 MySQL版本之間升級時相對
比較合適，這也是最常用的備份方法。mysqldump 比直接拷貝要慢些。對資料要求一致性非常嚴的
也很難保證，只能通過增量備份保證（適用任何引擎)

 

   恢複：此方案的恢複分為兩步：

   1.先恢複完全備份檔案，使用類似如下命令：
          mysql -hlocalhost -uroot -p backup < /tmp/t2.sql
    2.恢複增量備份檔案，按logbin的時間先後順序執行
        
        #  ls -tr1 HOSTNAME-bin* | xargs mysqlbinlog | mysql -uUser -pUserPWD

          
備份策略四：

使用主從複製機制(replication)（實現資料庫即時備份,適合中大型資料庫）,為了不影響線上業務，
實現線上備份，並且能增量備份，最好的辦法就是採用主從複製機制(replication)，在 slave 機器
上做備份。

mysql常用的備份命令與備份策略