掌握MySQL恢復數據備份知識

本文主要給大家簡單講講MySQL恢復數據備份知識，相關專業術語大家可以上網查查或者找一些相關書籍補充一下，這里就不涉獵了，我們就直奔主題吧，希望MySQL恢復數據備份知識這篇文章可以給大家帶來一些實際幫助。

數據庫備份恢復知識要點：

1.時間軸備份類型分類：

      完全備份：備份整個數據集

      增量備份：上一次完全備份，或上一次增量備份以后變化的數據的備份(還原麻煩，節省空間)

      差異備份：僅備份最近一次完全備份以來變化的數據（還原簡單，空間消耗大）

2.什么是物理備份、邏輯備份：

      物理備份：直接復制數據文件進行備份(有可能占用更多的空間，備份速度快，做熱備較難)

     邏輯備份：從數據庫中導出數據“另存為”而進行的備份(從二進制轉化為文本格式，有可能丟失精度，需要專門的協議客戶端才能進行，和數據存儲引擎無關、備份恢復時間較長、做熱備容易)

3.應該備份什么？

     ① 數據
     ② 二進制日志、innodb的事務日志
     ③ 代碼(存儲過程、存儲函數、觸發器、事件調度器)
     ④ 云服務器的配置文件

4.備份工具：

     ① MySQLdump  #mysql自帶邏輯備份工具
     ② cp、tar 物理備份工具，冷備
     ③ lvm2的快照：幾乎熱備（請求施加全局持鎖），借助于文件系統管理工具進行備份
     ④ mysqlhotcopy ：幾乎冷備，僅適用于myisam存儲引擎

5.備份方案的選擇：

    方案一：MySQLdump+復制binlog(時間較慢，支持遠程備份)

                  MySQLdump：完全備份

                  復制binlog中指定時間范圍的event：增量備份

    方案二：lvm2快照+復制binlog

                 lvm2快照：使用cp或tar等做物理備份：完全備份

                 復制binlog中指定時間范圍的event：增量備份

    方案三：XtraBackup

                 由percona提供的支持對innodb做熱備（物理備份）的工具

                 支持完全備份，增量備份

MySQLdump備份工具講解：

1.什么是MySQLdump？

      mysql或mariadb自帶客戶端命令，邏輯備份工具。是圖形化備份管理工具mydumper、phpmyadmin的備份調用工具。基于mysql客戶端協議，適用于所有存儲引擎，溫備：完全備份、部分備份。對innodb支持熱備(時間較長)適合備份較小的數據庫(GB以下).

2.對常見存儲引擎支持：

     InnoDB：熱備或溫備；

     MyISAM：溫備；

3.備份機制：

    通過mysql協議連接至mysql云服務器。向mysql云服務器發起一個全量查詢操作，把所有數據拿到本地以后，并且將讀取到的數據保存在文件中從而完成備份。
    庫：CREATE DATABASE
    表：CREATE TABLE
    數據：INSERT INTO

4.使用方法Usage:

     ① mysqldump [OPTIONS] database [tables]   # 備份單庫，可以只備份其中的一部分表（部分備份,還原時需手動創建數據庫）；

      ② mysqldump [OPTIONS]  -B/--databases [OPTIONS] DB1 [DB2 DB3...]  # 備份多庫(推薦使用)；

      ③ mysqldump [OPTIONS]  -A/--all-databases [OPTIONS] # 備份所有庫；

5. 初級備份實驗示例：

    ① 備份：mysqldump -uroot -p -B test > test.sql

    ② 登錄mysql刪除test庫：drop database  test；

    ③ 恢復：mysql -uroot -p <test.sql

MySQLdump使用進階：

        實際生產使用中，需要考慮到備份時長、備份精度、備份過程對線上體驗的影響、從完全備份后產生的數據恢復方法等，本節介紹MySQLdump針對主流存儲引擎額外選項，對事務記錄，以及演示生產使用MySQLdump。

MyISAM存儲引擎選項：支持溫備，備份時要鎖定表（避免數據不一致）；

      -x, --lock-all-tables：鎖定所有庫的所有表，讀鎖；

       -l, --lock-tables：鎖定指定庫所有表；

InnoDB存儲引擎：支持溫備和熱備；

     --single-transaction：創建一個事務，基于此快照執行備份；后續要跟崩潰后恢復操作

其它選項：

     -R, --routines：備份指定庫的存儲過程和存儲函數；

     --triggers：備份指定庫的觸發器；

     -E, --events：備份指定數據庫相關的所有event scheduler；

     --master-data[=#]

             1：默認（--master-data），記錄為CHANGE MASTER TO語句，此語句不被注釋（據說在change master to時不用指定master_log_file和master_log_pos即可）；

             2：記錄為CHANGE MASTER TO語句，此語句被注釋；

     -F / --flush-logs：鎖定表完成后，即進行日志滾動操作；

    -d :后加數據庫名表示只備份建庫語句

    --compact：去掉注釋

MySQLdump備份實驗：

實驗場景：兩臺數據庫主機A,B。全局備份主機A數據后，又產生了新的數據（自己手動修改表信息），將主機A的完全備份文件、二進制日志文件發送給主機B完成備份。

實驗準備：

        ① 兩臺主機安裝MySQL或者mariadb-server

        ② 開啟二進制日志功能(默認關閉)

             方法：vim /etc/my.cnf.d/server.cnf  #安裝方式不同，路徑也會不同，本文以yum安裝講解

                       在[server]或者[mysqld]下添加 ：log_bin=/路徑/前綴  (mysql用戶必須對日志路徑具有rwx權限，本例log_bin=/app/test)

                       重啟服務

       ③ 主機A數據示例：

select * from  shudian.mybook;

備份恢復流程：    

 1. 全局備份：

mysqldump -uroot -p --single-transaction --master-data=2 --flush-logs -B shudian >shudian.sql
--single-transaction：創建一個事務,在導出數據之前提交一個BEGIN SQL語句，BEGIN 不會阻塞任何應用程序且能保證導出時數據庫的一致性狀態。
--master-data=2:記錄備份時二進制文件記錄的位置，此語句被注釋
--flush-logs：鎖定表完成后，即進行日志滾動操作
--lock-all-tables,  -x 
提交請求鎖定所有數據庫中的所有表，以保證數據的一致性。這是一個全局讀鎖，并且自動關閉--single-transaction 和--lock-tables 選項。
mysqldump  -uroot -p --host=localhost --all-databases --lock-all-tables
--lock-tables,  -l
開始導出前，鎖定所有表。用READ  LOCAL鎖定表以允許MyISAM表并行插入。對于支持事務的表例如InnoDB和BDB，--single-transaction是一個更好的選擇，因為它根本不需要鎖定表。
請注意當導出多個數據庫時，--lock-tables分別為每個數據庫鎖定表。因此，該選項不能保證導出文件中的表在數據庫之間的邏輯一致性。不同數據庫表的導出狀態可以完全不同。
mysqldump  -uroot -p --host=localhost --all-databases --lock-tables

2.修改主機A數據

insert into mybook values (5,'liangzi',30,412);
flush logs;  #刷新使生成新的binlog日志

修改后數據：

3.發送備份信息

① 全局備份文件

scp shudian.sql B主機IP：/app

② 查看全局備份文件看備份到哪個節點并記錄：

grep " MASTER_LOG_POS" shudian.sql |head -1
-- CHANGE MASTER TO MASTER_LOG_FILE='test.000002', MASTER_LOG_POS=1545;

③ 備份節點后二進制日志

mysqlbinlog --start-position=1545 /app/test.000002 -D >incre.sql
#ROW模式導出mysqlbinlog --base64-output=decode-rows -v -d DBNAME mysqlbin.000001 >incre.sql
#mysqlbinlog -d -D ops test.000002 >ops_incre_bin.sql 
    --start-position= / -j起始位置
    -d #指定數據庫
    -D / --disable-log #禁用恢復時生成二進制日志,生成的sql文件需要root權限才能執行

如誤操作后需修改sql文件剔除相關操作的條目，例誤刪除某表要刪除：

DROP TABLE `t_bind_card` /* generated by server */
/*!*/;
# at 341
#180824 15:04:11 server id 1  end_log_pos 389 CRC32 0x2f0eec92 	Rotate to master-bin.000004  pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

發送二進制日志至主機B：

scp incre.sql B主機IP：/app

④ 主機B數據恢復

set sql_log_bin=0;臨時關閉記錄二進制日志
mysql <shudian.sql
mysql <incre.sql
set sql_log_bin=1;打開記錄二進制日志

注意:二進制文件不應該與數據文件放在同一塊磁盤，并應放置于具有冗余功能的磁盤上如ride10.

MySQL恢復數據備份知識就先給大家講到這里，對于其它相關問題大家想要了解的可以持續關注我們的行業資訊。我們的板塊內容每天都會捕捉一些行業新聞及專業知識分享給大家的。