二进制日志 MySQLbinlog

最新推荐文章于 2022-02-21 09:04:38 发布

trustnature

最新推荐文章于 2022-02-21 09:04:38 发布

阅读量149

点赞数

本文链接：https://blog.csdn.net/trustnature/article/details/93036990

版权

一、log-bin配置

1.1、vi my.cnf

log-bin=/var/lib/mysql/mysql-bin.log，如果是这样的话log-bin=mysql-bin 则默认在datadir目录下面。

1.2、查看配置情况

1.3、设置

上述的各种变量既可以在mysql>中配置，又可以在 my.cnf中配置：注意在mysql>中设置的话重启服务号不会保存最好是直接修改 my.cnf文件在[mysqld]下面修改

log-bin[=/usr/local/mysql/var/mysql-bin]     //启用二进制日志功能
binlog-do-db[=数据库名]                      //指定记录二进制日志的数据库
binlog-ingore-db[=数据库名]                  //指定不记录二进制日志的数据库
expire_logs_days=99                     //最大为99

进入文件目录查看：除了bin文件之外还有一个重要索引文件就是mysql-bin.index

[root@BlackGhost mysql]# cat mysql-bin.index ./mysql-bin.000001 ./mysql-bin.000002 ./mysql-bin.000003 ./mysql-bin.000004 ./mysql-bin.000005 ./mysql-bin.000006

二、mysqlbinlog

由于binlog以是binary方式存取，不能直接查看，需要用mysql提供的mysqlbinlog工具查看。

mysqlbinlog命令
格式:mysqlbinlog [选项]  日志文件
  选项: -d 数据库名    列出指定数据库的二进制日志
        -h 服务器地址  指定数据库服务器地址
        -u 用户名      指定连接服务器的用户名
        -p 口令        指定用户口令
        -P 数字        指定服务器端口号
        -R             读取二进制日志
        --start-datetime=datetime  指定开始时间
        --stop-datetime=datetime   指定结束时间
         --start-position=数字     指定开始位置
         --stop-position=数字      指定结束位置

根据条件看一下数据

mysqlbinlog --no-defaults --start-date="2010-09-29 18:00:00" --stop-date="2013-11-01 23:00:00" /var/lib/mysql/mysql-bin.000059

注意：在使用mysqlbinlog查看日志的时候碰到了一个问题，错误提示如下：

/usr/local/mysql/bin/mysqlbinlog: unknown variable 'default-character-set=utf8'

产生这个问题的原因是因为我在my.cnf中的client选项组中添加了 default-character-set=utf8

要解决这个bug的方法还是有的，

一种方法是使用：--no-defaults

./mysqlbinlog --no-defaults mysql-bin.000063

另一种方法：使用mysqlbinlog工具查看二进制日志时会重新读取的mysql的配置文件my.cnf(windows下是my.ini),而不是服务器已经加载进内存的配置文件。也就是说只要修改并保存了my.cnf文件，而不需要重起mysql服务器，则使用mysqlbinlog查看时修改后的my.cnf配置文件对mysqlbinlog而言已经生效。所以这里可以使用此方法：把client选项组中default-character- set=utf8选项屏蔽掉，如

#default-character-set=utf8

然后运行mysqlbinlog工具，则不会产生任何问题了。当然记得在不在使用mysqlbinlog工具时，把#default-character-set=utf8

选项恢复为default-character-set=utf8

原理：

为了告诉一个MySQL程式不读任何选项文档，在命令行上指定--no-defaults作为第一个选项。这必须是第一个选项，否则他将无效！假如您想检查使用哪个选项，您能够给出--print-defaults选项作为第一个选项。

假如您想要强制使用一个特定配置文档，您能够使用选项--defaults-file=full-path-to-default-file。假如您这样做，只有指定的文档将被读取。

三、二进制文件恢复

利用二进制日志实现的数据恢复可以实现基于时间点的恢复和基于位置的恢复：

a），根据时间来还原 –start-date,–stop-date

[root@BlackGhost mysql]#mysqlbinlog –start-date=”2010-09-29 18:00:00″ –stop-date=”2010-09-29 23:00:00″ /var/lib/mysql/mysql-bin.000002 |mysql -u root -p

根据条件看一下数据

[root@BlackGhost mysql]#mysqlbinlog --start-date="2010-09-29 18:00:00" --stop-date="2010-09-29 23:00:00" /var/lib/mysql/mysql-bin.000002 //下面是部分内容，其实也就是一些对数据进行操作的sql语句 #

b），根据起始位置来还原，–start-position,–stop-position

[root@BlackGhost mysql]# mysqlbinlog –start-position=370 –stop-position=440 /var/lib/mysql/mysql-bin.000002 |mysql -u root -p

//查看插入的内容，根a）中是一样的

[root@BlackGhost mysql]#mysqlbinlog –start-position=370 –stop-position=440 /var/lib/mysql/mysql-bin.000002

–start-position=370 –stop-position=440 这里面数字从哪儿来的呢？
# at 370
#100929 21:35:25 server id 1 end_log_pos440Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1285767325/*!*/;

上面的红色加粗的就是，一个是start-position，一个是stop-position

c），根据数据库名来进行还原 -d

在这里是小写的d，请不要把它和mysqldump中的－D搞混了。哈哈。

[root@BlackGhost mysql]# mysqlbinlog -d test /var/lib/mysql/mysql-bin.000002

查看内容，请参考a）

d），根据数据库所在IP来分－h

[root@BlackGhost mysql]#mysqlbinlog -h 192.168.1.102 /var/lib/mysql/mysql-bin.000002

查看内容，请参考a）

e），根据数据库所占用的端口来分－P

有的时候，我们的mysql用的不一定是3306端口，注意是大写的P

[root@BlackGhost mysql]# mysqlbinlog -P 3306 /var/lib/mysql/mysql-bin.000002

查看内容，请参考a）

f），根据数据库serverid来还原–server-id

在数据库的配置文件中，都有一个serverid并且同一集群中serverid是不能相同的。

[root@BlackGhost mysql]# mysqlbinlog –server-id=1 /var/lib/mysql/mysql-bin.000002

查看内容，请参考a）

注意：上面的几个例子，我都是一个一个说的，其实可以排列组合的。例如

[root@BlackGhost mysql]#mysqlbinlog –start-position=”2010-09-29 18:00:00″ -d test -h 127.0.0.1 /var/lib/mysql/mysql-bin.000002 ｜mysql -u root -p

四、删除二进制文件

a.mysql>reset master(清空所有的二进制日志文件)
b.purge master logs to 'mysql-bin.000006';(删除bintest.000006之前的二进制日志文件)
c.purge master logs before '2007-08-10 04:07:00'(删除该日期之前的日志)
d.在my.cnf 配置文件中[mysqld]中添加:
  expire_logs_day=3设置日志的过期天数,过了指定的天数,会自动删除

五，后续

增量备份的时候，有一点让人不爽，就是mysql-bin这样的文件，每启动一次mysql就会增加一些，如果你不去管他的话，时间长了，他会把你的磁盘占满。

./mysqldump –flush-logs -u root myblog > /tmp/myblog.sql

备份myblog数据库，清除增量备份里面的有关myblog的数据

./mysqldump –flush-logs -u root –all-databases > /tmp/alldatabase.sql

备份所有数据库，清除增量备份

mysql-bin.index的起索引作用，因为增量的数据不一定在一个mysql-bin000这样的文件里面，这个时候，我们就要根据mysql-bin.index来找mysql-bin这样的增量文件了。

如果mysql里面做了这样的配置binlog-do-db=test1，增量备份里面只会有test1这个数据库的数据

补充：

mysql的binlog安全删除

论上，应该在配置文件/etc/my.cnf中加上binlog过期时间的配置项，expire_logs_days = 10

但是如果没有加这一项，随着产生越来越多的binlog，磁盘被吃掉了不少。可以直接删除binlog文件，但是可以通过mysql提供的工具来删除更安全。因为purge会更新mysql-bin.index中的条目，而直接删除的话，mysql-bin.index文件不会更新。mysql-bin.index的作用是加快查找binlog文件的速度。

先help一下吧：

mysql> help purge
Name: 'PURGE MASTER LOGS'
Description:
Syntax:
PURGE {MASTER | BINARY} LOGS TO 'log_name'
PURGE {MASTER | BINARY} LOGS BEFORE 'date'

Deletes all the binary logs listed in the log index prior to the
specified log or date. The logs also are removed from the list recorded
in the log index file, so that the given log becomes the first.

This statement has no effect if the --log-bin option has not been
enabled.

URL: http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html

Examples:
PURGE MASTER LOGS TO 'mysql-bin.010';
PURGE MASTER LOGS BEFORE '2003-04-02 22:46:26';

两种方法都可用。第一个是删除至某一个文件为止，第二个是删除到某个日期为止。

比如我们让它保留近3天的log，可以这样

PURGE MASTER LOGS BEFORE '2010-10-17 00:00:00';

看下执行前后的文件数：

执行前：

part2# ls mysql-bin.*|wc -l
243

执行purge：

mysql> PURGE MASTER LOGS BEFORE '2010-10-17 00:00:00';
Query OK, 0 rows affected (0.02 sec)

执行后：

part2# ls mysql-bin.* | wc -l
88

可见把17号前的binlog全部删除了。

需要注意的是：最好到slave上面去看下当前同步到那个binlog文件了，用show slave status查看。否则，master上删多了的话，就造成slave缺失日志文件而导致数据不一致了。

expire_logs_days 和 PURGE MASTER LOGS 用于让MySQL自动删除过期的binlog文件，

但对于线上使用的服务器我一直没有让这两个参数/命令成功运行过，每次都是运行后返回但没有任何效果，今天终于发现问题所在， http://bugs.mysql.com/bug.php?id=28238

如果你之前手工删除过MySQL的binlog而没有更新对应的Index文件，则MySQL在运行这个命令的时候会试图删除index文件中记录的但已经被手工删除掉的binlog文件，这样MySQL会得到一个错误后停止命令运行，导致操作没有效果，手工调整好Index文件后就没有问题了。

注意：

在日志切换的时候才会做日志清理的动作，而日志切换只发生在mysqld启动,flush log,或者mysqld自发切换日志的时候！

trustnature

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
二进制日志 MySQLbinlog

一、log-bin配置1.1、vi my.cnflog-bin=/var/lib/mysql/mysql-bin.log，如果是这样的话log-bin=mysql-bin 则默认在datadir目录下面。1.2、查看配置情况mysql> show global variables like '%logs_days%';+------------------+-------+|...
复制链接

扫一扫