【MySQL】日志管理

---

一.日志分类

1. error log 错误日志 ：排错
2. bin log 二进制日志 ：备份，增量备份 DDL DML DCL
3. Relay log 中继日志 ：复制 接收 replication master
4. slow log 慢查询日志 ：调优 查询时间超过指定值

mysql> show variables like 'log_%';   //日志参数
+----------------------------------------+----------------------------------------+
| Variable_name                          | Value                                  |
+----------------------------------------+----------------------------------------+
| log_bin                                | ON                                     |
| log_bin_basename                       | /var/lib/mysql/binlog                  |
| log_bin_index                          | /var/lib/mysql/binlog.index            |
| log_bin_trust_function_creators        | OFF                                    |
| log_bin_use_v1_row_events              | OFF                                    |
| log_error                              | /var/log/mysqld.log                    |
| log_error_services                     | log_filter_internal; log_sink_internal |
| log_error_suppression_list             |                                        |
| log_error_verbosity                    | 2                                      |
| log_output                             | FILE                                   |
| log_queries_not_using_indexes          | OFF                                    |
| log_slave_updates                      | ON                                     |
| log_slow_admin_statements              | OFF                                    |
| log_slow_extra                         | OFF                                    |
| log_slow_slave_statements              | OFF                                    |
| log_statements_unsafe_for_binlog       | ON                                     |
| log_throttle_queries_not_using_indexes | 0                                      |
| log_timestamps                         | UTC                                    |
+----------------------------------------+----------------------------------------+
18 rows in set (0.17 sec)

---

二.错误日志

1.是什么？

• 错误日志通过记录了MySQL数据库启动关闭信息，以及服务器运行过程中所发生的任何严重的错误信息。通常，当数据库出现问题不能正常启动，应当首先想到的就是查看错误日志。从中可以看到此日志文件记录的MySQL数据库启动和关闭信息。

2.存放位置（/etc/my.cnf）

1. –log-error=[file-name]用来指定错误日志存放的位置（手工）
   2.配置文件/etc/my.cnf
   例：log-error=/var/log/mysqld.log

3.日志格式：

时间 [错误级别] 错误信息

mysql> show variables like 'log_error';   //MySQL中错误日志信息
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| log_error     | /var/log/mysqld.log |
+---------------+---------------------+
1 row in set (0.00 sec)

[root@tong log]# vim /var/log/mysqld.log  //查看具体错误日志具体内容
2019-03-05T18:22:37.709122Z 203 **[Warning]** [MY-010055] [Server] IP address '192.168.17.5' could not be resolved: Name or service not known
...... //错误日志文件记录级别（3）：error、warning 和information

---

三.bin log 二进制日志

1.是什么？

• 记录了所有的DDL,DML,DCL语句，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。（1%性能损耗）

附：事务四大特性: ACID 原子性，一致性，隔离性，持久性

2.常用场景：

• 1.MySQL复制时在Master端开启binlog，Mster把它的二进制日志传递给slaves来达到master-slave数据一致的目的。
• 2.备份恢复时，使用mysqlbinlog恢复数据。

3.相关文件：

• 1.二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件
• 2.二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL,DCL,DML语句事件。 （注意：binlog日志与数据库文件在同目录中）

4.存放位置设定（/etc/my.cnf）：

log-bin=[/path_to/file_name]
重启生效

（ expire_logs_days=5设置二进制日志文件的过期时间，这样mysql就会自动删除到期的日志文件，节省磁盘空间）

5.管理binlog日志：

1）查看
mysqlbinlog： binlog是二进制文件，必须使用自带的 mysqlbinlog 命令查看

mysql> show variables like 'log_bin';   //查看binlog启动情况
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)

mysql> show binary logs;   //查看二进制文件
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       500 | No        |
| binlog.000002 |       664 | No        |
| binlog.000003 | 870024191 | No        |
+---------------+-----------+-----------+
3 rows in set (0.00 sec)


[root@tong ~]# cd /var/lib/mysql
[root@tong mysql]# ls
auto.cnf       ca-key.pem       ibdata1       mysql               private_key.pem  test1     undo_002
binlog.000001  ca.pem           ib_logfile0   mysql.ibd           public_key.pem   test2     unit6
binlog.000002  client-cert.pem  ib_logfile1   mysql.sock          server-cert.pem  test3
binlog.000003  client-key.pem   ibtmp1        mysql.sock.lock     server-key.pem   test5
binlog.index   ib_buffer_pool   #innodb_temp  performance_schema  sys              undo_001
[root@tong mysql]# mysqlbinlog binlog.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190304 12:54:13 server id 1  end_log_pos 124 CRC32 0x6ab647b3 	Start: binlog v 4, server v 8.0.15 created 190304 12:54:13 at startup
ROLLBACK/*!*/;
BINLOG '
da98XA8BAAAAeAAAAHwAAAAAAAQAOC4wLjE1AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAB1r3xcEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
CgGzR7Zq
'/*!*/;
# at 124
#190304 12:54:13 server id 1  end_log_pos 155 CRC32 0x94651e68 	Previous-GTIDs
# [empty]
# at 155
#190304 13:01:03 server id 1  end_log_pos 234 CRC32 0xf8470129 	Anonymous_GTID	last_committed=0	sequence_number=1	rbr_only=no	original_committed_timestamp=1551675663367843	immediate_commit_timestamp=1551675663367843	transaction_length=322
# original_commit_timestamp=1551675663367843 (2019-03-04 13:01:03.367843 CST)
# immediate_commit_timestamp=1551675663367843 (2019-03-04 13:01:03.367843 CST)
/*!80001 SET @@session.original_commit_timestamp=1551675663367843*//*!*/;
/*!80014 SET @@session.original_server_version=80015*//*!*/;
/*!80014 SET @@session.immediate_server_version=80015*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 234
#190304 13:01:03 server id 1  end_log_pos 477 CRC32 0x3d08afcf 	Query	thread_id=8	exec_time=0	error_code=0	Xid = 6
SET TIMESTAMP=1551675663.359020/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
ALTER USER 'root'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$\r9h \Y"56H1cj2jcqzgK61MWZWf.FuHA5qvSoMDIxvSEXfc4vNJaAU7'
/*!*/;
....

mysql> show master status;   //当前binlog日志
+---------------+-----------+--------------+------------------+-------------------+
| File          | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+-----------+--------------+------------------+-------------------+
| binlog.000003 | 870024191 |              |                  |                   |
+---------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> show master logs;  //总的
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       500 | No        |
| binlog.000002 |       664 | No        |
| binlog.000003 | 870024191 | No        |
+---------------+-----------+-----------+
3 rows in set (0.01 sec)

2)截断binlog日志

• 重启 mysqld 会截断
• flush logs 会截断

3)删除binlog日志

1. reset master：删除所有binlog文件，然后从新生成一个从000001开始的文件
2. PURGE BINARY LOGS TO ‘mysql-bin.010’; (删除mysql-bin.010之前的所有日志
3. mysqladmin flush-log（–expire_logs_days=x，参数设置binlog日志的过期天数，触发日志文件更新，从当前日期开始前多少天的日志全部删除）

---

四.慢查询日志

1.是什么？

• 记录了执行时间超过long_query_time参数值的sql语句。慢查询日志可以有效的帮助我们发现实际应用中sql的性能问题，找出执行效率低下的sql语句

2.设置（/etc/my.cnf）：

slow_query_log=1 //打开慢查询日志
slow_query_log_file=/var/log/mysql-slow/slow.log //指定慢查询日志存放的位置（–log-slow-queries[=/path_to/file_name] ）
long_query_time=3 //执行超过3秒的sql会被记录下来
(重启mysqld生效）

3.查看日志：

mysqldumpslow
mysqldumpslow --h //查看帮助
-s 排序参数（按照什么方式起来排序）
-t n 显示头n条记录
at: 平均查询时间
-c 总执行次数

例：mysqldumpslow -s at -t 5 slow.log //平均执行时间最长的前5条SQL

mysql> show variables like 'long%';    //当前执行超过10秒的sql会被记录下来
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

mysql> set long_query_time=1;    //修改当前执行超过1秒的sql会被记录下来
Query OK, 0 rows affected (0.02 sec)

mysql> show variables like 'long%';  
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.01 sec)

mysql> show variables like 'slow%';  
+---------------------+------------------------------+
| Variable_name       | Value                        |
+---------------------+------------------------------+
| slow_launch_time    | 2                            |
| slow_query_log      | OFF                          |
| slow_query_log_file | /var/lib/mysql/tong-slow.log |
+---------------------+------------------------------+
3 rows in set (0.00 sec)

mysql> set global slow_query_log='ON' ;  //打开慢查询日志
Query OK, 0 rows affected (0.03 sec)