专栏持续更新中：MySQL详解
一、MySQL Server层日志简介
一个mysql client发起一个连接请求，处理请求的过程如下图所示：
MySQL日志是在MySQL server上生成的，不管更改哪个存储引擎，这些日志都是需要有的，包括：
- 错误日志：记录mysqld服务运行过程中出现的coredump、error、exception等
- 查询日志：记录MySQL Server收到的所有增删改查SQL。由于上线项目的SQL太多了，开启查询日志IO太多导致MySQL效率低下，我们一般都不会开启查询日志，只有调试时才开启
- 二进制日志：记录数据的更改（insert、update、delete、alter …），非常重要，可用于数据恢复，主从复制。主从复制技术依赖于log-bin，主库所有的更改操作都记录在log-bin里，从库从log-bin读取主库所有的操作，自己再执行一遍。
- 慢查询日志：记录了一些执行时间超过指定值的SQL语句，可供开发人员分析耗时SQL，从而针对性优化
查看日志相关变量
代码语言：javascript复制mysql> show variables like 'log%';
---------------------------------------- ----------------------------------------
| Variable_name | Value |
---------------------------------------- ----------------------------------------
| log_bin | ON | -- 是否开启二进制日志
| log_bin_basename | /var/lib/mysql/binlog | -- 二进制日志存储位置
| log_bin_index | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_error | /var/log/mysql/error.log | -- 错误日志存储位置
| log_error_services | log_filter_internal; log_sink_internal |
| log_error_suppression_list | |
| log_error_verbosity | 2 |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_raw | OFF |
| log_replica_updates | ON |
| log_slave_updates | ON |
| log_slow_admin_statements | OFF |
| log_slow_extra | OFF |
| log_slow_replica_statements | OFF |
| log_slow_slave_statements | OFF |
| log_statements_unsafe_for_binlog | ON |
| log_throttle_queries_not_using_indexes | 0 |
| log_timestamps | UTC |
---------------------------------------- ---------------------------------------- 二、配置文件参数
my.cnf
通过set方法只能影响当前session，session关闭后设置失效，如果想要配置永久有效，需要在配置文件上进行设置，然后重启MySQL服务，就可以永久生效
linux下重启mysqld服务的命令：sudo service mysqld restart
我们查看一下配置文件/etc/mysql/my.cnf
- 给出
log-error的路径就是开启了log-error，如果不自定义log-error的路径，默认在data_dir - 在开启
log-bin=mysql-bin的同时还要加上server-id=1(表示当前MySQL Server的身份），否则sudo service mysqld restart无法重启服务 - 设置过期的时间
expire_log_days，因为总有一天磁盘会被这个日志占满，导致服务器不可运行，超过设置时间后日志文件会被删除
三、错误日志
错误日志是 MySQL 中最重要的日志之一，它记录了mysqld 启动和停止，以及服务器在运行过程中发生任何严重错误（coredump，error，exception…）时的相关信息。当数据库出现故障导致无法正常使用时，可以首先查看此日志
mysqld 使用的错误日志名为 host_name.err(host_name 为主机名) ，并默认在参数data_dir(数据目录)指定的目录中写入日志文件
四、查询日志
查询日志记录了client发送的所有SQL语句
由于上线项目sql特别多，开启查询日志IO太多导致MySQL效率低，我们一般都不会开启，只有在调试时才开启，比如通过查看sql发现热点数据从而可以进行缓存
代码语言：javascript复制show global variables like '%genera%';
打开全局变量general_log开关：
五、二进制日志
不是明文，不能直接查看，需要通过mysqlbinlog工具（mysql原生自带）解析binlog日志文件
二进制日志(BINLOG)记录了所有的 DDL(数据定义语言)语句和 DML(数据操纵语言) 语句，但是不包括数据查询语句（不记录select操作，记录的是数据库的更改操作）
语句以“事件”的形式保存，它描述了数据的更改过程。二进制日志对于灾难时的数据恢复起着极其重要的作用。
两个重要的应用场景：主从复制、数据恢复
主从复制：主库所有的更新操作（update、delete、insert、alter …）都记录在binlog中，从库读主库的binlog，把binlog的所有操作在从库上在进行一遍
查看当前的binlog：
代码语言：javascript复制show binary logs; -- show master logs;
binlog默认在MySQL的配置文件/etc/mysql/my.cnf配置的data_dir下
1. 演示binlog记录更改
我们先刷新一下，生成一个新的binlog
切换数据库
更改一下数据
再次查看binlog
我们发现日志的filesize从154字节—>710字节，肯定记录我们刚才的数据更改操作
如果我们直接cat日志查看，会发现不是明文，无法直接查看
我们需要通过mysqlbinlog进行查看，如下：
代码语言：javascript复制mysqlbinlog --no-defaults --database=school --base64-output=decode-rows -v --start-datetime='2022-03-01 00:00:00' --stop-datetime='2022-03-31 00:00:00' mysql-bin.000003 | more- database：指定查看某个库的更改
- base64-output：binlog解码方式
- start-datetime & stop-datetime：指定查看某个时间段内的更改，不写则查看所有的更改
- mysql-bin.000003：查看的二进制日志文件路径
我们查看一下binlog
- @1、@2、@3、@4：表示数据库表的4个字段
- server id：表示我们在
my.cnf中设置的id，用于标识当前MySQL的身份 - at 565、at 621：指的是当前事件在binlog记录的位置，数据恢复的时候使用
2. 演示binlog数据恢复
现在创建数据库mytest，并创建表，添加数据
假如现在有人把库删除了：
这时mytest库的所有表和数据都没有了，然而这些操作都会记录在二进制日志binlog里面
理论上来说，可以从binlog把丢失的数据恢复出来。由于恢复过程也是对数据的修改，所以恢复过程产生的日志也要记录在binlog中，这就需要我们指定binlog恢复区间
我们现在知道，我们建库、建表、插入数据的操作都记录在mysql-bin.00003文件中
我们现在刷新一下，生成一个新的binlog，这就可以让我们接下来数据恢复的操作被记录在mysql-bin.00004文件中，而不会在追加到mysql-bin.00003
我们先查看mysql-bin.00003，找需要恢复的区间
从mysql-bin.000003中拿出区间内所有的操作，通过管道放到MySQL shell上执行
代码语言：javascript复制mysqlbinlog --start-position=775 --stop-position=1410 binlog.000003 | mysql -uroot -p
start-position和stop-position表示左闭右开区间：[start-position, stop-position)
查看一下当前的库
再查看一下表和数据
到这里，数据已经全部恢复了
我们不仅可以通过binlog记录的位置，得到需要恢复的区间，也可以通过binlog记录的时间得到需要恢复的区间
mysqlbinlog --start-datetime='2021-05-06 04:34:32' --stop-datetime='2022-04-24 04:36:02' binlog.000003 | mysql -uroot -p参数为：start-datetime、stop-datetime，也是左闭右开区间
由于binlog有一个过期时间，过期的日志数据都会备份到磁盘上的.sql脚本文件中，没有过期的数据可以直接通过binlog恢复，如果需要恢复过期的数据，通过以下命令即可：
mysql> source ~/data.sql$cat ~/data.sql | mysql -u root -p六、慢查询日志
MySQL可以设置慢查询日志，当SQL执行的时间超过我们设定的时间，那么这些SQL就会被记录在慢查询日志当中
我们通过查看日志，用explain分析这些SQL的执行计划，来判定为什么效率低下，是没有使用到索引？还是索引本身创建的有问题？或者是索引使用到了，但是由于表的数据量太大，花费的时间就是很长，那么此时我们可以把表分成n个小表，比如订单表按年份分成多个小表等
慢查询日志相关的参数如下所示：
慢查询日志记录了包含所有执行时间超过参数 long_query_time（单位：秒）所设置值的 SQL语句的日志，在MySQL上用命令可以查看，如下：
这个值是可以修改的：
现在修改成执行时间超过1秒的SQL都会被记录在慢查询日志当中！可以设置为0.01秒，表示10毫秒
慢查询日志，默认名称是host_name-slow.log，存放在MySQL的配置文件/etc/mysql/my.cnf配置的data_dir下，内容格式显示大致如下：
show profiles命令可有查看sql详细的运行时间，全局变量的名字是：profiling
首先需要：set profiling=on
我正在参与2023腾讯技术创作特训营第三期有奖征文，组队打卡瓜分大奖！
