背景

隨著業務的發展，公司業務和規模不斷擴大，網站積累了大量的用戶信息和數據，對于一家互聯網公司來說，用戶和業務數據是根基。一旦公司的數據錯亂或者丟失，對于互聯網公司而言就等于說是滅頂之災，為防止系統出現操作失誤或系統故障導致數據丟失，公司要求加強用戶數據的可靠性，要求全面加強數據層面備份，并能在故障發生時第一時間恢復。

數據備份形式

文件備份：

通過Linux的備份命令把文件統一打個包存起來，可存在本地和遠程服務器，等到要恢復時，再用這些文件恢復到指定位置。

數據庫數據備份：

在一些對數據可靠性要求很高的行業如銀行、證券、電信等，如果發生意外停機或數據丟失其損失會十分慘重。為此數據庫管理員應針對具體的業務要求制定詳細的數據庫備份與災難恢復策略，并通過模擬故障對每

種可能的情況進行嚴格測試，只有這樣才能保證數據的高可用性。數據庫的備份是一個長期的過程，而恢復只在發生事故后進行，恢復可以看作是備份的逆過程，恢復的程度的好壞很大程度上依賴于備份的情況。此外，

數據庫管理員在恢復時采取的步驟正確與否也直接影響最終的恢復結果。

數據備份類型

按業務劃分： 可分為完全備份、增量備份、差異備份

1、完全備份：就是對整個數據庫的數據和數據結構進行備份

優點：直觀，容易被人理解

缺點：1.備份的數據有大量的數據都是重復的，占用了大量的空間，增加成本

2.備份的數據量大，所需時間較長

（Full Backup） 所謂全備份就是對整個數據庫的數據和數據結構進行備份。這種備份方式的好處就是很直觀，容易被人理解。而且當發生數據丟失的災難時，只要用災難之前的備份文件，就可以恢復丟失的數據。

然而它也有不足之處：首先由于每天都對系統進行完全備份,因此在備份數據中有大量是重復的。這些重復的數據占用了大量的空間，這對用戶來說就意味著增加成本；其次，由于需要備份的數據量相當大，因此備份所需時間較長。對于那些業務繁忙，備份窗口時間有限的單位來說，選擇這種備份策略無疑是不明智的。

2、增量備份（Incremental Backup）：每次備份的數據只是相當于上次備份后增加和修改過的數據。

優點：沒有重復備份的數據，節省空間

缺點：恢復數據比較麻煩，其中任何一個備份數據出了問題都會導致數據丟失

就是每次備份的數據只是相當于上一次備份后增加的和修改過的數據。這種備份的優點很明顯：沒有重復的備份數據，即節省空間，又縮短了備份時間。但它的缺點在于當發生災難時，恢復數據比較麻煩。舉例來說，如

果系統在星期四的早晨發生故障，丟失大批數據，那么現在就需要將系統恢復到星期三晚上的狀態。

這時管理員需要首先找出星期一的那份完全備份數據進行系統恢復，然后再找出星期二的數據來恢復星期二的數據，然后在找出星期三的數據來恢復星期三的數據。 很明顯這比第一種策略要麻煩得多。另外這種備份可靠

性也差。在這種備份下，各備份數據間的關系就象鏈子一樣，一環套一環，其中任何一個備份數據出了問題都會導致整條鏈子脫節。

3、差異備份（Differential Backup）：每次備份的數據相當于上一次全備份之后新增加和修改過的數據。

就是每次備份的數據是相對于上一次全備份之后新增加的和修改過的數據。管理員先在星期一進行一次系統完全備份；然后在接下來的幾天里，管理員再將當天 所有與星期一不同的數據（新的或經改動的）備份到磁帶上。舉例來說，在星期一，網絡管理員按慣例進行系統完全備份；在星期二，假設系統內只多了一個資產清 單，于是管理員只需將這份資產清單一并備份下來即可；在星期三，系統內又多了一份產品目錄，于是管理員

不僅要將這份目錄，還要連同星期二的那份資產清單一 并備份下來。

如果在星期四系統內有多了一張工資表，那么星期四需要備份的內容就是：工資表+產品目錄+資產清單。 由此可以看出，全備份所需時間最長，但恢復時間最短，操作最方便，當系統中數據量不大時，采用全備份最可靠；差異備份可避免另外兩種策略缺陷，但不同備份類型可以存在一定組合不同備份類型可以存在一定組合不同備份類型可以存在一定組合不同備份類型可以存在一定組合。

不同備份類型組合應用的示例

完全備份和差異備份

在星期一進行完全備份，在星期二至星期五進行差異備份。如果在星期五數據被破壞了，則你只需要還原星期一完全的備份和星期四的差異備份。這種策略備份數據需要較多的時間，但還原數據使用較少的時間。

完全備份和增量備份

在星期一進行完全備份，在星期二至星期五進行增量備份。如果在星期五數據被破壞了，則你需要還原星期一正常的備份和從星期二至星期五的所有增量備份。這種策略備份數據需要較少的時間，但還原數據使用較多的時間。

按方式劃分：可分為熱備、溫備、冷備

熱備份（Hot Backup）是指在數據庫運行中直接備份，對正在運行的數據庫沒有任何影響。

冷備份（Cold Backup）是指在數據庫停止的情況下進行備份，這種備份最為簡單，一般只需要拷貝相關的數據庫物理文件即可。

溫備份（Warm Backup）備份同樣是在數據庫運行時進行，但是會對當前數據庫的操作有所影響，例如加一個全局讀鎖以保證備份數據的一致性。（當你備份數據庫中的一個表時，先把這個表鎖起來，不讓別人來增查刪改表中的數據，這樣你在備份的時候，該表中的數據就不會發生變化，保證了備份數據的一致性）

物理備份：直接復制數據文件進行的備份（直接復制備份的數據文件是二進制格式的）

優點：無需額外工具，直接copy即可，恢復直接復制備份文件即可

缺點：與存儲引擎有關，跨平臺能力較弱

邏輯備份：從數據庫中"導出"數據另存而進行的備份（將sql語句給導出到一個文本中，比二進制格式的文件大）

優點： 能使用編輯器處理，恢復簡單，能基于網絡恢復，有助于避免數據損壞

缺點： 備份文件較大，備份較慢，無法保證浮點數的精度，使用邏輯備份數據恢復后，還需手動重建索引，十分消耗CPU資源

備份流程圖

Mysql日志介紹

MySQL日志：

主要包含：錯誤日志、查詢日志、慢查詢日志、事務日志、二進制日志等；

日志是mysql數據庫的重要組成部分。日志文件中記錄著mysql數據庫運行期間發生的變化；也就是說用來記

錄mysql數據庫的客戶端連接狀況、SQL語句的執行情況和錯誤信息等。當數據庫遭到意外的損壞時，可以通

過日志查看文件出錯的原因，并且可以通過日志文件進行數據恢復。

Mysql錯誤日志

在mysql數據庫中，錯誤日志功能是默認開啟的。并且，錯誤日志無法被禁止。默認情況下，錯誤日志存儲在mysql數據庫的數據文件中。錯誤日志文件通常的名稱為hostname.err。其中，hostname表示服務器主機名。

錯誤日志信息可以自己進行配置的，錯誤日志所記錄的信息是可以通過logerror和log-warnings來定義的，其中log-err是定義是否啟用錯誤日志的功能和錯誤日志的存儲位置，log-warnings是定義是否將警告信息也定義至錯誤日志中。默認情況下錯誤日志大概記錄以下幾個方面的信息：服務器啟動和關閉過程中的信息（未必是錯誤信息，如mysql如何啟動InnoDB的表空間文件的、如何初始化自己的存儲引擎的等等）、服務器運行過程中的錯誤信息、事件調度器運行一個事件時產生的信息、在從服務器上啟動服務器進程時產生的信息。

mysql -uroot -p

select globle variables like '%log%';

可以通過配置文件來修改log_error

vim /etc/my.cnf //如下圖：我將錯誤日志的路徑改為/var/log/mariadb/mariadb.err

log-error=/var/log/mariadb/mariadb.err

然后重啟數據庫服務連接數據庫查看全局日志，修改成功

查看錯誤日志的內容

臨時修改：

Mysql錯誤日志中，log_error可以直接定義為文件路徑，也可以為ON|OFF；

log_warings只能使用1|0來定義開關啟動。

永久修改：

更改錯誤日志位置可以使用log_error來設置形式如下：

[root@stu18 data]# vim /etc/my.cnf

[mysqld]

Log_error=DIR/[filename]

解析：其中，DIR參數指定錯誤日志的路徑filename參數是錯誤日志的名稱，沒有指定該參數時默認為主機名。修改配置文件重啟mysql服務器即可生效。

注意：在mysql5.5.7之前：數據庫管理員可以刪除很長時間之前的錯誤日志，以保證mysql服務器上的硬盤空間。mysql數據庫中，可以使用mysqladmin命令開啟新的錯誤日志。

mysqladmin命令的語法如下：

mysqladmin –u root –pflush-logs也可以使用登錄mysql數據庫中使用FLUSHLOGS語句來開啟新的錯誤日志。

Mysql查詢日志

默認情況下查詢日志是關閉的。由于查詢日志會記錄用戶的所有操作，其中還包含增刪查改等信息，在并發操作大的環境下會產生大量的信息從而導致不必要的磁盤IO，會影響mysql的性能的。如若不是為了調試數據庫的目的建議不要開啟查詢日志。

mysql

show global variables like '%log%';

Mysql慢查詢日志

慢查詢日志是用來記錄執行時間超過指定時間的查詢語句。通過慢查詢日志，可以查找出哪些查詢語句的執行效率很低(有些查詢語句的執行時間比較長，要把這些查詢語句找出清除，來優化服務器性能)，以便進行優化。強烈建議開啟，它對服務器性能的影響微乎其微，但是可以記錄mysql服務器上執行了很長時間的查詢語句。可以幫助我們定位性能問題的。

啟動和設置慢查詢日志：

1、通過配置文件my.cnf中的log-slow-queries選項可以開啟慢查詢日志；

形式如下：

vim /etc/my.cnf

[mysqld]

slow-query-log = ON

slow-query-log-file = /var/log/mariadb/slow.log

long-query-time = 0.01

其中，DIR參數指定慢查詢日志的存儲路徑；filename參數指定日志的文件名，生成日志文件的完成名稱為filename-slow.log。如果不指定存儲路徑，慢查詢日志默認存儲到mysql數據庫的數據文件下，如果不指定文件名，默認文件名為hostname-slow.log

2、通過登錄mysql服務器直接定義

方式如下：

首先要有全局權限；然后執行mysql>set global slow_query_log=1;（臨時生效，sql語句執行時間超過1s就被成為慢查詢日志）

時間默認超過多少的稱為慢查詢日志？

一般都是通過long_query_time選項來設置這個時間值，時間以秒為單位，可以精確到微秒。如果查詢時間超過了這個時間值（默認為10秒），這個查詢語句將被記錄到慢查詢日志中。查看服務器默認時間值方式如下：

注釋：其中這個慢查詢時間并不是只表示語句自身執行超過10秒還包含由于其他資源被征用造成阻塞的查詢執行時間或其他原因等都被記錄到慢查詢中。所以這個慢查的時長表示從查詢開始到查詢結束中間包含可能的任何原因所經歷的所有時間。

查看慢查詢日志內容

Mysql事務日志

事務：事務就是一系列操作的集合，一系列操作之后需要提交，提交之后，這一系列操作才能被稱為事務。(要么操作都執行，要么都不執行)

事務日志（InnoDB特有的日志）可以幫助提高事務的效率。使用事務日志，存儲引擎在修改表的數據時只需要修改其內存拷貝，再把改修改行為記錄到持久在硬盤上的事務日志中，而不用每次都將修改的數據本身持久到磁盤。事務日志采用追加的方式，因此寫日志的操作是磁盤上一小塊區域內的順序I/O，而不像隨機I/O需要在磁盤的多個地方移動磁頭，所以采用事務日志的方式相對來說要快得多。事務日志持久以后，內存中被修改的數據在后臺可以慢慢的刷回到磁盤。目前大多數的存儲引擎都是這樣實現的，我們通常稱之為預寫式日志，修改數據需要寫兩次磁盤。

mysql基于事務的操作，會直接把對應內存中的數據給改掉，改完之后，去查看，都已經生效，但是并沒有網磁盤上去寫，他是先寫到事務日志里面，然后再定期的往磁盤上去刷（事務日志采用追加的方式，往磁盤上寫，是按照順序來寫的，大大提高了事務的效率）

如果數據的修改已經記錄到事務日志并持久化，但數據本身還沒有寫回磁盤，此時系統崩潰，存儲引擎在重啟時能夠自動恢復這部分修改的數據。具有的恢復方式則視存儲引擎而定。

innodb引擎是支持事務的引擎

查看事務日志的定義

show global variables like '%log%';

Mysql二進制日志

二進制日志也叫作變更日志，主要用于記錄修改數據或有可能引起數據改變的mysql語句，并且記錄了語句發生時間、執行時長、操作的數據等等。所以說通過二進制日志可以查詢mysql數據庫中進行了哪些變化。一般大小體積上限為1G

show global variables like '%log%';

sql_log_bin ={ON|OFF} #用于控制會話級別（連上mysql執行一個操作語句，這就是會話級別的，比如說直接用一個文件導入mysql，這就不算會話級別的）二進制日志功能的開啟或關閉。默認為ON，表示啟用記錄功能。用戶可以在會話級別修改此變量的值，但其必須具有SUPER權限。

binlog_cache_size =32768 #默認值32768 Binlog Cache用于在打開了二進制日志（binlog）記錄功能的環境，是MySQL 用來提高binlog的記錄效率而設計的一個用于短時間內臨時緩存binlog數據的內存區域。一般來說，如果我們的數據庫中沒有什么大事務，寫入也不是特別頻繁，2MB～4MB是一個合適的選擇。但是如果我們的數據庫大事務較多，寫入量比較大，可與適當調高binlog_cache_size。同時，我們可以通過binlog_cache_use 以及binlog_cache_disk_use來分析設置的binlog_cache_size是否足夠，是否有大量的binlog_cache由于內存大小不夠而使用臨時文件（binlog_cache_disk_use）來緩存了。

log_bin = mysql-bin #指定binlog的位置，默認在數據目錄下。

binlog-format= {ROW|STATEMENT|MIXED} #指定二進制日志的類型，建議為MIXED。如果設定了二進制日志的格式，卻沒有啟用二進制日志，則MySQL啟動時會產生警告日志信息并記錄于錯誤日志中。

row:不記錄每條sql語句的上下文關系，而僅僅記錄每條數據被修改了

statement：每一條會修改數據的sql語句都會被記錄

mixed：表示前兩者混合

sync_binlog = 10 #設定多久同步一次二進制日志至磁盤文件中，0表示不同步，任何正數值都表示對二進制每多少次寫操作之后同步一次。當autocommit的值為1時，每條語句的執行都會引起二進制日志同步，否則，每個事務的提交會引起二進制日志同步

通過編輯my.cnf中的log-bin選項可以開啟二進制日志；形式如下：

其中，DIR參數指定二進制文件的存儲路徑；filename參數指定二級制文件的文件名，其形式為filename.number，number的形式為000001、000002等。每次重啟mysql服務或運行mysql> flush logs;都會生成一個新的二進制日志文件，這些日志文件的number會不斷地遞增。除了生成上述的文件外還會生成一個名為filename.index的文件。這個文件中存儲所有二進制日志文件的清單又稱為二進制文件的索引。

每重啟一次數據庫服務，就會生成一個二進制日志文件

查看二進制日志：

二進制日志的定義方式為二進制格式；使用此格式可以存儲更多的信息，并且可以使寫入二進制日志的效率更高。但是不能直接使用查看命令打開并查看二進制日志。

小擴展：二進制日志的記錄位置,通常為上一個事件執行結束時間的位置，每一個日志文件本身也有自己的元數據所以說對于當前版本的mysql來說二進制的開始位置通常為107；

連接mysql，輸入幾條可以修改數據的sql語句，從而生成二進制日志

查看指定二進制日志信息

命令行下查看二進制日志：

由于無法使用cat等方式直接打開并查看二進制日志；所以必須使用mysqlbinlog命令。但是當正在執行mysql讀寫操作時建議不要使用此打開正在使用的二進制日志文件；若非要打開可flushlogs。mysqlbinlog命令的使用方式：

導出此數據庫的信息：

[root@stu18 data]#mysqlbinlog mysql-bin.000017 > /tmp/a.sql

導入此數據庫的信息：

[root@stu18 data]#mysql < a.sql

刪除二進制日志信息：

二進制日志會記錄大量的信息（其中包含一些無用的信息）。如果很長時間不清理二進制日志，將會浪費很多的磁盤空間。但是，刪除之后可能導致數據庫崩潰時無法進行恢復，所以若要刪除二進制日志首先將其和數據庫備份一份，其中也只能刪除備份前的二進制日志，新產生的日志信息不可刪(可以做即時點還原)。也不可在關閉mysql服務器之后直接刪除因為這樣可能會給數據庫帶來錯誤的。若非要刪除二進制日志需要做如下操作：導出備份數據庫和二進制日志文件進行壓縮歸檔存儲。刪除二進制文件的方法如下：

使用RESET MASTER語句可以刪除所有的二進制日志。該語句的形式如下：

mysql> reset master;

Query OK, 0 rowsaffected (0.17 sec)

mysql> show binary logs;

Mysql備份工具

mysqldump: 邏輯備份工具，適用于所有存儲引擎，可用于溫備，能實現完全備份，部分備份；對InnoDB存儲引擎

支持熱備；

cp, tar等文件系統工具：物理備份工具，適用于所有存儲引擎；用于冷備，能實現完全備份，部分備份；

lvm2的快照：幾乎熱備；借助于文件系統工具實現物理備份；

mysqlhotcopy: 幾乎冷備；僅適用于MyISAM存儲引擎；

Mysql備份方案①mysqldump+binlog: （ 推薦）

完全備份，通過備份二進制日志實現增量備份

②xtrabackup:

對InnoDB：熱備，支持完全備份和增量備份

對MyISAM：溫備，只支持完全備份

③lvm2快照+binlog：

幾乎熱備，物理備份

mysqldump+binlog命令的語法格式

mysqldump [OPTIONS] database [tables]：備份單個庫，或庫指定的一個或多個表

mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2DB3...]：備份一個或多個庫

mysqldump [OPTIONS] --all-databases [OPTIONS]：備份所有庫

其他選項

-x, --lock-all-tables：鎖定所有表

-l, --lock-tables：鎖定備份的表

--single-transaction：啟動一個大的單一事務實現備份

-C, --compress：壓縮傳輸

-E, --events：備份指定庫的事件調度器

-R, --routines：備份存儲過程和存儲函數

--triggers：備份觸發器

--master-data={0|1|2}

0：不記錄

1：記錄CHANGE MASTER TO語句；此語句未被注釋

2：記錄為注釋語句

-F，--flush-logs：鎖定表之后執行flush logs命令

mysqldump+binlog備份與恢復1.修改mysql配置文件，開啟二進制日志

vim /etc/my.cnf

log-bin = master-log

然后重啟mysql

systemctl restart mariadb

在進入mysql查看是否生成二進制日志

2、準備備份目錄

3、準備備份數據庫及表

mysql

create database test;

use magedu;

create table m(id int,name char(20));

4、進行完整備份

mysqldump --all-databases --lock-all-tables --flush-log --master-data=2 > /backup/`date +%F_%T`-all.sql

5、向表中插入數據

mysql

use magedu;

show master status;

insert into m26 (id,name) values(1,'fuming'),(2,'zhangmeng');

6、進行增量備份，備份二進制日志

mysqlbinlog --start-position=245 --stop-position=479 /var/lib/mysql/master-log.000002 > /backup/binlog/binlog-`date +%F_%T`.sql

判斷position的start和stop

show master logs;

show binlog events in 'master-log.000002';

結束要包含commit提交。

7、繼續插入數據，在沒備份的情況下刪除數據庫，模擬誤操作

8、數據恢復，由于最后我們沒有備份就刪除了數據庫，所以我們首先需要保護最后的二進制日志,如果這些二進制丟了，那就真的恢復不了了，查看刪除操作之前的position值mysqlbinlog /var/lib/mysql/master-log.000002

9、將最后操作的二進制日志備份

mysqlbinlog --start-position=467 --stop-position=677 /var/lib/mysql/master-log.000002 > /backup/binlog/binlog-`date +%F_%T`.sql

ls /backup/binlog/

10.導入之前的所有備份

mysql < /backup/2017-12-07_20/:20/:04-all.sql 導入完整備份

mysql < /backup/binlog/binlog-2017-12-07_20/:45/:17.sql 導入增量備份

mysql < /backup/binlog/binlog-2017-12-07_21/:05/:42.sql 導入刪掉數據庫之前的增量備份

11.查看數據庫及數據

xtrabackup

Xtrabackup是由percona提供的mysql數據庫備份工具，據官方介紹，是一款開源能夠對innodb和xtradb數據庫進行熱備的工具。

特點：

(1)備份過程快速、可靠

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

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

(4)自動實現備份檢驗

(5)還原速度快

實驗步驟：（1）xtrabackup的安裝

yum install percona-xtrabackup -y

（2）完全備份

innobackupex --user=root /backup

（3）添加數據

mysql -uroot

create database magedu;

use magedu

create table m26 (id int,name char(20));

insert into m26 values (007,'fuming'),(008,'zhangmeng')

（4）增量備份

innobackupex --incremental /backup/ --incremental-basedir=/backup/2017-11-16_16-53-4

（5）數據恢復準備
1.執行操作（完全備份）：

innobackupex --apply-log --redo-only BASE-DIR（BASE-DIR是完全備份的目錄）

例如：innobackupex --apply-log --redo-only BASE-DIR --incrementaldir=/backup/2017-11-16_17-17-52/

2.接著執行（增量）：

innobackupex --apply-log --redo-only BASE-DIR --incrementaldir=INCREMENTAL-DIR-1（INCREMENTAL-DIR-1是增量備份的目錄）

例如：innobackupex --apply-log --redo-only /backup/2017-11-16_16-53-43 --incrementaldir=/backup/2017-11-16_17-17-52/

（6）恢復階段，還原數據

mv /var/lib/mysql /var/lib/mysql.bak 模擬刪除數據庫

mkdir /var/lib/mysql

cd /var/lib/mysql

innobackupex --copy-back /backup/2017-11-16_16-53-43 恢復完全備份

lvm2快照+binlog

做實驗之前我們先回顧一下lvm2-snapshot的知識

LVM快照簡單來說就是將所快照源分區一個時間點所有文件的元數據進行保存，如果源文件沒有改變，那么訪問快照卷的相應文件則直接指向源分區的源文件，如果源文件發生改變，則快照卷中與之對應的文件不會發生改變。快照卷主要用于輔助備份文件。

實驗步驟：

1、添加硬盤，并劃分磁盤類型為lvm類型

echo '- - -' > /sys/class/scsi_host/host2/scan

2.分區

t 8e 就是lvm

partx -a /dev/sdb 使內核識別新磁盤

3.pvcreate /dev/sdb1 添加物理卷

4.vgcreate myvg /dev/sdb1 添加卷組

5.lvcreate -n mydata -L 5G myvg 添加邏輯卷

6、mkfs.ext4 /dev/mapper/myvg-mydata 格式化邏輯卷

7、掛載mount /dev/mapper/myvg-mydata /lvm_data 使用

8、修改Mysql配置，使得數據文件在邏輯卷上 datadir=/lvm_data

9、service mysqld restart 啟動Mysql服務

10、創建數據庫，進行操作

11、mysql> FLUSH TABLES WITH READ LOCK; #鎖定表

12、lvcreate -L 1G -n mydata-snap -p r -s /dev/mapper/myvgmydata

#創建快照卷 Logical volume "mydata-snap" created.

13、mysql> UNLOCK TABLES; #解鎖所有表

14、 mount /dev/myvg/mydata-snap /lvm_snap/ #掛載snap

15、tar cvf /tmp/mysqlback.tar ./* #打包物理備份

16、umount /lvm_snap/ #卸載snap

17、lvremove myvg mydata-snap #刪除snap

18、刪除mysql數據 rm -rf /lvm_data/*

19、 解壓恢復刪除數據 tar xvf /tmp/mysqlback.tar ./

20、驗證數據庫數據是否正確恢復

總結

好了，今天的內容就到這里，我們下期再見。

以上這篇Mysql實現企業級日志管理、備份與恢復的實戰教程就是小編分享給大家的全部內容了，希望能給大家一個參考，也希望大家多多支持VeVb武林網。