MySQLog is command line program used to collect mysql slow query log like Porcona Toolkit's pt-query-digest.
-e, --env TEXT The environment of mysql log to collect
[default: default]
-u, --user TEXT The user of database for collecting
[required]
-p, --password TEXT The password of database for collecting
[required]
-h, --host TEXT The host of database for collecting mysql
log [default: localhost]
-P, --port INTEGER The port of database for collecting mysql
log [default: 3306]
-d, --database TEXT The database for collecting mysql log
[default: test]
-t, --thread-size INTEGER The thread size of SlowQueryLogConsumer
[default: 1]
-s, --since TEXT Filter mysql log by datatime yyyy-MM-dd
HH:mm:ss
-T, --query-time FLOAT Filter mysql log by query_time [default:
0.3]
--enable-fingerprint Enable query sql fingerprint
--install-completion [bash|zsh|fish|powershell|pwsh]
Install completion for the specified shell.
--show-completion [bash|zsh|fish|powershell|pwsh]
Show completion for the specified shell, to
copy it or customize the installation.
--help Show this message and exit.# create python virtual environment for mysqlog
python3 -m venv mysqlog.venv
# active virtual environment mysqlog.venv
source mysqlog.venv/bin/activate
# use pip command install mysqlog
pip install mysqlog
# check mysqlog is installed
mysqlog --help- Example log file (mysql-slow.log)
# Time: 2020-09-25T06:05:23.747686Z
# User@Host: root[root] @ [10.190.66.171] Id: 7
# Query_time: 4.412855 Lock_time: 0.000136 Rows_sent: 3 Rows_examined: 8720446
SET timestamp=1601013923;
SELECT id FROM `xxl_job_log`
WHERE !(
(trigger_code in (0, 200) and handle_code = 0)
OR
(handle_code = 200)
)
AND `alarm_status` = 0
ORDER BY id ASC;
- Init Database
# connect to mysql
mysql -uroot -p123456
# create database and use it
mysql> create database test;
mysql> use database test;
# create mysql_slow_query_log table
mysql> CREATE TABLE `mysql_slow_query_log` (
`id` int NOT NULL auto_increment,
`md5` VARCHAR(64) NOT NULL comment 'md5',
`env` VARCHAR(64) NOT NULL DEFAULT 'default' comment '环境',
`datetime` DATETIME NOT NULL comment '执行时间',
`database` VARCHAR(50) NULL comment '数据库',
`user` VARCHAR(50) NULL comment '数据库用户',
`host` VARCHAR(100) NULL comment '数据库主机',
`fingerprint` TEXT NULL comment 'SQL指纹',
`query` TEXT NOT NULL comment '执行语句',
`query_time` FLOAT DEFAULT NULL comment '执行时间',
`lock_time` FLOAT DEFAULT NULL comment '锁定时间',
`rows_examined` INT DEFAULT NULL comment '扫描总行数',
`rows_sent` INT DEFAULT NULL comment '返回总行数',
`created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY (`md5`)
);
# make sure mysql_slow_query_log table is created
mysql> show tables;
- Run MySQLog
mysqlog -uroot -p123456 -hlocalhost -P3306 -dtest -t2 -T0.5 -s"2021-01-01 00:00:00" mysql-slow.log-
how to enable mysql's slow query log
-
how to know mysql's slow query log file's location