(7) MySQL資料庫備份詳解

對於任何資料庫來說，備份都是非常重要的

資料庫複製不能取代備份的作用

比如我們由於誤操作，在主資料庫上刪除了一些資料，由於主從複製的時間很短，在發現時，從資料庫上的資料可能也已經被刪除了，
我們不能使用從資料庫上的資料來恢復主資料庫上的資料，只能通過備份進行誤刪除資料的恢復

一. 備份的分類

1.按備份的結果來分：

邏輯備份

其備份結果為SQL語句，適合於所有儲存引擎，恢復時需要較多時間，邏輯備份時，對於MyISAM儲存引擎是需要進行鎖表操作的，通過使用的mysqldump就是一種邏輯備份工具

物理備份

是對資料庫目錄的拷貝，其備份結果的大小通常也與備份資料的資料目錄大小相同，物理備份和恢復通常比邏輯備份要快，因為只需要對mysql資料目錄拷貝即可，也正是因為這點，對於記憶體表只能備份其結構，無法備份資料(因為其資料儲存在記憶體中，沒有實際的物理資料檔案)

物理備份的方式

進行物理備份，我們可以採用離線備份和線上備份的方式進行備份

 離線備份：需要對資料庫進行停機，或對整個資料庫進行鎖定的情況下進行
 線上備份：需要使用第三方工具，如  XtraBackup

2. 按備份的資料庫的內容來分

2.1 全量備份

是對整個資料庫的一個完整備份

2.2 增量備份

是在上次全量或增量備份的基礎上，對更改過的資料進行的備份

注意

Mysql官方提供的mysqldump命令並不支援增量備份

通常情況要使用mysqldump來進行增量備份的話，增量備份只能通過備份Mysql的二進位制日誌來實現

XtraBackup本身就提供了增量備份的功能，所以對於Innodb來說，使用XtraBackup進行備份更加安全高效

無論是使用XtraBackup還是Mysqldump 進行備份，要進行基於時間點的恢復時都需要利用Mysql的二進位制日誌，所以通常情況下我們需要對Mysql的二進位制日誌也進行備份

二. 使用mysqldump進行備份

mysqldump 是mysql官方提供的邏輯備份工具，其備份結果是可讀的SQL檔案

mysqldump 支援多種語法

1. 常用語法

對一個資料庫下的一個或多個表進行備份

mysqldump [OPTIONS] database [tables]

備份多個表時，table直接用空格進行分隔

對指定的多個資料庫進行備份

mysqldump [OPTIONS] --database [OPTIONS] DB1 [DB2..]

對整個mysql例項下的所有資料庫進行備份

mysqldump [OPTIONS] --all-database [OPTIONS]

2. 常用引數

-u，--user = name    #  指定備份時所使用的資料庫賬號
-p. --password [=name]    # 指定賬號的密碼
--single-transaction  # 使用此引數會在備份前先執行start transaction命令啟動一個事務，以此來獲得資料庫備份時的資料的一致性，由於是通過事務保證資料的一致性，所以此引數只對Innodb儲存引擎有效；當使用此引數進行備份時，要確保沒有任何DDL語句在執行，因為Innodb的資料一致性的隔離級別並不能隔離DDL操作

-l, --lock-tables  # 如果沒有使用非事務儲存引擎，使用此引數保證備份時資料的一致性，在備份時會依次鎖住每個資料庫下的所有表，一般用於MyISAM儲存引擎的備份，使用了此引數，在資料庫備份時，只能進行讀操作，由於此引數是鎖住一個資料庫下的所有表，備份時可以保證一個資料庫下的所有表的資料一致性，但不能保證整個Mysql例項下的所有資料庫的所有表的資料一致性，這也是為什麼推薦使用Innodb引擎的一個原因
 
lock-tables 與 single-transaction 引數是互斥的，不能同時使用，所以資料庫中如果混合使用了Innodb表和MyISAM表就只能使用lock-tables來進行備份了

-x, --lock-all-tables  # 此引數可以對整個Mysql例項下的所有資料庫進行加鎖，可以避免lock-tables不能保證整個Mysql例項下的所有資料庫的所有表的資料一致性的問題，備份時同樣會將資料庫變為只讀的狀態

--master-data = [1/2]    # 無論是時間恢復還是新建slave例項都要用到這個引數，此引數有兩個可選值，當值為1時，備份中只記錄change_master語句，當值為2時，change_master語句會以註釋的形式出現在備份檔案中；預設值為1，且當使用了此引數時會忽略lock-tables引數，在備份時如果使用了此引數，但是沒有使用single-transaction引數，則會自動使用lock-all-tables引數

如果我們要備份的資料庫中包含了儲存過程，觸發器，資料庫排程事件時，要備份這些資料庫物件時，必須指定以下引數才能對相應資料庫進行備份

-R, --routines     # 指定要備份的資料庫中存在的的儲存過程
--triggers     # 指定要備份的資料庫中存在的的觸發器
-E,--events      # 指定要備份的資料庫中存在的的排程事件

除了以上引數，還有一些引數在備份時也會用到

--hex-blob    # 因為mysqldump備份匯出的是文字檔案，如果匯出的資料中含有以上型別，在文字格式下，有些字元是不可見的，如果使用了此引數使，將會對資料庫中所存在binary、varbinary、blob型別的資料以16進位制的形式儲存，就不會出現有些字元不可見的情況了
--tab =path    # 使用了此引數會在指定的路徑下對資料庫的每個表生成兩個檔案，一個檔案用於儲存表結構，另一個用於儲存表中的資料
-w, --where = `過濾條件`    # 匯出指定條件的資料（只支援單表資料條件匯出）


 備份賬號所需要的許可權 ： SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, PROCESS
如果使用--tab引數則還需要：FILE許可權

3. 演示

首先建立備份使用者

create user `backup`@`localhost` identified by `123456`;

賦予使用者備份許可權

grant select,reload,lock tables,replication client,show view,event,process on *.*  to `backup`@`localhost`;

使用mysqldump進行全量備份

備份某個資料庫

[root@localhost db_backup]# mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events mc_orderdb > mc_orderdb.sql

如果遇到以下問題

mysqldump: Error: Binlogging on server not active

MySQL中二進位制日誌功能預設是關閉，去/etc/my.cnf 檔案中加入下面配置，開啟log_bin(資料庫的操作日誌)功能，然後重啟mysql即可解決問題

log_bin=mysql-bin

之後使用 “systemctl start mysql” 重啟伺服器，報錯

Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

解決方法：

在設定 log-bin 的時候同時需要設定 server-id 變數，即在配置檔案中新增：

[mysqld]  
log-bin=mysql  
server-id=1  

然後再次重啟即可

補充知識

裝mysql,執行一段時間後，在mysql目錄下出現一堆類似mysql-bin.000***，從mysql-bin.000001開始一直排列下來，而且佔用了大量硬碟空間，高達幾十個G. 對於這些超大空間佔用量的檔案我們應該怎麼辦呢?

那麼mysql資料庫資料夾中的mysql-bin.00001是什麼檔案？
mysql-bin.000001、mysql-bin.000002等檔案是資料庫的操作日誌，例如UPDATE一個表，或者DELETE一些資料，即使該語句沒有匹配的資料，這個命令也會儲存到日誌檔案中，還包括每個語句執行的時間，也會記錄進去的。

這些形如mysql-bin.00001的檔案主要是用來做什麼的呢?
1：資料恢復
如果你的資料庫出問題了，而你之前有過備份，那麼可以看日誌檔案，找出是哪個命令導致你的資料庫出問題了，想辦法挽回損失。

2：主從伺服器之間同步資料 
主伺服器上所有的操作都在記錄日誌中，從伺服器可以根據該日誌來進行，以確保兩個同步。

如果不想要這些檔案應該怎麼做呢?
1：只有一個mysql伺服器，那麼可以簡單的註釋掉這個選項就行了。
vi /etc/my.cnf把裡面的 log-bin 這一行註釋掉，重啟mysql服務即可。

2：如果你的環境是主從伺服器，那麼就需要做以下操作了。 
A：在每個從屬伺服器上，使用SHOW SLAVE STATUS來檢查它正在讀取哪個日誌。
B：使用SHOW MASTER LOGS獲得主伺服器上的一系列日誌。
C：在所有的從屬伺服器中判定最早的日誌，這個是目標日誌，如果所有的從屬伺服器是更新的，就是清單上的最後一個日誌。
D：清理所有的日誌，但是不包括目標日誌，因為從伺服器還要跟它同步。

簡單地說,這些MySQL目錄下的形如mysql-bin.000***的檔案時MySQL的事務日誌。

刪除複製伺服器已經拿走的binlog是安全的，一般來說網路狀況好的時候，保留最新的那一個足以。

再次執行之前的備份命令，即可成功被封mc_orderdb資料庫下的所有表，我們可以查詢一下備份的SQL檔案中是否包含所有表

[root@localhost db_backup]# grep "CREATE TABLE" mc_orderdb.sql
CREATE TABLE `order_cart` (
CREATE TABLE `order_customer_addr` (
CREATE TABLE `order_detail` (
CREATE TABLE `order_master` (
CREATE TABLE `region_info` (
CREATE TABLE `shipping_info` (
CREATE TABLE `warehouse_info` (
CREATE TABLE `warehouse_proudct` (
[root@localhost db_backup]# 

通過上面結果可以看出我們的幾個表都在其中

備份某個資料庫下的某個表

[root@localhost db_backup]#  mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events mc_orderdb order_master > order_master.sql
Enter password: 
[root@localhost db_backup]# ls
mc_orderdb.sql  order_master.sql

備份MySQL例項下的所有資料庫

[root@localhost db_backup]#  mysqldump -ubackup -p --master-data=1 --single-transaction --routines --triggers --events  --all-databases > mc.sql
Enter password: 
[root@localhost db_backup]# ls
mc_orderdb.sql  mc.sql  order_master.sql

由於master-data的值設定為1，change master命令並沒有被註釋掉，如果我們使用這個命令進行恢復，change master命令就會被執行，在一些情況下可能會造成一些錯誤，所以建議使用時最好還是設定為2

可以通過下面的命令檢視，備份檔案中包含哪些資料庫

[root@localhost db_backup]# grep "Current Database" mc.sql
-- Current Database: `mc_orderdb`
-- Current Database: `mc_productdb`
-- Current Database: `mc_userdb`
-- Current Database: `mysql`
[root@localhost db_backup]# 

使用-tab引數指定備份檔案的位置

首先在/tmp 目錄下建立一個mc_orderdb目錄用來存放指定的備份檔案，之所以使用在此目錄下建立目錄，是因為使用–tab引數時，使用者必須對目標目錄有可寫許可權，而tmp目錄對任何使用者都有可寫許可權

[root@localhost db_backup]#  mkdir -p /tmp/mc_orderdb

現在我們可以使用–tab引數指定備份路徑

[root@localhost db_backup]# mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events --tab="/tmp/mc_orderdb" mc_orderdb 
Enter password: 

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE=`mysql-bin.000001`, MASTER_LOG_POS=154;
mysqldump: Got error: 1045: Access denied for user `backup`@`localhost` (using password: YES) when executing `SELECT INTO OUTFILE`

可以發現，報錯了，其實我們在這之前還缺少一步，由於使用者需要有寫檔案的許可權，所以我們還需要對備份使用者賦予file許可權

mysql> grant file on *.*  to `backup`@`localhost`;
Query OK, 0 rows affected (0.00 sec)

我們再次執行上面的備份命令

[root@localhost db_backup]# mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events --tab="/tmp/mc_orderdb" mc_orderdb 
Enter password: 

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE=`mysql-bin.000001`, MASTER_LOG_POS=347;
mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing `SELECT INTO OUTFILE`

可以很清楚地從提示看到是因為mysql服務啟用了–secure-file-priv，所以才無法執行。
那麼–secure-file-priv又是什麼呢，應該如何解決才能是它可以備份呢？
–secure-file-priv=name ：
Limit LOAD DATA, SELECT … OUTFILE, and LOAD_FILE() to files within specified directory

可以看到secure-file-priv引數是用來限制LOAD DATA, SELECT … OUTFILE, and LOAD_FILE()傳到哪個指定目錄的。

當secure_file_priv的值為null ，表示限制mysqld 不允許匯入|匯出

當secure_file_priv的值為/tmp/ ，表示限制mysqld 的匯入|匯出只能發生在/tmp/目錄下

當secure_file_priv的值沒有具體值時，表示不對mysqld 的匯入|匯出做限制

檢視資料庫當前該引數的值

mysql> show global variables like `%secure%`; 
+--------------------------+-----------------------+
| Variable_name            | Value                 |
+--------------------------+-----------------------+
| require_secure_transport | OFF                   |
| secure_auth              | ON                    |
| secure_file_priv         | /var/lib/mysql-files/ |
+--------------------------+-----------------------+
3 rows in set (0.00 sec)

清楚地看到secure_file_priv 的值是NULL，說明此時限制匯入匯出的
所以應該改變該引數
可是檢視了mysql.cnf中居然沒有對這個引數進行設定，就說明這個引數預設便是null
所以再mysql.cnf中的[mysqld]加入secure_file_priv =

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/home/mysql/data    # mysql資料存放的目錄
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

log_bin=mysql-bin
server-id=1  
secure_file_priv =

再重啟mysql服務

[root@localhost tmp]# systemctl restart mysqld

然後再查一下此時引數的值

mysql> show global variables like `%secure%`;
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         |       |
+--------------------------+-------+
3 rows in set (0.01 sec)

已經是我們要的結果 ，現在我們再次執行備份命令

[root@localhost tmp]# mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events --tab="/tmp/mc_orderdb" mc_orderdb 
Enter password: 

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE=`mysql-bin.000002`, MASTER_LOG_POS=154;
mysqldump: Got error: 1: Can`t create/write to file `/tmp/mc_orderdb/order_cart.txt` (Errcode: 13 - Permission denied) when executing `SELECT INTO OUTFILE`

結果還是許可權被拒絕，無法寫入，我們可以查詢一下目錄mc_orderdb的許可權，

[root@localhost tmp]# ls -lh mc_orderdb/
total 4.0K
-rw-r--r-- 1 root root 1.9K Jan 10 10:51 order_cart.sql

可以發現，是root使用者建立的目錄，我們需要修改其所屬使用者為mysql使用者，然後再次執行備份命令

[root@localhost tmp]#  chown mysql:mysql  mc_orderdb
[root@localhost tmp]# mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events --tab="/tmp/mc_orderdb" mc_orderdb 
Enter password: 

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE=`mysql-bin.000002`, MASTER_LOG_POS=154;

--
-- Dumping events for database `mc_orderdb`
--

--
-- Dumping routines for database `mc_orderdb`
--
[root@localhost tmp]# 

可以發現，修改成功後即可備份成功

進入該目錄下會發現mc_orderdb資料庫下的每個表都有兩種檔案，一種.sql結尾記錄是表結構，一種是.txt結尾的表資料

[root@localhost tmp]# cd mc_orderdb/
[root@localhost mc_orderdb]# ls
order_cart.sql  order_customer_addr.sql  order_detail.sql  order_master.sql  region_info.sql  shipping_info.sql  warehouse_info.sql  warehouse_proudct.sql
order_cart.txt  order_customer_addr.txt  order_detail.txt  order_master.txt  region_info.txt  shipping_info.txt  warehouse_info.txt  warehouse_proudct.txt

mysqldump如何使用全備where引數

使用場景

假設我們要對訂單id為1000到1050的主表進行修改，修改之前，我們需要先對資料進行備份，這裡我們就可以使用where引數來完成此需求

執行命令進行備份

[root@localhost db_backup]# mysqldump -ubackup -p --master-data=2 --single-transaction  --where "order_id>1000 and order_id<1050" mc_orderdb  order_master > order_master_1000_1050.sql
Enter password: 

檢視備份檔案可以發現，訂單id是從1001開始的

在日程工作中我們不可能一直手工備份，所以我們需要將備份進行指令碼話，然後使用計劃任務去執行指令碼

定義備份指令碼

指令碼檔案

#!/bin/bash
###############Basic parameters##########################
DAY=`date +%Y%m%d`    # 記錄發生備份的當前日期
Environment=$(/sbin/ifconfig | grep "inet" | head -1 |grep -v "127.0.0.1" | awk `{print $2;}` )    # 當前主機的IP
USER="backup"
PASSWD="123456"
HostPort="3306"
MYSQLBASE="/home/mysql/"
DATADIR="/home/db_backup/${DAY}"    # 備份存放的目錄（/home/db_backup目錄下的以日期命名的子目錄中）
MYSQL=`/usr/bin/which mysql`    # 定義mysql命令的目錄
MYSQLDUMP=`/usr/bin/which mysqldump`    # 定義mysqldump命令的目錄
mkdir -p ${DATADIR}    # 建立儲存目錄

# 定義備份函式，使用到上面定義的變數

Dump(){
 ${MYSQLDUMP} --master-data=2 --single-transaction  --routines --triggers --events -u${USER} -p${PASSWD} -P${HostPort} ${database}  > ${DATADIR}/${Environment}-${database}.sql
 cd ${DATADIR}
 gzip ${Environment}-${database}.sql    # 對檔案進行了壓縮
}

# 利用for迴圈對當前伺服器下的每一個資料庫（排除了一些系統檢視所在的資料庫）分別來呼叫上面的Dump函式來進行備份

for db in `echo "SELECT schema_name FROM information_schema.schemata where schema_name not in (`information_schema`,`sys`,`performance_schema`)" | ${MYSQL} -u${USER} -p${PASSWD} --skip-column-names`
do
   database=${db}
   Dump
done

執行指令碼檔案

[root@localhost home]# bash backup.sh 
mysql: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost home]# cd db_backup/
[root@localhost db_backup]# ls
20190110  mc_orderdb.sql  mc.sql  order_master_1000_1050.sql  order_master.sql
[root@localhost db_backup]# cd 20190110/
[root@localhost 20190110]# ls
172.17.0.1-mc_orderdb.sql.gz  172.17.0.1-mc_productdb.sql.gz  172.17.0.1-mc_userdb.sql.gz  172.17.0.1-mysql.sql.gz

可以看到結果已備份，可以使用crontab命令定時執行此指令碼

如何恢復mysqldump備份的資料庫

方法一：

mysql -u -p dbname < backup.sql

方法二：

mysql>  source /tmp/backup.sql

使用mysqldump備份時，恢復的速度完全取決於MySQL例項執行SQL的速度和伺服器的IO效能，並且恢復過程是單執行緒的，所以對於非常大的資料集來說，要恢復的話可能需要很長的時間

演示：

把剛才的全備資料恢復到bak資料庫中

建立bak_orderdb資料庫

[root@localhost db_backup]# mysql -uroot -p -e"create database bak_orderdb"
Enter password: 

將mc_orderdb備份的資料恢復到bak資料庫中

[root@localhost db_backup]#  mysql -uroot -p bak_orderdb < mc_orderdb.sql
Enter password: 
[root@localhost db_backup]# 

檢驗恢復結果的正確性

mysql> SELECT COUNT(*) FROM mc_orderdb.order_master;
+----------+
| COUNT(*) |
+----------+
|    10010 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM bak_orderdb.order_master;
+----------+
| COUNT(*) |
+----------+
|    10010 |
+----------+
1 row in set (0.00 sec)

模擬誤操作並恢復資料

假設我們現在不小心刪除了mc_orderdb下的order_master中的10條資料，我們現在需要通過剛剛恢復的備份資料庫把這10條資料恢復回來

mysql> DELETE FROM mc_orderdb.order_master LIMIT 10;
Query OK, 10 rows affected (0.01 sec)

mysql>  SELECT COUNT(*) FROM mc_orderdb.order_master;
+----------+
| COUNT(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.00 sec)

首先查出被誤刪的資料
SELECT a.* FROM bak_orderdb.order_master a LEFT JOIN mc_orderdb.order_master b ON a.order_id=b.order_id WHERE b.order_id IS NULL;
然後執行insert語句將查出的資料插入回去

完整語句為

INSERT INTO mc_orderdb.order_master(
order_id,order_sn,customer_id,shipping_user,province,city,district,address,
payment_method,order_money,district_money,shipping_money,payment_money,
shipping_comp_name,shipping_sn,create_time,shipping_time,pay_time,receive_time,
order_status,order_point,invoice_title,modified_time)
SELECT a.* FROM bak_orderdb.order_master a 
LEFT JOIN mc_orderdb.order_master b ON a.order_id=b.order_id
WHERE b.order_id IS NULL;

執行結果：

mysql> INSERT INTO mc_orderdb.order_master(
    -> order_id,order_sn,customer_id,shipping_user,province,city,district,address,
    -> payment_method,order_money,district_money,shipping_money,payment_money,
    -> shipping_comp_name,shipping_sn,create_time,shipping_time,pay_time,receive_time,
    -> order_status,order_point,invoice_title,modified_time)
    -> SELECT a.* FROM bak_orderdb.order_master a 
    -> LEFT JOIN mc_orderdb.order_master b ON a.order_id=b.order_id
    -> WHERE b.order_id IS NULL;
Query OK, 10 rows affected (0.03 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(*) FROM mc_orderdb.order_master;
+----------+
| COUNT(*) |
+----------+
|    10010 |
+----------+
1 row in set (0.00 sec)

對於一些靜態型資料我們可以這樣進行，但是對於一些時刻有資料在往表裡寫的資料還原，就不能使用這種方式了

對於生產環境中，時刻有資料寫入的表如何進行資料恢復呢？

mysqldump單表備份恢復（使用了–tab引數備份的結果集）

需要進入mysql客戶端中

先恢復表結構

mysql>  source /tmp/mc_orderdb/region_info.sql;

再匯入資料

mysql>     load data infile `/tmp/mc_orderdb/region_info.txt` info table region_info;

如何進行指定時間點的恢復

進行某一時間點的資料恢復，恢復到誤操作的時間

先決條件：

具有指定時間點前的mysqldump的全備
具有全備到指定時間點的mysql二進位制日誌

演示

首先我們需要有個資料庫的全備，此處我們對mc_orderdb 資料庫進行全備

[root@localhost db_backup]# mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events mc_orderdb > mc_orderdb.sql

然後我們模擬一下生產環境中對資料庫的操作，這樣才能看到時間點恢復的效果

我們到mc_orderdb資料庫中新建一個統計表 t, uid列是使用者id,cnt 是使用者的總消費金額，將統計結果插入表t中後，模擬誤操作，刪除表t中的100行資料

mysql> use mc_orderdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table t(id int auto_increment not null,uid int,cnt decimal(8,2),primary key (id));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t(uid,cnt) select customer_id,sum(order_money) from order_master group by customer_id;
Query OK, 6314 rows affected (0.05 sec)
Records: 6314  Duplicates: 0  Warnings: 0

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|     6314 |
+----------+
1 row in set (0.00 sec)

mysql> delete from t limit 100;
Query OK, 100 rows affected (0.01 sec)

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|     6214 |
+----------+
1 row in set (0.01 sec)

先恢復一個最近的全備，進行全量資料恢復

[root@localhost db_backup]# mysql -uroot -p mc_orderdb < mc_orderdb.sql

然後檢視mc_orderdb.sql檔案中change master命令中

在進行這個全備時Mysql二進位制日誌的檔名（MASTER_LOG_FILE），以及時間點（MASTER_LOG_POS）

CHANGE MASTER TO MASTER_LOG_FILE=`mysql-bin.000001`, MASTER_LOG_POS=154;

此處檔名為mysql-bin.000001，日誌時間點為154;

下面我們要恢復這個時間點（154）之後到第一次刪除資料之前的資料

要找到這個時間點（154）之後到第一次刪除資料之前的資料

檢視二進位制日誌，進行分析

[root@localhost db_backup]# cd /home/mysql
[root@localhost mysql]# mysqlbinlog --base64-output=decode-rows -vv --start-position=154 --database=mc_orderdb mysql-bin.000001| grep -B3 DELETE | more
#190110 13:44:54 server id 1  end_log_pos 83285 CRC32 0xf679d195    Table_map: `mc_orderdb`.`t` mapped to number 119
# at 83285
#190110 13:44:54 server id 1  end_log_pos 84620 CRC32 0xa3408e6c    Delete_rows: table id 119 flags: STMT_END_F
### DELETE FROM `mc_orderdb`.`t`
--
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2=1 /* INT meta=0 nullable=1 is_null=0 */
###   @3=1042.34 /* DECIMAL(8,2) meta=2050 nullable=1 is_null=0 */
### DELETE FROM `mc_orderdb`.`t`
--
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2=3 /* INT meta=0 nullable=1 is_null=0 */
###   @3=803.37 /* DECIMAL(8,2) meta=2050 nullable=1 is_null=0 */

...省略

從中可以看到剛剛的二進位制日誌中的第一個DELETE，在它之前的日誌結束點為84620

所以我們需要恢復的是154 到84620 之間，且資料庫為mc_orderdb ，日誌所在檔名 為mysql-bin.000001的資料

我們通過mysqlbinlog 將這些資料匯出來

[root@localhost mysql]# mysqlbinlog --start-position=154 --stop-position=84620 --database=mc_orderdb mysql-bin.000001 > mc_order_diff.sql

將資料匯入恢復

[root@localhost mysql]# mysql -uroot -p mc_orderdb < mc_order_diff.sql

實時二進位制日誌備份

Mysql5.6版本之後，可以實時備份Binlog(二進位制日誌)

要使用這個功能，我們需要進行以下配置
首先新建一個使用者，這個使用者要有replication slave 許可權

mysql>  grant replication slave on *.* to `repl`@`localhost` identified by `123456`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

建立一個儲存備份的二進位制日誌檔案的目錄

mkdir -p binlog_backup

最後在這個目錄下，執行以下命令，就可以實時的進行二進位制日誌的備份了

[root@localhost binlog_bak]# mysqlbinlog --raw --read-from-remote-server --stop-never --host localhost --port 3306 -u repl -p123456 mysql-bin.000001
mysqlbinlog: [Warning] Using a password on the command line interface can be insecure.

raw：代表這個命令是輸出的是raw格式的二進位制日誌
read-from-remote-server： 從mysql伺服器上讀取這個日誌
stop-never：備份的這個程式會持續在後臺執行
最後的是指定要備份的二進位制檔案的名稱

此命令執行後這個終端會一直執行這個命令
現在開啟另外一個終端，進入備份的目錄進行檢視

[root@localhost mysql]# cd /home/binlog_bak/
[root@localhost binlog_bak]# ls
mysql-bin.000001

可以看到日誌已經備份成功
進入mysql命令列，重新整理日誌

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |   3560467 |
| mysql-bin.000002 |       154 |
+------------------+-----------+
2 rows in set (0.00 sec)

現在有兩個日誌，我們再回到備份目錄進行檢視

[root@localhost binlog_bak]# ls
mysql-bin.000001  mysql-bin.000002

可以看到最新的日誌也已實時備份

三. xtrabackup

1. xtrabackup介紹

xtrabackup 物理備份工具，用於線上備份innodb儲存引擎的表

在所有表全是innodb儲存引擎表的情況下：
xtrabackup 可以保證在備份過程中，不影響表的讀寫操作
在最初的時候，xtrabackup 本身只支援對innodb儲存引擎表的備份，且只會備份資料檔案，不會備份表的結構
innobackupex 是對xtrabackup 的外掛，提供了備份表結構及其他配置資訊的功能，並支援MyISAM表的備份，但也會鎖表

因為在當前的mysql版本下，還有一些系統表使用的是MyISAM儲存引擎，所以一般情況下使用的是innobackupex 指令碼進行備份的

2. 安裝xtrabackup

3. 利用innobackupex 進行全備

innobackupex --user=backup --password=123456 --parallel=2 /home/db_backup/

[root@localhost home]# innobackupex --user=backup --password=123456 --parallel=2 /home/db_backup/
xtrabackup: recognized server arguments: --datadir=/home/mysql --log_bin=mysql-bin --server-id=1 --parallel=2 
xtrabackup: recognized client arguments: --datadir=home/mysql --log_bin=mysql-bin --server-id=1 --parallel=2 
190110 15:15:30 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

... 省略

190110 15:15:37 [00] Writing /home/db_backup/2019-01-10_15-15-30/xtrabackup_info
190110 15:15:37 [00]        ...done
xtrabackup: Transaction log of lsn (79088947) to (79088956) was copied.
190110 15:15:37 completed OK!

當看到completed OK!代表備份已完成

和mysqldump單執行緒備份不同，我們可以通過parallel引數指定備份的執行緒數
/home/db_backup/ 是我們指定的備份檔案的儲存目錄

xtrabackup 會已當前時間在目標目錄中生成一個子目錄用來存放當前的備份檔案

我們進入目錄中檢視一下

[root@localhost db_backup]# ls
20190110  2019-01-10_15-15-30  mc_orderdb.sql  mc.sql  order_master_1000_1050.sql  order_master.sql
[root@localhost db_backup]# cd 2019-01-10_15-15-30/
[root@localhost 2019-01-10_15-15-30]# ls
backup-my.cnf  ib_buffer_pool  mc_orderdb    mc_userdb  performance_schema  xtrabackup_binlog_info  xtrabackup_info
bak_orderdb    ibdata1         mc_productdb  mysql      sys                 xtrabackup_checkpoints  xtrabackup_logfile

與原資料檔案目錄相比，少了ib_logfile0等日誌檔案

xtrabackup_binlog_info 比較重要，其中記錄了備份的日誌名和日誌點，相當於mysqldump中設定master-data引數的作用

如果我們不想以時間戳的形式自動生成子目錄，我們可以通過指定–no-timestamp引數來實現

innobackupex --user=backup --password=123456 --parallel=2  /home/db_backup/2019-01-10 --no-timestamp

[root@localhost db_backup]#  innobackupex --user=backup --password=123456 --parallel=2  /home/db_backup/2019-01-10 --no-timestamp
[root@localhost db_backup]# ls
20190110  2019-01-10  2019-01-10_15-15-30  mc_orderdb.sql  mc.sql  order_master_1000_1050.sql  order_master.sql
[root@localhost db_backup]# cd 2019-01-10
[root@localhost 2019-01-10]# ls
backup-my.cnf  ib_buffer_pool  mc_orderdb    mc_userdb  performance_schema  xtrabackup_binlog_info  xtrabackup_info
bak_orderdb    ibdata1         mc_productdb  mysql      sys                 xtrabackup_checkpoints  xtrabackup_logfile
[root@localhost 2019-01-10]# 

4. 利用innobackupex 進行全備的恢復

innobackupex  --apply-log /path/to/BACKUP-DIR
mv /path/to/BACKUP-DIR /home/mysql/data

利用xtrabackup備份產生的備份集並不能直接用於資料庫的恢復
進行資料庫恢復之前，我們必須把備份中產生的備份集 進行應用，此處我們使用的是備份檔案的目錄偽2019-01-10

[root@localhost db_backup]# innobackupex --apply-log /home/db_backup/2019-01-10

[root@localhost db_backup]# cd 2019-01-10
[root@localhost 2019-01-10]# ls
backup-my.cnf   ib_logfile0  mc_productdb        sys                           xtrabackup_info
bak_orderdb     ib_logfile1  mc_userdb           xtrabackup_binlog_info        xtrabackup_logfile
ib_buffer_pool  ibtmp1       mysql               xtrabackup_binlog_pos_innodb  xtrabackup_master_key_id
ibdata1         mc_orderdb   performance_schema  xtrabackup_checkpoints

可以發現，使用了上面的命令後，備份集中多出了之前缺少的ib_logfile0等檔案

使用xtrabackup進行資料庫恢復時，我們必須對資料庫例項進行重啟
先停用mysql服務

然後將原資料庫檔案所在的資料夾重新命名為data_bak,然後將此時的 2019-01-10資料夾移動到data_bak所在的資料夾，並重新命名為data，即覆蓋了原來的檔案

然後 chown -R mysql:mysql data
對資料夾修改所屬使用者

最後重啟mysql服務即可

5. 利用innobackupex 進行增量備份

innobackupex --user=backup --password=123456 /home/db_backup    # 先進行全備
  
innobackupex --user=backup --password=123456 --incremental  /home/db_backup/  --incremental-basedir=/home/db_backup/2019-01-10/

incremental ：表示我們要進行的是一個全備， 指定全備的目錄
incremental-basedir ：指定增量備份所依賴的資料基礎的備份目錄，這個增量備份所依賴的上一個全備

演示：

先進行全備

innobackupex --user=backup --password=123456 /home/db_backup  

在 /home/db_backup 目錄下生產了一個新的全備子目錄 2019-01-10_16-19-37

再依賴上個全量備份進行增量備份

innobackupex --user=backup --password=123456 --incremental  /home/db_backup/  --incremental-basedir=/home/db_backup/2019-01-10_16-19-37/

此時在 /home/db_backup 目錄下生產了一個增量備分的子目錄2019-01-10_16-22-09
增量備份會把自上一個全備後的資料變更記錄下來

然後再進行一次增量備份，此時的命令和前面基本相同，只是所基於的增量備份的資料目錄要變成上次增量備份生成的目錄

innobackupex --user=backup --password=123456 --incremental  /home/db_backup/  --incremental-basedir=/home/db_backup/2019-01-10_16-22-09/

此時，第二次增量備份的生成的目錄名為2019-01-10_16-24-09

6. 利用innobackupex 進行增量恢復

innobackupex  --apply-log  --redo-only 全備目錄

我們要迴圈的在多次增量備份中應用上面步驟

 innobackupex  --apply-log  --redo-only 全備目錄  --incremental-dir=第一次增量目錄

所有增量備份的都應有了上面的命令後，就可以像全備一樣，在全備目錄上再進行崩潰恢復的過程

    innobackupex  --apply-log /path/to/BACKUP-DIR
    mv /path/to/BACKUP-DIR /home/mysql/data

最後和全備一樣，要用全備目錄替換mysql資料庫目錄

演示

下面演示只恢復到第一次備份

[root@localhost db_backup]#   innobackupex  --apply-log  --redo-only    /home/db_backup/2019-01-10_16-19-37

[root@localhost db_backup]#   innobackupex  --apply-log  --redo-only    /home/db_backup/2019-01-10_16-19-37  --incremental-basedir=/home/db_backup/2019-01-10_16-22-09

[root@localhost db_backup]#   innobackupex  --apply-log   /home/db_backup/2019-01-10_16-19-37 

[root@localhost db_backup]#  mv 2019-01-10_16-19-37  /home/mysql

[root@localhost db_backup]#  cd  /home/mysql

[root@localhost mysql]#  systemctl stop  mysqld

[root@localhost mysql]#  mv 2019-01-10_16-19-37 data

[root@localhost mysql]# chown -R mysql:mysql data

[root@localhost mysql]#  systemctl start mysqld

四. 要制定備份計劃

每天凌晨對資料庫進行一次全備
實時對二進位制日誌進行遠端備份