普段MYSQLをWindowsで利用する事がないので、一応最低限の
事は知識として知っておこうと思い覚書。。。

MySQL はすべての Windows プラットフォームで TCP/IP をサポートしています。
platforms.mysqld-nt サーバは Windows NT、2000、XP、および 2003 上の名前付きパイプ
をサポートしています。しかし、デフォルトではプラットフォーに関係なく TCP/IP を使用します。
(名前付きパイプは多くの Windows 設定では TCP/IP より低速です。)

名前付きパイプの使用は以下の条件

1) 名前付きパイプはサーバを –enable-named-pipe オプションで起動したときのみ有効
です。このオプションは明示的に使用する必要があります。
2)名前付きパイプの接続は mysqld-nt サーバにのみ可能で、サーバが名前付きパイプを
サポートしている Windows のバージョン (NT、2000、XP、2003) で動作している時のみ
使用できます。
3)これらのサーバは Windows 98 あるいは Me でも動作しますが、TCP/IP がインストール
されている時のみで、名前付き接続は使用できません。
4)これらのサーバは Windows 95 では動作しません。

    接続プロトコルとOSによる接続の違い

==============================================
TCP/IP
==============================================
全てのOSで使用可能
リモート、ローカルで使用可能
--skip-networking optionを指定してMYSQLを起動すると無効になる。

==============================================
Unix Socket file
==============================================
Unix系 OS
ローカル接続のみ
TCP/IPよりパフォーマンスが良い

==============================================
Named pipe
==============================================
Windows OS
ローカル接続のみ
mysql-nt,mysql-max-nt
--enable-named-pipe optionを使用しサーバを起動必要あり
TCP/IPより遅い

==============================================
Shared memory
==============================================
Windows OS
ローカル接続のみ
--shared-memory optionを使用しサーバを起動必要あり

2.3.8. MySQL サーバ タイプの選択


バイナリーログをマスターしてデータのリカバリーをマスター


--start-datetime
バイナリログ開始日時を指定した時点からのログがファイルに出力される。

--stop-datetime
バイナリログ終了日時を指定した時点までのログがファイルに出力される。

--start-position
指定された位置からログを読む

--stop-position
指定された位置までログを読み込む


--start-datetime=name
Start reading the binlog at first event having a datetime
equal or posterior to the argument; the argument must be
a date and time in the local time zone, in any format
accepted by the MySQL server for DATETIME and TIMESTAMP
types, for example: 2004-12-25 11:25:56 (you should
probably use quotes for your shell to set it properly).
--start-position=# Start reading the binlog at position N. Applies to the
first binlog passed on the command line.
--stop-datetime=name
Stop reading the binlog at first event having a datetime
equal or posterior to the argument; the argument must be
a date and time in the local time zone, in any format
accepted by the MySQL server for DATETIME and TIMESTAMP
types, for example: 2004-12-25 11:25:56 (you should
probably use quotes for your shell to set it properly).
--stop-position=# Stop reading the binlog at position N. Applies to the
last binlog passed on the command line.

「例」

[root@colinux data]# ls -l mysql-bin.00002*
-rwxrwxrwx 1 root mysql 125 2009-02-25 11:09 mysql-bin.000020
-rwxrwxrwx 1 root mysql 125 2009-02-25 11:19 mysql-bin.000021
-rwxrwxrwx 1 root mysql 125 2009-02-25 11:24 mysql-bin.000022
-rw-rw—- 1 mysql mysql 125 2009-02-25 11:26 mysql-bin.000023
-rw-rw—- 1 mysql mysql 125 2009-02-25 11:33 mysql-bin.000024
-rw-rw—- 1 mysql mysql 125 2009-02-25 11:34 mysql-bin.000025
-rw-rw—- 1 mysql mysql 125 2009-02-25 11:38 mysql-bin.000026
-rw-rw—- 1 mysql mysql 388 2009-02-25 16:28 mysql-bin.000027
-rw-rw—- 1 mysql mysql 1200 2009-02-26 17:01 mysql-bin.000028
-rw-rw—- 1 mysql mysql 18361 2009-02-27 15:10 mysql-bin.000029
[root@colinux data]# mysqlbinlog --no-defaults --start-datetime="2009-02-27 00:01:00" mysql-bin.000029 > /tmp/today_recover.sql

開始時間を指定
mysqlbinlog

出力結果
mysqlbinlog_read


━ MYISAMのテーブルをコピーしてみる。 ━ 

mysql> use TEST
Database changed
mysql> LOCK TABLES MYSQLIMP READ;
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH TABLE MYSQLIMP;
Query OK, 0 rows affected (0.00 sec)
mysql>

——- ここは別コンソール ——–
[root@colinux TEST]# cp MYSQLIMP.* /tmp
——————————————

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

myisam_table_copy

DBを停止しても良い場合は、一度サービスを停止してファイル
コピーすればOK (COLD BACKUP)
STOP —> COPY FILES —–> START


———————-「 特定DB内のテーブルをチェック 」———————–
++++ OK: MYISAM , INNODB
++++ NG: MEMORY

[root@colinux ~]# mysqlcheck --check DB01 -u root -p
Enter password:
DB01.TABLE007 OK
DB01.TABLE008 OK
DB01.TABLE009 OK
[root@colinux ~]#

[root@colinux ~]# mysqlcheck --check DB01 TABLE007 -u root -p
Enter password:
DB01.TABLE007 OK
[root@colinux ~]#

——————————————————————————————-

———————-「DB内のテーブルをOPTIMIZE 」———————–
++++ OK: MYISAM , INNODB
++++ NG: MEMORY

[root@colinux ~]# mysqlcheck --optimize --all-databases -u root -p
Enter password:
[root@colinux ~]#

DB01.TABLE007
note : Table does not support optimize, doing recreate + analyze instead
status : OK
DB01.TABLE008
note : Table does not support optimize, doing recreate + analyze instead
status : OK
DB01.TABLE009
note : Table does not support optimize, doing recreate + analyze instead
status : OK
DB02.animals Table is already up to date
DB02.animals02 Table is already up to date
TEST.Y2008_Y2009
note : The storage engine for the table doesn’t support optimize
TEST.Y2009 Table is already up to date
client_test_db.bug2247 Table is already up to date
client_test_db.foo_dfr Table is already up to date
client_test_db.my_demo_transaction

■INNODBに関しては、制限がある。
[root@colinux ~]# mysqlcheck --optimize DB01 TABLE007 -u root -p
Enter password:

DB01.TABLE007
note : Table does not support optimize, doing recreate + analyze instead
status : OK
[root@colinux ~]#

——————————————————————————————-

———————-「DB内のテーブルをREPAIR 」———————–
++++ OK: MYISAM
++++ NG: INNODB,MEMORY

■MYISAMは可能
[root@colinux ~]# mysqlcheck --repair TEST MYSQLIMP -u root -p
Enter password:

TEST.MYSQLIMP OK
[root@colinux ~]#

■INNODBはサポート外
[root@colinux ~]# mysqlcheck --repair DB01 -u root -p
Enter password:

DB01.TABLE007
note : The storage engine for the table doesn’t support repair
DB01.TABLE008
note : The storage engine for the table doesn’t support repair
DB01.TABLE009
note : The storage engine for the table doesn’t support repair
[root@colinux ~]#


========================================
my.cnfに追加し恒久対策
—————————————-
max_connections=150
thread_cache=150
========================================

========================================
再起動無しの動的変更
—————————————-
set global max_connections=150;
set global thread_cache_size=150;
========================================

========================================
max_connections
MySQLへの最大同時接続数。デフォルトは100です。
—————————————-
mysql> show variables like ‘max_connections’;
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| max_connections | 100 |
+—————–+——-+
1 row in set (0.00 sec)

↓変更

mysql> set global max_connections=150;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like ‘max_connections’;
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| max_connections | 150 |
+—————–+——-+
1 row in set (0.00 sec)
========================================

========================================
thread_cache_size
再利用のためにキャッシュ可能なスレッドの数を指定します。
大量の新しい接続を必要とする場合、この値を大きくすることで
パフォーマンスを増加させることが可能。
—————————————-

mysql> show variables like ‘thread_cache_size’;
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| thread_cache_size | 0 |
+——————-+——-+
1 row in set (0.00 sec)

↓変更

mysql> set global thread_cache_size=150;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like ‘thread_cache_size’;
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| thread_cache_size | 150 |
+——————-+——-+
1 row in set (0.00 sec)

mysql>
========================================

========================================
max_used_connections最大接続数確認
—————————————-

mysql> SHOW STATUS LIKE ‘max_used_connections’;
+———————-+——-+
| Variable_name | Value |
+———————-+——-+
| Max_used_connections | 101 |
+———————-+——-+
1 row in set (0.00 sec)
========================================


MySQL はMyISAM とMEMORY テーブルにはテーブルレベルロックを使用し、
InnoDBテーブルには行レベルロックを使用します。
MYISAMのテーブルロックの状態を調査。

mysql> show status like ‘Table_locks_waited’;
+——————–+——-+
| Variable_name | Value |
+——————–+——-+
| Table_locks_waited | 10161 |
+——————–+——-+
1 row in set (0.00 sec)

mysql> show status like ‘Table_locks_immediate’;
+———————–+———-+
| Variable_name | Value |
+———————–+———-+
| Table_locks_immediate | 57905458 |
+———————–+———-+
1 row in set (0.00 sec)

mysql>

mysql> select 10161/57905458;
+—————-+
| 10161/57905458 |
+—————-+
| 0.0002 |
+—————-+
1 row in set (0.02 sec)

mysql>

特に問題は無さそうです。

MySQL のテーブルロック方法


※ DELAYED が指定されていると、サーバはレコードをバッファに挿入する。
INSERT DELAYED ステートメントを発行したクライアントは処理を続行することができる。
※ テーブルが使用されていると、サーバはレコードを保持。テーブルが解放されると、
サーバはレコードの挿入を開始し、そのテーブルに対する新しい読み取り要求がない事
   を定期的にチェックする。新しい読み取り要求があると、そのテーブルが再び解放される
まで、遅延されたレコードのキューの処理は中断される。

mysql> show variables like ‘delayed%’;
+————————+——-+
| Variable_name | Value |
+————————+——-+
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
+————————+——-+
3 rows in set (0.01 sec)

キューを作った行は、テーブルに挿入されるまでメモリ内だけで保持されます。
これは、もしmysqld を強制的に終了させたり (例えば、kill -9 を利用して)、mysqld
が突然停止してしまったりすると、ディスクに書き込まれる前のキューを作った行は
全て失われてしまう という事を意味します。

INSERT DELAYED は MyISAM、MEMORY、 ARCHIVE テーブルのみ機能

——————————————————————————————-
INNODB
——————————————————————————————-
mysql> show table status like ‘TABLE007’\G
*************************** 1. row ***************************
Name: TABLE007
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 9
Avg_row_length: 1820
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 35651584
Auto_increment: 10
Create_time: 2009-02-25 11:23:38
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

mysql>
————————————- INNODBではエラーになる —————————–
mysql> insert delayed into TABLE007(comment) values (‘Insert deleyed’);
ERROR 1616 (HY000): DELAYED option not supported for table ‘TABLE007’

——————————————————————————————-
MYISAM
——————————————————————————————-

mysql> show table status like ‘MYSQLIMP’\G
*************************** 1. row ***************************
Name: MYSQLIMP
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 2
Avg_row_length: 24
Data_length: 48
Max_data_length: 281474976710655
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2009-02-14 08:31:05
Update_time: 2009-02-14 08:32:13
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

mysql>

insert_delayed

注: テーブルが使用中でない場合、INSERT DELAYED は通常の INSERT より遅くなります。


SHOW とINFORMATION_SCHEMAがどのような違いがあるのか
疑問に思ったので、少し調べてみます。 

━━━━━━ 抜粋 ━━━━━━
SHOW は、データベース、テーブル、カラム、またサーバのステータス情報
などのような様々な情報を提供する多くの形を持っています。

mysql> SHOW BINARY LOGS;
+——————+———–+
| Log_name | File_size |
+——————+———–+
| mysql-bin.000001 | 125 |
| mysql-bin.000002 | 125 |
| mysql-bin.000003 | 220235904 |
| mysql-bin.000004 | 125 |
| mysql-bin.000005 | 683459 |
| mysql-bin.000006 | 480901 |
| mysql-bin.000007 | 218700 |
| mysql-bin.000008 | 2028338 |
| mysql-bin.000009 | 12987 |
| mysql-bin.000010 | 3425 |
| mysql-bin.000011 | 26573 |
| mysql-bin.000012 | 6140472 |
| mysql-bin.000013 | 286 |
| mysql-bin.000014 | 3055543 |
+——————+———–+
14 rows in set (0.00 sec)

mysql>

━━━━━━ 抜粋 ━━━━━━
INFORMATION_SCHEMA はデータベース メタデータへのアクセスを提供
します。 メタデータ は、データベース名またはテーブル名、カラムのデータタイプ、
あるいはアクセス権限などのデータに関するデータです。
この情報に時々使用される他の用語にはデータ ディクショナリおよびシステム カタログがあります。

INFORMATION_SCHEMA は情報のデータベースで、MySQL サーバーが保持する他の
すべてのデータベースに関する情報を保存しています。INFORMATION_SCHEMA の
中にはいくつかの読み出し専用テーブルがあります。それらは実際は
ベーステーブルではなく表示ですので、それらに関連付けされたファイルはありません。

SELECT …FROM INFORMATION_SCHEMA ステートメントは様々なSHOW ステートメント、
つまり MySQL がサポートする ( SHOW DATABASES、SHOW TABLES、など により提供
された情報へのアクセスをさらに一貫した手法を意図したものです。SELECT は、
SHOW に比べて有利な点が 3 つあります。

1) それは Codd の規則に合致していることです。つまり、すべてのアクセスはテーブルで行われます。
2) 新しいステートメント構文を学ぶ必要はありません。なぜなら既にSELECT の機能を理解されており、
オブジェクト名を知るだけでいいからです。
3) インプリメンターはキーワードを加える心配する必要がありません。
たった 1 つの出力に代わり、可能な出力は無数にあります。これによりメタデータ
に対する多様な要件を持つアプリケーションにさらに軟性を提供します。
すべての他の DBMS が同じ手法を用いていますので移行は容易です。

SHOW は ユーザーに評判がよく、また無くなったら混乱することが考えられ
たため、従来の構文の利点だけでは SHOW を無くすのに十分な理由とは
いえません。実際のところ、INFORMATION_SCHEMA を実装することに
よって、SHOW もまた同様に強化されます。

MySQL での INFORMATION_SCHEMA テーブル構成の
インプリメンテーションは ANSI/ISO SQL:2003 標準 パート 11 の
Schemata に準拠しています。SQL:2003 コア機能 F021
基本情報スキーマに最大限準拠することを意図しています。
━━━━━━━━━━━━━━━━━━━

mysql> SHOW TABLES FROM INFORMATION_SCHEMA;
+—————————————+
| Tables_in_INFORMATION_SCHEMA |
+—————————————+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
+—————————————+
28 rows in set (0.01 sec)

mysql>


MYISAMはデータファイルがFULLになると、データファイル拡張が起こるまで
待機し続けますが、InnoDBはデータファイルがFULLになるとnext statementの
データ追加処理をrolls backします。アプリケーション側でデータファイルがいっぱい
になった事を認識して全てのトランザクションをROLLBACKするようにした方がよい。
それ以前にデータファイルがいっぱいにならないように設定するのが一番良い。

autoextend がONに設定されていればデータファイルが自動拡張します。

DATA FILE
mysql> show variables like ‘innodb_data_file_path’;
+———————–+————————+
| Variable_name | Value |
+———————–+————————+
| innodb_data_file_path | ibdata1:10M:autoextend |
+———————–+————————+
1 row in set (0.00 sec)

LOG FILE
mysql> show variables like ‘innodb_log%’;
+—————————+———+
| Variable_name | Value |
+—————————+———+
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
+—————————+———+
4 rows in set (0.01 sec)

mysql>

ibdata

———————————————————————————-
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/local/mysql/data/
#innodb_data_file_path = ibdata1:10M:autoextend
                 ↓
innodb_data_home_dir = /usr/local/mysql/data/
innodb_data_file_path = ibdata1:10M;ibdata2:30M:autoextend
———————————————————————————-

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

[root@colinux data]# tail colinux.err
090225 11:26:49 InnoDB: Data file /usr/local/mysql/data/ibdata2 did not exist:new to be created
090225 11:26:49 InnoDB: Setting file /usr/local/mysql/data/ibdata2 size to 30 MB

ibdata2

mysql> show variables like ‘innodb_data_file_path’;
+———————–+————————————+
| Variable_name | Value |
+———————–+————————————+
| innodb_data_file_path | ibdata1:10M;ibdata2:30M:autoextend |
+———————–+————————————+
1 row in set (0.01 sec)

mysql> show variables like ‘innodb_log%’;
+—————————+———+
| Variable_name | Value |
+—————————+———+
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
+—————————+———+
4 rows in set (0.01 sec)

mysql>

※ InnoDB トランザクション モデルとロック

InnoDB トランザクション モデル内のゴールは、マルチ バージョン データベースの
優れた性質を、従来の二相ロックと合体させる事です。
InnoDB は、行レベルでロックを行い、デフォルトではクエリを Oracle 式の非ロックの
一貫した読み取りとして実行します。
InnoDB のロック テーブルは領域効率の高い方法で格納される為、
ロック エスカレーションは不要です:一般には、複数のユーザがデータベースのあらゆる
レコードまたはレコードのランダムなサブセットをロックする事ができ、InnoDB でメモリ不足
が発生する事もありません。


InnoDB に関連するコマンド オプションとシステム変数

虚実であるシステム変数は、それらを名づける事によってサーバ起動時に有効にされるか、
または skip- プリフィックスを利用する事で無効にされます。
例えば、InnoDB チェックサムを有効、または無効にするには、コマンド ライン上で
–innodb_checksums か –skip-innodb_checksums を、またはオプション ファイル
内で innodb_checksums か skip-innodb_checksums を利用する事ができます。

innodb_concurrency_tickets
InnoDB に同時に入る事ができるスレッドの数は、innodb_thread_concurrency 変数
によって決められます。スレッドが InnoDB に入ろうとする時にもし並行処理の限度
までスレッド数が達していたら、それらは列になります。スレッドが InnoDB に入るのを許可
されると、innodb_concurrency_tickets の値と同等の 「フリー チケット」 をたくさん与えられ、
スレッドはそのチケットを使ってしまうまでは自由に InnoDB に出入りできます。
それ以降は、スレッドが次に InnoDB に入ろうとした時に、再度並行処理チェックの対象
となります。(または列に並ぶ可能性もある)

mysql> show variables like ‘innodb_flush_log_at_trx_commit’;
+——————————–+——-+
| Variable_name | Value |
+——————————–+——-+
| innodb_flush_log_at_trx_commit | 1 |
+——————————–+——-+
1 row in set (0.01 sec)

mysql> show variables like ‘innodb_concurrency_tickets’;
+—————————-+——-+
| Variable_name | Value |
+—————————-+——-+
| innodb_concurrency_tickets | 500 |
+—————————-+——-+
1 row in set (0.01 sec)

mysql> show variables like ‘innodb_thread_concurrency’;
+—————————+——-+
| Variable_name | Value |
+—————————+——-+
| innodb_thread_concurrency | 8 |
+—————————+——-+
1 row in set (0.01 sec)

mysql>

innodb_options

mysql> show variables like ‘innodb_%’;
innodb_options_2