MySQL运维—binlog日志

一、mysql的日志
1、日志类型
二、错误日志
1、错误日志介绍
错误日志：MySQL的错误日志用于记录MySQL服务进程mysqld在启动/关闭或运行过程中遇到的错误信息。
2、错误日志配置

# 错误日志配置
vim /etc/my.cnf
#错误日志
log-error=/var/log/mysqld.log
# 查看存放位置 
show variables like '%log_error%';

三、bin-log日志
1、介绍

binlog 基本认识
   MySQL的二进制日志可以说是MySQL最重要的日志了，它记录了所有的DDL和DML(除了数据查询语句语句，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。

一般来说开启二进制日志大概会有1%的性能损耗(参见MySQL官方中文手册 5.1.24版)。二进制有两个最重要的使用场景: 
   其一：MySQL Replication在Master端开启binlog，Mster把它的二进制日志传递给slaves来达到master-slave数据一致的目的。 其二：自然就是数据恢复了，通过使用mysqlbinlog工具来使恢复数据。
    
二进制日志包括两类文件：
   二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件，
   二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML(除了数据查询语句)语句事件。 

2、配置bin-log

注意：5.7版本版本必须加server_id，mysql默认是没有开启二进制日志的。
vim /etc/my.cnf

#配置server_id
server_id = 1
#bin_log日志文件
log_bin = /var/lib/mysql/mysql-bin
#配置记录模式为行(三种记录模式：statement、row、mixed)
binlog_format = row
#查看binlog日志
show global variables like "%binlog_format%";  
#不重启，使配置在msyql中生效
SET global binlog_format='STATEMENT';

3、binlog日志
binlog是sql层的功能，记录的是变更SQL语句，不记录查询语句
4、三种记录方式
ROW Level
记录的方式是行，即如果批量修改数据，记录的不是批量修改的SQL语句事件，而是每条记录被更改的SQL语句，因此，ROW模式的binlog日志文件会变得很“重”。
优点：row level的binlog日志内容会非常清楚的记录下每一行数据被修改的细节。而且不会出现某些特定情况下存储过程或function，以及trigger的调用和触发器无法被正确复制的问题。
缺点：row level下，所有执行的语句当记录到日志中的时候，都以每行记录的修改来记录，这样可能会产生大量的日志内容，产生的binlog日志量是惊人的。批量修改几百万条数据，那么记录几百万行……

Statement level(默认)
记录每一条修改数据的SQL语句（批量修改时，记录的不是单条SQL语句，而是批量修改的SQL语句事件）。看上面的图解可以很好的理解row level和statement level两种模式的区别。
优点：statement模式记录的更改的SQ语句事件，并非每条更改记录，所以大大减少了binlog日志量，节约磁盘IO，提高性能。
缺点：statement level下对一些特殊功能的复制效果不是很好，比如：函数、存储过程的复制。由于row level是基于每一行的变化来记录的，所以不会出现类似问题

Mixed
实际上就是前两种模式的结合。在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。

5、企业场景如何选择binlog的模式
1、 如果生产中使用MySQL的特殊功能相对少（存储过程、触发器、函数）。选择默认的语句模式，Statement Level。
2、 如果生产中使用MySQL的特殊功能较多的，可以选择Mixed模式。
3、 如果生产中使用MySQL的特殊功能较多，又希望数据最大化一致，此时最好Row level模式；但是要注意，该模式的binlog非常“沉重”。

三、SQL语句的种类
1、根据对RDBMS赋予的指令种类不同，SQL语句可以分以下三类
DDL（Data Defintion Language，数据定义语言）用来创建或删除存储数据用的数据库以及数据库中的表等对象
CREATE 创建数据库和表
DROP 删除数据库和表
ALTER 修改数据库和表
2、DML（Data ManipulationLanguage，数据操纵语言）用于查询或者变更表中的记录
INSERT 向表中插入新数据（增）
DELETE 删除表中的数据（删）
UPDATE 更新表中数据（改）
SELECT 查询表中的数据（查）
3、DCL（Data Control Language，数据控制语言）用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对RDBMS的用户是否有权限操作数据库中的对象（数据表）进行设定
COMMIT 确认对数据库中的数据进行的变更
ROLLBACK 取消对数据库中的数据的变更
GRANT 赋予用户操作权限
REVOKE 取消用户的操作权限

四、event事件
1、event事件介绍
二进制日志的最小记录单元，是MySQL在相应的时刻调用的过程式数据库对象。一个事件可调用一次，也可周期性的启动，它由一个特定的线程来管理的，也就是所谓的“事件调度器”。

事件和触发器类似，都是在某些事情发生的时候启动。当数据库上启动一条语句的时候，触发器就启动了，而事件是根据调度事件来启动的。由于他们彼此相似，所以事件也称为临时性触发器。

事件取代了原先只能由操作系统的计划任务来执行的工作，而且MySQL的事件调度器可以精确到每秒钟执行一个任务，而操作系统的计划任务（如：Linux下的CRON或Windows下的任务计划）只能精确到每分钟执行一次。

2、事件优缺点
优点：一些对数据定时性操作不再依赖外部程序，而直接使用数据库本身提供的功能。
可以实现每秒钟执行一个任务，这在一些对实时性要求较高的环境下就非常实用了。
缺点：定时触发，不可以调用。

3、配置开启事件调度器

vim /etc/my.cnf
event_scheduler=ON
#查看是事件调度器
SHOW VARIABLES LIKE '%event_sche%';
#临时事件调度器
SET GLOBAL event_scheduler = 1; 

五、日志
1、日志的查看

2、查看binlog日志内容

3、语句模式

4、ROW模式下翻译

5、加参数过滤某个数据库的binlog文件

6、总结binlog用到的语句

六、日志的截取
1、数据库管理

数据库管理系统=实例+数据
1）物理损坏
存数据的硬盘损坏，文件系统损坏，数据文件损坏（ibd）rm
2）逻辑损坏
drop truncate delete update

2、日志截取

mysqlbinlog  --stat-position=321  --stop-position=513 /data/mysql/mysql-bin.000002>
/tmp/binlog.sql
set sql_log_bin=0;
source /tmp/binlog.sql;
set sql_log_bin=1;

3、查找截取日志步骤
4、截取日志，输出结果
5、恢复数据，屏蔽临时会话日志记录
6、binlog日志过期时间设置，查看过期时间
7、过期时间设置介绍、及建议
8、清理binlog日志

9、全部清理
reset master；