MySQL之日志管理

haixin3036

　　日志
　　事务日志：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>　　use `mydb`/*!*/;
　　SET TIMESTAMP=1446712300/*!*/;
　　CREATE TABLE tb1 (id int, name char(30))
　　/*!*/;
　　事件发生的日期和时间：151105 16:31:40

　　事件发生的服务器标识：server>　　事件的结束位置：end_log_pos 431
　　事件的类型：Query
　　事件发生时所在服务器执行此事件的线程的ID：thread_id=1
　　语句的时间戳与将其写入二进制文件中的时间差：exec_time=0
　　错误代码：error_code=0
　　事件内容：

　　GTID：Global Transaction>　　清除指定二进制日志：
　　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;