自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

刪庫不必跑路！詳解MySQL數(shù)據(jù)恢復(fù)

作者：佚名
數(shù)據(jù)庫 MySQL
日常工作中，總會有因手抖、寫錯條件、寫錯表名、錯連生產(chǎn)庫造成的誤刪庫表和數(shù)據(jù)的事情發(fā)生。但是，如果每次刪庫都跑路的話，怕是再也不好找工作了吧！所以，刪庫跑路不是上上策。

日常工作中，總會有因手抖、寫錯條件、寫錯表名、錯連生產(chǎn)庫造成的誤刪庫表和數(shù)據(jù)的事情發(fā)生。但是，如果每次刪庫都跑路的話，怕是再也不好找工作了吧！所以，刪庫跑路不是上上策。

1、前言

數(shù)據(jù)恢復(fù)的前提的做好備份，且開啟 binlog，格式為 row。如果沒有備份文件，那么刪掉庫表后就真的刪掉了，lsof 中還有記錄的話，有可能恢復(fù)一部分文件。但若剛好數(shù)據(jù)庫沒有打開這個表文件，那就只能跑路了。如果沒有開啟 binlog，那么恢復(fù)數(shù)據(jù)后，從備份時間點開始的數(shù)據(jù)都沒了。如果 binlog 格式不為 row，那么在誤操作數(shù)據(jù)后就沒有辦法做閃回操作，只能老老實實地走備份恢復(fù)流程。

2、直接恢復(fù)

直接恢復(fù)是使用備份文件做全量恢復(fù)，這是最常見的場景。

2.1 mysqldump 備份全量恢復(fù)

使用 mysqldump 文件恢復(fù)數(shù)據(jù)非常簡單，直接解壓了執(zhí)行：

  1. gzip -d backup.sql.gz | mysql -u<user> -h<host> -P<port> -p 

2.2 xtrabackup 備份全量恢復(fù)

恢復(fù)過程： 

  1. # 步驟一：解壓（如果沒有壓縮可以忽略這一步）  
  2. innobackupex --decompress <備份文件所在目錄>  
  3. # 步驟二：應(yīng)用日志  
  4. innobackupex --apply-log <備份文件所在目錄>   
  5. # 步驟三：復(fù)制備份文件到數(shù)據(jù)目錄  
  6. innobackupex --datadir=<MySQL數(shù)據(jù)目錄> --copy-back <備份文件所在目錄> 

2.3 基于時間點恢復(fù)

基于時間點的恢復(fù)依賴的是 binlog 日志，需要從 binlog 中找過從備份點到恢復(fù)點的所有日志，然后應(yīng)用。我們測試一下。

新建測試表： 

  1. chengqm-3306>>show create table mytest.mytest \G;  
  2. *************************** 1. row ***************************  
  3.        Table: mytest  
  4. Create Table: CREATE TABLE `mytest` (  
  5.   `id` int(11) NOT NULL AUTO_INCREMENT, 
  6.   `ctime` datetime DEFAULT NULL,  
  7.   PRIMARY KEY (`id`)  
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 

每秒插入一條數(shù)據(jù)： 

  1. [mysql@mysql-test ~]$ while true; do mysql -S /tmp/mysql.sock -e 'insert into mytest.mytest(ctime)values(now())';date;sleep 1;done 

備份： 

  1. [mysql@mysql-test ~]$ mysqldump --opt --single-transaction --master-data=2 --default-character-set=utf8 -S /tmp/mysql.sock -A > backup.sql 

找出備份時的日志位置： 

  1. [mysql@mysql-test ~]$ head -n 25 backup.sql | grep 'CHANGE MASTER TO MASTER_LOG_FILE'  
  2. -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000032', MASTER_LOG_POS=39654; 

假設(shè)要恢復(fù)到 2019-08-09 11:01:54 這個時間點，我們從 binlog 中查找從 39654 到 019-08-09 11:01:54 的日志。 

  1. [mysql@mysql-test ~]$ mysqlbinlog --start-position=39654 --stop-datetime='2019-08-09 11:01:54' /data/mysql_log/mysql_test/mysql-bin.000032 > backup_inc.sql  
  2. [mysql@mysql-test-83 ~]$ tail -n 20 backup_inc.sql  
  3. ......  
  4. ### INSERT INTO `mytest`.`mytest`  
  5. ### SET  
  6. ###   @1=161 /* INT meta=0 nullable=0 is_null=0 */  
  7. ###   @2='2019-08-09 11:01:53' /* DATETIME(0) meta=0 nullable=1 is_null=0 */  
  8. ...... 

當(dāng)前數(shù)據(jù)條目數(shù)： 

  1. -- 2019-08-09 11:01:54之前的數(shù)據(jù)條數(shù)  
  2. chengqm-3306>>select count(*) from mytest.mytest where ctime < '2019-08-09 11:01:54';  
  3. +----------+  
  4. | count(*) |  
  5. +----------+  
  6. |      161 |  
  7. +----------+  
  8. 1 row in set (0.00 sec) 

所有數(shù)據(jù)條數(shù) 

  1. chengqm-3306>>select count(*) from mytest.mytest;  
  2. +----------+  
  3. | count(*) |  
  4. +----------+  
  5. |      180 |  
  6. +----------+  
  7. 1 row in set (0.00 sec) 

然后執(zhí)行恢復(fù)： 

  1. # 全量恢復(fù)  
  2. [mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup.sql   
  3. # 應(yīng)用增量日志  
  4. [mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup_inc.sql 

檢查數(shù)據(jù)： 

  1. chengqm-3306>>select count(*) from mytest.mytest;  
  2. +----------+  
  3. | count(*) |  
  4. +----------+  
  5. |      161 |  
  6. +----------+  
  7. 1 row in set (0.00 sec)  
  8. chengqm-3306>>select * from mytest.mytest order by id desc limit 5;  
  9. +-----+---------------------+  
  10. | id  | ctime               |  
  11. +-----+---------------------+  
  12. | 161 | 2019-08-09 11:01:53 |  
  13. | 160 | 2019-08-09 11:01:52 |  
  14. | 159 | 2019-08-09 11:01:51 |  
  15. | 158 | 2019-08-09 11:01:50 |  
  16. | 157 | 2019-08-09 11:01:49 |  
  17. +-----+---------------------+  
  18. 5 rows in set (0.00 sec) 

已經(jīng)恢復(fù)到 2019-08-09 11:01:54 這個時間點。

3、恢復(fù)一個表

3.1 從 mysqldump 備份恢復(fù)一個表

假設(shè)要恢復(fù)的表是 mytest.mytest： 

  1. # 提取某個庫的所有數(shù)據(jù)  
  2. sed -n '/^-- Current Database: `mytest`/,/^-- Current Database:/p' backup.sql > backup_mytest.sql  
  3. # 從庫備份文件中提取建表語句  
  4. sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `mytest`/!d;q' backup_mytest.sql > mytest_table_create.sql  
  5. # 從庫備份文件中提取插入數(shù)據(jù)語句  
  6. grep -i 'INSERT INTO `mytest`' backup_mytest.sql > mytest_table_insert.sql  
  7. # 恢復(fù)表結(jié)構(gòu)到 mytest 庫  
  8. mysql -u<user> -p mytest < mytest_table_create.sql  
  9. # 恢復(fù)表數(shù)據(jù)到 mytest.mytest 表  
  10. mysql -u<user> -p mytest <  mytest_table_insert.sql 

3.2 從 xtrabackup 備份恢復(fù)一個表

假設(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 個文件，復(fù)制到對應(yīng)的數(shù)據(jù)目錄中，并授權(quán)

進入 MySQL。檢查表情況： 

  1. chengqm-3306>>show tables;  
  2. +------------------+  
  3. | Tables_in_mytest | 
  4. +------------------+  
  5. | mytest           |  
  6. | t_myisam         |  
  7. +------------------+  
  8. 2 rows in set (0.00 sec)  
  9. chengqm-3306>>check table t_myisam;  
  10. +-----------------+-------+----------+----------+  
  11. | Table           | Op    | Msg_type | Msg_text |  
  12. +-----------------+-------+----------+----------+  
  13. | mytest.t_myisam | check | status   | OK       |  
  14. +-----------------+-------+----------+----------+  
  15. 1 row in set (0.00 sec) 

3.2.2 Innodb 表

假設(shè)從備份文件中恢復(fù)表 mytest.t_innodb，恢復(fù)前提是設(shè)置了 innodb_file_per_table = on：

  •  起一個新實例；
  •  在實例上建一個和原來一模一樣的表；
  •  執(zhí)行 alter table t_innodb discard tablespace; 刪除表空間，這個操作會把 t_innodb.ibd 刪除；
  •  從備份文件中找到 t_innodb.ibd 這個文件，復(fù)制到對應(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ù)庫。

注意：

  •  在新實例上恢復(fù)再 dump 出來是為了避免風(fēng)險，如果是測試，可以直接在原庫上操作步驟 2-6；
  •  只在 8.0 以前的版本有效。

4、跳過誤操作SQL

跳過誤操作 SQL 一般用于執(zhí)行了無法閃回的操作比如 drop table\database。

4.1 使用備份文件恢復(fù)跳過

4.1.1 不開啟 GTID

使用備份文件恢復(fù)的步驟和基于時間點恢復(fù)的操作差不多，區(qū)別在于多一個查找 binlog 操作。舉個例子，我這里建立了兩個表 a 和 b，每分鐘插入一條數(shù)據(jù)，然后做全量備份，再刪除表 b，現(xiàn)在要跳過這條 SQL。

刪除表 b 后的數(shù)據(jù)庫狀態(tài)： 

  1. chgnqm-3306>>show tables;  
  2. +------------------+  
  3. | Tables_in_mytest |  
  4. +------------------+  
  5. | a                |  
  6. +------------------+  
  7. 1 row in set (0.00 sec) 
  1.  找出備份時的日志位置 
  1. [mysql@mysql-test ~]$ head -n 25 backup.sql | grep 'CHANGE MASTER TO MASTER_LOG_FILE'  
  2. -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000034', MASTER_LOG_POS=38414; 

      2.  找出執(zhí)行了 drop table 語句的 pos 位置 

  1. [mysql@mysql-test mysql_test]$  mysqlbinlog -vv /data/mysql_log/mysql_test/mysql-bin.000034 | grep -i -B 3 'drop table `b`';  
  2. # at 120629  
  3. #190818 19:48:30 server id 83  end_log_pos 120747 CRC32 0x6dd6ab2a     Query    thread_id=29488    exec_time=0    error_code=0  
  4. SET TIMESTAMP=1566128910/*!*/; 
  5. DROP TABLE `b` /* generated by server */ 

從結(jié)果中我們可以看到 drop 所在語句的開始位置是 120629，結(jié)束位置是 120747。

      3.  從 binglog 中提取跳過這條語句的其他記錄 

  1. # 第一條的 start-position 為備份文件的 pos 位置，stop-position 為 drop 語句的開始位置  
  2. mysqlbinlog -vv --start-position=38414 --stop-position=120629 /data/mysql_log/mysql_test/mysql-bin.000034 > backup_inc_1.sql  
  3. # 第二條的 start-position 為 drop 語句的結(jié)束位置  
  4. mysqlbinlog -vv --start-position=120747 /data/mysql_log/mysql_test/mysql-bin.000034 > backup_inc_2.sql 

      4.  恢復(fù)備份文件 

  1. [mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup.sql 

全量恢復(fù)后狀態(tài)： 

  1. chgnqm-3306>>show tables;  
  2. +------------------+  
  3. | Tables_in_mytest | 
  4. +------------------+  
  5. | a                |  
  6. | b                |  
  7. +------------------+  
  8. 2 rows in set (0.00 sec)  
  9. chgnqm-3306>>select count(*) from a;  
  10. +----------+  
  11. | count(*) |  
  12. +----------+  
  13. |       71 |  
  14. +----------+  
  15. 1 row in set (0.00 sec) 

     5.   恢復(fù)增量數(shù)據(jù) 

  1. [mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup_inc_1.sql  
  2. [mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup_inc_2.sql 

恢復(fù)后狀態(tài)，可以看到已經(jīng)跳過了 drop 語句： 

  1. chgnqm-3306>>show tables;  
  2. +------------------+  
  3. | Tables_in_mytest |  
  4. +------------------+  
  5. | a                |  
  6. | b                |  
  7. +------------------+  
  8. 2 rows in set (0.00 sec)  
  9. chgnqm-3306>>select count(*) from a;  
  10. +----------+  
  11. | count(*) |  
  12. +----------+  
  13. |      274 |  
  14. +----------+  
  15. 1 row in set (0.00 sec) 

4.1.2 開啟 GTID

使用 GTID 可以直接跳過錯誤的 SQL：

  •  找出備份時的日志位置；
  •  找出執(zhí)行了 drop table 語句的 GTID 值；
  •  導(dǎo)出備份時日志位置到最新的 binglog 日志；
  •  恢復(fù)備份文件；
  •   跳過這個 GTID； 
  1. SET SESSION GTID_NEXT='對應(yīng)的 GTID 值';  
  2. BEGIN; COMMIT; 
  3. SET SESSION GTID_NEXT = AUTOMATIC; 
  •  應(yīng)用步驟 3 得到的增量 binlog 日志。

4.2 使用延遲庫跳過

4.2.1 不開啟 GTID

使用延遲庫恢復(fù)的關(guān)鍵操作在于 start slave until。我在測試環(huán)境搭建了兩個 MySQL 節(jié)點，節(jié)點二延遲600秒，新建 a，b 兩個表，每秒插入一條數(shù)據(jù)模擬業(yè)務(wù)數(shù)據(jù)插入。 

  1. localhost:3306 -> localhost:3307(delay 600) 

當(dāng)前節(jié)點二狀態(tài)： 

  1. chengqm-3307>>show slave status \G;  
  2. ...  
  3.                   Master_Port: 3306  
  4.                 Connect_Retry: 60  
  5.               Master_Log_File: mysql-bin.000039  
  6.           Read_Master_Log_Pos: 15524  
  7.                Relay_Log_File: mysql-relay-bin.000002  
  8.                 Relay_Log_Pos: 22845  
  9.         Relay_Master_Log_File: mysql-bin.000038  
  10.              Slave_IO_Running: Yes  
  11.             Slave_SQL_Running: Yes  
  12. ...  
  13.         Seconds_Behind_Master: 600  
  14. ... 

當(dāng)前節(jié)點二表： 

  1. chengqm-3307>>show tables;  
  2. +------------------+  
  3. | Tables_in_mytest |  
  4. +------------------+  
  5. | a                |  
  6. | b                |  
  7. +------------------+ 

在節(jié)點一刪除表 b： 

  1. chengqm-3306>>drop table b;  
  2. Query OK, 0 rows affected (0.00 sec)  
  3. chengqm-3306>>show tables;  
  4. +------------------+  
  5. | Tables_in_mytest |  
  6. +------------------+  
  7. | a                |  
  8. +------------------+  
  9. 1 row in set (0.00 sec) 

接下來就是跳過這條 SQL 的操作步驟。

  1.  延遲庫停止同步
  1. stop slave; 

      2.  找出執(zhí)行了 drop table 語句的前一句的 pos 位置 

  1. [mysql@mysql-test ~]$ mysqlbinlog -vv /data/mysql_log/mysql_test/mysql-bin.000039 | grep -i -B 10 'drop table `b`';  
  2. ...  
  3. # at 35134 
  4. #190819 11:40:25 server id 83  end_log_pos 35199 CRC32 0x02771167     Anonymous_GTID    last_committed=132    sequence_number=133    rbr_only=no  
  5. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;  
  6. # at 35199  
  7. #190819 11:40:25 server id 83  end_log_pos 35317 CRC32 0x50a018aa     Query    thread_id=37155    exec_time=0    error_code=0  
  8. use `mytest`/*!*/;  
  9. SET TIMESTAMP=1566186025/*!*/;  
  10. DROP TABLE `b` /* generated by server */ 

從結(jié)果中我們可以看到 drop 所在語句的前一句開始位置是 35134，所以我們同步到 35134（這個可別選錯了）。

      3.  延遲庫同步到要跳過的 SQL 前一條 

  1. change master to master_delay=0;  
  2. start slave until master_log_file='mysql-bin.000039',master_log_pos=35134; 

查看狀態(tài)看到已經(jīng)同步到對應(yīng)節(jié)點： 

  1. chengqm-3307>>show slave status \G;  
  2. ...  
  3.                   Master_Port: 3306  
  4.                 Connect_Retry: 60  
  5.               Master_Log_File: mysql-bin.000039  
  6.           Read_Master_Log_Pos: 65792  
  7. ...  
  8.              Slave_IO_Running: Yes  
  9.             Slave_SQL_Running: No  
  10.           Exec_Master_Log_Pos: 35134  
  11. ...  
  12.                Until_Log_File: mysql-bin.000039  
  13.                 Until_Log_Pos: 35134 

      4.  跳過一條 SQL 后開始同步 

  1. set global sql_slave_skip_counter=1;  
  2. start slave; 

查看同步狀態(tài)，刪除表 b 的語句已經(jīng)被跳過： 

  1. chengqm-3307>>show slave status \G;  
  2. ...  
  3.              Slave_IO_Running: Yes  
  4.             Slave_SQL_Running: Yes 
  5. ...  
  6. 1 row in set (0.00 sec)  
  7. chengqm-3307>>show tables;  
  8. +------------------+  
  9. | Tables_in_mytest |  
  10. +------------------+ 
  11. | a                | 
  12. | b                |  
  13. +------------------+  
  14. 2 rows in set (0.00 sec) 

4.2.2 開啟 GTID

使用 GTID 跳過的步驟會簡單很多，只要執(zhí)行一條和要跳過的 SQL 的 GTID 相同的事務(wù)就可以跳過了。

  •  停止同步；
  •  找出執(zhí)行了 drop table 語句的 GTID；
  •  執(zhí)行這個 GTID 的事務(wù)； 
  1. SET SESSION GTID_NEXT='對應(yīng)的 GTID 值';  
  2. BEGIN; COMMIT; 
  3. SET SESSION GTID_NEXT = AUTOMATIC; 
  •  繼續(xù)同步；

5. 閃回。

閃回操作就是反向操作，比如執(zhí)行了 delete from a where id=1，閃回就會執(zhí)行對應(yīng)的插入操作 insert into a (id,...) values(1,...)，用于誤操作數(shù)據(jù)，只對 DML 語句有效，且要求 binlog 格式設(shè)為 ROW。本章介紹兩個比較好用的開源工具。

5.1 binlog2sql

binlog2sql 是大眾點評開源的一款用于解析 binlog 的工具，可以用于生成閃回語句，項目地址 binlog2sql。

5.1.1 安裝 

  1. wget https://github.com/danfengcao/binlog2sql/archive/master.zip -O binlog2sql.zip  
  2. unzip binlog2sql.zip  
  3. cd binlog2sql-master/  
  4. # 安裝依賴  
  5. pip install -r requirements.txt 

5.1.2 生成回滾SQL 

  1. python binlog2sql/binlog2sql.py --flashback \  
  2. -h<host> -P<port> -u<user> -p'<password>' -d<dbname> -t<table_name>\  
  3. --start-file='<binlog_file>' \  
  4. --start-datetime='<start_time>' \  
  5. --stop-datetime='<stop_time>' > ./flashback.sql  
  6. python binlog2sql/binlog2sql.py --flashback \  
  7. -h<host> -P<port> -u<user> -p'<password>' -d<dbname> -t<table_name> \  
  8. --start-file='<binlog_file>' \  
  9. --start-position=<start_pos> \  
  10. --stop-position=<stop_pos> > ./flashback.sql 

5.2 MyFlash

MyFlash 是由美團點評公司技術(shù)工程部開發(fā)維護的一個回滾 DML 操作的工具，項目鏈接 MyFlash。

限制：

  •  binlog 格式必須為 row，且 binlog_row_image=full；
  •  僅支持5.6與5.7；
  •  只能回滾 DML（增、刪、改）。

5.2.1 安裝 

  1. # 依賴(centos)  
  2. yum install gcc*  pkg-config glib2 libgnomeui-devel -y  
  3. # 下載文件  
  4. wget https://github.com/Meituan-Dianping/MyFlash/archive/master.zip -O MyFlash.zip  
  5. unzip MyFlash.zip  
  6. cd MyFlash-master  
  7. # 編譯安裝  
  8. gcc -w  `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c  -o binary/flashback  
  9. mv binary /usr/local/MyFlash  
  10. ln -s /usr/local/MyFlash/flashback /usr/bin/flashback 

5.2.2 使用

生成回滾語句： 

  1. flashback --databaseNames=<dbname> --binlogFileNames=<binlog_file> --start-position=<start_pos> --stop-position=<stop_pos>  

執(zhí)行后會生成 binlog_output_base.flashback 文件，需要用 mysqlbinlog 解析出來再使用： 

  1. mysqlbinlog -vv binlog_output_base.flashback | mysql -u<user> -p  

 

責(zé)任編輯：龐桂玉 來源： 民工哥技術(shù)之路
刪庫MySQL數(shù)據(jù)庫
相關(guān)推薦

2019-08-20 14:20:19

MySQL數(shù)據(jù)恢復(fù)數(shù)據(jù)庫

2025-04-17 03:30:00

MySQL數(shù)據(jù)備份

2024-03-29 08:08:25

2020-08-25 17:30:32

MySQL數(shù)據(jù)庫數(shù)據(jù)恢復(fù)

2018-06-26 13:30:32

數(shù)據(jù)庫MySQL損壞恢復(fù)

2018-10-24 16:25:24

數(shù)據(jù)庫MySQLxtraback

2018-02-23 13:41:05

數(shù)據(jù)庫MySQL數(shù)據(jù)恢復(fù)

2020-10-21 08:59:50

刪庫程序員虛擬機

2020-03-03 17:28:39

CIO刪庫微盟

2018-09-21 16:30:55

2017-09-11 10:09:59

刪庫DBA淘汰

2020-11-27 14:45:57

開發(fā)服務(wù)器代碼

2022-06-02 16:56:46

刪庫刪庫跑路

2018-07-11 10:24:33

數(shù)據(jù)恢復(fù)數(shù)據(jù)刪除

2018-09-21 11:34:42

災(zāi)備

2024-06-07 08:26:10

2022-01-10 21:48:37

刪庫跑路開發(fā)代碼

2020-10-19 13:01:31

刪庫程序員思科

2024-08-30 17:25:23

開發(fā)AI

2017-04-01 18:30:47

MySQL誤刪除數(shù)據(jù)庫
點贊
收藏

51CTO技術(shù)棧公眾號