在my.ini 文件中开启二进制日志. log-bin=%xx%/logbin.log
===================================================================
CREATE TABLE x1(
id VARCHAR(20) ,
name VARCHAR(20) ,
age VARCHAR(20)
);INSERT INTO x1(id,name,age ) VALUES('1','tt','99');
INSERT INTO x1(id,name,age ) VALUES('2','xx','88');
INSERT INTO x1(id,name,age ) VALUES('3','ww','77');xx>SELECT id,name,age FROM x1;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | tt | 99 |
| 2 | xx | 88 |
| 3 | ww | 77 |
+------+------+------+
3 rows in set (0.00 sec)
===================================================================
xx>FLUSH LOGS; -- 从新开启一个新的文件记录.生成二进制日志文件名为:logbin.000002INSERT INTO x1(id,name,age ) VALUES('4','qq','66');xx>SELECT id,name,age FROM x1;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | tt | 99 |
| 2 | xx | 88 |
| 3 | ww | 77 |
| 4 | qq | 66 |
+------+------+------+
4 rows in set (0.02 sec)
xx>FLUSH LOGS;
====================================================================其实,我想表达的意思是这样的.最后一个flush后,id=4 的一条信息进入了日志文件logbin.000002里面然后,我想恢复到刚开始的状态.
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | tt | 99 |
| 2 | xx | 88 |
| 3 | ww | 77 |
+------+------+------+====================================================================请问,我用mysqlbinlog 语法怎么样才能做到.
===================================================================
CREATE TABLE x1(
id VARCHAR(20) ,
name VARCHAR(20) ,
age VARCHAR(20)
);INSERT INTO x1(id,name,age ) VALUES('1','tt','99');
INSERT INTO x1(id,name,age ) VALUES('2','xx','88');
INSERT INTO x1(id,name,age ) VALUES('3','ww','77');xx>SELECT id,name,age FROM x1;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | tt | 99 |
| 2 | xx | 88 |
| 3 | ww | 77 |
+------+------+------+
3 rows in set (0.00 sec)
===================================================================
xx>FLUSH LOGS; -- 从新开启一个新的文件记录.生成二进制日志文件名为:logbin.000002INSERT INTO x1(id,name,age ) VALUES('4','qq','66');xx>SELECT id,name,age FROM x1;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | tt | 99 |
| 2 | xx | 88 |
| 3 | ww | 77 |
| 4 | qq | 66 |
+------+------+------+
4 rows in set (0.02 sec)
xx>FLUSH LOGS;
====================================================================其实,我想表达的意思是这样的.最后一个flush后,id=4 的一条信息进入了日志文件logbin.000002里面然后,我想恢复到刚开始的状态.
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | tt | 99 |
| 2 | xx | 88 |
| 3 | ww | 77 |
+------+------+------+====================================================================请问,我用mysqlbinlog 语法怎么样才能做到.
你的操作方法有几种
1:先删除全部数据,然后用第一个日志文件进行恢复,这样就只有1,2,3,共3条记录。操作方法如下mysql> delete from x1;
Query OK, 4 rows affected (0.22 sec)mysql>2:用MYSQLBINLOG导出第一个日志文件的内容,如下:D:\mysql6\bin>mysqlbinlog d:\mysql6\data\mysql.000017 --start-position=333 --stop-position=884 -r 12.sql这里指定了POS的起始位置和终止位置,是因为在第一个日志文件里记录了CREATE TABLE命令,而这个命令是不需要执行的,我们只要执行INSERT语句就可以了。具体的POS位置,你可以打开日志文件,找适当的位置。3:到BIN目录下找到11.SQL,查看里面,有3条INSERT语句。导入到数据库内,如下:D:\mysql6\bin>mysql -uroot -p123 cpc<d:\mysql6\bin\12.sqlD:\mysql6\bin>mysql -uroot -p123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.45-community-log MySQL Community Server (GPL)Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use cpc;
Database changed
mysql> select * from x1;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | tt | 99 |
| 2 | xx | 88 |
| 3 | ww | 77 |
+------+------+------+
3 rows in set (0.00 sec)mysql>