binlog日志记录对数据发生或潜在发生更改的SQL语句,并以二进制的形式保存在磁盘中;
作用:可以用来查看数据库的变更历史(具体的时间点所有的SQL操作)、数据库增量备份和恢复(增量备份和基于时间点的恢复)、MySQL的复制(主主数据库的复制、主从数据库的复制)恢复数据
文件位置:默认存放位置为数据库文件所在目录mysql/data下
文件的命名方式: 名称为hostname-bin.xxxxx (重启mysql一次将会自动生成一个新的binlog)
Mysql binlog日志有三种格式,分别为Statement,MiXED,以及ROW!
1.Statement:每一条会修改数据的sql都会记录在binlog中。
优点:不需要记录每一行的变化,减少了binlog日志量,节约了IO,提高性能。(相比row能节约多少性能 与日志量,这个取决于应用的SQL情况,正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量,但是考虑到如果带条 件的update操作,以及整表删除,alter表等操作,ROW格式会产生大量日志,因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况,其所 产生的日志量会增加多少,以及带来的IO性能问题。)
缺点:由于记录的只是执行语句,为了这些语句能在slave上正确运行,因此还必须记录每条语句在执行的时候的 一些相关信息,以保证所有语句能在slave得到和在master端执行时候相同 的结果。另外mysql 的复制,像一些特定函数功能,slave可与master上要保持一致会有很多相关问题(如sleep()函数, last_insert_id(),以及user-defined functions(udf)会出现问题).
2.Row:不记录sql语句上下文相关信息,仅保存哪条记录被修改。
优点: binlog中可以不记录执行的sql语句的上下文相关的信息,仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下 每一行数据修改的细节。而且不会出现某些特定情况下的存储过程,或function,以及trigger的调用和触发无法被正确复制的问题
缺点:所有的执行的语句当记录到日志中的时候,都将以每行记录的修改来记录,这样可能会产生大量的日志内容,比 如一条update语句,修改多条记录,则binlog中每一条修改都会有记录,这样造成binlog日志量会很大,特别是当执行alter table之类的语句的时候,由于表结构修改,每条记录都发生改变,那么该表每一条记录都会记录到日志中。
3.MIXED: 是以上两种的混合使用,推荐使用MIXED.
一般的语句修改使用statment格式保存binlog,如一些函数,statement无法完成主从复制的操作,则 采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式,也就是在Statement和Row之间选择 一种.新版本的MySQL中队row level模式也被做了优化,并不是所有的修改都会以row level来记录,像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句,还是会记录所有行的变更。
1、登录MySQL,查看binlog日志的状态
登录MySQL后,输入show variables like '%log_bin%';查看到binlog日志为OFF关闭状态;
2、开启MySQL binlog日志
在my.cnf配置文件中添加如下图的两句
首先我们来认识几个与bin-log相关的MySQL命令:
- flush logs; 会多一个最新的bin-log日志
- show master status; 查看最后一个bin-log日志的相关信息
- reset master; 清空所有的bin-log日志
这个时候我们进入到MySQL里,查看一下最后一个bin-log日志信息
然后刷新一下bin-log日志,再查看下最后一个bin-log日志,此时已经变成了000002这个版本了,
以后的增删改操作就记录在这个最新的bin-log日志里
接着我们创建一个数据库,然后创建一个表,再插入一些记录,然后再查看下日志信息有没有变化
接着我们再看下bin-log信息,此时位置已经发生了变化,也就是说已经对刚才的操作进行了记录
接着我们打开二进制日志,看看里面就是记录了什么,我们需要知道的是,如果用vi打开二进制日志的乱码的,
所以我们需要使用下面的命令进行打开:
/usr/local/mysql/bin/mysqlbinlog --no-defaults mysql-bin.000002
也可以另存为其他格式的文件打开
/usr/local/mysql/bin/mysqlbinlog --no-defaults mysql-bin.000002 > /tmp/add.sql
我把打开的日志截图看看.我用红色圈起来的都是我们需要注意的,end_log_pos大概的意思是这这个操作的最后位置在哪,
例如:end_log_pos 400,在299-400之间是存了插入操作的记录的,insert into test1 values(1);
流程如下:
(1)先生成一个新的日志文件(000003);
(2)然后删除test1表的数据;
(3)然后利用前一个日志恢复数据(000002);
(4)最后查看是否恢复成功。
/usr/local/mysql/bin/mysqlbinlog --no-defaults mysql-bin.000002 |mysql -uroot -p
我们到数据库里查看下是否恢复成功了:
/usr/local/mysql/bin/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
b)根据起始位置来还原,–start-position,–stop-position
/usr/local/mysql/bin/mysqlbinlog --no-defaults mysql-bin.000002 --start-position 134 --stop-position=386|mysql -uroot -p
c)根据数据库名来进行还原 -d在这里是小写的d,请不要把它和mysqldump中的-D搞混了。哈哈。
/usr/local/mysql/bin/mysqlbinlog -d test /var/lib/mysql/mysql-bin.000002
d)根据数据库所在IP来分-h
/usr/local/mysql/bin/mysqlbinlog -h 192.1681.102 /var/lib/mysql/mysql-bin.000002
e)根据数据库所占用的端口来分-P
/usr/local/mysql/bin/mysqlbinlog -p 13306 /var/lib/mysql/mysql-bin.000002
f)根据数据库serverid来还原–server-id
在数据库的配置文件中,都有一个serverid并且同一集群中serverid是不能相同的。
/usr/local/mysql/bin/mysqlbinlog –server-id=1 /var/lib/mysql/mysql-bin.000002
注意:上面的几个例子,我都是一个一个说的,其实可以排列组合的。
下面还有一个重要索引文件就是mysql-bin.index
.\mysql-bin.000001
.\mysql-bin.000002
.\mysql-bin.000003
mysql-bin.index的起索引作用,因为增量的数据不一定在一个mysql-bin000这样的文件里面,这个时候,我们就要根据mysql-bin.index来找mysql-bin这样的增量文件了
如果mysql里面做了这样的配置binlog-do-db=test1,增量备份里面只会有test1这个数据库的数据
3、设置自动清理binlog日志和手动删除的方法
MYSQL主从复制(replication)采用 RBR 模式后,binlog的格式为"ROW",能解决很多原先出现的主键重复问题。在一个繁忙的master db server上,binlog日志文件增长速度很快,如果不定时清除,硬盘空间很快就会被充满。
设置自动清理mysql binlog日志:
配置my.cnf:expire_logs_days = 10
在运行时修改:
show binary logs;
show variables like '%log%';
set global expire_logs_days = 10;
清除之前可以采用相应的备份策略。 手动删除10天前的mysql binlog日志:
PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);
show master logs;
MASTER和BINARY是同义词。