INFORMATION_SCHEMAはほとんどのRDBMSで実装されて
いてDBの情報を格納しているテーブルなどの集合である。
Oracle、MS SQLでもかなり運用中にお世話になりました。
MYSQLも同様にINFORMATION_SCHEMAが実装されてますので、
運用に大活躍する事でしょう。

information_schema

mysql> select table_name from information_schema.tables where table_schema=’TEST
‘;
+————+
| table_name |
+————+
| MEM |
| MEM2 |
| MEM3 |
| T1 |
| T2 |
+————+
5 rows in set (0.00 sec)

mysql>

mysql> show tables from TEST;
+—————-+
| Tables_in_TEST |
+—————-+
| MEM |
| MEM2 |
| MEM3 |
| T1 |
| T2 |
+—————-+
5 rows in set (0.00 sec)

mysql>

Information_Schemaはテーブルの集まりなので、用意されている
Viewのみを利用するよりも効率的にDBを運用する事が出来る。
MS SQLでいうsysobjectsテーブル,sysindexesテーブルのような
テーブルがあるのでJOINなどの処理で運用に必要なデータを
結合して利用してみると運用効率UPです。

(例)
━━━━━━DB━━━━━━
SELECT
t.table_schema
,t.table_name
,COUNT( IF(c.column_key = ‘PRI’,1,NULL) ) AS primary_key
,COUNT( IF(c.column_key != ‘PRI’,1,NULL) ) AS other_key
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;

━━━━━━特定DB━━━━━━
SELECT
t.table_schema
,t.table_name
,COUNT( IF(c.column_key = ‘PRI’,1,NULL) ) AS primary_key
,COUNT( IF(c.column_key != ‘PRI’,1,NULL) ) AS other_key
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 = ‘TEST’
GROUP BY t.table_name
ORDER BY table_schema, table_name;

information_schema2

(例)
━━━━━━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;

━━━━━━特定DB━━━━━━
(SELECT
table_schema AS object_schema
,table_name AS object_name
,table_type AS object_type
FROM information_schema.tables
where table_schema = ‘TEST’
)
UNION ALL
(SELECT
routine_schema
,routine_name
,routine_type
FROM information_schema.routines
where routine_schema = ‘TEST’ )
UNION ALL
( SELECT
trigger_schema
,trigger_name
,’TRIGGER’
FROM information_schema.triggers
where trigger_schema = ‘TEST’
)
ORDER BY object_schema, object_type, object_name;

information_schema3


━━━━━━INFORMATION_SCHEMA DIAGRAM━━━━━━

5.0

5.1

————————————————————————————-
テーブル名 説明 他の参照方法
————————————————————————————-
CHARACTER_SETS 使用可能な文字コード SHOW CHARACTER SET
COLLATIONS 使用可能な照合順序 SHOW COLLATION
COLLATION_CHARACTER_SET_APPLICABILITY 使用可能な照合順序 SHOW COLLATIONの最初の2カラム
COLUMNS 各テーブルのカラム SHOW COLUMNS
COLUMN_PRIVILEGES カラムの権限情報 mysql.columns_priv
KEY_COLUMN_USAGE カラムの制約の情報
ROUTINES SP情報等 mysql.proc
SCHEMATA データベースの情報 SHOW DATABASES
SCHEMA_PRIVILEGES データベースの権限情報 mysql.db
STATISTICS インデックスの情報 SHOW INDEX
TABLES テーブルの情報 SHOW TABLES
TABLE_CONSTRAINTS テーブルの制約の情報
TABLE_PRIVILEGES テーブルの権限情報 mysql.tables_priv
TRIGGERS トリガの情報 SHOW TRIGGERS
USER_PRIVILEGES ユーザの権限情報 mysql.user
VIEWS ビューの情報 SHOW CREATE VIEW

information_schemaは、データベースやテーブルの名前やユーザ権限情報などのメタデータへ
のアクセスを提供する仕組みで,ほかのRDBMSでは、データディクショナリやシステムカタログと呼ばれているものになります。
information_schemaはMySQL独自のものではなく、SQLの標準規格であるSQL:2003(ISO/IEC 9075:2003)に定められているようです。

その他のDBで直接テーブルを参照する例)
——————————————————————
RDBMS コマンド
——————————————————————
MySQL SHOW TABLES;
PostgreSQL SELECT * FROM pg_tables;
Oracle SELECT * FROM tab;
MS SQL SELECT table_name FROM information_schema.tables

show-open-tables

※ソースファイルの中を調査するとHiddenテーブルがあるかもしれません。

mysql> SELECT
-> t.table_schema
-> ,t.table_name
-> ,COUNT( IF(c.COLUMN_KEY = ‘PRI’,1,NULL) ) AS primary_key
-> ,COUNT( IF(c.COLUMN_KEY != ‘PRI’,1,NULL) ) AS other_key
-> 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 = ‘WP_SCHEMA’
-> GROUP BY t.table_name
-> ORDER BY table_schema,table_name;
+————–+—————————+————-+———–+
| table_schema | table_name | primary_key | other_key |
+————–+—————————+————-+———–+
| WP_SCHEMA | wp_category | 1 | 0 |
| WP_SCHEMA | wp_comments | 1 | 3 |
| WP_SCHEMA | wp_item | 1 | 0 |
| WP_SCHEMA | wp_links | 1 | 2 |
| WP_SCHEMA | wp_maker | 1 | 0 |
| WP_SCHEMA | wp_options | 3 | 0 |
| WP_SCHEMA | wp_postmeta | 1 | 2 |
| WP_SCHEMA | wp_posts | 1 | 2 |
| WP_SCHEMA | wp_price | 1 | 0 |
| WP_SCHEMA | wp_product08 | 1 | 0 |
| WP_SCHEMA | wp_terms | 1 | 2 |
| WP_SCHEMA | wp_term_relationships | 2 | 0 |
| WP_SCHEMA | wp_term_taxonomy | 1 | 1 |
| WP_SCHEMA | wp_usermeta | 1 | 2 |
| WP_SCHEMA | wp_users | 1 | 2 |
+————–+—————————+————-+———–+
15 rows in set (0.31 sec)

mysql>


mysqldump Ver 10.13 Distrib 5.1.30, for pc-linux-gnu (i686)
By Igor Romanenko, Monty, Jani & Sinisa
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Dumping definition and data mysql database or table
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3…]
OR mysqldump [OPTIONS] --all-databases[OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
The following groups are read: mysqldump client
The following options may be given as the first argument:
--print-defaults Print the program argument list and exit

■ディフォルトのオプションを表示(オプションを指定しないと以下のように設定されている)
=======================================================================
[root@colinux mysql]# mysqldump --print-defaults
mysqldump would have been started with the following arguments:
--port=3306 --socket=/tmp/mysql.sock --default-character-set=utf8 --quick --max_
allowed_packet=16M --default-character-set=utf8

[root@colinux mysql]#
=======================================================================


--no-defaults Don't read default options from any options file

■ディフォルトオプションをオプションファイルから読み込まない

--defaults-file=# Only read default options from the given file #

■特定のオプションファイルからのみバックアップオプションを読み込む

--defaults-extra-file=# Read this file after the global files are read

■グローバルファイルを読み込んだ後にこのファイルを読み込む。

-a, --all Deprecated. Use --create-options instead.
-A, --all-databases Dump all the databases. This will be same as --databases
with all databases selected.
-Y, --all-tablespaces
Dump all the tablespaces.
-y, --no-tablespaces
Do not dump any tablespace information.
--add-drop-database Add a 'DROP DATABASE' before each create.
--add-drop-table Add a 'drop table' before each create.
--add-locks Add locks around insert statements.
--allow-keywords Allow creation of column names that are keywords.
--character-sets-dir=name
Directory where character sets are.
-i, --comments Write additional information.
--compatible=name Change the dump to be compatible with a given mode. By
default tables are dumped in a format optimized for
MySQL. Legal modes are: ansi, mysql323, mysql40,
postgresql, oracle, mssql, db2, maxdb, no_key_options,
no_table_options, no_field_options. One can use several
modes separated by commas. Note: Requires MySQL server
version 4.1.0 or higher. This option is ignored with
earlier server versions.
--compact Give less verbose output (useful for debugging). Disables
structure comments and header/footer constructs. Enables
options --skip-add-drop-table --no-set-names
--skip-disable-keys --skip-add-locks
-c, --complete-insert
Use complete insert statements.
-C, --compress Use compression in server/client protocol.
--create-options Include all MySQL specific create options.
-B, --databases To dump several databases. Note the difference in usage;
In this case no tables are given. All name arguments are
regarded as databasenames. 'USE db_name;' will be
included in the output.

-#, --debug[=#] This is a non-debug version. Catch this and exit
--debug-check Check memory and open file usage at exit.
--debug-info Print some debug info at exit.
--default-character-set=name
Set the default character set.

■バックアップデータの文字コード。my.cnfで指定しているdefault-character-setと
同じコードを指定する。 省略した場合、UTF-8に変換されるので文字化けの原因となる。

--delayed-insert Insert rows with INSERT DELAYED;
--delete-master-logs
Delete logs on master after backup. This automatically
enables --master-data.
-K, --disable-keys '/*!40000 ALTER TABLE tb_name DISABLE KEYS */; and
'/*!40000 ALTER TABLE tb_name ENABLE KEYS */; will be put
in the output.
-E, --events Dump events.
-e, --extended-insert
Allows utilization of the new, much faster INSERT syntax.
--fields-terminated-by=name
Fields in the textfile are terminated by ...
--fields-enclosed-by=name
Fields in the importfile are enclosed by ...
--fields-optionally-enclosed-by=name
Fields in the i.file are opt. enclosed by ...
--fields-escaped-by=name
Fields in the i.file are escaped by ...
-x, --first-slave Deprecated, renamed to --lock-all-tables.
-F, --flush-logs Flush logs file in server before starting dump. Note that
if you dump many databases at once (using the option
--databases= or --all-databases), the logs will be
flushed for each database dumped. The exception is when
using --lock-all-tables or --master-data: in this case
the logs will be flushed only once, corresponding to the
moment all tables are locked. So if you want your dump
and the log flush to happen at the same exact moment you
should use --lock-all-tables or --master-data with
--flush-logs

■ダンプ前にバイナリログをスイッチさせる。差分ファイルを適用する際、
全体バックアップ直後のバイナリログから適用すればよいので、指定しておく。

--flush-privileges Emit a FLUSH PRIVILEGES statement after dumping the mysql
database. This option should be used any time the dump
contains the mysql database and any other database that
depends on the data in the mysql database for proper
restore.
-f, --force Continue even if we get an sql-error.
-?, --help Display this help message and exit.
--hex-blob Dump binary strings (BINARY, VARBINARY, BLOB) in
hexadecimal format.

■バイナリ型のデータをエスケープ処理を行わずに実際に格納された値の16進数表記で保存する。
これを指定しないとdefault-character-setがシフトJIS系の場合エスケープ処理に失敗し、
バイナリデータが破壊される場合がある。

-h, --host=name Connect to host.

■ mysqldumpは、サーバー上でもリモートクライアント側でもバックアップ出来るリモートにて
サーバーのバックアップを行う場合はこのオプションにてサーバー名を指定する。

--ignore-table=name Do not dump the specified table. To specify more than one
table to ignore, use the directive multiple times, once
for each table. Each table must be specified with both
database and table names, e.g.
--ignore-table=database.table
--insert-ignore Insert rows with INSERT IGNORE.
--lines-terminated-by=name
Lines in the i.file are terminated by ...
-x, --lock-all-tables
Locks all tables across all databases. This is achieved
by taking a global read lock for the duration of the
whole dump. Automatically turns --single-transaction and
--lock-tables off.

■ダンプ前に全データベースを共有ロックする。"FLUSH TABLES WITH READ LOCK"
これは、すべてのセッションから一切の更新ができなくなる。"UNLOCK TABLES"コマンド
で共有ロックを解除できる。

-l, --lock-tables Lock all tables for read.

■ダンプ前に全テーブルをロックする。但し、データベース単位でロックする為、
DB間の一貫性は保障しない。 MyISAMバックアップ時はこのオプションでロックをかける。
InnoDBの場合は、--single-transactionを使用する。


--log-error=name Append warnings and errors to given file.

--master-data
[=#] This causes the binary log position and filename to be
appended to the output. If equal to 1, will print it as a
CHANGE MASTER command; if equal to 2, that command will
be prefixed with a comment symbol. This option will turn
--lock-all-tables on, unless --single-transaction is
specified too (in which case a global read lock is only
taken a short time at the beginning of the dump - don't
forget to read about --single-transaction below). In all
cases any action on logs will happen at the exact moment
of the dump.Option automatically turns --lock-tables off.

■値が1の場合、CHANGE MASTER文でダンプ開始時のバイナリログの位置情報を記録する。
■値が2の場合、CHANGE MASTER文がコメントアウトされる。(デフォルト)

--max_allowed_packet=#
--net_buffer_length=#
--no-autocommit Wrap tables with autocommit/commit statements.
-n, --no-create-db 'CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name;' will
not be put in the output. The above line will be added
otherwise, if --databases or --all-databases option was
given.}.
-t, --no-create-info
Don't write table creation info.
-d, --no-data No row information.
-N, --no-set-names Deprecated. Use --skip-set-charset instead.
--opt Same as --add-drop-table, --add-locks, --create-options,
--quick, --extended-insert, --lock-tables, --set-charset,
and --disable-keys. Enabled by default, disable with
--skip-opt.
--order-by-primary Sorts each table's rows by primary key, or first unique
key, if such a key exists. Useful when dumping a MyISAM
table to be loaded into an InnoDB table, but will make
the dump itself take considerably longer.
-p, --password[=name]
Password to use when connecting to server. If password is
not given it's solicited on the tty.

-P, --port
=# Port number to use for connection.
--protocol=name The protocol of connection (tcp,socket,pipe,memory).
-q, --quick Don't buffer query, dump directly to stdout.
-Q, --quote-names Quote table and column names with backticks (`).
--replace Use REPLACE INTO instead of INSERT INTO.
-r, --result-file=name
Direct output to a given file. This option should be used
in MSDOS, because it prevents new line '\n' from being
converted to '\r\n' (carriage return + line feed).
-R, --routines Dump stored routines (functions and procedures).
--set-charset Add 'SET NAMES default_character_set' to the output.
Enabled by default; suppress with --skip-set-charset.
-O, --set-variable=name
Change the value of a variable. Please note that this
option is deprecated; you can set variables directly with
--variable-name=value.
--single-transaction
Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines. While a
--single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them. Option automatically turns off --lock-tables.

■ダンプ前にBEGIN SQL文を発行する。--master-data=2と組み合わせて指定することで、
ロックをかけずに一貫性のあるバックアップを取得できる。但し、InnoDBの場合に限る。

--dump-date Put a dump date to the end of the output.
--skip-opt Disable --opt. Disables --add-drop-table, --add-locks,
--create-options, --quick, --extended-insert,
--lock-tables, --set-charset, and --disable-keys.
-S, --socket=name Socket file to use for connection.
-T, --tab=name Creates tab separated textfile for each table to given
path. (creates .sql and .txt files). NOTE: This only
works if mysqldump is run on the same machine as the
mysqld daemon.
--tables Overrides option --databases (-B).
--triggers Dump triggers for each dumped table
--tz-utc SET TIME_ZONE='+00:00' at top of dump to allow dumping of
TIMESTAMP data when a server has data in different time
zones or data is being moved between servers with
different time zones.
-u, --user=name User for login if not current user.
-v, --verbose Print info about the various stages.
-V, --version Output version information and exit.
-w, --where=name Dump only selected records; QUOTES mandatory!
-X, --xml Dump a database as well formed XML.

Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- -----------------------------
all TRUE
all-databases FALSE
all-tablespaces FALSE
no-tablespaces FALSE
add-drop-database FALSE
add-drop-table TRUE
add-locks TRUE
allow-keywords FALSE
character-sets-dir (No default value)
comments TRUE
compatible (No default value)
compact FALSE
complete-insert FALSE
compress FALSE
create-options TRUE
databases FALSE
debug-check FALSE
debug-info FALSE
default-character-set utf8
delayed-insert FALSE
delete-master-logs FALSE
disable-keys TRUE
events FALSE
extended-insert TRUE
fields-terminated-by (No default value)
fields-enclosed-by (No default value)
fields-optionally-enclosed-by (No default value)
fields-escaped-by (No default value)
first-slave FALSE
flush-logs FALSE
flush-privileges FALSE
force FALSE
hex-blob FALSE
host (No default value)
insert-ignore FALSE
lines-terminated-by (No default value)
lock-all-tables FALSE
lock-tables TRUE
log-error (No default value)
master-data 0
max_allowed_packet 16777216
net_buffer_length 1047551
no-autocommit FALSE
no-create-db FALSE
no-create-info FALSE
no-data FALSE
order-by-primary FALSE
port 3306
quick TRUE
quote-names TRUE
replace FALSE
routines FALSE
set-charset TRUE
single-transaction FALSE
dump-date TRUE
socket /tmp/mysql.sock
tab (No default value)
triggers TRUE
tz-utc TRUE
user (No default value)
verbose FALSE
where (No default value)

■■■幾つかのオプションを利用してバックアップ検証■■■

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


mysqldump --single-transaction --flush-logs --master-data=2 --databases DB01 DB02 TEST > /tmp/MYSQL_DUMP_3DB_WITHOPT.sql -u root -p

[root@colinux tmp]# ls -l
total 12
-rw-r–r– 1 root root 8843 2009-02-08 08:30 MYSQL_DUMP_3DB_WITHOPT.sql
srwxrwxrwx 1 mysql mysql 0 2009-02-08 08:30 mysql.sock
[root@colinux tmp]#

注意※ バイナリーログをONにしていないと –flush-logが指定出来ないのでONにする。

mysqldump

ログのスイッチが行われているので、メモリー内データもFLUSHされている。
オラクルのオンラインバックアップと同じですね。
mysqldump_log


FEDERATED ストレージエンジンは、レプリケーションやクラスターテクノロジを
使わずに、ローカルサーバ上のリモートMySQLデータベースからデータに
アクセスすることを可能にします。FEDERATED テーブルを利用する時、
ローカルサーバ上のクエリはリモート(federated)テーブル上で自動的に
実行されます。ローカルテーブル上にはデータは何も格納されません。
データは、ネットワークに接続された他のMySQLサーバ(リモートサーバ)
上のデータベース内に格納されています。

MS SQLでいうリンクサーバー、オラクルでいうDB LINKに近い
機能かなと考えてます。

ソースからMySQLを構築し FEDERATED ストレージエンジンの機能を
有効にするには、–with-federated-storage-engine オプションの 
configure コマンドを実行します。

Federatedエンジンではリモートサーバ上のテーブルが、
ローカルサーバ上のデータベース内に存在するかのように動作します。
ローカルサーバのFederatedテーブルにアクセスするアプリケーションは、
リモートサーバの存在を意識することなく処理を行えます。
処理結果はリモートサーバ上のテーブルに反映されます。

データベースのあるリモートサーバ、(.frm ファイルに格納されている)
テーブル定義や関連テーブルのテーブルによって成り立っているもので
あると言い換える事もできます。リモートテーブルのテーブルタイプは
MyISAM や InnoDB を含むリモート mysqld サーバに
サポートされている全てのタイプ
と言えるかもしれません。

1. リモートサーバ上にテーブルを作成します。または、
SHOW CREATE TABLE ステートメントを利用したりして、
存在するテーブルのテーブル定義のノートを作成します。

2. ローカルサーバ上に同一のテーブル定義を使ってテーブル
を作成しますが、そのときローカルテーブルをリモートテーブルにリンク
させるための接続情報を追加します。


CONNECTIONを利用して FEDERATED テーブルを作成



CREATE SERVERを利用してFEDERATED テーブルを作成



FEDERATEDストレージエンジンを専門に扱うフォーラム

━  Federatedテーブルに対しては、以下のコマンドが実行が可能 ━
SELECT文
INSERT文
UPDATE文
DELETE文
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
※実際のデータへの処理はリモートサーバにて行われるため、
リモートサーバにてインデックスが有効であれば、
インデックスが使用されます。

━━━Federatedエンジンにはいくつかの制約があります ━━━━
* トランザクションをサポートしません
* ALTER TABLE文とDROP TABLE文をサポートしません
* クエリーキャッシュをサポートしません
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

ローカルサーバとリモートサーバを同一のサーバにて構成することも可能
しかし同一サーバで構成するケースはあまりありません。

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
scheme      “mysql”文字列固定
user_name      リモートサーバへ接続するためのユーザ名
password       リモートサーバへ接続するためのパスワード(省略可能)
host_name      リモートサーバのホスト名
port_num       リモートサーバの接続ポート(省略可能)
db_name      リモートサーバ上の対象データベース名
tbl_name       リモートサーバ上の対象テーブル名
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Federatedテーブルを作成する際は、事前に対象となるリモートテーブルが
存在し、それが接続可能な状態になっている必要があります

もし、リモートテーブルが存在しないとCREATE TABLE文の実行が
正常終了しません。

Federatedテーブルを作成するとローカルサーバ上のデータベースディレクトリ
内に、どのようなカラム構成にてできているかなどのテーブル構造のデータが
格納された「テーブル名.frm」ファイルのみが作成されます。ディスク上に
データを格納するためのファイルは存在しません。
※ メモリーテーブルと同じ。


MySQL はMyISAM とMEMORY テーブルにはテーブルレベルロック
を使用し、InnoDBテーブルには行レベルロックを使用します。
Table_locks_waitedおよびTable_locks_immediateステータス変数を
チェックすることでシステム上でテーブルロック競合を分析できます。

mysql> SHOW STATUS LIKE ‘Table%’;
+———————–+——-+
| Variable_name | Value |
+———————–+——-+
| Table_locks_immediate | 19 |
| Table_locks_waited | 0 |
+———————–+——-+
2 rows in set (0.01 sec)

mysql>

InnoDBは行ロックを使用する。InnoDBでは、SQLステートメントの
トランザクションの初めではなく、プロセス中に自動的に入手するため、
デッドロックが可能です。

行レベルロックの利点
* 多数のスレッドで異なる行をアクセスする際に、ロックコンフリクトが少なくてすみます。
* ロールバックの変更がすくなくてすみます。
*1つの行を長時間ロックすることが可能です。

http://dev.mysql.com/doc/refman/5.1/ja/internal-locking.html


メモリーテーブルは、処理が早いので使い方によってはかなり
作業効率をあげてくれるかと思います。

mysql> CREATE TABLE MEM_TABLE (i INT) ENGINE = MEMORY;
Query OK, 0 rows affected (0.00 sec)
mysql>

mysql> show table status like 'MEM%'\G
*************************** 1. row ***************************
           Name: MEM_TABLE
         Engine: MEMORY
        Version: 10
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 5
    Data_length: 0
Max_data_length: 10485760
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.01 sec)

mysql> DROP TABLE MEM_TABLE;
Query OK, 0 rows affected (0.00 sec)

memory_tbl

===============================================================
そのニ
===============================================================


mysql> CREATE TABLE MEM ENGINE = MEMORY
    -> select id,comment from T1;
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> show table status like 'MEM'\G
*************************** 1. row ***************************
           Name: MEM
         Engine: MEMORY
        Version: 10
     Row_format: Fixed
           Rows: 12
 Avg_row_length: 156
    Data_length: 260160
Max_data_length: 16357692
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql>

オリジナルテーブル

mysql> select id,comment from T1;
+----+---------------+
| id | comment       |
+----+---------------+
|  1 | test update   |
|  2 | 22222222222   |
|  3 | test 20090117 |
|  4 | test 20090117 |
|  5 | test 20090117 |
|  6 | test 20090117 |
|  7 | test 20090117 |
|  8 | test 20090117 |
|  9 | test 20090117 |
| 10 | TEST DE SU    |
| 11 | TEST DE SU    |
| 12 | TEST DE SU    |
+----+---------------+
12 rows in set (0.00 sec)

メモリー(HEAP)テーブル

mysql> select * from MEM;
+----+---------------+
| id | comment       |
+----+---------------+
|  1 | test update   |
|  2 | 22222222222   |
|  3 | test 20090117 |
|  4 | test 20090117 |
|  5 | test 20090117 |
|  6 | test 20090117 |
|  7 | test 20090117 |
|  8 | test 20090117 |
|  9 | test 20090117 |
| 10 | TEST DE SU    |
| 11 | TEST DE SU    |
| 12 | TEST DE SU    |
+----+---------------+
12 rows in set (0.00 sec)

mysql>

[root@colinux TEST]# /etc/init.d/mysql.server restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@colinux TEST]#

mysql> show tables;
+----------------+
| Tables_in_TEST |
+----------------+
| MEM            |
| MEM2           |
| MEM3           |
| T1             |
| T2             |
+----------------+
5 rows in set (0.00 sec)

mysql> select * from MEM;
Empty set (0.00 sec)

mysql>

※ 再起動するとメモリーテーブルにデータはありません。
.frmファイルはあるので、テーブルはDROPしない限り再起動しても存在します。

frm_mem

ハッシュインデックスを使用し、処理が非常に高速で、
テンポラリーテーブルを作成するのに大変便利です。
ただし、サーバがクラッシュすると、この MEMORY
テーブルに格納されたすべてのデータが失われます。
テーブル自体は、定義がディスク上に .frm ファイルで
格納されているので引き続き存在しますが、サーバが
再起動したときにはデータは全て失われています。

MEMORY テーブルは、テーブルあたり最大32インデックスまで、
インデックスあたり16列、インデックスの最大幅は500バイトです。
The MEMORY ストレージエンジンは HASHBTREE 両方のインデックスを
使って実行します。ここに記すように USING 節 を追加することに
よりどちらのインデックスであるかを明確にすることが出来ます。

=========================================================
HASH
=========================================================


mysql> CREATE TABLE MEM2
    ->     (id INT, INDEX USING HASH (id))
    ->     ENGINE = MEMORY;
Query OK, 0 rows affected (0.01 sec)

=========================================================
BTREE
=========================================================


mysql> CREATE TABLE MEM3
    ->     (id INT, INDEX USING BTREE (id))
    ->     ENGINE = MEMORY;
Query OK, 0 rows affected (0.00 sec)

mysql>desc MEM;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   |     | 0       |       |
| comment | varchar(50) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc MEM2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  | MUL | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> desc MEM3;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  | MUL | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql>

■MEMORY テーブルに、一意でないキーを使用出来ます (ハッシュテーブルにはあまり見られない機能です)。
■MEMORY テーブル上に重複キーをもつハッシュインデックスがある場合(作成されるインデックスは同じ
値を持つことが多い)、 キーの値に影響を与えるテーブルアップデートと、
全ての消去は非常に処理が遅くなります。処理速度がどの程度落ちるかは、
重複の度合いに比例します (或いは、インデックス濃度に反比例します。) 。
BTREE インデックス を使用すれば、この問題は生じません。
■インデックスを張ったカラムが NULL 値を含みます。
■MEMORY テーブルが固定長のレコードフォーマットを使用しています。
■MEMORY テーブルは BLOB や TEXT カラムをサポートしません。
■MEMORY は AUTO_INCREMENT カラムのサポートもしています。
■INSERT DELAYED のスレッドを MEMORY テーブルで使用出来ます。
■MEMORY テーブルは全てのクライアント間で共有されます(他の TEMPORARY と同様。)。
■MEMORY テーブルのデータはメモリ内に格納されますが、MEMORY
テーブルは、クエリ実行中サーバが作成するテンポラリーテーブルとその情報を共有します。
■テンポラリーテーブルのサイズが大きくなりすぎると、サーバは自動的に
テーブル形式を変換してディスクに格納します。最大サイズはシステム変数
tmp_table_size から求められます。
■MEMORY テーブルは決してディスク上のテーブルに変換されないようにします。
誤った操作を行わないために、 max_heap_table_size システム変数をセットして
MEMORY テーブルの最大サイズを設定することが出来ます。
個々のテーブルに関しては、CREATE TABLE ステートメントの中で
MAX_ROWS のオプションを指定することも出来ます。
■サーバには、同時に動作する全ての MEMORY テーブルを維持するための十分なメモリが必要です。
■MEMORY テーブルの不要になったメモリを開放するには、DELETE または TRUNCATE TABLE
を実行するか、DROP TABLEを指定してテーブルをまとめて削除します。
■MySQLサーバ起動時に MEMORY テーブルにデータを投入したい場合は、
–init-file オプションを指定します。例えば、ファイルに
INSERT INTO … SELECT または LOAD DATA INFILE iなどの
ステートメントを使用し、固定データソースからテーブルを取り込むことが出来ます。
■レプリケーションを使用している場合、 シャットダウン、再起動後のマスタサーバの
MEMORY テーブルは空になっています。ところが、スレーブはこれを認識しないため、
そこにある情報は古いものとなってしまいます。マスタ起動後、マスタ上のMEMORY
テーブルを初めて使う際、DELETE ステートメントがマスタのバイナリログに自動的に
書き込まれます。このようにして、スレーブをマスタに再び一致させます。 ここで注意することは、
この方法によっても、マスタ再起動からその後のテーブル使用時までのインターバル中、
スレーブは依然として古いデータを保存し続けているということです。ただし、マスタ起動時
MEMORY テーブルにデータを投入する際 –init-file オプションを使用することにより、
このインターバルタイムはゼロになります。

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
━━━MEMORYテーブル上、一行に必要なメモリは以下の表現で算出━━━
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
SUM_OVER_ALL_BTREE_KEYS(max_length_of_key + sizeof(char*) × 4)
+ SUM_OVER_ALL_HASH_KEYS(sizeof(char*) × 2)
+ ALIGN(length_of_row+1, sizeof(char*))
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
ALIGN() は行の長さを char ポインタサイズのちょうど倍数にするための数式です。
sizeof(char*) は32ビットマシンでは4、64ビットマシンでは8です。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

MEMORY (HEAP) ストレージエンジン

REPLICATION環境での留意点 add on 2015/05/13


root@localhost [test]> show create table T_MEM01\G
*************************** 1. row ***************************
       Table: T_MEM01
Create Table: CREATE TABLE `T_MEM01` (
  `id` int(11) DEFAULT NULL,
  `text` varchar(100) DEFAULT NULL,
  KEY `idx_T_MEM01_id` (`id`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

root@localhost [test]> 

root@localhost [test]> insert into T_MEM01 select * from  T_ONLINE_DDL;
Query OK, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

root@localhost [test]> select * from T_MEM01;
+------+-----------------+
| id   | text            |
+------+-----------------+
|    1 | Group by Test A |
|    2 | Group by Test A |
|    3 | Group by Test A |
|    4 | Group by Test B |
|    5 | Group by Test B |
|    6 | Group by Test B |
|    7 | Group by Test C |
|    8 | Group by Test C |
|    9 | Group by Test C |
+------+-----------------+
9 rows in set (0.00 sec)

root@localhost [test]> 

root@localhost [test]> explain select * from T_MEM01 where id = 8;
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------+
| id | select_type | table   | type | possible_keys  | key            | key_len | ref   | rows | Extra |
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------+
|  1 | SIMPLE      | T_MEM01 | ref  | idx_T_MEM01_id | idx_T_MEM01_id | 5       | const |    2 | NULL  |
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------+
1 row in set (0.00 sec)

root@localhost [test]> 

再起動してみる

[admin@GA01 ~]$ sudo -s
[sudo] password for admin: 
[root@GA01 admin]# /etc/init.d/mysql.server restart
Shutting down MySQL... SUCCESS! 
Starting MySQL. SUCCESS! 
[root@GA01 admin]# exit


[admin@GA01 ~]$ 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.6.24-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2015, 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.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)]> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
root@localhost [test]> select * from T_MEM01;
Empty set (0.00 sec)

root@localhost [test]> show create table T_MEM01\G
*************************** 1. row ***************************
       Table: T_MEM01
Create Table: CREATE TABLE `T_MEM01` (
  `id` int(11) DEFAULT NULL,
  `text` varchar(100) DEFAULT NULL,
  KEY `idx_T_MEM01_id` (`id`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

root@localhost [test]> 

※ Masterは再起動してデータは無いが、Slaveには残っているので注意!!
※ レプリケーション環境で利用する場合はレプリケーション対象外にする方法もある。
その他、17.4.1.21 Replication and MEMORY Tablesに色々と留意点が載っているので要確認。

補足: 
MEMORYテーブル以外には、TEMPORARY TABLEを利用する方法もあります。
MYSQL: Create Temporary Table with Index and SELECT
http://stackoverflow.com/questions/14397785/mysql-create-temporary-table-with-index-and-select


INNODBテーブルのデフラグメント化

■調査
mysql> show table status like ‘T1’\G
*************************** 1. row ***************************
Name: T1
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 12
Avg_row_length: 1365
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 4194304
Auto_increment: 13
Create_time: 2009-02-06 11:54:08
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

mysql>

■メンテナンス
mysql> ALTER TABLE T1 ENGINE=INNODB;
Query OK, 12 rows affected (0.06 sec)
Records: 12 Duplicates: 0 Warnings: 0

————————————————————————————–
ALTER TABLE 操作を定期的に実行すればインデックス スキャンの速度を上げる
事ができます:
それによって MySQL はテーブルを再構成します。デフラグ操作を行う別の方法は、
テーブルをテキスト ファイルにダンプし、テーブルをドロップし、
そしてそれをダンプ ファイルから再ロードする為に mysqldump を利用する事です。


MYSQLDUMPにてデータのみをテキストファイルに抽出する。

[root@colinux tmp]# mysqldump --no-create-info --tab=/tmp --lines-terminated-by=
"\r\n" TEST -u root -p

Enter password:
[root@colinux tmp]# ls -l

[root@colinux tmp]# ls -l
total 8
srwxrwxrwx 1 mysql mysql 0 2009-02-06 18:09 mysql.sock
-rw-rw-rw- 1 mysql mysql 320 2009-02-06 18:11 T1.txt
-rw-rw-rw- 1 mysql mysql 15 2009-02-06 18:11 T2.txt
[root@colinux tmp]#

data_only

mysql> LOAD DATA INFILE ‘/tmp/T1.txt’ INTO TABLE T1 LINES TERMINATED BY ‘\r\n’;
Query OK, 12 rows affected (0.02 sec)
Records: 12 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from T1;
+—-+————+—————+
| id | name | comment |
+—-+————+—————+
| 1 | SHOW WARIN | test update |
| 2 | SHOW WARIN | 22222222222 |
| 3 | select_lim | test 20090117 |
| 4 | select_lim | test 20090117 |
| 5 | select_lim | test 20090117 |
| 6 | select_lim | test 20090117 |
| 7 | select_lim | test 20090117 |
| 8 | select_lim | test 20090117 |
| 9 | select_lim | test 20090117 |
| 10 | FLUSHMAN | TEST DE SU |
| 11 | FLUSHMAN | TEST DE SU |
| 12 | FLUSHMAN | TEST DE SU |
+—-+————+—————+
12 rows in set (0.00 sec)

mysql>

load


他のサーバーなどにDBの側だけ作成するために、
データ無しのBACKUPを作成する方法。

[root@colinux tmp]# mysqldump --no-data TEST > /tmp/TEST_SCRIPT_ONLY.sql -u root
-p
Enter password:
[root@colinux tmp]#

テーブル作成ようのDDL文しかありません。

create_table_only


REPLICATION環境にてSLAVE側でバックアップする為の手順。

STEP1
マスタの処理要求を停止する。または mysqladmin を使用して完全に
スレーブを停止する。

shell> mysqladmin stop-slave

or

別の方法としては、レプリケーション SQL スレッドを停止して
リレー ログ ファイルの処理を停止します。この方法は、
バイナリ ログのデータの転送を許可します。
この方法を活発なレプリケーション環境で使用すると、
スレーブ処理を再開をしたときにキャッチ アップ プロセスを
スピードアップする可能性があります。

shell> mysql -e ‘STOP SLAVE SQL_THREAD;’

STEP2
FLUSH TABLES
flush_tables

STEP3
データベースのバックアップ
shell> mysqldump --all-databases > fulldb.dump

STEP4
ダンプが完了したら、スレーブのオペレーションを再開する。
shell> mysqladmin start-slave
or
shell> mysql -e ‘START SLAVE SQL_THREAD;’


--single-transactionオプション

[root@colinux tmp]# mysqldump --databases --single-transaction TEST > /tmp/backu
p_single_tran_test.sql -u root -p
Enter password:
[root@colinux tmp]# ls -l /tmp/backup_single_tran_test.sql
-rw-r–r– 1 root root 2891 2009-02-06 12:38 /tmp/backup_single_tran_test.sql
[root@colinux tmp]#

このオプションはサーバからデータをダンプする前にBEGIN SQLステートメント
を発行します。InnoDBといったトランザクションテーブルに対してのみ便利です。
なぜなら、アプリケーションをブロックせずに、BEGINが発行された当時の
データベースの状態をダンプするからです。

このオプションを使用しているときは、一定の状態でダンプされるのは
InnoDBテーブルのみだということを留意してください。
例えば、このオプションを使用中にダンプされたMyISAMやMEMORYテーブル
は状態が変化する可能性があります。