首先说明下配置大部分可以直接执行命令动态配置，适用于不能重启mysql情况，比如线上服务器不能轻易重启，但是之后重启后配合 失效，还有一种就是直接修改配置文件，需要重启mysql后配置永久有效。
1、开启general_log (查看执行的所有sql)：
#(1)在mysql命令行或者客户端管理工具中执行
mysql > show variables like "%general_log%";
+------------------+------------------------------+
| Variable_name | Value |
+------------------+------------------------------+
| general_log | ON |
| general_log_file | /tmp/mariadb_general_log.log |
+------------------+------------------------------+
#可以看到设置到两个变量，一个是开关，一个是general_log的文件保存路径。
#(2)分别执行开启日志以及日志路径和日志文件名
mysql > SET GLOBAL general_log_file = '/tmp/mariadb_general_log.log';
mysql > SET GLOBAL general_log = 'ON';
#(3)配置文件里面修改，需要重启mysql服务
[root@node ~]# vim /etc/my.cnf
[mysqld]
general_log_file=/tmp/mariadb_general_log.log
general_log=on
#(4)还要注意：这时执行的所有sql都会别记录下来，方便查看，但是如果重启mysql就会停止记录需要重新设置
mysql > SHOW VARIABLES LIKE "log_output%";
#如果是NONE，说明不输出，如果是file就是输出到日志文件，如果是table就是输出到mysql库中的general_log表中，这个按需配置，设置方式：
mysql > SET GLOBAL log_output='TABLE,FILE'
#将日志记录到表 --- 如果不方便查看文件，也可以将日志直接存放到表里面（适合mysql数据库在远程）
mysql > set global log_output='table';
mysql > set global general_log=on;
2、查看general_log日志内容
[root@node ~]# less /tmp/mariadb_general_log.log
/usr/sbin/mysqld, Version: 5.7.24-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
2020-04-13T01:48:04.405638Z 93 Query SET autocommit=1
2020-04-13T01:48:04.407926Z 93 Query SET autocommit=0
2020-04-13T01:48:04.408132Z 93 Query SET SQL_SELECT_LIMIT=1
2020-04-13T01:48:04.408291Z 93 Query SELECT TRIGGER_NAME, TRIGGER_GROUP, NEXT_FIRE_TIME, PRIORITY FROM QRTZ_TRIGGERS WHERE SCHED_NAME = 'schedulerFactoryBean' AND TRIGGER_STATE = 'WAITING' AND NEXT_FIRE_TIME <= 1586742514407 AND (MISFIRE_INSTR = -1 OR (MISFIRE_INSTR != -1 AND NEXT_FIRE_TIME >= 1586742459408)) ORDER BY NEXT_FIRE_TIME ASC, PRIORITY DESC
2020-04-13T01:48:04.409335Z 93 Query SET SQL_SELECT_LIMIT=DEFAULT
2020-04-13T01:48:04.412692Z 93 Query SELECT * FROM QRTZ_TRIGGERS WHERE SCHED_NAME = 'schedulerFactoryBean' AND TRIGGER_NAME = '微信刷卡支付任务' AND TRIGGER_GROUP = 'cloud'
2020-04-13T01:48:04.413079Z 93 Query SELECT * FROM QRTZ_CRON_TRIGGERS WHERE SCHED_NAME = 'schedulerFactoryBean' AND TRIGGER_NAME = '微信刷卡支付任务' AND TRIGGER_GROUP = 'cloud'
2020-04-13T01:48:04.413368Z 93 Query SELECT * FROM QRTZ_JOB_DETAILS WHERE SCHED_NAME = 'schedulerFactoryBean' AND JOB_NAME = '微信刷卡支付任务' AND JOB_GROUP = 'cloud'
2020-04-13T01:48:04.413788Z 93 Query UPDATE QRTZ_TRIGGERS SET TRIGGER_STATE = 'ACQUIRED' WHERE SCHED_NAME = 'schedulerFactoryBean' AND TRIGGER_NAME = '微信刷卡支付任务' AND TRIGGER_GROUP = 'cloud' AND TRIGGER_STATE = 'WAITING'
2020-04-13T01:48:04.414409Z 93 Query INSERT INTO QRTZ_FIRED_TRIGGERS (SCHED_NAME, ENTRY_ID, TRIGGER_NAME, TRIGGER_GROUP, INSTANCE_NAME, FIRED_TIME, SCHED_TIME, STATE, JOB_NAME, JOB_GROUP, IS_NONCONCURRENT, REQUESTS_RECOVERY, PRIORITY) VALUES('schedulerFactoryBean', 'zhmt-alpha00215863146714371586315362717', '微信刷卡支付任务', 'cloud', 'zhmt-alpha0021586314671437', 1586742484414, 1586742490000, 'ACQUIRED', null, null, 0, 0, 5)
2020-04-13T01:48:04.414887Z 93 Query commit
2020-04-13T01:48:04.418340Z 93 Query SET autocommit=1
2020-04-13T01:48:04.423502Z 38 Query select retrytask0_.id as id1_65_, retrytask0_.created_by as created_2_65_, retrytask0_.created_dt as created_3_65_, retrytask0_.deleted as deleted4_65_, retrytask0_.updated_by as updated_5_65_, retrytask0_.updated_dt as updated_6_65_, retrytask0_.business_type as business7_65_, retrytask0_.http_protocal as http_pro8_65_, retrytask0_.max_retry_times as max_retr9_65_, retrytask0_.next_retry_time as next_re10_65_, retrytask0_.request_body as request11_65_, retrytask0_.request_headers as request12_65_, retrytask0_.result_type as result_13_65_, retrytask0_.retry_time_gaps as retry_t14_65_, retrytask0_.retry_times as retry_t15_65_, retrytask0_.retry_type as retry_t16_65_, retrytask0_.status as status17_65_, retrytask0_.target_endpoint as target_18_65_, retrytask0_.target_type as target_19_65_ from retry_task retrytask0_ where retrytask0_.deleted=0 and retrytask0_.retry_type=1 and retrytask0_.result_type=0 and retrytask0_.retry_times<retrytask0_.max_retry_times and (retrytask0_.next_retry_time is not null) and retrytask0_.next_retry_time<now() order by retrytask0_.next_retry_time asc
2020-04-13T01:48:04.423720Z 93 Query SET autocommit=0
2020-04-13T01:48:04.423844Z 93 Query SELECT * FROM QRTZ_LOCKS WHERE SCHED_NAME = 'schedulerFactoryBean' AND LOCK_NAME = 'TRIGGER_ACCESS' FOR UPDATE
2020-04-13T01:48:04.424087Z 93 Query UPDATE QRTZ_TRIGGERS SET TRIGGER_STATE = 'WAITING' WHERE SCHED_NAME = 'schedulerFactoryBean' AND JOB_NAME = '重试任务' AND JOB_GROUP = 'cloud' AND TRIGGER_STATE = 'BLOCKED'
2020-04-13T01:48:04.424584Z 93 Query UPDATE QRTZ_TRIGGERS SET TRIGGER_STATE = 'PAUSED' WHERE SCHED_NAME = 'schedulerFactoryBean' AND JOB_NAME = '重试任务' AND JOB_GROUP = 'cloud' AND TRIGGER_STATE = 'PAUSED_BLOCKED'
2020-04-13T01:48:04.424912Z 93 Query DELETE FROM QRTZ_FIRED_TRIGGERS WHERE SCHED_NAME = 'schedulerFactoryBean' AND ENTRY_ID = 'zhmt-alpha00215863146714371586315362716'
2020-04-13T01:48:04.425200Z 93 Query commit
。。。。。。。
3、开启慢查询
mysql> show variables like 'slow_query%';
+---------------------------+----------------------------------+
| Variable_name | Value |
+---------------------------+----------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /mysql/data/localhost-slow.log |
+---------------------------+----------------------------------+
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
#(1).直接修改配置文件[永久生效 - 需要重启mysql服务]：
[root@node ~]# vim /etc/my.cnf
[mysqld]
slow-query-log=1 --开启慢查询
long_query_time=1 --超过1s认为是慢查询
slow_query_log_file=/var/lib/mysql/mysql-slow.log --慢查询日志文件名，默认跟data目录相同路径
#(2).设置全局变量[临时生效]：
#将 slow_query_log 全局变量设置为“ON”状态
mysql> set global slow_query_log='ON';
#设置慢查询日志存放的位置
mysql> set global slow_query_log_file='/var/lib/mysql/mysql-slow.log';
#查询超过1秒就记录
mysql> set global long_query_time=1;
4、查看慢查询日志
[root@node ~]# vim /var/lib/mysql/mysql-slow.log
/usr/sbin/mysqld, Version: 5.7.24-log (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
/usr/sbin/mysqld, Version: 5.7.24-log (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
/usr/sbin/mysqld, Version: 5.7.24-log (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 2020-03-14T23:21:10.725691Z
# User@Host: api_deploy[api_deploy] @ localhost [127.0.0.1] Id: 2597
# Query_time: 10.561693 Lock_time: 0.000242 Rows_sent: 0 Rows_examined: 2657913
SET timestamp=1584228070;
select parkorderi0_.id as id1_145_, parkorderi0_.created_by as created_2_145_, parkorderi0_.created_dt as created_3_145_, parkorderi0_.deleted as deleted4_145_, parkorderi0_.updated_by as updated_5_145_, parkorderi0_.updated_dt as updated_6_145_, parkorderi0_.advance_charge as advance_7_145_, parkorderi0_.back_pay as back_pay8_145_, parkorderi0_.busi_type as busi_typ9_145_, parkorderi0_.callback_time as callbac10_145_, parkorderi0_.channel_order as channel11_145_, parkorderi0_.collect_fees_setting_name as collect12_145_, parkorderi0_.coupon_pay as coupon_13_145_, parkorderi0_.data_flag as data_fl14_145_, parkorderi0_.data_version as data_ve15_145_, parkorderi0_.dispute_back_pay as dispute16_145_, parkorderi0_.end_date as end_dat17_145_, parkorderi0_.expenses_json as expense18_145_, parkorderi0_.how_long as how_lon19_145_, parkorderi0_.invoice_id as invoice20_145_, parkorderi0_.make_up_payno as make_up21_145_, parkorderi0_.money_pay as money_p22_145_, parkorderi0_.no as no23_145_, parkorderi0_.offline_back_pay_flag as offline24_145_, parkorderi0_.offline_back_pay_time as offline25_145_, parkorderi0_.paper_invoice_id as paper_i26_145_, parkorderi0_.order_park_order_no as order_p46_145_, parkorderi0_.pay_channel as pay_cha27_145_, parkorderi0_.pay_terminal_type as pay_ter28_145_, parkorderi0_.pay_time as pay_tim29_145_, parkorderi0_.pay_type as pay_typ30_145_, parkorderi0_.percent_json as percent31_145_, parkorderi0_.phone as phone32_145_, parkorderi0_.plate_no_bind_state as plate_n33_145_, parkorderi0_.pos_input_money as pos_inp34_145_, parkorderi0_.pos_user_id as pos_use35_145_, parkorderi0_.postage as postage36_145_, parkorderi0_.reduction_amount as reducti37_145_, parkorderi0_.refund_status as refund_38_145_, parkorderi0_.reser_order_type as reser_o39_145_, parkorderi0_.settle_date as settle_40_145_, parkorderi0_.settle_old_date as settle_41_145_, parkorderi0_.settle_status as settle_42_145_, parkorderi0_.start_date as start_d43_145_, parkorderi0_.status as status44_145_, parkorderi0_.type as type45_145_ from order_park_order_item parkorderi0_ where (parkorderi0_.status in (1 , 2)) and parkorderi0_.deleted=0;
# Time: 2020-03-14T23:21:40.413197Z
# User@Host: api_deploy[api_deploy] @ localhost [127.0.0.1] Id: 2597
# Query_time: 10.313175 Lock_time: 0.000213 Rows_sent: 0 Rows_examined: 2657913
SET timestamp=1584228100;
select parkorderi0_.id as id1_145_, parkorderi0_.created_by as created_2_145_, parkorderi0_.created_dt as created_3_145_, parkorderi0_.deleted as deleted4_145_, parkorderi0_.updated_by as updated_5_145_, parkorderi0_.updated_dt as updated_6_145_, parkorderi0_.advance_charge as advance_7_145_, parkorderi0_.back_pay as back_pay8_145_, parkorderi0_.busi_type as busi_typ9_145_, parkorderi0_.callback_time as callbac10_145_, parkorderi0_.channel_order as channel11_145_, parkorderi0_.collect_fees_setting_name as collect12_145_, parkorderi0_.coupon_pay as coupon_13_145_, parkorderi0_.data_flag as data_fl14_145_, parkorderi0_.data_version as data_ve15_145_, parkorderi0_.dispute_back_pay as dispute16_145_, parkorderi0_.end_date as end_dat17_145_, parkorderi0_.expenses_json as expense18_145_, parkorderi0_.how_long as how_lon19_145_, parkorderi0_.invoice_id as invoice20_145_, parkorderi0_.make_up_payno as make_up21_145_, parkorderi0_.money_pay as money_p22_145_, parkorderi0_.no as no23_145_, parkorderi0_.offline_back_pay_flag as offline24_145_, parkorderi0_.offline_back_pay_time as offline25_145_, parkorderi0_.paper_invoice_id as paper_i26_145_, parkorderi0_.order_park_order_no as order_p46_145_, parkorderi0_.pay_channel as pay_cha27_145_, parkorderi0_.pay_terminal_type as pay_ter28_145_, parkorderi0_.pay_time as pay_tim29_145_, parkorderi0_.pay_type as pay_typ30_145_, parkorderi0_.percent_json as percent31_145_, parkorderi0_.phone as phone32_145_, parkorderi0_.plate_no_bind_state as plate_n33_145_, parkorderi0_.pos_input_money as pos_inp34_145_, parkorderi0_.pos_user_id as pos_use35_145_, parkorderi0_.postage as postage36_145_, parkorderi0_.reduction_amount as reducti37_145_, parkorderi0_.refund_status as refund_38_145_, parkorderi0_.reser_order_type as reser_o39_145_, parkorderi0_.settle_date as settle_40_145_, parkorderi0_.settle_old_date as settle_41_145_, parkorderi0_.settle_status as settle_42_145_, parkorderi0_.start_date as start_d43_145_, parkorderi0_.status as status44_145_, parkorderi0_.type as type45_145_ from order_park_order_item parkorderi0_ where (parkorderi0_.status in (1 , 2)) and parkorderi0_.deleted=0;
5、开启未使用索引SQL过滤配置
mysql> show variables like 'log_queries_not_using_indexes%';
+-----------------+---------------------+
| Variable_name | -- Value |
+-----------------+----------------------+
| log_queries_not_using_indexes| ON |
+-----------------+-----------------------+
#(1)修改全局变量[临时生效]：
set global log_queries_not_using_indexes =1;
#(2)在配置文件中添加这一行[永久生效 - 重启mysql]：
[root@node ~]# vim /etc/my.cnf
[mysqld]
log_queries_not_using_indexes =1