一、MySQL日志

在任何一种数据库中,都会有各种各样的日志,记录着数据库工作的方方面面,以帮助数据库管理员追踪数据库曾经发生过的各种事件。

MySQL 也不例外,在 MySQL 中,有 4 种不同的日志,分别是错误日志二进制日志(BINLOG 日志)查询日志慢查询日志,这些日志记录着数据库在不同方面的踪迹。

1.1、错误日志

错误日志是 MySQL 中最重要的日志之一,它记录了当 mysqld 启动和停止时,以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时,可以首先查看此日志。

该日志是默认开启的 , 默认存放目录为 mysql 的数据目录(var/lib/mysql), 默认的日志文件名为hostname.err(hostname是主机名)。

查看日志位置指令:

1
show variables like 'log_error%';

image-20210312145042887

1.2、二进制日志

1、概述

二进制日志(BINLOG)记录了所有的 DDL(数据定义语言)语句和 DML(数据操纵语言)语句,但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用,MySQL的主从复制, 就是通过该 binlog 实现的。

二进制日志,默认情况下是没有开启的,需要到MySQL的配置文件中开启,并配置MySQL日志的格式。

配置文件位置 : /usr/my.cnf

日志存放位置 : 配置时,给定了文件名但是没有指定路径,日志默认写入Mysql的数据目录。

2、二进制日志格式

  • STATEMENT

该日志格式在日志文件中记录的都是SQL语句(statement),每一条对数据进行修改的SQL(类似Redisaof,只记录写操作,不记录查询操作)都会记录在日志文件中,通过 Mysql 提供的 mysqlbinlog 工具,可以清晰的查看到每条语句的文本。

主从复制的时候,从库(slave)会将日志解析为原文本,并在从库重新执行一次。

  • ROW

该日志格式在日志文件中记录的是每一行的数据变更,而不是记录SQL语句。

比如,执行SQL语句 : update tb_book set status='1', 如果是STATEMENT 日志格式,在日志中会记录一行SQL文件;

如果是ROW,由于是对全表进行更新,也就是每一行记录都会发生变更,ROW 格式的日志中会记录每一行的数据变更。

  • MIXED

这是目前MySQL默认的日志格式,即混合了STATEMENT 和 ROW两种格式。默认情况下采用STATEMENT,但是在一些特殊情况下采用ROW来进行记录。MIXED 格式能尽量利用两种模式的优点,而避开他们的缺点。

3、查看日志

由于日志以二进制格式存储,所以不能直接读取,需要借助 mysqlbinlog 工具来查看,语法如下:

1
mysqlbinlog log file;
  • 查看STATEMENT格式日志
1
mysqlbinlog 文件名;
  • 查看ROW格式日志

如果日志格式是 ROW , 直接查看数据 , 是查看不懂的 ;

可以在mysqlbinlog 后面加上参数 -vv

1
mysqlbinlog -vv 文件名;

4、日志删除

对于比较繁忙的系统,由于每天生成日志量大 ,这些日志如果长时间不清楚,将会占用大量的磁盘空间。下面我们将会讲解几种删除日志的常见方法:

  • 方式一:通过 Reset Master 指令删除全部 binlog 日志,删除之后,日志编号,将从 xxxx.000001重新开始

删除前先查看一下日志文件:

image-20210312151755838

1
Reset Master;

image-20210312151819371

  • 方式二:执行指令purge master logs to 'mysqlbin.xxx',这个命令将删除xxx编号之前的所有日志。
  • 方式三:执行指令 purge master logs before 'yyyy-mm-dd hh:mm:ss',该命令将删除日志为 "yyyy-mm-dd hh:mm:ss"之前产生的所有日志 。
  • 方式四:设置参数 --expire_logs_days=# ,此参数的含义是设置日志的过期天数, 过了指定的天数后日志将会被自动删除,这样将有利于减少DBA 管理日志的工作量。

1.3、查询日志

查询日志中记录了客户端的所有操作语句,而二进制日志不包含查询数据的SQL语句。

默认情况下, 查询日志是未开启的。如果需要开启查询日志,可以设置以下配置 :

1
2
3
4
#该选项用来开启查询日志 , 可选值 : 0 或者 1 ; 0 代表关闭,      1 代表开启
general_log=1
#设置日志的文件名 , 如果没有指定, 默认的文件名为 host_name.log
general_log_file=file_name
  • mysql配置文件 my.cnf 中配置如下内容

image-20210312153855304

  • 配置完毕之后,在数据库执行以下操作 :
1
2
3
4
select * from tb_book;
select * from tb_book where id = 1;
update tb_book set name = 'lucene入门指南' where id = 5;
select * from tb_book where id < 8;
  • 执行完毕后查看查询日志

image-20210312154159228

1.4、慢查询日志

慢查询日志记录了所有执行时间超过参数 long_query_time 设置值并且扫描记录数不小于

min_examined_row_limit 的所有的SQL语句的日志。

long_query_time 默认为 10 秒,最小为 0, 精度可以到微秒。

1、文件位置和格式

慢查询日志默认是关闭的 ,可以通过两个参数来控制慢查询日志:

1
2
3
4
5
6
7
# 该参数用来控制慢查询日志是否开启, 可取值: 1 和 0 , 1 代表开启, 0  代表关闭
slow_query_log=1

# 该参数用来指定慢查询日志的文件名
slow_query_log_file=slow_query.log

# 该选项用来配置查询的时间限制, 超过这个时间将认为值慢查询, 将需要进行日志记录, 默认10s long_query_time=10

2、日志的读取

和错误日志、查询日志一样,慢查询日志记录的格式也是纯文本,可以被直接读取。

  • 查询long_query_time 的值。
1
show variables like 'long%';

image-20210312155744225

  • 执行查询操作
1
select id, title,price,num ,status from tb_item where id = 1;

image-20210312155851586

由于执行时间很短,所以该查询语句不会被记录在慢查询日志中。

  • 执行一条查询时间极长的SQL语句
1
2
select * from tb_item where title like '%阿尔卡特 (OT-927) 炭黑 联通3G手机 双卡双待
165454%' ;

image-20210312160016746

该SQL语句 , 执行时长为 26.77s ,超过10s(long_query_time) , 所以会记录在慢查询日志文件中。

二、MySQL复制

2.1、复制概述

复制是指将主数据库的 DDL 和 DML **操作通过二进制日志传到从库服务器中**,然后在从库上对这些日志重新执行(也叫重做),从而使得从库和主库的数据保持同步。

MySQL支持一台主库同时向多台从库进行复制, 从库同时也可以作为其他从服务器的主库,实现链状复制。

2.2、复制原理

MySQL的主从复制原理如下:

注意,主从复制的前提是Master节点必须开启二进制日志。

image-20210312161130480

从上层来看,复制分为三步:

  • Master 主库在事务提交时,会把数据变更作为时间 Events 记录在二进制日志文件 Binlog 中。
  • 主库推送二进制日志文件 Binlog 中的日志事件到从库的中继日志 Relay Log
  • slave重做中继日志中的事件,将改变反映它自己的数据。

2.3、复制优势

MySQL 复制的有点主要包含以下三个方面:

  • 主库出现问题,可以快速切换到从库提供服务。
  • 可以在从库上执行查询操作,从主库中更新,实现读写分离,降低主库的访问压力。
  • 可以在从库中执行备份,以避免备份期间影响主库的服务。

2.4、搭建步骤

准备两台服务器

1、master

  • master 的配置文件 my.cnf 中,配置如下内容:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#mysql 服务ID,保证整个集群环境中唯一
server-id=1

#mysql binlog 日志的存储路径和文件名
log-bin=/var/lib/mysql/mysqlbin

#错误日志,默认已经开启
#log-err
#mysql的安装目录
#basedir
#mysql的临时目录
#tmpdir
#mysql的数据存放目录
#datadir

#是否只读,1 代表只读, 0 代表读写,由于此处为master节点,所以为读写型0
read-only=0

#忽略的数据, 指不需要同步的数据库
binlog-ignore-db=mysql

#指定同步的数据库
#binlog-do-db=db01
  • 配置完毕之后,需要重启 MySQL,关闭防火墙 (主从节点都需要关闭)
1
2
3
4
# 重启mysql服务
service mysql restart;
# 关闭防火墙
service iptables stop;
  • 创建同步数据的账户,并且进行授权操作:

给从节点 itcast'@'192.168.192.131 授予一个主从复制的权限,在所有数据库的所有表上。

*.* 表示所有数据库所有表。

identified by 'itcast' 指定访问密码为 'itcast'

1
2
3
grant replication slave on *.* to 'itcast'@'192.168.192.131' identified by 'itcast';
# 刷新权限列表
flush privileges;
  • 查看当前 master 节点状态信息

image-20210312164038664

字段含义:

  • File : 从哪个日志文件开始推送日志文件,这里应该从 mysqlbin.000001 开始推送日志文件
  • Position : 从哪个位置开始推送日志,这里应从413行开始推送日志
  • Binlog_Ignore_DB : 指定不需要同步的数据库,忽略 mysql 数据库

2、slave节点

  • 在 slave 端配置文件中,配置如下内容:
1
2
3
4
5
#mysql服务端ID,唯一
server-id=2

#指定binlog日志
log-bin=/var/lib/mysql/mysqlbin
  • 执行完毕之后,需要重启 Mysql,并关闭防火墙
1
2
3
4
# 重启mysql服务
service mysql restart;
# 关闭防火墙
service iptables stop;
  • 执行如下指令 :
1
change master to master_host= '192.168.192.130', master_user='itcast', master_password='itcast',  master_log_file='mysqlbin.000001',  master_log_pos=413;

指定当前从库对应的主库的IP地址,用户名,密码,从哪个日志文件开始的那个位置开始同步推送日志。

  • 开启同步操作
1
start slave;
  • 查看从节点当前状态
1
show slave status\G;

Slave_IO_State的值为:等待主节点给从节点发送数据。

image-20210312165843704

  • 关闭同步操作
1
stop slave;

3、验证主从复制同步操作

  • 在主节点中创建数据库,创建表,并插入数据 :
1
2
3
4
5
6
7
8
9
10
11
12
create database db01;
use db01;
create table user
(
id int(11) not null auto_increment,
name varchar(50) not null,
sex varchar(1),
primary key (id)
);
insert into user (id, name,sex) values (null,'Dawn','1');
insert into user(id,name,sex) values(null,'Trigger','0');
insert into user(id,name,sex) values(null,'Dawn','1');
  • 在从节点中,可以看到刚才创建的数据库

image-20210312170759153

  • 也可以在 db01 数据库中,查询到 user 表中的数据。

image-20210312170832484

  • 此时在主节点中使用 update 更新 user 表中的数据
1
update `user` set sex = '2';

image-20210312171104252

  • 在从节点中查询 user 表中的数据

image-20210312171138826