MySQL备份恢复（十二）
文章目錄
- 1. MySQL數據損壞類型
- 1.1 物理損壞
- 1.2 邏輯損壞
- 2. DBA運維人員備份/恢復職責
- 2.1 設計備份/容災策略
- 2.1.1 備份策略
- 2.1.2 容災策略
- 2.2 定期的備份/容災檢查
- 2.3 定期的故障恢復演練
- 2.4 數據損壞時的快速準確恢復
- 2.5 數據遷移工作
- 3. MySQL常用備份工具
- 3.1 邏輯備份方式
- 3.2 物理備份方式
- 3.3 架構備份方式
- 4. mysqldump應用
- 4.1 mysqldump介紹
- 4.2 mysqldump備份方式
- 4.2.1 InnoDB表
- 4.2.2 非InnoDB表
- 4.3 mysqldump核心參數
- 4.3.1 mysqldump連接參數
- 4.3.2 mysqldump備份參數
- 4.3.3 mysqldump備份高級參數
- 4.3.3.1 --master-data參數
- 4.3.3.2 --single-transaction參數
- 4.3.3.2 -R/-E/--triggers參數
- 4.3.3.2 --max_allowed_packet參數
- 5. mysqldump+binlog故障恢復案例
- 6. mysqldump小總結
1. MySQL數據損壞類型
1.1 物理損壞
磁盤損壞：硬件，磁道壞，dd，格式化
文件損壞：數據文件損壞，redo損壞
1.2 邏輯損壞
drop、delete、truncate、update
2. DBA運維人員備份/恢復職責
2.1 設計備份/容災策略
2.1.1 備份策略
備份工具原則
備份周期設計
備份監控方法
2.1.2 容災策略
備份：用什么備份
架構： 高可用，延時從庫，災備庫
容災的好處： 在發生災難的時候，怎么能快速的恢復業務。可以用備份或高可用的架構
2.2 定期的備份/容災檢查
備份軟件 ------> 帶庫（磁帶）
每周 北京 --> 天津 貨運
一定要定時的檢查數據庫的備份情況，以防止自己寫的腳本有bug，保證正常備份。
2.3 定期的故障恢復演練
至少每年兩次的恢復演練。在測試環境進行恢復演練。
2.4 數據損壞時的快速準確恢復
每個產品數據庫出現故障，要快速的恢復數據，把相關命令寫清楚，每一種損壞的應對策略。
2.5 數據遷移工作
一般用備份的手段或者主從的手段來進行遷移
3. MySQL常用備份工具
3.1 邏輯備份方式
mysqldump（MDP） *****
replication（主從方式）
mydumper（自行擴展）
load data in file（自行擴展）
邏輯備份： 備份的是sql語句
3.2 物理備份方式
MySQL Enterprise Backup（企業版）
Percona Xtrabackup（PBK，XBK）*****
物理備份： 備份的是表空間文件(數據文件)
小提示：社區版沒有自帶的物理備份方式
3.3 架構備份方式
架構備份方式，屬于邏輯的一種，比如MySQL主從。
高可用和主從主要解決物理損壞（磁盤或主機壞了），邏輯損壞時沒辦法解決的
4. mysqldump應用
4.1 mysqldump介紹
邏輯備份工具。備份的是SQL語句。
查看mysqldump的參數幫助：mysqldump --help
4.2 mysqldump備份方式
innodb表，備份的時候不需要鎖原來的表的，原來的表該干什么干什么，不受影響。
因為這只是一個優先讀的快照，不會影響其他人操作
非innodb表在mysql中基本上都是系統相關的表，系統表要備份，元數據要備份的話，要進行
全局鎖表
做全備份的時候，有innodb和非innodb，備份時會有短暫的鎖表的情況，盡量避免非innodb鎖表的情況
mysql8.0以后，大部分的系統表都被存儲到innnodb表中，備份起來性能會更好一些
4.2.1 InnoDB表
可以采用快照備份的方式
開啟一個獨立的事務，獲取當前最新的一致性快照。
將快照數據，放在臨時表中，轉換成SQL（Create database，Create table，insert），保存到sql文件中
4.2.2 非InnoDB表
需要鎖表備份，觸發FTWRL，全局鎖表。轉化成SQL（Create database，Create table，insert），保存到sql文件中
4.3 mysqldump核心參數
4.3.1 mysqldump連接參數
-u 用戶名 -p密碼 -h IP地址 -P 端口號 -S 套接字文件
mysql也好，mysqldump也好，在你沒有指定-S或者配置文件里面也沒有-S的時候，也會自動去/tmp里面去找，-S 不指定也行，默認會去找/tmp/mysql.sock，如果mysql.sock在其他地方，那就必須要加-S或者在配置文件加上參數指定mysql.sock的具體位置
避免mysql密碼暴露問題：（一般不采用，沒有必要）
# 在配置文件中添加這些參數，登錄mysql時不需要密碼（一般不采用，沒有必要）。 vim ~/.my.cnf [mysql] user=root password=123456 [mysqldump]# 讀取mysql配置文件順序： 后面的參數覆前面的參數 [root@db01 ~]# mysql --help --verbose | grep my.cnf /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf4.3.2 mysqldump備份參數
-A 全備
[root@db01 ~]# mkdir -p /data/backup [root@db01 ~]# chown -R mysql.mysql /data/* [root@db01 ~]# mysqldump -uroot -p123456 -S /tmp/mysql.sock -A > /data/backup/full.sql [root@db01 ~]# vim /data/backup/full.sql 例子1: [root@db01 ~]# mkdir -p /data/backup mysqldump -uroot -p -A >/data/backup/full.sql Enter password: mysqldump: [Warning] Using a password on the command line interface can be insecure. Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. # 補充: # 1.常規備份是要加 --set-gtid-purged=OFF,解決備份時的警告 # [root@db01 ~]# mysqldump -uroot -p123 -A --set-gtid-purged=OFF >/backup/full.sql # 2.構建主從時,做的備份,不需要加這個參數 # [root@db01 ~]# mysqldump -uroot -p123 -A --set-gtid-purged=ON >/backup/full.sql-B 備份單庫或多個庫（-B后面跟的都是庫名）
# 備份world庫和test庫 [root@db01 ~]# mysqldump -uroot -p123456 -B world test > /data/backup/db.sql備份單表或多表（固定語法： 數據庫 表 表 表，不能寫多個庫名）
# 備份world庫中的city表和coutry表 [root@db01 ~]# mysqldump -uroot -p123456 -S /tmp/mysql.sock world city country > /data/backup/tab.sql驗證一下：以下兩個命令的備份結果區別？
mysqldump -uroot -p123456 -B world > /data/backup/db1.sql # 上面的比下面多出兩條sql create database world; use world;# 這條sql備份world庫下面的所有表。應用時，world庫不存在，需要手工創建，并且use到world庫下再恢復。 mysqldump -uroot -p123456 world > /data/backup/db2.sql對比兩個sql文件：vimdiff db1.sql db2.sql
4.3.3 mysqldump備份高級參數
4.3.3.1 --master-data參數
場景：每周日23:00全備，周1-6進行binlog備份。所有備份時完整的。
周三時，有一個核心運維人員濟寧了刪庫操作。
恢復思路：恢復全備 + 所有需要binlog恢復
痛點：binlog的截取。起點查找比較困難，有兩種方法
起點： 一定要找到備份開啟時刻準確的position號以及binlog文件名，因為對innodb來說時快照備份，對于myisam是鎖表備份，所以全備的那一刻起就必須找到準確起點position號。有兩種方法找起點。
方法一：備份開始時，自動記錄日志文件信息（有position號） --master-data=2（–master-data=2在備份開始的那個時刻，記錄一下當前日志文件的一個狀態）常用方法
方法二：備份開始時，切割日志。-F（備份一開始binlog日志就是flush一下，生成一個新的文件，切割一個新的日志出來，-F 備份起始的時候，直接刷新一個新的日志，是有多少個庫，刷新多少個。這個參數使用起來不太方便）
終點： drop之前的位置點
–master-data=2 參數詳解：
[root@db01 binlog]# mysqldump --help--master-data[=#] This causes the binary log position and filename to beappended to the output. If equal to 1, will print it as aCHANGE MASTER command; if equal to 2, that command willbe prefixed with a comment symbol. This option will turn--lock-all-tables on, unless --single-transaction isspecified too (in which case a global read lock is onlytaken a short time at the beginning of the dump; don'tforget to read about --single-transaction below). In allcases, any action on logs will happen at the exact momentof the dump. Option automatically turns --lock-tablesoff.功能：
（1）備份時自動記錄binlog信息
（2）自動鎖表和解鎖
（3）配合single transaction 可以減少鎖表時間
開始實驗
確認起點方法一：（生產中使用–master-data=2參數）
對比 --master-data=2和 --master-data=1備份出來的區別：
vim full1.sql full2.sql
區別：–master-data=2多了主從的命令，但是前面有注釋。 --master-data=1沒有注釋，我們不需要主從的命令，真要使用主從，手動去指定恢復的起點。
確認起點方法二：（一般不用這種方法，了解即可）
[root@db01 binlog]# ls mysql-bin.000001 mysql-bin.000003 mysql-bin.000005 mysql-bin.000007 mysql-bin.000002 mysql-bin.000004 mysql-bin.000006 mysql-bin.index # 這種方法切出來的日志文件比較多而且混亂，不好確定位置點 [root@db01 binlog]# mysqldump -uroot -p123456 -A -F > /data/backup/full3.sql [root@db01 binlog]# ls mysql-bin.000001 mysql-bin.000008 mysql-bin.000015 mysql-bin.000022 mysql-bin.000029 mysql-bin.000002 mysql-bin.000009 mysql-bin.000016 mysql-bin.000023 mysql-bin.000030 mysql-bin.000003 mysql-bin.000010 mysql-bin.000017 mysql-bin.000024 mysql-bin.000031 mysql-bin.000004 mysql-bin.000011 mysql-bin.000018 mysql-bin.000025 mysql-bin.index mysql-bin.000005 mysql-bin.000012 mysql-bin.000019 mysql-bin.000026 mysql-bin.000006 mysql-bin.000013 mysql-bin.000020 mysql-bin.000027 mysql-bin.000007 mysql-bin.000014 mysql-bin.000021 mysql-bin.0000284.3.3.2 --single-transaction參數
對于InnoDB引擎表備份時，開啟一個獨立事務，獲取一致性快照，進行備份。
# --master-data=2 --single-transaction生產必加參數 [root@db01 backup]# mysqldump -uroot -p123456 -A --master-data=2 --single-transaction > /data/backup/full1.sql [root@db01 binlog]# mysqldump --help--single-transaction Creates a consistent snapshot by dumping all tables in asingle transaction. Works ONLY for tables stored instorage engines which support multiversioning (currentlyonly InnoDB does); the dump is NOT guaranteed to beconsistent for other storage engines. While a--single-transaction dump is in process, to ensure avalid dump file (correct table contents and binary logposition), no other connection should use the followingstatements: ALTER TABLE, DROP TABLE, RENAME TABLE,TRUNCATE TABLE, as consistent snapshot is not isolatedfrom them. Option automatically turns off --lock-tables.4.3.3.2 -R/-E/–triggers參數
這一組參數涉及mysql高級變成部分，涉及到的一些對象，可以理解為和表有一定的關系，所以備份時也要加上。
拿Linux系統來通俗的理解這些參數：
-R 類似于自己寫的程序腳本，如果之備份linux系統，不備份自己寫的腳本程序，那就缺少了功能【備份存儲過程和函數，相當于再數據庫里開發的一些程序】
-E 類似于計劃任務，【備份事件】
–triggers類似于特殊類的腳本，【備份觸發器】
4.3.3.2 --max_allowed_packet參數
傳輸包的最大傳輸大小：--max_allowed_packet=64M
# 調整客戶端--max_allowed_packet大小，如果64M還是不夠，那就調整128M [root@db01 backup]# mysqldump -uroot -p123456 -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M > /data/backup/full1.sql深度解析：
兩種情況：（mysqldump是修改客戶端–max_allowed_packet的大小）
（1） mysql發送insert指令是客戶端向服務端發送數據，如果報錯Got a packet bigger than ‘max_allowed_packet’ bytes，那么就修改服務端–max_allowed_packet的大小。
（2）mysqldump命令指令是服務端向客戶端發送數據，如果報錯Got a packet bigger than ‘max_allowed_packet’ bytes，那么就修改客戶端–max_allowed_packet的大小。
5. mysqldump+binlog故障恢復案例
這個栗子可以用binlog也可以恢復，但是假設這個創庫創表兩年前就有了，用全備+binlog進行恢復。
注意：備份數據、mysql數據、日志要分開放，別放一個地方
案例場景: 基礎環境: Centos 7.6 + MySQL 5.7.28, LNMT網站業務, 數據量100G, 每天5-10M數據增長。備份策略: mysqldump每天全備，binlog定時備份。故障說明: 周三上午10點數據故障，例如: 核心業務庫被誤刪除。 恢復思路：1. 掛維護頁（停業務，避免數據的二次傷害）2. 找測試庫3. 恢復周二23：00全備4. 根據周二全備文件找到起點的position號 --> 周三上午10點誤刪除之前的binlog，并恢復5. 測試業務功能正常6. 恢復業務（在MySQL中有邊緣數據庫，有可能其他邊緣業務使用，不敢直接進行全備恢復。從測試庫中單庫（核心庫）導出來，然后再恢復到生產庫中）方案1: 故障庫導回到原生產方案2: 直接用測試庫稱當生產，先跑著 模擬數據庫損壞及恢復：1. 模擬原始數據mysql> create database mdp charset utf8mb4;mysql> use mdp;mysql> create table t1 (id int);mysql> insert into t1 values(1),(2),(3);mysql> commit;2. 模擬周二晚上全備mysql> mysqldump -uroot -p123456 -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M > /data/backup/full_`date +%F`.sql壓縮備份：mysql> mysqldump -uroot -p123 -A -R --triggers --set-gtid-purged=OFF --master-data=2 --single-transaction|gzip > /backup/full_$(date +%F).sql.gz3. 模擬周二 23：00到周三 10點之間數據變化mysql> use mdp;mysql> create table t2 (id int);mysql> insert into t2 values(1),(2),(3);mysql> commit;4. 模擬故障,刪除表(只是模擬，不代表生產操作)mysql> drop database mdp;5. 開始恢復(再測試環境進行恢復，如果生產環境只是損壞了一張表，那么就在測試環境全備+binlog恢復完之后，進行導出單表，然后正在恢復到生產環境)5.1 檢查全備[root@db01 backup]# vim full_2023-02-23.sql# 備份開始時的binlog日志的起點位置-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000031', MASTER_LOG_POS=788;5.2 恢復全備# 如果開啟了GTID，在備份文件里面有SET @@SESSION.SQL_LOG_BIN=0作用是臨時取消了二進制日志記錄，但是沒有自動開啟，所以還是手動臨時開啟和關閉mysql> set sql_log_bin=0;mysql> source /data/backup/full_2023-02-23.sql驗證：(mdp數據庫已恢復，t1表和數據也已恢復)mysql> use mdp;mysql> show tables;+---------------+| Tables_in_mdp |+---------------+| t1 |+---------------+mysql> select * from t1;+------+| id |+------+| 1 || 2 || 3 |+------+用position號和GTID都可以來截取日志：（二選一） 5.3 截取binlogposition截取:起點:(788)[root@db01 backup]# grep "\-- CHANGE MASTER TO" /data/backup/full_2023-02-23.sql-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000031', MASTER_LOG_POS=788終點:(使用1210或者使用1275都可以，這里使用1210比較好)mysql> show binlog events in 'mysql-bin.000031';| mysql-bin.000031 | 1210 | Gtid | 6 | 1275 | SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:24' || mysql-bin.000031 | 1275 | Query | 6 | 1364 | drop database mdpposition日志截取:# 雖然說時使用的position號截取的，但是因為binlog里面開啟了GTID，所以依然要加上參數--skip-gtidsmysqlbinlog --skip-gtids --start-position=788 --stop-position=1210 /data/binlog/mysql-bin.000031 > /tmp/binlog.sqlGTID截取:(備份文件中的信息)# GTID號進行恢復，在備份文件中存在GTID的信息SET @@GLOBAL.GTID_PURGED='XXX:1-21'; GLOBAL.GTID_PURGE這是一個命令，意思是全備恢復數據的時候把1-21號的GTID給刪了，刪完了之后再恢復 言外之意是，此備份中已經具備了1-21號GTID信息了，也就是說全備結束的GTID的位置是21號[root@db01 backup]# vim full_2023-02-23.sqlSET @@GLOBAL.GTID_PURGED='638fec45-e734-11ec-bcd2-000c29d09be0:1-21';起點: 638fec45-e734-11ec-bcd2-000c29d09be0:22# show binlog events in 'mysql-bin.000031';查看確定終點終點: 638fec45-e734-11ec-bcd2-000c29d09be0:23(因為gtid=24為刪庫操作，所以要恢復到gtid的終點是23)| mysql-bin.000031 | 1210 | Gtid |6 |1275 | SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:24' || mysql-bin.000031 | 1275 | Query| 6 |1364 | drop database mdp GTID日志截取:（gtid可以跨文件，position號是不可以跨文件的。這也是gtid的優勢所在）mysqlbinlog --skip-gtids --include-gtids='xxx:17-18' /data/binlog/mysql-bin.000031 > /tmp/binlog1.sql小提示：沒有開啟gitd的話也要加上--skip-gtids,因為截取出還是帶有gtid的（猜測應該是之前開過gtid，不加也成功的話，因備份文件里面有SET @@GLOBAL.GTID_PURGED='XXX:1-21'，刪除了gtid） 建議加上--skip-gtids，標準用法5.4 恢復binlog# 如果開啟了GTID，在備份文件里面有SET @@SESSION.SQL_LOG_BIN=0作用是臨時取消了二進制日志記錄，但是沒有自動開啟，所以還是手動臨時開啟和關閉mysql> set sql_log_bin=0;# 使用position號或者gtid進行截取的日志，進行恢復mysql> source /tmp/binlog.sql;mysql> set sql_log_bin=1;# 驗證數據，成功恢復mysql> use mdp;mysql> show tables;+---------------+| Tables_in_mdp |+---------------+| t1 || t2 |+---------------+小練習:使用rm -rf /data/3306/* 故障模擬恢復 恢復思路：1. 需要把原來的實例給kill掉2. kill完之后把目錄清空，重新進行初始化，重新初始之后再按這上面的思路進行恢復6. mysqldump小總結
參數:-u -p -S -h -P-A -B --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64選擇場景:優點: 可讀性比較強，壓縮比，節省空間，不需要下載安裝。缺點：備份時間相對較長。恢復時間長。數據量較少，建議mysqldump 100G以內。數據量巨大: 分布式架構，數據量較大時候，可以采用分布式備份。也可以選擇mysqldump。（分布式架構（分別備份不同的分片），一個T數據，砍成十份，每份100G，并發的分布式備份。然后就沒有這么慢了 如果全備數據量大，但是只想恢復一張表，而這張表只有兩M，這時可以從全備中截取單表）mysqldump恢復是備份兩倍以上的時間mysqldump用壓縮工具壓縮備份出來的文件的壓縮比高（比物理備份要高）思考：mysql如何進行分布式備份？？？擴展： 從mysqldump 全備中獲取 庫和表的備份 1、獲得表結構 # sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `city`/!d;q' full.sql>createtable.sql 2、獲得INSERT INTO 語句，用于數據的恢復 # grep -i 'INSERT INTO `city`' full.sqll >data.sql & 3.獲取單庫的備份 # sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql >world.sql總結
以上是生活随笔為你收集整理的MySQL备份恢复（十二）的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 泛函分析 02.02 赋范空间-完备的赋
- 下一篇： 北京大学所有专硕确定不安排住宿！北大招生