MySQL/mariadb從刪庫到跑路——備份

標籤：因此   ref   redo   必須   sla   事件   name   資料表空間   開啟   

 

 

備份策略1、備份的類型

類型1：

• 熱備份：讀寫不受影響（MyISAM不支援熱備，InnoDB支援熱備）
• 溫備份：僅可以執行讀操作
• 冷備份：離線備份，讀寫操作均中止

類型2：

• 物理備份：複製資料檔案進行備份，佔用較多的空間，速度快
• 邏輯備份：將資料匯出至文字檔中，佔用空間少，速度慢，可能丟失精度

類型3：

• 完全備份：備份全部資料
• 增量備份：僅備份上次完全備份或增量備份以後變化的資料，備份較快，還原複雜
• 差異備份：僅備份上次完全備份以來變化的資料，備份較慢，還原簡單

2、備份需要考慮的因素

• 溫備的持鎖多久，在鎖狀態的情況下無法寫入資料
• 備份產生的負載，要調閒置時間備份
• 備份過程的時間長度，資料量大的時候時間會很長，要選擇合適的方案
• 恢複過程的時間長度，備份資料需要即時測試

3、備份的目標

• 資料庫資料，每個資料表空間單獨存放
• 二進位日誌，需要和資料分開儲存
• InnoDB的交易記錄
• 預存程序、儲存函數、觸發器或事件調度器等
• 伺服器的設定檔：/etc/my.cnf

 

4、備份工具

• mysqldump工具：邏輯備份工具，適用所有儲存引擎溫備；支援完全或部份備份；對InnoDB儲存引擎支援熱備；Schema（資料庫的定義）和資料存放區在一起。

用法：

shell> mysqldump [options] db_name [tbl_name ...]shell> mysqldump [options] --databases db_name ...shell> mysqldump [options] --all-databases

選項：

-A：備份所有庫-B db_name1,[db_name2,...]：備份指定庫-E：備份相關的所有event scheduler-R：備份所有預存程序和儲存函數--triggers：備份表相關觸發器，預設啟用,用--skip-triggers，不備份觸發器--master-data={1|2}：    1：所備份的資料之前加一條記錄為CHANGE MASTER TO語句，非注釋，不指定預設為1    2：記錄為注釋的CHANGE MASTER TO語句，注意：此選項會自動關閉--lock-tables功能，自動開啟--lock-all-tables功能（除非開啟--single-transaction）-F：備份前滾動日誌，鎖定表完成後，執行flush logs命令,產生新的二進位記錄檔，配合-A時，會導致重新整理多次資料庫，在同一時刻執行轉儲和日誌重新整理，則應同時使用--flush-logs和-x，--master-data或-single-transaction,此時只重新整理一次；建議：和-x，--master-data或 --single-transaction一起使用--compact 去掉注釋，適合調試，生產不使用-d：只備份表結構-t：只備份資料，不備份create table-n：不備份create database，可被-A或-B覆蓋--flush-privileges：備份前重新整理授權表，備份mysql庫或相關時需要使用-f：忽略SQL錯誤，繼續執行--hex-blob：使用十六進位符號轉儲二進位列（例如，“abc”變為0x616263），受影響的資料類型包括BINARY， VARBINARY，BLOB，BIT-q：不緩衝查詢，直接輸出，加快備份速度

MyISAM備份選項：支援溫備；不支援熱備，所以必須先鎖定要備份的庫，而後啟動備份操作
-x,--lock-all-tables：加全域讀鎖，鎖定所有庫的所有表，同時加--single-transaction或--lock-tables選項會關閉此選項功能，注意：資料量大時，可能會導致長時間無法並發訪問資料庫
-l,--lock-tables：對於需要備份的每個資料庫，在啟動備份之前分別鎖定其所有表，預設為on,--skip-lock-tables選項可禁用,對備份MyISAM的多個庫,可能會造成資料不一致

 

InnoDB備份選項：支援熱備，可用溫備但不建議用
--single-transaction：此選項Innodb中推薦使用，不適用MyISAM，此選項會開始備份前，先執
行START TRANSACTION指令開啟事務此選項通過在單個事務中轉儲所有表來建立一致的快照。僅適用於儲存在支援多版本控制的儲存引擎中的表（目前只有InnoDB可以）; 轉儲不保證與其他儲存引擎保持一致。

? 在進行單事務轉儲時，要確保有效轉儲檔案（正確的表內容和二進位日誌位置），需要保證沒有其他串連使用以下語句：ALTER TABLE，DROP TABLE，RENAME TABLE，TRUNCATE TABLE

? 此選項和 --lock-tables（此選項隱含提交掛起的事務）選項是相互排斥備份大型表時，建議將--single-transaction選項和--quick結合一起使用

InnoDB建議備份策略：

mysqldump –uroot –A –F –E –R  --single-transaction --master-data=1 --flush-privileges  --triggers --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql

MyISAM建議備份策略：

mysqldump –uroot –A –F –E –R –x --master-data=1 --flush-privileges  --triggers --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql

　　

• xtrabackup工具：由Percona提供支援對InnoDB做熱備(物理備份)的工具，支援完全備份、增量備份

由Percona公司提供的mysqlDatabase Backup工具，開源的能夠對innodb和xtradb資料庫進行熱備的工具；

xtrabackup 是用來備份 InnoDB 表的，不能備份非 InnoDB 表；

innobackupex 指令碼用來備份非 InnoDB 表，同時會調用 xtrabackup 命令來備份 InnoDB 表，還會和 MySQL Server 發送命令進行互動，如加全域讀鎖（FTWRL）、擷取位點（SHOW SLAVE STATUS）等。即innobackupex是在xtrabackup 之上做了一層封裝實現的；

雖然目前一般不用 MyISAM 表，只是 MySQL 庫下的系統資料表是 MyISAM 的，因此備份基本都通過 innobackupex 命令進行；

xtrabackup版本升級到2.4後，相比之前的2.1有了比較大的變化：innobackupex 功能全部整合到 xtrabackup 裡面，只有一個 binary程式，另外為了相容考慮，innobackupex作為 xtrabackup 的軟連結，即xtrabackup現在支援非Innodb表備份，並且Innobackupex在下一版本中移除，建議通過xtrabackup替換innobackupex。

參考手冊：https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html

使用innobakupex備份時，其會調用xtrabackup備份所有的InnoDB表，複製所有關於表結構定義的相關檔案(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相關檔案，同時還會備份觸發器和資料庫配置資訊相關的檔案。這些檔案會被儲存至一個以時間命名的目錄中,在備份時，innobackupex還會在備份目錄中建立如下檔案：

1）xtrabackup_checkpoints：備份類型（如完全或增量）、備份狀態（如是否已經為prepared狀態）和LSN(記錄序號)範圍資訊,每個InnoDB頁(通常為16k大小)都會包含一個記錄序號，即LSN。LSN是整個資料庫系統的系統版本號碼，每個頁面相關的LSN能夠表明此頁面最近是如何發生改變的；

2）xtrabackup_binlog_info：MySQL伺服器當前正在使用的二進位記錄檔及至備份這一刻為止二進位日誌事件的位置；

3）xtrabackup_info：innobackupex工具執行時的相關資訊；

4）backup-my.cnf：備份命令用到的配置選項資訊；

5）xtrabackup_logfile：備份產生的記錄檔。

用法：

innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/

選項：

--user：該選項表示備份帳號--password：該選項表示備份的密碼--host：該選項表示備份資料庫的地址--databases：該選項接受的參數為資料名，如果要指定多個資料庫，彼此間需要以空格隔開；如："xtra_test dba_test"，同時，在指定某資料庫時，也可以只指定其中的某張表。如："mydatabase.mytable"。該選項對innodb引擎表無效，還是會備份所有innodb表--defaults-file：該選項指定從哪個檔案讀取MySQL配置，必須放在命令列第一個選項位置--incremental：該選項表示建立一個增量備份，需要指定--incremental-basedir--incremental-basedir：該選項指定為前一次全備份或增量備份的目錄，與--incremental同時使用--incremental-dir：該選項表示還原時增量備份的目錄--include=name：指定表名，格式：databasename.tablename--apply-log：一般情況下,在備份完成後，資料尚且不能用於恢複操作，因為備份的資料中可能會包含尚未提交的事務或已經提交但尚未同步至資料檔案中的事務。因此，此時資料檔案仍處理不一致狀態。此選項作用是通過復原未提交的事務及同步已經提交的事務至資料檔案使資料檔案處於一致性狀態--use-memory：該選項表示和--apply-log選項一起使用，prepare 備份的時候，xtrabackup做crash recovery分配的記憶體大小，單位位元組。也可(1MB,1M,1G,1GB)，推薦1G--export：表示開啟可匯出單獨的表之後再匯入其他Mysql中--redo-only：此選項在prepare base full backup，往其中merge增量備份時候使用--copy-back：做資料恢複時將備份資料檔案拷貝到MySQL伺服器的datadir--move-back：這個選項與--copy-back相似，唯一的區別是它不拷貝檔案，而是移動檔案到目的地。這個選項移除backup檔案，用時候必須小心。使用情境：沒有足夠的磁碟空間同事保留資料檔案和Backup副本

注意：

1）datadir目錄必須為空白。除非指定innobackupex --force-non-empty-directorires選項指定，否則--copy-backup選項不會覆蓋；

2）在restore之前,必須shutdown MySQL執行個體，不能將一個運行中的執行個體restore到datadir目錄中；

3）由於檔案屬性會被保留，大部分情況下需要在啟動執行個體之前將檔案的屬主改為mysql，chown -R mysql:mysql /data/mysqldb

• mysqlbackup工具：熱備份，MySQL Enterprise Edition組件
• mysqlhotcopy工具：幾乎冷備，僅適用於MyISAM儲存引擎
• 基於lvm快照備份：幾乎熱備，需要在拍快照前鎖表
• tar + cp 等歸檔複製工具備份：完全冷備

 

MySQL/mariadb從刪庫到跑路——備份