详解Mysql数据库之Binlog日志使用代码总结

下面小编就为大家带来一篇Mysql数据库之Binlog日志使用总结(必看篇)。小编觉得挺不错的，现在就分享给大家，也给大家做个参考。一起跟随小编过来看看吧

binlog二进制日志对于mysql数据库的重要性有多大，在此就不多说了。下面根据本人的日常操作经历，并结合网上参考资料，对binlog日志使用做一梳理：

一、binlog日志介绍

1）什么是binlog
binlog日志用于记录所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句。语句以“事件”的形式保存，它描述数据更改。

2）binlog作用
因为有了数据更新的binlog，所以可以用于实时备份，与master/slave主从复制结合。

3）和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信息都会丢失

4）binlog的删除

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

a）自动删除binlog

通过binlog参数（expire_logs_days ）来实现mysql自动删除binlog

mysql> show binary logs;
mysql> show variables like 'expire_logs_days';     
 //该参数表示binlog日志自动删除/过期的天数，默认值为0，表示不自动删除
mysql> set global expire_logs_days=3;       
 //表示日志保留3天，3天后就自动过期。

b）手工删除binlog

mysql> reset master;      //删除master的binlog，即手动删除所有的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 binlog日志自动清理及手动删除案例说明：

当开启MySQL数据库主从时，会产生大量如mysql-bin.00000* log的文件，这会大量耗费您的硬盘空间。 
mysql-bin.000001 
mysql-bin.000002 
mysql-bin.000003 
mysql-bin.000004 
mysql-bin.000005 
… 
 
删除这些binlog日志有三种解决方法： 
1.关闭mysql主从，关闭binlog； 
实例操作如下： 
[root@huqniupc ~]# vim /etc/my.cnf  //注释掉log-bin和binlog_format 
# Replication Master Server (default) 
# binary logging is required for replication 
# log-bin=mysql-bin 
# binary logging format - mixed recommended 
# binlog_format=mixed 
然后重启数据库 
 
2.开启mysql主从，设置expire_logs_days； 
实例操作如下： 
[root@huqniupc ~]# vim /etc/my.cnf //修改expire_logs_days,x是自动删除的天数，一般将x设置为短点，如10 
expire_logs_days = x //二进制日志自动删除的天数。默认值为0,表示“没有自动删除” 
此方法需要重启mysql 
 
当然也可以不重启mysql,开启mysql主从，直接在mysql里设置expire_logs_days 
> show binary logs; 
> show variables like '%log%'; 
> set global expire_logs_days = 10; 
 
 
3.手动清除binlog文件，(比如Mysql> PURGE MASTER LOGS TO ‘MySQL-bin.010′;） 
实例操作如下： 
[root@huqniupc ~]# /usr/local/mysql/bin/mysql -u root -p 
> PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);  //删除10天前的MySQL binlog日志,附录2有关于PURGE MASTER LOGS手动删除用法及示例 
> show master logs; 
  
也可以重置master，删除所有binlog文件： 
# /usr/local/mysql/bin/mysql -u root -p 
> reset master; //附录3有清除binlog时，对从mysql的影响说明 
  
--------------------------------------------------------------- 
PURGE MASTER LOGS手动删除用法及示例,MASTER和BINARY是同义词 
> PURGE {MASTER | BINARY} LOGS TO 'log_name'
> PURGE {MASTER | BINARY} LOGS BEFORE 'date'
删除指定的日志或日期之前的日志索引中的所有二进制日志。这些日志也会从记录在日志索引文件中的清单中被删除MySQL BIN-LOG 日志，这样被给定的日志成为第一个。 
 
实例： 
> PURGE MASTER LOGS TO 'MySQL-bin.010'; //清除MySQL-bin.010日志 
> PURGE MASTER LOGS BEFORE '2008-06-22 13:00:00';  //清除2008-06-22 13:00:00前binlog日志 
> PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY); //清除3天前binlog日志BEFORE，变量的date自变量可以为'YYYY-MM-DD hh:mm:ss'格式。 
-----------------------------------------------------

5）清除binlog时，对从mysql的影响

如果有一个活跃的slave从属服务器，该服务器当前正在读取您正在试图删除的日志之一，则本语句不会起作用，而是会失败，并伴随一个错误；不过如果slave从属服务器是关闭的（或master-slave主从关系关闭），并且碰巧清理了其想要读取的日志之一，则slave从属服务器启动后不能复制；当从属服务器正在复制时，本语句可以安全运行，不需要停止它们。

6）binglog的查看

通过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 TIMEST