博客

一般线上MySQL服务器，为了数据存储安全，都会默认开启binlog功能，以便恢复日后服务器故障或者更改删除等误操作的数据，而坏处是binlog日志文件增长速度快，很快会占满磁盘空间，所以对binlog日志文件的处理显得格外重要！

1. 1. 定义

      二进制日志包含了所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句。

   2. my.cnf配置 binlog相关参数

      1. #设置此参数表示启用binlog功能，并制定二进制日志的存储目录，
      2. log-bin=/home/mysql/binlog/mysql-bin
      4. #mysql-bin.*日志文件最大字节（单位：字节）
      5. #设置最大100MB
      6. max_binlog_size=104857600
      8. #设置了只保留7天BINLOG（单位：天）
      9. expire_logs_days = 7
      11. #binlog日志只记录指定库的更新
      12. #binlog-do-db=db_name
      14. #binlog日志不记录指定库的更新
      15. #binlog-ignore-db=db_name

      1. 1. NOTE:
            
            max_binlog_size — Binlog最大和默认值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束。所以真实文件有时候会大于max_binlog_size设定值！
            
            expire_logs_days — binlog过期删除不是服务定时执行，是需要借助事件触发才执行，事件有a.服务器重启 b.服务器被更新 c.日志达到了最大日志长度max_binlog_size d.日志被刷新mysql> flush logs;

      2. 作用
         

         1. 二进制日志的主要目的是在恢复使能够最大可能地更新数据库，因为二进制日志包含备份后进行的所有更新。

         2. 二进制日志还用于在主服务器上记录所有将发送给从服务器的语句。

            不良影响：运行服务器时若启用二进制日志则性能大约慢1%。

      3. 
         基础知识
         
         

         1. 二进制日志的启用

            二进制日志由配置文件的log-bin选项负责启用，Mysql服务器将在数据根目录创建两个新文件XXX-bin.001和XXX-bin.index，若配置选项没有给出文件名，Mysql将使用主机名称命名这两个文件，其中.index文件包含一份全体日志文件的清单。
            
            Mysql会把用户对所有数据库的内容和结构的修改情况记入XXX-bin.n文件，而不会记录SELECT和没有实际更新的UPDATE语句。

         2. 日志文件的扩展（XXX-bin.000001，XXX-bin.000002 – – – -）

            当停止或重启时，服务器会把日志文件记入下一个日志文件，Mysql会在重启时生成一个新的日志文件，文件序号递增，此外，如果日志文件超过max_binlog_size系统变量配置的上限时，也会生成新的日志文件。

         3. 日志文件的查看

            Mysql提供了mysqlbinlog命令来查看日志文件，如mysqlbinlog mysql-bin.00001 | more。在记录每条变更日志的时候，日志文件都会把当前时间给记录下来，以便进行数据库恢复。

         4. 日志文件的停用

            可以使用SET SQL_LOG_BIN=0命令停止使用日志文件，然后可以通过SET SQL_LOG_BIN=1命令来启用。

         5. 使用日志进行数据库恢复

            如果遇到灾难事件，应该用最近一次制作的完整备份恢复数据库，然后使用备份之后的日志文件把数据库恢复到最接近现在的可用状态。
            
            使用日志进行恢复时需要依次进行，即最早生成的日志文件要最先恢复：
            

            1. /usr/local/mysql/bin/mysqlbinlog /home/mysql/binlog/mysql-bin.000001 | /usr/local/mysql/bin/mysql -uroot -proot
            2. /usr/local/mysql/bin/mysqlbinlog /home/mysql/binlog/mysql-bin.000002 | /usr/local/mysql/bin/mysql -uroot -proot

            当只是需要恢复部分记录的时候，你可以使用时间点或位置点来定位
            

            1. /usr/local/mysql/bin/mysqlbinlog –start-date=”2015-04-1 9:01:00″ –stop-date=”2015-04-1 10:00:01″ /home/mysql/binlog/mysql-bin.000001 |mysql -uroot -ppassword
            2. #意思是恢复4月1日早上9点到10点这一段时间的数据。
            4. /usr/local/mysql/bin/mysqlbinlog –start-position=”368301″ –stop-position=”368312″/home/mysql/binlog/mysql-bin.000001 | mysql -uroot -pmypwd
            5. #意思是重做368301到368312位置点之间的操作。（位置点可以就是用mysqlbinlog看到的at xxx节点）
            7. /usr/local/mysql/bin/mysqlbinlog –start-date=”2005-04-20 9:55:00″ –stop-date=”2005-04-20 10:05:00″ /home/mysql/binlog/mysql-bin.000001 > /tmp/mysql_restore.sql
            8. #同样的，如果我们只是想查看某段时间的操作，直接把它导到一个文件即可