create database if not exists bilibili; #创建数据库bilibili use bilibili; #选择bilibili数据库，即可进行下面的代码演示 drop database if exists bilibili; #代码演示结束后删除bilibili数据库。这个小文件全部代码演示完再删 show databases; #查看是否删除完成

-- 准备数据

create table stu(
    id int primary key comment '主键ID',
    name varchar(10) comment '姓名',
    age int comment '年龄'
) comment '简单学生表';
insert into stu values (1,'tom',1),(3,'cat',3),(8,'rose',8),(11,'jetty',11),(19,'lily',19),(25,'luci',25);

运维篇 日志 错误日志

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

该日志是默认开启的，默认存放目录/var/log/，默认的日志文件名为mysqld.log。查看日志位置: show variables like '%log_error%';#其中就有log_error变量，log_error变量记录了错误日志关联的文件(/var/log/mysqld.log)

不登录进MySQL执行如下即可查看'mysqld.log错误日志文件'里面尾部的50条数据 tail -50 /var/log/mysqld.log;

当我们执行某条语句错误的时候，错误信息就会被记录到'mysqld.log文件里面。下面是模拟:

#新开一个会话，实时查看mysqld.log文件里面的内容，这个会话我们不用动，用来切换方便查看日志的
tail -f /var/log/mysqld.log;

#修改配置文件，auto.cnf文件记录的是MySQL服务的uuid
vim /var/lib/mysql/auto.cnf;
按a键
删一个数字
按Esc键
:wq
回车
#上面那六行执行之后会进入一个编辑页面，并且目前只有一行uuid的数据，因为uuid的数据长度是固定的，为了模拟错误，
#我们可以把uuid末尾的那个数字删掉(记住，等下你要改回来的)，然后如下重启MySQL，看一下错误日志会不会发现这里的错误
systemctl restart mysqld;#重启报错

当我们想知道是什么错误导致的是，就可以去看mysqld.log文件，由于我们已经在另一个会话实时查看着了，所以直接切换到那个会话 我们会看到新多的一堆英文，只需要看有[error]的行即可，它会告诉我们具体原因，比如auto.cnf file is not a valid UUID，即无效的uuid 我们相对应的去修改即可恢复正常

vim /var/lib/mysql/auto.cnf;#把你删的那个数字写回去
按a键
补回你删的那个数字
按Esc键
:wq
回车

再次重启就正常啦

systemctl restart mysqld;

查看log_error变量对应的日志路径

show variables like '%log_error%';

运维篇 日志 二进制日志

二进制日志

二进制日志(binlog)记录了所有的DDL(数据定义语言)语句和DML(数据操纵语言)语句，但不包括数据查询(select、show)语句

DDL语句的作用: 对数据库对象(数据库、表、列、索引等)进行创建、删除、修改 DML语句的作用: 用于添加、修改、删除和查询数据库记录，并检查数据完整性

二进制日志的作用

1、灾难时的数据恢复;

2、MySQL的主从复制。在MySQL8版本中，默认二进制日志是开启着的，涉及到的参数下面会详细讲

show variables like '%log_bin%';#查看二进制日志的参数，能查到6行2列的结果，前三行的作用如下

1、log_bin: 二进制文件状态，ON，也就是默认开启的

2、log_bin_basename: 最终生成的二进制文件，/var/lib/mysql/binlog，也就是在/var/lib/mysql/路径有很多binlog前缀的日志文件

3、log_bin_index: 日志的索引文件，/var/lib/mysql/binlog.index，binlog.index是索引文件记录了当前MySQL数据库关联的所有日志文件

查看二进制日志文件 cd /var/lib/mysql/ && ll; cat binlog.index;

日志格式 MySQL服务器中提供了多种格式来记录二进制日志，共有三个日志格式，对应的特点如下:

日志查看: 由于二进制数据的日志不能直接读取，所以需要通过我们学过的二进制日志查询工具mysqlbinlog来查看，如下 mysqlbinlog [参数选项] 日志文件名;

参数选项如下

日志删除:对于比较繁忙的业务系统，每天生成的binlog前缀的日志文件是很多的，如果长时间不清理，将会占用大量磁盘空间。清理方式如下

1、reset master: 删除全部binlog日志，删除之后，日志编号，将从binlog.000001重新开始

2、purge master logs to 'binlog.**': 删除**编号之前的所有日志

3、purge master logs before 'yyyy-mm-dd hh24:mi:ss': 删除日志为'yyyy-mm-dd hh24:mi:ss'之前产生的所有日志

如果不想手动删除过久的二进制日志文件，我们也可以在mysql的配置文件中配置二进制日志的过期时间，然后二进制日志过期之后会自动删除

show variables like '%binlog_expire_logs_seconds%';#默认的过期时间是30天

演示如下

row日志格式的日志文件，查看update行数据变更后的日志

首先执行一条update语句

xxxxxxxxxx
update stu set age = age + 1;

查看MySQL默认的日志格式是哪个

show variables like '%binlog_format%';

举例: 当我们执行了一条update语句，这3个不同格式的日志会有什么区别呢

1、statement日志格式: 只记录这条update语句(下下面会单独讲，这里只讲下面那行的row格式的)

2、row日志格式: update这条语句能够影响的行，每一行都会被记录(变更之前+变更之后的数据都会被记录) 注意基于行的二进制日志文件，需要mysqlbinlog -v才能打开，不要漏了-v参数，因为我们打开的是行二进制日志文件 我们执行update语句的时候，就会在/var/lib/mysql生成基于行的二进制日志文件

查看二进制日志里面到底记录了什么数据，例如查看binlog.000002二进制日志文件

cd /var/lib/mysql && mysqlbinlog -v binlog.000002;#注意你的不是binlog.000002，你要用你binglog后面最大的尾缀，才是最新的数据

#如何查看你binglog后面最大的尾缀: 先cd /var/lib/mysql && ll; 找出binlog.xxxx数字最大的文件，就是目前最新的二进制日志文件 #里面记录的才有我们刚刚执行的update语句产生的日志数据

statement日志格式的日志文件，查看update行数据变更后的日志

首先执行一条update语句

xxxxxxxxxx
update stu set age = age - 1;

如何把默认的日志格式row修改为statement或mixed。需要在配置文件里面才能改，如下

vim /etc/my.cnf;

进去编辑页面之后，在最末尾行添加如下

binlog_format=STATEMENT

保存退出之后，重启mysql即可生效

xxxxxxxxxx
systemctl restart mysqld

#然后cd /var/lib/mysql && ll; 找出binlog.xxxx数字最大的文件，就是目前最新的二进制日志文件，例如binlog.000003 #里面记录的才有我们刚刚执行的update语句产生的日志数据

查看二进制日志里面到底记录了什么数据，例如查看binlog.000003二进制日志文件

mysqlbinlog binlog.000003;

#不需要加-v参数，原因:statement日志格式的日志文件记录的就是SQL，也就是我们能读懂的 #如果是row日志格式的日志文件，就需要加-v参数(我们上上面演示过)，因为它记录的是基于行的数据，我们读不懂

注意只有update、insert、create、delete语句的操作才会被记录到二进制日志中，select语句是不会被记录的 原因:二进制文件只记录DDL和DML语句

日志删除

cd /var/lib/mysql && ll; #查看有哪些日志。linux终端执行
purge master logs to 'binlog.000003'; #表示删除binlog.000002日志文件、binlog.000001日志文件。登录进mysql后执行
reset master; #删除所有binlog前缀的日志。登录进mysql后执行*/

运维篇 日志 查询日志

查询日志

上节课的二进制日志是不包含查询数据的SQL语句，那么我们的查询语句被什么日志记录着呢，这节课的'查询日志'就是记录了客 户端的所有操作语句，其中就包含查询语句。

由于查询日志记录的日志信息比较多，所以默认情况下，查询日志是未开启的 show variables like '%general%';#general_log查询日志默认是OFF

开启查询日志 #修改MySQL的配置文件 /etc/my.cnf 文件

vim /etc/my.cnf

#把下面那两行粘贴到my.cnf文件里面的末尾行，然后保存退出，表示开启查询日志。0代表关闭，1代表开启

xxxxxxxxxx
general_log=1 #开启查询日志
general_log_file=/var/lib/mysql/mysql_query.log #日志文件的名字、保存路径。如果不设置，默认是/var/lib/mysql/localhost.log

重启mysql服务

xxxxxxxxxx
systemctl restart mysqld

查看'查询日志'文件

cd /var/lib/mysql && ll;

新开一个会话，方便实时查看'查询日志'文件尾部的内容，-f参数表示实时刷新，tail表示查看文件的尾部内容

tail -f mysql_query.log

去mysql执行一些SQL操作

use bilibili;
show tables;
update stu set age = 18;
select * from stu;
create table stu2(id int,age int);
drop table stu2;

去那个'查询日志'的会话，可以发现记录了我们所有的SQL语句(只记录原本的SQL语句，不记录数据变化) 只要你执行了SQL语句就会被记录，所以该查询日志占用会挺大，因为是条SQL语句就会被记录。当我们 在业务中如果暂时用不上该查询日志文件，那么就不要开启，保持默认关闭就行

开启也没什么事，它能记录所有的SQL语句，用来监控挺不错

运维篇 日志

慢查询日志(前面a_53_0的性能分析里学过一部分) 当某条SQL语句的执行时间超过我们指定的时间，就称这条语句为慢查询语句，该语句就会被记录在慢查询日志，方便我 们后续对这条语句进行优化

慢查询日志记录了所以执行时间超过参数long_query_time设置值，并且扫描记录数不小于min_examined_row_limit的 所有的SQL语句的日志，默认未开启。long_query_time默认为10秒，最小为0，精度可以到微秒

long_query_time默认是10秒，当某条SQL语句的执行时间超过10秒就会被记录到慢查询日志。这个10秒是可以修改的， 我们需要去该mysql的配置文件，如下

#编辑my.cnf配置文件

xxxxxxxxxx
vim /etc/my.cnf

把下面的4行复制粘贴到上面my.cnf的末尾行，并保存退出

#开启慢查询日志
slow_query_log=1
#修改最大执行时间的参数，例如超过2秒就算慢查询
long_query_time=2

重启mysql服务

systemctl restart mysqld

验证如下

需要导入前面导过很多次的200万数据表，分两步导入，先导入表结构，再导入表数据

表结构

CREATE TABLE `tb_sku` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品id',
  `sn` varchar(100) NOT NULL COMMENT '商品条码',
  `name` varchar(200) NOT NULL COMMENT 'SKU名称',
  `price` int(20) NOT NULL COMMENT '价格（分）',
  `num` int(10) NOT NULL COMMENT '库存数量',
  `alert_num` int(11) DEFAULT NULL COMMENT '库存预警数量',
  `image` varchar(200) DEFAULT NULL COMMENT '商品图片',
  `images` varchar(2000) DEFAULT NULL COMMENT '商品图片列表',
  `weight` int(11) DEFAULT NULL COMMENT '重量（克）',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `category_name` varchar(200) DEFAULT NULL COMMENT '类目名称',
  `brand_name` varchar(100) DEFAULT NULL COMMENT '品牌名称',
  `spec` varchar(200) DEFAULT NULL COMMENT '规格',
  `sale_num` int(11) DEFAULT '0' COMMENT '销量',
  `comment_num` int(11) DEFAULT '0' COMMENT '评论数',
  `status` char(1) DEFAULT '1' COMMENT '商品状态 1-正常，2-下架，3-删除',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';

表数据

xxxxxxxxxx
mysql --local-infile=1 -u root -p
set global local_infile=1;
load data local infile '/root/200wdata/tb_sku.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';

准备好200万数据的tb_sku表后，就有了慢查询的环境啦，看查询语句会不会被记录到慢查询日志 #查看'慢查询日志'文件，找一下有没有localhost-slow.log文件

cd /var/lib/mysql && ll;

#新开一个会话，方便实时查看'查询日志'文件尾部的内容，-f参数表示实时刷新，tail表示查看文件的尾部内容

tail -f localhost-slow.log

select * from tb_sku limit 0,10; #查看前十条[1~10]的数据。不会被记录，原因:执行速度<2秒
select * from tb_sku limit 1000000,10; #查看100万后面的10条数据[1000001~10]的数据。会被记录，原因:执行速度>=2秒

localhost-slow.log慢查询日志文件里记录了什么信息 什么时间点、哪个用户、哪个主机、执行了什么SQL语句、执行耗时

下面的也是慢查询日志的笔记，是前面没有讲过的，认真学

默认情况下，不会记录管理语句，也不会记录不使用索引进行查找的查询。解决如下

#修改MySQL的配置文件 /etc/my.cnf 文件

vim /etc/my.cnf

#把下面那四行行粘贴到my.cnf文件里面的末尾行，然后保存退出，表示开启查询日志。0代表关闭，1代表开启

#记录执行较慢的管理语句
log_slow_admin_statements=1
#记录执行较慢的未使用索引的语句
log_queries_not_using_indexes=1

如果你不想你的慢查询日志太乱，那么建议还是把上面两个1改为0，等什么时候有业务需求的时候，再设置为1

重启mysql服务

systemctl restart mysqld

现在你的慢查询日志就非常强大了，只要是查询效率比较低，耗费时间>2秒的，都会被记录

删除tb_sku表

drop table if exists tb_sku;