1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
|
[root@test4 Desktop]# cat /tmp/test.sql
-- MySQL dump
10.13
Distrib
5.1
.
70
,
for
unknown-linux-gnu (x86_64)
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version
5.1
.
70
-log
/*!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 */
;
--
-- Position to start replication or point-
in
-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE=
'mysqlbin.000166'
, MASTER_LOG_POS=
798
;
--
-- Current Database: `test`
--
CREATE DATABASE
/*!32312 IF NOT EXISTS*/
`test`
/*!40100 DEFAULT CHARACTER SET utf8 */
;
USE `test`;
--
-- Table structure
for
table `tt`
--
/*!40101 SET @saved_cs_client = @@character_set_client */
;
/*!40101 SET character_set_client = utf8 */
;
CREATE TABLE `tt` (
`id`
int
(
11
) DEFAULT NULL,
`name`
var
char(
12
) DEFAULT NULL
);
/*!40101 SET character_set_client = @saved_cs_client */
;
--
-- Dumping data
for
table `tt`
--
INSERT INTO `tt` VALUES (
1
,
'zz'
);
INSERT INTO `tt` VALUES (
2
,
'yy'
);
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */
;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */
;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */
;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */
;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */
;
-- Dump completed on
2013
-
09
-
05
20
:
15
:
38
|
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql> create database test1;
Query OK,
1
row affected (
0.04
sec)
mysql>
use
test1
Database changed
mysql> create table tt(id
int
,name
var
char(
100
),msg
var
char(
200
)) engine=myisam;
Query OK,
0
rows affected (
0.18
sec)
mysql> insert into tt values(
1
,
'chenzhongyang'
,
'how are you'
);
Query OK,
1
row affected (
0.00
sec)
mysql> insert into tt values(
2
,
'tianhongyan'
,
'BMW'
);
Query OK,
1
row affected (
0.00
sec)
mysql> insert into tt values(
3
,
'jisuanji'
,
'why'
);
Query OK,
1
row affected (
0.00
sec)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
|
[root@test4 ~]# mysqlbinlog
'/tmp/mysqlbin.000161'
。。。。。。。。。。。。。。。。。。。
# at
588
#
130905
22
:
26
:
42
server id
1
end_log_pos
658
Query thread_id=
7
exec_time=
0
error_code=
0
SET TIMESTAMP=
1378391202
/*!*/
;
COMMIT
/*!*/
;
# at
658
#
130905
22
:
27
:
15
server id
1
end_log_pos
727
Query thread_id=
7
exec_time=
0
error_code=
0
SET TIMESTAMP=
1378391235
/*!*/
;
BEGIN
/*!*/
;
# at
727
# at
775
#
130905
22
:
27
:
15
server id
1
end_log_pos
775
Table_map: `test1`.`tt` mapped to number
21
#
130905
22
:
27
:
15
server id
1
end_log_pos
827
Write_rows: table id
21
flags: STMT_END_F
BINLOG '
w5QoUhMBAAAAMAAAAAcDAAAAABUAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIH
w5QoUhcBAAAANAAAADsDAAAAABUAAAAAAAEAA
//4AgAAAAsAdGlhbmhvbmd5YW4DAEJNVw==
'
/*!*/
;
# at
827
#
130905
22
:
27
:
15
server id
1
end_log_pos
897
Query thread_id=
7
exec_time=
0
error_code=
0
SET TIMESTAMP=
1378391235
/*!*/
;
COMMIT
/*!*/
;
# at
897
#
130905
22
:
27
:
56
server id
1
end_log_pos
966
Query thread_id=
7
exec_time=
0
error_code=
0
SET TIMESTAMP=
1378391276
/*!*/
;
BEGIN
/*!*/
;
# at
966
# at
1014
#
130905
22
:
27
:
56
server id
1
end_log_pos
1014
Table_map: `test1`.`tt` mapped to number
21
#
130905
22
:
27
:
56
server id
1
end_log_pos
1063
Write_rows: table id
21
flags: STMT_END_F
BINLOG '
7JQoUhMBAAAAMAAAAPYDAAAAABUAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIH
7JQoUhcBAAAAMQAAACcEAAAAABUAAAAAAAEAA
//4AwAAAAgAamlzdWFuamkDAHdoeQ==
'
/*!*/
;
# at
1063
#
130905
22
:
27
:
56
server id
1
end_log_pos
1133
Query thread_id=
7
exec_time=
0
error_code=
0
SET TIMESTAMP=
1378391276
/*!*/
;
COMMIT
/*!*/
;
DELIMITER ;
# End of log file
ROLLBACK
/* added by mysqlbinlog */
;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/
;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
|
[root@test4 ~]# cat /tmp/test1.sql
-- MySQL dump
10.13
Distrib
5.1
.
70
,
for
unknown-linux-gnu (x86_64)
--
-- Host: localhost Database: test1
-- ------------------------------------------------------
-- Server version
5.1
.
70
-log
/*!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 */
;
--
-- Position to start replication or point-
in
-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE=
'mysqlbin.000161'
, MASTER_LOG_POS=
1133
;
--
-- Current Database: `test1`
--
CREATE DATABASE
/*!32312 IF NOT EXISTS*/
`test1`
/*!40100 DEFAULT CHARACTER SET utf8 */
;
USE `test1`;
--
-- Table structure
for
table `tt`
--
/*!40101 SET @saved_cs_client = @@character_set_client */
;
/*!40101 SET character_set_client = utf8 */
;
CREATE TABLE `tt` (
`id`
int
(
11
) DEFAULT NULL,
`name`
var
char(
100
) DEFAULT NULL,
`msg`
var
char(
200
) DEFAULT NULL
);
/*!40101 SET character_set_client = @saved_cs_client */
;
--
-- Dumping data
for
table `tt`
--
INSERT INTO `tt` VALUES (
1
,
'chenzhongyang'
,
'how are you'
);
INSERT INTO `tt` VALUES (
2
,
'tianhongyan'
,
'BMW'
);
INSERT INTO `tt` VALUES (
3
,
'jisuanji'
,
'why'
);
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */
;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */
;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */
;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */
;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */
;
-- Dump completed on
2013
-
09
-
05
22
:
48
:
50
|
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql> show binlog events
in
'mysqlbin.000161'
-> ;
| mysqlbin.
000161
|
1250
| Write_rows |
1
|
1301
| table_id:
22
flags: STMT_END_F |
| mysqlbin.
000161
|
1301
| Query |
1
|
1371
| COMMIT |
| mysqlbin.
000161
|
1371
| Query |
1
|
1440
| BEGIN |
| mysqlbin.
000161
|
1440
| Table_map |
1
|
1488
| table_id:
22
(test1.tt) |
| mysqlbin.
000161
|
1488
| Write_rows |
1
|
1552
| table_id:
22
flags: STMT_END_F |
| mysqlbin.
000161
|
1552
| Query |
1
|
1622
| COMMIT |
| mysqlbin.
000161
|
1622
| Query |
1
|
1699
|
use
`test1`; drop table tt |
| mysqlbin.
000161
|
1699
| Rotate |
1
|
1741
| mysqlbin.
000162
;pos=
4
|
+-----------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------+
26
rows
in
set
(
0.00
sec)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
|
[root@test4 ~]# mysqlbinlog -p123456 --start-position=
1133
--stop-position=
1622
-vv /tmp/mysqlbin.
000161
/*!40019 SET @@session.max_insert_delayed_threads=0*/
;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/
;
DELIMITER
/*!*/
;
# at
4
#
130905
21
:
02
:
49
server id
1
end_log_pos
106
Start: binlog v
4
, server v
5.1
.
70
-log created
130905
21
:
02
:
49
at startup
ROLLBACK
/*!*/
;
BINLOG '
+YAoUg8BAAAAZgAAAGoAAAAAAAQANS4xLjcwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAD5gChSEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'
/*!*/
;
# at
1133
#
130905
23
:
06
:
50
server id
1
end_log_pos
1202
Query thread_id=
7
exec_time=
0
error_code=
0
SET TIMESTAMP=
1378393610
/*!*/
;
SET @@session.pseudo_thread_id=
7
/*!*/
;
SET @@session.foreign_key_checks=
1
, @@session.sql_auto_is_null=
1
, @@session.unique_checks=
1
, @@session.autocommit=
1
/*!*/
;
SET @@session.sql_mode=
0
/*!*/
;
SET @@session.auto_increment_increment=
1
, @@session.auto_increment_offset=
1
/*!*/
;
/*!\C utf8 */
/*!*/
;
SET @@session.character_set_client=
33
,@@session.collation_connection=
33
,@@session.collation_server=
33
/*!*/
;
SET @@session.lc_time_names=
0
/*!*/
;
SET @@session.collation_database=DEFAULT
/*!*/
;
BEGIN
/*!*/
;
# at
1202
# at
1250
#
130905
23
:
06
:
50
server id
1
end_log_pos
1250
Table_map: `test1`.`tt` mapped to number
22
#
130905
23
:
06
:
50
server id
1
end_log_pos
1301
Write_rows: table id
22
flags: STMT_END_F
BINLOG '
Cp4oUhMBAAAAMAAAAOIEAAAAABYAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIH
Cp4oUhcBAAAAMwAAABUFAAAAABYAAAAAAAEAA
//4BAAAAAkAc2h1aWp1bnlpBABib3Nz
'
/*!*/
;
### INSERT INTO `test1`.`tt`
### SET
### @
1
=
4
/* INT meta=0 nullable=1 is_null=0 */
### @
2
=
'shuijunyi'
/* VARSTRING(300) meta=300 nullable=1 is_null=0 */
### @
3
=
'boss'
/* VARSTRING(600) meta=600 nullable=1 is_null=0 */
# at
1301
#
130905
23
:
06
:
50
server id
1
end_log_pos
1371
Query thread_id=
7
exec_time=
0
error_code=
0
SET TIMESTAMP=
1378393610
/*!*/
;
COMMIT
/*!*/
;
# at
1371
#
130905
23
:
07
:
39
server id
1
end_log_pos
1440
Query thread_id=
7
exec_time=
0
error_code=
0
SET TIMESTAMP=
1378393659
/*!*/
;
BEGIN
/*!*/
;
# at
1440
# at
1488
#
130905
23
:
07
:
39
server id
1
end_log_pos
1488
Table_map: `test1`.`tt` mapped to number
22
#
130905
23
:
07
:
39
server id
1
end_log_pos
1552
Write_rows: table id
22
flags: STMT_END_F
BINLOG '
O54oUhMBAAAAMAAAANAFAAAAABYAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIH
O54oUhcBAAAAQAAAABAGAAAAABYAAAAAAAEAA
//4BQAAAAYAemh1anVuFABtYXkgSSBrbm93IHlv
dXIgbmFtZQ==
'
/*!*/
;
### INSERT INTO `test1`.`tt`
### SET
### @
1
=
5
/* INT meta=0 nullable=1 is_null=0 */
### @
2
=
'zhujun'
/* VARSTRING(300) meta=300 nullable=1 is_null=0 */
### @
3
=
'may I know your name'
/* VARSTRING(600) meta=600 nullable=1 is_null=0 */
# at
1552
#
130905
23
:
07
:
39
server id
1
end_log_pos
1622
Query thread_id=
7
exec_time=
0
error_code=
0
SET TIMESTAMP=
1378393659
/*!*/
;
COMMIT
/*!*/
;
DELIMITER ;
# End of log file
ROLLBACK
/* added by mysqlbinlog */
;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/
;
|