binlog常用指令

参考： mysql binlog详解 - Presley - 博客园 (cnblogs.com)
说明：记录了所有的DDL和DML语句（除了数据查询语句select）包含语句所执行的消耗的时间

一、释义

DDL ----Data Definition Language 数据库定义语言 
主要的命令有create、alter、drop等，ddl主要是用在定义或改变表(table)的结构,数据类型，表之间的连接和约束等初始工作上，他们大多在建表时候使用

DML　----Data Manipulation Language 数据操纵语言
主要命令是slect,update,insert,delete,就像它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言

mysqlbinlog常见的选项有一下几个：

　　　　a、-- start-datetime：从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间

　　　　b、-- stop-datetime：从二进制日志中读取指定小于时间戳或者等于本地计算机的时间 取值和上述一样

　　　　c、-- start-position：从二进制日志中读取指定position 事件位置作为开始。

　　　　d、-- stop-position：从二进制日志中读取指定position 事件位置作为事件截至

3、一般来说开启binlog日志大概会有1%的性能损耗。

二、开启binlog日志

1、编辑打开mysql配置文件/application/mysql3307/my.cnf在

　　　　[mysqld]区块添加

　　　　log-bin=mysql-bin(也可指定二进制日志生成的路径，如：log-bin=/opt/Data/mysql-bin)
　　　　server-id=1

　　　　binlog_format=MIXED(加入此参数才能记录到insert语句)
2、重启mysqld服务

　　　　/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日志；

4、重置（清空）所有binlog日志
-- reset master;

四、查看binlog日志内容，常用有两种方式：

1、使用mysqlbinlog自带查看命令法

注意：
-- 　　　　　　a、binlog是二进制文件，普通文件查看器cat、more、vim等都无法打开，必须使用自带的mysqlbinlog命令查看。
-- 　　　　　　b、binlog日志与数据库文件在同目录中。
-- 　　　　　　c、在Mysql5.5以下版本使用mysqlbinlog命令时如果报错，就加上"--no-defaults"选项
-- 　　　　　　d、使用mysqlbinlog命令查看binlog日志内容，
[  mysql]# mysqlbinlog mysql-bin.00000002
解释：
server id 1:数据库主机的服务号
end_log_pos 796 :sql结束时的pos节点
thread_id=11:线程号
　　e、也可根据时间点查看
-- /home/software/mysql-5.1.72-linux-x86_64-glibc23/bin/mysqlbinlog --no-defaults mysql-bin.000720 --start-datetime="2018-09-12 18:45:00" --stop-datetime="2018-09-12:18:47:00"
2、上面这种办法读取出binlog日志的全文内容比较多，不容易分辨查看到pos点信息，下面介绍一种更为方便的查询命令：
　　　　mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
show binlog events in 'mysql-bin.001995' FROM 467345738 LIMIT 100 , 10;
show binlog events in 'mysql-bin.001995' FROM 467345738 LIMIT 0, 10;
　　　　参数解释：
-- 　　　　　　a、IN 'log_name':指定要查询的binlog文件名（不指定就是第一个binlog文件）
-- 　　　　　　b、FROM pos:指定从哪个pos起始点开始查起（不指定就是从整个文件首个pos点开始算）
-- 　　　　　　c、LIMIT【offset】：偏移量(不指定就是0)
-- 　　　　　　d、row_count :查询总条数（不指定就是所有行）

a、查询第一个最早的binlog日志：
　　show binlog events;

b、指定查询mysql-bin.000002这个文件
　　show binlog events in 'mysql-bin.000002';

c、指定查询mysql-bin.000002这个文件，从pos点:624开始查起：
　　show binlog events in 'mysql-bin.000002' from 624;

d、指定查询mysql-bin.000002这个文件，从pos点:624开始查起，查询10条（即10条语句）
　　show binlog events in 'mysql-bin.000002' from 624 limit 10;

e、指定查询 mysql-bin.000002这个文件，从pos点:624开始查起，偏移2行（即中间跳过2个）查询10条（即10条语句）。
　　show binlog events in 'mysql-bin.000002' from 624 limit 2,10;

9、从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日志

SELECT * FROM mysql.general_log

 show binary logs;
 
mysql> show variables like 'expire_logs_days'; #//该参数表示binlog日志自动删除/过期的天
mysql> set global expire_logs_days=7; #//表示日志保留3天，3天后就自动过期
 
mysql> show variables like 'binlog_expire_logs_seconds'; 
mysql> set global binlog_expire_logs_seconds=1296000;  #15天前的binlog自动清理
mysql> reset master; //删除master的binlog，即手动删除所有的binlog日志
mysql> reset slave; //删除slave的中继日志
mysql> purge master logs before '2012-03-30 17:20:00'; //删除指定日期以前的日志索引中binlog日志文件
mysql> purge master logs to 'binlog.000002'; //删除指定日志文件的日志索引中binlog日志文件
mysql> flush logs; //产生一个新的binlog日志文件

SHOW VARIABLES LIKE 'log_bin'; 判断MySQL是否已经开启binlog
show variables like '%binlog%';#查看MySQL的binlog模式
#查看日志开启状态 
show variables like 'log_%';
#查看所有binlog日志列表
show master logs;
#查看最新一个binlog日志的编号名称，及其最后一个操作事件结束点 
-- show master status;
#刷新log日志，立刻产生一个新编号的binlog日志文件，跟重启一个效果 
-- flush logs;
#清空所有binlog日志 
-- reset master;

mysql查看binlog

[/var/lib/mysql] # mysqlbinlog ./mysql-bin.000001

五、异常处理

-- 可能报错
mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'
-- 原因是mysqlbinlog这个工具无法识别binlog中的配置中的default-character-set=utf8这个指令。
两个方法可以解决这个问题

-- 在MySQL的配置/etc/my.cnf中将default-character-set=utf8 修改为 character-set-server = utf8，但是这需要重启MySQL服务，如果你的MySQL服务正在忙，那这样的代价会比较大。
-- 用mysqlbinlog --no-defaults mysql-bin.000001 命令打开
-- 但是用mysqlbinlog打开的binlog日志内容很多不容易分辨查看pos点信息

-- 通过binlog数据恢复
mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名
-- 使用.sql文件全量恢复(记得剔除掉问题语句比如drop)demo

root@ba586179fe4b:/opt/backup# mysqlbinlog /opt/backup/mysql-bin.000003 > /opt/backup/000003.sql
root@ba586179fe4b:/opt/backup# vi /opt/backup/000003.sql #删除里面的drop语句
# 删掉drop语句前后的# at 到 /*！*/之间的内容
root@ba586179fe4b:/opt/backup# mysql -uroot -p123456 -v < /opt/backup/000003.sql

-- 指定pos结束点恢复(部分恢复)：

root@ba586179fe4b:/opt/backup# mysqlbinlog --stop-position=571 --database=codehui /var/lib/mysql/mysql-bin.000003 | mysql -uroot -p123456 -v codehui

mysql> use codehui;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from test;
+----+--------+
| id | name   |
+----+--------+
|  1 | code   |
|  2 | php    |
|  3 | java   |
|  4 | golang |
|  5 | shell  |
+----+--------+
5 rows in set (0.00 sec)

-- pos点区间恢复
root@ba586179fe4b:/opt/backup# mysqlbinlog --start-position=882 --stop-position=995 --database=codehui /var/lib/mysql/mysql-bin.000003 | mysql -uroot -p123456 -v codehui

-- 也可指定时间区间恢复(部分恢复)
# 起始时间点
--start-datetime="YYYY-MM-DD H:I:S"
# 结束时间点
--stop-datetime ="YYYY-MM-DD H:I:S"

# 用法举例
mysqlbinlog --start-position=811 --start-datetime="YYYY-MM-DD H:I:S" --stop-datetime="YYYY-MM-DD H:I:S" --database=codehui /var/lib/mysql/mysql-bin.000003 | mysql -uroot -p123456 -v codehui

（2）通过mysql提供的工具来删除(推荐)

删除之前可以先看一下purge的用法：help purge;

#删除所有binlog日志，新日志编号从头开始
-- RESET MASTER;
#删除mysql-bin.010之前所有日志
-- PURGE MASTER LOGS TO 'mysql-bin.010';
#删除2003-04-02 22:46:26之前产生的所有日志
PURGE MASTER LOGS BEFORE '2003-04-02 22:46:26';