MYSQL備份與恢複精華篇

標籤：資料庫資料   internet   資料中心   線上的   乙太網路   

資料備份原理圖

         650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/59/93/wKiom1TYWLvjFG6QAADdLOzjiWQ351.jpg" alt="wKiom1TYWLvjFG6QAADdLOzjiWQ351.jpg" />

資料備份屬於資料容災保護中的內容，所有的資料備份系統設計都基於這五個元素，備份源、備份目標、傳輸網路、備份引擎和備份策略。使用者按照需要制定備份策略，使用定時任務執行備份指令碼，使用備份引擎將需要備份的的資料從備份源通過傳輸網路傳送到備份目標。

備份五元組：

1、備份源

需要備份的資料統一稱為備份源，可以是文本資料，音視頻資料，也可以是資料庫資料等等。

2、備份目標

存放備份資料的位置，通常建議將備份資料存放在異機，或者是更遠的資料中心，備份目標可以是線上的磁碟，磁碟陣列櫃，也可以是磁帶庫或是虛擬帶庫。而備份目標所在的位置可以在同一個資料中心，也可以是容災機房。

3、傳輸網路

備份資料時使用的傳輸鏈路，可以是專線，乙太網路，Internet，VPN等等，只要保證備份源與目標之間的路由可達即可。

4、備份引擎

資料要能夠從源到目標流動，就要有動力，就像是水要流動一樣，這個動力來源就是備份引擎，像mysqldump ，nvbu，還有大量的備份軟體都是備份引擎。

5、備份策略

為了有效備份，並減少人為操作，應該制定完善的備份策略。通常全備與差備與增備相結合，備份的時間點應該盡量避開業務高鋒期，通常在晚上執行，通過定時任務實現。

 

MYSQL資料備份原理

mysql資料備份其實就是通過SQL語句的形式將資料DUMP出來，以檔案的形式儲存，而且匯出的檔案還是可編輯的，這和Oracle資料庫的rman備份還是很不一樣的，mysql更像是一種邏輯備份從庫中抽取SQL語句，這就包括建庫，連庫，建表，插入等就像是將我們之前的操作再通過SQL語句重做一次。

 

MYSQL的日誌類型

1、  二進位日誌（log-bin）

二進位日誌非常重要，二進位日誌會記錄mysql資料庫的所有變更操作，其實和oracle的redolog日誌原理差不多，由於它記錄的所有的操作，於是我們就可以使用某個時間點之後的二進位日誌做前滾操作，來增量恢複資料。

mysql的二進位日誌可以使用mysqlbinlog來進行查看和過濾，一直過濾到我們想要的資料再匯入資料庫，而且也是非常方便的，但尤其要強調的是要嚴格按照二進位日誌產生的順序執行。

用途：記錄所有變更操作，用於增量備份

配置：在my.cnf中添加

[mysqld]

log-bin =mysql-bin

log-bin-index =mysql-bin.index

 

2、  中繼日誌（relay-log）

顧名思義，傳遞日誌，主要用在主從複製的架構中，只在從庫中有中繼日誌（多級複製除外）在從庫中將主庫複製過來的二進位日誌儲存為中繼日誌，用於從庫重構資料。

配置：在my.cnf中添加

[mysqld]

relay-log =relay-log

relay_log_index= relay-log.index

 

3、  慢查詢日誌（slow_query_log）

慢查詢日誌主要用於mysql最佳化，從資料庫中找出哪些SQL語句是比較慢的，將其放到一個檔案中，後續可以使用mysqlsla工具去對慢查詢語句進行分析，將分析結果提交給開發進行SQL最佳化。

用途：找出慢查詢進行最佳化

配置：在my.cnf中添加

[mysqld]

slow_query_log= 1

long-query-time= 2  

slow_query_log_file= /data/3306/slow.log

 

4、  一般查詢日誌

會記錄所有訪問mysql的行業，因此會產生大量日誌，一般建議關閉。

配置：在my.cnf中添加

[mysqld]

general_log = 1

log_output =FILE

general_log_file= /home/mysql/mysql/log/mysql.log 

 

5、  錯誤記錄檔

記錄mysql產生的錯誤，這個日誌在排錯的時候相當有用，一般建議開啟。

配置：在my.cnf中添加

[mysqld]

log-warnings =1

log-error =/home/mysql/mysql/log/mysql.err

 

6、  交易記錄

緩衝事務提交的資料，實現將隨機IO轉換成順序IO。

配置：在my.cnf中添加

[mysqld]

innodb_log_buffer_size= 16M

innodb_log_file_size= 128M

innodb_log_files_in_group= 3

 

一、mysqldump|mysql|mysqlbinlog工具使用

mysqldump

命令介紹：

mysqldump是mysql提供的一個基於命令列的mysql資料備份工具，提供了豐富的參數選擇，用於各種需求形式的備份，如單庫備份，多庫備份，單表與多表備份，全庫備份，備份表結構，備份表資料等。

 

文法格式：

1、單庫備份

mysqldump -uroot -p123456 -S /data/3306/data/mysql.sock 庫名 >/opt/backup/mysql_bak_db.sql

2、多庫備份

mysqldump -uroot -p123456 -S /data/3306/data/mysql.sock -B 庫1 庫2 庫3 >/opt/backup/mysql_bak_db.sql

多個庫之間用空格分隔

 

3、單表備份

mysqldump -uroot -p123456 -S /data/3306/data/mysql.sock 庫名 表名>/opt/backup/mysql_bak_db.sql

 

4、多表備份

mysqldump -uroot -p123456 -S /data/3306/data/mysql.sock 庫名表1 表2>/opt/backup/mysql_bak_db.sql

5、全庫備份

mysqldump -uroot -p123456 -S /data/3306/data/mysql.sock -A >/opt/backup/mysql_bak_db.sql

 

常用參數解析：

1、-B

如果想一次備份多個庫需要添加B參數，B參數會在備份資料中添加create database和use語句

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/59/93/wKiom1TYWPGSusuPAAEaOgmGTr0309.jpg" alt="wKiom1TYWPGSusuPAAEaOgmGTr0309.jpg" />

2、-F

在備份之前會先重新整理日誌，可以看到二進位檔案前滾產生新的二進位檔案。

3、--master-data

有二個值1或者2，等於1會在備份資料中增加如下語句：

CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000040‘,MASTER_LOG_POS=4543;

等於2會在備份資料中增加如下語句：

-- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000040‘,MASTER_LOG_POS=4543;

唯一區別就是有沒有被“--”注釋掉，如果備份的資料用於slave，等於1即可，此時從庫就知道應該從哪個地方開始讀二進位日誌，如果僅用於備份標識當前二進位是哪一個和位置點等於2合適。

 

4、-x

鎖表，備份的時候鎖表來保證資料一致性。

5、-d

只備份表結構不備份資料。

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/59/93/wKiom1TYWRmxtwMQAAFj8PwvVpQ937.jpg" alt="wKiom1TYWRmxtwMQAAFj8PwvVpQ937.jpg" />

6、-t

只備份表資料而不備份表結構。

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/59/90/wKioL1TYWiCSugQfAAE1KSDWmdo248.jpg" alt="wKioL1TYWiCSugQfAAE1KSDWmdo248.jpg" />

7、--compact

用於測試，會在螢幕上列印備份結果，上面的幾個例子就使用了這個參數，但真正備份時是不會用的。

 

5、--single-transaction

Innodb引擎保證資料一致性的參數，使用此參數後會話的安全隔離等級會被置為repeatble-read，此時其它會話提交的資料是不可視的，從而保證資料的一致性。

更多參數請參考help

mysql

命令介紹：

mysql通常用於登陸mysql伺服器，然後執行操作，其實mysql也提供了非互動式執行mysql命令的參數，同時mysql命令也是資料恢複非常重要的工具。

 

文法格式：

1、登陸mysql伺服器

mysql -uroot -p123456 -S /data/3306/data/mysql.sock

加S參數是考慮的多執行個體的情況，以下類同

 

2、非互動式執行mysql語句

mysql -uroot -p123456 -S /data/3306/data/mysql.sock  -e “use capinfo; showtables;”

 

3、資料恢複

mysql -uroot -p123456 -S /data/3306/data/mysql.sock  <mysql_bak_capinfo.sql

 

4、使用source恢複資料

登陸mysql伺服器後，使用source命令也可以恢複資料。

 

mysqlbinlog

命令介紹：

處理二進位日誌工具，可用於查看，從二進位日誌恢複資料，以及過濾需要的資料匯出到檔案。

 

常用參數：

1、-d or –database

　　過濾出需要的資料庫資料；

2、--start-position   --stop-position

　　過濾出開始位和結束位置的資料；

3、--start-datetime   --stop-datetime

　　過濾出開始時間和結束時間段資料；

 

功能：

1、查看二進位日誌

mysqlbinlog 二進位日誌名

 

2、過濾指定資料庫資料並匯出到檔案

mysqlbinlog –d 庫名　二進位日誌名 > capinfo.log

 

3、使用二進位日誌做增量恢複

樣本：指定位置的增量恢複

mysqlbinlog -d capinfo --start-position=98 --stop-position=768 mysql-bin.000038 |mysql -uroot -p123456 -S/data/3306/data/mysql.sock

指定時間段的增量恢複

mysqlbinlog -d capinfo --start-datetime=”2015-02-27 09:30:00”--stop-datetime=”2015-02-27 12:30:00” mysql-bin.000038|mysql -uroot -p123456 -S /data/3306/data/mysql.sock

注意：通常我們只會過濾出需要的資料，將其先放到一個檔案裡面，然後可以對檔案進行二次過濾，或者直接編輯檔案再匯入，這樣是比較妥當的做法。

 

二、備份參數最佳化

1、myISAM引擎備份參數最佳化

mysqldump -uroot -p123456 -F -A -B -x--master-data=2| gzip >/opt/backup/mysql_bak_db_full.sql.gz

 

2、innoDB引擎備份參數最佳化

mysqldump -uroot -p123456 -F -A -B --single-transaction--master-data=2| gzip >/opt/backup/mysql_bak_db_full.sql.gz

 

唯一區別：myISAM引擎使用x參數鎖表備份，而innoDB引擎則使用—single-transaction將會話的隔離等級修改成repeatble-read來保證資料一致性，顯然innodb引擎的資料控制粒度更強。

 

三、分庫分表備份指令碼實現

1、分庫備份實現指令碼

指令碼思路：

如果庫較少，可以使用forin db1 db2 db3；do ;done來執行個體

如果庫較多，則應該先用showdatabases取出庫名，做為for迴圈變數的取值列表，然後再執行備份操作：

取庫名：

mysql-uroot -p123456 -S /data/3306/data/mysql.sock -e "showdatabases;"|egrep -v"Database|information_schema|mysql|performance_schema"

備份一個庫：

mysqldump -uroot -p123456 -S/data/3306/data/mysql.sock --compact-B -F --master-data=2 --single-transaction –events\ capinfo|gzip>/opt/backup/mysql_bak_db_capinfo.sql.gz

 

瞭解指令碼思路了指令碼就很好寫了，下面自己編寫的一個樣本指令碼：

#!/bin/sh  cmdPath="/usr/local/mysql/bin"my_user="root"my_pass="123456"bakDir="/opt/backup"socketfile="/data/3306/data/mysql.sock"  dbList=`mysql -u$my_user-p$my_pass -S $socketfile -e "show databases"        |egrep -v"Database|information_schema|mysql|performance_schema"`  for db in $dbListdo        $cmdPath/mysqldump -u$my_user-p$my_pass -S $socketfile -B -F         --master-data=2 --single-transaction--events         $db | gzip >$bakDir/mysql_bak_db_${db}_$(date+%Y%m%d).sql.gzdone

 

2、分表備份實現指令碼

指令碼思路：

大致思路是在庫中再分表進行備份，那就會用到for迴圈嵌套，外層for迴圈是庫，內層for迴圈是表，並將每個庫中的表存放在各自庫名命名的目錄下面。

取到庫中的表名：

mysql -uroot -p123456 -S /data/3306/data/mysql.sock -e "showtables from capinfo"|sed ‘1d‘




樣本指令碼：

#!/bin/sh cmdPath="/usr/local/mysql/bin"my_user="root"my_pass="123456"bakDir="/opt/backup"socketfile="/data/3306/data/mysql.sock"  dbList=`mysql-u$my_user -p$my_pass -S $socketfile -e "show databases"        |egrep -v"Database|information_schema|mysql|performance_schema"`  fordb in $dbListdo        table=`mysql -u$my_user -p$my_pass -S$socketfile -e "showtables from $db"|sed ‘1d‘`        for tb in $table        do                mkdir -p $bakDir/$db                $cmdPath/mysqldump -u$my_user-p$my_pass -S $socketfile -F                 --master-data=2--single-transaction --events $db $tb | gzip >$bakDir/${db}/mysql_bak_tb_${tb}_$(date+%Y%m%d).sql.gz        donedone







本文出自 “平平淡淡才是真” 部落格，請務必保留此出處http://codings.blog.51cto.com/10837891/1735201

MYSQL備份與恢複精華篇