Mysql的日志管理

mysql日志的种类

1. 事务日志 transaction log (记录事务信息，实现undo,redo等故障恢复功能)

2. 错误日志 error log (记录启动，运行，停止mysql时出现的信息)

3. 通用日志 general log (记录对数据库的通用操作，包括错误的SQL语句)
4. 慢查询日志 slow query log （记录所有执行超过long_query_time秒的所有查询）
5. 二进制日志 binary log （记录所有更改数据的语句，还用于复制，恢复数据库用）
6. 中继日志 reley log (主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件)

事务日志

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

redo log
undo log

Innodb事务日志相关配置：

1、show variables like ‘%innodb_log%’;

例：

MariaDB [hellodb]> show variables like '%innodb_log%';
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| innodb_log_block_size     | 512     |   每个块的大小
| innodb_log_buffer_size    | 8388608 |   缓冲区buffer的大小
| innodb_log_file_size      | 5242880 |   每个日志文件大小
| innodb_log_files_in_group | 2       |   日志组成员个数 
| innodb_log_group_home_dir | ./      |   事务文件路径
+---------------------------+---------+
5 rows in set (0.00 sec)

2、innodb_flush_log_at_trx_commit 默认为1

例：

MariaDB [hellodb]> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

​ 说明：设置为1，同时sync_binlog = 1表示最高级别的容错innodb_use_global_flush_log_at_trx_commit的值确定是否可以使用SET语句重置此变量

• 1默认情况下，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。这是完全遵守ACID特性
• 0提交时没有任何操作; 而是每秒执行一次日志缓冲区写入和刷新。 这样可以提供更好的性能，但服务器崩溃可以清除最后一秒的事务
• 2每次提交后都会写入日志缓冲区，但每秒都会进行一次刷新。 性能比0略好一些，但操作系统或停电可能导致最后一秒的交易丢失
• 3模拟MariaDB 5.5组提交（每组提交3个同步），此项MariaDB 10.0支持

错误日志

mysqld启动和关闭过程中输出的事件信息

mysqld运行中产生的错误信息

event scheduler运行一个event时产生的日志信息

在主从复制架构中的从服务器上启动从服务器线程时产生的信息

错误日志相关配置

1、show global variables like ‘log_error’;

例：

MariaDB [hellodb]> show global variables like 'log_error';
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| log_error     | /var/log/mariadb/mariadb.log |
+---------------+------------------------------+
1 row in set (0.00 sec)

2、错误文件路径

log_error=/PATH/TO/LOG_ERROR_FILE

3、是否记录警告信息至错误日志文件

log_warnings=1|0 默认值1

例：

MariaDB [hellodb]> set log_error="/data/mariadb.log";
ERROR 1238 (HY000): Variable 'log_error' is a read only variable

这个变量只能在配置文件修改

通用日志

通用日志：记录对数据库的通用操作，包括错误的SQL语句

​ 文件：file，默认值

​ 表：table

通用日志相关设置

1、general_log=ON|OFF

例：

MariaDB [hellodb]> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

2、general_log_file=HOSTNAME.log

例：

MariaDB [hellodb]> show variables like 'general_log_file';
+------------------+-------------+
| Variable_name    | Value       |
+------------------+-------------+
| general_log_file | centos7.log |
+------------------+-------------+
1 row in set (0.00 sec)

3、log_output=TABLE|FILE|NONE

例：

MariaDB [hellodb]> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

慢查询日志

    慢查询日志主要记录执行查询时长超出指定时长的操作

通过慢查询日志，可以找出执行时间较长、执行效率较低的语句，然后进行优化

1、开启或关闭慢查询(生产中启用)

slow_query_log=ON|OFF

例：

MariaDB [hellodb]> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set (0.00 sec)

2、慢查询的阀值，单位秒(生产中阈值根据实际情况设小点)

long_query_time=N

例：

MariaDB [hellodb]> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |      默认10秒
+-----------------+-----------+
1 row in set (0.00 sec)

3、慢查询日志文件

slow_query_log_file=HOSTNAME-slow.log

例：

MariaDB [hellodb]> show variables like 'slow_query_log_file';
+---------------------+------------------+
| Variable_name       | Value            |
+---------------------+------------------+
| slow_query_log_file | centos7-slow.log |
+---------------------+------------------+
1 row in set (0.00 sec)

4、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，则记录日志

MariaDB [hellodb]> show variables like 'log_slow_filter';    
+-----------------+--------------------------------------------------------------------------------------------------------------+
| Variable_name   | Value                                                                                                        |
+-----------------+--------------------------------------------------------------------------------------------------------------+
| log_slow_filter | admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk |
+-----------------+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

5、log_queries_not_using_indexes=ON 不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语句是否记录日志，默认OFF，即不记录 生产中也需要开启

MariaDB [hellodb]> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.00 sec)

6、log_slow_rate_limit = 1 多少次查询才记录，mariadb特有

MariaDB [hellodb]> show variables like 'log_slow_rate_limit';          
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| log_slow_rate_limit | 1     |
+---------------------+-------+
1 row in set (0.00 sec)

7、log_slow_verbosity= Query_plan,explain 记录内容

MariaDB [hellodb]> show variables like 'log_slow_verbosity'; 
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| log_slow_verbosity |       |
+--------------------+-------+
1 row in set (0.00 sec)

二进制日志

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

中继日志：relay log

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

二进制日志记录格式

二进制日志记录三种格式

​ 基于“语句”记录：statement，记录语句，默认模式

​ 基于“行”记录：row，记录数据，日志量较大

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

格式配置

show variables like 'binlog_format';

例：

MariaDB [hellodb]> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.01 sec)

二进制日志文件的构成

有两类文件

日志文件：mysql|mariadb-bin.文件名后缀，二进制格式

​ 如： mariadb-bin.000001

索引文件：mysql|mariadb-bin.index，文本格式

[root@centos7 ~]#ls /var/lib/mysql/ -l
total 28708
-rw-rw----. 1 mysql mysql    16384 Dec  4 19:33 aria_log.00000001
-rw-rw----. 1 mysql mysql       52 Dec  4 19:33 aria_log_control
drwx------. 2 mysql mysql      272 Nov 29 21:04 hellodb
-rw-rw----. 1 mysql mysql 18874368 Dec  4 19:33 ibdata1
-rw-rw----. 1 mysql mysql  5242880 Dec  4 19:33 ib_logfile0
-rw-rw----. 1 mysql mysql  5242880 Nov 29 09:42 ib_logfile1
-rw-rw----. 1 mysql mysql      245 Dec  4 19:33 mariadb-bin.000001
-rw-rw----. 1 mysql mysql       21 Dec  4 19:33 mariadb-bin.index

二进制日志相关的服务器变量：

1、sql_log_bin=ON|OFF：是否记录二进制日志，默认ON

MariaDB [hellodb]> show variables like 'sql_log_bin';  
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | ON    |
+---------------+-------+
1 row in set (0.01 sec)

2、log_bin=/PATH/BIN_LOG_FILE：指定文件位置；默认OFF，表示不启用二进制日志功能.

MariaDB [hellodb]> show variables like 'log_bin';    
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |    在配置文件也可以加上路径log_bin=/data/log
+---------------+-------+
1 row in set (0.00 sec)

上述两项都同时开启才能启用二进制日志

3、binlog_format=STATEMENT|ROW|MIXED：二进制日志记录的格式，默认STATEMENT

4、max_binlog_size=1073741824：单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G

说明：文件达到上限时的大小未必为指定的精确值

MariaDB [hellodb]> show variables like 'max_binlog_size';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.01 sec)

5、sync_binlog=1|0：设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘

MariaDB [hellodb]> show variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 0     |
+---------------+-------+
1 row in set (0.00 sec)

6、expire_logs_days=N：二进制日志可以自动删除的天数。 默认为0，即不自动删除二进制日志

MariaDB [hellodb]> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+
1 row in set (0.00 sec)

二进制日志相关配置

查看mariadb自行管理使用中的二进制日志文件列表，及大小

show {binary | master} logs

例：

MariaDB [hellodb]> show binary logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       245 |
+--------------------+-----------+
1 row in set (0.00 sec)

查看使用中的二进制日志文件

show master status

例：

MariaDB [hellodb]> show master status;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 |      245 |              |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

查看二进制文件中的指定内容

SHOW BINLOG EVENTS [IN 'log_name'][FROM pos] [LIMIT [offset,] row_count];

例：

MariaDB [hellodb]> show binlog events in 'mariadb-bin.000001';
+--------------------+-----+-------------+-----------+-------------+---------------------------------------------------+
| Log_name           | Pos | Event_type  | Server_id | End_log_pos | Info                                              |
+--------------------+-----+-------------+-----------+-------------+---------------------------------------------------+
| mariadb-bin.000001 |   4 | Format_desc |         1 |         245 | Server ver: 5.5.56-MariaDB, Binlog ver: 4         |
| mariadb-bin.000001 | 245 | Query       |         1 |         316 | BEGIN                                             |
| mariadb-bin.000001 | 316 | Intvar      |         1 |         344 | INSERT_ID=5                                       |
| mariadb-bin.000001 | 344 | Query       |         1 |         444 | use `hellodb`; insert teachers (name) values('a') |
| mariadb-bin.000001 | 444 | Xid         |         1 |         471 | COMMIT /* xid=17 */                               |
| mariadb-bin.000001 | 471 | Query       |         1 |         542 | BEGIN                                             |
| mariadb-bin.000001 | 542 | Intvar      |         1 |         570 | INSERT_ID=6                                       |
| mariadb-bin.000001 | 570 | Query       |         1 |         670 | use `hellodb`; insert teachers (name) values('b') |
| mariadb-bin.000001 | 670 | Xid         |         1 |         697 | COMMIT /* xid=18 */                               |
+--------------------+-----+-------------+-----------+-------------+---------------------------------------------------+
9 rows in set (0.00 sec)

日志

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

mysqlbinlog [OPTIONS] log_file…

​ –start-position=# 指定开始位置

​ –stop-position=#

​ –start-datetime=

​ –stop-datetime=

​ 时间格式：YYYY-MM-DD hh:mm:ss

​ –base64-output[=name]

​ -v -vvv

例：

mysqlbinlog /var/lib/mysql/mariadb-bin.000003 -v  
mysqlbinlog --start-position=245 --stop-position=316 /var/lib/mysql/mariadb-bin.000001 -vvv
mysqlbinlog --start-datetime="2018-01-30 20:30:10" --stopdatetime="2018-01-30 20:35:22" /var/lib/mysql/mariadb-bin.000003 -vvv

清除指定二进制日志：

PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }

例：

PURGE BINARY LOGS BEFORE '2017-01-23';   
PURGE BINARY LOGS BEFORE '2017-03-22 09:25:30';  
purge binary logs to 'mariadb-bin.000003'; 删除3之前的日志

MariaDB [hellodb]> show binary logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       742 |
| mariadb-bin.000002 |       290 |
| mariadb-bin.000003 |       290 |
| mariadb-bin.000004 |       245 |
+--------------------+-----------+
4 rows in set (0.00 sec)

MariaDB [hellodb]> purge binary logs to 'mariadb-bin.000003';
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> show binary logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000003 |       290 |
| mariadb-bin.000004 |       245 |
+--------------------+-----------+
2 rows in set (0.01 sec)

删除所有二进制日志，index文件重新记数

RESET MASTER [TO #];

删除所有二进制日志文件，并重新生成日志文件，文件名从#开始记数，默认从1开始，一般是master主机第一次启动时执行，

MariaDB [hellodb]> show binary logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       290 |
| mariadb-bin.000002 |       290 |
| mariadb-bin.000003 |       290 |
| mariadb-bin.000004 |       290 |
| mariadb-bin.000005 |       290 |
| mariadb-bin.000006 |       245 |
+--------------------+-----------+
6 rows in set (0.00 sec)


MariaDB [hellodb]> reset master;
Query OK, 0 rows affected (0.01 sec)

MariaDB [hellodb]> show binary logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       245 |
+--------------------+-----------+
1 row in set (0.00 sec)

切换日志文件：

flush logs;

例：

MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       245 |
+------------------+-----------+
1 row in set (0.00 sec)

MariaDB [hellodb]> flush logs;
Query OK, 0 rows affected (0.01 sec)

MariaDB [hellodb]> flush logs;
Query OK, 0 rows affected (0.02 sec)

MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       288 |
| mysql-bin.000002 |       288 |
| mysql-bin.000003 |       245 |
+------------------+-----------+
3 rows in set (0.00 sec)