一. 查看正在执行的SQL语句
但是有个弊端，就是只能查看正在执行的sql语句，对应历史记录，查看不到。好处是不用设置，不会保存。
use information_schema;
show processlist;
或者：
-- select * from information_schema.PROCESSLIST where info is not null;
二. 开启日志模式
1. 通用日志log_output
- show variables like '%log_output%'; //查看参数
- SET GLOBAL log_output = 'TABLE';SET GLOBAL general_log = 'ON'; //日志开启
- SET GLOBAL log_output = 'TABLE'; SET GLOBAL general_log = 'OFF'; //日志关闭
2. 通用日志general_log
- show variables like '%general%'; //查看参数
- 开启通用日志查询：set global general_log = on; //设置
- 关闭通用日志查询：set global general_log = off; //设置
- 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
- 永久配置
vi /etc/my.cnf
general_log = ON
general_log_file=/apps/mysql/log/mysql_general.log
- 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';
3. 慢查询日志slow_query_log
（1）记录所有执行时间超过long_query_time秒的所有查询或者不适用索引的查询
（2）默认情况下，MySQL不开启慢查询日志，long_query_time的默认值为10，即运行时间超过10s的语句
是慢查询语句。
（3）一般来说，慢查询发生在大表中，且查询的字段没有建立索引，此时，要匹配查询的字段会对全表进
行扫描，耗时查long_query_time表
（4）查看状态：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,否则，不会
（5）查询当前慢查询的语句个数：show global status like '%slow%';
（6）可以通过查询语句查看慢查询的语句：select * from mysql.slow_log;
（7）永久配置：long_query_time=10
slow_query_log_file = /apps/mysql/log/slow.log
expire_logs_days = 5
（8）日志格式
# 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;
4. 设置binlog
（1）MySQL的二进制日志只记录变更语句，不记录查询语句（根据记录个事SBR,RBR,MBR进行相对应的
格式存储）
---包含了所有更新了的数据或者潜在更新了的数据，包含关于每个更新数据库的语句的执行时间信息
---尽可能将数据库恢复到是巨酷故障点，因为二进制日志包含备份后进行的所有更新，用于在主复制服务
器上记录所有将发生送给从服务器的语句
（2）二进制日志查看：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)
（3）二进制所有参数查看：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)
（4）日志位置：log_bin=配置路径
（5）日志格式
[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*/;
5. 错误日志
（1）MySQL错误日志世纪路MySQL运行过程中较为严重的警告和错误信息，以及MySQL每次启动和关闭的
详细信息。错误日志的命名通常为服务器主机名.err。
（2）参数查看：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)
（3）路径配置：log_error=/data/3306/data/mysql_errpr.log
（4）日志格式：
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. 配置log，将MySQL执行的每行命令都记录下来（推荐）
（1）此参数配置后所有执行的SQL语句都会记录其中，配置后要restart MySQL即可
（2）配置：log = /tmp/mysql.log
三. 利用tee命令记录执行过的SQL语句以及执行结果
（1）链接数据库时使用
---mysql -uroot -proot --tee=路径（不需要加引号）
注释：此时我们登入数据库后的所有操作和结果都会记录到所配置的路径当中
（2）链接上数据库使用
tee + 路径
操作SQL语句
notee；
注释：直接在MySQL中tee加路径后会把之后的SQL操作及结果记录其中，直到notee结束
————————————————
版权声明：本文参考CSDN博主「率毛毛虫」的文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/zyb378747350/article/details/78728886
