Linux:MYSQL（十）日志二进制日志

日志

事务日志 transaction log
中继日志 reley log
错误日志 error log
通用日志 general log
慢查询日志 slow query log
二进制日志 binary log

事务日志

• 事务日志：transaction log

• 事务型存储引擎自行管理和使用，建议和数据文件分开存放
  redo log
  undo log

• Innodb事务日志相关配置：
  show variables like ‘%innodb_log%’;
  innodb_log_file_size 5242880 每个日志文件大小
  innodb_log_files_in_group 2 日志组成员个数
  innodb_log_group_home_dir ./ 事务文件路径(如更改路径需写入配置文件）

vim /etc/my.cnf
[mysqld]
innodb_log_group_home_dir=/data/mysqllogs/

show variables like ‘%datadir%’; 可查看路径，默认可知在
/var/lib/mysql/

• 中继日志：relay log
  主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件

小技巧：

optimize table testlog;  
testlog表删除了大量数据，文件大小不会缩减，执行此命令后可缩小数据库文件

错误日志

• 错误日志
  mysqld启动和关闭过程中输出的事件信息
  mysqld运行中产生的错误信息
  event scheduler运行一个event时产生的日志信息
  在主从复制架构中的从服务器上启动从服务器线程时产生的信息
• 错误日志相关配置
  SHOW GLOBAL VARIABLES LIKE ‘log_error’
  错误文件路径
  log_error=/PATH/TO/LOG_ERROR_FILE
  是否记录警告信息至错误日志文件
  log_warnings=1|0 默认值1

通用日志

• 通用日志：记录对数据库的通用操作，包括错误的SQL语句
  文件：file，默认值
  表：table

• 通用日志相关设置
  general_log=ON|OFF
  general_log_file=HOSTNAME.log
  log_output=TABLE|FILE|NONE

慢查询日志 tail -f 跟踪日志

• 慢查询日志：记录执行查询时长超出指定时长的操作
• slow_query_log=ON|OFF 开启或关闭慢查询

show variables like '%slow%' 查看是否开启慢日志
set global slow_query_log=on 开启慢查询

• long_query_time=N 慢查询的阀值，单位秒

show variables like '%long_query%' 查看阈值
set long_query_time=5 设置超过5秒的记录日志 

• slow_query_log_file=HOSTNAME-slow.log 慢查询日志文件

生成文件 例： centos7-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，即不记录

思路：记录日志后，可为记录语句添加索引，优化速度
1.select * from testlog where age=90000; 
先执行语句记录日志
2.explain select * from testlog where age=90000;
查看此语句执行过程
3.create index idx_age on testlog(age);
创建索引
4.select * from testlog where age=99000;
测试添加索引后是否优化了速度

• log_slow_rate_limit = 1 慢多少次查询才记录，mariadb特有

• log_slow_verbosity= Query_plan,explain 记录内容（查询计划，执行过程）

• log_slow_queries = OFF 同slow_query_log 新版已废弃

profiling 查看详细执行状态选项

show variables like 'profiling' 查询状态
set profiling =on 开启
select * from teachers; 执行一个查询语句
show profiles; 查询刚刚执行的语句编号
show profile for query 1; 查询1号的过程

二进制日志

记录导致数据改变或潜在导致数据改变的SQL语句
记录已提交的日志
不依赖于存储引擎类型
功能：通过“重放”日志文件中的事件来生成数据副本

基于备份，数据丢失先备份还原，然后用二进制日志还原到最新的操作

注意：建议二进制日志和数据文件分开存放

二进制日志记录格式

• 基于“语句”记录：statement，记录语句，默认模式
  update students set age=20; （未加where语句）执行后，记录一条

• 基于“行”记录：row，记录数据，日志量较大
  update students set age=20;（未加where语句）若有100项，记录100条

• 混合模式：mixed, 让系统自行判定该基于哪种方式进行

• 格式配置
  show variables like ‘binlog_format’;

二进制日志文件的构成（两类文件）

日志文件：mysql|mariadb-bin.文件名后缀，二进制格式
如： mariadb-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

• 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
从6516开始看，其中的2，3条

mysqlbinlog：二进制日志的客户端命令工具

mysqlbinlog [OPTIONS] log_file…
选项:
–start-position=# 指定开始位置
–stop-position=#
–start-datetime2018-01-30 20:30:10
–stop-datetime=
–base64-out=decode-rows 解码64
-v
-vvv

从位置查：
mysqlbinlog --start-position=6787 --stop-position=7527 /var/lib/mysql/mariadb-bin.000003 -v
从时间查：
mysqlbinlog --start-datetime="2018-01-30 20:30:10" --stop-datetime="2018-01-30 20:35:22" mariadb-bin.000003 -vvv

二进制日志事件的格式

准备：

mysqlbinlog /data/mysqlbin/mysql-bin.000001 --start-position=48355715 > /data/test.sql
按位置导出日志
cat /data/test.sql
查看文件格式

二进制日志事件的格式：

# at 48355715   
#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 #

测试环境没问题，准备上线才使用，从头开始计数
reset master 全清除

• 切换日志文件：
  FLUSH LOGS;

flush logs；
mysql-bin.000001
mysql-bin.000002
生成一个新一个日志文件，原日志文件不会被删。
作用：原日志文件已经备份了，可以存放新日志文件