MySQL 中 binlo

最新推荐文章于 2024-09-20 21:26:51 发布

masterjames

最新推荐文章于 2024-09-20 21:26:51 发布

阅读量617

点赞数

分类专栏：数据库文章标签： mysql query events insert 服务器 table

数据库专栏收录该内容

20 篇文章 0 订阅

订阅专栏

是一些有效管理 MySQL 中 binlog 的方法。

一、在 my.cnf 中增加下述参数，指定保存更新到 binlog 的数据库：db_name，未在此指定的数据库将不记录 binlog

binlog-do-db=db_name

二、在 my.cnf 中增加下述参数，指定不保存更新到 binlog 的数据库：db_name

binlog-ignore-db=db_name

三、要清理日志，需按照以下步骤：

在每个从属服务器上，使用SHOW SLAVE STATUS来检查它正在读取哪个日志。
使用SHOW MASTER LOGS获得主服务器上的一系列日志。
在所有的从属服务器中判定最早的日志。这个是目标日志。如果所有的从属服务器是更新的，这是清单上的最后一个日志。
制作您将要删除的所有日志的备份。（这个步骤是自选的，但是建议采用。）
清理所有的日志，但是不包括目标日志。

1、清除所有 binlog，可以执行RESET（必须拥有RELOAD权限）。

RESET MASTER;

该命令将删除索引文件中所有的 binlog，并创建一个新的 binlog。（在以前版本的MySQL中，被称为FLUSH MASTER）。

2、清除指定的 binlog。

PURGE MASTER LOGS TO ‘mysql-bin.1000′;

该命令将清除 mysql-bin.1000 文件。

3、清除指定日期前的 binlog。

PURGE MASTER LOGS BEFORE ‘2008-08-08 08:08:08′;

该命令将清除 2008-08-08 08:08:08（奥运会开幕前的日志？）

PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 7 DAY);

该命令将清除7天前的 binlog。

4、定期删除 binlog

在 contab 中设置：下面语句定期删除3天前的 binlog

0 1 * * * `mysql -uroot -e ‘PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);

查看mysql binlog,监控mysql执行的操作及命令
What commands were executed? Reading the log files. (By default on 4.1.2 is should
be setup, but, see TIP 25 to check.)

The following is an example of the “show binlog events” which will show
all the inserted and deleted commands. See “TIP 2:” which shows how to
clean up or reset with the “reset master”.

mysql> show binlog events;
show binlog events;
+————————+—–+————+———–+————–+——————————————–+
| Log_name | Pos | Event_type | Server_id | Orig_log_pos | Info |
+————————+—–+————+———–+————–+——————————————–+
| third-fl-71-bin.000001 | 4 | Start | 1 | 4 | Server ver: 4.1.1-alpha-log, Binlog ver: 3 |
| third-fl-71-bin.000001 | 79 | Query | 1 | 79 | use `test`; create table j2 (a int) |
| third-fl-71-bin.000001 | 137 | Query | 1 | 137 | use `test`; insert into j2 (a) values (3) |
| third-fl-71-bin.000001 | 201 | Query | 1 | 201 | use `test`; insert into j2 (a) values (9) |
| third-fl-71-bin.000001 | 265 | Query | 1 | 265 | use `test`; delete from j2 where a=3 |
+————————+—–+————+———–+————–+——————————————–+
5 rows in set (0.00 sec)

Note that the logfiles by default are located in “/usr/local/var” (see TIP 25:) or execute “mysql> show variables” and look
for the entry under the datadir. It’s possible to query specific logfiles if they exist:

mysql> show binlog events in 'third-fl-71-bin.000001';

It’s also possible to specify the start position and limit the number of records as follows:

Or, from the command line:

$ mysqlbinlog

Another option is writing a C api. For a complete example of this (selectbinlog.c) download the following:

http://prdownloads.sourceforge.net/souptonuts/mysql_select-0.0.1.tar.gz?download

or take a peek at

http://souptonuts.sourceforge.net/code/selectbinlog.c.html

This sample “selectbinlog.c” selects only the “Query” events. The code is pretty simple.

FYI: If you do a lot of tracking, you may want to write the information to a Berkeley DB.
Contrary to the name Berkeley DB is not a database but a hash, or there is an option for
b-tree format. MySQL can use Berkeley DB for the underlying table structure. It’s very fast,
and you won’t get logs of your logs. If you’re using Linux, Berkeley DB is already installed
on your system. Ok, so how does one use Berkeley DB? Samples can be found at the following
link. Look for berkeleydb_0.x.x.tar.gz at the following link

http://sourceforge.net/project/showfiles.php?group_id=79066&package_id=80379

The newer version of Berkeley DB also supports encryption. Berkeley DB is open source
and can be downloaded from the following:

http://www.sleepycat.com/

NOTE: MySQL uses Berkeley DB if configured for it

./configure --with-berkeley-db

And tables are created with the “ENGINE” or “TYPE”
option:

CREATE TABLE b (i INT) ENGINE = BDB;
or
CREATE TABLE b (i INT) TYPE = BDB;

Popularity: unranked [?]