分类: MySQL

binlog开启与删除及参数说明

MySQL Server 有四种类型的日志——Error Log、General Query Log、Binary Log 和 Slow Query Log。

Error Log:错误日志,记录 mysqld 的一些错误。
General Query Log:一般查询日志,记录 mysqld 正在做的事情,比如客户端的连接和断开、来自客户端每条 Sql Statement 记录信息;如果你想准确知道客户端到底传了什么给服务端,这个日志就非常管用了,不过它非常影响性能。
Binary Log:就是Binlog 了,包含了事件,这些事件描述了数据库的改动,如建表、数据改动等,也包括一些潜在改动,比如DELETE FROM ran WHERE bing = luan,然而一条数据都没被删掉的这种情况。除非使用 Row-based logging,否则会包含所有改动数据的 SQL Statement,Binlog 就有了两个重要的用途——复制和恢复。
Slow Query Log:慢查询日志,记录一些查询比较慢的 SQL 语句——这种日志非常常用,主要是调优用的。

启用 Binlog

通常情况 MySQL 是默认关闭 Binlog 的,
log-bin = /data/mysql/binlog/mybinlog #开启binlog
这里的 log-bin 是指以后生成各 Binlog 文件的前缀,比如上述使用mybinlog,那么文件就将会是mybinlog.000001、mybinlog.000002 等。

查看binlog是否开启

mysql> SHOW VARIABLES LIKE '%log_bin%';
+---------------------------------+-----------------------------------+
| Variable_name                   | Value                             |
+---------------------------------+-----------------------------------+
| log_bin                         | ON                                |
| log_bin_basename                | /data/mysql/binlog/mybinlog       |
| log_bin_index                   | /data/mysql/binlog/mybinlog.index |
| log_bin_trust_function_creators | OFF                               |
| log_bin_use_v1_row_events       | OFF                               |
| sql_log_bin                     | ON                                |
+---------------------------------+-----------------------------------+
6 rows in set (0.00 sec)

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 = 1 #在进行1次事务提交以后,Mysql将执行一次fsync之类的磁盘同步指令,同志文件系统将Binlog文件缓存刷新到磁盘。
Mysql中默认的设置是sync_binlog=0,即不作任何强制性的磁盘刷新指令,这时性能是最好的,但风险也是最大的。一旦系统绷Crash,在文件系统缓存中的所有Binlog信息都会丢失,推荐设置为1,不会丢失数据。

MySQL的复制(Replication),实际上就是通过将Master端的Binlog通过利用IO线程通过网络复制到Slave端,然后再通过SQL线程解析Binlog中的日志再应用到数据库中来实现的。所以,Binlog量的大小对IO线程以及Msater和Slave端之间的网络都会产生直接的影响。

MySQL中Binlog的产生量是没办法改变的,只要我们的Query改变了数据库中的数据,那么就必须将该Query所对应的Event记录到Binlog中。那我们是不是就没有办法优化复制了呢?当然不是,在MySQL复制环境中,实际上是是有8个参数可以让我们控制需要复制或者需要忽略而不进行复制的DB或者Table的,分别为:

Binlog_Do_DB:设定哪些数据库(Schema)需要记录Binlog;

Binlog_Ignore_DB:设定哪些数据库(Schema)不要记录Binlog;

Replicate_Do_DB:设定需要复制的数据库(Schema),多个DB用逗号(“,”)分隔;

Replicate_Ignore_DB:设定可以忽略的数据库(Schema);

Replicate_Do_Table:设定需要复制的Table;

Replicate_Ignore_Table:设定可以忽略的Table;

Replicate_Wild_Do_Table:功能同Replicate_Do_Table,但可以带通配符来进行设置;

Replicate_Wild_Ignore_Table:功能同Replicate_Ignore_Table,可带通配符设置;

通过上面这八个参数,我们就可以非常方便按照实际需求,控制从Master端到Slave端的Binlog量尽可能的少,从而减小Master端到Slave端的网络流量,减少IO线程的IO量,还能减少SQL线程的解析与应用SQL的数量,最终达到改善Slave上的数据延时问题。

实际上,上面这八个参数中的前面两个是设置在Master端的,而后面六个参数则是设置在Slave端的。虽然前面两个参数和后面六个参数在功能上并没有非常直接的关系,但是对于优化MySQL的Replication来说都可以启到相似的功能。当然也有一定的区别,其主要区别如下:

如果在Master端设置前面两个参数,不仅仅会让Master端的Binlog记录所带来的IO量减少,还会让Master端的IO线程就可以减少Binlog的读取量,传递给Slave端的IO线程的Binlog量自然就会较少。这样做的好处是可以减少网络IO,减少Slave端IO线程的IO量,减少Slave端的SQL线程的工作量,从而最大幅度的优化复制性能。当然,在Master端设置也存在一定的弊端,因为MySQL的判断是否需要复制某个Event不是根据产生该Event的Query所更改的数据

所在的DB,而是根据执行Query时刻所在的默认Schema,也就是我们登录时候指定的DB或者运行“USEDATABASE”中所指定的DB。只有当前默认DB和配置中所设定的DB完全吻合的时候IO线程才会将该Event读取给Slave的IO线程。所以如果在系统中出现在默认DB和设定需要复制的DB不一样的情况下改变了需要复制的DB中某个Table的数据的时候,该Event是不会被复制到Slave中去的,这样就会造成Slave端的数据和Master的数据不一致的情况出现。同样,如果在默认Schema下更改了不需要复制的Schema中的数据,则会被复制到Slave端,当Slave端并没有该Schema的时候,则会造成复制出错而停止。

而如果是在Slave端设置后面的六个参数,在性能优化方面可能比在Master端要稍微逊色一点,因为不管是需要还是不需要复制的Event都被会被IO线程读取到Slave端,这样不仅仅增加了网络IO量,也给Slave端的IO线程增加了RelayLog的写入量。但是仍然可以减少Slave的SQL线程在Slave端的日志应用量。虽然性能方面稍有逊色,但是在Slave端设置复制过滤机制,可以保证不会出现因为默认Schema的问题而造成Slave和Master数据不一致或者复制出错的问题。

常用binlog日志操作命令

查看所有binlog日志列表

mysql> show master logs;

查看master状态,即最后(最新)一个binlog日志的编号名称,及其最后一个操作事件pos结束点(Position)值
mysql> show master status;

刷新log日志,自此刻开始产生一个新编号的binlog日志文件

mysql> flush logs;

注:每当mysqld服务重启时,会自动执行此命令,刷新binlog日志;在mysqldump备份数据时加 -F 选项也会刷新binlog日志;

重置(清空)所有binlog日志

mysql> reset master;

自动删除binlog

mysql> show binary logs;    #查看所有binlog
mysql> show variables like 'expire_logs_days';        #显示binglog保存日期    
mysql> set global expire_logs_days=3;        #设置binlog保存日期,到期后自动删除

手工删除binlog

mysql> reset master;   //删除master的binlog
mysql> reset slave;    //删除slave的中继日志
mysql> purge master logs before '2017-08-30 17:20:00';  //删除指定日期以前的日志索引中binlog日志文件
mysql> PURGE MASTER LOGS BEFORE DATE_SUB( NOW(), INTERVAL 3 DAY);  //清除3天前binlog日志BEFORE,变量的date自变量可以为'YYYY-MM-DD hh:mm:ss'格式。
mysql> purge master logs to 'binlog.000002';   //删除指定日志文件的日志索引中binlog日志文件,也可以直接rm命令删除

常用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不会显示在本列表中

相关文章

发表新评论