根据mysql数据库日志恢复删除数据
根據(jù)mysql數(shù)據(jù)庫日志恢復(fù)刪除數(shù)據(jù)
BINLOG就是一個記錄SQL語句的過程，和普通的LOG一樣。不過只是她是二進(jìn)制存儲，普通的是十進(jìn)制存儲罷了。
1、配置文件里要寫的東西：
[mysqld]
log-bin=mysql-bin(名字可以改成自己的，如果不改名字的話，默認(rèn)是以主機(jī)名字命名)
重新啟動MSYQL服務(wù)。
二進(jìn)制文件里面的東西顯示的就是執(zhí)行所有語句的詳細(xì)記錄，當(dāng)然一些語句不被記錄在內(nèi)，要了解詳細(xì)的，見手冊頁。
2、查看自己的BINLOG的名字是什么。
show binlog events;
query result(1 records)
Log_name Pos Event_type Server_id End_log_pos Info
yueliangdao_binglog.000001 4 Format_desc 1 106 Server ver: 5.1.22-rc-community-log, Binlog ver: 4
3、我做了幾次操作后，她就記錄了下來。
又一次 show binlog events 的結(jié)果。
query result(4 records)
Log_name Pos Event_type Server_id End_log_pos Info
yueliangdao_binglog.000001 4 Format_desc 1 106 Server ver: 5.1.22-rc-community-log, Binlog ver: 4
yueliangdao_binglog.000001 106 Intvar 1 134 INSERT_ID=1
yueliangdao_binglog.000001 134 Query 1 254 use test; create table a1(id int not null auto_increment primary key, str varchar(1000)) engine=myisam
yueliangdao_binglog.000001 254 Query 1 330 use test; insert into a1(str) values ('I love you'),('You love me')
yueliangdao_binglog.000001 330 Query 1 485 use test; drop table a1
4、用mysqlbinlog 工具來顯示記錄的二進(jìn)制結(jié)果，然后導(dǎo)入到文本文件，為了以后的恢復(fù)。
詳細(xì)過程如下：
D:\LAMP\MYSQL5\data>mysqlbinlog --start-position=4 --stop-position=106 yueliangd
ao_binglog.000001 > c:\test1.txt
test1.txt的文件內(nèi)容：
/!40019 SET @@session.max_insert_delayed_threads=0/;
/!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0/;
DELIMITER /!/;
at 4
#7122 16:9:18 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.22-rc-community-log created 7122 16:9:18 at startup
Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
ROLLBACK/!/;
DELIMITER ;
End of log file
ROLLBACK / added by mysqlbinlog /;
/!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE/;
第二行的記錄：
D:\LAMP\MYSQL5\data>mysqlbinlog --start-position=106 --stop-position=134 yuelian
gdao_binglog.000001 > c:\test1.txt
test1.txt內(nèi)容如下：
/!40019 SET @@session.max_insert_delayed_threads=0/;
/!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0/;
DELIMITER /!/;
at 106
#7122 16:22:36 server id 1 end_log_pos 134 Intvar
SET INSERT_ID=1/!/;
DELIMITER ;
End of log file
ROLLBACK / added by mysqlbinlog /;
/!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE/;
第三行記錄：
D:\LAMP\MYSQL5\data>mysqlbinlog --start-position=134 --stop-position=254 yuelian
gdao_binglog.000001 > c:\test1.txt
內(nèi)容：
/!40019 SET @@session.max_insert_delayed_threads=0/;
/!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0/;
DELIMITER /!/;
at 134
#7122 16:55:31 server id 1 end_log_pos 254 Query thread_id=1 exec_time=0 error_code=0
use test/!/;
SET TIMESTAMP=1196585731/!/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/!/;
SET @@session.sql_mode=1344274432/!/;
/!\C utf8 //!/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/!/;
create table a1(id int not null auto_increment primary key,
str varchar(1000)) engine=myisam/!/;
DELIMITER ;
End of log file
ROLLBACK / added by mysqlbinlog /;
/!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE/;
/!40019 SET @@session.max_insert_delayed_threads=0/;
第四行的記錄：
D:\LAMP\MYSQL5\data>mysqlbinlog --start-position=254 --stop-position=330 yuelian
gdao_binglog.000001 > c:\test1.txt
/!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0/;
DELIMITER /!/;
at 254
#7122 16:22:36 server id 1 end_log_pos 330 Query thread_id=1 exec_time=0 error_code=0
use test/!/;
SET TIMESTAMP=1196583756/!/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/!/;
SET @@session.sql_mode=1344274432/!/;
/!\C utf8 //!/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/!/;
use test; insert into a1(str) values ('I love you'),('You love me')/!/;
DELIMITER ;
End of log file
ROLLBACK / added by mysqlbinlog /;
/!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE/;
5、查看這些東西是為了恢復(fù)數(shù)據(jù)，而不是為了好玩。所以我們最中還是為了要導(dǎo)入結(jié)果到MYSQL中。
D:\LAMP\MYSQL5\data>mysqlbinlog --start-position=134 --stop-position=330 yuelian
gdao_binglog.000001 | mysql -uroot -p
或者
D:\LAMP\MYSQL5\data>mysqlbinlog --start-position=134 --stop-position=330 yuelian
gdao_binglog.000001 >test1.txt
進(jìn)入MYSQL導(dǎo)入
mysql> source c:\test1.txt
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Database changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Charset changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
6、查看數(shù)據(jù)：
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| a1 |
+----------------+
1 row in set (0.01 sec)
mysql> select * from a1;
+----+-------------+
| id | str |
+----+-------------+
| 1 | I love you |
| 2 | You love me |
+----+-------------+
2 rows in set (0.00 sec)
將一個mysqlbinlog文件導(dǎo)為sql文件
cd cd /usr/local/mysql
./mysqlbinlog /usr/local/mysql/data/mysql-bin.000001 > /opt/001.sql
將mysql-bin.000001日志文件導(dǎo)成001.sql
可以在mysqlbinlog語句中通過--start-date和--stop-date選項指定DATETIME格式的起止時間
./mysqlbinlog --stop-date="2009-04-10 17:41:28" /usr/local/mysql/data/mysql-bin.000002 > /opt/004.sql
將mysql-bin.000002文件中截止到2009-04-10 17:41:28的日志導(dǎo)成004.sql
./mysqlbinlog --start-date="2009-04-10 17:30:05" --stop-date="2009-04-10 17:41:28" /usr/local/mysql/data/mysql-bin.000002 /usr/local/mysql/data/mysql-bin.0000023> /opt/004.sql
----如果有多個binlog文件，中間用空格隔開，打上完全路徑
./mysqlbinlog --start-date="2009-04-10 17:30:05" --stop-date="2009-04-10 17:41:28" /usr/local/mysql/data/mysql-bin.000002 |mysql -u root -p123456
或者 source /opt/004.sql
將mysql-bin.000002日志文件中從2009-04-10 17:30:05到2008-04-10 17:41:28截止的sql語句導(dǎo)入到mysql中
說明：參考本文在真實生產(chǎn)環(huán)境恢復(fù)過因數(shù)據(jù)庫同步而刪除的數(shù)據(jù)庫，建議做運(yùn)維的兄弟們好好看一下。在此保存以備不時之需，十分感謝作者寫出這么妙的文章并且給我很大的幫助！
本文出自 “聆聽未來” 博客，請務(wù)必保留此出處http://kerry.blog.51cto.com/172631/146259
轉(zhuǎn)載于:https://blog.51cto.com/chaochang/2046562
總結(jié)
以上是生活随笔為你收集整理的根据mysql数据库日志恢复删除数据的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 批量给MapGis文件正确的地图参数
- 下一篇： 2017年12月