二進制日志簡要:

專注于為中小企業(yè)提供成都網站建設、成都做網站服務,電腦端+手機端+微信端的三站合一,更高效的管理,為中小企業(yè)新化免費做網站提供優(yōu)質的服務。我們立足成都，凝聚了一批互聯(lián)網行業(yè)人才，有力地推動了千余家企業(yè)的穩(wěn)健成長，幫助中小企業(yè)通過網站建設實現(xiàn)規(guī)模擴充和轉變。

二進制日志通常作為備份的重要資源，所以再說備份之前我們來回顧下前面專題講過的二進制日志內容。

1.二進制日志內容

引起MySQL服務器改變的任何操作。

復制功能依賴于此日志。

從服務器通過主服務器的二進制日志完成主從復制，在執(zhí)行之前保存于中繼日志中。

從服務器通?？梢躁P閉二進制日志以提升性能。

2.二進制文件的格式表現(xiàn)形式:

默認在安裝目錄下，存在mysql-bin.000001,mysql-bin.00002的二進制文件

另外還有mysql-bin.index用來記錄被mysql管理的二進制文件列表

如果需要刪除二進制日志時，切勿直接二進制文件，這樣會導致mysql管理混亂。

3.二進制文件查看相關mysql命令。

#> SHOW MASTER STATUS;查看正在使用的二進制文件

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000007 |    22094 |              |                  |
+------------------+----------+--------------+------------------+

#> FLUSH LOGS;手動滾動二進制日志

mysql> FLUSH LOGS;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000008 |      107 |              |                  |
+------------------+----------+--------------+------------------+
#滾動以后，mysql重新創(chuàng)建一個新的日志mysql-bin.000008

#> SHOW BINARY LOGS 顯示使用過的二進制日志文件。

mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       126 |
| mysql-bin.000002 |      2576 |
| mysql-bin.000003 |       126 |
| mysql-bin.000004 |       126 |
| mysql-bin.000005 |       126 |
| mysql-bin.000006 |       126 |
| mysql-bin.000007 |     22137 |
| mysql-bin.000008 |       107 |
+------------------+-----------+

#> SHOW BINLOG EVENTS;以表的形式查看二進制文件

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count] 
mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000001' \G;
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 107
       Info: Server ver: 5.5.33-log, Binlog ver: 4

4.MySQL二進制日志文件讀取工具mysqlbinlog

Usage: mysqlbinlog [options] log-files            
     --start-datetime
     --stop-datetime
     --start-position
     --stop-position

截取指定位置的日志記錄

# mysqlbinlog --start-position 15642 --stop-position 15643 /mydata/data/mysql-bin.000001
--截取結果如下:
# at 15642
#160612 16:56:52 server id 1  end_log_pos 15760     Query    thread_id=6    exec_time=0    error_code=0
use `hellodb`/*!*/;

根據以上截取結果第二行，進行解釋二進制日志內容            
     1)時間點： 160612 16:56:52
     2)服務器ID： server id 1 服務器ID主要用于標記日志產生的服務器，主要用于雙主模型中，互為主從，確保二進制文件不會被相互循環(huán)復制
     3)記錄類型： Query  
     4)線程號： thread_id = 6 
     5)語句的時間戳和寫入二進制日志文件的時間差； exec_time=0 
     6)事件內容
     7)事件位置 #at 15642 
     8)錯誤代碼 error_code=0 
     9)事件結束位置 end_log_pos也就是下一事件開始的位置

5.二進制日志格式

由bin_log_format={statement|row|mixed}定義

1)statement:基于語句，記錄生成數(shù)據的語句

#缺點在于如果當時插入信息為函數(shù)生成，有可能不同時間點執(zhí)行結果不一樣。

例如:

mysql > INSERT INTO tb1 VALUE(CURRENT_DATE());

2)row:基于行數(shù)據

缺點在于，有時候數(shù)據量會過大

3)mixed:混合模式，又mysql自行決定何時使用statement,何時使用row模式

6.二進制相關參數(shù)總結:

1)log_bin = {ON|OFF} 還可以是個文件路徑，主要用于控制全局binlog的存放位置和是否開啟

2)log_bin_trust_function_creators 是否記錄在

3)sql_log_bin = {ON|OFF} 會話級別是否關閉binlog， 如果關閉當前會話內的操作將不會記錄

4)sync_binlog 是否馬上同步事務類操作到二進制日志中

5)binlog_format = {statement|row|mixed} 二進制日志的格式，上面單獨提到了

6)max_binlog_cache_size = 二進制日志緩沖空間大小，僅用于緩沖事務類的語句；

7)max_binlog_stmt_cache_size = 語句緩沖，非事務類和事務類共用的空間大小

8)max_binlog_size = 二進制日志文件上限，超過上限后則滾動

9)刪除二進制日志
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }

刪除二進制日志例:

mysql> PURGE BINARY LOGS TO 'mysql-bin.000001';
mysql> PURGE BINARY LOGS BEFORE '2016-06-12 00:00:00';

Tips:切勿將二進制日志與數(shù)據文件放在同一設備及同一目錄下。

二進制日志備份與恢復:

為什么要做備份:

1.災難恢復

2.審計，數(shù)據庫在過去某一個時間點是什么樣的

3.測試

備份目的是什么呢？

1.用于恢復，例如：數(shù)據誤刪除、數(shù)據庫損壞。

2.備份結束后，需要周期性的做恢復測試，以便保證備份的數(shù)據的完整。

備份類型:

1.根據備份時，服務器是否在線

1)冷備(cold backup): 服務器離線，讀寫操作都不能進行

2)溫備份:全局施加鎖共享鎖，只能讀不能寫

3)熱備(hot backup):數(shù)據庫在線，讀寫照樣進行

2.根據備份時的數(shù)據集分類

1)完全備份(full backup)

2)部分備份(partial backup)

3.根據備份時的接口

1)物理備份(physical backup)：直接賦值數(shù)據文件，打包歸檔

特點:

不需要額外工具，直接歸檔命令即可，但是可以跨平臺能比較差，如果數(shù)據量超過幾十個G，則使用于物理備份

2)邏輯備份(logical backup): 把數(shù)據抽取保存在sql腳本中

特點:

可以使用文本編輯器編輯；

導入方便，直接讀取sql語句即可；

邏輯備份恢復時間慢，占據空間大；

無法保證浮點數(shù)的精度；

恢復完數(shù)據庫后需要重建索引；

4.根據備份整個數(shù)據還是變化數(shù)據

1)完全備份 full backup

2)增量備份 incremental backup

在不同時間點起始備份一段數(shù)據

比較節(jié)約空間

3)差異備份 differential backup

5.備份策略，需要考慮因素如下:

備份方式

備份實踐

備份成本

鎖時間

時長

性能開銷

恢復成本

恢復時長

可以容忍丟失的數(shù)據量

6.備份內容

1)數(shù)據庫中的數(shù)據

2)配置文件

3)mysql中的代碼:存儲過程，存儲函數(shù)，觸發(fā)器

4)OS相關的配置文件，crontab中的備份策略腳本

5)如果是主從復制的場景中:跟復制相關的信息

6)二進制日志文件需要定期備份，一旦發(fā)現(xiàn)二進制文件出現(xiàn)問題，需要馬上對數(shù)據進行完全備份。

7.常用的備份工具

1)mysqldump:邏輯備份工具

innodb：熱備，溫備

MyISAM,Aria：溫備

單線程備份恢復比較慢

2)mysqldumper：多線程的mysqldump

3)vm-snapshot：

接近于熱備的工具:因為要先請求全局鎖，而后創(chuàng)建快照，并在創(chuàng)建快照完成后釋放全局鎖；

使用cp、tar等工具進行物理備份

備份與恢復速度較快

但是很難實現(xiàn)增量備份，并且請求全局需要等待一段時間，在繁忙的服務器上尤其如此；

4)通過查詢語句備份數(shù)據:

mysql > SELECT * FROM tb1 INTO OUTFILE '/path/to/somefile';    --導出查詢數(shù)據為文本文件
mysql > LOAD DATA INFILE '/path/from/somefile';                --導入文本文件至數(shù)據庫中
部分備份工具，不會備份關系定義，僅備份表中的數(shù)據；
邏輯備份鞏固，快于mysqldump，因為不備份表格式信息

5)Innobase：商業(yè)備份工具,innobackup

InnoDB熱備，增量備份

MyISAM溫備，不支持增量，只有完全備份

屬于物理備份，速度快；

6)Xtrabackup:由Percona提供的開源備份工具

InnoDB熱備，增量備份；

MyISAM溫備，不支持增量；

7)mysqlhotcopy:接近冷備，基本不使用。

mysqldump工具基本使用

1.mysqldump [OPTIONS] database [tables....]

還原庫時數(shù)據庫必須存在，不存在需要手動創(chuàng)建

--all-databases:　備份所有庫    
    --databases db1 db2 ...: 備份指定的多個庫，如果使用此命令，恢復時將不用手動創(chuàng)建庫
    --lock-all-tables：請求鎖定所有表之后再備份，對MyISAM、InnoDB、Aria做溫備
    --lock-table: 對正在備份的表加鎖，但是不建議使用，如果其它表被修改，則備份后表與表之間將不同步
    --single-transaction: 能夠對InnoDB存儲引擎實現(xiàn)熱備；
    啟動一個很大的大事物，基于MOCC可以保證在事物內的表版本一致
    自動加鎖不需要，再加--lock-table, 可以實現(xiàn)熱備
備份代碼：
    --events: 備份事件調度器代碼
    --routines: 備份存儲過程和存儲函數(shù)
    --triggers：備份觸發(fā)器
備份時滾動日志：
    --flush-logs: 備份前、請求到鎖之后滾動日志；
    方恢復備份時間點以后的內容
復制時的同步位置標記：主從架構中的，主服務器數(shù)據。效果相當于標記一個時間點。
    --master-data=[0|1|2]
    0:不記錄
    1:記錄為CHANGE MASTER語句
    2:記錄為注釋的CHANGE MASTER語句

2.使用mysqldump備份簡要過程如下:

1)請求鎖:-lock-all-tables或使用-singe-transaction進行innodb熱備；

2)滾動日志: -flush-logs

3)選定要備份的數(shù)據庫:-databases

4)記錄二進制日志文件及位置:-master-data=

mysql > FLUSH TABLES WITH READ LOCK;

3.恢復:

恢復過程無需寫到二進制日志中

Tips:關閉二進制日志，關閉其他用戶連接；

4.備份策略:基于mysqldump

備份:mysqldump+二進制日志文件；

周日做一次完全備份:備份的同時滾動日志

周一至周六:備份二進制日志；

恢復:

完全備份+各二進制日志文中至此刻的事件

5.二進制備份恢復使用案例

1)完全備份mysql數(shù)據庫，并實現(xiàn)還原

備份之前的數(shù)據庫及數(shù)據情況

mysql> SHOW DATABASES;    --查詢當前數(shù)據庫服務器下所有的數(shù)據庫
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
--開啟mysql二進制日志功能
# vim /etc/my.cnf 
[server]
log-bin=/mydata/data/mysql-bin
--修改配置文件后重啟mysql服務
# service mysqld restart
--經安全考慮在備份的時候最好不要使用管理員賬號操作，所以需要創(chuàng)建一個專門的用戶用來做mysqldump備份
mysql> GRANT SELECT,SHOW DATABASES,LOCK TABLES,RELOAD,EVENT,SUPER ON *.* TO 'sqldump'@'172.16.100.%' IDENTIFIED BY 'sqldum';

然后使用mysqldump進行備份

# mysqldump --events --master-data=2 --all-databases --lock-all-tables --flush-logs -usqldump -h272.16.100.7 -psqldump > /tmp/all_dump_bak.sql
以上賦予sqldump用戶權限對應分析:
SHOW DATABASES --> --all-databases
LOCK TABLES --> --lock-all-tables
RELOAD ---> --flush-logs
EVENT ---> --events
SUPER ---> --master-data 主要授予SHOW MASTER STATUS權限

在hellodb.students表中插入一行，再刪除hellodb庫，然后再恢復
mysql> INSERT hellodb.students (Name,Age,Gender) VALUE ('Samlee GZ',28,'M');
mysql> DROP DATABASE hellodb;

恢復:
此時需要關閉session級別的二進制日志功能，恢復內容不記錄日志
mysql> SET SESSION sql_log_bin='OFF';
mysql> SOURCE /tmp/all_dump_bak.sql;

此時數(shù)據庫恢復到，插入Samlee GZ 這一行之前的數(shù)據，然后通過二進制日志恢復直到數(shù)據庫被刪除之前的內容
由于設置了--master-data選項，所以在備份文件中可以找到如下一行
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=107;
這一行標記了新的二進制文件從那個點起始，通過查看二進制日志mysql_bin.000010可以得知，還需要恢復107到340也就是hellodb被刪除之前的數(shù)據
# mysqlbinlog --start-position 107 --stop-position 340 /mydata/data/mysql-bin.000010 > /tmp/binlog107_340.sql
mysql> SOURCE /tmp/binlog107_340.sql;
mysql> SELECT * FROM hellodb.students；--查詢我們可以看到最后一行是自己插入的數(shù)據
|    26 | Samlee GZ     |  28 | M      |    NULL |      NULL |

2)使用shell腳本自動定時備份--mysqldump方式:

腳本內容如下:

#!/bin/bash
#
## Using mysqldump to backup the all databases
function backup {
    prefix=$1
    outputdir=$2
    [ -d $outputdir ] || (echo "No output dir,create one!" && mkdir -p $outputdir)
    now=`/bin/date +'%Y_%b_%d_%k%M'`
    mysqldump='/usr/local/mysql/bin/mysqldump'
    mysqldump --events -master-data=2 --all-databases --single-transaction --flush-logs -usqldump -h272.16.100.7 -psqldump > $outputdir/${prefix}_${now}.sql
}
function main {
    case $# in
    2)
    backup $1 $2
    ;;
    *)
    echo 'Usage: ./mysqldump_backup.sh prefix outputdir'
    ;;
    esac
}
main $*
You have new mail in /var/spool/mail/root

設置周期任務計劃表

# crontab -e            
* * * * * /usr/local/mysql/bin/mysqldump_backup.sh back /tmp/mysqlback
# ll /tmp/mysqlback/    --實現(xiàn)每分鐘備份一次
-rw-r--r-- 1 root root 0 Jun 14 11:13 back_2016_Jun_14_1113.sql
-rw-r--r-- 1 root root 0 Jun 14 11:14 back_2016_Jun_14_1114.sql
-rw-r--r-- 1 root root 0 Jun 14 11:15 back_2016_Jun_14_1115.sql
-rw-r--r-- 1 root root 0 Jun 14 11:16 back_2016_Jun_14_1116.sql
-rw-r--r-- 1 root root 0 Jun 14 11:17 back_2016_Jun_14_1117.sql
-rw-r--r-- 1 root root 0 Jun 14 11:18 back_2016_Jun_14_1118.sql
-rw-r--r-- 1 root root 0 Jun 14 11:19 back_2016_Jun_14_1119.sql
-rw-r--r-- 1 root root 0 Jun 14 11:20 back_2016_Jun_14_1120.sql

lvm-snapshot：基于LVM快照的備份

關于快照:

1.事務日志跟數(shù)據文件必須在同一個卷上；

2.剛剛創(chuàng)立的快照卷，里面沒有任何數(shù)據，所有的數(shù)據均來源于原卷

3.一旦員卷數(shù)據發(fā)生修改，修改的數(shù)據將復制到快照卷中，此時訪問數(shù)據-部分來自于快照卷，一部分來自于原卷

4.當快照使用過程中，如果修改的數(shù)據量大于快照卷容量，則會導致快照卷崩潰。

5.快照卷本身不是備份，只是提供一個實踐一致性的訪問目錄。

基于快照備份幾乎為熱備:

1.創(chuàng)建快照卷之前，要請求MySQL的全局鎖；在快照創(chuàng)建完成之后釋放鎖；

2.如果是Innodb引擎，當flush tables后會有一部分保存在事務日志中，卻不在文件中。因此恢復時候，需要事務日志和數(shù)據文件

但是釋放鎖以后，事務日志的內容會同步數(shù)據文件中，因此備份內容并不絕對是鎖釋放時刻的內容，由于有些未完成的事務已經完成，但在備份數(shù)據中因為沒有完成而回滾。因此需要借助二進制日志往后走一段。

基于快照備份的注意事項:

1.事務日志跟數(shù)據文件必須在同一個卷中；

2.創(chuàng)建快照卷之前，要請求MySQL的全局鎖；在快照創(chuàng)建完成之后釋放鎖；

3.請求全局鎖完成之后，做一次日志滾動；做二進制日志文件及位置標記(手動進行);

備份與恢復的簡要步驟如下:

備份

1.請求全局鎖，并滾動日志

mysql > FLUSH TABLES WITH READ LOCK;
mysql > FLUSH LOGS;

2.生成二進制日志文件及位置標記(手動進行)

# mysql -e 'SHOW MASTER STATUS' > /path/to/orignal_volume

3.創(chuàng)建快照卷

# lvcreate -L -s -n -p r /path/to/some_lv

4.釋放全局鎖

5.掛載快照卷并備份

6.備份完成之后，刪除快照卷

恢復:

1.二進制日志保存好；

提取備份之后的所有事件至某sql腳本中

2.還原數(shù)據，修改權限及屬主屬組等，并啟動mysql

3.做即時點還原

4.生產環(huán)境下，一次大型恢復后，需要馬上進行一次完全備份。

使用快照卷備份恢復mysql實例:

環(huán)境，實現(xiàn)創(chuàng)建一個myvg的卷組，mydata邏輯卷用來存儲mysql數(shù)據，掛載至/mydata/data

備份:

1.創(chuàng)建備份專用的賬號，授予權限FLUSH LOGS和LOCK TABLES

mysql> GRANT RELOAD,LOCK TABLES,SUPER ON *.* TO 'lvm'@'172.16.100.%' IDENTIFIED BY 'lvm';

mysql> FLUSH PRIVILEGES;

2.請求全局鎖，鎖定數(shù)據庫并且滾動日志

mysql > FLUSH TABLES WITH READ LOCK;

mysql > FLUSH LOGS;

3.記錄備份點

# mysql -ulvm -h272.16.100.7 -plvm -e 'SHOW MASTER STATUS' > /tmp/backp_point.txt
# cat /tmp/backp_point.txt
File    Position    Binlog_Do_DB    Binlog_Ignore_DB
mysql-bin.000010    499

4.創(chuàng)建快照卷并掛載快照卷及創(chuàng)建備份文件提取點

# lvcreate -L 100M -s -n mydata-snap -p r /dev/myvg/mydata 
# mount -t ext4 -o ro /dev/myvg/mydata-snap /mnt/
#  mkdir /backups

5.釋放鎖

# mysql -ulvm -h272.16.100.7 -plvm -e 'UNLOCK TABLES';
## 模擬寫入操作
mysql> UNLOCK TABLES;
mysql> CREATE DATABASE samleedb;

6.復制快照里面文件至備份文件存儲目錄

# cp -ar /mnt/data/ /backups/data-2016-06-14

7.備份完成，刪除快照卷，減少磁盤I/O

# umount /mnt/
# lvremove /dev/myvg/mydata-snap

故障模擬恢復: 數(shù)據庫存儲目錄損壞或整個服務器崩潰，并且數(shù)據存儲目錄全部被刪除

1.模擬數(shù)據損壞故障:

[root@mysql ~]# service mysqld stop
Shutting down MySQL.                                       [  OK  ]
[root@mysql ~]# rm -rf /mydata/data/*
[root@mysql ~]# service mysqld start
Starting MySQL....The server quit without updating PID file[FAILED]a/data/mysql.samlee.com.pid).
--此時發(fā)現(xiàn)mysql無法啟動了

2.將備份的數(shù)據文件復制回源目錄再啟動mysql

# cp -arp /backups/data-2016-06-14/* /mydata/data/
# service mysql start
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| samlee             |
| test               |
+--------------------+
此時顯示沒有samleedb，因為這個是備份之后創(chuàng)建的，因此需要通過之前記錄的二進制日志位置還原

3.查看之前記錄的記錄點。向后還原

# cat /tmp/backp_point.txt 
File    Position    Binlog_Do_DB    Binlog_Ignore_DB
mysql-bin.000010    499    
# mysqlbinlog /mydata/data/mysql-bin.000010 --start-position 499 > /tmp/2016_06_14.sql
mysql> source /tmp/2016_06_14.sql;
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| samlee             |
| test               |
| samleedb             |
+--------------------+

使用Xtrabackup進行MySQL備份

安裝:

1.簡介

  Xtrabackup是percona提供的mysql數(shù)據庫備份工具，據官方介紹，這也是世界上唯一一款開源的能夠對innodb和xtradb數(shù)據庫進行熱備的工具。特點:

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

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

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

4)自動實現(xiàn)備份校驗；

5)還原速度快；

2.安裝及安裝源獲取

其最新版本可以從以下URL獲得:

https://www.percona.com/downloads/XtraBackup/

安裝

# yum -y install  percona-toolkit-2.2.16-1.noarch.rpm 
# yum -y install  libev-4.15-1.el6.rf.x86_64.rpm
# yum -y install  percona-xtrabackup-2.3.2-1.el6.x86_64.rpm

備份實現(xiàn):

1.完全備份:

如果要使用一個最小權限的用戶進行備份，則可基于如下命令創(chuàng)建此類用戶:

Usage: innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/
--user:需要創(chuàng)建一個擁有最小權限的用戶
mysql> GRANT RELOAD,LOCK TABLES,REPLICATION CLIENT ON *.* TO 'xtrauser'@'localhost' IDENTIFIED BY 'xtrauser';
mysql> REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'xtrauser';
mysql> GRANT RELOAD,LOCK TABLES,REPLICATION CLIENT ON *.* TO 'xtrauser'@'localhost';
mysql> FLUSH PRIVILEGES;

/path/to/BACKUP_DIR：備份出來的數(shù)據存儲目錄，外加包含一些xtrabackup的元數(shù)據

使用innobackupex備份時，其會調用xtrabackup備份所有的InnoDB表，復制所有關于表結構定義的相關文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相關文件，同時還會備份觸發(fā)器和數(shù)據庫配置信息相關的文件。這些文件會被保存至一個以時間命令的目錄中。

示例:備份

# innobackupex --user=xtrauser --password=xtrauser /tmp/xtrabackup/
160615 09:47:34 Executing UNLOCK TABLES
160615 09:47:34 All tables unlocked
160615 09:47:34 Backup created in directory '/tmp/xtrabackup//2016-06-15_09-47-27'
MySQL binlog position: filename 'mysql-bin.000011', position '655'
160615 09:47:34 [00] Writing backup-my.cnf
160615 09:47:34 [00]        ...done
160615 09:47:34 [00] Writing xtrabackup_info
160615 09:47:34 [00]        ...done
xtrabackup: Transaction log of lsn (1604789) to (1604789) was copied.
160615 09:47:35 completed OK!

看到最后一行的時候，說明備份已經完成。

在備份的同時，innobackupex還會再備份目錄中創(chuàng)建如下文件:

(1)xtrabackup_checkpoints —— 備份類型（如完全或增量）、備份狀態(tài)（如是否已經為prepared狀態(tài)）和LSN(日志序列號)范圍信息；

# cat xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 1604789
last_lsn = 1604789
compact = 0
recover_binlog_info = 0

每個InnoDB頁(通常為16k大小)都會包含一個日志序列號，即LSN。LSN是整個數(shù)據庫系統(tǒng)的系統(tǒng)版本號，每個頁面相關的LSN能夠表明此頁面最近是如何發(fā)生改變的

在mysql中，存儲數(shù)據的數(shù)據塊會有按照順序的ID，如果某一塊數(shù)據被修改，將會賦予新的ID。根據這些ID，可以標記數(shù)據的新老程度。xtrabackup也就是使用這些ID來進行備份，和增量備份。

2.xtrabackup_binlog_info: mysql服務器當前正在使用的二進制日志文件及至備份這 一刻位置二進制日志事件的位置。

# cat xtrabackup_binlog_info 
mysql-bin.000011    655

3. xtrabackup_info: 包含很多xtrabackup工具信息以及所備份的數(shù)據庫信息

# cat xtrabackup_info 
uuid = 21c7cde7-329b-11e6-b888-000c2923351b
name = 
tool_name = innobackupex
tool_command = --user=xtrauser --password=... /tmp/xtrabackup/
tool_version = 2.3.2
ibbackup_version = 2.3.2
server_version = 5.5.33-log
start_time = 2016-06-15 09:47:31
end_time = 2016-06-15 09:47:34
lock_time = 0
binlog_pos = filename 'mysql-bin.000011', position '655'
innodb_from_lsn = 0
innodb_to_lsn = 1604789
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N

4.backup-my.cnf —— 備份命令用到的配置選項信息

# cat backup-my.cnf 
# This MySQL options file was generated by innobackupex.

# The MySQL server
[mysqld]
innodb_checksum_algorithm=innodb
innodb_log_checksum_algorithm=innodb
innodb_data_file_path=ibdata1:10M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=5242880
innodb_fast_checksum=false
innodb_page_size=16384
innodb_log_block_size=512
innodb_undo_directory=.
innodb_undo_tablespaces=0

5.xtrabackup_binlog_pos_innodb —— 二進制日志文件及用于InnoDB或XtraDB表的二進制日志文件的當前position。

在使用innobackupex進行備份時，還可以使用--no-timestamp選項來阻止命令自動創(chuàng)建一個以時間命名的目錄；如此一來，innobackupex命令將會創(chuàng)建一個BACKUP-DIR目錄來存儲備份數(shù)據。

準備(prepare)一個完全備份

一般情況下，在備份完成后，數(shù)據尚且不能用于恢復操作，因為備份的數(shù)據中可能會包含尚未提交的事務或已經提交但尚未同步至數(shù)據文件中的事務。

因此，此時數(shù)據文件仍處理不一致狀態(tài)。"準備"的主要作用正是通過回滾未提交的事務及同步提交的事務至數(shù)據文件也使得數(shù)據文件處于一致性狀態(tài)。

innobackupex命令的-apply-log選項可用于實現(xiàn)上述功能。如下面的命令:實際上就是把未完成的事務提交，準備工作需要在還原之前才執(zhí)行，在這之前都能執(zhí)行準備工作。

Usage:innobackupex --apply-log /path/to/BACKUP-DIR
示例:
# innobackupex --apply-log /tmp/xtrabackup/2016-06-15_09-47-27/
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1605142
160615 10:26:54 completed OK!
看到這幾行說明已經完成"準備"
在實現(xiàn)"準備"的過程中，innobackupex通常還可以使用--use-memory選項來指定其可以使用的內存大小，默認通常為100M。如果有足夠的內容可用，可以多劃分一些內存給prepare的過程使用，以提高其完成速度。

從一個完全備份中恢復數(shù)據

注意：恢復不用啟動MySQL
innobackupex命令的--copy-back選項用于執(zhí)行恢復操作，其通過復制所有數(shù)據相關的文件至mysql服務器DATADIR目錄中來執(zhí)行恢復過程。innobackupex通過backup-my.cnf來獲取DATADIR目錄的相關信息。

usage:innobackupex --copy-back  /path/to/BACKUP-DIR
# innobackupex --copy-back /tmp/xtrabackup/2016-06-15_09-47-27/
如果執(zhí)行正確，其輸出信息的最后幾行通常如下：
160615 10:46:34 [01]        ...done
160615 10:46:34 completed OK!

當數(shù)據恢復至DATADIR目錄以后，還需要確保所有數(shù)據文件的屬主和屬組均為正確的用戶，如mysql，否則，在啟動mysqld之前還需要事先修改數(shù)據文件的屬主和屬組。

# chown -R mysql:mysql /mydata/data/
# service mysqld start

使用innobackupex進行增量備份

            每個InnoDB的頁面都會包含一個LSN信息，每當相關的數(shù)據發(fā)生改變，相關的頁面的LSN就會自動增長。這正是InnoDB表可以進行增量備份的基礎，即innobackupex通過備份上次完全備份之后發(fā)生改變的頁面來實現(xiàn)。

        1. 備份過程： 

            要實現(xiàn)第一次增量備份，可以使用下面的命令進行：

usage: innobackupex –incremental /backup –incremental-basedir=BASEDIR
BASEDIR：指的是完全備份所在的目錄，
此命令執(zhí)行結束后，innobackupex命令會在/backup目錄中創(chuàng)建一個新的以時間命名的目錄以存放所有的增量備份數(shù)據。另外，在執(zhí)行過增量備
份之后再一次進行增量備份時，其–incremental-basedir應該指向上一次的增量備份所在的目錄。

                需要注意的是，增量備份僅能應用于InnoDB或XtraDB表，對于MyISAM表而言，執(zhí)行增量備份時其實進行的是完全備份。

增量備份恢復案例應用:

首先做一次完全備份，在之前完全備份的基礎上做兩次增量備份，之間創(chuàng)建兩個數(shù)據庫。

完全備份:

# innobackupex --user=xtrauser --password=xtrauser --no-timestamp /tmp/xtrabackup/full_backup
--創(chuàng)建測試數(shù)據庫
mysql> CREATE DATABASE samlee1;

第一次增量備份:

# innobackupex --user=xtrauser --password=xtrauser --incremental /tmp/xtrabackup/ --incremental-basedir=/tmp/xtrabackup/full_backup/
mysql> CREATE DATABASE samlee2;

第二次增量備份:

# innobackupex --user=xtrauser --password=xtrauser --incremental /tmp/xtrabackup/ --incremental-basedir=/tmp/xtrabackup/2016-06-15_13-26-08/

2. 準備過程

準備”(prepare)增量備份與整理完全備份有著一些不同，尤其要注意的是：

1)需要在每個備份(包括完全和各個增量備份)上，將已經提交的事務進行“重放”?！爸胤拧敝?，所有的備份數(shù)據將合并到完全備份上。

2)基于所有的備份將未提交的事務進行“回滾”。于是，操作就變成了：不能回滾，因為有可能第一次備份時候沒提交，在增量中已經成功提交

使用方法如下：

# innobackupex --apply-log --redo-only BASE-DIR

接著執(zhí)行:

 # innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1

而后就是第二個增量

 # innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-2

這樣一個個準備完成后，所有增量備份將合并至完全備份中。

其中BASE-DIR指的是完全備份所在的目錄，而INCREMENTAL-DIR-1指的是第一次增量備份的目錄，INCREMENTAL-DIR-2指的是第二次增量備份的目錄，其它依次類推，即如果有多次增量備份，每一次都要執(zhí)行如上操作；

示例:

# innobackupex --apply-log --redo-only /tmp/xtrabackup/full_backup
# innobackupex --apply-log --redo-only /tmp/xtrabackup/full_backup/ --incremental-dir=/tmp/xtrabackup/2016-06-15_13-26-08
# innobackupex --apply-log --redo-only /tmp/xtrabackup/full_backup/ --incremental-dir=/tmp/xtrabackup/2016-06-15_13-28-42/

 3. 恢復過程：與完全備份類似，直接copy-back完全備份的那個目錄。 此時所有的增量已經正好到完全備份的目錄中

# service mysqld stop
# rm -rf /mydata/data/*
# innobackupex --copy-back /tmp/xtrabackup/full_backup/
# chown -R mysql:mysql /mydata/data
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| samlee             |
| samlee1            |
| samlee2            |
| test               |
+--------------------+
可以看到我們前面創(chuàng)建的兩個數(shù)據庫都已經恢復成功了。

Xtrabackup的“流”及“備份壓縮”功能

            Xtrabackup對備份的數(shù)據文件支持“流”功能，即可以將備份的數(shù)據通過STDOUT傳輸給tar程序進行歸檔，而不是默認的直接保存至某備份目錄中。要使用此功能，僅需要使用–stream選項即可。如：

Usage: innobackupex –stream=tar  /backup | gzip > /backup/`date +%F_%H-%M-%S`.tar.gz

示例:

# innobackupex --user=xtrauser --password=xtrauser --stream=tar /tmp/xtrabackup/ | gzip > /tmp/xtrabackup/`date +%F_%H-%M-%S`.tar.gz

甚至也可以使用類似如下命令將數(shù)據備份至其它服務器：

# innobackupex --stream=tar  /backup | ssh user@www.samlee.com  "cat -  > /backups/`date +%F_%H-%M-%S`.tar"

此外，在執(zhí)行本地備份時，還可以使用--parallel選項對多個文件進行并行復制。此選項用于指定在復制時啟動的線程數(shù)目。當然，在實際進行備份時要利用此功能的便利性，也需要啟用innodb_file_per_table選項或共享的表空間通過innodb_data_file_path選項存儲在多個ibdata文件中。對某一數(shù)據庫的多個文件的復制無法利用到此功能。其簡單使用方法如下：

# innobackupex --parallel  /path/to/backup

同時，innobackupex備份的數(shù)據文件也可以存儲至遠程主機，這可以使用--remote-host選項來實現(xiàn)：

# innobackupex --remote-host=root@www.samlee.com  /path/IN/REMOTE/HOST/to/backup

 導入或導出單張表

            默認情況下，InnoDB表不能通過直接復 制表文件的方式在mysql服務器之間進行移植，即便使用了innodb_file_per_table選項。而使用Xtrabackup工具可以實現(xiàn)此 種功能，不過，此時需要“導出”表的mysql服務器啟用了innodb_file_per_table選項（嚴格來說，是要“導出”的表在其創(chuàng)建之 前，mysql服務器就啟用了innodb_file_per_table選項），并且“導入”表的服務器同時啟用了 innodb_file_per_table和innodb_expand_import選項。

        在創(chuàng)建數(shù)據庫之前，在配置文件中server段下面寫入innodb_file_per_table=1

1) “導出”表

導出表是在備份的prepare階段進行的，因此，一旦完全備份完成，就可以在prepare過程中通過–export選項將某表導出了：

Usage: innobackupex –apply-log –export /path/to/backup

示例：

# innobackupex --user=xtrauser --password=xtrauser /tmp/xtrabackup/
# innobackupex --apply-log --export /tmp/xtrabackup/2016-06-15_14-45-06/
--此命令會為每個innodb表的表空間創(chuàng)建一個以.exp結尾的文件，這些以.exp結尾的文件則可以用于導入至其它服務器。
# ls /tmp/xtrabackup/2016-06-15_14-45-06/hellodb/*.exp
classes.exp  students.exp    scores.exp    toc.exp
coc.exp      teachers.exp    courses.exp

2 )“導入”表

使用show CREATE TABLE mytable; 來查看原始表創(chuàng)建命令 

要在mysql服務器上導入來自于其它服務器的某innodb表，需要先在當前服務器上創(chuàng)建一個跟原表表結構一致的表，而后才能實現(xiàn)將表導入：

事例： 這里以students 表為例：

mysql> SHOW CREATE TABLE hellodb.students \G
*************************** 1. row ***************************
       Table: students
Create Table: 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
1 row in set (0.02 sec)

在samlee庫中創(chuàng)建這個表:
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;
    
 然后將此表的表空間刪除:
 mysql> ALTER TABLE samlee.students DISCARD TABLESPACE;
 接下來，將來自于“導出”表的服務器的students表的students.ibd和students.exp文件復制到當前服務器的數(shù)據目錄，然后使用如下命令將其“導入”：    
 ##注意權限
# cp /tmp/xtrabackup/2016-06-15_14-45-06/hellodb/students{.ibd,.exp} /mydata/data/samlee/
# chown mysql.mysql /mydata/data/samlee/students.*
mysql> ALTER TABLE samlee.students IMPORT TABLESPACE;

備份注意：

        1. 將數(shù)據和備份放在不同的磁盤設備上；異機或異地備份存儲較為理想；

        2. 備份的數(shù)據應該周期性地進行還原測試；

        3. 每次災難恢復后都應該立即做一次完全備份；

        4. 針對不同規(guī)模或級別的數(shù)據量，要定制好備份策略；

        5. 二進制日志應該跟數(shù)據文件在不同磁盤上，并周期性地備份好二進制日志文件；

從備份中恢復應該遵循步驟：

        1. 停止MySQL服務器；

        2. 記錄服務器的配置和文件權限；

        3. 將數(shù)據從備份移到MySQL數(shù)據目錄；其執(zhí)行方式依賴于工具；

        4. 改變配置和文件權限；

        5. 以限制訪問模式重啟服務器；mysqld的–skip-networking選項可跳過網絡功能；

            方法：編輯my.cnf配置文件，添加如下項：

            skip-networking

            socket=/tmp/mysql-recovery.sock

        6. 載入邏輯備份（如果有）；而后檢查和重放二進制日志；

        7. 檢查已經還原的數(shù)據；

        8. 重新以完全訪問模式重啟服務器；

            注釋前面在my.cnf中添加的選項，并重啟；

本文標題：關系型數(shù)據庫之Mysql備份(五)
路徑分享：http://aaarwkj.com/article14/pjccge.html

成都網站建設公司_創(chuàng)新互聯(lián)，為您提供微信公眾號、動態(tài)網站、用戶體驗、網站設計、品牌網站制作、網站維護

廣告

聲明：本網站發(fā)布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)