1.config binlog
###########################################
#add by xinsj on 20090522 for bin log
log-bin="D:/mysql/log/binlog"
#add by xinsj on 20091020 for binlog config
#binlog-do-db=test
#binlog-ignore-db=slash
2.reset binlog
mysql> reset master;
3.start test
##flush logs --indicate creating new binlog
mysql> show create table s;
+-------+-------------------------------------------------------
----------------------------------------------------------------
| Table | Create Table
+-------+-------------------------------------------------------
----------------------------------------------------------------
| s | CREATE TABLE `s` (
`f` int(11) NOT NULL DEFAULT '0',
`a` int(4) DEFAULT NULL,
PRIMARY KEY (`f`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------
----------------------------------------------------------------
1 row in set (0.02 sec)
mysql> insert into s values(1,11),(2,22);
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> flush logs;
Query OK, 0 rows affected (0.08 sec)
mysql> insert into s values(3,33),(4,44);
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> flush logs;
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> insert into s values(5,55),(6,66),(7,77);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from s;
+---+------+
| f | a |
+---+------+
| 1 | 11 |
| 2 | 22 |
| 3 | 33 |
| 4 | 44 |
| 5 | 55 |
| 6 | 66 |
| 7 | 77 |
+---+------+
3 rows in set (0.00 sec)
##now we delete some records
mysql>
mysql> delete from s where f>3;
Query OK, 4 rows affected (0.03 sec)
mysql> select * from s;
+---+------+
| f | a |
+---+------+
| 1 | 11 |
| 2 | 22 |
| 3 | 33 |
+---+------+
3 rows in set (0.00 sec)
##ok,let's recover data from binlog
D:\mysql\log çç®å½
2009-05-22 14:40
2009-05-22 14:40
2009-10-22 10:31 81 binlog.index
2009-10-22 10:30 269 binlog.000001
2009-10-22 10:31 269 binlog.000002
2009-10-22 10:31 350 binlog.000003
4 个æ件 969 åè
2 个ç®å½ 5,151,227,904 å¯ç¨åè
#we know that binlog contain all the data we save into the db
#
#binlog.000001(1,11),(2,22);
#binlog.000002(3,33),(4,44);
#binlog.000003(5,55),(6,66),(7,77);
#
#so we use binlog named binlog.000002 and binlog.000003 to recover data
#we should parse binlog first
D:\mysql\log>mysqlbinlog binlog.000001 > 1.txt
D:\mysql\log>mysqlbinlog binlog.000002 > 2.txt
D:\mysql\log>mysqlbinlog binlog.000003 > 3.txt
D:\mysql\log>dir
D:\mysql\log çç®å½
2009-05-22 14:40
2009-05-22 14:40
2009-10-22 10:31 81 binlog.index
2009-10-22 10:30 269 binlog.000001
2009-10-22 10:31 269 binlog.000002
2009-10-22 10:31 350 binlog.000003
2009-10-22 10:43 1,061 1.txt
2009-10-22 10:43 1,034 2.txt
2009-10-22 10:43 1,287 3.txt
7 个æ件 4,351 åè
2 个ç®å½ 5,151,178,752 å¯ç¨åè
#we analyse binlog.000002 then because of it containing part of data we want to recover
D:\mysql\log>cat 2.txt
'cat' ä¸æ¯å é¨æå¤é¨å½ä»¤ï¼ä¹ä¸æ¯å¯è¿è¡çç¨åº
ææ¹å¤çæ件ã
D:\mysql\log>more 2.txt
;
;
DELIMITER ;
# at 4
#091022 10:30:19 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.
23-rc-community-log created 091022 10:30:19
# at 106
#091022 10:30:37 server id 1 end_log_pos 202 Query thread_id=1 exec_tim
e=0 error_code=0
use test;
SET TIMESTAMP=1256178637;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.uniq
ue_checks=1;
SET @@session.sql_mode=1344274432;
;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.
collation_server=8;
insert into s values(3,33),(4,44);
# at 202
#091022 10:30:37 server id 1 end_log_pos 229 Xid = 17
COMMIT;
# at 229
#091022 10:31:13 server id 1 end_log_pos 269 Rotate to binlog.000003 pos: 4
DELIMITER ;
# End of log file
ROLLBACK ;
;
D:\mysql\log>
#yes,we find [from 106] ,the position is right,the the sql is not proper
#may it is my false :(
#insert into s values(3,33),(4,44);
#ok,we can remove the data 3,33ï¼then recover
mysql> delete from s where f = 3;
Query OK, 1 row affected (0.17 sec)
#god!the binlog.000003 contain the sql rigth now
D:\mysql\log>mysqlbinlog binlog.000003 > 4.txt
D:\mysql\log>more 4.txt
;
;
DELIMITER ;
# at 4
#091022 10:31:13 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.
23-rc-community-log created 091022 10:31:13
# Warning: this binlog was not closed properly. Most probably mysqld crashed wri
ting it.
# at 106
#091022 10:31:30 server id 1 end_log_pos 209 Query thread_id=1 exec_tim
e=0 error_code=0
use test;
SET TIMESTAMP=1256178690;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.uniq
ue_checks=1;
SET @@session.sql_mode=1344274432;
;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.
collation_server=8;
insert into s values(5,55),(6,66),(7,77);
# at 209
#091022 10:31:30 server id 1 end_log_pos 236 Xid = 19
COMMIT;
# at 236
#091022 10:34:38 server id 1 end_log_pos 323 Query thread_id=1 exec_tim
e=0 error_code=0
SET TIMESTAMP=1256178878;
delete from s where f>3;
# at 323
#091022 10:34:38 server id 1 end_log_pos 350 Xid = 20
COMMIT;
# at 350
#091022 10:57:04 server id 1 end_log_pos 438 Query thread_id=1 exec_tim
e=0 error_code=0
SET TIMESTAMP=1256180224;
delete from s where f = 3;
# at 438
#091022 10:57:04 server id 1 end_log_pos 465 Xid = 22
COMMIT;
DELIMITER ;
# End of log file
ROLLBACK ;
;
#now we start recover
D:\mysql\log>mysqlbinlog binlog.000002 | mysql -uxsj -p test
Enter password: ***
D:\mysql\log>mysqlbinlog binlog.000003 --start-pos=106 --stop-pos=236 | mysql -u
xsj -p test
Enter password: ***
#check the data
mysql> select * from s;
+---+------+
| f | a |
+---+------+
| 1 | 11 |
| 2 | 22 |
| 3 | 33 |
| 4 | 44 |
| 5 | 55 |
| 6 | 66 |
| 7 | 77 |
+---+------+
7 rows in set (0.00 sec)
#it is great!over :)
å享å°ï¼
2009-10-22 11:21
æµè§ 1603
è¯è®º