mysql刪庫不偷跑

2024-07-24 12:31:43

字體：大中小

來源：轉載

供稿：網友

一個奢侈品電商網站的程序員手抖，把阿里云rds的某個庫給刪了，當時都11點多了，心中一萬個羊駝在奔騰，還好，沒讓我出手，小弟就把它給恢復了。昨天上午，又一個網站的程序員手抖，給一個還要用的庫給刪了。這也不能全怪他，本來計劃遷移數據的，因為數據庫太大，進行分拆，把大庫變小。遷移走了一些，就要刪掉遷走的，這樣以利于后邊遷移加快速度。

也許有人說，這是管理上的問題，的確如此。應該加強權限管理，并制度相應的技術保障措施及流程規范。有相應的保障或者措施，雖然不能完全杜絕誤操作，但起碼會大大降低概率。我為啥經常遇到這種事情，來回折騰呢？主要是這些年，我做的是外圍支援，以技術層面為主，不參與更多的事務。當然，對于大多數規模有限的公司來說，完善流程制度也難于落實。因此，雖然遇到不少麻煩，總結一下寫出來，應該對其他人有所幫助。

恢復的第一要務，是找備份。還好，有一個2018年6月9日的備份存在。本來一直都在自動備份的，后來由于要遷移，而且據說近期夜間備份時，非常占用資源，負載老高，到上午上班時，備份壓縮那一步還沒有結束，就把備份給停了。

這是遷移部分數據后，還刪掉遷走庫以后的容量，確實有點大。像這種情況，要么分拆，要么做增量備份，把自動備份停了的做法，風險實在是大。

為了降低風險，在恢復前最好做一次全庫備份，萬一搞出問題，還能回得去。基于這個想法，開始執行備份。執行了兩個小時，還沒見動靜，也無法評估大概要多長時間，其它人等不及了，同時備份也影響其它業務的正常運行，因此只好直接停止備份。

開干前先算一卦，測一下吉兇。得詬之坎卦，三爻、四爻、上爻動，好在用神旺相，有修復的希望。導入數據步驟如下：

◆備份文件解包，并打開文件看一眼內容

[root@db-209 mysql_bk_dir]# pwd

/data/databk/mysql_bk_dir

[root@db-209 mysql_bk_dir]# tar zxvf mobile7_quanzhen20180609.tgz

mobile7_quanzhen20180609.sql

[root@db-209 mysql_bk_dir]# more mobile7_quanzhen20180609.sql

-- MySQL dump 10.13 Distrib 5.5.29, for Linux (x86_64)

--

-- Host: localhost Database: mobile7_quanzhen

-- ------------------------------------------------------

-- Server version 5.5.29-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--

-- Table structure for table `quanzhen_aboutegg`

--

DROP TABLE IF EXISTS `quanzhen_aboutegg`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `quanzhen_aboutegg` (

`id` int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',

`uid` mediumint(8) unsigned NOT NULL COMMENT '會員ID',

`content` text NOT NULL COMMENT '內容',

`title` varchar(100) NOT NULL COMMENT '標題',

`sort` int(5) unsigned NOT NULL COMMENT '排序',

`status` tinyint(1) unsigned NOT NULL COMMENT '0不顯示，1顯示',

`updated` int(8) NOT NULL COMMENT '修改時間',

`created` int(8) NOT NULL COMMENT '增加時間',

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='關于金蛋系統說明表';

/*!40101 SET character_set_client = @saved_cs_client */;

……………………………省略若干……………………………………….

◆創建數據庫并導入數據

mysql> create database mobile7_quanzhen;

mysql> use mobile7_quanzhen;

mysql> source /data/databk/mysql_bk_dir/mobile7_quanzhen20180609.sql;

上述操作很順利，基本沒啥障礙。

重點部分來了，需要確定恢復到什么位置。這些信息從哪里得到呢？數據庫存儲目錄里邊的二進制日志。

[root@db-209 mysql_db]# pwd

/data/mysql_db

[root@db-209 mysql_db]# ls -al |grep mysql-bin

-rw-rw---- 1 mysql mysql 1073744253 Jun 17 01:20 mysql-bin.000856

-rw-rw---- 1 mysql mysql 106606897 Jun 17 04:02 mysql-bin.000857

-rw-rw---- 1 mysql mysql 1073742155 Jun 18 02:22 mysql-bin.000858

-rw-rw---- 1 mysql mysql 58702046 Jun 18 04:02 mysql-bin.000859

-rw-rw---- 1 mysql mysql 1073745791 Jun 19 03:20 mysql-bin.000860

-rw-rw---- 1 mysql mysql 24180628 Jun 19 04:02 mysql-bin.000861

-rw-rw---- 1 mysql mysql 1073741973 Jun 20 00:31 mysql-bin.000862

-rw-rw---- 1 mysql mysql 172478749 Jun 20 04:02 mysql-bin.000863

-rw-rw---- 1 mysql mysql 1073741972 Jun 20 23:11 mysql-bin.000864

-rw-rw---- 1 mysql mysql 172554351 Jun 21 04:02 mysql-bin.000865

-rw-rw---- 1 mysql mysql 150489 Jun 21 04:02 mysql-bin.000866

-rw-rw---- 1 mysql mysql 747165143 Jun 21 16:11 mysql-bin.000867

-rw-rw---- 1 mysql mysql 228 Jun 21 04:02 mysql-bin.index

從輸出可知，二進制文件并不多，最舊的一個的時間戳是6月17號，10號到16號的丟失了。只保留最近5天的日志，這個是由mysql選項文件/etc/my.cnf設定的，其內容為：

[root@db-209 mysql_db]# more /etc/my.cnf

……………………………………….省略若干…………………………

[mysqld]

port = 3306

socket = /tmp/mysql.sock

datadir = /data/mysql_db

#log_bin_trust_function_creators = 1

skip-external-locking

skip-name-resolve

wait_timeout=10

interactive_timeout=300

connect_timeout=300

max_connections = 2000

max_connect_errors = 50

tmp_table_size = 128M

expire_logs_days = 5

slave-skip-errors = 1690,1062

# Try number of CPU's*2 for thread_concurrency

thread_concurrency = 8

slow_query_log = 1

slow_query_log_file=/data/mysql_db/slow.log

………………………………省略若干……………………………………………….

雖然是五天的數據，但文件卻是好幾個。為啥不設置保留天數多一些呢?還不是因為這個機器太老，資源有限。把風險告知其他讓你，申明可能有幾天的的數據會丟失。大家一致認同，能恢復多少算多少，總比沒有強。

詢問了執行誤操作的程序員，大概是哪個時間執行的drop操作？回答說是6月20號上午10點過幾分。有了這個參照點，再查看二進制日志文件的時間戳，就可定位到文件“mysql-bin.000864”，確定這個文件的原因是文件“mysql-bin.000863”的最后寫入時間是“6月20日4:02”,這樣一來范圍就縮小很多。當然，也可以不管這些，用通配符，搜索所有二進制日志文件，確定恢復點。我決定以時間點來進行恢復，具體的操作如下：

◆確定恢復的時間點

[root@db-209 mysql_db]# pwd

/data/mysql_db

[root@db-209 mysql_db]# /usr/local/mysql/bin/mysqlbinlog -v --base64-output=DECODE-ROWS mysql-bin.000864|grep -C 10 -i drop

INSERT INTO `quanzhen_everydaycpdcontrol` (todaytime,uid,todaynum) VALUES (1529424000,661526,3)

/*!*/;

# at 393124773

#180620 10:08:44 server id 21 end_log_pos 393124800 Xid = 629128212

COMMIT/*!*/;

# at 393124800

#180620 10:08:44 server id 21 end_log_pos 393124901 Query thread_id=85127174 exec_time=0 error_code=0

SET TIMESTAMP=1529460524/*!*/;

/*!/C latin1 *//*!*/;

SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=33/*!*/;

drop database mobile7_quanzhen

/*!*/;

# at 393124901

#180620 10:08:44 server id 21 end_log_pos 393124989 Query thread_id=85160078 exec_time=0 error_code=0

SET TIMESTAMP=1529460524/*!*/;

/*!/C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;

BEGIN

/*!*/;

# at 393124989

#180620 10:08:44 server id 21 end_log_pos 393125017 Intvar

--

INSERT INTO `quanzhen_everydaycpdcontrol` (todaytime,uid,todaynum) VALUES (1529424000,399677,4)

/*!*/;

# at 399014378

#180620 10:13:27 server id 21 end_log_pos 399014405 Xid = 629434799

COMMIT/*!*/;

………………………………………………………..省略若干…………………………………………

現在得到確切刪庫的時間是2018年6月20日上午10:08:44，恢復時間就以此為準，稍微往前移一點也是合理的。

這里有一個小插曲，最開始不加選項，簡化執行“mysqlbinlog mysql-bin.000864”只有寥寥幾行輸出，輸出結尾還有一行報錯信息，如下所示：

ERROR: Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 96, event_type: 19

Could not read entry at offset 820:Error in log format or read error

DELIMITER ;

又試著打開其它幾個二進制日志，結果差不多。加選項執行“mysqlbinlog -v --base64-output=DECODE-ROWS mysql-bin.000864|grep -C 10 -i drop”，提示不支持。

這樣看來，恢復的機會不太大了，嚇得趕緊喝泡菜水壓壓驚。用某度搜一下，知道是版本不一致造成的，find 一下，果然有兩個mysql安裝。這mysql是我幾年前親自部署的，一般都是定制安裝在/usr/local/mysql目錄，數據文件分開。但不知道后邊那個程序員，yum慣了，又給安裝了一個低版本的。后邊我執行帶全路徑，就沒問題，不過我暫時不想告訴他們，以免大家商和氣。

◆數據恢復

還是使用mysqlbinlog指令，加管道“|”把輸出給mysql，具體的操作如下所示：

[root@db-209 ~]#/usr/local/mysql/bin/mysqlbinlog --database=mobile7_quanzhen --stop-datetime='2018-06-20 10:08:40' mysql-bin.000* |mysql mobile7_quanzhen -p

開始操作的時候，mysqlbinlog 沒帶選項“--database=mobile7_quanzhen”，執行后，拋主鍵沖突錯誤信息。后在管道右側mysql這一邊強制強制加選項“-f”，雖然能繼續往下進行，但還是不斷報主鍵沖突的錯誤信息。

后來想，是不是在管道又邊把數據庫名字也指定上？反正這樣搞也沒風險。嘿嘿，加上以后，輸入mysql密碼，還真正常導入了。我猜想，不指定數據庫選項，mysqlbinlog會嘗試對所有的庫進行恢復，而其它庫卻并沒有丟失數據，因此造成主鍵沖突。

有一個有意思的問題，本來在恢復前導入了舊庫，其所關聯的目錄和文件都已經生成，但在執行mysqlbinlog恢復時，該庫所對應的目錄里的文件和子目錄會消失一陣，過一會又會自己回來。恢復繼續進行，可看到數據目錄里的文件時間戳在發生變化，而不需要恢復的數據，其時間戳是固定的。

恢復過的數據，跟最初舊庫導入數據的時間有明顯的差異。恢復完以后，請程序員幫忙核對一下，看表的數量，表的記錄數是否與刪除前一樣活著差不多。經確認，恢復成功，大家不用跑路。

（編輯：武林網）

上一篇：MySQL -mysql索引與存儲引擎

下一篇：mysql-物理配備-Percona xtrabackup