關於刪庫跑路的事故現在已經屢見不鮮了，數據備份的必要性是企業數據管理極其重要的一項工作。關於數據備份、恢復也有很多場景及方法，本系列也會將主要的幾種工具通過案例進行演示。 本系列將從邏輯備份及恢復開始講起，邏輯備份的工具主要有mysqldump/mydumper等其中mydumper可以指定多線程工 ...
關於刪庫跑路的事故現在已經屢見不鮮了，數據備份的必要性是企業數據管理極其重要的一項工作。關於數據備份、恢復也有很多場景及方法，本系列也會將主要的幾種工具通過案例進行演示。
本系列將從邏輯備份及恢復開始講起，邏輯備份的工具主要有mysqldump/mydumper等其中mydumper可以指定多線程工作，本文介紹的是mysqldump。
1. mysqldump 備份
mysqldump是MySQL資料庫自帶的邏輯備份工具，屬於熱備工具。它的備份結果是根據設置的參數將資料庫中的信息通過生成創建庫、表等對象以及對應表的insert語句組成。
mysqldump 參數選項特別多，可以通過mysqldump --help 查看對應的參數及說明（）
[root@testdb ~]# mysqldump --help
mysqldump Ver 10.13 Distrib 5.7.25-28, for Linux (x86_64)
Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Dumping structure and contents of MySQL databases and tables.
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
mysqldump常用的選項如下
1.1 備份指定表
mysqldump可以備份指定的單個表或指定庫的多個表，例如備份testdb庫的test1表的表結構和數據
/* 備份testdb庫的test1表 */ /usr/local/mysql5.7/bin/mysqldump -uroot -p --socket=/tmp/mysql.sock --master-data=2 --default-character-set=utf8 --single-transaction testdb test1 > test1.sql
備份後的文件主體部分如下：
主要參數說明：
--master-data=2 記錄當前binlog信息，有1和2兩個值,如果值等於1，就會添加一個CHANGE MASTER語句;如果值等於2，也會生成一個CHANGE MASTER，但是語句前添加註釋，
如本例中所示（建議設置為2），此信息可以用作後期配置搭建主從同步用
--default-character-set=utf8 設置字元集，建議指明字元集
--single-transaction 配合RR隔離級別使用，用於保證innodb備份數據一致性，且不會鎖表，此參數很重要，生產環境中一定要設置該參數
testdb 庫名
test1 需要備份的表名，如果需要備份多張表 可以將表名直接羅列在後面 如 test1 test2
1.2. 備份單個資料庫
mysqldump可以備份指定的資料庫，可以是單個庫也可以是多個庫，先備份單個庫，如下
/* 備份整個testdb庫 */ /usr/local/mysql5.7/bin/mysqldump -uroot -p --socket=/tmp/mysql.sock --master-data=2 --default-character-set=utf8 --single-transaction testdb > testdb.sql
結果如下，會將testdb庫下的所有表的結構和數據都備份出來
1.3 備份多個庫
備份多個資料庫可以用如下命令
/* 備份monitor庫及testdb庫 */ /usr/local/mysql5.7/bin/mysqldump -uroot -p --socket=/tmp/mysql.sock --master-data=2 --default-character-set=utf8 --single-transaction --databases monitor testdb > mul_db.sql
結果中主要信息如下，包含了創建庫及切換庫的內容
參數說明（其他參數之前也說明，此處不再贅述）：
--databases 後面填寫需要備份的資料庫名即可指定備份對應的庫
1.4 備份所有的資料庫
如果想備份所有的資料庫，可以使用如下命令：
/* 備份所有資料庫 */ /usr/local/mysql5.7/bin/mysqldump -uroot -p --socket=/tmp/mysql.sock --master-data=2 --default-character-set=utf8 --single-transaction --all-databases > all_db.sql
註： 備份中沒有information_schema、performance_schema、sys庫的信息（MySQL5.7及以上版本）
1.5 其他情況
實際使用中可能還會遇到只備份表結構、只備份數據，需要備份存儲過程及事件等需求，相應參數如下：
--no-data 只備份表結構，不包含數據，可以簡寫為 -d --no-create-info 只備份數據，不備份建表信息，也可以簡寫為-t --routines 備份存儲過程及函數,可以簡寫為 -R --events 備份事件，可以簡寫為 -E --triggers 備份觸發器 --flush-logs 備份完成後切換日誌 --flush-privileges 備份完成後刷新許可權 --set-gtid-purged 開啟了GTID的庫需要設置該參數，值可以是ON, OFF 或 AUTO --where 指定條件，例如每張表導出1000行的記錄或者 導出每張表id<=10的記錄等，可以參考歷史文章查看示例
--skip-add-drop-table 不生成刪除表的語句
1.6 示例腳本
備份全部資料庫，包含觸發器、事件、存儲過程，同時刷新日誌及許可權的實例
/usr/local/mysql5.7/bin/mysqldump -uroot -p --socket=/tmp/mysql.sock --master-data=2 --default-character-set=utf8 --routines --triggers --events --flush-logs --flush-privileges --single-transaction --all-databases >backup.sql
註意：備份中存在drop表的情況，如果確定需要重建表則保持預設，否則保險起見，添加--skip-add-drop-table 參數
1.7 說點特別的
mysqldump也可以生成平面文件，同時指定分隔符等，此情況比較簡單，可以自行測試
1.8 使用場景
mysqldump屬於邏輯備份，使用的主要場景如下：
- 備份部分庫或備份部分表
- 數據規模較小的庫
- 有指定條件的備份等
2 模擬誤刪數據操作
模擬誤刪除testdb庫的test1表中的一條記錄
mysql> delete from test1 where name='孫權'; Query OK, 1 row affected (0.00 sec)
3. 恢複數據
通過mysqldump備份的數據還原比較簡單，將備份的文件導入資料庫中即可。
3.1 恢復test1表
可以使用如下三種方式恢復：
a) 登錄進資料庫里用source 命令恢復
/* 將備份數據恢復至 rec 庫中 */ mysql> use rec; Database changed /* 恢複數據 */ mysql> source test1.sql;
b) 使用 mysql -e 的方式恢復
/* 此方式等同於第一種 */ [root@testdb ~]# /usr/local/mysql5.7/bin/mysql --socket=/tmp/mysql.sock --port=3306 -uroot -p -e "use rec ; source test1.sql;" Enter password:
c) 直接 MySQL 導入備份文件的方式
[root@testdb ~]# /usr/local/mysql5.7/bin/mysql --socket=/tmp/mysql.sock --port=3306 -uroot -p rec < test1.sql Enter password:
註：
a) 還原命令使用起來比較方便，但是實際生產環境中還原數據時不建議直接還原至目標表裡（尤其處理誤刪除恢複數據時），而是建議先還原至其他實例或其他庫里，確認無誤後再將需要還原的記錄導入至目標表裡;
b) 要警惕備份文件中是否有刪除庫或刪表的指令，否則如果選擇在同一實例中還原即使選擇了臨時恢復的庫，而備份文件里有use db；及drop table的語句，則會將目標表全部刪掉。
3.2 恢復誤刪除的記錄至目標表
mysql> insert into testdb.test1 select * from rec.test1 where name='孫權'; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0
此時記錄恢復至資料庫中了。
4. mysqldump的原理
通過查看備份文件可以看到大致的流程，也可以通過開啟general log的方式看具體都執行哪些SQL。
4.1 查看general log
先查看配置
然後開啟genera-log
4.2 開始備份
我們就用之前的推薦備份腳本進行備份
/usr/local/mysql5.7/bin/mysqldump -uroot -p --socket=/tmp/mysql.sock --master-data=2 --default-character-set=utf8 --routines --triggers --events --flush-logs --flush-privileges --single-transaction --all-databases > all_db.sql
4.3 關閉general-log
因開啟general-log對資料庫性能影響大（表現特別明顯），因此測試完畢後關閉general log。勿忘，切記。
4.4 查看general-log內容
後面開始備份具體庫的內容
備份其他庫的內容與此相同，省略。
以上內容，如需轉載，請註明出處，謝謝）
使用mysqldump工具備份及恢複數據至此就基本介紹完畢了，如果補充或想獲取原圖的請聯繫我（關註公眾號： 資料庫乾貨鋪）。
