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;

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注