mysql binlog_MySQL binlog基本用法

本文隻是簡單的介紹mysql binlog基本用法，并不涉及到binlog的原理、格式等知識，如果需要了解這些進階的知識，請參見官方文檔。

本文重點介紹--start-position和--stop-position參數的使用

--start-position的文法是 --start-position=N

含義是從相對與二進制日志的第N偏移的事件開始讀。 同理，--stop-position=N的介紹和--start-position類似。在預設的情況下， log-bin是關閉的，如下： mysql> show variables like 'log_bin';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin | OFF |

+---------------+-------+

1 row in set (0.00 sec)

我們可以通過修改my.ini配置檔案，在[mysqld] 下面添加 log-bin=日志名： [mysqld]

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

port=3306

log-bin=mysql-bin

修改完成之後，我們需要重新開機mysql服務，然後再看下是否啟動了binlog mysql> show variables like 'log_bin';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin | ON |

+---------------+-------+

1 row in set (1.01 sec)

已經開啟了binlog。然後我們建立一個資料庫binlog mysql> create database binlog;

Query OK, 1 row affected (0.00 sec)

mysql> use binlog;

Database changed

然後在binlog資料庫下面建立表test，并依次進行如下操作。 mysql> create table test(

id int auto_increment not null primary key,

val int,

data varchar(20)

);

Query OK, 0 rows affected (0.01 sec)

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

Query OK, 1 row affected (0.02 sec)

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

Query OK, 1 row affected (0.01 sec)

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

Query OK, 1 row affected (0.01 sec)

mysql> flush logs;

Query OK, 0 rows affected (0.04 sec)

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

Query OK, 1 row affected (0.01 sec)

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

Query OK, 1 row affected (0.01 sec)

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

Query OK, 2 rows affected (0.01 sec)

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

Query OK, 1 row affected (0.02 sec)

mysql> flush logs;

Query OK, 0 rows affected (0.05 sec)

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

Query OK, 1 row affected (0.01 sec)

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

Query OK, 1 row affected (0.01 sec)

mysql> drop table test;

Query OK, 0 rows affected (0.01 sec)

mysql> drop database binlog;

Query OK, 0 rows affected (0.00 sec)

經過上述的操作，将會在本地資料庫資料存放目錄下面生成以下四個檔案： mysql-bin.000001

mysql-bin.000002

mysql-bin.000003

mysql-bin.index

*.index是索引檔案，其他三個是binlog檔案，我們可以用mysqlbinlog 工具來恢複資料。為了下面講解的友善，我們先将binlog檔案解析成txt檔案，如下： mysqlbinlog data\mysql-bin.000001 > E:/1.txt

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

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

通過這三個指令，可以在E盤下生成3個檔案，裡面分别記錄了日志檔案的内容，也就是使用者操作的步驟。

下面開始恢複binlog日志到Mysql資料庫，因為我們需要重做第一個日志檔案的所有操作，是以這裡隻需要将第一個日志檔案全恢複就行了。 mysqlbinlog data\mysql-bin.000001 | mysql -uroot -p123456

在第二個binlog裡面我們進行了delete操作，我們并不想将delete的操作恢複到資料庫，這樣我們可以通過讀取2.txt檔案： ................................

;

# at 653

#140902 16:07:43 server id 1 end_log_pos 759 Querythread_id=1exec_time=0error_code=0

SET TIMESTAMP=1409645263;

delete from test where id between 4 and 5

;

# at 759

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

COMMIT;

................................

在這個檔案中，我們可以看到DELETE的操作的起始位置是653，終止位置是759.那麼我們隻要重做第二個日志檔案的開頭到653的操作，然後再從759到末尾的操作，我們就可以把資料給恢複回來，而不會DELETE資料。是以執行兩個指令 mysqlbinlog data\mysql-bin.000002 --stop-pos=653 | mysql -uroot -p123456

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

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

好了，到這裡，所有的資料全部恢複了，我們可以用下面語句檢視到： mysql> select * from test

+----+------+----------+

| id | val | data |

+----+------+----------+

| 1 | 10 | wu |

| 2 | 20 | yang |

| 3 | 20 | ping |

| 4 | 40 | hao |

| 5 | 50 | iteblog |

| 6 | 60 | iteblog1 |

| 7 | 70 | ping123 |

| 8 | 80 | ping163 |

+----+------+----------+

8 rows in set (0.00 sec)