MySQL binlog基本用法

本文只是简单的介绍mysql binlog基本用法，并不涉及到binlog的原理、格式等知识，如果需要了解这些高级的知识，请参见官方文档。
本文重点介绍--start-position和--stop-position参数的使用 
--start-position的语法是

1	--start-position=N

含义是从相对与二进制日志的第N偏移的事件开始读。 同理，--stop-position=N的介绍和--start-position类似。在默认的情况下， log-bin是关闭的，如下：

1	mysql> show variables like 'log_bin';

2	+---------------+-------+

3	| Variable_name | Value |

4	+---------------+-------+

5	| log_bin       | OFF   |

6	+---------------+-------+

7	1 row in set (0.00 sec)

我们可以通过修改my.ini配置文件，在[mysqld] 下面添加 log-bin=日志名：

1

[mysqld]

2

3	# The TCP/IP Port the MySQL Server will listen on

4

port=3306

5

6	log-bin=mysql-bin

修改完成之后，我们需要重启mysql服务，然后再看下是否启动了binlog

1	mysql> show variables like 'log_bin';

2	+---------------+-------+

3	| Variable_name | Value |

4	+---------------+-------+

5	| log_bin       | ON    |

6	+---------------+-------+

7	1 row in set (1.01 sec)

已经开启了binlog。然后我们创建一个数据库binlog

1	mysql> create database binlog;

2	Query OK, 1 row affected (0.00 sec)

3

4	mysql> use binlog;

5	Database changed

然后在binlog数据库下面创建表test，并依次进行如下操作。 

01	mysql> create table test(

02	id int auto_increment not null primary key,

03

val int,

04	data varchar(20)

05

);

06	Query OK, 0 rows affected (0.01 sec)

07

08	mysql> insert into test(val, data) values (10, 'wu');

09	Query OK, 1 row affected (0.02 sec)

10

11	mysql> insert into test(val, data) values (20, 'yang');

12	Query OK, 1 row affected (0.01 sec)

13

14	mysql> insert into test(val, data) values (20, 'ping');

15	Query OK, 1 row affected (0.01 sec)

16

17	mysql> flush logs;

18	Query OK, 0 rows affected (0.04 sec)

19

20	mysql> insert into test(val, data) values (40, 'hao');

21	Query OK, 1 row affected (0.01 sec)

22

23	mysql> insert into test(val, data) values (50, 'iteblog');

24	Query OK, 1 row affected (0.01 sec)

25

26	mysql> delete from test where id between 4 and 5;

27	Query OK, 2 rows affected (0.01 sec)

28

29	mysql> insert into test(val, data) values (60, 'iteblog1');

30	Query OK, 1 row affected (0.02 sec)

31

32	mysql> flush logs;

33	Query OK, 0 rows affected (0.05 sec)

34

35	mysql> insert into test(val, data) values (70, 'ping123');

36	Query OK, 1 row affected (0.01 sec)

37

38	mysql> insert into test(val, data) values (80, 'ping163');

39	Query OK, 1 row affected (0.01 sec)

40

41	mysql> drop table test;

42	Query OK, 0 rows affected (0.01 sec)

43

44	mysql> drop database binlog;

45	Query OK, 0 rows affected (0.00 sec)

经过上述的操作，将会在本地数据库数据存放目录下面生成以下四个文件：

1	mysql-bin.000001

2	mysql-bin.000002

3	mysql-bin.000003

4	mysql-bin.index

*.index是索引文件，其他三个是binlog文件，我们可以用mysqlbinlog 工具来恢复数据。为了下面讲解的方便，我们先将binlog文件解析成txt文件，如下：

1	mysqlbinlog  data\mysql-bin.000001 > E:/1.txt

2

3	mysqlbinlog  data\mysql-bin.000002 > E:/2.txt

4

5	mysqlbinlog  data\mysql-bin.000003 > E:/3.txt

通过这三个命令，可以在E盘下生成3个文件，里面分别记录了日志文件的内容，也就是用户操作的步骤。 

　　下面开始恢复binlog日志到Mysql数据库，因为我们需要重做第一个日志文件的所有操作，所以这里只需要将第一个日志文件全恢复就行了。

1	mysqlbinlog  data\mysql-bin.000001 | mysql -uroot -p123456

　　在第二个binlog里面我们进行了delete操作，我们并不想将delete的操作恢复到数据库，这样我们可以通过读取2.txt文件：

01	................................

02

03

/*!*/;

04

# at 653

05	#140902 16:07:43 server id 1  end_log_pos 759   Query   thread_id=1 exec_time=0 error_code=0

06	SET TIMESTAMP=1409645263/*!*/;

07	delete from test where id between 4 and 5

08

/*!*/;

09

# at 759

10	#140902 16:07:43 server id 1  end_log_pos 786   Xid = 175

11	COMMIT/*!*/;

12	................................

　　在这个文件中，我们可以看到DELETE的操作的起始位置是653，终止位置是759.那么我们只要重做第二个日志文件的开头到653的操作，然后再从759到末尾的操作，我们就可以把数据给恢复回来，而不会DELETE数据。所以执行两个命令

1	mysqlbinlog  data\mysql-bin.000002 --stop-pos=653 | mysql -uroot -p123456

2

3	mysqlbinlog  data\mysql-bin.000002 --start-pos=759 | mysql -uroot -p123456

4

5	mysqlbinlog  data\mysql-bin.000003 --stop-pos=587 | mysql -uroot -p123456

好了，到这里，所有的数据全部恢复了，我们可以用下面语句查看到：

01	mysql> select * from test

02	+----+------+----------+

03	| id | val  | data     |

04	+----+------+----------+

05	|  1 |   10 | wu       |

06	|  2 |   20 | yang     |

07	|  3 |   20 | ping     |

08	|  4 |   40 | hao      |

09	|  5 |   50 | iteblog  |

10	|  6 |   60 | iteblog1 |

11	|  7 |   70 | ping123  |

12	|  8 |   80 | ping163  |

13	+----+------+----------+

14	8 rows in set (0.00 sec)

本博客文章除特别声明，全部都是原创！
尊重原创，转载请注明： 转载自过往记忆（http://www.iteblog.com/）
本文链接: 【MySQL binlog基本用法】（http://www.iteblog.com/mysql-binlog_basic_usage）