查看MySQL4种日志参数及配置

1 查看版本
mysql> show variables like '%version';
+------------------+---------------+
| Variable_name    | Value         |
+------------------+---------------+
| innodb_version   | 5.7.20        |
| protocol_version | 10            |
| tls_version      | TLSv1,TLSv1.1 |
| version          | 5.7.20-log    |
+------------------+---------------+




2 MySQL通用日志
2.1 临时配置
查看参数
mysql> show variables like '%general%';
+------------------+-----------------------------------+
| Variable_name    | Value                             |
+------------------+-----------------------------------+
| general_log      | ON                                |
| general_log_file | /apps/mysql/log/mysql_general.log |
+------------------+-----------------------------------+
2 rows in set (0.05 sec)


设置
开启通用日志查询：set global general_log = on;
关闭通用日志查询：set global general_log = off;




日志格式：
[root@mysql-5 log]# tail -10 mysql_general.log 
2017-12-05T11:50:41.252042Z         4 Query     show variables like '%version'
2017-12-05T11:51:44.149565Z         4 Query     help 'show'
2017-12-05T11:51:58.302866Z         4 Query     help 'show variables'
2017-12-05T11:53:03.843965Z         4 Query     show variables like '%general'
2017-12-05T11:53:09.659452Z         4 Query     show variables like '%general%'
2017-12-05T11:54:35.341783Z         4 Query     set global general_log=off




2.2 配置
永久配置
vi /etc/my.cnf
general_log = ON
general_log_file=/apps/mysql/log/mysql_general.log






3 查看当前慢文件的格式
mysql> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)
当前慢文件格式为file ，存储在数据库的数据文件中的hostname.log
可以是table格式，存储在数据库的数据文件中的mysql.general_log
设置通用日志输出为表方式：
set global log_output = 'file';
设置通用日志输出为表和文件方式：
set global log_output = 'file,table';


4 慢查询日志：
记录所有执行时间超过long_query_time秒的所有查询或者不适用索引的查询
默认情况下，MySQL不开启慢查询日志，long_query_time的默认值为10，即运行时间超过10s的语句是慢查询语句。
一般来说，慢查询发生在大表中，且查询的字段没有建立索引，此时，要匹配查询的字段会对全表进行扫描，耗时查long_query_time表


4.1 查看状态
mysql> show variables like '%query%';
+------------------------------+--------------------------+
| Variable_name                | Value                    |
+------------------------------+--------------------------+
| binlog_rows_query_log_events | OFF                      |
| ft_query_expansion_limit     | 20                       |
| have_query_cache             | YES                      |
| long_query_time              | 10.000000                |
| query_alloc_block_size       | 8192                     |
| query_cache_limit            | 1048576                  |
| query_cache_min_res_unit     | 4096                     |
| query_cache_size             | 1048576                  |
| query_cache_type             | OFF                      |
| query_cache_wlock_invalidate | OFF                      |
| query_prealloc_size          | 8192                     |
| slow_query_log               | ON                       |
| slow_query_log_file          | /apps/mysql/log/slow.log |
+------------------------------+--------------------------+
13 rows in set (0.01 sec)


其中：
1）slow_query_log 的值为ON 为开启慢查询日志，off表示关闭慢查询日志
2） slow_query_log_file 的值是记录的慢查询日志到文件中（默认为主机 名.log）
3）long_query_time 指定了慢查询的阈值，即执行语句的时间若超过这个值则为慢查询语句
4）log_queries_not_using_indexes 如果该值是ON，则会记录所有没有利用索引来进行查询的语句，前提是slow_query_log 的值也是ON,否则，不会




查询当前慢查询的语句个数：
show global status like '%slow%';
可以通过查询语句查看慢查询的语句：
select * from mysql.slow_log;


永久配置：
long_query_time=10
slow_query_log_file = /apps/mysql/log/slow.log
expire_logs_days = 5


日志格式;
# User@Host: myh2017[myh2017] @  [10.26.43.80]
# Query_time: 7.893942  Lock_time: 0.000066 Rows_sent: 15  Rows_examined: 4583043
SET timestamp=1512530520;
SELECT 
                                           l.cid,
                                           l.wid,
                                           b.brand,
                                           w.wechat,
                                           w.server_name,
                                           SUM(if(l.act_type=1,1,0)) AS t1,
                                           SUM(if(l.act_type=2,1,0)) AS t2,
                                           SUM(if(l.act_type=3,1,0)) AS t3,
                                           SUM(if(l.act_type=4,1,0)) AS t4,
                                           SUM(if(l.act_type=5,1,0)) AS tt5,
                                           SUM(if(l.act_type=6,1,0)) AS tt6,
                                           SUM(if(l.act_type=7,1,0)) AS t7
                                FROM cps_data_log l
                                LEFT JOIN cps_wechat w ON w.id=l.wid
                                LEFT JOIN cps_brand b ON b.id=w.brand
                                WHERE  l.wid>0  AND b.id='15' AND l.cid in('671','672') AND l.add_time>='1512518401' AND l.add_time<='1512572459'
                                GROUP BY l.wid,l.cid
                                LIMIT 0 , 15;




5 错误日志
MySQL错误日志世纪路MySQL运行过程中较为严重的警告和错误信息，以及MySQL每次启动和关闭的详细信息。错误日志的命名通常为服务器主机名.err


mysql> show variables like '%err%';
+---------------------+--------------+
| Variable_name       | Value        |
+---------------------+--------------+
| binlog_error_action | ABORT_SERVER |
| error_count         | 0            |
| log_error           | ./error.log  |
| log_error_verbosity | 3            |
| max_connect_errors  | 100          |
| max_error_count     | 64           |
| slave_skip_errors   | OFF          |
+---------------------+--------------+
7 rows in set (0.00 sec)


日志格式：
2017-12-05T11:44:31.633593Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2017-12-05T11:44:31.636804Z 0 [Note] InnoDB: Waiting for purge to start
2017-12-05T11:44:31.691053Z 0 [Note] InnoDB: 5.7.20 started; log sequence number 2565654
2017-12-05T11:44:31.692462Z 0 [Note] InnoDB: Loading buffer pool(s) from /apps/mysql-5.7.20/data/ib_buffer_pool
2017-12-05T11:44:31.693457Z 0 [Note] Plugin 'FEDERATED' is disabled.
2017-12-05T11:44:31.695431Z 0 [Note] InnoDB: Buffer pool(s) load completed at 171205 19:44:31
2017-12-05T11:44:31.716218Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2017-12-05T11:44:31.716275Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2017-12-05T11:44:31.716398Z 0 [Note] IPv6 is available.
2017-12-05T11:44:31.716421Z 0 [Note]   - '::' resolves to '::';
2017-12-05T11:44:31.716448Z 0 [Note] Server socket created on IP: '::'.
2017-12-05T11:44:31.734030Z 0 [Note] Event Scheduler: Loaded 0 events
2017-12-05T11:44:31.734671Z 0 [Note] /apps/mysql/bin/mysqld: ready for connections.
Version: '5.7.20-log'  socket: '/apps/mysql/tmp/mysql.sock'  port: 3306  Source distribution
2017-12-05T11:44:31.734706Z 0 [Note] Executing 'SELECT * FROM INFORMATION_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine. You may use the startup option '--disable-partition-engine-check' to skip this check. 
2017-12-05T11:44:31.734713Z 0 [Note] Beginning of list of non-natively partitioned tables
2017-12-05T11:44:31.754505Z 0 [Note] End of list of non-natively partitioned tables




6 二进制日志文件
目的：
包含了所有更新了的数据或者潜在更新了的数据，包含关于每个更新数据库的语句的执行时间信息
尽可能将数据库恢复到是巨酷故障点，因为二进制日志包含备份后进行的所有更新，用于在主复制服务器上记录所有将发生送给从服务器的语句


查看是否启用二进制日志：
mysql> show variables like '%log_bin%';
+---------------------------------+----------------------------------+
| Variable_name                   | Value                            |
+---------------------------------+----------------------------------+
| log_bin                         | ON                               |
| log_bin_basename                | /apps/mysql/data/mysql-bin       |
| log_bin_index                   | /apps/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                              |
| log_bin_use_v1_row_events       | OFF                              |
| sql_log_bin                     | ON                               |
+---------------------------------+----------------------------------+
6 rows in set (0.01 sec)
查看所有的二进制参数
mysql> show variables like '%binlog%';
+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| binlog_cache_size                       | 32768                |
| binlog_checksum                         | CRC32                |
| binlog_direct_non_transactional_updates | OFF                  |
| binlog_error_action                     | ABORT_SERVER         |
| binlog_format                           | ROW                  |
| binlog_group_commit_sync_delay          | 0                    |
| binlog_group_commit_sync_no_delay_count | 0                    |
| binlog_gtid_simple_recovery             | ON                   |
| binlog_max_flush_queue_time             | 0                    |
| binlog_order_commits                    | ON                   |
| binlog_row_image                        | FULL                 |
| binlog_rows_query_log_events            | OFF                  |
| binlog_stmt_cache_size                  | 32768                |
| innodb_api_enable_binlog                | OFF                  |
| innodb_locks_unsafe_for_binlog          | OFF                  |
| log_statements_unsafe_for_binlog        | ON                   |
| max_binlog_cache_size                   | 18446744073709547520 |
| max_binlog_size                         | 1073741824           |
| max_binlog_stmt_cache_size              | 18446744073709547520 |
| sync_binlog                             | 1                    |
+-----------------------------------------+----------------------+
20 rows in set (0.01 sec)


查看文件的位置
mysql> show variables like '%datadir%';
+---------------+-------------------+
| Variable_name | Value             |
+---------------+-------------------+
| datadir       | /apps/mysql/data/ |
+---------------+-------------------+
1 row in set (0.01 sec)




日志格式：
[root@mysql-5 data]# mysqlbinlog mysql-bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#171205 19:43:01 server id 10  end_log_pos 123 CRC32 0x147eacd6         Start: binlog v 4, server v 5.7.20-log created 171205 19:43:01 at startup
ROLLBACK/*!*/;
BINLOG '
RYYmWg8KAAAAdwAAAHsAAAAAAAQANS43LjIwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABFhiZaEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AdasfhQ=
'/*!*/;
# at 123
#171205 19:43:01 server id 10  end_log_pos 154 CRC32 0xb947e916         Previous-GTIDs
# [empty]
# at 154
#171205 19:44:26 server id 10  end_log_pos 177 CRC32 0xec8a765c         Stop
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;