使用mysqldumpslow.pl分析慢查询日志遇到的问题及解决方式

最新推荐文章于 2024-09-20 18:04:59 发布

hzq_ah

最新推荐文章于 2024-09-20 18:04:59 发布

阅读量1.8k

点赞数 3

分类专栏： mysql学习

本文链接：https://blog.csdn.net/hzq_java/article/details/86303507

版权

mysql学习专栏收录该内容

1 篇文章 0 订阅

订阅专栏

c:\Program Files\MySQL\MySQL Server 8.0\bin>perl mysqldumpslow.pl
Can't determine basedir from 'my_print_defaults mysqld' output:  at mysqldumpslow.pl line 61.

先分析下出现这个问题的原因：

c:\Program Files\MySQL\MySQL Server 8.0\bin>my_print_defaults mysqld

可以看到执行这个命令，并没有任何输出。

查看下这个命令：

c:\Program Files\MySQL\MySQL Server 8.0\bin>my_print_defaults --help
my_print_defaults  Ver 8.0.13 for Win64 on x86_64 (MySQL Community Server - GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Prints all arguments that is give to some program using the default files
Usage: my_print_defaults [OPTIONS] groups
  -c, --config-file=name
                      Deprecated, please use --defaults-file instead. Name of
                      config file to read; if no extension is given, default
                      extension (e.g., .ini or .cnf) will be added
  -#, --debug[=#]     This is a non-debug version. Catch this and exit
  -c, --defaults-file=name
                      Like --config-file, except: if first option, then read
                      this file only, do not read global or per-user config
                      files; should be the first option
  -e, --defaults-extra-file=name
                      Read this file after the global config file and before
                      the config file in the users home directory; should be
                      the first option
  -g, --defaults-group-suffix=name
                      In addition to the given groups, read also groups with
                      this suffix
  -e, --extra-file=name
                      Deprecated. Synonym for --defaults-extra-file.
  -n, --no-defaults   Ignore reading of default option file(s), except for
                      login file.
  -l, --login-path=name
                      Path to be read from under the login file.
  -s, --show          Show passwords in plain text.
  -?, --help          Display this help message and exit.
  -v, --verbose       Increase the output level
  -V, --version       Output version information and exit.

Default options are read from the following files in the given order:
C:\Windows\my.ini C:\Windows\my.cnf C:\my.ini C:\my.cnf c:\Program Files\MySQL\MySQL Server 8.0\my.ini c:\Program Files\MySQL\MySQL Server 8.0\my.cnf

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
config-file                       my
defaults-file                     my
defaults-extra-file               (No default value)
defaults-group-suffix             (No default value)
extra-file                        (No default value)
login-path                        (No default value)
show                              FALSE

Example usage:
my_print_defaults --defaults-file=example.cnf client mysql

指定默认配置文件试下：

c:\Program Files\MySQL\MySQL Server 8.0\bin>my_print_defaults -e "c:\programdata\mysql\mysql server 8.0\my.ini" mysqld
--port=3306
--basedir=C:/Program Files/MySQL/MySQL Server 8.0/
--datadir=C:/ProgramData/MySQL/MySQL Server 8.0/Data
--default_authentication_plugin=caching_sha2_password
--default-storage-engine=INNODB
--sql-mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
--log-output=FILE
--general-log=0
--general_log_file=DESKTOP-VI7DM9L.log
--slow-query-log=1
--slow_query_log_file=DESKTOP-VI7DM9L-slow.log
--long_query_time=10
--log-bin=DESKTOP-VI7DM9L-bin
--log-error=DESKTOP-VI7DM9L.err
--server-id=1
--lower_case_table_names=1
--secure-file-priv=C:/ProgramData/MySQL/MySQL Server 8.0/Uploads
--max_connections=151
--table_open_cache=2000
--tmp_table_size=25M
--thread_cache_size=10
--myisam_max_sort_file_size=100G
--myisam_sort_buffer_size=40M
--key_buffer_size=8M
--read_buffer_size=64K
--read_rnd_buffer_size=256K
--innodb_flush_log_at_trx_commit=1
--innodb_log_buffer_size=1M
--innodb_buffer_pool_size=8M
--innodb_log_file_size=48M
--innodb_thread_concurrency=9
--innodb_autoextend_increment=64
--innodb_buffer_pool_instances=8
--innodb_concurrency_tickets=5000
--innodb_old_blocks_time=1000
--innodb_open_files=300
--innodb_stats_on_metadata=0
--innodb_file_per_table=1
--innodb_checksum_algorithm=0
--back_log=80
--flush_time=0
--join_buffer_size=256K
--max_allowed_packet=4M
--max_connect_errors=100
--open_files_limit=4161
--sort_buffer_size=256K
--table_definition_cache=1400
--binlog_row_event_max_size=8K
--sync_master_info=10000
--sync_relay_log=10000
--sync_relay_log_info=10000
--loose_mysqlx_port=33060

看到了吧，让我们把mysqldumpslow.pl改一下：

my $defaults   = `my_print_defaults  -e "c:\\programdata\\mysql\\mysql server 8.0\\my.ini" mysqld`;

修改以后，再次执行，我遇到了第二个问题：

c:\Program Files\MySQL\MySQL Server 8.0\bin>perl mysqldumpslow.pl

Reading mysql slow query log from C:/ProgramData/MySQL/MySQL Server
Can't open C:/ProgramData/MySQL/MySQL: No such file or directory at mysqldumpslow.pl line 96.
Can't open Server: No such file or directory at mysqldumpslow.pl line 96.

很明显的慢查询日志路径出了问题。因为不熟悉Perl语言，所以借助了eclipse调试这段代码，最终找到了问题。

代码做出如下修改：

   if ( -f $slowlog ) {
        @ARGV = ($slowlog);
        die "Can't find '$slowlog'\n" unless @ARGV;
    } else {
        @ARGV = <"$datadir"/$opt{h}-slow.log>;
        die "Can't find '$datadir/$opt{h}-slow.log'\n" unless @ARGV;
    }

用引号将$datadir引起来，这样问题就解决了。

出现这个问题的原因就在于windows命令行中带空格的路径要使用引号括起来。

再次执行就可以分析慢查询日志了。

c:\Program Files\MySQL\MySQL Server 8.0\bin>perl mysqldumpslow.pl

Reading mysql slow query log from C:/ProgramData/MySQL/MySQL Server
Can't open C:/ProgramData/MySQL/MySQL: No such file or directory at mysqldumpslow.pl line 96.
Can't open Server: No such file or directory at mysqldumpslow.pl line 96.

c:\Program Files\MySQL\MySQL Server 8.0\bin>perl mysqldumpslow.pl

Reading mysql slow query log from C:/ProgramData/MySQL/MySQL Server 8.0/Data/DESKTOP-VI7DM9L-slow.log
Count: 1  Time=0.00s (0s)  Lock=0.03s (0s)  Rows=0.0 (0), root[root]@localhost
  show create table customers

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=122.0 (122), root[root]@localhost
  select * from customers

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=122.0 (122), root[root]@localhost
  select * from customers order by phone

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=122.0 (122), root[root]@localhost
  select * from customers order by phone;
  C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
  TCP Port: N, Named Pipe: (null)
  C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
  TCP Port: N, Named Pipe: (null)


Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
  TCP Port: N, Named Pipe: (null)
  C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
  TCP Port: N, Named Pipe: (null)
  C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
  TCP Port: N, Named Pipe: (null)
  C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
  TCP Port: N, Named Pipe: (null)
  C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
  TCP Port: N, Named Pipe: (null)
  C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
  TCP Port: N, Named Pipe: (null)
  C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
  TCP Port: N, Named Pipe: (null)
  C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
  TCP Port: N, Named Pipe: (null)
  C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
  TCP Port: N, Named Pipe: (null)
  C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
  TCP Port: N, Named Pipe: (null)
  C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
  TCP Port: N, Named Pipe: (null)
  C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
  TCP Port: N, Named Pipe: (null)
  C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
  TCP Port: N, Named Pipe: (null)
  C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
  TCP Port: N, Named Pipe: (null)
  c:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
  TCP Port: N, Named Pipe: (null)
  C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
  TCP Port: N, Named Pipe: (null)
  # Time: N-N-10T08:N:N.145812Z
  # User@Host: root[root] @ localhost [::N]  Id:     N
  # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
  use yiibaidb;
  SET timestamp=N;
  show tables;
  C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
  TCP Port: N, Named Pipe: (null)
  C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
  TCP Port: N, Named Pipe: (null)
  # Time: N-N-11T00:N:N.815072Z
  # User@Host: root[root] @ localhost [::N]  Id:     N
  # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
  SET timestamp=N;
  show databases