centos 数据库日志管理

centos 数据库日志管理
事务日志：transaction log 事务日志的写入类型为“追加”，因此其操作为“顺序IO”；通常也被称为：预写式日志 write ahead logging 
事务日志文件： ib_logfile0， ib_logfile1 
错误日志error log 
通用日志general log 
慢查询日志 slow query log 
二进制日志 binary log 
中继日志reley log，在主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件 
一，事务日志
1.    事务日志相关配置
MariaDB [hellodb]> show variables like '%innodb_log%';
innodb_log_file_size 5242880 每个日志文件大小
innodb_log_files_in_group 2 日志组成员个数
innodb_log_group_home_dir ./ 事务文件路径
innodb_flush_log_at_trx_commit 默认为1
2.    事务日志性能优化
innodb_flush_log_at_trx_commit=0|1|2|3
1 默认情况下，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。 这是完全遵守ACID特性 
0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。 这样可提 供更好的性能，但服务器崩溃可能丢失最后一秒的事务 
2 每次提交后都会写入OS的缓冲区，但每秒才会进行一次刷新到磁盘文件中。 性能比0略差一些，但操 作系统或停电可能导致最后一秒的交易丢失
3 模拟MariaDB 5.5组提交（每组提交3个同步），此项MariaDB 10.0支持 
二，错误日志
mysqld启动和关闭过程中输出的事件信息 
mysqld运行中产生的错误信息 
event scheduler运行一个event时产生的日志信息 
在主从复制架构中的从服务器上启动从服务器线程时产生的信息
1.    查询错误日志存储路径
SHOW GLOBAL VARIABLES LIKE 'log_error'
2.    修改错误文件路径 my.cnf
log_error=/PATH/TO/LOG_ERROR_FIL
例如
log_error       =/data/log 
[root@localhost ~]#ll /data
total 8
-rw-rw----. 1 mysql mysql 5109 Nov 25 21:09 log.err
3.    是否记录警告信息至错误日志文件 
log_warnings=1|0 默认值1
三，通用日志
通用日志：记录对数据库的通用操作，包括错误的SQL语句 通用日志可以保存在：file（默认值）或 table
1.    相关设置
general_log=ON|OFF   默认为off，打开后，默认会在数据库目录生成想要主机名.log 的文件
general_log_file=HOSTNAME.log 
log_output=TABLE|FILE|NONE 
四，慢查询日志
慢查询日志：记录执行查询时长超出指定时长的操作
1.    慢查询相关变量
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 
#新版已废弃 
五，二进制日志
记录导致数据改变或潜在导致数据改变的SQL语句 
记录已提交的日志 
不依赖于存储引擎类型 
功能：通过“重放”日志文件中的事件来生成数据副本 
注意：建议二进制日志和数据文件分开存放 
1.    二进制日志记录三种格式 
基于“语句”记录：statement，记录语句，默认模式 
基于“行”记录：row，记录数据，日志量较大 
混合模式：mixed, 让系统自行判定该基于哪种方式进行
show variables like ‘binlog_format';
MariaDB [(none)]> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> 
2.    二进制日志文件的构成 
日志文件：mysql|mariadb-bin.文件名后缀，
二进制格式 如： mariadb-bin.000001 
索引文件：mysql|mariadb-bin.index，文本格式
[root@localhost ~]#ll /data
total 64
-rw-rw----. 1 mysql mysql 49518 Nov 26 10:26 log.err
-rw-rw----. 1 mysql mysql   351 Nov 26 10:26 mysql-bin.000001
-rw-rw----. 1 mysql mysql   328 Nov 26 10:26 mysql-bin.000002
-rw-rw----. 1 mysql mysql    46 Nov 26 10:26 mysql-bin.index
[root@localhost ~]#
3.    二进制日志相关变量
服务器配置文件 log-bin=/data/mysql-bin  加上路径开启，或者直接 =mysql-bin 这样会生成在数据库默认位置，建议更改
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，即不自动删除 
4.    查看二进制文件
SHOW {BINARY | MASTER} LOGS 
MariaDB [(none)]> show binary logs；
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       351 |
| mysql-bin.000002 |       328 |
+------------------+-----------+
2 rows in set (0.00 sec)
MariaDB [(none)]> show master logs；
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       351 |
| mysql-bin.000002 |       328 |
+------------------+-----------+
2 rows in set (0.00 sec)
MariaDB [(none)]> 
5.    查看使用中的二进制文件
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      328 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
6.    查看为二进制文件中的指定内容
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count] 
show binlog events in ‘mysql-bin.000001' from 6516 limit 2,3 
from 和 limit 是可选项
7.    mysqlbinlog 二进制日志的客户端命令工具
mysqlbinlog [OPTIONS] log_file… 
--start-position=# 指定开始位置 
--stop-position=# 
--start-datetime= 
--stop-datetime= 
时间格式：YYYY-MM-DD hh:mm:ss 
--base64-output[=name] -v -vvv
a.    mysqlbinlog --start-position=678 --stop-position=752 /var/lib/mysql/mariadbbin.000003 -v 
b.    mysqlbinlog --start-datetime="2018-01-30 20:30:10" --stop-datetime="2018-01- 30 20:35:22" mariadb-bin.000003 -vvv 
h.    清楚指定二进制日志
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'; 
9.    删除所有二进制日志，index文件重新计数
RESET MASTER [TO #]; #删除所有二进制日志文件，并重新生成日志文件，文件名从
#开始记数，默认从 1开始，一般是master主机第一次启动时执行，MariaDB10.1.6开始支持TO # 
10.    切换新日志
FLUSH LOGS;