掌握MySQL恢復(fù)數(shù)據(jù)備份知識

本文主要給大家簡單講講MySQL恢復(fù)數(shù)據(jù)備份知識，相關(guān)專業(yè)術(shù)語大家可以上網(wǎng)查查或者找一些相關(guān)書籍補充一下，這里就不涉獵了，我們就直奔主題吧，希望MySQL恢復(fù)數(shù)據(jù)備份知識這篇文章可以給大家?guī)硪恍嶋H幫助。

數(shù)據(jù)庫備份恢復(fù)知識要點：

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

      完全備份：備份整個數(shù)據(jù)集

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

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

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

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

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

3.應(yīng)該備份什么？

     ① 數(shù)據(jù)
     ② 二進(jìn)制日志、innodb的事務(wù)日志
     ③ 代碼(存儲過程、存儲函數(shù)、觸發(fā)器、事件調(diào)度器)
     ④ 云服務(wù)器的配置文件

4.備份工具：

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

5.備份方案的選擇：

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

                  MySQLdump：完全備份

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

    方案二：lvm2快照+復(fù)制binlog

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

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

    方案三：XtraBackup

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

                 支持完全備份，增量備份

MySQLdump備份工具講解：

1.什么是MySQLdump？

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

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

     InnoDB：熱備或溫備；

     MyISAM：溫備；

3.備份機制：

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

4.使用方法Usage:

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

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

    ③ 恢復(fù)：mysql -uroot -p <test.sql

MySQLdump使用進(jìn)階：

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

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

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

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

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

     --single-transaction：創(chuàng)建一個事務(wù)，基于此快照執(zhí)行備份；后續(xù)要跟崩潰后恢復(fù)操作

其它選項：

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

     --triggers：備份指定庫的觸發(fā)器；

     -E, --events：備份指定數(shù)據(jù)庫相關(guān)的所有event scheduler；

     --master-data[=#]

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

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

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

    -d :后加數(shù)據(jù)庫名表示只備份建庫語句

    --compact：去掉注釋

MySQLdump備份實驗：

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

實驗準(zhǔn)備：

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

        ② 開啟二進(jìn)制日志功能(默認(rèn)關(guān)閉)

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

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

                       重啟服務(wù)

       ③ 主機A數(shù)據(jù)示例：

select * from  shudian.mybook;

備份恢復(fù)流程：    

 1. 全局備份：

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

2.修改主機A數(shù)據(jù)

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

修改后數(shù)據(jù)：

3.發(fā)送備份信息

① 全局備份文件

scp shudian.sql B主機IP：/app

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

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

③ 備份節(jié)點后二進(jìn)制日志

mysqlbinlog --start-position=1545 /app/test.000002 -D >incre.sql
#ROW模式導(dǎo)出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 #指定數(shù)據(jù)庫
    -D / --disable-log #禁用恢復(fù)時生成二進(jìn)制日志,生成的sql文件需要root權(quán)限才能執(zhí)行

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

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 */ /*!*/;

發(fā)送二進(jìn)制日志至主機B：

scp incre.sql B主機IP：/app

④ 主機B數(shù)據(jù)恢復(fù)

set sql_log_bin=0;臨時關(guān)閉記錄二進(jìn)制日志
mysql <shudian.sql
mysql <incre.sql
set sql_log_bin=1;打開記錄二進(jìn)制日志

注意:二進(jìn)制文件不應(yīng)該與數(shù)據(jù)文件放在同一塊磁盤，并應(yīng)放置于具有冗余功能的磁盤上如ride10.

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