MariaDB資料庫的備份和還原

                                 MariaDB資料庫備份和還原

-------------------------------------------------------------------------------------------------------------------------------------------

1、備份和恢複

    (1)為什麼要備份？

        災難恢複：硬體故障、軟體故障、自然災害、******、誤操作

        測試，做還原測試，用于測試備份的可用性

    (2)要注意的要點

        能容忍最多丢失多少資料，99.999%比99.99%成功率是多了10倍

        恢複資料需要在多長時間内完成，盡可能盡量短的時間内恢複完成

        需要恢複哪些資料     

2、備份類型：

    (1)完全備份，部分備份

    (2)完全備份：整個資料集

    (3)部分備份：隻備份資料子集

3、還原要點

    (1)做還原測試，用于測試備份的可用性

    (2)還原演練，便于在發生萬一時可以從容面對快速恢複資料

4、完全備份、增量備分、差異備份

    (1)增量備份：以某個固定時間為周期，這個時間内到上一次變化的資料就是增量，僅備份最近一次完全備份或增量備份（如果存在增量）以來變化的資料，還原時候完全備份+增量備份1+增量備份2+...增量備份n，從完全備份後有幾個增量備份就要加幾個增量備份，這種方式還原麻煩，但節省空間

    (2)差異備份：僅備份最近一次完全備份以來變化的資料，還原時候是完全備份+差異備份，相對增量備份比較占用空間

    (3)一般使用政策是完全備份+增量備份或者完全備份+差異備份，不能增量備份和差異備份混用

5、熱備份、溫備份、冷備份

    (1)熱備份：線上系統讀寫操作均可操作，mysql引擎不支援

    (2)溫備份：線上系統讀操作可執行，但寫操作不行

    (3)冷備份：線上系統讀寫操作不可執行

    注意：MyISAM引擎：溫備，不能熱備；InnoDB，引擎：可以做熱備、溫備

6、實體備份、邏輯備份

    (1)實體備份：直接複制資料檔案進行備份，隻需要直接複制即可

    (2)邏輯備份：從資料可中“導出”資料另存而進行的備份，需要專業的協定用戶端，與存儲引擎無關

7、備份時需要考慮的因素：

    溫備持鎖多久、備份過程的時長、備份負載、恢複過程的時長

8、備份内容

    資料、二進制日志和InnoDB的事務日志、代碼（存儲過程、存儲函數、觸發器、時間排程器）、伺服器的配置檔案

9、設計備份方案：

    (1)資料集：完全+增量

    (2)備份手段：實體，邏輯

10、備份工具

    (1)mysqldump：邏輯備份工具，适用所有存儲引擎，支援溫備，不支援熱備，但對于InnoDB存儲引擎支援熱備，該工具同時可做完全備份、部分備份

    (2)cp,tar等複制歸檔工具，實體備份工具，使用所有存儲引擎，隻能做冷備，可用來做完全備份和部分備份

    (3)lvm2的快照：幾乎熱備，借助于檔案系統管理工具

    (4)mysqlhotcopy：幾乎冷備，僅适用于MyISAM存儲引擎

    (5)備份工具的選擇

        <1>mysqldump+複制binlog（二進制日志），時間較慢，支援遠端操作

            mysqldump：完全備份

            複制binlog中指定時間範圍内的event完成做增量備份

        <2>lvm2快照+複制binlog

            lvm2快照：使用cp或tar等做實體備份，完全備份

        <3>xtrabackup

            由Percona提供的支援對InnoDB做熱備（實體備份）的工具

            完全備份、增量備份

11、邏輯備份工具

    mysqldump、mydumper、phpMyAdmin

    mysqldump：用戶端指令，通過mysql協定連接配接至mysqld伺服器

    mysqldump [OPTION] DBNAME [TBLNAME]，語句中帶有[]内内容代表不是必須有的

                -A,--all-databases

                -B DBNAME,--databases DBNAME [TBLNAME]

    差別： mysqldump DBNAME TBLNAME，不會自動建立資料庫，而-A,--all-databases和-B DBNAME,--databases DBNAME [TBLNAME]會自動建立資料庫

12、實作冷備

    (1)停止資料庫，進入資料庫存放目錄，tar Jcvf /DIR/SQL.tar /資料庫存放目錄/，過程中如果日志在其它目錄，也需要一并拷貝

    (2)還原：停止服務，解壓備份資料，拷貝至需要還原的資料庫，解壓縮，檢查使用者權限組權限是否都為mysql，之後配置檔案放到對應的位置後，重新開機資料庫服務

    注意：拷貝單個資料庫時候最好打包後拷貝至需要還原的資料庫，同時拷貝事務日志，同時注意檔案的使用者群組權限

13、邏輯卷快照實作資料庫幾乎熱備

    (1)建立兩個邏輯卷（一個給資料庫，一個給二進制日志），建立檔案系統，并挂載建立的備份存放目錄

    (2)vim /etc/my.cnf，在[mysqld_safe]下寫入資訊datadir=/資料庫備份存放目錄/

    (3)請求鎖定所有表：進入資料庫 FLUSH TABLES with READ LOCK;

    (4)記錄二進制日志檔案及事件位置

        <1>FLUSH LOGS;

        <2>SHOW MASTER STATUS;，或者mysql -e 'SHOW MASTER STATUS' > /DIR/pos.`date + F%`，記下binlog的時間點

    (5)建立快照lvcreate -L SIZE -n LVNAME-snap -p r -s /dev/LVMDIR/LVNAME，建立邏輯卷/dev/LVMDIR/LVNAME的快照，SIZE大小，名稱叫LVNAME-snap，隻讀r屬性

    (6)釋放鎖，進入資料庫UNLOCK TABLES;

    (7)挂載邏輯卷快照臨時到某個目錄

    (8)轉到快照目錄下，cp -a mysql/ /tmp

    (9)備份完成後，删除快照卷，lvremove

    (10)制定好政策後，通過原卷備份二進制日志，mysqlbinlog --start-position=7697 binlog.00001 > /root/binlog.sql，mysqlbinlog  binlog.00002 >>binlog.sql，7697是剛才記錄的binlog的時間點

    (11)還原，将/tmp/mysql/拷貝至需要還原的資料庫存放目錄，将binlog放置對應目錄中，mysql < 導入後重新開機mysql服務

14、mysqldump的使用

    (1)備份

        mysqldump -uUSER --databases DBNAME > /DIR/*.sql，用重定向方式備份一個資料庫

        mysqldump -uUSER --databases DB1 DB2 > /DIR/DB12.sql，備份多個資料庫

    (2)鎖表

        MyISAM、InnoDB實作溫備

        mysqldump --lock-all-tables：鎖定所有庫的所有表

              --lock-tables：對于每個單獨的資料庫，在啟動備份之前鎖定該資料庫所有表

        InnoDB實作熱備

        mysqldump --single-transaction，啟動一個事務，當完成時才會被送出，使用時配合-q一起使用，不适用MyISAM引擎，使用時候需要確定其他人不用增删改和truncate指令

    (3)其它選項

        mysqldump -E,--events：備份指定資料庫相關的所有event scheduler，事件排程器

                            -R,--routines：備份指定資料庫可相關的所有存儲過程和存儲函數

                            --triggers：備份表相關的觸發器

                            --master-data=NUM，此選項須啟用二進制日志，NUM為1：所備份的資料之前加一條記錄為CHANGE MASTER TO語句，非注釋，不指定#，預設為1，記錄二進制日志POS點，從此點之後沒做備份；NUM為2：記錄為注釋的CHANGE MASTER TO語句，此選項會自動關閉--lock-tables功能，自動打開-x | --lock-all-tables功能（除非開啟--single-transaction）

                            -F, --flush-logs ：備份前滾動日志，鎖定表完成後，執行flush logs指令,生成新的二進制日志檔案，配合-A 或 -B 選項時，會導緻重新整理多次資料庫。建議在同一時刻執行轉儲和日志重新整理，可通過和--single-transaction或-x，--master-data 一起使用實作，此時隻重新整理一次日志

                            --compact 去掉注釋，适合調試，生産不使用

                            -d, --no-data 隻備份表結構

                            -t, --no-create-info 隻備份資料,不備份create table

                            -n,--no-create-db 不備份create database，可被-A或-B覆寫

                            --flush-privileges 備份mysql或相關時需要使用

                            -f, --force 忽略SQL錯誤，繼續執行

                            --hex-blob 使用十六進制符号轉儲二進制列（例如，“abc”變為0x616263），受影響的資料類型包括BINARY， VARBINARY，BLOB，BIT的列時使用，避免亂碼，生産中帶有二進制日志資料建議加

                            -q, --quick 不緩存查詢，直接輸出，加快備份速度

15、完全備份，并還原至最新狀态

    (1)啟用二進制日志，并分離存放vim /etc/my.cnf，[mysqld]下log-bin=/DIR/

    (2)mysqldump -A -F --single-transaction --master-data=2 | gzip > /data/`date +%F`.sql.gz

    (3)模拟破壞，停止資料庫并删庫

    (4)還原到備份時狀态，直到還原結束一直不允許其他使用者連接配接資料庫，解壓資料備份，gzip -d *.sql.gz，systemctl start mariadb（資料庫無内容），關閉二進制，進入資料庫，mysql >set sql_log_bin=off，mysql > source / *.sql.gz

    (5)還原到最新狀态grep "CHANGE MASTER" /data/*.sql，檢視position，比如說245

        mysqlbin --start-potion=245 mysql-bin.0002 > /data/incr.sql

        mysqlbin mysql-bin.0003 >>  /data/incr.sql

        mysqlbin mysql-bin.0004 >> /data/incr.sql

        mysql

        >set sql_log_bin=off

        >source /data/incr.sql

        >set sql_log_bin=on

        附：備份時候壓縮：mysaldump -B DB | gzip > /DIR/*.sql.gz，解壓時候gzip -d

        注意：二進制日志檔案不應該與資料檔案放在同一磁盤

16、恢複誤删除

    (1)開啟二進制日志功能

    (2)完全備份：mysqldump -A -F --single-transaction --master-data=2 | gzip > /data/`date +%F`.sql.gz

    (3)修改資料庫：産生變化二進制檔案

        删除表：産生模拟事件

        修改資料庫

    (4)加讀鎖，flush tables with read lock，并拒絕使用者通路資料庫

    (5)還原：<1>停止服務，清空資料庫，啟動資料庫，解壓備份的資料

                  <2>mysql

                        > set sql_log_bin=off

                        >source /DIR/完全備份的資料庫.sql

    (6)分析二進制日志，找到丢失的表的drop table指令

        grep -i "change master" /data/all.sql 找到帶有drop table的二進制檔案和pos點

        mysqlbinlog --start-position=POSNUM binlog.0000* > /data/incr.sql

        vim /data/incr.sql，注釋drop那一行

    (7)還原

        mysql>set sql_log_bin=off

        mysql> source /data/incr.sql;

        mysql>set sql_log_bin=on

    (8)複查資料完整

    (9)開放使用者

17、分庫備份腳本

    (1)mysql -e 'show databases' | grep -Ev  '^Database|info|performanc'|while read dbname;do mysqldump -B $dbname | gzip > /data/${dbname}_bak`date +%F`.gz;done

    (2)mysql -e 'show databases' | grep -Ev  '^Database|info|performanc' |sed -r 's@(.*)@mysqldump -B \1| gzip > /data/\1__bak`date +%F`.gz@' |bash

    (3)for db in `mysql -e 'show databases' | grep -Ev  '^Database|info|performanc'`; do mysqldump -B $db |gzip > /data/${db}_bak`date +%F`.gz;done

18、生産環境實戰備份政策

    (1)InnoDB建議備份政策

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

    (2)MyISAM建議備份政策

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

注：以上實驗在另一篇文章中實作資料庫備份和還原實驗

19、Xtrabackup

    (1)特點：

        <1>備份還原過程快速、可靠

        <2>備份過程不會打斷正在執行的事務

        <3>能夠基于壓縮等功能節約磁盤空間和流量

        <4>自動實作備份檢驗

        <5>開源，免費

    (2)xtrabackup是用來備份InnoDB表的，不能備份非InnoDB表，和MySQL沒有互動

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

    (4)雖然目前一般不用 MyISAM 表，隻是 MySQL 庫下的系統表是 MyISAM 的，是以備份基本都通過 innobackupex 指令進行

    (5)選項

        --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

    (6)Prepare：innobackupex --apply-log [option] BACKUP-DIR

        選項說明：

        --apply-log：一般情況下,在備份完成後，資料尚且不能用于恢複操作，因為備份的資料中可能會包含尚未送出的事務或已經送出但尚未同步至資料檔案中的事務。是以，此時資料檔案仍處理不一緻狀态。此選項作用是通過復原未送出的事務及同步已經送出的事務至資料檔案使資料檔案處于一緻性狀态

        --use-memory：和--apply-log選項一起使用，當prepare 備份時，做crash recovery配置設定的記憶體大小，機關位元組，也可1MB,1M,1G,1GB等，推薦1G

        --export：表示開啟可導出單獨的表之後再導入其他Mysql中

        --redo-only：此選項在prepare base full backup，往其中合并增量備份時候使用，不适用于最後一個增量備份

    (7)還原：innobackupex --copy-back [選項] BACKUP-DIR，還原的時候需要停止服務

        innobackupex --move-back [選項] [--defaults-group=GROUP-NAME] BACKUP-DIR

    選項說明：

        --copy-back：做資料恢複時将備份資料檔案拷貝到MySQL伺服器的datadir

        --move-back：這個選項與--copy-back相似，唯一的差別是它不拷貝檔案，而是移動檔案到目的地。這個選項移除backup檔案，用時候必須小心。使用場景：沒有足夠的磁盤空間同僚保留資料檔案和Backup副本

    (8)還原注意事項：

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

        <2>在restore之前,必須shutdown MySQL執行個體，不能将一個運作中的執行個體restore到datadir目錄中

        <3>由于檔案屬性會被保留，大部分情況下需要在啟動執行個體之前将檔案的屬主改為mysql，這些檔案将屬于建立備份的使用者

            chown -R mysql:mysql /data/mysql

    以上需要在使用者調用innobackupex之前完成

        --force-non-empty-directories：指定該參數時候，使得innobackupex --copy-back或--move-back選項轉移檔案到非空目錄，已存在的檔案不會被覆寫。如果--copy-back和--move-back檔案需要從備份目錄拷貝一個在datadir已經存在的檔案，會報錯失敗

    (9)備份生成的相關檔案

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

        <1>xtrabackup_info：innobackupex工具執行時的相關資訊，包括版本，備份選項，備份時長，備份LSN(log sequence number日志序列号)，BINLOG的位置

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

        <3>xtrabackup_binlog_info：MySQL伺服器目前正在使用的二進制日志檔案及至備份這一刻為止二進制日志事件的位置，可利用實作基于binlog的恢複

        <4>backup-my.cnf：備份指令用到的配置選項資訊

        <5>xtrabackup_logfile：備份生成的日志檔案

20、xtrabackup的備份和還原

舊版：

    (1)xtrabackup使用進行熱備份，需要epel下載下傳或者官網下載下傳

        <1>确認資料庫表格的引擎是InnoDB或者XtraDB

        <2>vim /etc/my.cnf，在[mysqld]和[mysqld_safe]之間加入innodb_file_per_table=ON

        <3>重新開機資料庫服務

        <4>建立臨時目錄 /backups

        <5>innobackupex --user=root /backups，該指令會自動備份二進制日志檔案，是整庫備份，生成的檔案夾以日期顯示

        <6>scp -r /backups/2018-10-13_16-45-57/ 目标主機:/data/

    (2)還原：

        <1>注意vim /etc/my.cnf下[mysqld]内容中datadir指向路徑和預存放的資料庫路徑要一緻，如果自己想在/data/mysql存放還原的資料庫，需要建立目錄後更改權限為mysql.mysql，并在檔案下添加datadir=/data/mysql

        <4>将備份的資料庫傳至本機，移動至本機的備份目錄/backups

        <5>innobackupex --apply-log /backups/日期顯示的檔案夾

        <6>停止資料庫服務，删除原來所有資料庫

        <7>完全恢複：innobackupex --copy-back /backups/2018-10-13_16-45-57/

        <8>更改/data/mysql目錄權限為mysql.mysql

        <9>重新開機服務

新版：兩邊的配置檔案需要同步

        <1>在原主機做完全備份到/data/backups

        xtrabackup --backup --target-dir=/backups/

        scp -r /backups/* 目标主機:/backups

        <2>在目标主機上

            [1]預準備：確定資料一緻，送出完成的事務，復原未完成的事務

            xtrabackup --prepare --target-dir=/backups/

            [2]複制到資料庫目錄

            注意：資料庫目錄必須為空，MySQL服務不能啟動

            xtrabackup --copy-back --target-dir=/backups/

            [3]還原屬性

            chown -R mysql:mysql /var/lib/mysql

            [4]啟動服務

            systemctl start mariadb

21、xtrabackup的增量備份和還原

舊版

    (1)在原主機

        innobackupex /backups

        mkdir /backups/inc{1,2}

        修改資料庫内容

        innobackupex --incremental /backups/inc1 --incremental-basedir=/backups/2018-02-23_14-21-42（完全備份生成的路徑）

        再次修改資料庫内容

        innobackupex --incremental /backups/inc2 --incremental-basedir=/backups/inc1/2018-02-23_14-26-17 （上次增量備份生成的路徑）

        scp -r /backups/* 目标主機:/data/

    (2)在目标主機

        不啟動mariadb

        rm -rf /var/lib/mysql/*

        innobackupex --apply-log --redo-only /data/2018-02-23_14-21-42/

        innobackupex --apply-log --redo-only /data/2018-02-23_14-21-42/ --incremental-dir=/data/inc1/2018-02-23_14-26-17

        innobackupex --apply-log /data/2018-02-23_14-21-42/ --incremental-dir=/data/inc2/2018-02-23_14-28-29/

        ls /var/lib/mysql/

        innobackupex --copy-back /data/2018-02-23_14-21-42/

        chown -R mysql.mysql /var/lib/mysql/

        systemctl start mariadb

新版：

    (1)備份過程

        [1]完全備份：xtrabackup --backup --target-dir=/backups/base

        [2]第一次修改資料

        [3]第一次增量備份：xtrabackup --backup --target-dir=/backups/inc1 --incremental-basedir=/backups/base

        [4]第二次修改資料

        [5]第二次增量：xtrabackup --backup --target-dir=/backups/inc2 --incremental-basedir=/backups/inc1

        [6]scp -r /backups/* 目标主機:/backups/

    (2)備份過程生成三個備份目錄

        /backups/{base，inc1，inc2}

    (3)還原過程

        [1]預準備完成備份，此選項--apply-log-only阻止復原未提完成的事務

        xtrabackup --prepare --apply-log-only --target-dir=/backups/base

        [2]合并第1次增量備份到完全備份，xtrabackup --prepare --apply-log-only --target-dir=/backups/base --incremental-dir=/backups/inc1

        [3]合并第2次增量備份到完全備份：最後一次還原不需要加選項--apply-log-only

        xtrabackup --prepare --target-dir=/backups/base --incremental-dir=/backups/inc2

        [4]複制到資料庫目錄，注意資料庫目錄必須為空，MySQL服務不能啟動

        xtrabackup --copy-back --target-dir=/data/backups/base

        [5]還原屬性：chown -R mysql:mysql /var/lib/mysql

        [6]啟動服務：systemctl start mariadb

22、Xtrabackup單表導出和導入

    (1) 單表備份

        innobackupex --include='hellodb.students' /backups

    (2)備份表結構

        mysql -e 'show create table hellodb.students' > student.sql

    (3)删除表

        mysql -e 'drop table hellodb.students‘

    (4)innobackupex --apply-log --export /backups/2018-02-23_15-03-23/

    (5) 建立表

        mysql>CREATE TABLE `students` (

        `StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,

        `Name` varchar(50) NOT NULL,

        `Age` tinyint(3) unsigned NOT NULL,

        `Gender` enum('F','M') NOT NULL,

        `ClassID` tinyint(3) unsigned DEFAULT NULL,

        `TeacherID` int(10) unsigned DEFAULT NULL,

        PRIMARY KEY (`StuID`)

        ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8

    (6)删除表空間

        alter table students discard tablespace;

    (7)cp /backups/2018-02-23_15-03-23/hellodb/students.{cfg,exp,ibd} /var/lib/mysql/hellodb/

    (8)chown -R mysql.mysql /var/lib/mysql/hellodb/

    (9)mysql>alter table students import tablespace;