MySQL日志类型及管理 ========================================================
mysql 主要的日志类型 ：
1，错误日志：记录启动、运行或停止时出现的问题，一般也会记录警告信息。
2，一般查询日志/通用查询日志 ：记录建立的客户端连接 和 执行的语句。
3，慢查询日志：记录所有执行时间超过，long_query_time 秒的所有查询或不使用索引的查询，可以帮我们定位服务器性能问题。
4，二进制日志：任何引起或可能引起数据库变化的操作，主要用于 主从复制 和 即时 点恢复。
5，中继日志：从主服务器的二进制日志文件中复制而来的事件，并保存为的日志文件。
6，事务日志：记录InnoDB等支持事务的存储引擎执行事务时产生的日志。
7，撤销日志：用于事务回滚
查看系统中与 日志相关的 变量信息 ： mysql> show variables like '%log%';
mysql 错误日志：==================================
错误日志主要记录如下信息：（常用于排错）
服务器启动和关闭过程中的信息
服务器运行过程中的错误信息
事件调度器运行一个事件时产生的信息
在从服务器上启动 从服务器 进程时产生的信息
错误日志定义：
可以用log-error [ = file_name ]
选项来指定mysqld保存错误日志文件的位置。如果没有给定file_name值，mysqld使用错误日志名host_name.err
并在数据目录中写入日志文件。
如果你执行 FLUSH LOGS，错误日志用-old重新命名后缀并且mysqld创建一个新的空日志文件。(如果未给出log-error选项，则不会重新命名）。
错误日志一般有以上两个变量可以定义：
错误日志文件：log-error
启用警告信息：log-warnings （默认启用）
日志定义示例： log-error=/var/log/mariadb/mariadb.log ##默认值 编译版 中，日志文件如果无法建立，需要手动 touch 出来，保证mysql用户能读，等写。
指定日志路径方法：
编辑my.cnf 写入 log-error=[path]
mysql 一般查询日志，通用日志：==================================
## 一般不使用 全局日志开关：log={ON|OFF} 该开关打开后，所有日志都会被启用
启动开关：general_log={ON|OFF}
日志文件变量：general_log_file [ =/PATH/TO/file]
记录类型：log_output={TABLE|FILE|NONE}
log_output 定义了日志的输出格式，可以是表，文件，若设置为NONE，则不启用日志，
因此，要启用通用查询日志，需要至少配置general_log=ON，log_output={TABLE|FILE}。
而general_log_file如果没有指定，默认名是host_name.log。
由于一般查询使用量比较大，启用写入日志文件，服务器的I/O操作较多，会大大降低服务器的性能，所以默认为关闭的。
示例：general_log=ON ##默认最好不要开启。
log_output=file
general_log_file=/mydb/general.log ##（该路径的目录，必须让mysql 作为所有者，并具有可写权限。 ）
[root@http1 mydb]# cat general.log
/usr/libexec/mysqld, Version: 5.5.56-MariaDB (MariaDB Server). started with:
Tcp port: 0 Unix socket: /mydb/mysql.sock
Time Id Command Argument
2 Query select @@version_comment limit 1
180110 15:17:48 2 Query select version()
mysql 慢查询日志：==================================
默认不启用，在调优情况下，建议启用，找到执行缓慢的 查询语句，以便于准确的设置索引，加快操作。
设置查询超时时间：long_query_time ## 规定 “慢” 的标准
启动慢查日志：log_slow_queries={YES|NO}
启动慢查日志 : slow_query_log =ON
日志记录文件：slow_query_log_file [= file_name ]
MySQL如果启用了slow_query_log=ON选项，就会记录执行时间超过long_query_time的查询。
日志记录文件如果没有给出file_name值， 默认为主机名，后缀为-slow.log。
如果给出了文件名，但不是绝对路径名，文件则写入数据目录。
mysql> show global variables like '%slow_query_log%';
mysql> show global variables like 'long_query_time';
示例：
slow_query_log=ON
slow_query_log_file=/var/log/mariadb/slow.log
long_query_time=0.500000 ## 微秒级别，默认10秒算慢查询，被记录，我们测试使用 0.5 秒。
不止是语句执行时间，算上其他进程影响，阻塞等。
### 编写批量插入 数据脚本，增加数据库体积，以检验慢查询日志
### 也可以使用存储过程和函数，
#!/bin/bash
for ((i=1;1<10000;i++))
do
mysql <<EOF
insert into stu_sys.students (name,sex,age,class,stat) values ("王阳$i","man",29,2,"0");
EOF
if [ $i -eq 10000 ];then
exit 0
fi
done
 |
 |
mysql 事务日志 ：===================================
事务性存储引擎用于保证（ACID）原子性、一致性、隔离性和持久性；
其不会立即写到数据文件中，而是写到事务日志中。
事务日志默认开启，使用两个文件轮转，默认每个日志 5M 。非文本文件（损坏致命，建议raid）
相关参数（全局&静态）:
innodb_log_buffer_size=64M
innodb_log_file_size=50M
innodb_log_files_in_group=2
innodb_log_group_home_dir=/mysql
innodb_flush_log_at_trx_commit # 1/0/2 redo是否持久化到磁盘参数 对性能影响大 0 不推荐 1 推荐 2 只追求速度，不需要事务安全
解析：参照 Mysql 事务的 章节。
mysql 二进制日志 Binary Log ： =============================
二进制日志用于记录所有 可能更改数据的语句
主要用于主从复制 和 数据库 即时点恢复（备份）。
二进制日志的主要目的是在数据库存在故障时，恢复时能够最大可能地更新数据库（即时点恢复），因为二进制日志包含备份后进行的所有更新，
二进制日志还用于在主复制服务器上记录所有将发送给从服务器的语句。
（建议不要将二进制日志文件 与 数据库文件放在同一磁盘）
二进制日志启动开关：log-bin [= file_name]
################################################
Innodb 引擎，默认设置， 每一个修改的操作，都会封装成事务，并且事务会自动提交。
 |
示例：
### 请慎重考虑，二进制日志文件的存放位置，它将是恢复数据库的关键！
## log-bin=/mydb/mysql-bin ### 文件前缀 mysql-bin作为二进制日志文件名的起始。 例如： mysql-bin.000002
## server-id=1 ## 集群中的唯一值 注意，一般打开二进制日志后，server-id 也要随之指派，二进制日志记录信息中会使用该 ID 值 标识唯一，尤其在主从环境中
## max_binlog_size=1073741824 ## 单个二进制日志 大小上限 默认1G ，到达自动滚动日志
## expire_logs_days=0 ## 自动清理多少天前的二进制日志，建议手动管理
mysql > 临时配置 ！！
## sql_log_bin ##会话级别二进制日志开关
set session sql_log_bin=0 临时关闭二进制日志，关闭后，操作将不再被二进制文件记录
A
B
C
set session sql_log_bin=1 ## 或者直接退出，不影响下次登录。
## binlog_format =statement 二进制日志格式 ##默认使用 statement 建议改为 mixed 内置策略会自动选择最佳的格式。
## binlog_rows_query_log_events=ON ##使得 row 格式的二进制日志输出 明文的 DML 语句。
基于语句: statement # 使用命令的方式记录 操作 ， 记录语句及上下文，容易出bug
基于行: row ## 记录的 命令执行后，各个列的值。
混合方式: mixed
ROW 模式下，速度快，数据不易出错。
## 建议配合 binlog_rows_query_log_events=ON 使用 ##新增功能，在ROW模式下，输出明文 DML 语句，让ROW日志更易读。
明文DML语句 #Q> insert into stu_sys.students (name,sex,age,class,stat) values ("王阳10","man",29,2,"0")
示例：
log-bin=/var/log/mariadb/mysql-bin
server-id=1
binlog_format=row
binlog_rows_query_log_events=OFF
max_binlog_size=1073741824
expire_logs_days=0
[root@http1 mydb]# ll
total 30360
-rw-rw---- 1 mysql mysql 245 Jan 10 15:49 mysql-bin.000001
-rw-rw---- 1 mysql mysql 23 Jan 10 15:49 mysql-bin.index
#### 二进制日志文件包括：
索引文件 mysql-bin.index
二进制日志文件 mysql-bin.000001
在数据目录下有一个mysql-bin.index便是索引文件，以mysql-bin开头并以数字结尾的文件为二进制日志文件。
#### 查看与管理二进制日志：
方法一： 使用命令行工具：mysqlbinlog
[root@http1 mydb]# mysqlbinlog mysql-bin.000002 ## 用于 statement 格式的二进制日志查看
# at 26956 ## 相对位置：position
#180110 15:52:38 server id 1 end_log_pos 26984 Intvar ### 产生的时间：starttime
SET INSERT_ID=201/*!*/;
# at 26984
#180110 15:52:38 server id 1 end_log_pos 27137 Query thread_id=101 exec_time=0 error_code=0
SET TIMESTAMP=1515599558/*!*/;
insert into stu_sys.students (name,sex,age,class,stat) values ("李清照99","women",29,2,"0")
/*!*/;
 |
mysqlbinlog [options] log-files ======================
常用options（类似字节偏移数）：
--start-position = ：开始位置
--stop-position = ：结束位置
--start-datetime = 'yyyy-mm-dd hh:mm:ss' ：开始时间
--stop-datetime = 'yyyy-mm-dd hh:mm:ss' ：结束时间
例子： [root@http1 mydb]# mysqlbinlog mysql-bin.000002 --start-position 27137 --stop-position 27228
方法二 ：在mysql 客户端工具中查看
mysql> show master status; #查看当前正在使用的二进制文件
mysql> show binlog events in 'mysql-bin.000033'; #查看二进制文件中记录的内容
mysql> show binlog events in 'mysql-bin.000002' from 26593 \G ### 可以看到 二进制日志中的明文语句。
mysql> show binary logs; 查看所有的二进制日志文件
mysql> purge binary logs to 'mysql-bin.000025'; # 删除某个序号之前的日志文件
mysql> flush logs;
## 滚动二进制日志文件，产生新的序号的文件，并开始记录。 ## 重启服务也会回滚二进制日志。
清除二进制日志：============================================
清除所有日志（不存在主从复制关系） mysql> reset master;
清除指定日志之前的所有日志 mysql> purge master logs to '日志';
清除某一时间点前的所有日志 mysql> purge master logs before '年-月-日 时：分：秒';
清除 n 天前的所有日志 mysql> purge master logs before current_date - interval 10 day;
由于二进制日志的重要性,请仅在确定不再需要 ， 将被删除的二进制文件，
或者在已经对二进制日志文件进行归档备份，
或者已经进行数据库备份的情况下，才进行删除操作，且不要使用 rm 命令删除。
 |
练习，使用二进制日志文件恢复一条被删除的数据：==========================
mysqlbinlog 工具读取 二进制日志内容，注意 at postison的位置
mysqlbinlog mysql-bin.0000001 > /tmp/a.sql 可导出成为sql语句
恢复某一个操作，读出删除数据操作之前的所有事件，选取二进制日志范围，进行重新导入操作
导出二进制日志： mysqlbinlog --stop-position=XXX mysql-bin.00000X > /tmp/a.sql
导入二进制日志：mysql> source /tmp/a.sql; 注意访问权限
什么时候需要关闭二进制日志，例如刚才我们恢复二进制时候的操作。
一般需要用的，慢日志，二进制日志，错误日志
普通查询日志，一般不开启
lock tables students write;
unlock tables;
尝试恢复某一条被误删除的数据。 （表? 数据库？）
导出当前二进制文件，注意停止位置。
mysql>souce /tmp/XXXX.sql
临时关闭 二进制日志功能
导入后再 开启。
show variables like
A ------ backup A ------- Backup B 2 bob
|
| -------post 4
10------ post 114
11------ post 180======》start=4 stop=300 > a.sql source a.sql
12------ post 240
13------ post 300
|
|
13 删除 ？ post 350
2 bob
误删除了一条数据；
============ 附录：日志相关的服务器参数详解： =================================
expire_logs_days={0..99}
设定二进制日志的过期天数，超出此天数的二进制日志文件将被自动删除。默认为0，表示不启用过期自动删除功能。如果启用此功能，自动删除工作通常发生在MySQL启动时或FLUSH日志时。作用范围为全局，可用于配置文件，属动态变量。
general_log={ON|OFF}
设定是否启用查询日志，默认值为取决于在启动mysqld时是否使用了--general_log选项。如若启用此项，其输出位置则由--log_output选项进行定义，如果log_output的值设定为NONE，即使用启用查询日志，其也不会记录任何日志信息。作用范围为全局，可用于配置文件，属动态变量。
general_log_file=FILE_NAME
查询日志的日志文件名称，默认为“hostname.log"。作用范围为全局，可用于配置文件，属动态变量。
binlog-format={ROW|STATEMENT|MIXED}
指定二进制日志的类型，默认为STATEMENT。如果设定了二进制日志的格式，却没有启用二进制日志，则MySQL启动时会产生警告日志信息并记录于错误日志中。作用范围为全局或会话，可用于配置文件，且属于动态变量。
log={YES|NO}
是否启用记录所有语句的日志信息于一般查询日志(general query log)中，默认通常为OFF。MySQL 5.6已经弃用此选项。
log-bin={YES|NO}
是否启用二进制日志，如果为mysqld设定了--log-bin选项，则其值为ON，否则则为OFF。其仅用于显示是否启用了二进制日志，并不反应log-bin的设定值。作用范围为全局级别，属非动态变量。
log_bin_trust_function_creators={TRUE|FALSE}
此参数仅在启用二进制日志时有效，用于控制创建存储函数时如果会导致不安全的事件记录二进制日志条件下是否禁止创建存储函数。默认值为0，表示除非用户除了CREATE ROUTING或ALTER ROUTINE权限外还有SUPER权限，否则将禁止创建或修改存储函数，同时，还要求在创建函数时必需为之使用DETERMINISTIC属性，再不然就是附带READS SQL DATA或NO SQL属性。设置其值为1时则不启用这些限制。作用范围为全局级别，可用于配置文件，属动态变量。
log_error=/PATH/TO/ERROR_LOG_FILENAME
定义错误日志文件。作用范围为全局或会话级别，可用于配置文件，属非动态变量。
log_output={TABLE|FILE|NONE}
定义一般查询日志和慢查询日志的保存方式，可以是TABLE、FILE、NONE，也可以是TABLE及FILE的组合(用逗号隔开)，默认为TABLE。如果组合中出现了NONE，那么其它设定都将失效，同时，无论是否启用日志功能，也不会记录任何相关的日志信息。作用范围为全局级别，可用于配置文件，属动态变量。
log_query_not_using_indexes={ON|OFF}
设定是否将没有使用索引的查询操作记录到慢查询日志。作用范围为全局级别，可用于配置文件，属动态变量。
log_slave_updates
用于设定复制场景中的从服务器是否将从主服务器收到的更新操作记录进本机的二进制日志中。本参数设定的生效需要在从服务器上启用二进制日志功能。
log_slow_queries={YES|NO}
是否记录慢查询日志。慢查询是指查询的执行时间超出long_query_time参数所设定时长的事件。MySQL 5.6将此参数修改为了slow_query_log。作用范围为全局级别，可用于配置文件，属动态变量。
log_warnings=#
设定是否将警告信息记录进错误日志。默认设定为1，表示启用；可以将其设置为0以禁用；而其值为大于1的数值时表示将新发起连接时产生的“失败的连接”和“拒绝访问”类的错误信息也记录进错误日志。
long_query_time=#
设定区别慢查询与一般查询的语句执行时间长度。这里的语句执行时长为实际的执行时间，而非在CPU上的执行时长，因此，负载较重的服务器上更容易产生慢查询。其最小值为0，默认值为10，单位是秒钟。它也支持毫秒级的解析度。作用范围为全局或会话级别，可用于配置文件，属动态变量。
max_binlog_cache_size{4096 .. 18446744073709547520}
二进定日志缓存空间大小，5.5.9及以后的版本仅应用于事务缓存，其上限由max_binlog_stmt_cache_size决定。作用范围为全局级别，可用于配置文件，属动态变量。
max_binlog_size={4096 .. 1073741824}
设定二进制日志文件上限，单位为字节，最小值为4K，最大值为1G，默认为1G。
某事务所产生的日志信息只能写入一个二进制日志文件，因此，实际上的二进制日志文件可能大于这个指定的上限。
作用范围为全局级别，可用于配置文件，属动态变量。
max_relay_log_size={4096..1073741824}
设定从服务器上中继日志的体积上限，到达此限度时其会自动进行中继日志滚动。
此参数值为0时，mysqld将使用max_binlog_size参数同时为二进制日志和中继日志设定日志文件体积上限。作用范围为全局级别，可用于配置文件，属动态变量。
innodb_log_buffer_size={262144 .. 4294967295}
设定InnoDB用于辅助完成日志文件写操作的日志缓冲区大小，单位是字节，默认为8MB。较大的事务可以借助于更大的日志缓冲区来避免在事务完成之前将日志缓冲区的数据写入日志文件，以减少I/O操作进而提升系统性能。因此，在有着较大事务的应用场景中，建议为此变量设定一个更大的值。作用范围为全局级别，可用于选项文件，属非动态变量。
innodb_log_file_size={108576 .. 4294967295}
设定日志组中每个日志文件的大小，单位是字节，默认值是5MB。较为明智的取值范围是从1MB到缓存池体积的1/n，其中n表示日志组中日志文件的个数。日志文件越大，在缓存池中需要执行的检查点刷写操作就越少，这意味着所需的I/O操作也就越少，然而这也会导致较慢的故障恢复速度。作用范围为全局级别，可用于选项文件，属非动态变量。
innodb_log_files_in_group={2 .. 100}
设定日志组中日志文件的个数。InnoDB以循环的方式使用这些日志文件。默认值为2。作用范围为全局级别，可用于选项文件，属非动态变量。
innodb_log_group_home_dir=/PATH/TO/DIR
设定InnoDB重做日志文件的存储目录。在缺省使用InnoDB日志相关的所有变量时，其默认会在数据目录中创建两个大小为5MB的名为ib_logfile0和ib_logfile1的日志文件。作用范围为全局级别，可用于选项文件，属非动态变量。
relay_log=file_name
设定中继日志的文件名称，默认为host_name-relay-bin。也可以使用绝对路径，以指定非数据目录来存储中继日志。作用范围为全局级别，可用于选项文件，属非动态变量。
relay_log_index=file_name
设定中继日志的索引文件名，默认为为数据目录中的host_name-relay-bin.index。作用范围为全局级别，可用于选项文件，属非动态变量。
relay-log-info-file=file_name
设定中继服务用于记录中继信息的文件，默认为数据目录中的relay-log.info。作用范围为全局级别，可用于选项文件，属非动态变量。
relay_log_purge={ON|OFF}
设定对不再需要的中继日志是否自动进行清理。默认值为ON。作用范围为全局级别，可用于选项文件，属动态变量。
relay_log_space_limit=#
设定用于存储所有中继日志文件的可用空间大小。默认为0，表示不限定。最大值取决于系统平台位数。作用范围为全局级别，可用于选项文件，属非动态变量。
slow_query_log={ON|OFF}
设定是否启用慢查询日志。0或OFF表示禁用，1或ON表示启用。日志信息的输出位置取决于log_output变量的定义，如果其值为NONE，则即便slow_query_log为ON，也不会记录任何慢查询信息。作用范围为全局级别，可用于选项文件，属动态变量。
slow_query_log_file=/PATH/TO/SOMEFILE
设定慢查询日志文件的名称。默认为hostname-slow.log，但可以通过--slow_query_log_file选项修改。作用范围为全局级别，可用于选项文件，属动态变量。
sql_log_bin={ON|OFF}
用于控制二进制日志信息是否记录进日志文件。默认为ON，表示启用记录功能。用户可以在会话级别修改此变量的值，但其必须具有SUPER权限。作用范围为全局和会话级别，属动态变量。
sql_log_off={ON|OFF}
用于控制是否禁止将一般查询日志类信息记录进查询日志文件。
默认为OFF，表示不禁止记录功能。用户可以在会话级别修改此变量的值，但其必须具有SUPER权限。作用范围为全局和会话级别，属动态变量。
sync_binlog=#
“sync_binlog”：这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。
对于“sync_binlog”参数的各种设置的说明如下：
sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，
或者cache满了之后才同步到磁盘。
sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。
因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。
因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。
从以往经验和相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多