
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;*/

show variables like '%log_error%';#查看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服务器中提供了多种格式来记录二进制日志，共有三个日志格式，对应的特点如下:

日志格式                  含义
statement                基于SQL语句的日志记录，记录的是SQL语句，对数据进行修改的SQL都会记录在日志文件中
row                          基于行的日志记录，记录的是每一行的数据变更。(MySQL默认的日志格式。是可以修改为其他两种格式的，下面会讲)
mixed                      混合statement和row两种格式，默认采用statement，在某些特殊情况下会自动切换为row进行记录

日志查看: 由于二进制数据的日志不能直接读取，所以需要通过我们学过的二进制日志查询工具mysqlbinlog来查看，如下
mysqlbinlog [参数选项] 日志文件名;
参数选项如下
-d    指定数据库名称，只列出指定的数据库相关操作
-o    忽略掉日志中的前n行命令
-v    将基于行数据的日志数据重构为SQL语句
-w    将基于行数据的日志数据重构为SQL语句，并输出注释信息

日志删除:对于比较繁忙的业务系统，每天生成的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语句
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语句
update stu set age = age - 1;

如何把默认的日志格式row修改为statement或mixed。需要在配置文件里面才能改，如下
vim /etc/my.cnf;
进去编辑页面之后，在最末尾行添加如下
binlog_format=STATEMENT
保存退出之后，重启mysql即可生效
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代表开启
general_log=1 #开启查询日志
general_log_file=/var/lib/mysql/mysql_query.log #日志文件的名字、保存路径。如果不设置，默认是/var/lib/mysql/localhost.log

重启mysql服务
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配置文件
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='商品表';

【表数据】
# 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;*/
