MYSQL5.1.32を別のディレクトリーとPortでインストールしてみました。

「–log_slow_queriesはMySQL7.0で無くなるとの事。
–slow_query_log/–slow_query_log_fileを代わりに利用して下さいとのメッセージが表示されました。」

============================================================================
1) MYSQLのダウンロードと展開後にシステムデータベースの作成
============================================================================

[root@DB001 mysql-5.1.32-linux-i686-icc-glibc23]# scripts/mysql_install_db
Installing MySQL system tables...
090318 10:06:58 [Warning] The syntax '--log_slow_queries' is deprecated and will be removed in MySQL 7.0. Please use '--slow_query_log'/'--slow_query_log_file' instead.
090318 10:06:58 [Warning] Forcing shutdown of 2 plugins
OK
Filling help tables...
090318 10:06:58 [Warning] The syntax '--log_slow_queries' is deprecated and will be removed in MySQL 7.0. Please use '--slow_query_log'/'--slow_query_log_file' instead.
090318 10:06:59 [Warning] Forcing shutdown of 2 plugins
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h DB001 password 'new-password'

Alternatively you can run:
./bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd . ; ./bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd ./mysql-test ; perl mysql-test-run.pl

Please report any problems with the ./bin/mysqlbug script!

The latest information about MySQL is available at http://www.mysql.com/
Support MySQL by buying support/licenses from http://shop.mysql.com/

[root@DB001 mysql-5.1.32-linux-i686-icc-glibc23]#

============================================================================
2) オプションファイルの設定変更
============================================================================
(例) clientやmysqldの項目

port = 3307
socket = /tmp/mysql-5.1.32.sock
basedir=/usr/local/mysql-5.1.32-linux-i686-icc-glibc23
datadir=/usr/local/mysql-5.1.32-linux-i686-icc-glibc23/data
log_slow_queries=/usr/local/mysql-5.1.32-linux-i686-icc-glibc23/data/slow_query.log

============================================================================
3) オプションファイルを指定して起動と確認
============================================================================

./bin/mysqld_safe --defaults-file=/etc/my5.1.32.cnf &

[root@DB001 mysql-5.1.32-linux-i686-icc-glibc23]# netstat -an | grep 3307
tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN
[root@DB001 mysql-5.1.32-linux-i686-icc-glibc23]# cd bin/

============================================================================
4) mysqlクライアントにてportとsocketを指定して接続
============================================================================

[root@DB001 bin]#<code> ./mysql -u root -p -P 3307 --socket=/tmp/mysql-5.1.32.sock</code>
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.32-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>SET PASSWORD FOR root@localhost = PASSWORD('パスワード');

mysql>

============================================================================
5) rootのパスワードを設定後に再度接続確認しデータベースの確認
============================================================================

[root@DB001 bin]#<code> ./mysql -u root -p -P 3307 --socket=/tmp/mysql-5.1.32.sock</code>
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.32-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.01 sec)

mysql>

============================================================================
6) 不要なアカウントの削除と確認
============================================================================

mysql> select user,host from user;
+------+--------------------+
| user | host               |
+------+--------------------+
| root | 127.0.0.1          |
|      | localhost          |
| root | localhost          |
|      | DB001              |
| root | DB001              |
+------+--------------------+
5 rows in set (0.00 sec)

mysql> delete from user where user ='';
Query OK, 2 rows affected (0.00 sec)

mysql> select user,host from user;
+------+--------------------+
| user | host               |
+------+--------------------+
| root | 127.0.0.1          |
| root | localhost          |
| root | DB001              |
+------+--------------------+
3 rows in set (0.00 sec)

mysql> SET PASSWORD FOR root@127.0.0.1 = PASSWORD('パスワード');
Query OK, 0 rows affected (0.00 sec)

mysql> SET PASSWORD FOR root@'DB001' = PASSWORD('パスワード');
Query OK, 0 rows affected (0.00 sec)

mysql>

============================================================================
7) 基本ステップ終了
============================================================================


INNODBのログファイルを大きくするとパフォーマンスが向上する場合がある、
ためしにサイズを変更してみました。

■変更前
logfile_innodb_before

■my.cnfを変更して、古いログを削除して再起動
logfile_innodb

■変更後にサイズ確認
logfile_innodb_after

※ オラクルのようなログスイッチはどのようにやるのか…….

InnoDBログファイル

13.5.3. InnoDB 設定

——————————————————————————————
おまけ: テーブルロック確認
——————————————————————————————

mysql> show open tables;
+----------+----------+--------+-------------+
| Database | Table    | In_use | Name_locked |
+----------+----------+--------+-------------+
| mysql    | user     |      0 |           0 |
| mysql    | db       |      0 |           0 |
| mysql    | event    |      0 |           0 |
| DB01     | TABLE000 |      0 |           0 |
+----------+----------+--------+-------------+
4 rows in set (0.00 sec)

mysql>

スロー クエリ ログの内容は、long_query_time 秒より実行に時間がかかる
SQL ステートメントすべてが入ります。最初のテーブル ロックを取得するまでの時間は、
実行時間としてはカウントしていません。すべてのステートメントを実行し、
すべてのロックをリリースした後に、mysqld で、ステートメントをスロー クエリ ログ
として書き込むため、ログ順は実行順とは異なることがあります。最低限値は 1 で、
long_query_time のデフォルト値 (最大値) は 10です。

MySQL 5.1.6 以降、スロー クエリ ログを有効化するには、
mysqld を –log-slow-queries[=file_name]
オプションで起動します。必要に応じて、–log-output オプションを使用して、
ログの出力先を指定します。
MySQL 5.1.6 より前は、スロー クエリ ログの出力先はファイルです。
スロー クエリ ログ ファイルを有効化するには、–log-slow-queries[=file_name]
オプションを使用します。

スロー クエリ ログには、実行に時間がかかるクエリが入るため、最適化の対象になります。
しかし、時間がかかるスロー クエリ ログの検査は手間がかかります。
ここで、mysqldumpslowコマンドを使用してスロー クエリ ログを処理することで、
そのクエリをログに概括表示します。mysqldumpslow –help を使用して、
このコマンドに関するサポートを探してください。

MySQL 5.1 では、インデックスを使用しないクエリは、–log-queries-not-using-indexes
オプションで指定すると、スロー クエリ ログで記録するようになります。
MySQL 5.1 では、スロー クエリ ログに対して、–log-slow-admin-statements というサーバ
オプションで、OPTIMIZE TABLE、ANALYZE TABLE、ALTER TABLE など、時間がかかる管理ステートメントのロギング要求を有効化します。

クエリ キャッシュで扱うクエリは、スロー クエリ ログには付加しません。
テーブルのレコードがない、または 1 つだけであるときは、インデックスで管理する必要がないため、
これもスロー クエリ ログには入りません。

SlowログをCATコマンドで確認した場合

[root@colinux data]# cat mysql-slow.log
/usr/local/mysql/bin/mysqld, Version: 5.1.30-log (MySQL Community Server (GPL)).
 started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
[root@colinux data]# 

mysqldumpslowでSlowログを確認した場合

[root@colinux data]# mysqldumpslow  mysql-slow.log

Reading mysql slow query log from mysql-slow.log
Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts

[root@colinux data]#

MySQL Server 5.0 管理監視ガイド

4.11.5. スロー クエリ ログ

mysqldumpslow

[root@colinux data]# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
<code>
Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (t, at, l, al, r, ar etc), 'at' is default
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time
</code>
[root@colinux data]# 

mysqldumpslowは、スロークエリーログ内における、条件値が異なるだけの
SQL文をまとめて、各SQL文をまとめて、各SQL文の平均実行時間の長い順番に
並び替えて表示してくれる。

mysql_slow

その他以下のように一定時間より長いQueryのみをフィルター抽出出来る。
mysql_slow_log_filter.pl

===========================================================
"tail -f mysql-slow.log | mysql_slow_log_filter -T 0.5 -R 1000"
===========================================================
Filtering Slow Query Log – Especially after the changes to log all
queries slow query log may be growing too rapidly to follow,
so we implemented slow query log filter (based on parse_mysql_slow_log by Nathanial Hendler)
which allows you to filter out only queries which took more than certain amount
of time or examined more than certain amount of rows. This is great as allows
multiple passes across same slow query log first to fix worse queries and then
come to find more optimization candidates.
So “tail -f mysql-slow.log | mysql_slow_log_filter -T 0.5 -R 1000” will look at queries
as they come and will print out queries taking more than 0.5 seconds to execute or
having more than 1000 rows examined.
===========================================================


[root@HOME001 data]# ./slolog.pl -T 0.05 < home001-mysql-slow.log Starting... # Query_time: 0.461031 Lock_time: 0.000137 Rows_sent: 0 Rows_examined: 39 SET timestamp=1359610099; update test.TABLE001 set comment = 'test percona-toolkit-2.1.8'; # Time: 130131 14:28:25 # User@Host: root[root] @ localhost [] # Query_time: 0.433720 Lock_time: 0.000131 Rows_sent: 0 Rows_examined: 1 SET timestamp=1359610148; delete from test.TABLE001 where id = 39; # Time: 130131 14:29:12 # User@Host: root[root] @ localhost [] [root@HOME001 data]# [/SQL]


MySQLTunerでMYSQLの設定を見直す。

[root@colinux tools]#wget mysqltuner.pl

HTTP request sent, awaiting response... 302 Found
Location: http://mysqltuner.pl/mysqltuner.pl [following]
--11:52:57-- http://mysqltuner.pl/mysqltuner.pl
=> `mysqltuner.pl'
Reusing existing connection to mysqltuner.pl:80.
HTTP request sent, awaiting response... 200 OK
Length: 38,688 (38K) [text/plain]

100%[====================================>] 38,688 71.29K/s

11:52:58 (71.28 KB/s) - `mysqltuner.pl' saved [38688/38688]

[root@colinux tools]# ls -l
total 40
-rw-r--r-- 1 root root 38688 2008-12-01 18:37 mysqltuner.pl
[root@colinux tools]# chmod 0700 mysqltuner.pl
[root@colinux tools]# ls -l
total 40
-rwx------ 1 root root 38688 2008-12-01 18:37 mysqltuner.pl
[root@colinux tools]# ./mysqltuner.pl

>> MySQLTuner 1.0.0 - Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login:

[hup]

[!!] Key buffer size / total MyISAM indexes: 16.0K/155.0K
[!!] Query cache is disabled
[OK] Temporary tables created on disk: 0% (0 on disk / 18 total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 18% (4 open / 22 opened)
[OK] Open file limit used: 0% (3/1K)
[OK] Table locks acquired immediately: 100% (21 immediate / 21 locks)
[!!] Connections aborted: 37%
[OK] InnoDB data size / buffer pool: 144.0K/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Your applications are not closing MySQL connections properly
Variables to adjust:
key_buffer_size (> 155.0K)
query_cache_size (>= 8M)
thread_cache_size (start at 4)
table_cache (> 4)

mysqltunner

■■■ サーバー① ■■■

[root@localhost tools]# ./mysqltuner.pl
<code>
&gt;&gt; MySQLTuner 1.0.0 - Major Hayden
&gt;&gt; Bug reports, feature requests, and downloads at http://mysqltuner.com/
&gt;&gt; Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
</code>
[root@localhost tools]#

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.25-rc-log
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 24M (Tables:98)
[--] Data in InnoDB tables: 384K (Tables:8) 
[!!] Total fragmented tables: 28

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2h 48m 57s (6K q [0.646 qps], 323 conn, TX: 38M, RX: 739K)
[--] Reads / Writes: 93% / 7%
[--] Total buffers: 42.0M global + 1.6M per thread (151 max threads)
[OK] Maximum possible memory usage: 277.9M (27% of installed RAM)
[!!] Slow queries: 8% (526/6K)
[OK] Highest usage of available connections: 17% (27/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/1.3M
[OK] Key buffer hit rate: 99.6% (22K cached / 100 reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 369 sorts)
[!!] Temporary tables created on disk: 43% (497 on disk / 1K total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 21% (64 open / 299 opened)
[OK] Open file limit used: 9% (99/1K)
[OK] Table locks acquired immediately: 100% (6K immediate / 6K locks)
[OK] InnoDB data size / buffer pool: 384.0K/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Set thread_cache_size to 4 as a starting value
Variables to adjust:
query_cache_size (&gt;= 8M)
tmp_table_size (&gt; 16M)
max_heap_table_size (&gt; 16M)
thread_cache_size (start at 4)

■■■ サーバー② ■■■

[root@localhost ~]$ ./mysqltuner.pl
<code>
&gt;&gt; MySQLTuner 1.0.0 - Major Hayden
&gt;&gt; Bug reports, feature requests, and downloads at http://mysqltuner.com/
&gt;&gt; Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:</code>
[root@localhost ~]$

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.51a
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 403M (Tables: 145)
[--] Data in InnoDB tables: 47M (Tables: 14)
[!!] Total fragmented tables: 13

-------- Performance Metrics -------------------------------------------------
[--] Up for: 101d 8h 30m 57s (4M q [0.466 qps], 695K conn, TX: 3B, RX: 256M)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 90.0M global + 18.3M per thread (100 max threads)
[OK] Maximum possible memory usage: 1.9G (47% of installed RAM)
[OK] Slow queries: 0% (5/4M)
[OK] Highest usage of available connections: 54% (54/100)
[OK] Key buffer size / total MyISAM indexes: 64.0M/276.2M
[OK] Key buffer hit rate: 96.6% (261M cached / 8M reads)
[!!] Query cache is disabled
[!!] Sorts requiring temporary tables: 24% (45K temp sorts / 189K sorts)
[OK] Temporary tables created on disk: 2% (27K on disk / 1M total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (4 open / 675K opened)
[OK] Open file limit used: 0% (8/1K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[!!] InnoDB data size / buffer pool: 47.2M/8.0M

——– Recommendations —————————————————–
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (>= 8M)
sort_buffer_size (> 63K)
read_rnd_buffer_size (> 15M)
thread_cache_size (start at 4)
table_cache (> 4)
innodb_buffer_pool_size (>= 47M)


動的にSLOWログを有効/無効出来るか確認

MySQL 5.1.12 から、ランタイムで一般クエリ ログとスロー クエリ ログを
無効化できるようになりました。 」

4.11.6. ログ ファイルの保守に書かれてました。


[root@colinux ~]# mysqladmin version -u root -p
Enter password:
mysqladmin  Ver 8.42 Distrib 5.1.30, for pc-linux-gnu on i686
Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version          5.1.30-log
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /tmp/mysql.sock
Uptime:                 52 sec

Threads: 1  Questions: 1  Slow queries: 0  Opens: 17  Flush tables: 1  Open tabl
es: 4  Queries per second avg: 0.19
[root@colinux ~]#

mysql> show variables like 'log%';
+---------------------------------+-----------------------------------+
| Variable_name                   | Value                             |
+---------------------------------+-----------------------------------+
| log                             | OFF                               |
| log_bin                         | ON                                |
| log_bin_trust_function_creators | OFF                               |
| log_bin_trust_routine_creators  | OFF                               |
| log_error                       | /usr/local/mysql/data/colinux.err |
| log_output                      | FILE                              |
| log_queries_not_using_indexes   | OFF                               |
| log_slave_updates               | OFF                               |
| log_slow_queries                | OFF                               |
| log_warnings                    | 1                                 |
+---------------------------------+-----------------------------------+
10 rows in set (0.03 sec)


mysql> show variables like 'slow%';
+---------------------+----------------------------------------+
| Variable_name       | Value                                  |
+---------------------+----------------------------------------+
| slow_launch_time    | 2                                      |
| slow_query_log      | OFF                                    |
| slow_query_log_file | /usr/local/mysql/data/colinux-slow.log |
+---------------------+----------------------------------------+
3 rows in set (0.00 sec)

mysql>

slow

mysql>SET GLOBAL slow_query_log = 'ON';
Query OK, 0 rows affected (0.01 sec)

mysql>show variables like 'slow%';
+---------------------+----------------------------------------+
| Variable_name       | Value                                  |
+---------------------+----------------------------------------+
| slow_launch_time    | 2                                      |
| slow_query_log      | ON                                     |
| slow_query_log_file | /usr/local/mysql/data/colinux-slow.log |
+---------------------+----------------------------------------+
3 rows in set (0.00 sec)

mysql>

mysql> show variables like 'log%';
+---------------------------------+-----------------------------------+
| Variable_name                   | Value                             |
+---------------------------------+-----------------------------------+
| log                             | OFF                               |
| log_bin                         | ON                                |
| log_bin_trust_function_creators | OFF                               |
| log_bin_trust_routine_creators  | OFF                               |
| log_error                       | /usr/local/mysql/data/colinux.err |
| log_output                      | FILE                              |
| log_queries_not_using_indexes   | OFF                               |
| log_slave_updates               | OFF                               |
| log_slow_queries                | ON                                |
| log_warnings                    | 1                                 |
+---------------------------------+-----------------------------------+
10 rows in set (0.02 sec)

mysql>

mysql>SET GLOBAL slow_query_log = 'OFF';
Query OK, 0 rows affected (0.00 sec)

mysql>show variables like 'slow%';
+---------------------+----------------------------------------+
| Variable_name       | Value                                  |
+---------------------+----------------------------------------+
| slow_launch_time    | 2                                      |
| slow_query_log      | OFF                                    |
| slow_query_log_file | /usr/local/mysql/data/colinux-slow.log |
+---------------------+----------------------------------------+
3 rows in set (0.00 sec)

mysql>

slow_query_log

===========================================================

MySQL 5.1.12 から、ランタイムで一般クエリ ログとスロー クエリ ログを無効化できるようになりました。

SET GLOBAL general_log = 'OFF';
SET GLOBAL slow_query_log = 'OFF';

ログを無効化した状態で、コマンドラインなどを使用して、ログ ファイルの名前を外部的に変更します。そして、ログを再び有効化します。

SET GLOBAL general_log = 'ON';
SET GLOBAL slow_query_log = 'ON';

このやり方は、どのプラットフォームでも使用でき、サーバの再起動は不要です。

===========================================================


Oracleのディフォルトユーザーなどの利用していないアカウントを無効にして権限の確認。

■ CREATE SESSION出来ないように設定

SQL> REVOKE CONNECT FROM DBSNMP;

取消しが成功しました。

SQL> SELECT USERNAME, PROFILE, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME = ‘DBSNMP’;

USERNAME PROFILE ACCOUNT_STATUS
—————————— —————————— ——————————–
DBSNMP DEFAULT OPEN

■ アカウントの無効化

SQL> ALTER USER DBSNMP ACCOUNT LOCK PASSWORD EXPIRE;

ユーザーが変更されました。

■ アカウントがOPENになって無い事を確認

SQL> SELECT USERNAME, PROFILE, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME = ‘DBSNMP’;

USERNAME PROFILE ACCOUNT_STATUS
—————————— —————————— ——————————–
DBSNMP DEFAULT EXPIRED & LOCKED

■ 権限の確認

SQL> SELECT LPAD(‘ ‘, 2*level) || granted_role “USER PRIVS”
2 FROM (
3 SELECT NULL grantee, username granted_role
4 FROM dba_users
5 WHERE username LIKE UPPER(‘DBSNMP’)
6 UNION
7 SELECT grantee, granted_role
8 FROM dba_role_privs
9 UNION
10 SELECT grantee, privilege
11 FROM dba_sys_privs)
12 START WITH grantee IS NULL
13 CONNECT BY grantee = prior granted_role;

USER PRIVS
—————————————————————————————————-
DBSNMP
SELECT ANY DICTIONARY

SQL>


MYSQLのバージョン確認とMYSQLの稼働時間の確認

■ Windowsでの確認

C:\Documents and Settings\administrator>mysqladmin version -u root -p
Enter password: **********
mysqladmin Ver 8.41 Distrib 5.0.22, for Win32 on ia32
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version 5.0.22-community-nt
Protocol version 10
Connection localhost via TCP/IP
TCP port 3306
Uptime: 21 min 28 sec
Threads: 13 Questions: 84580 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 79 Queries per second avg: 65.668
C:\Documents and Settings\administrator>

■ Linuxでの確認

[root@localhost ~]$ mysqladmin version -u root -p
Enter password:
mysqladmin Ver 8.42 Distrib 5.1.25-rc, for redhat-linux-gnu on i686
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version 5.1.25-rc-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 24 days 1 hour 9 min 5 sec

Threads: 3 Questions: 233646 Slow queries: 27059 Opens: 737 Flush tables: 1 Open tables: 64 Queries per second avg: 0.11 2
[root@localhost ~]$


テーブル内にある列情報を表示

■方法①

DESC DB01.TABLE007;

■方法②

SHOW COLUMNS
FROM TABLE007
FROM DB01;

■方法③

SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE, IS_NULLABLE,COLUMN_DEFAULT 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TABLE007' AND TABLE_SCHEMA = 'DB01';

※ WHERE句をはずしてORDER BYを付ければリストをまとめて作成出来ます。

information_schema_tables_0

information_schema_tables


動作しているスレッドに関する情報を確認

mysql> show processlist;
+----+----------+--------------------------+--------------------+---------+------+-------+------------------+
| Id | User     | Host                     | db                 | Command | Time | State | Info             |
+----+----------+--------------------------+--------------------+---------+------+-------+------------------+
|  1 | root     | localhost                | information_schema | Query   |    0 | NULL  | show processlist |
|  2 | variable | anywhere.mshome.net:1776 | DB01               | Sleep   |   13 |       | NULL             |
|  3 | variable | anywhere.mshome.net:1777 | DB01               | Sleep   |    3 |       | NULL             |
|  4 | variable | anywhere.mshome.net:1778 | DB01               | Sleep   |    8 |       | NULL             |
+----+----------+--------------------------+--------------------+---------+------+-------+------------------+
4 rows in set (0.00 sec)
mysql>

mysql> show full processlist;
+----+----------+--------------------------+--------------------+---------+------+-------+-----------------------+
| Id | User     | Host                     | db                 | Command | Time | State | Info                  |
+----+----------+--------------------------+--------------------+---------+------+-------+-----------------------+
|  1 | root     | localhost                | information_schema | Query   |    0 | NULL  | show full processlist |
|  2 | variable | anywhere.mshome.net:1776 | DB01               | Sleep   |   89 |       | NULL                  |
|  3 | variable | anywhere.mshome.net:1777 | DB01               | Sleep   |    4 |       | NULL                  |
|  4 | variable | anywhere.mshome.net:1778 | DB01               | Sleep   |   84 |       | NULL                  |
+----+----------+--------------------------+--------------------+---------+------+-------+-----------------------+
4 rows in set (0.00 sec)
mysql>


mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
+----+----------+--------------------------+--------------------+---------+------+-----------+----------------------------------------------+
| ID | USER     | HOST                     | DB                 | COMMAND | TIME | STATE     | INFO                                         |
+----+----------+--------------------------+--------------------+---------+------+-----------+----------------------------------------------+
|  4 | variable | anywhere.mshome.net:1778 | DB01               | Sleep   |   79 |           | NULL                                         |
|  3 | variable | anywhere.mshome.net:1777 | DB01               | Sleep   |    4 |           | NULL                                         |
|  2 | variable | anywhere.mshome.net:1776 | DB01               | Sleep   |  219 |           | NULL                                         |
|  1 | root     | localhost                | information_schema | Query   |    0 | executing | SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST |
+----+----------+--------------------------+--------------------+---------+------+-----------+----------------------------------------------+
4 rows in set (0.00 sec)
mysql>

information_schema_processlist

「PROCESS」権限が無いと、SHOW PROCESSLISTコマンドを実行しても
自分のPROCESSのみしか確認出来ません。
process