Mysql管理必备工具Maatkit详解之三（mk-audit）

2009年05月18日　作者： 大头刚　

仅提供到5427版，后继版本取消了该工具

mk-audit - 分析报告mysql状态和操作系统相关环境，给出推荐值。安装方法可以参考。

这个工具能把MYSQL的配置文件，SCHEMA的情况，操作系统的情况统一收集起来，形成一个统一的报表，并给出建议。

主要分成两部分：
第一部分是操作系统的情况，CPU,内存，硬盘等.

mk-audit -u sg -p'xxxx' 
Server Specs
OS: 
 
CPU: Intel(R) Xeon(TM) CPU 3.20GHz       Architecture: 64-bit
Speed: MHz: 3192 3192 3192 3192
Cache: 2048 KB
   Count: 2
   Cores: 4
 
Memory: used 5.58G   of 5.83G   total  (255.02M free)
   Buffers: 98.55M
   Cached:  1.41G
   Shared:  0
   Slots:
 
Storage:
   1 RAID controllers  detected:
 
LSI Logic MegaRAID
##################
 
*** MegaRAID present but unable to check its status
########## End of RAID controllers ##########
 
   LVM volume groups: 	
   df: 
	Filesystem    Type    Size  Used Avail Use% Mounted on
 
libc: 2.3.4
 
PROBLEMS _________________________________________________________________

第二部分是MYSQL实例的情况。

   MySQL Instance   1
   Version:  5.1.33-log                                    Architecture: 64-bit
   Uptime:   28+00:59:29
   ps vals:  user mysql    cpu% 0.1    rss 3.86G   vsz 4.27G   syslog: No
   Bin:     
   Data dir: 
   PID file: 
   Socket:   /tmp/mysql.sock
   Port:     3306
   Log locations:
      Error:
      Relay:
      Slow:   10s     OFF
   Config file location:
   SCHEMA ________________________________________________________________
      5            213       120.59M   291        9.58G       3.11G
 
      Key buffer size        : 1.00G
      InnoDB buffer pool size: 8.00M
 
      Top 5 largest databases:
         DATABASE             SIZE DATA
 
      Top 5 largest tables:
         DB.TBL              SIZE DATA  SIZE INDEX  #ROWS    ENGINE
 
         Remaining 208        4.57G (22.49M average)
      Engines:
         ENGINE      SIZE DATA   SIZE INDEX   #TABLES   #INDEXES
         MyISAM      7.00G       2.79G        130       195
         CSV         0           0            2         NA
         InnoDB      32.00k      0            1         1
         MEMORY      2.58G       333.92M      80        95
      Triggers, Routines, Events:
         DATABASE           TYPE      COUNT
 
 
   PROBLEMS ______________________________________________________________
	Out of sync system variables (online value differs from config value):
	VARIABLE              ONLINE VALUE              CONFIG VALUE            
	record_buffer         6291456                   8388608                 
	myisam_recover                                  DEFAULT                 
	binlog_format         MIXED                     mixed                   
	datadir                           .                       
 
	Things to Note:
		- max_connections has been modified from its default (100): 1024
		- innodb_flush_method is not set to O_DIRECT
		- Slow query logging is disabled (log_slow_queries = OFF)
 
   Aggregated PROCESSLIST ________________________________________________
      FIELD      VALUE                       COUNT   TOTAL TIME (s)
          host
                 localhost                   1       0
      state
                 null                        1       0
      command
                 query                       1       0

但是目前的版本只支持在本机操作，对远程的MYSQL库无法进行统计，不过会在后续的版本实现。

mk-audit -u sg -p'xxxx' --host localhost
Sorry, --host is not implemented yet at /usr/bin/mk-audit line 3558.

OK,还有其他的一些参数，可以看下帮助文件

mk-audit --help
mk-audit inspects, analyzes and reports on a MySQL server.  For more details,
please use the --help option, or try 'perldoc /usr/bin/mk-audit' for complete
documentation.
 
Usage: /usr/bin/mk-audit <options>
 
Options:
  --ask-pass          Prompt for a password when connecting to MySQL
  --charset       -A  Default character set
  --config            Read this comma-separated list of config files; if
                      specified, this must be the first option on the command
                      line
  --defaults-file -F  Only read mysql options from the given file
  --help              Show help and exit
  --host              Connect to host (not implemented yet)
  --password      -p  Password to use when connecting
  --port          -P  Port number to use for connection
  --set-vars          Set these MySQL variables (default wait_timeout=10000)
  --socket        -S  Socket file to use for connection
  --top               Show top N largest databases and tables (default 5)
  --user          -u  User for login if not current user
  --version           Show version and exit
 
Options and values after processing arguments:
  --ask-pass          FALSE
  --charset           (No value)
  --config            /etc/maatkit/maatkit.conf,/etc/maatkit/mk-audit.conf,/root/.maatkit.conf,/root/.mk-audit.conf
  --defaults-file     (No value)
  --help              TRUE
  --host              (No value)
  --password          (No value)
  --port              (No value)
  --set-vars          wait_timeout=10000
  --socket            (No value)
  --top               5
  --user              (No value)
  --version           FALSE