linux12 -MYSQL数据库 --＞12日志管理

文章目录

mysql的日志

日志分类

日志种类	作用
错误日志	记录 MySQL 服务器启动、关闭及运行错误等信息
事务日志	1、redo log重做日志 2、undo log回滚日志
查询日志	记录查询的信息
慢查询日志	记录执行时间超过指定时间的操作
二进制日志	又称binlog日志，以二进制文件的方式记录数据库中除 SELECT 以外的操作
中继日志	备库将主库的二进制日志复制到自己的中继日志中，从而在本地进行重放
通用日志	审计哪个账号、在哪个时段、做了哪些事件

一、mysql错误日志

首次启动会提示错误日志位置
查询错误日志：
[root@db01 ~]# mysqladmin -uroot -p variables |grep -w log_error
| log_error                                                | /var/log/mysqld.log 

variables 其实就是查看mysql的配置文件，很多都是注释掉的，他就走默认的，其实我们可以把配置文件打开，一般都会配置
[root@db01 ~]# vim /etc/my.cnf 
[mysqld] 
#绝对路径 
log_error=/var/log/mysql.errlog 
#相对路径
log_error=mysql.errlog
[root@db01 ~]# touch /var/log/mysql.errlog
[root@db01 ~]# chmod 640 /var/log/mysql.errlog 
[root@db01 ~]# chown mysql.mysql /var/log/mysql.errlog 
[root@db01 ~]# systemctl restart mysqld 
#结论：
1.默认是开启的
2.路径在 $datadir/data/ 下面，以主机名.err来命名
3.可以修改我们指定的地址
[root@db01 ~]# vim /etc/my.cnf
log_error=/var/log/mysql.errlog 
4.查看错误日志方式
# 方式一
mysqladmin -uroot -p variables | grep -w log_error
# 方式二
mysql> show variables like '%log_error%';

1、配置错误日志（默认就是启用的）

[root@db01 ~]# vim /etc/my.cnf 
[mysqld] 
#绝对路径 
log_error=/var/log/mysql.errlog 
#相对路径
log_error=mysql.errlog
[root@db01 ~]# touch /var/log/mysql.errlog
[root@db01 ~]# chmod 640 /var/log/mysql.errlog 
[root@db01 ~]# chown mysql.mysql /var/log/mysql.errlog 
[root@db01 ~]# systemctl restart mysqld

MySQL中，其中log_error定义是否启用错误日志的功能和错误日志的存储位置，此外还可以用参数控制

是否将告警信息（warning messages）也写入错误日志。在不同的版本中改控制参数有所不同

2、在MySQL 5.6中用log_warnings参数

# 1、log_warnings=0， 表示不记录告警信息。
# 2、log_warnings=1(默认值)， 表示告警信息写入错误日志。 
# 3、log_warnings大于1，表示各类告警信息，例如有关网络故障的信息和重新连接信息写入错误日志。

3、mysql5.7新增的log_error_verbosity参数

# 它有三个可选值， 分别对应：
# 1、log_error_verbosity=1：错误信息；
# 2、log_error_verbosity=2：错误信息和告警信息；(推荐)
# 3、log_error_verbosity=3（默认值就是3）：错误信息、告警信息和通知信息。

二、一般查询日志

1、一般查询日志

1.默认是关闭的 
一般不会开启，因为哪怕你开启事务一顿操作，最后不提交也会记录，生产上程序跑sql很多，会非常 非常占地方，从来都不启动，要看操作去binlog 
2.开启
[root@db01 ~]# vim /etc/my.cnf general_log=on
general_log_file=/var/log/select.log 
set global general_log=on
#可以使用set global general_log=on;设置 

[root@db01 ~]# touch /var/log/select.log 
[root@db01 ~]# chmod 640 /var/log/select.log 
[root@db01 ~]# chown mysql.mysql /var/log/select.log 
[root@db01 ~]# systemctl restart mysqld
3.查看一般查询日志 
# 方式一
[root@db01 ~]# mysqladmin -uroot -p'Mm2021@Ping' variables|grep general_log 
# 方式二
[root@db01 ~]# mysql -uroot -p'Mm2021@Ping' mysql> show variables like '%gen%';

mysql的好处就是编译完了不是一定得根据他编译的配置走，可以去配置自己想要的内容
# 一般不会开启，因为哪怕你开启事务一顿操作，最后不提交也会记录，生产上程序跑sql很多，会非常非常占地方，从来都不启动，要看操作去binlog

三、二进制日志

1、二进制日志简介

二进制日志即binlog日志，记录了mysql数据库所有的dml,ddl语句事件（不包含select）。
记录增删改，也可以记录SQL语句及行记录变化，还可以记录这些操作事件；总之会记录所有修改操作的SQL语句。

2、不要混淆以下三种日志：

# 1、general log：记录数据库里的所有SQL操作记录 
# 2、redo log：只记录innodb存储引擎的修改日志 
# 3、binlog：只记录server层面内部的修改情况。--select /show 不记录

3、开启binlog日志的好处

1.记录已提交的DML事务语句，并拆分为多个事件（event）来进行记录
2.记录所有DDL、DCL等语句
3.总之，二进制日志会记录所有对数据库发生修改的操作
	
# 记录数据库,增,删,改的SQL语句
1.如果我拥有数据库搭建开始所有的二进制日志，那么我可以把数据恢复到任意时刻
2.mysql主从复制，通过binlog实现数据复制
3.数据恢复：可以基于时间点恢复，以及根据其进行增量与差异备份

4、二进制日志工作模式

1、查看工作模式

#查看工作模式
mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

2、语句模式

语句模式：binlog_format=statement（Mysql5.7.6之前的默认级别）

# 语句模式介绍 
记录对数据库做出修改的sql语句,不记录该sql的上下文信息 
#语句模式（MySQL5.6默认）STATEMENT：
	主要是记录增，删，改的SQL语句

# 例如
1、开启binlog日志后，我们在某个库下自定义函数，若想定义成功需要先设置配置项 
set global log_bin_trust_function_creators=TRUE; 
2、然后自定义函数
delimiter //
create function f1( 
    i1 int, 
    i2 int) 
returns int 
BEGIN
    declare num int; 
    set num = i1 + i2; 
    return(num);
END //
delimiter ;

3、最后执行一条插入语句 
insert into t1(name) values(concat("egon",f1(1,2))); 

如果使用statement模式，那么这条insert语句将会被记录到二进制日志中,而sql语句中依赖的f1 的定义是不会记录下来的，f1只存在于当前库。
# 优点 
不需要记录细到每一行数据的更改变化，只需要记录该条语句即可
因此，binlog日志量小，IO压力小，性能较高

例如:
一条语句修改了100万行，该模式下只需要记录下该条语句即可 

# 缺点
日志中记录的sql语句可能有上下文依赖，此时脱离了当前数据库环就无法运行了，因此该模式下容易 出现主从不一致的问题。
例如：
    主库记录的某条sql语句引用了主库中的函数、触发器、存储过程等特殊功能 
    在从库上接收了该sql之后，可能就无法正确运行，从而主从库数据不一致的问题。
    
ps：row模式是基于每一行来记录变化的，所以不会出现类似的问题。 

# 应用场景
sql语句对mysql内置功能依赖比较少：不使用存储过程/触发器/函数，可以使用该模式，否则还是推 荐行级模式

====================================================================================
#查看binlog，看看有没有记录，二进制文件我们看不了，有专门的命令
[root@db01 data]# mysqlbinlog mysql-bin.000001

#注意：
  binlog 默认多大？120	（系统占用的一部分）
  binlog起始位置点是哪里？show master status
  binlog位置点是跟着大小走的

#binlog可以用来恢复数据
#修改数据
  mysql> begin;
  mysql> update binlog set id=200 where id=2;
  mysql> commit;
#发现自己修改错了
  mysql> select  from binlog;
#回滚，回滚不了，已经提交了
   mysql> rollback;
   mysql> select  from binlog;
#一怒之下删表
   mysql> drop table binlog;
#恢复数据
查看binlog到底应该恢复到哪个位置点，到处成SQL
[root@db01 data]# mysqlbinlog --start-position=120 --stop-position=549 /service/mysql/data/mysql-bin.000001  > /tmp/binlog.sql
导入SQL
[root@db01 data]# mysql -uroot -p123 < /tmp/binlog.sql

#优缺点：
1.易读
2.占用磁盘空间小
3.不严谨

3、行级模式

行级模式：binlog_format=row,（mysql5.7.6之后+8.0的默认级别）

# 行级模式介绍
记录每一行数据修改的细节，即哪一条记录被修改了，修改成什么样了

例如：执行语句（f1参照上例，此处略）
insert into t1(name) values(concat("egon",f1(1,2)));

如果使用row模式，那么日志中会记录插入了一条新记录，记录中的name字段值为'egon3' 
# 优点
相当于把上下文依赖都记录了下来，可以更方便查看每一条数据修改的细节，并且不会出现某些特定 情况下的存储过程或function以及trigger的调用和触发无法被正确复制的问题，即该模式下主从复 制强一致，数据最安全。

# 缺点
日志量大 例如 一条语句修改了100万行，语句模式下只需要记录一条语句即可 而行级模式却修改记录下100万行的修改记录，binlog日志的量可能会大得惊人。 
# 应用 
sql语句对mysql内置功能依赖比较多，希望数据最安全，复制强一致的场景推荐行级模式

#行级模式（MySQL5.7默认）ROW：
	主要是记录SQL语句,修改数据变化过程	
===================================================================================
#修改binlog为行级模式
[root@db01 data]# vim /etc/my.cnf
[mysqld]
binlog_format=row
[root@db01 data]# /etc/init.d/mysqld restart

#验证：
	#建表、插入数据、修改数据
	use test;
	create table binlog(id int);
	begin;
	insert into binlog values(1),(2),(3);
	update binlog set id=10 where id=1;
	commit;

#再次查看binlog
[root@db01 data]# mysqlbinlog mysql-bin.000002
#这个时候我们是看不懂他写的内容的，因为行模式就是这样子的，需要加上参数
[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000002
#优缺点：企业喜欢使用这个模式
	1.严谨,不容易丢失数据
	2.日志文件会越来越大,占用磁盘空间
	3.不易读
#修改错误数据
mysql> update test22 set id=100;
#修复数据前最好将表删除，否则会报已存在，或者把起始位置点修改成建表之后
[root@db01 data]# mysqlbinlog --start-position=120 --stop-position=518 /service/mysql/data/mysql-bin.000002  > /tmp/test22.sql
[root@db01 data]# mysql -uroot -p123 < /tmp/test22.sql

4、混合模式

混合模式：binlog_format=mixed，一般不用

语句模式和行级模式混合使用，一般不适用，只使用行级模式

# 混合模式介绍 
row level 与statement level的优点 
混合（mixed-based）模式默认采用语句模式记录日志，在一些特定的情况下会将记录模式切换为行 级模式记录，这些特殊情况包含但不限于以下情况。
•当函数中包含UUID()时。
•当表中有自增列（AUTO_INCREMENT）被更新时。
•当执行触发器（trigger）或者存储过程（stored function）等特殊功能时。
•当FOUND_ROWS()、ROW_COUNT()、USER()、CURRENT_USER()、CURRENT_USER等执行时。
# 应用场景 
看上去这种方式似乎比较美好，但是在生产环境中，为了保险起见，一般会使用row模式。

5、结合实例升级数据库

#1.提出方案升级数据库
#2.准备一台新机器，搭建mysql 5.6.48版本
#3.在旧数据库上备份所有数据（除了系统库）
	mysqldump -uroot -p123 -R --trigers -B world --master-data=2 > /tmp/123.sql
	--master-data=2 参数会在导出的数据加一行 位置点的记录
#4.将备份的数据传到新的数据库上
	scp  rsync  NFS  物理
#5.模拟旧的库有数据写入
	insert into test22 values(5),(6)
	update test22 set id=500 where id=3;
#6.将修改的数据导入数据库
	mysql -uroot -p123 < /tmp/123.sql
#7.将测试环境连接到新的数据库测试功能，业务
#8.测试没有问题切换生产环境数据库配置
#9.停止生产旧的数据库，使用binlog截取数据传输时新的数据
  通过sql里面的位置点截取binlog找到新增数据，导出sql语句
[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000002
[root@db01 data]# mysqlbinlog --start-position=120 --stop-position=518 /service/mysql/data/mysql-bin.000002  > /tmp/test22.sql
#10.将新增数据导入新的数据库
#11.完成

四、二进制日志的管理操作

1、binlog日志相关概念

事件介绍
1）在binlog中最小的记录单元为event
2）一个事务会被拆分成多个事件（event）

# 1、事件（event）特性
1）每个event都有一个开始位置（start position）和结束位置（stop position）。
2）所谓的位置就是event对整个二进制的文件的相对位置。
3）对于一个二进制日志中，前120个position是文件格式信息预留空间。
4）MySQL第一个记录的事件，都是从120开始的。
# 2、位置position

2、二进制日志相关参数

# (1) server_id
服务ID,主从库必须不一样，建议数字为:ip+端口，5.7.3以后版本,必须指定
# (2) log-bin
此变量用于控制是否开启二进制日志，而且这是一个只读变量，默认值为OFF
示例： 
log-bin=/var/lib/mysql/mybinlog #绝对路径 
# log-bin=mybinlog #也可以用相对路径， 会在$datadir下产生mysqlbinlog-00000N 1234567
# (3) log_bin_index
不设置的话，会根据log_bin值名称自动生成mybinlog.index log_bin_index=var/lib/mysql/mybinlog.index
# (4) sql_log_bin
此变量用于标识当前会话中的操作是否会被记录于二进制日志，此变量值设置为ON #临时开启和关闭
# (5) binlog_format
此变量的值决定了二进制日志的记录方式，此变量的值可以设置为 statement、row、mixed，分别表示以语句的形式记录二进制日志，以数据修改的形式记录二进制日 志，以混合的方式记录二进制日志，安全保险起见，推荐使用row的方式记录。
# (6) max_binlog_size
设置单个二进制日志文件的最大大小
# (7) sync_binlog
在存储引擎章节中我们提过： innodb_flush_log_at_trx_commit控制redo日志的刷盘策略，建议值为1 sync_binlog控制binlog日志的刷盘策略，建议值为1
# (8) 其他参数
#打开才能查看详细记录，默认为off 
binlog_rows_query_log_events=on 

#表示自动删除10天以前的日志
expire_logs_days=10 
# full,minimal,noblob分别表示binlog中内容全记录，只记录被操作的，和不记录二进制 
binlog_row_image=full #（full,minimal,noblob），

3、查看binlog配置项

show variables like '%log_bin%';
show variables like '%binlog%'; 
show variables like '%binlog_format%';
show variables like '%server%'; 
show variables like 'expire_logs_days'; -- 过期日志天数
# 或者 
[root@db01 ~]# mysqladmin -uroot -pEgon@123 variables |grep -w log_bin

4、开启二进制日志

1.默认是关闭的 
2.配置开启binlog 
vim /etc/my.cnf 
[mysqld] 
server_id=1 
log-bin=/var/lib/mysql/mybinlog 
binlog_format='row' #(row,statement,mixed)，不建议随意去修改binlog工作模式 
binlog_rows_query_log_events=on
max_binlog_size=100M

#5.7中必须配置server_id
[root@db01 data]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format=row
server_id=1

5、二进制日志的操作

1、查看日志日志名、状态、事件

# 查看日志日志名
show binary logs;  
show master logs;    
# 查看状态
show master status; 
# 查看细部的状态和事件
show binlog events in 'mybinlog.000002'; 
show binlog events in 'mybinlog.000002' limit 3;

2、查看日志内容

# 1、执行下述sql 
create database if not exists test; 
use test; 
create table user(name varchar(10));
begin; 
insert into user values("egon1"),("egon2"),("egon3"); 
update user set name="EGON" where name="egon1"; 
commit;
show master status; -- binlog的当前position 

查看binlog
# 看看有没有记录，二进制文件我们看不了，有专门的命令 
# show master status;
查看全部：
# mysqlbinlog mybinlog.000002
按时间：
# mysqlbinlog mybinlog.000002 --start-datetime="2022-11-05 10:02:56" 
# mysqlbinlog mybinlog.000002 --stop-datetime="2022-11-05 11:02:54
按字节数：
# mysqlbinlog mybinlog.000002 --start-position=337 
# mysqlbinlog mybinlog.000002 --stop-position=662 
# mysqlbinlog mybinlog.000002 --start-position=337 --stop-position=662 

如果是行级模式，想要看懂详细内容则需要加上额外参数，但是仅用于看懂内容，如果要用于还原数 据，还是应该去掉额外的参数并将内容定位到文件中 
# mysqlbinlog --base64-output=decode-rows -vvv mybinlog.000002 # 仅用于查看，不 能用于日后的数据恢复 
#mysqlbinlog mybinlog.000002 --start-position=100 > /tmp/1.sql # /tmp/1.sql 可用于日后的数据恢复

=================================================================================
[root@db01 mysql]# mysqlbinlog mysql-bin.000001 
mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8mb4'
# 如果直接用mysqlbinlog回复数据  =========失败  ================= utf8mb4注释就好
[root@db01 mysql]# vim /etc/my.cnf
[mysql]
# default-character-set=utf8mb4
[root@db01 mysql]# ystemctl restart mysqld

# 注： 字符问题
查看全部：
#   mysqlbinlog --no-defaults mysql-bin.000001

3、binlolg可以用来恢复数据

#修改数据 
begin;
update user set name="XXX" where name="egon2";
commit;
#发现自己修改错了 
select  from user; 
#回滚，回滚不了，已经提交了
rollback; select  from user; 
#一怒之下删表 
drop table user;
#恢复数据:查看binlog数据的起始点与要恢复到的位置点，导出成SQL 
mysqlbinlog mybinlog.000002 --stop-position=772 > /tmp/binlog.sql
mysql -uroot -pEgon@123 < /tmp/binlog.sql

6、刷新与清除二进制日志

1、刷新binlog

#刷新binlog：关闭当前的二进制日志文件并创建一个新文件 
1）手动执行命令刷新
flush logs;
或者
mysqladmin -uroot -p flush-logs;
或者
mysql -uroot -pEgon@123 -e 'flush logs'

2）重启数据库时会刷新
3）二进制日志上限（max_binlog_size）;当binlog达到1G,自动刷新

2、删除binlog

#删除二进制日志原则
在存储能力范围内，能多保留则多保留
基于上一次全备前的可以选择删除

# 1) 删除所有binlog，相当于重置
reset master; 
# 2) 删除指定binlog名之前的所有binlog(保留指定的binlog) 
purge binary logs to 'mybinlog.00003';-- mybinlog.00003之前的都删除掉
# 3）删除日期之前的日志：手动执行
PURGE {MASTER | BINARY} LOGS BEFORE 'date' --用于删除日期之前的日志，BEFORE变量的 
date自变量可以为'YYYY-MM-DD hh:mm:ss'格式
如：（MASTER 和BINARY 在这里都是等效的）
PURGE MASTER LOGS TO 'mybinlog.00003'; 
purge binary logs before '2021-07-13 19:11:00'; 
还可以做减法：如只保留3天的 
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day; 
# 4）删除日期之前的日志：修改配置参数，让mysql自动执行
删除7天前的binlog 
#临时生效 
SET GLOBAL expire_logs_days = 7; 
#永久生效 
[root@db01 data]# vim /etc/my.cnf
[mysqld] 
expire_logs_days = 7

ansible做主从的时候有的人因为名字和位置点不好获取，他每次都会reset，如果先同步了所有数据那没问题，如果没同步问题就大了；

五、实战演练使用binlog恢复数据

1.对数据库一顿操作

#查看binlog信息
mysql> show master status;
#创建一个binlog库
mysql> create database binlog;
#使用binlog库
mysql> use binlog
#创建binglog_table表
mysql> create table binlog_table(id int);
#查看binlog信息
mysql> show master status;
#插入数据1
mysql> insert into binlog_table values(1);
#查看binlog信息
mysql> show master status;
#提交
mysql> commit;
#查看binlog信息
mysql> show master status;
#插入数据2
mysql> insert into binlog_table values(2);
#插入数据3
mysql> insert into binlog_table values(3);
#查看binlog信息
mysql> show master status;
#提交
mysql> commit;
#删除数据1
mysql> delete from binlog_table where id=1;
#查看binlog信息
mysql> show master status;
#提交
mysql> commit;
#更改数据2为22
mysql> update binlog_table set id=22 where id=2;
#查看binlog
mysql> show master status;
#提交
mysql> commit;
#查看binlog信息
mysql> show master status;
#查看数据
mysql> select  from binlog_table;
#删表
mysql> drop table binlog_table;
#删库
mysql> drop database binlog;

2.恢复数据

#查看binlog事件
mysql> show binlog events in 'mysql-bin.000013';
#使用mysqlbinlog来查看
[root@db01 data]# mysqlbinlog /application/mysql/data/mysql-bin.000013
[root@db01 data]# mysqlbinlog /application/mysql/data/mysql-bin.000013|grep -v SET
[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000013
### UPDATE `binlog`.`binlog_table`
### WHERE
###   @1=2 / INT meta=0 nullable=1 is_null=0 /
### SET
###   @1=22 / INT meta=0 nullable=1 is_null=0 /
#分析
update binlog.binlog_table
set
@1=22 --------->@1表示binlog_table中的第一列,集合表结构就是id=22
where
@1=2  --------->@1表示binlog_table中的第一列,集合表结构就是id=2
#结果
update binlog.binlog_table set id=22 where id=2;
#截取二进制日志
查看二进制日志后，发现delete语句开始位置是858
[root@db01 data]# mysqlbinlog --start-position=120 --stop-position=858 /application/mysql/data/mysql-bin.000013
#临时关闭binlog
mysql> set sql_log_bin=0;
#执行sql文件
mysql> source /tmp/binlog.sql
#查看删除的库
mysql> show databases;
#进binlog库
mysql> use binlog
#查看删除的表
mysql> show tables;
#查看表中内容
mysql> select  from binlog_table;

3、问题：

数据库或表被误删除的是很久之前创建的（一年前，生成了上百个binlog）
如果基于binlog全量恢复，成本很高
1）可以用备份恢复+短时间内二进制日志，恢复到故障之前
2）非官方方法，binlog2sql，binlog取反，类似于Oracle的flushback
3）延时从库 如果同一时间内和故障库无关的数据库都有操作，在截取binlog时都会被截取到

#都是废话，数据库出了有binlog可以恢复数据以外，还有全备，每天或者每周全被一次，全备之后，那个位置点之前的binlog全都可以删除，不可能一年有上百个binlog

生产中很多库，只有一个库的表被删除，我不可能把所有的库都导出来筛选，想一个办法过滤出来？

1、grep？
其他过滤方案？
2、-d 参数接库名
mysqlbinlog -d database --base64-output=decode-rows -vvv mysql-bin.000002
--base64-output  显示模式
-vvv			显示详细信息

六、慢查询日志

1、慢日志作用

1）是将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件(不论是什么语句，增删改查)
2）通过对这些特殊的SQL语句分析，改进以达到提高数据库性能的目的

2.配置慢日志

#默认慢日志是不开启的
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
#指定是否开启慢查询日志
slow_query_log = 1
#指定慢日志文件存放位置（默认在data）
slow_query_log_file=/var/log/slow.log
#设定慢查询的阀值(默认10s)
long_query_time=0.05
#不使用索引的慢查询日志是否记录到日志
log_queries_not_using_indexes
#查询检查返回少于该参数指定行的SQL不被记录到慢查询日志，少于100行的sql语句查询慢的话不记录，一般不使用
min_examined_row_limit=100（鸡肋）
执行下述命令
touch /var/log/slow.log
chmod 640 /var/log/slow.log
chown mysql.mysql /var/log/slow.log 
systemctl restart mysqld

3.测试慢日志

# 测试方式一
测试:BENCHMARK(count,expr) 
SELECT BENCHMARK(50000000,23); 
#执行卡死，查看执行的sql执行时间，如果停不下来 可以 kill id
show processlist;
kill 3；
# 测试方式二
insert city_new select  from city;
insert city_new select  from city_new;
insert city_new select  from city_new;
insert city_new select  from city_new;
... ...

# 查看方式一
查看慢日志可以使用
cat /service/mysql/data/slow.log
#看起来太费劲，我们使用以下命令
# 查看方式二
mysqldumpslow -s r -t 10 /service/mysql/data/slow.log
得到按照时间排序的前10条里面含有左连接的查询语句
	参数说明:
	-s:
	是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；
	-t:
	是top n的意思，即为返回前面多少条的数据；
	-g:
	后边可以写一个正则匹配模式，大小写不敏感的；
# 查看方式三
# 第三方工具查看慢日志
yum provides pt-query-digest

# 查看慢查询日志中
mysql> select * from t1;  #全局扫描会记录慢查询日志
+----+------+
| id | name |
+----+------+
|  1 | mm   |
|  2 | kk   |
|  3 | mdm  |
|  4 | fg   |
+----+------+
4 rows in set (0.00 sec)

mysql> select * from t1 where id=4; #不会记录慢查询日志
+----+------+
| id | name |
+----+------+
|  4 | fg   |
+----+------+

4、慢查询日志优化

如何定位运行速度慢的sql：# 开启慢查询

如何分析sql：# explain工具
如何优化sql：
   1、逻辑（能链表就不要子查询）====》rows是否变低
   2、对查询范围大的sql建议：分段取、缓存
   3、建立索引
     尽量给占用空间小，标签重复度低
     不要让索引字段参与运算
     尽量减少范围（能分段取就不要一下全取）
     能覆盖索引就不要回表（尽量主键字段作为查询条件）
     多条sql语句用的是and连接的多个条件，而条件中都出现某一个字段，可以创建联合索引

posted @ 2021-07-19 17:08 博客之记阅读(73) 评论(0) 编辑收藏举报

刷新页面返回顶部