對於任何資料庫來說，備份都是非常重要的資料庫複製不能取代備份的作用比如我們由於誤操作，在主資料庫上刪除了一些數據，由於主從複製的時間很短，在發現時，從資料庫上的數據可能也已經被刪除了，我們不能使用從資料庫上的數據來恢復主資料庫上的數據，只能通過備份進行誤刪除數據的恢復一. 備份的分類 1.按 ...

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

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

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

一. 備份的分類

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

四. 要制定備份計劃

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

MySQL資料庫備份詳解