MySQL之日志管理
日志
事务日志:transaction log
错误日志:error log
查询日志:query log
慢查询日志:slow query log
二进制日志:binary log
中继日志:reley log
命令日志:~/.mysql_history,记录各自终端输过的mysql命令
事务日志
事务日志:transaction log
事务型存储引擎自行管理和使用
redo log
undo log
Innodb事务日志相关配置:
show variables like '%innodb_log%';
innodb_log_file_size 5242880 每个日志文件大小(建议按生产情况调大,如1G,否则执行大的事务可能会直接提交,无法回滚)
innodb_log_files_in_group 2 日志组成员个数(建议按生产情况调多,如3个)
innodb_log_group_home_dir ./ 事务文件路径(建议事务日志跟数据存放在不同目录中)
中继日志:relay log
主从复制架构中,从服务器用于保存从主服务器的二进制日志中读取到的事件
optimize table tbl_name 整理表,例如进行大量修改|删除操作后,表文件大小并不会减小,执行此命令后可减小
错误日志
错误日志
mysqld启动和关闭过程中输出的事件信息
mysqld运行中产生的错误信息
event scheduler运行一个event时产生的日志信息
在主从复制架构中的从服务器上启动从服务器线程时产生的信息
错误日志相关配置
SHOW GLOBAL VARIABLES LIKE 'log_error'
错误文件路径:
log_error=/PATH/TO/LOG_ERROR_FILE
是否记录警告信息至错误日志文件
log_warnings=1|0 默认值1
通用日志
通用日志:记录查询操作
文件:file,默认值
表:table
通用日志相关设置
general_log=ON|OFF(通用日志,谁操作都记录,量会很大,不建议开启)
general_log_file=HOSTNAME.log
log_output=TABLE|FILE|NONE(日志记录在表里/文件里)
慢查询日志
慢查询日志:记录执行查询时长超出指定时长的操作
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
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 新版已废弃
例:
select sleep(1),name from students; 每查一项休眠1秒
再show profile; 查看命令各阶段执行的时间,需先set profiling=1,
二进制日志
记录导致数据改变或潜在导致数据改变的SQL语句
记录已提交的日志
不依赖于存储引擎类型
功能:通过“重放”日志文件中的事件来生成数据副本
注意:建议二进制日志和数据文件分开存放
二进制日志记录格式
二进制日志记录三种格式
基于“语句”记录:statement,记录语句,默认模式
基于“行”记录:row,记录数据,日志量较大(生产中强烈建议基于“行”记录)
混合模式:mixed, 让系统自行判定该基于哪种方式进行
格式配置
show variables like '%binlog_format%';
二进制日志文件的构成
有两类文件
日志文件:mysql|mariadb-bin.文件名后缀,二进制格式
如: mysql-bin.000001
索引文件:mysql|mariadb-bin.index,文本格式
二进制日志相关的服务器变量:
sql_log_bin=ON|OFF:是否记录二进制日志,默认ON(此项可以动态更改,例如硬盘满了想临时不记录日志,可以临时关掉)
log_bin=/PATH/BIN_LOG_FILE:指定文件位置;默认OFF,表示不启用二进制日志功能,上述两项都开启才可(此项不支持动态更改,需写入配置文件中,需重启服务)
binlog_format=STATEMENT|ROW|MIXED:二进制日志记录的格式,默认STATEMENT(强烈建议改成ROW)
max_binlog_size=1073741824:单个二进制日志文件的最大体积,到达最大值会自动滚动,默认为1G
说明:文件达到上限时的大小未必为指定的精确值
sync_binlog=1|0:设定是否启动二进制日志即时同步磁盘功能,默认0,由操作系统负责同步日志到磁盘(即时的话效率低,好处是不会丢东西,一般情况0就行)
expire_logs_days=N:二进制日志可以自动删除的天数。 默认为0,即不自动删除(可留个30天)
二进制日志相关配置
查看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
mysqlbinlog
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;
免责声明:
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341