MySQL之binlog

516 阅读7分钟

这是我参与8月更文挑战的第28天,活动详情查看:8月更文挑战

前言

mysql-binlog是MySQL数据库的二进制日志,用于记录用户对数据库操作的SQL语句((除了数据查询语句)信息。可以使用mysqlbin命令查看二进制日志的内容。

MySQL 的二进制日志 binlog 可以说是 MySQL 最重要的日志,它记录了所有的 DDL 和 DML 语句(除了数据查询语句select、show等),以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。 Binlog日志的两个最重要的使用场景

  • MySQL主从复制:MySQL Replication在Master端开启binlog,Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的
  • 数据恢复:通过使用 mysqlbinlog工具来使恢复数据

1 、Binlog概念

Binlog 是逻辑日记,用于记录数据库执行的写入操作(查询不记录)信息,Server层记录和引擎层无关,并且是以追加方式进行写入,可以通过参数 max_binlog_size 设置每个Binlog文件的大小,文件大小达到设定值时会生成新的文件来保存日记。

2、 Binlog 作用

在实际应用中,主要用在两个场景:主从复制和数据恢复

  • 主从复制场景:在Master主端开启Binlog,将Binlog发生到各个Slave从端,Slave从端重放Binlog从而达到主从数据一致
  • 数据恢复场景:通过使用 mysqlbinlog 工具来恢复数据

3、 Binlog 记录过程及刷盘时机

Binlog何时记录将在第六点进行介绍,大致记录过程是先写Binlog Buffer,然后通过刷盘时机,控制刷入OS Buffer,控制fsync()进行写入Binlog File日记磁盘的过程。

对于Binlog,MySQL是通过参数sync_binlog参数来控制刷盘时机,取值是0、1和N三种值。0表示由系统自行判断何时调用sync()写入磁盘;1表示每次事务commit都要调用fsync()写入磁盘;N表示每N个事务,才会调用fsync()写入磁盘。

查看binlog日志

查看某个binlog日志内容,常用有两种方式:

    1.使用mysqlbinlog自带查看命令法:
      注: binlog是二进制文件,普通文件查看器cat more vi等都无法打开,必须使用自带的 mysqlbinlog 命令查看
          binlog日志与数据库文件在同目录中(我的环境配置安装是选择在/usr/local/mysql/data中)
      在MySQL5.5以下版本使用mysqlbinlog命令时如果报错,就加上 “--no-defaults”选项
    
      # /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000013
        下面截取一个片段分析:

         ...............................................................................
         # at 552
         #131128 17:50:46 server id 1  end_log_pos 665   Query   thread_id=11    exec_time=0     error_code=0 ---->执行时间:17:50:46;pos点:665
         SET TIMESTAMP=1385632246/*!*/;
         update zyyshop.stu set name='李四' where id=4              ---->执行的SQL
         /*!*/;
         # at 665
         #131128 17:50:46 server id 1  end_log_pos 692   Xid = 1454 ---->执行时间:17:50:46;pos点:692 
         ...............................................................................

         注: server id 1     数据库主机的服务号;
             end_log_pos 665 pos点
             thread_id=11    线程号


    2.上面这种办法读取出binlog日志的全文内容较多,不容易分辨查看pos点信息,这里介绍一种更为方便的查询命令:

      mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

             选项解析:
               IN 'log_name'   指定要查询的binlog文件名(不指定就是第一个binlog文件)
               FROM pos        指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
               LIMIT [offset,] 偏移量(不指定就是0)
               row_count       查询总条数(不指定就是所有行)

             截取部分查询结果:
             *************************** 20. row ***************************
                Log_name: mysql-bin.000021  ----------------------------------------------> 查询的binlog日志文件名
                     Pos: 11197 ----------------------------------------------------------> pos起始点:
              Event_type: Query ----------------------------------------------------------> 事件类型:Query
               Server_id: 1 --------------------------------------------------------------> 标识是由哪台服务器执行的
             End_log_pos: 11308 ----------------------------------------------------------> pos结束点:11308(即:下行的pos起始点)
                    Info: use `zyyshop`; INSERT INTO `team2` VALUES (0,345,'asdf8er5') ---> 执行的sql语句
             *************************** 21. row ***************************
                Log_name: mysql-bin.000021
                     Pos: 11308 ----------------------------------------------------------> pos起始点:11308(即:上行的pos结束点)
              Event_type: Query
               Server_id: 1
             End_log_pos: 11417
                    Info: use `zyyshop`; /*!40000 ALTER TABLE `team2` ENABLE KEYS */
             *************************** 22. row ***************************
                Log_name: mysql-bin.000021
                     Pos: 11417
              Event_type: Query
               Server_id: 1
             End_log_pos: 11510
                    Info: use `zyyshop`; DROP TABLE IF EXISTS `type`

      这条语句可以将指定的binlog日志文件,分成有效事件行的方式返回,并可使用limit指定pos点的起始偏移,查询条数;
      
      A.查询第一个(最早)的binlog日志:
        mysql> show binlog events\G; 
    
      B.指定查询 mysql-bin.000021 这个文件:
        mysql> show binlog events in 'mysql-bin.000021'\G;

      C.指定查询 mysql-bin.000021 这个文件,从pos点:8224开始查起:
        mysql> show binlog events in 'mysql-bin.000021' from 8224\G;

      D.指定查询 mysql-bin.000021 这个文件,从pos点:8224开始查起,查询10条
        mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 10\G;

      E.指定查询 mysql-bin.000021 这个文件,从pos点:8224开始查起,偏移2行,查询10条
        mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 2,10\G;
show variables like 'log_bin';

# 查看详细的日志配置信息
show global variables like '%log%';

# mysql数据存储目录
show variables like '%dir%';

# 查看binlog的目录
show global variables like "%log_bin%";

# 查看当前服务器使用的biglog文件及大小
show binary logs;

# 查看主服务器使用的biglog文件及大小

# 查看最新一个binlog日志文件名称和Position
show master status;


# 事件查询命令
# IN 'log_name' :指定要查询的binlog文件名(不指定就是第一个binlog文件)
# FROM pos :指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
# LIMIT [offset,] :偏移量(不指定就是0)
# row_count :查询总条数(不指定就是所有行)
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

# 查看 binlog 内容
show binlog events;

# 查看具体一个binlog文件的内容 (in 后面为binlog的文件名)
show binlog events in 'master.000003';

# 设置binlog文件保存事件,过期删除,单位天
set global expire_log_days=3; 

# 删除当前的binlog文件
reset master; 

# 删除slave的中继日志
reset slave;

# 删除指定日期前的日志索引中binlog日志文件
purge master logs before '2019-03-09 14:00:00';

# 删除指定日志文件
purge master logs to 'master.000003';

清理binlog

reset master; //删除master的binlog,线上不要使用
purge master logs before '2012-03-30 17:20:00'; //删除指定日期以前的日志索引中binlog日志文件
purge master logs to 'binlog.000002'; //删除指定日志文件的日志索引中binlog日志文件

如果想直接删除binlog
rm binlog.000002
(不要删除当前正在使用的binlog,也就是编号最大的binlog)
[root@namenode mysql3306]# cat mysql-bin.index 不会自动更新
/home/mysql3306/mysql3306/mysql-bin.000001
/home/mysql3306/mysql3306/mysql-bin.000002\

恢复

   恢复是binlog的两大主要作用之一,接下来通过实例演示如何利用binlog恢复数据:
    
    a.首先,看下当前binlog位置
        mysql> show master status;
        +------------------+----------+--------------+------------------+-------------------+
        | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
        +------------------+----------+--------------+------------------+-------------------+
        | mysql-bin.000008 |     1847 |              |                  |                   |
        +------------------+----------+--------------+------------------+-------------------+
    b.向表tb_person中插入两条记录:
        insert into tb_person  set name="person_1", address="beijing", sex="man", other="test-1";
        insert into tb_person  set name="person_2", address="beijing", sex="man", other="test-2";
    c.记录当前binlog位置:
        mysql> show master status;
        +------------------+----------+--------------+------------------+-------------------+
        | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
        +------------------+----------+--------------+------------------+-------------------+
        | mysql-bin.000008 |     2585 |              |                  |                   |
        +------------------+----------+--------------+------------------+-------------------+
    d.查询数据 
        mysql> select *  from tb_person where name ="person_2" or name="person_1";
        +----+----------+---------+-----+--------+
        | id | name     | address | sex | other  |
        +----+----------+---------+-----+--------+
        |  6 | person_1 | beijing | man | test-1 |
        |  7 | person_2 | beijing | man | test-2 |
        +----+----------+---------+-----+--------+
    e.删除一条: delete from tb_person where name ="person_2";
        mysql> select *  from tb_person where name ="person_2" or name="person_1";
        +----+----------+---------+-----+--------+
        | id | name     | address | sex | other  |
        +----+----------+---------+-----+--------+
        |  6 | person_1 | beijing | man | test-1 |
        +----+----------+---------+-----+--------+
    f. binlog恢复(指定pos点恢复/部分恢复)
        mysqlbinlog   --start-position=1847  --stop-position=2585  mysql-bin.000008  > test.sql
        mysql> source /var/lib/mysql/3306/test.sql
    d.数据恢复完成 
        mysql> select *  from tb_person where name ="person_2" or name="person_1";
        +----+----------+---------+-----+--------+
        | id | name     | address | sex | other  |
        +----+----------+---------+-----+--------+
        |  6 | person_1 | beijing | man | test-1 |
        |  7 | person_2 | beijing | man | test-2 |
        +----+----------+---------+-----+--------+
    e.总结
        恢复,就是让mysql将保存在binlog日志中指定段落区间的sql语句逐个重新执行一次而已