mysqlbinlog日志恢復(fù)數(shù)據(jù)
嗨！有段時(shí)間沒有更新了，最近有好多人加我說(shuō)要進(jìn)群的，由于當(dāng)時(shí)建群沒人進(jìn)，再加上最近一段時(shí)間比較忙，就無(wú)奈先解散了哈哈哈，抱歉了各位！
ok，回歸正文
恢復(fù)數(shù)據(jù)的重要命令如下
flush logs;默認(rèn)的日志是mysql-bin.000001
現(xiàn)在刷新了重新開啟一個(gè)就多了一個(gè)mysql-bin.000002
./mysqlbinlog --no-defaults binlog日志名，來(lái)查看日志
[root bin]# ./mysqlbinlog --no-defaults ../var/mysql-bin.000001 | more //查看bin-log日志的內(nèi)容[root?bin]#?./mysqlbinlog?--no-defaults?../var/mysql-bin.000001?|?./mysql?-uroot?-p?//恢復(fù)mysql-bin.000001日志的內(nèi)容
如果需要從某個(gè)點(diǎn)恢復(fù)到某個(gè)點(diǎn)，用以下操作
定位：--start-position 開始點(diǎn)
--stop-position 結(jié)束點(diǎn)--start-date 開始時(shí)間--stop-date 結(jié)束時(shí)間
現(xiàn)在恢復(fù)mysql-bin.000002恢復(fù)，從134點(diǎn)開始到386結(jié)束?
[]mysqlbinlog日志恢復(fù)數(shù)據(jù)實(shí)驗(yàn)
//查看一下var下面的內(nèi)容，現(xiàn)在是沒有mysql-log.000001類似的binlog日志的
[root@localhost var]# lsbrocms ibdata1 ib_logfile1 localhost.pid mysql-bin.indexbrotherblog ib_logfile0 localhost.err mysql test
[root@localhost var]# ../bin/mysql -uroot -p //登錄數(shù)據(jù)庫(kù)mysql> use test; //使用test數(shù)據(jù)庫(kù)mysql> flush logs; //刷新binlog日志，新開一個(gè)，現(xiàn)在會(huì)在var目錄下面生成一個(gè)mysql-bin.000001的文件，以下的操作都會(huì)記錄其中
//創(chuàng)建一個(gè)表
mysql> create table user(-> id int auto_increment primary key,-> username char(30),-> password char(32))-> engine=myisam default charset=utf8;
//插入幾條測(cè)試數(shù)據(jù)
mysql> insert into user(username,password) values(1,2);mysql> insert into user(username,password) values(1,2);mysql> insert into user(username,password) values(1,2);
新開一個(gè)binlog日志，現(xiàn)在會(huì)生成一個(gè)名為mysql-bin.000002的文件,下面的操作會(huì)記錄在mysql-bin.000002的文件中
flush logs;查詢一下內(nèi)容
mysql> select * from user;+----+----------+----------+| id | username | password |+----+----------+----------+| 1 | 1 | 2 || 2 | 1 | 2 || 3 | 1 | 2 |+----+----------+----------+
現(xiàn)在將數(shù)據(jù)刪除
mysql> delete from user;將表刪除
drop table user;查看表里面的內(nèi)容
select * from user;\q
[root var]# lsbrocms ibdata1 ib_logfile1 localhost.pid mysql-bin.000001 mysql-bin.indexbrotherblog ib_logfile0 localhost.err mysql mysql-bin.000002 test[root var]# ../bin/mysqlbinlog --no-defaults mysql-bin.000001 | more //查看mysql-bin.000001里面的內(nèi)容[root var]# ../bin/mysqlbinlog --no-defaults mysql-bin.000002 | more //查看mysql-bin.000002里面的內(nèi)容[root var]# ../bin/mysqlbinlog --no-defaults mysql-bin.000001 | ../bin/mysql -uroot -p //用mysql-bin.000001來(lái)恢復(fù)數(shù)據(jù)Enter password:[root var]# ../bin/mysql -uroot -p //進(jìn)數(shù)據(jù)庫(kù)查看
數(shù)據(jù)庫(kù)操作
mysql> use test;mysql> show tables;+----------------+| Tables_in_test |+----------------+| user |+----------------+1 row in set (0.00 sec)mysql> select * from user; //查看數(shù)據(jù)，數(shù)據(jù)回來(lái)了+----+----------+----------+| id | username | password |+----+----------+----------+| 1 | 1 | 2 || 2 | 1 | 2 || 3 | 1 | 2 |+----+----------+----------+3 rows in set (0.00 sec)mysql> \q
如果需要從某個(gè)點(diǎn)恢復(fù)到某個(gè)點(diǎn)，用以下操作
定位：?
--start-position 開始點(diǎn)
--stop-position 結(jié)束點(diǎn)
--start-date 開始時(shí)間
--stop-date? 結(jié)束時(shí)間
?
現(xiàn)在恢復(fù)mysql-bin.000002恢復(fù)，從134點(diǎn)開始到386結(jié)束?
[]