MySQL 數(shù)據(jù)恢復(fù)的多種方法匯總
1、
數(shù)據(jù)恢復(fù)的前提的做好備份，且開啟 binlog，格式為 row。如果沒有備份文件，那么刪掉庫(kù)表后就真的刪掉了，lsof 中還有記錄的話，有可能恢復(fù)一部分文件。但若剛好數(shù)據(jù)庫(kù)沒有打開這個(gè)表文件，那就只能跑路了。如果沒有開啟 binlog，那么恢復(fù)數(shù)據(jù)后，從備份時(shí)間點(diǎn)開始的數(shù)據(jù)都沒了。如果 binlog 格式不為 row，那么在誤操作數(shù)據(jù)后就沒有辦法做閃回操作，只能老老實(shí)實(shí)地走備份恢復(fù)流程。
2、直接恢復(fù)
直接恢復(fù)是使用備份文件做全量恢復(fù)，這是最常見的場(chǎng)景。
2.1 mysqldump 備份全量恢復(fù)
使用 mysqldump 文件恢復(fù)數(shù)據(jù)非常簡(jiǎn)單，直接解壓了執(zhí)行：
gzip-dbackup.sql.gz|mysql-u<user>-h<host>-P<port>-p
2.2 xtrabackup 備份全量恢復(fù)
恢復(fù)過程：
#步驟一：解壓（如果沒有壓縮可以忽略這一步） innobackupex--decompress<備份文件所在目錄> #步驟二：應(yīng)用日志 innobackupex--apply-log<備份文件所在目錄> #步驟三：復(fù)制備份文件到數(shù)據(jù)目錄 innobackupex--datadir=<MySQL數(shù)據(jù)目錄>--copy-back<備份文件所在目錄>
2.3 基于時(shí)間點(diǎn)恢復(fù)
基于時(shí)間點(diǎn)的恢復(fù)依賴的是 binlog 日志，需要從 binlog 中找過從備份點(diǎn)到恢復(fù)點(diǎn)的所有日志，然后應(yīng)用。我們測(cè)試一下。
新建測(cè)試表：
chengqm-3306>>showcreatetablemytest.mytest\G; ***************************1.row*************************** Table:mytest CreateTable:CREATETABLE`mytest`( `id`int(11)NOTNULLAUTO_INCREMENT, `ctime`datetimeDEFAULTNULL, PRIMARYKEY(`id`) )ENGINE=InnoDBDEFAULTCHARSET=utf8
每秒插入一條數(shù)據(jù)：
[mysql@mysql-test~]$whiletrue;domysql-S/tmp/mysql.sock-e'insertin
備份：
[mysql@mysql-test~]$mysqldump--opt--single-transaction--master-data=2--defa
找出備份時(shí)的日志位置：
[mysql@mysql-test~]$head-n25backup.sql|grep'CHANGEMASTERTOMASTER_LOG_FILE' --CHANGEMASTERTOMASTER_LOG_FILE='mysql-bin.000032',MASTER_LOG_POS=39654;
假設(shè)要恢復(fù)到 2019-08-09 11:01:54 這個(gè)時(shí)間點(diǎn)，我們從 binlog 中查找從 39654 到 019-08-09 11:01:54 的日志。
[mysql@mysql-test~]$mysqlbinlog--start-position=39654--stop-datetime='2019-08-0911:01:54'/data/mysql_log/mysql_test/mysql-bin.000032>backup_inc.sql [mysql@mysql-test-83~]$tail-n20backup_inc.sql ...... ###INSERTINTO`mytest`.`mytest` ###SET ###@1=161/*INTmeta=0nullable=0is_null=0*/ ###@2='2019-08-0911:01:53'/*DATETIME(0)meta=0nullable=1is_null=0*/ ......
當(dāng)前數(shù)據(jù)條目數(shù)：
--2019-08-0911:01:54之前的數(shù)據(jù)條數(shù) chengqm-3306>>selectcount(*)frommytest.mytestwherectime<'2019-08-0911:01:54'; +----------+ |count(*)| +----------+ |161| +----------+ 1rowinset(0.00sec)
所有數(shù)據(jù)條數(shù)
chengqm-3306>>selectcount(*)frommytest.mytest; +----------+ |count(*)| +----------+ |180| +----------+ 1rowinset(0.00sec)
然后執(zhí)行恢復(fù)：
#全量恢復(fù) [mysql@mysql-test~]$mysql-S/tmp/mysql.sock<backup.sql #應(yīng)用增量日志 [mysql@mysql-test~]$mysql-S/tmp/mysql.sock<backup_inc.sql
檢查數(shù)據(jù)：
chengqm-3306>>selectcount(*)frommytest.mytest; +----------+ |count(*)| +----------+ |161| +----------+ 1rowinset(0.00sec) chengqm-3306>>select*frommytest.mytestorderbyiddesclimit5; +-----+---------------------+ |id|ctime| +-----+---------------------+ |161|2019-08-0911:01:53| |160|2019-08-0911:01:52| |159|2019-08-0911:01:51| |158|2019-08-0911:01:50| |157|2019-08-0911:01:49| +-----+---------------------+ 5rowsinset(0.00sec)
已經(jīng)恢復(fù)到 2019-08-09 11:01:54 這個(gè)時(shí)間點(diǎn)。
3、恢復(fù)一個(gè)表
3.1 從 mysqldump 備份恢復(fù)一個(gè)表
假設(shè)要恢復(fù)的表是 mytest.mytest：
#提取某個(gè)庫(kù)的所有數(shù)據(jù)
sed-n'/^--CurrentDatabase:`mytest`/,/^--CurrentDatabase:/p'backup.sql>backup_mytest.sql
#從庫(kù)備份文件中提取建表語(yǔ)句
sed-e'/./{H;$!d;}'-e'x;/CREATETABLE`mytest`/!d;q'backup_mytest.sql>mytest_table_create.sql
#從庫(kù)備份文件中提取插入數(shù)據(jù)語(yǔ)句
grep-i'INSERTINTO`mytest`'backup_mytest.sql>mytest_table_insert.sql
#恢復(fù)表結(jié)構(gòu)到mytest庫(kù)
mysql-u<user>-pmytest<mytest_table_create.sql
#恢復(fù)表數(shù)據(jù)到mytest.mytest表
mysql-u<user>-pmytest<mytest_table_insert.sql
3.2 從 xtrabackup 備份恢復(fù)一個(gè)表
假設(shè) ./backup_xtra_full 目錄為解壓后應(yīng)用過日志的備份文件。
3.2.1 MyISAM 表
假設(shè)從備份文件中恢復(fù)表 mytest.t_myisam。從備份文件中找到 t_myisam.frm, t_myisam.MYD, t_myisam.MYI 這 3 個(gè)文件，復(fù)制到對(duì)應(yīng)的數(shù)據(jù)目錄中，并授權(quán)
進(jìn)入 MySQL。檢查表情況：
chengqm-3306>>showtables; +------------------+ |Tables_in_mytest| +------------------+ |mytest| |t_myisam| +------------------+ 2rowsinset(0.00sec) chengqm-3306>>checktablet_myisam; +-----------------+-------+----------+----------+ |Table|Op|Msg_type|Msg_text| +-----------------+-------+----------+----------+ |mytest.t_myisam|check|status|OK| +-----------------+-------+----------+----------+ 1rowinset(0.00sec)
3.2.2 Innodb 表
假設(shè)從備份文件中恢復(fù)表 mytest.t_innodb，恢復(fù)前提是設(shè)置了 innodb_file_per_table = on：
- 起一個(gè)新實(shí)例；
- 在實(shí)例上建一個(gè)和原來一模一樣的表；
- 執(zhí)行 alter table t_innodb discard tablespace; 刪除表空間，這個(gè)操作會(huì)把 t_innodb.ibd 刪除；
- 從備份文件中找到 t_innodb.ibd 這個(gè)文件，復(fù)制到對(duì)應(yīng)的數(shù)據(jù)目錄，并授權(quán)；
- 執(zhí)行 alter table t_innodb IMPORT tablespace; 加載表空間；
- 執(zhí)行 flush table t_innodb;check table t_innodb; 檢查表；
- 使用 mysqldump 導(dǎo)出數(shù)據(jù)，然后再導(dǎo)入到要恢復(fù)的數(shù)據(jù)庫(kù)。
注意：
在新實(shí)例上恢復(fù)再 dump 出來是為了避免風(fēng)險(xiǎn)，如果是測(cè)試，可以直接在原庫(kù)上操作步驟 2-6；
只在 8.0 以前的版本有效。
4、跳過誤操作SQL
跳過誤操作 SQL 一般用于執(zhí)行了無法閃回的操作比如 drop table\database。
4.1 使用備份文件恢復(fù)跳過
4.1.1 不開啟 GTID
使用備份文件恢復(fù)的步驟和基于時(shí)間點(diǎn)恢復(fù)的操作差不多，區(qū)別在于多一個(gè)查找 binlog 操作。舉個(gè)例子，我這里建立了兩個(gè)表 a 和 b，每分鐘插入一條數(shù)據(jù)，然后做全量備份，再刪除表 b，現(xiàn)在要跳過這條 SQL。
刪除表 b 后的數(shù)據(jù)庫(kù)狀態(tài)：
chgnqm-3306>>showtables; +------------------+ |Tables_in_mytest| +------------------+ |a| +------------------+ 1rowinset(0.00sec)
找出備份時(shí)的日志位置
[mysql@mysql-test~]$head-n25backup.sql|grep'CHANGEMASTERTOMASTER_LOG_FILE' --CHANGEMASTERTOMASTER_LOG_FILE='mysql-bin.000034',MASTER_LOG_POS=38414;
找出執(zhí)行了 drop table 語(yǔ)句的 pos 位置
[mysql@mysql-testmysql_test]$mysqlbinlog-vv/data/mysql_log/mysql_test/mysql-bin.000034|grep-i-B3'droptable`b`'; #at120629 #19081819:48:30serverid83end_log_pos120747CRC320x6dd6ab2aQuerythread_id=29488exec_time=0error_code=0 SETTIMESTAMP=1566128910/*!*/; DROPTABLE`b`/*generatedbyserver*/
從結(jié)果中我們可以看到 drop 所在語(yǔ)句的開始位置是 120629，結(jié)束位置是 120747。
從 binglog 中提取跳過這條語(yǔ)句的其他記錄
#第一條的start-position為備份文件的pos位置，stop-position為drop語(yǔ)句的開始位置 mysqlbinlog-vv--start-position=38414--stop-position=120629/data/mysql_log/mysql_test/mysql-bin.000034>backup_inc_1.sql #第二條的start-position為drop語(yǔ)句的結(jié)束位置 mysqlbinlog-vv--start-position=120747/data/mysql_log/mysql_test/mysql-bin.00003
恢復(fù)備份文件
[mysql@mysql-test~]$mysql-S/tmp/mysql.sock<backup.sql
全量恢復(fù)后狀態(tài)：
chgnqm-3306>>showtables; +------------------+ |Tables_in_mytest| +------------------+ |a| |b| +------------------+ 2rowsinset(0.00sec) chgnqm-3306>>selectcount(*)froma; +----------+ |count(*)| +----------+ |71| +----------+ 1rowinset(0.00sec)
恢復(fù)增量數(shù)據(jù)
[mysql@mysql-test~]$mysql-S/tmp/mysql.sock<backup_inc_1.sql [mysql@mysql-test~]$mysql-S/tmp/mysql.sock<backup_inc_2.sql
恢復(fù)后狀態(tài)，可以看到已經(jīng)跳過了 drop 語(yǔ)句：
chgnqm-3306>>showtables; +------------------+ |Tables_in_mytest| +------------------+ |a| |b| +------------------+ 2rowsinset(0.00sec) chgnqm-3306>>selectcount(*)froma; +----------+ |count(*)| +----------+ |274| +----------+ 1rowinset(0.00sec)
4.1.2 開啟 GTID
使用 GTID 可以直接跳過錯(cuò)誤的 SQL：
- 找出備份時(shí)的日志位置；
- 找出執(zhí)行了 drop table 語(yǔ)句的 GTID 值；
- 導(dǎo)出備份時(shí)日志位置到最新的 binglog 日志；
- 恢復(fù)備份文件；
- 跳過這個(gè) GTID；
SET SESSION GTID_NEXT='對(duì)應(yīng)的 GTID 值'; BEGIN; COMMIT; SET SESSION GTID_NEXT = AUTOMATIC;
應(yīng)用步驟 3 得到的增量 binlog 日志。
4.2 使用延遲庫(kù)跳過
4.2.1 不開啟 GTID
使用延遲庫(kù)恢復(fù)的關(guān)鍵操作在于 start slave until。我在測(cè)試環(huán)境搭建了兩個(gè) MySQL 節(jié)點(diǎn)，節(jié)點(diǎn)二延遲600秒，新建 a，b 兩個(gè)表，每秒插入一條數(shù)據(jù)模擬業(yè)務(wù)數(shù)據(jù)插入。
localhost:3306->localhost:3307(delay600)
當(dāng)前節(jié)點(diǎn)二狀態(tài)：
chengqm-3307>>showslavestatus\G; ... Master_Port:3306 Connect_Retry:60 Master_Log_File:mysql-bin.000039 Read_Master_Log_Pos:15524 Relay_Log_File:mysql-relay-bin.000002 Relay_Log_Pos:22845 Relay_Master_Log_File:mysql-bin.000038 Slave_IO_Running:Yes Slave_SQL_Running:Yes ... Seconds_Behind_Master:600 ...
當(dāng)前節(jié)點(diǎn)二表：
chengqm-3307>>showtables; +------------------+ |Tables_in_mytest| +------------------+ |a| |b| +------------------+
在節(jié)點(diǎn)一刪除表 b：
chengqm-3306>>droptableb; QueryOK,0rowsaffected(0.00sec) chengqm-3306>>showtables; +------------------+ |Tables_in_mytest| +------------------+ |a| +------------------+ 1rowinset(0.00sec)
接下來就是跳過這條 SQL 的操作步驟。
延遲庫(kù)停止同步
stopslave;
找出執(zhí)行了 drop table 語(yǔ)句的前一句的 pos 位置
[mysql@mysql-test~]$mysqlbinlog-vv/data/mysql_log/mysql_test/mysql-bin.000039|grep-i-B10'droptable`b`'; ... #at35134 #19081911:40:25serverid83end_log_pos35199CRC320x02771167Anonymous_GTIDlast_committed=132sequence_number=133rbr_only=no SET@@SESSION.GTID_NEXT='ANONYMOUS'/*!*/; #at35199 #19081911:40:25serverid83end_log_pos35317CRC320x50a018aaQuerythread_id=37155exec_time=0error_code=0 use`mytest`/*!*/; SETTIMESTAMP=1566186025/*!*/; DROPTABLE`b`/*generatedbyserver*/
從結(jié)果中我們可以看到 drop 所在語(yǔ)句的前一句開始位置是 35134，所以我們同步到 35134（這個(gè)可別選錯(cuò)了）。
延遲庫(kù)同步到要跳過的 SQL 前一條
changemastertomaster_delay=0; startslaveuntilmaster_log_file='mysql-bin.000039',master_log_pos=35134;
查看狀態(tài)看到已經(jīng)同步到對(duì)應(yīng)節(jié)點(diǎn)：
chengqm-3307>>showslavestatus\G; ... Master_Port:3306 Connect_Retry:60 Master_Log_File:mysql-bin.000039 Read_Master_Log_Pos:65792 ... Slave_IO_Running:Yes Slave_SQL_Running:No Exec_Master_Log_Pos:35134 ... Until_Log_File:mysql-bin.000039 Until_Log_Pos:35134
跳過一條 SQL 后開始同步
setglobalsql_slave_skip_counter=1; startslave;
查看同步狀態(tài)，刪除表 b 的語(yǔ)句已經(jīng)被跳過：
chengqm-3307>>showslavestatus\G; ... Slave_IO_Running:Yes Slave_SQL_Running:Yes ... 1rowinset(0.00sec) chengqm-3307>>showtables; +------------------+ |Tables_in_mytest| +------------------+ |a| |b| +------------------+ 2rowsinset(0.00sec)
4.2.2 開啟 GTID
使用 GTID 跳過的步驟會(huì)簡(jiǎn)單很多，只要執(zhí)行一條和要跳過的 SQL 的 GTID 相同的事務(wù)就可以跳過了。
- 停止同步；
- 找出執(zhí)行了 drop table 語(yǔ)句的 GTID；
- 執(zhí)行這個(gè) GTID 的事務(wù)；
SETSESSIONGTID_NEXT='對(duì)應(yīng)的GTID值'; BEGIN;COMMIT; SETSESSIONGTID_NEXT=AUTOMATIC;
- 繼續(xù)同步；
5. 閃回。
閃回操作就是反向操作，比如執(zhí)行了 delete from a where id=1，閃回就會(huì)執(zhí)行對(duì)應(yīng)的插入操作 insert into a (id,...) values(1,...)，用于誤操作數(shù)據(jù)，只對(duì) DML 語(yǔ)句有效，且要求 binlog 格式設(shè)為 ROW。本章介紹兩個(gè)比較好用的開源工具。
5.1 binlog2sql
binlog2sql 是大眾點(diǎn)評(píng)開源的一款用于解析 binlog 的工具，可以用于生成閃回語(yǔ)句，項(xiàng)目地址 binlog2sql。
5.1.1 安裝
wgethttps://github.com/danfengcao/binlog2sql/archive/master.zip-Obinlog2sql.zip unzipbinlog2sql.zip cdbinlog2sql-master/ #安裝依賴 pipinstall-rrequirements.txt
5.1.2 生成回滾SQL
pythonbinlog2sql/binlog2sql.py--flashback\ -h<host>-P<port>-u<user>-p'<password>'-d<dbname>-t<table_name>\ --start-file='<binlog_file>'\ --start-datetime='<start_time>'\ --stop-datetime='<stop_time>'>./flashback.sql pythonbinlog2sql/binlog2sql.py--flashback\ -h<host>-P<port>-u<user>-p'<password>'-d<dbname>-t<table_name>\ --start-file='<binlog_file>'\ --start-position=<start_pos>\ --stop-position=<stop_pos>>./flashback.sql
5.2 MyFlash
MyFlash 是由美團(tuán)點(diǎn)評(píng)公司技術(shù)工程部開發(fā)維護(hù)的一個(gè)回滾 DML 操作的工具，項(xiàng)目鏈接 MyFlash。
限制：
- binlog 格式必須為 row，且 binlog_row_image=full；
- 僅支持5.6與5.7；
- 只能回滾 DML（增、刪、改）。
5.2.1 安裝
#依賴(centos) yuminstallgcc*pkg-configglib2libgnomeui-devel-y #下載文件 wgethttps://github.com/Meituan-Dianping/MyFlash/archive/master.zip-OMyFlash.zip unzipMyFlash.zip cdMyFlash-master #編譯安裝 gcc-w`pkg-config--cflags--libsglib-2.0`source/binlogParseGlib.c-obinary/flashback mvbinary/usr/local/MyFlash ln-s/usr/local/MyFlash/flashback/usr/bin/flashback
5.2.2 使用
生成回滾語(yǔ)句：
flashback--databaseNames=<dbname>--binlogFileNames=<binlog_file>--start-position=<s
執(zhí)行后會(huì)生成 binlog_output_base.flashback 文件，需要用 mysqlbinlog 解析出來再使用：
mysqlbinlog-vvbinlog_output_base.flashback|mysql-u<user>-p
以上就是MySQL 數(shù)據(jù)恢復(fù)的多種方法匯總的詳細(xì)內(nèi)容，更多關(guān)于MySQL 數(shù)據(jù)恢復(fù)的資料請(qǐng)關(guān)注本站其它相關(guān)文章！
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信