数据库binlog

一、初步了解binlog

1. MySQL的二进制日志binlog，可以说是MySQL最重要的日志，以事件形式记录除查询语句select以外所有的DDL和DML语句，还包含语句执行消耗的时间。MySQL的二进制日志是事务安全型的。

   • DDL：Date Definition Language 数据库定义语言

     主要的命令：create，alter，drop等

     主要用于定义或改变表(table)的结构、数据类型、表之间的链接和约束等初始工作上。

   • DML：Date Manipulation Language 数据库操纵语言

     主要命令：select、update、insert、delete

     主要用于对数据库的数据进行操作

2. MySQL binlog的常见选项

   • --start-datetime：从二进制日志中读取指定等于时间戳或晚于本地计算机的时间
   • --stop-datetime：从二进制日志中读取制定小于时间戳或等于本地计算机的时间
   • --start-position：从二进制日志中读取指定position事件位置作为开始
   • --stop-position：从二进制日志中读取指定position事件位置作为事件截止

3. 一般来说binlog日志大概会有1%的性能损耗

4. binlog日志最重要的使用场景

   • mysql主从复制：mysql replication在master端开启binlog，master把它的二进制日志传递给slaves来达到master-slave数据一致
   • 数据恢复：通过mysqlbinlog工具来恢复数据
   • binlog日志包括两类文件：
     • 二进制日志索引文件(后缀名为.index)：用于记录所有的二进制文件
     • 二进制日志文件(后缀名为.00000)：用于记录数据库所有的DDL和DML(除了select查询语句)

二、开启binlog日志

1. 编辑打开mysql配置文件/application/mysql3307/my.cn

   在[mysqld]区块添加
   	log-bin=mysql-bin
       (也可指定二进制日志生成的路径，如：log-bin=/opt/Data/mysql-bin)
   	server-id=1
   	binlog_format=MIXED(加入此参数才能记录到insert语句)
   

2. 重启mysqld服务(service mysqld restart)

   /application/mysql3307/bin/mysqladmin -uroot -S /application/mysql3307/logs/mysql.sock -p shutdown
   
   nohup /application/mysql3307/bin/mysqld_safe --defaults-file=/application/mysql3307/my.cnf --user=mysql &
   

3. 查看binlog是否开启

   mysql> show variables like 'log_%';
   

三、常用的binlog日志操作命令

1. 查看所有binlog日志列表：show master logs;
2. 查看master状态，即最后(最新)一个binlog日志的编号名称，机器最后一个操作时间pos结束点(Position)值：show master status；
3. flush 刷新log日志，自此刻开始产生一个新编号的binlog日志文件：flush logs；
   • 注意：每当mysqld重启，会自动执行此命令，刷新binlog日志；在mysqlddump备份数据的时候加上 -F选项也会刷新binlog日志；

四、查看binlog日志内容，常用的方式

1. 使用mysqlbinlog自带查看命令语法

   • binlog是二进制文件，普通文件查看器cat、more、vim都无法打开，必须使用自带的mysqlbinlog命令查看。
   • binlog日志与数据库文件在同目录中。
   • 在MySQL5.5以下版本使用mysqlbinlog命令如果报错，需要加上“-no-defaults”

   # 查看日志文件bin.000002
   mysqlbinlog mysql-bin.000002  
   
   # 根据时间点查看
   mysqlbinlog --no-defaults mysql-bin.000720 --start-datetime="2018-09-12 18:45:00" --stop-datetime="2018-09-12:18:47:00"
   

2. 指定binlog日志文件，分成有效事件行的方式返回，并可以使用limit指定pos点的起始偏移，查询条数。

   # 以上的读出的内容比较多，不容易分辨pos点信息，下面这种更方便
   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：查询总条数（不指定就是所有行）
   

   a、查询第一个最早的binlog日志：
     show binlog events\G;
   
   b、指定查询mysql-bin.000002这个文件
     show binlog events in 'mysql-bin.000002'\G;
   
   c、指定查询mysql-bin.000002这个文件，从pos点:624开始查起：
     show binlog events in 'mysql-bin.000002' from 624\G;
   
   d、指定查询mysql-bin.000002这个文件，从pos点:624开始查起，查询10条（即10条语句）
     show binlog events in 'mysql-bin.000002' from 624 limit 10\G;
   
   e、指定查询 mysql-bin.000002这个文件，从pos点:624开始查起，偏移2行（即中间跳过2个）查询10条（即10条语句）。
     show binlog events in 'mysql-bin.000002' from 624 limit 2,10\G;
   

五、利用binlog日志回复mysql数据

1. 先仔细看最后一个binlog日志，并记录下关键的pos点，到底是哪个pos点的操作导致了数据库的破坏（通常在最后几步）

2. 先备份最后一个binlog日志文件

   cd /application/mysql3306/mysql_data
   
   cp -v mysql-bin.000004  /application/data/backup/
   
   ls  /application/data/backup/
   

3. 接着执行一次刷新日志索引操作，重新开始新的binlog日志记录文件。

   flush logs;
   show master status;
   

4. 读取binlog日志

   a、方法一：使用mysqlbinlog读取binlog日志：
   	/application/mysql3306/bin/mysqlbinlog /application/mysql3306/mysql_data/mysql-bin.000004
   
   b、方法二：登录服务器，并查看（推荐此种方法）
     show binlog events in 'mysql-bin.000003';
       
   c、或者:
     show binlog events in 'mysql-bin.000004'\G;
   

5. 分析造成库数据破坏的pos点区间

6. 恢复备份的数据（建议另起一个库，等恢复成功后替换当前库）

   cd  /application/data/backup/
   
   gzip -d ops_2018-09-11.sql.gz
   
   /application/mysql3307/bin/mysql -uroot -S /application/mysql3307/logs/mysql.sock -p123456 <ops_2018-09-11.sql
   

7. 从binlog日志恢复数据

   a、恢复命令的语法格式：
   
   	mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名
   
   b、常用参数选项解释：
   
       --start-position=875 起始pos点
       --stop-position=954 结束pos点
       --start-datetime="2016-9-25 22:01:08" 起始时间点
       --stop-datetime="2019-9-25 22:09:46" 结束时间点
       --database=ops指定只恢复ops数据库(一台主机上往往有多个数据库，只限本地log日志)
   
   c、不常用选项：
   
       -u --user=name 连接到远程主机的用户名
   
       -p --password[=name]连接到远程主机的密码
   
       -h --host=name 从远程主机上获取binlog日志
   
       --read-from-remote-server从某个Mysql服务器上读取binlog日志
   
   d、小结：实际是将读出的binlog日志内容，通过管道符传递给myslq命令。这些命令，文件尽量写成绝对路径；
   
   e、完全恢复（需要手动vim编辑mysql-bin.000003，将那条drop语句剔除掉）(此方法测试未通过)
   
       /application/mysql3306/bin/mysqlbinlog /application/mysql3306/mysql_data/mysql-bin.000004 | /application/mysql3307/bin/mysql -uroot -S /application/mysql3307/logs/mysql.sock -p123456 -v ops
   
   f、指定pos结束点恢复（部分恢复）：
   
   	/application/mysql3306/bin/mysqlbinlog --stop-position=3064 --database=ops  /application/mysql3306/mysql_data/mysql-bin.000002 | /application/mysql3307/bin/mysql -uroot -S /application/mysql3307/logs/mysql.sock -p123456 -v(因为加了--database=ops因此不会恢复二进制日志中关于ops1库的相应操作，若也需要恢复ops1库的相应操作，则再加上--database=ops1即可)
   
   g、指定pos点区间恢复（部分恢复）
   
   	在f环节我们已经恢复到了删库之前的时刻，在删库后我们还做了创建ops2库并创建了member表和增加了数据的操作，此时我们要跳过删库并且恢复到创建ops2库和创建member表的时刻可以采用区间pos点恢复：
   	/application/mysql3306/bin/mysqlbinlog --start-position=3153 --stop-position=3880 /application/mysql3306/mysql_data/mysql-bin.000002 | /application/mysql3307/bin/mysql -uroot -S /application/mysql3307/logs/mysql.sock -p123456 -v
   
   h、此时后面创建的表member恢复回来了但是库ops1被删除了，因为在这中间有删除ops1库的操作，若想继续恢复后面表中插入的数据只需要以建表后的pos点为开始点即可恢复除删库之外的所有数据。　　　　　　　
   
   	/application/mysql3306/bin/mysqlbinlog --start-position=3880 /application/mysql3306/mysql_data/mysql-bin.000002 | /application/mysql3307/bin/mysql -uroot -S /application/mysql3307/logs/mysql.sock -p123456 -v
   
   
   
   
   

8. 另外：也可指定时间节点区间恢复（部分恢复）：按时间恢复需要mysqlbinlog命令读binlog日志内容，找时间节点。

   a、/application/mysql3306/bin/mysqlbinlog /application/mysql3306/mysql_data/mysql-bin.000002　　　　　　　　
   可以看到图中每个红框下的时间两个时间点都分别为事件的开始事件和结束时间
   
   	/application/mysql3306/bin/mysqlbinlog --stop-datetime="2018-09-12 10:37:58"  /application/data/backup/mysql-bin.000002 | 	/application/mysql3307/bin/mysql -uroot -S /application/mysql3307/logs/mysql.sock -p123456 -v(此时stopdatetime不能写到2018-09-12 10:38:01否则会更新到drop database ops这个操作，其它时间点同此步骤)
   
   b、跳过删库环节恢复后面数据，可以从2018-09-12 10:38:45时间开始恢复，因为删除ops1库的时间不足一秒因此可以这样干，这样干的话库ops1不会被删，不过建议最好还是从下一个时间节点为开始进行恢复,即2018-09-12 11:11:22
   	/application/mysql3306/bin/mysqlbinlog --start-datetime="2018-09-12 10:38:45" /application/data/backup/mysql-bin.000002 | /application/mysql3307/bin/mysql -uroot -S /application/mysql3307/logs/mysql.sock -p123456 -v
   
   c、基本原理和通过pos点恢复差不多。
   

本栏目推荐文章
• Jmeter 如何连接mysql数据库？
• SQL Server 检测是不是数字型数据的两种方法
• 如何通过Navicat把MySQL远程数据库导入到本地数据库
• 和利时MACS6通过dat文件实现任意时间范围与测点的历史数据提取与科研分析
• 深入理解spring框架：剖析多线程模式下数据库连接
• 数据可视化是如何帮助大型企业提高效率的？
• redis 数据库
• openpyxl模块--------------------------------重复数据
• Django客户端应用1向服务端应用2发送POST请求并接收解析数据
• 数据可视化是如何帮助我们高效了解数据的？

数据库 数据 binlog数据binlog mysql 数据恢复windows数据binlog streamsets实时 数据binlog 数据库 数据binlog日志 数据 数据恢复binlog mysql binlog备份 数据 数据binlog 权限 数据库 数据binlog binlog binlog2