==============================================
まとめてデータベース内のテーブルストレージタイプをリスト
==============================================


SELECT table_name, table_type, engine
FROM information_schema.tables
WHERE table_schema = 'TEST'
ORDER BY table_name DESC;

my_info

==============================================
テーブルで利用されているインデックスの数リスト
==============================================


SELECT
t.table_schema,t.table_name
,COUNT( IF(c.column_key = 'PRI',1,NULL) ) AS PK
,COUNT( IF(c.column_key != 'PRI',1,NULL) ) AS POSSIBLE
FROM information_schema.tables t
LEFT JOIN information_schema.columns c
ON t.table_schema = c.table_schema
AND t.table_name = c.table_name
AND c.column_key != ''
WHERE t.table_type != 'VIEW'
AND t.table_schema = database()
GROUP BY t.table_name
ORDER BY table_schema, table_name;

te-buru_key

==============================================
DBオブジェクトリスト
==============================================


(SELECT
table_schema AS object_schema,
table_name AS object_name,
table_type AS object_type
FROM information_schema.tables
)
UNION ALL
(SELECT
routine_schema,
routine_name,
routine_type
FROM information_schema.routines )
UNION ALL
(SELECT
trigger_schema,
trigger_name,
'TRIGGER'
FROM information_schema.triggers )
ORDER BY object_schema, object_type, object_name;

obj


MYSQLでテーブルの状態確認では、SHOW STATUSを良く
利用するが詳細な統計情報を確認にするには以下のようなコマンドで確認する。


mysql> show table status like 'TABLE911'\G
*************************** 1. row ***************************
Name: TABLE911
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 12
Avg_row_length: 1365
Data_length: 16384
Max_data_length: 0
Index_length: 32768
Data_free: 0
Auto_increment: 22
Create_time: 2009-04-10 21:41:36
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)

mysql>

show_status


select * from TABLE911 PROCEDURE ANALYSE();

optimise


テーブルにインデックスを付ける事で、パフォーマンスは向上するが、
NULLが入らないようにする事でよりパフォーマンスが向上する。

①もしインデックスが無ければ、インデックス追加


mysql> desc TABLE911;
+---------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-----------------+------+-----+---------+----------------+
| id | int(4) unsigned | NO | PRI | NULL | auto_increment |
| comment | varchar(45) | YES | | NULL | |
+---------+-----------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE TABLE911 ADD INDEX (comment(10));
Query OK, 12 rows affected (0.04 sec)
Records: 12 Duplicates: 0 Warnings: 0

mysql> desc TABLE911;
+---------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-----------------+------+-----+---------+----------------+
| id | int(4) unsigned | NO | PRI | NULL | auto_increment |
| comment | varchar(45) | YES | MUL | NULL | |
+---------+-----------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql>

add_index

②パフォーマンス向上を図る為に、where句で指定されているcomment列にNULLが
入らないようにする。


mysql> desc TABLE911;
+---------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-----------------+------+-----+---------+----------------+
| id | int(4) unsigned | NO | PRI | NULL | auto_increment |
| comment | varchar(45) | YES | MUL | NULL | |
+---------+-----------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> alter table TABLE911
-> modify comment varchar(45) NOT NULL;
Query OK, 12 rows affected (0.03 sec)
Records: 12 Duplicates: 0 Warnings: 0

mysql> desc TABLE911;
+---------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-----------------+------+-----+---------+----------------+
| id | int(4) unsigned | NO | PRI | NULL | auto_increment |
| comment | varchar(45) | NO | MUL | NULL | |
+---------+-----------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql>

modify

===========================================
TABLE OPTIMIZATION
===========================================
■適切なINDEXを付ける (付けすぎは良くないです)
■COLUMNSは出来るだけ必要最低限の長さで
■短めのINDEXを作成することで、WHERE句などの比較が可能であれば短めのINDEXを作成する
■COLUMNは出来るだけNOT NULLを指定して作成する。(DEFAULT値でカバーもあり)
===========================================


MYSQLのパフォーマンスチューニングを考えていると、何処かオラクルやMS SQLのチューニング
をしていた時と違う違和感を憶える。おそらくまだMYSQLに慣れていないせいだと
思うので、早めに慣れたいと思う今日この頃。

列に付けるIndexの長さと、Sortオーダーで利用される列へのインデッスクの付与
による動作確認。

index_length

comment列の先頭の文字がC以上H以下の列を抽出。
OracleでもMS SQLでもこんな感じの検索をする時は、
Likeで検索してたのですがこんな方法もあるんだな….と初めて知りました。


mysql> select * from TABLE000 where comment
-> between 'C' and 'H'
-> order by comment;
+----+------------+
| id | comment |
+----+------------+
| 3 | CAAAAAAAAA |
| 4 | DAAAAAAAAA |
| 5 | EAAAAAAAAA |
| 6 | FAAAAAAAAA |
| 7 | GAAAAAAAAA |
+----+------------+
5 rows in set (0.00 sec)

mysql>

プランは以下のような感じでインデックスが無いのでファイルソートになってる。


mysql> explain
-> select * from TABLE000 where comment
-> between 'C' and 'H'
-> order by comment;
+----+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+
| 1 | SIMPLE | TABLE000 | ALL | NULL | NULL | NULL | NULL | 12 | Using where; Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)
mysql>

インデックスを追加して処理が早くなるようにして見ます。
ただここではデータが無いので速度は変わりませんが、
データが増えたときに処理速度は大きく変わります。
尚where句で指定している部分では最初の一文字しか比較
で利用していないので、indexの長さは実際の列の長さより
短くしております。利用用途が明確な場合は、よりパフォーマンスを
向上してくれるようです。


mysql> desc TABLE000;
+---------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| comment | varchar(45) | NO | | NULL | |
+---------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE TABLE000 ADD INDEX (comment(10));
Query OK, 12 rows affected (0.18 sec)
Records: 12 Duplicates: 0 Warnings: 0

index_add


mysql> select * from TABLE000 where comment
-> between 'C' and 'H'
-> order by comment;
+----+------------+
| id | comment |
+----+------------+
| 3 | CAAAAAAAAA |
| 4 | DAAAAAAAAA |
| 5 | EAAAAAAAAA |
| 6 | FAAAAAAAAA |
| 7 | GAAAAAAAAA |
+----+------------+
5 rows in set (0.00 sec)

mysql>

インデックス追加前とインデックス追加後のプラン比較。
データ量が少ないせいかMYSQLが統計情報で判断して
取り出すデータ量に違いが出てきていないので特にインデックス
を利用していない….. 明示的に指定すればインデックスを利用して
取り出すデータもやはり減る。データが増えればきっとパフォーマンスに
大きな差がでてくるはず。。

以下 インデックス追加前、追加後、インデックスを明示的に指定して
プランを確認した場合の実行プランと取り出すデータ量の違い比較。

plan


特定テーブルにあるインデックスをCacheしてパフォーマンスアップする方法

TABLE007、TABLE008にあるインデックスをTABLE_IDX_CACHEにロードしてみる。
サイズは2MBとしてみる。

テーブル構造

index_cache

1) TABLE_IDX_CACHEという名前のキーCACHEを作成

mysql> SET GLOBAL TABLE_IDX_CACHE.key_buffer_size = 2 * 1024 * 1024;
Query OK, 0 rows affected (0.01 sec)

2) 作成したCACHEにテーブルを割り当てる

mysql> CACHE INDEX animals,animals02 IN TABLE_IDX_CACHE;
+----------------+--------------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+--------------------+----------+----------+
| DB02.animals | assign_to_keycache | status | OK |
| DB02.animals02 | assign_to_keycache | status | OK |
+----------------+--------------------+----------+----------+
2 rows in set (0.00 sec)

mysql>

index_cache2

3) INDEXを作成したKEY CACHEにロードする。


mysql> LOAD INDEX INTO CACHE animals,animals02;
+----------------+--------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+--------------+----------+----------+
| DB02.animals | preload_keys | status | OK |
| DB02.animals02 | preload_keys | status | OK |
+----------------+--------------+----------+----------+
2 rows in set (0.00 sec)

index_cache3

—————— 上記の方法で特定テーブルのIndexをKey Cacheにロードは完了 ———–

サーバー起動時に常に上記のインデックスをKey Cacheにロードしておく為には
以下の方法でロードする事が出来る。
——————————————————————————————————

(1) init_cache.sqlの作成 (インデックスロードスクリプト)


SET GLOBAL TABLE_IDX_CACHE.key_buffer_size = 2 * 1024 * 1024;
CACHE INDEX animals,animals02 IN TABLE_IDX_CACHE;
LOAD INDEX INTO CACHE animals,animals02;

(2)mysqlを起動するときに次のようにスクリプトを指定して起動させる

[root@colinux ~]# mysqld --init-file=init_cache.sql

※my.cnfに記入してもOK。


——————————————————————————————
ログEXPIREオプション
——————————————————————————————
①再起動必要
/etc/my.cnf
expire_logs_days = 10

②再起動不要
SET GLOBAL expire_logs_days = 10;

②を設定してから①を書いておくのがいいと思います。

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_expire_logs_days

——————————————————————————————
その他ログのパージ
——————————————————————————————
PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 31 DAY);

So you could set up a weekly cron job like:

0 9 * * mon mysql -uroot -e “PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 31 DAY);”
——————————————————————————————


MySQL のリリースによっては、新たに権限を追加するとき、または新たな機能をサポートする
ときに、mysql データベースのシステム テーブルのストラクチャを変更できます。
新しいバージョンの MySQL にアップグレードするときは、システム テーブルも同様に更新し、
ストラクチャが最新であることを確かめる必要があります。これをしないと、この利点を活用
できません。まず、mysql データベースをバックアップしてから実行。

mysql_fix_privilege_tables

■スクリプト一部
fix


mysqlcheck --check-upgrade --all-databases --auto-repair
mysql_fix_privilege_tables

実行後:
[root@colinux bin]# cat ../data/mysql_upgrade_info
5.1.30
[root@colinux bin]#

ノート:MySQL 5.1.7 以降は mysql_upgrade を使用


mysql_upgrade


shell> mysql_upgrade [options]

mysqlupgrade


MYSQLインストール直後のアカウント初期設定

基本的ですが、MYSQLサービスはrootでは動かさない。
MYSQLサーバー用 専用アカウントの作成

[mysql@colinux local]$ /etc/init.d/mysql.server start
Starting MySQL… SUCCESS!
[mysql@colinux local]$ ps -ef | grep mysql
root 2649 2623 0 12:17 tty1 00:00:00 su - mysql
mysql 2658 2649 0 12:17 tty1 00:00:00 -bash
mysql 2693 1 0 12:18 tty1 00:00:00 /bin/sh ./bin/mysqld_safe
--datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/colinux.pid
mysql 2821 2693 9 12:18 tty1 00:00:02 /usr/local/mysql/bin/mysqld
--basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
--log-error=/usr/local/mysql/data/colinux.err
--pid-file=/usr/local/mysql/data/colinux.pid
--socket=/tmp/mysql.sock --port=3306
mysql 2836 2658 0 12:18 tty1 00:00:00 ps -ef
mysql 2837 2658 0 12:18 tty1 00:00:00 grep mysql

[mysql@colinux local]$

—————– my.cnf ———————
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
user = mysql
skip-locking
key_buffer = 16K

mysqlインストール初期状態でまずはパスワードを設定して不要なアカウントを削除

[mysql@colinux local]$ mysql -u root mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.30-log MySQL Community Server (GPL)

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

mysql> update user set password = PASSWORD('password2009')
-> where user ='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

mysql> delete from user where user = 'root' and host ='%';
Query OK, 0 rows affected (0.01 sec)

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

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

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql>

init_secure

パスワードの付いていないアカウントがないかダブルチェック

mysql> select user,host,password from user
-> where user = '' or password = '';
Empty set (0.00 sec)

mysql>

今後の為に、パスワードの付いてないアカウントが作成されないように制限する


mysql> select @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
| |
+-------------------+
1 row in set (0.00 sec)

mysql> set global sql_mode = 'NO_AUTO_CREATE_USER';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.sql_mode;
+---------------------+
| @@global.sql_mode |
+---------------------+
| NO_AUTO_CREATE_USER |
+---------------------+
1 row in set (0.00 sec)

mysql>

no_auto_create_user

[mysql@colinux local]$ mysql -u root mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.30-log MySQL Community Server (GPL)

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

mysql> select @@global.sql_mode;
+---------------------+
| @@global.sql_mode |
+---------------------+
| NO_AUTO_CREATE_USER |
+---------------------+
1 row in set (0.00 sec)

mysql> GRANT select,insert,update,delete on DB001.* to 'no_password_user';
ERROR 1133 (42000): Can't find any matching row in the user table
mysql>

上記の”NO_AUTO_CREATE_USER”モードはMYSQLサーバーを再起動したら消えます。

mysql> select @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
| |
+-------------------+
1 row in set (0.01 sec)

mysql>

そこでmy.cnfに”sql_mode”の設定を行い再起動しても設定が消えないようにします。

—————————————————————–
抜粋
—————————————————————–
[mysqld]
port = 3306
socket = /tmp/mysql.sock
user = mysql
skip-locking
key_buffer = 16K
max_allowed_packet = 1M
table_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 64K
default-character-set=utf8
sql_mode =”NO_AUTO_CREATE_USER”
—————————————————————–

上記の設定を行い、mysqlを再起動すればパスワードの無いユーザーは
作成出来なくなります。後はルールとして周知すればOK。

sql_mode


STEP① MYSQLを停止させる。

[root@colinux ~]# /etc/init.d/mysql.server stop
Shutting down MySQL.. SUCCESS!
[root@colinux ~]#

STEP② GRANTテーブルをSKIPして、外部ユーザーが接続して来ないようにネットワークもSKIPして起動

[root@colinux ~]# mysqld --skip-grant-tables --skip-networking &
[1] 2643
[root@colinux ~]# 090405 9:39:59 [Warning] option 'thread_stack': unsigned value  65536 adjusted to 131072
090405 9:40:00 InnoDB: Started; log sequence number 0 303628
090405 9:40:00 [Note] mysqld: ready for connections.
Version: '5.1.30-log' socket: '/tmp/mysql.sock' port: 0 MySQL Community Server (GPL)

[root@colinux ~]#

STEP③ mysqlコマンドで接続する。(GRANTテーブルは読み込んでいないのでパスワード不要)

[root@colinux ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.30-log MySQL Community Server (GPL)

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

mysql>

skip_grant_tables

STEP④ パスワードをリセット

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| DATABASE |
| DB01 |
| DB02 |
| TEST |
| client_test_db |
| mysql |
+--------------------+
7 rows in set (0.09 sec)

mysql> update mysql.user set password = password('Dontforget')
-> where user ='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

mysql>

mysql> exit
Bye
[root@colinux ~]#

STEP⑤ MYSQLをシャットダウンして通常起動させる。

[root@colinux ~]# mysqladmin shutdown
090405 9:51:48 [Note] mysqld: Normal shutdown

090405 9:51:48 InnoDB: Starting shutdown...
090405 9:51:49 InnoDB: Shutdown completed; log sequence number 0 303628
090405 9:51:49 [Note] mysqld: Shutdown complete

[1]+ Done mysqld --skip-grant-tables --skip-networking
[root@colinux ~]#

※ メモリー内のgrantテーブルをflushしていないので、パスワードは不要。

[root@colinux ~]# /etc/init.d/mysql.server start
Starting MySQL.. SUCCESS!

STEP⑥ DB接続の確認。

[root@colinux ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.30-log MySQL Community Server (GPL)

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

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| DATABASE |
| DB01 |
| DB02 |
| TEST |
| client_test_db |
| mysql |
+--------------------+
7 rows in set (0.00 sec)

mysql>

STEP⑦ もしkillコマンドでDBをシャットダウンした場合 (オプション:メンテ時間が許せば。。。。)

━ エラーログの確認
━ mysqlcheckでデータベースの確認

[root@colinux ~]# mysqlcheck -u root -p --all-databases

[hup]
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.servers OK
mysql.slow_log
Error : You can’t use locks with log tables.
status : OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
mysql.user_info OK
[root@colinux ~]#

mysqlcheck

================================
メモ (パスワード変更方法)
================================

1: パスワード関数

mysql> set password for 'hatena'@'%' = PASSWORD('password-change');
Query OK, 0 rows affected (0.00 sec)

2: GRANTコマンド

mysql> GRANT USAGE ON *.* TO 'hatena'@'%' IDENTIFIED BY 'Password-Change';
Query OK, 0 rows affected (0.00 sec)


MYSQLユーザーに接続時間、クエーリ数などアクセス制限を付けて作成する。


GRANT select,insert,update,delete on DB001.* to 'restrict_user'
IDENTIFIED BY 'password'
WITH
MAX_CONNECTIONS_PER_HOUR 10
MAX_QUERIES_PER_HOUR 1000
MAX_UPDATES_PER_HOUR 100;

restrict_user


mysql> show grants for 'restrict_user';
--------------------------------------------------------------------------------------+
| Grants for restrict_user@% |
+-------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'restrict_user'@'%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19'
WITH MAX_QUERIES_PER_HOUR 1000 MAX_UPDATES_PER_HOUR 100 MAX_CONNECTIONS_PER_HOUR 10 |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB001`.* TO 'restrict_user'@'%' |
+-------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>