mysql数据库二进制日志使用

最新推荐文章于 2024-01-17 15:08:04 发布

栀子栀子栀

最新推荐文章于 2024-01-17 15:08:04 发布

阅读量814

点赞数

分类专栏： mysqlbinlog mysql 备份与恢复文章标签： mysql 备份二进制

本文链接：https://blog.csdn.net/u012535178/article/details/50204265

版权

mysql 同时被 3 个专栏收录

6 篇文章 0 订阅

订阅专栏

备份与恢复

2 篇文章 0 订阅

订阅专栏

mysqlbinlog

1 篇文章 0 订阅

订阅专栏

二进制日志的文件的作用
mysql二进制日志文件用来记录所有用户对数据库操作，即记录用户对数据库操作的sql语句。如果有此文件，当数据库发生意外时，可以通过此文件查看到用户在此文件记录的时间段内用户所做的操作，再和数据库备份配合使用，即可再现用户操作，使数据库恢复。
二进制日志文件的弊端
二进制日志文件开启后，所有对数据库操作的记录均会被记录到此文件，所以，当长时间开启之后，日志文件会变得很大，占用磁盘空间。

windows下mysql数据库二进制日志使用：

使用二进制日志文件恢复数据库

开启日志文件

mysql默认是不开启日志文件的功能的，需要我们手动开启。操作如下：

1、打开mysql安装目录\my.ini（windows系统是my.ini文件，linux是my.cnf文件）。

2、找到[mysqld]这个标签，在此标签下面一行，添加语句：log-bin=binary-log。上面语句中，log-bin说明要开启二进制日志文件，binary-log是二进制日志文件的名字。

3、重启mysql服务。可以在mysql安装目录\data 文件夹下看到“binary-log.数字编号”文件，如binary-log.000001。以后每重启一次mysql服务，都会重新生成二进制日志文件，文件名中的数字编号依次递增。

4、关闭二进制日志的方法：log-bin = mysql-bin注释掉即可

（方法二：

不重启修改二进制日志配置，该方法mysql的版本需要5.6以上

SET @@global.log_bin=1|0 （1为开启，0为关闭）

SET @@global.binlog_size=37268(单位bytes)

）

暂停二进制日志 SET sql_log_bin={0|1}

恢复数据库

使用日志文件恢复数据库必须要有一个数据库备份（只要是在二进制日志文件功能开启之后备份的就行）。恢复数据库时，可以通过时间恢复，也可以通过操作点恢复。（试验后发现只能后恢复被删除的对象。假若想恢复插入某条记录前的状态，会显示该表已存在并出错，恢复失败）

在Windows的cmd下操作

1、通过时间恢复方法如下：

如果我在2011-04-02 00:00:00时刻开启了二进制日志文件功能，并备份了数据库文件。且在2011-04-02 12:00:00数据库发生异常，需要恢复到2011-04-02 10:00:00。则操作如下：

l 用备份将数据库回复到2011-04-02 00:00:00时候的状态。

l 在命令行输入如下命令：

mysqlbinlog --stop-date="2011-04-02 10:00:00" mysql安装目录\data\binary-log.000001 | mysql -u root –p

这样，系统会自动执行从二进制日志有记录开始截止到2011-04-02 10:00:00，用户所做过的所有操作。与stop-date对应的，还有start-date属性，可以设定执行记录的开始时间。也可以两个属性都设置。

2、通过操作点恢复方法如下：

在命令行中输入mysqlbinlog D:\binary-log.000003 > D:\log.txt，执行后打开log.txt，查看里面语句，可以发现：在每个操作之前，都会有一个独特的编号，如下红字显示：

/*!*/;

# at 450 /*编号450*/

#110402 15:31:50 server id 1 end_log_pos 529 Query thread_id=2 exec_time=0 error_code=0

SET TIMESTAMP=1301729510/*!*/;

DROP TABLE `jwc`

此编号随着操作数增多而变大。和时间一样是一个标记，通过操作点恢复的语句如下：

mysqlbinlog --stop-position="450" mysql安装目录\data\binary-log.000001 | mysql -u root –p

这样，系统会自动执行二进制日志中编号最低的语句到编号450语句。与stop-position对应的，还有start-position属性，可以设定执行记录的开始编号。

Linux下mysql数据库二进制日志使用：

通过mysqlbinlog命令可以查看binlog的内容
[root@localhost ~]# mysqlbinlog /home/mysql/binlog/binlog.000003 | more

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#120330 16:51:46 server id 1 end_log_pos 98    Start: binlog v 4, server v 5.0.45-log created 120330 1
6:51:46
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
# at 196
#120330 17:54:15 server id 1 end_log_pos 294   Query   thread_id=3     exec_time=2     error_code=0
SET TIMESTAMP=1333101255/*!*/;
insert into tt7 select * from tt7/*!*/;
# at 294
#120330 17:54:46 server id 1 end_log_pos 388   Query   thread_id=3     exec_time=28    error_code=0
SET TIMESTAMP=1333101286/*!*/;
alter table tt7 engine=innodb/*!*/;

解析binlog格式

位置
位于文件中的位置，“at 196”说明“事件”的起点，是以第196字节开始；“end_log_pos 294”说明以第294字节结束

时间戳
事件发生的时间戳：“120330 17:54:46”

事件执行时间
事件执行花费的时间:"exec_time=28"

错误码
错误码为：“error_code=0”

服务器的标识
服务器的标识id：“server id 1”

====================================================================================

和binlog有关参数

log_bin
设置此参数表示启用binlog功能，并指定路径名称
log_bin_index
设置此参数是指定二进制索引文件的路径与名称
binlog_do_db
此参数表示只记录指定数据库的二进制日志
binlog_ignore_db
此参数表示不记录指定的数据库的二进制日志
max_binlog_cache_size
此参数表示binlog使用的内存最大的尺寸
binlog_cache_size
此参数表示binlog使用的内存大小，可以通过状态变量binlog_cache_use和binlog_cache_disk_use来帮助测试。
      binlog_cache_use：使用二进制日志缓存的事务数量
     binlog_cache_disk_use:使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量

max_binlog_size
Binlog最大值，最大和默认值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束

sync_binlog
这个参数直接影响mysql的性能和完整性

sync_binlog=0：
当事务提交后，Mysql仅仅是将binlog_cache中的数据写入Binlog文件，但不执行fsync之类的磁盘        同步指令通知文件系统将缓存刷新到磁盘，而让Filesystem自行决定什么时候来做同步，这个是性能最好的。

sync_binlog=n，在进行n次事务提交以后，Mysql将执行一次fsync之类的磁盘同步指令，同志文件系统将Binlog文件缓存刷新到磁盘。

Mysql中默认的设置是sync_binlog=0，即不作任何强制性的磁盘刷新指令，这时性能是最好的，但风险也是最大的。一旦系统绷Crash，在文件系统缓存中的所有Binlog信息都会丢失

binlog的删除
binlog的删除可以手工删除或自动删除

自动删除binlog
通过binlog参数（expire_logs_days ）来实现mysql自动删除binlog
mysql> show binary logs;
mysql> show variables like 'expire_logs_days';
mysql> set global expire_logs_days=3;

手工删除binlog
mysql> reset master;   //删除master的binlog
mysql> reset slave;    //删除slave的中继日志
mysql> purge master logs before '2012-03-30 17:20:00'; //删除指定日期以前的日志索引中binlog日志文件
mysql> purge master logs to 'binlog.000002';   //删除指定日志文件的日志索引中binlog日志文件
或者直接用操作系统命令直接删除

mysql> set sql_log_bin=1/0; //如果用户有super权限，可以启用或禁用当前会话的binlog记录

mysql> show master logs; //查看master的binlog日志
mysql> show binary logs; //查看master的binlog日志
mysql> show master status; //用于提供master二进制日志文件的状态信息
mysql> show slave hosts; //显示当前注册的slave的列表。不以--report-host=slave_name选项为开头的slave不会显示在本列表中

进行二进制日志的切换，默认情况下当二进制日志写满了或者数据库重启了才会进行切换，但是也可以手工的进行切换的动作

mysql> flush logs;

查看二进制日志的内容：

binlog不能直接用文本的方式打开，mysql提供了相应的查看工具：mysqlbinlog，直接查看单个二进制日志文件：mysqlbinlog filename

例如： mysqlbinlog /data/mysql/mysql-bin.000001

当然也可以通过二进制日志完成数据库的恢复，具体的使用将在数据库的备份还原中介绍。

删除二进制日志

二进制日志会不断的增长,并产生多个文件。因此,需要制定备份计划和管理策略.无用的二进制日志要记得及时删除。

删除慢查询日志有三种方法:

1、操作系统命令直接删除

2、reset master

3、PURGE BINARY LOGS BEFORE '2014-07-09 12:40:26′;