MYSQL日志管理

作者: 你好_请关照 | 来源:发表于2019-08-29 18:12 被阅读0次

Mysql日志管理：

介绍：错误日志、二进制日志、慢日志

1、错误日志

配置方式：在配置文件中（my.cnf）中添加log_error=path 即可
错误日志默认就是开启的，默认存放在数据目录下，日志名称主机名.err 的文件
**注意事项：此日志的存放位置需要存放在启动进程用户有权限管理的目录下，并且日志文件
本身属主、属组是服务进程管理用户也就是配置文件中user等于后面的用户
查看错误日志时只需要查看文件中[ERROR]的行即可

[root@db01 /data/mysql/data]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
log_error=/data/mysql/data/error.log
[mysql]
socket=/tmp/mysql.sock

[root@db01 /data/mysql/data]# ll error.log 
-rw-r-----. 1 mysql mysql 10866 Aug 28 21:52 error.log
[root@db01 /data/mysql/data]#

2、二进制日志（binlog）

2.1 二进制日志作用：数据恢复、主从复制

2.2 二进制日志记录了什么：记录数据库所有变化的操作，包括DDL、DCL、DML，其实就是SQL语句

2.3 二进制日志的配置：

server_id=6 #主从复制需要用到
log_bin=/data/binlog/mysql-binlog/mysql-bin #指定存放位置mysql-bin名称前缀
binlog_format=row #日志格式

[root@db01 /data/binlog]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
log_error=/data/mysql/data/error.log
binlog_format=row
log_bin=/data/binlog/mysql-bin
[mysql]
socket=/tmp/mysql.sock

[root@db01 /data/binlog]#

2.4 二进制日志格式

DDL和DCL，以statement（语句）方式直接记录SQL
DML（insert、update、delete），记录的是已经提交的事物
SBR ：statement，记录具体语句（5.6以下版本常用）
RBR ：Row ,记录数据行的变化（常用）
MBR ：mixed,混合模式（一般不用）

2.5 日志内容

以事件(event) 作为记录的最小单元

以下（截取）为一个事件，以一个at开始，到下一个at结束

[root@db01 /data/binlog]# mysqlbinlog mysql-bin.000001 
# at 534
#190829 15:02:50 server id 6  end_log_pos 625 CRC32 0xaf1341d1  Query   thread_id=3    exec_time=0  error_code=0
create database bbb

2.6 mysql客户端下二进制日志的基本查看

查看二进制日志有多少个

mysql> show binary logs; 
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       625 |
+------------------+-----------+
1 row in set (0.00 sec)

flush logs 刷新二进制日志

mysql> flush logs; #刷新二进制日志
Query OK, 0 rows affected (0.15 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       672 |
| mysql-bin.000002 |       154 |
+------------------+-----------+
2 rows in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

mysql>

查看正在使用哪个二进制日志

mysql> show master  status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

查看某二进制日志的事件信息
说明：
对于DDL、DCL操作，一个操作（info列）就是一个事件（ Pos 892 ---End_log_pos 999）。
对于DML语句在info列，以BEGIN 开始一个事物，以COMMIT结束一个事物（ Pos 390---End_log_pos 712）。

mysql> show binlog events in 'mysql-bin.000005';
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                          |
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------+
| mysql-bin.000005 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.26-log, Binlog ver: 4         |
| mysql-bin.000005 | 123 | Previous_gtids |         6 |         154 |                                               |
| mysql-bin.000005 | 154 | Anonymous_Gtid |         6 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'          |
| mysql-bin.000005 | 219 | Query          |         6 |         325 | use `aaa`; create table aa(
id int not null ) |
| mysql-bin.000005 | 325 | Anonymous_Gtid |         6 |         390 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'          |
| mysql-bin.000005 | 390 | Query          |         6 |         461 | BEGIN                                         |
| mysql-bin.000005 | 461 | Table_map      |         6 |         505 | table_id: 108 (aaa.aa)                        |
| mysql-bin.000005 | 505 | Write_rows     |         6 |         545 | table_id: 108 flags: STMT_END_F               |
| mysql-bin.000005 | 545 | Xid            |         6 |         576 | COMMIT /* xid=45 */                           |
| mysql-bin.000005 | 576 | Anonymous_Gtid |         6 |         641 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'          |
| mysql-bin.000005 | 641 | Query          |         6 |         712 | BEGIN                                         |
| mysql-bin.000005 | 712 | Table_map      |         6 |         756 | table_id: 108 (aaa.aa)                        |
| mysql-bin.000005 | 756 | Write_rows     |         6 |         796 | table_id: 108 flags: STMT_END_F               |
| mysql-bin.000005 | 796 | Xid            |         6 |         827 | COMMIT /* xid=48 */                           |
| mysql-bin.000005 | 827 | Anonymous_Gtid |         6 |         892 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'          |
| mysql-bin.000005 | 892 | Query          |         6 |         999 | create database ccc charset utf8mb4           |
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------+
16 rows in set (0.00 sec)

mysql>

2.7 二进制日志的内容查看和截取

(1)查看
查看 mysqlbinlog mysql-bin.000005
mysql日志详细显示mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000005

(2)截取二进制日志恢复数据

例子:

mysql> create database bak charset utf8mb4;
mysql> use bak
mysql> create table t1 (id int) charset utf8mb4;
mysql> insert into t1 values(1),(2),(3);
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
mysql> drop database bak;

第二步恢复过程:

mysql> show master status ;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |     1764 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

    
mysql> show binlog events in 'mysql-bin.000004'
| mysql-bin.000004 | 1065 | Query          |         6 |        1172 | create database bak charset utf8mb4     
| mysql-bin.000004 | 1675 | Query          |         6 |        1764 | drop database bak   

mysqlbinlog --start-position=1065 --stop-position=1675   mysql-bin.000004 >/tmp/bin.sql     
[root@db01 /data/binlog]# mysql -uroot -p123456</tmp/bin.sql

mysqlbinlog --start-position=1065 --stop-position=1675 mysql-bin.000004 >/tmp/bin.sql
-b database :mysqlbinlog 中-b参数后面加库名，指明过滤某个库的二进制日志

2.8 日志滚动

第一种：/etc/init.d/mysqld restart
第二种：mysql> flush logs;
mysql> show binary logs;
mysql> select @@max_binlog_size/1024/1024;

2.9删除二进制日志

mysql>help purge;
mysql> PURGE BINARY LOGS TO 'mysql-bin.000004'; 删除000004号之前的所有文件

mysql>PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26'; #按日期删除

可以在配置文件中定制二进制文件的过期时间（一般最少设定为数据库全备期间）

mysql> select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

mysql>

3、slow log（慢日志）

3.1 作用：记录MYSQL运行期间执行较慢的语句

3.2 查看慢日志是否打开

slow_query_log 状态为ON 表示打开

mysql> show variables like '%slow%';
+---------------------------+--------------------------------+
| Variable_name             | Value                          |
+---------------------------+--------------------------------+
| log_slow_admin_statements | OFF                            |
| log_slow_slave_statements | OFF                            |
| slow_launch_time          | 2                              |
| slow_query_log            | ON                             |
| slow_query_log_file       | /data/mysql/data/db01-slow.log |
+---------------------------+--------------------------------+
5 rows in set (0.01 sec)

mysql>

3.3 查看慢日志记录时间

当查询语句大于等于long_query_time这个值得时间(单位S)就会被定义为查询慢的语句，就会被慢日志记录起来

mysql> show variables like '%long%';
+----------------------------------------------------------+-----------+
| Variable_name                                            | Value     |
+----------------------------------------------------------+-----------+
| long_query_time                                          | 10.000000 |
| performance_schema_events_stages_history_long_size       | 10000     |
| performance_schema_events_statements_history_long_size   | 10000     |
| performance_schema_events_transactions_history_long_size | 10000     |
| performance_schema_events_waits_history_long_size        | 10000     |
+----------------------------------------------------------+-----------+
5 rows in set (0.00 sec)

3.4查看是否开启未使用索引记录功能

当log_queries_not_using_indexes 这个功能被开启，会把未使用索引查询的语句记录到慢日志中

mysql> show variables like '%indexes%';
+----------------------------------------+-------+
| Variable_name                          | Value |
+----------------------------------------+-------+
| log_queries_not_using_indexes          | OFF   |
| log_throttle_queries_not_using_indexes | 0     |
+----------------------------------------+-------+
2 rows in set (0.00 sec)

mysql>

3.5 配置

slow_query_log=ON #打开慢日志
slow_query_log_file=/data/mysql/data/db01-slow.log #默认路径可手动指定
long_query_time=5 #超过多长时间被定义为慢语句
log_queries_not_using_indexes=ON #开启此功能，会将未走索引的语句记录到慢日志中

3.5慢日志分析工具

[root@db01 /data/mysql/data]# mysqldumpslow -s c -t 3 db01-slow.log