![](https://www.yuandangsheng.top/"wp-content/uploads"/2023/04/特色图像-mysql.png)
MySql日志类型
事务日志 transaction log
中继日志 reley log
错误日志 error log
通用日志 general log
慢查询日志 slow query log
二进制日志 binary log
事务日志
- 事务日志：transaction log
事务型存储引擎自行管理和使用，建议和数据文件分开存放更改事务日志路径 mkdir /data/mysqllogs/ 创建自定义事务日志目录 chown mysql.mysql /data/mysqllogs/ 更改权限 vim /etc/my.cnf 更改配置文件 [mysqld] mysqld语句块下 innodb_log_group_home_dir=/data/mysqllogs/ 指定事务日志文件路径redo log
undo log
Innodb事务日志相关配置：
show variables like '%innodb_log%';
innodb_log_file_size 5242880 每个日志文件大小,默认大小为5M
innodb_log_files_in_group 2 日志组成员个数
innodb_log_group_home_dir ./ 事务文件路径
注意：./是指/var/lib/mysql - 中继日志：relay log
主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件开启事务，执行数据库写操作，在提交之前进行事务回滚，回滚完毕后数据库大小不会更改，即执行命令把数据写入数据库，数据库文件变大，但是取消该操作，数据库文件大小不会自动缩减会原来的大小 使用以下命令释放多余的空间：optimize table testlog;
错误日志
- 错误日志
mysqld启动和关闭过程中输出的事件信息
mysqld运行中产生的错误信息
event scheduler运行一个event时产生的日志信息
在主从复制架构中的从服务器上启动从服务器线程时产生的信息 - 错误日志相关配置
SHOW GLOBAL VARIABLES LIKE 'log_error'
错误文件路径：
log_error=/PATH/TO/LOG_ERROR_FILE
是否记录警告信息至错误日志文件
log_warnings=1|0 默认值1
更改错误日志路径：配置文件/etc/my.cnf下 [mysqld_safe] log-error=/var/log/mariadb/mariadb.log
通用日志
- 通用日志：记录对数据库的通用操作，包括错误的SQL语句，会造成日志量较多，占用空间较大，一般情况下不用开启，系统默认为关闭状态
文件：file，默认值
表：table - 通用日志相关设置
general_log=ON|OFF
开启日志，系统会在/var/lib/mysql/目录下自动生成centos7.log文件存放通用日志
general_log_file=HOSTNAME.log
log_output=TABLE|FILE|NONE
示例：
查看mysql数据库中的general_log表
MariaDB [mysql]> use mysql;
MariaDB [mysql]> select * from general_log;
+----------------------------+---------------------------+-----------+-----------+--------------+---------------------------+
| event_time | user_host | thread_id | server_id | command_type | argument |
+----------------------------+---------------------------+-----------+-----------+--------------+---------------------------+
| 2018-10-12 15:09:44.271858 | root[root] @ localhost [] | 2 | 0 | Query | select * from general_log |
| 2018-10-12 15:09:51.461674 | root[root] @ localhost [] | 2 | 0 | Query | select * from general_log |
+----------------------------+---------------------------+-----------+-----------+--------------+---------------------------+
mysql -e 'select * from mysql.general_log' > test.log 把日志从数据库导出慢查询日志
- 慢查询日志：记录执行查询时长超出指定时长的操作
slow_query_log=ON|OFF 开启或关闭慢查询
long_query_time=N 慢查询的阀值，单位秒
slow_query_log_file=HOSTNAME-slow.log 慢查询日志文件
log_slow_filter = admin,filesort,filesort_on_disk,full_join,
full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk 上述查询类型且查询时长超过long_query_time，则记录日志
log_queries_not_using_indexes=ON 不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语句是否记录日志，默认OFF，即不记录
log_slow_rate_limit = 1 多少次查询才记录，mariadb特有
log_slow_verbosity= Query_plan,explain 记录内容
log_slow_queries = OFF 同slow_query_log 新版已废弃
示例：
开启慢查询日志
set global slow_query_log=on;
慢查询日志位置：/var/lib/mysql/centos7-slow.log
set long_query_time=3 时间阀值，默认为10秒，即命令执行超过10秒就会被记录,更改为3秒
或：在日志文件中更改(永久保存)
vim /etc/my.cnf
[mysqld]
slow_query_log=on 开启慢查询日志
long_query_time=3 设置时间阀值为3秒，即超过3秒就记录下来
测试：
select sleep(2) from teachers; teachers表有4条记录，每条记录sleep2秒，该命令执行需要8秒
查看慢查询日志
cat /var/lib/mysql/centos7-slow.log知识扩展：
查询命令中有多个子句，查看每个子句查询所占用的时间
show variables like '%profiling%'; 查询变量
set profiling=on 开启命令分析功能
show profiles 查看命令执行过程，查看执行命令id号为1
show profile for query 1 查看命令执行详细过程，可以查看sql命令过程中每一步骤占用时间
示例：
MariaDB [hellodb]> select * from testlog where name="wang99000";
+-------+-----------+-------+
| id | name | age |
+-------+-----------+-------+
| 99000 | wang99000 | 99000 |
+-------+-----------+-------+
1 row in set (0.00 sec)
MariaDB [hellodb]> show profiles;
+----------+------------+----------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------------------------+
| 1 | 0.00006964 | select * from testlog where name="wang99000" |
+----------+------------+----------------------------------------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> show profile for query 1 ;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000020 |
| Waiting for query cache lock | 0.000005 |
| checking query cache for query | 0.000007 |
| checking privileges on cached | 0.000003 |
| checking permissions | 0.000017 |
| sending cached result to clien | 0.000010 |
| updating status | 0.000004 |
| cleaning up | 0.000003 |
+--------------------------------+----------+二进制日志
- 二进制日志
记录导致数据改变或潜在导致数据改变的SQL语句
记录已提交的日志
不依赖于存储引擎类型
功能：通过“重放”日志文件中的事件来生成数据副本
注意：建议二进制日志和数据文件分开存放
注意：
二进制日志与事务日志的区别：
1、事务日志依赖于事务功能，依赖于存储引擎；二进制日志并不依赖于存储引擎
2、二进制日志记录数据库已经确定的增删改操作，也即是只记录事务提交过的操作；事务日志记录的是提交过的以及未提交的操作
3、事务日志文件大小确定，默认为5M，因此日志文件在写的过程中不断覆盖旧日志，会丢失以前的日志文件；二进制日志是不断累积的文件，系统默认不会被覆盖和删除
二进制日志记录执行过程，并不记录数据库初始状态，因此二进制日志需要与备份文件相结合才能发挥作用- 二进制日志记录格式
二进制日志记录三种格式
基于“语句”记录：statement，记录语句，默认模式如某条命令更改了很多条记录，但该日志只记录这条命令，并不记录结果 update students set age=20； 只记录该命令 有时会造成有些记录无法还原，如某学员的生日为update students set birth=now()，还原时执行该命令，日期无法还原为正确的日期基于“行”记录：row，记录数据，日志量较大
记录数据库中每一行的更改记录 update students set age=20； 记录该命令更改的每一条记录 可以完全还原，但是产生的日志量最大混合模式：mixed, 让系统自行判定该基于哪种方式进行
格式配置
show variables like ‘binlog_format';mariadb10.2.3版本以前使用statement语句模式 mariadb10.2.4版本以后使用mixed混合模式 - 二进制日志文件的构成
有两类文件
日志文件：mysql|mariadb-bin.文件名后缀，二进制格式
如： mariadb-bin.000001
索引文件：mysql|mariadb-bin.index，文本格式 - 二进制日志相关的服务器变量：
sql_log_bin=ON|OFF：是否记录二进制日志，默认ON
log_bin=/PATH/BIN_LOG_FILE：指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开启才可
示例：
把此选项写入配置文件，即使不指定路径，也可以开启功能
log_bin写入配置文件时需要指定文件路径，即log_bin=mysql-bin；如果不指定路径，则系统默认文件名为/var/lib/mysql/目录下的mariadb-bin.000001和mariadb-bin.index
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin 指定文件名为mysql-bin
mysql-bin.index 记录当前有效的二进制日志文件是哪些文件
[root@centos7 mysql]# cat mariadb-bin.index
./mariadb-bin.000001
注意：重启数据库服务，二进制日志文件会自动增加 binlog_format=STATEMENT|ROW|MIXED：二进制日志记录的格式，默认STATEMENT
max_binlog_size=1073741824：单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G
说明：文件达到上限时的大小未必为指定的精确值
sync_binlog=1|0：设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘
expire_logs_days=N：二进制日志可以自动删除的天数。默认为0，即不自动删除
- 二进制日志相关配置
查看mariadb自行管理使用中的二进制日志文件列表，及大小
SHOW {BINARY | MASTER} LOGS
查看使用中的二进制日志文件
SHOW MASTER STATUS
查看二进制文件中的指定内容
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
show binlog events in ‘mysql-bin.000001' from 6516 limit 2,3
示例：
查看二进制文件中的指定内容
MariaDB [hellodb]> show binlog events in 'mysql-bin.000001' from 48355435 ;
+------------------+----------+------------+-----------+-------------+--------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+----------+------------+-----------+-------------+--------------------------------------------------------------+
| mysql-bin.000001 | 48355435 | Query | 1 | 48355506 | BEGIN |
| mysql-bin.000001 | 48355506 | Query | 1 | 48355617 | use `hellodb`; insert students values(26,'wang',20,'m',10,1) |
| mysql-bin.000001 | 48355617 | Xid | 1 | 48355644 | COMMIT /* xid=600022 */ |
| mysql-bin.000001 | 48355644 | Query | 1 | 48355715 | BEGIN |
| mysql-bin.000001 | 48355715 | Query | 1 | 48355824 | use `hellodb`; insert students values(27,'li',30,'m',15,1) |
| mysql-bin.000001 | 48355824 | Xid | 1 | 48355851 | COMMIT /* xid=600024 */ |
+------------------+----------+------------+-----------+-------------+--------------------------------------------------------------+
MariaDB [hellodb]> show binlog events in 'mysql-bin.000001' from 48355435 limit 2,3;
+------------------+----------+------------+-----------+-------------+------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+----------+------------+-----------+-------------+------------------------------------------------------------+
| mysql-bin.000001 | 48355617 | Xid | 1 | 48355644 | COMMIT /* xid=600022 */ |
| mysql-bin.000001 | 48355644 | Query | 1 | 48355715 | BEGIN |
| mysql-bin.000001 | 48355715 | Query | 1 | 48355824 | use `hellodb`; insert students values(27,'li',30,'m',15,1) |
+------------------+----------+------------+-----------+-------------+------------------------------------------------------------+日志文件以及相关命令
- mysqlbinlog：二进制日志的客户端命令工具
- 命令格式：
mysqlbinlog [OPTIONS] log_file…
--start-position=# 指定开始位置
--stop-position=#
--start-datetime=
--stop-datetime=
时间格式：YYYY-MM-DD hh:mm:ss
--base64-output[=name]
示例：mysqlbinlog --start-position=6787 --stop-position=7527 /var/lib/mysql/mariadb-bin.000003 mysqlbinlog --start-datetime="2018-01-30 20:30:10" --stop-datetime="2018-01-30 20:35:22" mariadb-bin.000003 - 二进制日志事件的格式：
格式如下：
#at 328
#151105 16:31:40 server id 1 end_log_pos 431 Query thread_id=1 exec_time=0 error_code=0
use \`mydb`/*!*/;
SET TIMESTAMP=1446712300/*!*/;
CREATE TABLE tb1 (id int, name char(30))
/\*!\*/; 事件发生的日期和时间：151105 16:31:40
事件发生的服务器标识：server id 1
事件的结束位置：end_log_pos 431
事件的类型：Query
事件发生时所在服务器执行此事件的线程的ID：thread_id=1
语句的时间戳与将其写入二进制文件中的时间差：exec_time=0
错误代码：error_code=0
事件内容：
GTID：Global Transaction ID，mysql5.6以mariadb10以上版本专属属性：GTID
- 清除指定二进制日志：
PURGE { BINARY | MASTER } LOGS
{ TO 'log_name' | BEFORE datetime_expr }
示例：
PURGE BINARY LOGS TO ‘mariadb-bin.000003’;删除3之前的日志
PURGE BINARY LOGS BEFORE '2017-01-23';
PURGE BINARY LOGS BEFORE '2017-03-22 09:25:30';- 删除所有二进制日志，index文件重新记数
RESET MASTER [TO #]; 日志文件从#开始记数，默认从1开始，一般是master第一次启动时执行，MariaDB10.1.6开始支持TO # - 切换日志文件：
FLUSH LOGS;
切换日志使用场景：需要做阶段性备份，把新日志和旧日志(已备份)分开存放
文章评论