binlog

一、什么是binlog

  binlog是一个二进制格式的文件，用于记录用户对数据库更新的SQL语句信息，例如更改数据库表和更改内容的SQL语句都会记录到binlog里，但是对库表等内容的查询不会记录。

binlog的作用

  当有数据写入到数据库时，还会同时把更新的SQL语句写入到对应的binlog文件里，这个文件就是上文说的binlog文件。使用mysqldump备份时，只是对一段时间的数据进行全备，但是如果备份后突然发现数据库服务器故障，这个时候就要用到binlog的日志了。

简单来说：binlog就是记录日志的增删改查，不记录对表的查询。

作用：用于数据恢复。

vi编辑打开MySQL配置信息

vi /etc/my.cnf

将最后一句加上。

重启MySQL服务生效

systemctl stop mysqld.service
systemctl start mysqld.service

二、也可登录mysql服务器，通过mysql的变量配置表，查看二进制日志是否已开启 单词：variable[ˈvɛriəbəl] 变量

mysql> show variables like 'log_%';

三、常用binlog日志操作命令

1.查看所有binlog日志列表
mysql> show master logs;

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

3.刷新log日志，自此刻开始产生一个新编号的binlog日志文件
mysql> flush logs;
注：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；

4、

  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;
 

三、复binlog日志实验

1、假设现在是凌晨4：00

mysql> show master status

 也就是说， mysql-bin.000014是用来记录4:00之后对数据库的所有“增删改”操作。

2、现在需要做一些需求，例如对数据的增删改

（1）、创建表

CREATE TABLE IF NOT EXISTS `dd` (
          `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
          `name` varchar(16) NOT NULL,
          `sex` enum('m','w') NOT NULL DEFAULT 'm',
          `age` tinyint(3) unsigned NOT NULL,
          `classid` char(6) DEFAULT NULL,
          PRIMARY KEY (`id`)
         ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

（2）、导入数据实验

insert into sys.dd(`name`,`sex`,`age`,`classid`) values('yiyi','w',20,'cls1'),('xiaoer','m',22,'cls3'),('zhangsan','w',21,'cls5'),('lisi','m',20,'cls4'),('wangwu','w',26,'cls6');

（3）、修改数据

      mysql> update sys.dd set name='李四' where id=4;
      mysql> update sys.dd set name='小二' where id=2;

2、假设执行删除语句，

  mysql> drop database sys;

先仔细查看最后一个binlog日志，并记录下关键的pos点，到底是哪个pos点的操作导致了数据库的破坏(通常在最后几步)；
备份一下最后一个binlog日志文件：

      # ll /var/lib/mysql | grep mysql-bin

       cp -v /var/lib/mysql/mysql-bin.000012 /root/

此时执行一次刷新日志索引操作，重新开始新的binlog日志记录文件，理论说 mysql-bin.000012 这个文件不会再有后续写入了(便于我们分析原因及查找pos点)，以后所有数据库操作都会写入到下一个日志文件；

mysql> flush logs;

mysql> show master status;

 

3、读取binlog文件，分析问题

mysql> show binlog events in 'mysql-bin.000014';

 

 通过分析，造成数据库破坏的pos点区间是介于 1460–1549 之间，只要恢复到1460前就可。

4、6.从binlog日志恢复数据
恢复语法格式：

mysqlbinlog mysql-bin.000014 --start-position=120 --stop-position=1460 | mysql -uroot -p1234 mysql （从120----1460）

 在另一端登录查看