2014年11月28日星期五

與mysqlbinlog可以點時間恢復例如恢復丟失的MySQL數據

Original post: http://anothermysqldba.blogspot.com/2014/11/recover-lost-mysql-data-with.html

備份...備份...備份...但當然..你還需要監控和測試這些備份通常否則他們可能是毫無價值的。 有你的MySQL binlogs啟用一定可以幫助你在緊急的時候也是如此。 MySQL的binlogs經常在關於MySQL複製引用,一個很好的理由,他們店裡所有的更改數據(查詢或事件的基於行的有一點不同,但這樣的例子)。 在binlogs對服務器性能的影響微乎其微考慮它們所提供的恢復選項的時候。 


[anothermysqldba]> show variables like 'log_bin%'; 
+---------------------------------+--------------------------------------------+ 
| Variable_name | Value | 
+---------------------------------+--------------------------------------------+ 
| log_bin | ON | 
| log_bin_basename | /var/lib/mysql/binlogs/mysql-binlogs | 
| log_bin_index | /var/lib/mysql/binlogs/mysql-binlogs.index | 

show variables like 'binlog_format%'; 
+---------------+-------+ 
| Variable_name | Value | 
+---------------+-------+ 
| binlog_format | MIXED | 
+---------------+-------+ 


因此,這是一個使用mysqlbinlog可以恢復來自一個二進制日誌數據,並將其應用到數據庫只是一個簡單的例子。 

首先,我們需要的東西鬆動。 如果事情是發生在我們的數據庫中,我們需要能夠恢復數據也許它只是一種方式,從某人的失誤中恢復過來。 


CREATE TABLE `table_w_rdata` ( 
`id` int(11) NOT NULL AUTO_INCREMENT, 
`somedata` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
`moredata` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
PRIMARY KEY (`id`) 
) ENGINE=InnoDB; 

我們可以在這裡假裝和假設我們有開發人員/ DBA的未溝通非常好和/或保存其代碼的副本。 


delimiter // 
CREATE PROCEDURE populate_dummydata( IN rowsofdata INT ) 
BEGIN 

SET @A = 3; 
SET @B = 15 - @A; 
SET @C = 16; 
SET @D = 25 - @C; 

WHILE rowsofdata > 0 DO 
INSERT INTO table_w_rdata 
SELECT NULL, SUBSTR(md5(''),FLOOR( @A + (RAND() * @B ))) as somedata, SUBSTR(md5(''),FLOOR( @C + (RAND() * @D ))) AS moredata ; 
SET rowsofdata = rowsofdata - 1; 
END WHILE; 
END// 
delimiter ; 
call populate_dummydata(50); 

> SELECT NOW() \G 
*************************** 1. row *************************** 
NOW(): 2014-11-27 17:32:25 
1 row in set (0.00 sec) 

> SELECT * from table_w_rdata WHERE id > 45; 
+----+----------------------------+------------------+ 
| id | somedata | moredata | 
+----+----------------------------+------------------+ 
| 46 | b204e9800998ecf8427e | 0998ecf8427e | 
| 47 | d98f00b204e9800998ecf8427e | 8ecf8427e | 
| 48 | b204e9800998ecf8427e | 800998ecf8427e | 
| 49 | 98f00b204e9800998ecf8427e | e9800998ecf8427e | 
| 50 | 98f00b204e9800998ecf8427e | 998ecf8427e | 
+----+----------------------------+------------------+ 

同時創建一個過程中它以後在寫別人不正確。 

DROP PROCEDURE IF EXISTS populate_dummydata ; 
delimiter // 
CREATE PROCEDURE populate_dummydata( IN rowsofdata INT ) 
BEGIN 

SET @A = 3; 
SET @B = 15 - @A; 
SET @C = 16; 
SET @D = 25 - @C; 

WHILE rowsofdata > 0 DO 
INSERT INTO table_w_rdata 
SELECT NULL, SUBSTR(md5(''),FLOOR( @C + (RAND() * @A ))) as somedata, SUBSTR(md5(''),FLOOR( @B + (RAND() * @D ))) AS moredata ; 
SET rowsofdata = rowsofdata - 1; 
END WHILE; 
END// 
delimiter ; 

call populate_dummydata(50); 
> SELECT NOW(); SELECT * from table_w_rdata WHERE id > 95; 
+---------------------+ 
| NOW() | 
+---------------------+ 
| 2014-11-27 17:36:28 | 
+---------------------+ 
1 row in set (0.00 sec) 

+-----+-------------------+---------------------+ 
| id | somedata | moredata | 
+-----+-------------------+---------------------+ 
| 96 | 4e9800998ecf8427e | 00998ecf8427e | 
| 97 | 9800998ecf8427e | 800998ecf8427e | 
| 98 | e9800998ecf8427e | 204e9800998ecf8427e | 
| 99 | e9800998ecf8427e | 4e9800998ecf8427e | 
| 100 | 9800998ecf8427e | 04e9800998ecf8427e | 
+-----+-------------------+---------------------+ 


該過程的替換版本不會產生隨機值,如團隊希​​望。 該過程的原創者剛從無奈退出。 那麼怎麼辦? 時間一點點過去有,因為它創建為好。 我們知道數據庫名稱,例程名和一般時間框架在創建時不正確的程序和幸運,我們的bin日誌仍然存在,所以我們可以去得到它。 

我們必須採取一般環顧四周,因為我們只想要一個點時恢復該procedure.We中偶然發現的過程,並在之前和之後的二進制日誌的位置。 


NOW(): 2014-11-27 19:46:17 
# mysqlbinlog --start-datetime=20141127173200 --stop-datetime=20141127173628 --database=anothermysqldba mysql-binlogs.000001 | more 

at 253053 
at 253564 

# mysql anothermysqldba --login-path =local -e "DROP PROCEDURE populate_dummydata"; 
# mysqlbinlog --start-position=253053 --stop-position=253564 --database=anothermysqldba mysql-binlogs.000001 | mysql --login-path =local anothermysqldba 


> SHOW CREATE PROCEDURE populate_dummydata\G 
*************************** 1. row *************************** 
Procedure: populate_dummydata 
sql_mode: NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `populate_dummydata`( IN rowsofdata INT ) 
BEGIN 

SET @A = 3; 
SET @B = 15 - @A; 
SET @C = 16; 
SET @D = 25 - @C; 

WHILE rowsofdata > 0 DO 
INSERT INTO table_w_rdata 
SELECT NULL, SUBSTR(md5(''),FLOOR( @A + (RAND() * @B ))) as somedata, SUBSTR(md5(''),FLOOR( @C + (RAND() * @D ))) AS moredata ; 
SET rowsofdata = rowsofdata - 1; 
END WHILE; 
END 
character_set_client: utf8 
collation_connection: utf8_general_ci 
Database Collation: latin1_swedish_ci 
1 row in set (0.00 sec) 

NOW(): 2014-11-27 19:51:03 
> call populate_dummydata(50); 
> SELECT * from table_w_rdata WHERE id > 145; 
+-----+-----------------------------+------------------+ 
| id | somedata | moredata | 
+-----+-----------------------------+------------------+ 
| 146 | 98f00b204e9800998ecf8427e | 800998ecf8427e | 
| 147 | cd98f00b204e9800998ecf8427e | 800998ecf8427e | 
| 148 | 204e9800998ecf8427e | 98ecf8427e | 
| 149 | d98f00b204e9800998ecf8427e | e9800998ecf8427e | 
| 150 | 204e9800998ecf8427e | 9800998ecf8427e | 
+-----+-----------------------------+------------------+ 


我們通過我們的恢復從二進制日誌程序點時間恢復  
這是一個簡單的例子,但它是你可以用前進的工具的一個例子。 

這就是為什麼binlogs是如此寶貴。 

有用的網址: