Microsoft PowerPoint - 2-MySQL50_intro.ppt

MySQL 5.0 入門 日本ヒューレット パッカード株式会社 オープンソース コンピテンシ センター 2006 年 8 月 31 日 2006 Hewlett-Packard Development Company, L.P. The information contained herein is subject to change without notice

目次 MySQLのインストール ユーザデータベースの作成 MySQLのディレクトリ構成 サーバ変数とmy.cnf( オプション設定ファイル ) ログファイル MySQLのステータス確認方法 MySQLでの日本語処理 MySQLに関する情報源 付録 バイナリログ MySQL サーバの起動スクリプト 2 平成 18 年 9 月 5 日

目次 MySQL のインストール ユーザデータベースの作成 MySQL のディレクトリ構成 サーバ変数と my.cnf( オプション設定ファイル ) ログファイル MySQL のステータス確認方法 MySQL での日本語処理 MySQL に関する情報源 付録 バイナリログ MySQL サーバの起動スクリプト 3 平成 18 年 9 月 5 日

15 分ルール (MySQL のソフトの優先順位 ) 性能 MySQL, Sun と BEA WebLogic は SPEC ベンチマークで New World Records のスピードとコスト 性能効率記録を達成 2. 信頼性 調査 : MySQL データベースには殆どバグがない CNET News.com の Robert Lemos 著 2005 年 2 月 4 日 3. 使い勝手勝手の良さ 15 分ルール 15 分でダウンロード インストール MySQL が使用可能 4 平成 18 年 9 月 5 日

バイナリバイナリバイナリバイナリインストール方法あれこれ インストール方法 メリット デメリット RPM 形式 インストールが簡単 クライアントツールのみインストール といったインストールコンポーネントの選択も可能 インストールディレクトリの変更不可 1 つのマシン上の複数の MySQL サーバをインストールできない MySQL 社によりコンパイルされているのでコンパイルオプション等の変更不可 tar.gz 形式 任意のディレクトリに MySQL 本体をインストール可能 1 つのマシン上に複数の MySQL サーバ ( バージョン問わず ) が混在可能 MySQL 社によりコンパイルされているのでコンパイルオプション等の変更不可 不要なファイルもインストールされてしまう場合がある ( クライアントツールなど ) ソースコードからビルド 5 平成 18 年 9 月 5 日 コンパイルオプションを設定可能 バイナリ版で提供されていない機能を利用可能 ( デバッグ情報など ) 不要なコンポーネントを削除可能 開発バージョンを利用可 自分でビルドする必要がある バイナリ版よりもパフォーマンスが劣る場合がある 今回はtar.gz 形式でインストールインストールします

MySQL のインストール手順 (tar.gz 形式 ) STEP 1 : バイナリのダウンロード STEP 2 : OSユーザの作成 STEP 3 : MySQLのインストール STEP 4 : 権限データベースの作成 STEP 5 : MySQLサーバの起動 STEP 6 : 起動確認 6 平成 18 年 9 月 5 日

STEP 1 : バイナリのダウンロード http://www.mysql.com/ Developer Zone Downloads MySQL 5.0 Generally Available(GA) release for production use [ 直接の URL] http://dev.mysql.com/downloads/mysql/5.0.html Linux (non RPM package) downloads Linux (x86) Standard 5.0.24 Download をクリックしてダウンロードを開始 mysql-standard-5.0.24-linux-i686-glibc23.tar.gz (/tmp ディレクトリにダウンロードしたこととします ) 7 平成 18 年 9 月 5 日

STEP 2 : OS ユーザ作成 MySQL サーバを起動する OS ユーザ mysql ( グループは mysql) を作成します $ su # /usr/sbin/groupadd mysql # /usr/sbin/useradd g mysql mysql # passwd mysql ( パスワード mysql を入力 ) 8 平成 18 年 9 月 5 日

STEP 3 : MySQL のインストール MySQL サーバ本体を /usr/local/mysql にインストールします 基本的に tar.gz アーカイブを展開するだけです # cd /usr/local # tar zxvf /tmp/mysql-standard-5.0.24-linux-i686-glib23.tar.gz # chown R mysql:mysql mysql-standard-5.0.24-linux-i686-glib23 # ln s mysql-standard-5.0.24-linux-i686-glib23 mysql MySQL サーバ本体へのパスの簡潔さと将来のアップデートのし易さを考慮して /usr/local/mysql とシンボリックリンクを張っています 展開したディレクトリ以下のオーナーを MySQL サーバを起動する OS ユーザ (mysql) に設定しています 注意 : chown を行う対象は 必ず tar で展開して作成されたディレクトリ (mysqlstandard-5.0.24-linux-i686-glib23) にしてください 9 平成 18 年 9 月 5 日

STEP 4 : 権限データベースの作成 MySQL サーバが内部的に使用する権限データベースを作成します # cd /usr/local/mysql #./scripts/mysql_install_db -user=mysql ご注意 : tar.gz 形式のバイナリからのインストール時に mysql_install_dbスクリプトを実行する場合は 必ず上記ディレクトリから同スクリプトを実行してください 10 平成 18 年 9 月 5 日

STEP 5 : MySQL サーバの起動 インストールした MySQL サーバを起動します # cd /usr/local/mysql #./bin/mysqld_safe -user=mysql & 11 平成 18 年 9 月 5 日

STEP 6 : 起動確認 MySQL サーバが起動したかを確認します # cd /usr/local/mysql #./bin/mysqladmin ping mysql is alive mysql is alive と表示されれば MySQL サーバが起動していることになります 以上で MySQL サーバのインストールインストールと起動が完了完了しました!! 12 平成 18 年 9 月 5 日

まとめ : MySQL サーバのインストール MySQL バイナリのダウンロードダウンロードからから インストールインストール 起動確認までを たった 13 コマンドで実現可能!! $ wget -P /tmp http://dev.mysql.com/get/downloads/mysql-5.0/mysql-standard-5.0.24 -linux-i686-glibc23.tar.gz/from/http://mir2.ovh.net/ftp.mysql.com/ $ su # /usr/sbin/groupadd mysql # /usr/sbin/useradd g mysql mysql # passwd mysql # cd /usr/local # tar zxvf /tmp/mysql-standard-5.0.24-linux-i686-glib23.tar.gz # ln s mysql-standard-5.0.24-linux-i686-glib23 mysql # chown R mysql:mysql mysql-standard-5.0.24-linux-i686-glib23 # cd /usr/local/mysql #./scripts/mysql_install_db -user=mysql #./bin/mysql_safe -user=mysql & #./bin/mysqladmin ping 13 平成 18 年 9 月 5 日

MySQL サーバのインストール手順 (RPM 形式 ) 概要 STEP 1 : バイナリのダウンロード STEP 2 : rpm コマンドで MySQL のインストール STEP 3 : 起動確認 * tar.gz 形式でのインストールで必要だった以下のの手順は STEP 2 時に実行されます OS ユーザの作成権限データベースの作成 MySQL サーバの起動 $ wget -P /tmp http://dev.mysql.com/get/downloads/mysql-5.0/mysql-server-standard-5.0.24-0.rhel4.i386.rpm/from/http://mir2.ovh.net/ftp.mysql.com/ $ wget -P /tmp http://dev.mysql.com/get/downloads/mysql-5.0/mysql-client-standard-5.0.24-0.rhel4.i386.rpm/from/http://mir2.ovh.net/ftp.mysql.com/ $ wget -P /tmp http://dev.mysql.com/get/downloads/mysql-5.0/mysql-devel-standard-5.0.24-0.rhel4.i386.rpm/from/http://mir2.ovh.net/ftp.mysql.com/ $ wget -P /tmp http://dev.mysql.com/get/downloads/mysql-5.0/mysql-shared-standard-5.0.24-0.rhel4.i386.rpm/from/http://mir2.ovh.net/ftp.mysql.com/ $ su # rpm -ivh /tmp/mysql-server-standard-5.0.24-0.rhel4.i386.rpm ( 上記をインストールすると MySQL サーバが自動的に起動されます ) # rpm -ivh /tmp/mysql-client-standard-5.0.24-0.rhel4.i386.rpm # rpm -ivh /tmp/mysql-devel-standard-5.0.24-0.rhel4.i386.rpm # rpm -ivh /tmp/mysql-shared-standard-5.0.24-0.rhel4.i386.rpm # /usr/bin/mysqladmin ping 14 平成 18 年 9 月 5 日

目次 MySQL のインストール ユーザデータベースの作成 MySQLのディレクトリ構成 サーバ変数とmy.cnf( オプション設定ファイル ) ログファイル MySQLのステータス確認方法 MySQLでの日本語処理 MySQLに関する情報源 付録 バイナリログ MySQL サーバの起動スクリプト 15 平成 18 年 9 月 5 日

データベースの作成 mysql コマンドラインツールを使って 新規データベース mydb を作成します mydb データベースで使用する文字エンコーディングは cp932 とします # cd /usr/local/mysql #./bin/mysql u root mysql> CREATE DATABASE mydb CHARACTER SET cp932; Query OK, 1 row affected (0.06 sec) 16 平成 18 年 9 月 5 日

データベースユーザの作成 データベース mydb にアクセスするデータベースユーザを作成します ユーザ名 : dbuser パスワード : dbpass mysql> GRANT ALL PRIVILEGES ON mydb.* TO 'dbuser'@'localhost -> IDENTIFIED BY 'dbpass'; Query OK, 0 rows affected (0.40 sec) mysql> GRANT ALL PRIVILEGES ON mydb.* TO 'dbuser'@ % -> IDENTIFIED BY 'dbpass'; Query OK, 0 rows affected (0.01 sec) 17 平成 18 年 9 月 5 日

テーブルの作成 データベース mydb に 新規テーブル mytable01 を作成します mytable01 のストレージエンジンはデフォルトで MyISAM になります mysql> USE mydb Database changed mysql> CREATE TABLE mytable01 -> ( -> id INT NOT NULL AUTO_INCREMENT, -> last_name CHAR(30) NOT NULL, -> first_name CHAR(30) NOT NULL, -> PRIMARY KEY (id) -> ); Query OK, 0 rows affected (0.02 sec) 18 平成 18 年 9 月 5 日

データのインサート (1/3) テーブル mytable01 にデータをインサートします 方法 (1) INSERT 文 mysql> INSERT INTO mytable01 (last_name, first_name) -> VALUES ('Hurd', 'Mark'); Query OK, 1 row affected (0.00 sec) 方法 (2) mysql コマンドを使って $ bin/mysql -u dbuser -pdbpass mydb --default-character-set=cp932 < /tmp/data.sql /tmp/data.sql : ( 文字エンコーディングは Shift_JIS) INSERT INTO mytable01 (last_name, first_name) VALUES (' 水野 ', ' 浩典 '); INSERT INTO mytable01 (last_name, first_name) VALUES (' 大矢 ', ' 俊夫 '); INSERT INTO mytable01 (last_name, first_name) VALUES (' 佐藤 ', ' 修一 '); 19 平成 18 年 9 月 5 日

データのインサート (2/3) テーブル mytable01 にインサートしたデータを確認をします mysql> SELECT * FROM mytable01; +----+-----------+------------+ id last_name first_name +----+-----------+------------+ 1 水野 浩典 2 大矢 俊夫 3 佐藤 修一 +----+-----------+------------+ 3 rows in set (0.00 sec) もし日本語日本語が正しくしく表示表示されないされない場合場合には? $ bin/mysql -u dbuser -pdbpass --default-character-set=eucjpms もしくは mysql> SET NAMES eucjpms; mysql> SELECT * FROM mytable01; 20 平成 18 年 9 月 5 日

データのインサート (3/3) その他のインサート方法 バルク INSERT INTO mysql> INSERT INTO mytable01 (last_name, first_name) -> VALUES ('Hurd', 'Mark'), ('Livemore', 'Ann'); 参考 : http://dev.mysql.com/doc/refman/5.0/en/insert.html LOAD DATA INFILE mysql> LOAD DATA INFILE '/tmp/data.txt' -> INTO TABLE mytable01 FIELDS TERMINATED BY ',' -> LINES TERMINATED BY ' n'; 参考 : http://dev.mysql.com/doc/refman/5.0/en/load-data.html mysql source コマンド mysql> SOURCE '/tmp/data.sql'; 参考 : http://dev.mysql.com/doc/refman/5.0/en/mysql-commands.html 21 平成 18 年 9 月 5 日

MySQL サーバの停止 MySQL サーバを停止するには mysqladmin コマンドを使います # cd /usr/local/mysql # bin/mysqladmin shutdown # ps -ef grep mysqld grep -v grep # メモ : 通常 mysqlamdin 等のクライアントツールを実行する場合 bin/mysqladmin --user=<mysql 管理者ユーザユーザ名 > --password=<mysql 管理者のパスワード > コマンド のように --user と --password オプションを指定するのが一般的です 今回は mysql_install_db スクリプトによって作成された MySQL 管理者ユーザ "root"( パスワードは空 ) を使っていますが --user を指定しない場合は OS のユーザ名 "root" が --user に渡されることになっていますので 結果的に "--user=root" が指定されたことになっています 22 平成 18 年 9 月 5 日

目次 MySQLのインストール ユーザデータベースの作成 MySQL のディレクトリ構成 サーバ変数とmy.cnf( オプション設定ファイル ) ログファイル MySQLのステータス確認方法 MySQLでの日本語処理 MySQLに関する情報源 付録 バイナリログ MySQL サーバの起動スクリプト 23 平成 18 年 9 月 5 日

ディレクトリ構成 (1/2) tar.gz 形式でインストールインストールしたした場合場合のデフォルト /etc `-- my.cnf -- オプション設定設定ファイル /usr/local/mysql -- MySQL 本体のインストールディレクトリ (basedir) -- bin -- MySQL サーバ (mysqld) やクライアントツール等 -- docs -- ドキュメントと変更履歴 -- include -- ヘッダーファイル -- lib -- ライブラリ -- man -- man ファイル -- mysql-test -- テストプログラム データなど -- scripts -- mysql_install_db スクリプト -- share -- エラーメッセージファイル -- sql-bench -- ベンチマーク用ファイル -- support-files -- my.cnf のサンプルファイルなど -- tests -- テスト用プログラムのサンプルファイル /etc ( 次ページへ続く ) 24 平成 18 年 9 月 5 日

ディレクトリ構成 (1/2) tar.gz 形式でインストールインストールしたした場合場合のデフォルト ( 前ページからの続き : /usr/local/mysql/ 以下 ) `-- data -- データディレクトリ (datadir) -- <host_name>.err -- エラーログ -- <host_name>.log -- クエリログ -- <host_name>-slow.log スロークエリログ -- <host_name>-bin.nnnnnn -- バイナリログ -- <host_name>-bin.index -- バイナリログインデックスファイル -- ibdata1 -- InnoDBデータファイル -- ib_logfile0 -- InnoDBログファイル -- ib_logfile1 InnoDBログファイル -- mydb -- ユーザデータベース mydb -- mytable01.frm -- テーブル定義ファイル -- mytable01.myd -- MyISAMデータファイル -- mytable01.myi -- MyISAMインデックスファイル `-- mytable02.frm -- テーブル定義ファイル -- mysql -- 権限データベース `-- test -- テスト用データベース 25 平成 18 年 9 月 5 日

目次 MySQLのインストール ユーザデータベースの作成 MySQLのディレクトリ構成 サーバ変数と my.cnf( オプション設定ファイル ) ログファイル MySQLのステータス確認方法 MySQLでの日本語処理 MySQLに関する情報源 付録 バイナリログ MySQL サーバの起動スクリプト 26 平成 18 年 9 月 5 日

サーバ変数 MySQL サーバに組み込みのパラメータで MySQL サーバの動作を決める MySQL がサポートしているサーバ変数の一覧表示法 : 方法 稼働中の MySQL サーバに設定されているサーバ変数参照法 : 方法 (1) 方法 (2) # bin/mysqld - verbose --help mysql> SHOW VARIABLES; # bin/mysqladmin variables 27 平成 18 年 9 月 5 日

サーバ変数の設定方法 MySQL サーバ起動時にオプションを設定することでサーバ変数の値を変更できる オプションの設定方法 MySQL サーバ起動時 1. コマンドラインオプション #./bin/mysqld_safe -user=mysql --datadir=/var/lib/mysql/data & 2. オプション設定ファイル (my.cnf ファイル ) MySQL サーバ稼働中 3. SET 文推奨 mysql> SET sort_buffer_size=10000; 推奨の設定方法 28 平成 18 年 9 月 5 日

オプション設定ファイル : my.cnf 推奨のオプションオプション設定方法 オプションをファイルで一元管理するので 設定項目の確認や変更が容易 コマンドラインで設定すると コマンドラインが煩雑になり 設定ミスを起こしやすい 設定方法 一般的な記述規則 具体例 [< グループ >] < オプション名 > < オプション名 >=< オプションの値 > # コメント # コメント ; コメント [mysqld] port=3306 socket=/tmp/mysql.sock log-error max_connections=300 [client] port=3306 socket=/tmp/mysql.sock [mysql] connect_timeout=2 29 平成 18 年 9 月 5 日

オプション設定ファイルが読み込まれる順序 my.cnfファイルは複数の場所に置くことができ それらが読み込まれる順番は 次の通りです 通常は /etc/my.cnf でのみ設定設定するする方法方法が一般的一般的ですです 順番 1 2 3 4 ファイル /etc/my.cnf $MYSQL_HOME/my.cnf --defaults-extra-file=path ~/.my.cnf 目的グローバルオプションサーバ (mysqld) 毎のオプション任意のファイルを読み込みたい場合ユーザー毎のオプション tar.gz 形式でインストールした直後は いずれのオプション設定ファイルも存在しません RPM 形式でインストールした直後は /etc/my.cnf のみ存在します 複数のオプション設定ファイルで同じオプションが設定されていた場合には 最後に設定されたオプションが有効になります --userオプションのみ例外で これは最初の設定が有効になります ご注意 : その他のユーザに書き込み権限が付与されているオプション設定ファイルは (ex. rw-rw-rw-) 無視されて読み込まれません 30 平成 18 年 9 月 5 日

代表的なサーバ変数 分類 サーバ変数 意味 デフォルト値 ディレクトリ関係 basedir MySQL 本体をインストールしたディレクトリ N/A datadir データベース ( ファイル ) を格納するディレクトリ N/A 文字エンコーディングエンコーディング関連 character-set-server MySQL サーバ全体のデフォルト文字エンコーディング latain1 character-set-database データベースを作成する際のデフォルト文字エンコーディング N/A ログ関連 log-error[=name] エラーログを有効にする 無効 (datadir/host_name.err) log[=name] クエリログを有効にする 無効 (datadir/host_name.log) log-slow-queries[=name] スロークエリログを有効にする 無効 (datadir/host_nameslow.log) long_query_time スロークエリログに記録されるまでの秒数 10( 秒 ) log-bin[=name] バイナリログを有効にする 無効 (host_name-bin) ストレージエンジン関連 default-storage-engine CREATE TABLE した際のデフォルトのストレージエンジン MyISAM パフォーマンス関連 max_connections クライアントからの最大同時接続数 100 query_cache_size クエリキャッシュのメモリサイズ 0 innodb_buffer_pool_size (InnoDB) データをインデックス用のキャッシュサイズ 8388608 innodb_log_buffer_size (InnoDB) ログファイルに書き出すまでログを貯めておくためのバッファサイズ 1048576 table-cache 1 つのスレッドがオープン可能なテーブル数 64 sort_buffer_size ソート用のバッファサイズ 2097144 join_buffer_size フルジョイン用のバッファサイズ 131072 key_buffer_size (MyISAM) インデックスブロック用バッファサイズ 8388600 read_buffer_size シーケンシャルスキャン用のバッファサイズ 131072 read_rnd_buffer_size ソートされたレコードを読み出す際に割り当てられるバッファのサイズ 262144 31 平成 18 年 9 月 5 日

オプション設定ファイルのサンプルファイル MySQL をインストールすると サンプルのオプション設定ファイルがインストールされます インストールされるディレクトリ tar.gz 形式 : /usr/local/mysql/support-files/ RPM 形式 : /usr/share/mysql/ サンプルファイル一覧 サンプルファイル my-small.cnf my-medium.cnf my-large.cnf my-huge.cnf my-innodb-heavy-4g.cnf 想定している MySQLを稼動稼動するするシステム メモリ 64MB 以下程度のシステム MySQL サーバ以外のプロセスを含めてメモリ 128MB 程度の規模 メモリ 512MB 程度の規模 メモリ 1-2GB 程度の規模 メモリ 4GB 程度の規模で InnoDB ストレージエンジンを稼動させる場合 32 平成 18 年 9 月 5 日

目次 MySQLのインストール ユーザデータベースの作成 MySQLのディレクトリ構成 サーバ変数とmy.cnf( オプション設定ファイル ) ログファイル MySQLのステータス確認方法 MySQLでの日本語処理 MySQLに関する情報源 付録 バイナリログ MySQL サーバの起動スクリプト 33 平成 18 年 9 月 5 日

MySQL サーバが起動しなかったら?? MySQL サーバのシステム ( 権限 ) データベースを削除してから MySQL サーバを起動してみます [ システム ( 権限 ) データベースの削除 ] # cd /usr/local/mysql/data # mv mysql /tmp MySQL サーバの起動起動に失敗!! [MySQL サーバの起動 ] # cd.. # bin/mysqld_safe --user=mysql & [1] 16363 # Starting mysqld daemon with databases from /usr/local/mysql/data STOPPING server from pid file /usr/local/mysql/data/hpjsdabj.pid 060809 11:26:24 mysqld ended # ps -ef grep mysqld grep -v mysqld # MySQL のログで原因を調査します 34 平成 18 年 9 月 5 日

MySQL のログファイル概要 ログファイル エラーログクエリログスロークエリログ 用途 MySQL プロセスの動作記録 診断 MySQL サーバが処理する SQL 文の記録 診断 更新系 SQL 文の記録 バイナリログ バックアップデータのリカバリ レプリケーション 35 平成 18 年 9 月 5 日

エラーログ (1/2) 記録されるもの MySQL サーバ (mysqld プロセス ) の起動起動 停止停止の際の情報情報を記録 起動 停止中停止中にエラーエラーがあればがあれば記録記録される その他 ネットワークインターフェース障害 ストレージエンジン初期化失敗情報 レプリケーション障害情報... など 用途 MySQL サーバやストレージエンジンの起動停止時やレプリケーションの障害時の原因調査目的として エラーログファイルの有効化有効化と実際実際のファイル 次のサーバ変数にてエラーログの有効化とファイル名を指定 log-error[=file_name] [=file_name] を設定しない場合 : データディレクトリ /<host_name>.err 今回の例では /usr/local/mysql/data/<host_name>.err log-error[=file_name] を設定せずに MySQL サーバを mysqld_safe コマンドで起動した場合 データディレクトリ /<host_name>.err にエラーログが記 36 平成 18 年 9 月 5 日 録されます

エラーログ (2/2) MySQL サーバが起動しなかった時のエラーログの内容はどうなっていたか? MySQL サーバ [MySQL サーバの起動 ] # bin/mysqld_safe --user=mysql & [1] 16363 の起動起動に失敗!! # Starting mysqld daemon with databases from /usr/local/mysql/data STOPPING server from pid file /usr/local/mysql/data/hpjsdabj.pid 060809 11:26:24 mysqld ended エラーログの中身 # cat /usr/local/mysql/data/<host_name>.err 060809 11:26:23 mysqld started 060809 11:26:24 InnoDB: Started; log sequence number 0 43655 060809 11:26:24 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist 060809 11:26:24 mysqld ended mysql.host テーブルが開けないことがけないことがエラーの原因原因となっていることがとなっていることが分かるかる ( 前段階で mysql データベースを故意故意に削除していたため ) 37 平成 18 年 9 月 5 日

クエリログ 記録されるもの クライアントからMySQLサーバに対してリクエストがあったすべての SQL 文が記録される 用途 SQL 文処理の成功 失敗に関わらず サーバがリクエストを受信した順番で記録される 開発中 障害対応時において サーバに対して実際どのような SQL 文がリクエストされているかを知るため クエリログの内容は大量になるため 開発中や障害対応時のみ取得するようにした方がよい つまり 正常運用中は無効にしておく クエリログファイルの有効化有効化と実際実際のファイル 次のサーバ変数にてクエリログの有効化とファイル名を指定 log[=file_name] [=file_name] を設定しない場合 : データディレクトリ /<host_name>.log 今回の例では /usr/local/mysql/data/<host_name>.log 38 平成 18 年 9 月 5 日

スロークエリログ 記録されるもの クライアントからMySQLサーバに対してリクエストがあったSQL 文のうち 一定時間以上経過してして処理処理された SQL 文と付随付随するする情報 用途 デフォルトは 10 秒 次のサーバ変数によりスロークエリに記録されるまでの時間を変更可能 long_query_time( 秒 ) 長時間かかる SQL 文の発見と MySQL サーバを含めたチューニングや SQL 文の最適化検討に関する判断材料として スロークエリログファイルの有効化有効化と実際実際のファイル 次のサーバ変数にてスロークエリログの有効化とファイル名を指定 log-slow-queries[=file_name] [=file_name] を設定しない場合 : データディレクトリ /<host_name>-slow.log 今回の例では /usr/local/mysql/data/<host_name>-slow.log 39 平成 18 年 9 月 5 日

目次 MySQLのインストール ユーザデータベースの作成 MySQLのディレクトリ構成 サーバ変数とmy.cnf( オプション設定ファイル ) ログファイル MySQL のステータス確認方法 MySQLでの日本語処理 MySQLに関する情報源 付録 バイナリログ MySQL サーバの起動スクリプト 40 平成 18 年 9 月 5 日

MySQL サーバのステータス確認方法 確認手段 mysql コマンドランツール mysqladmin ツール mysqlshow ツール 確認項目 設定されているサーバ変数 現在稼動している MySQL サーバの状態 MySQL サーバに接続しているクライアントに関する情報 データベースオブジェクトの情報や定義 データベース テーブル インデックス InnoDB に関する情報 41 平成 18 年 9 月 5 日

設定されているサーバ変数の確認方法 ( 方法 1) mysql コマンドラインツール mysql> SHOW VARIABLES; +---------------------------------+-----------+ Variable_name Value +---------------------------------+-----------+ auto_increment_increment 1... wait_timeout 28800 +---------------------------------+-----------+ 219 rows in set (0.00 sec) < 参考 > mysql> SHOW GLOBAL VARIABLES とGLOBALを付けて実行すると 現在のセッションに対してではなく 新規セッションセッションに対するするサーバ変数とその値が表示されます mysql> SHOW VARIABLES LIKE '%size%'; ( 方法 2) mysqladmin ツール 同じ出力出力が返されます # bin/mysqladmin variables # bin/mysqladmin variables grep size 42 平成 18 年 9 月 5 日

現在稼動している MySQL サーバの状態 ( 方法 1) mysql コマンドラインツール mysql> SHOW STATUS; +---------------------------------+-----------+ Variable_name Value +---------------------------------+-----------+ Aborted_clients 0... Uptime 17492 +---------------------------------+-----------+ 245 rows in set (0.00 sec) mysql> SHOW STATUS LIKE '%cache%'; ( 方法 2) mysqladmin ツール 同じ出力出力が返されます # bin/mysqladmin extended-status # bin/mysqladmin extended-status grep cache サマリ情報情報のみ # bin/mysqlamdin status Uptime: 18255 Threads: 2 Questions: 83 Slow queries: 0 Opens: 13 Flush tables: 1 Open tables: 7 Queries per second avg: 0.005 43 平成 18 年 9 月 5 日

MySQL サーバに接続しているクライアント に関する情報 ( 方法 1) mysql コマンドラインツール mysql> SHOW PROCESSLIST; +----+--------+-----------------+------+---------+------+-------+------------------+ Id User Host db Command Time State Info +----+--------+-----------------+------+---------+------+-------+------------------+ 13 root localhost NULL Query 0 NULL SHOW PROCESSLIST 24 dbuser localhost:34045 mydb Sleep 2 NULL 25 dbuser localhost:34046 mydb Sleep 2 NULL 26 dbuser localhost:34047 mydb Sleep 2 NULL +----+--------+-----------------+------+---------+------+-------+------------------+ 4 rows in set (0.00 sec) mysql> SHOW STATUS like 'threads_connected'; +-------------------+-------+ Variable_name Value +-------------------+-------+ Threads_connected 4 +-------------------+-------+ 1 row in set (0.00 sec) 44 平成 18 年 9 月 5 日

データベースに関する情報 現在稼働中の MySQL サーバに登録されているデータベースをリストする mysql> SHOW DATABASES; +--------------------+ Database +--------------------+ information_schema mydb mysql test +--------------------+ 4 rows in set (0.00 sec) あるデータベースがどのように作成されたかを確認する mysql> SHOW CREATE DATABASE mydb; +----------+----------------------------------------------------------------+ Database Create Database +----------+----------------------------------------------------------------+ mydb CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET cp932 */ +----------+----------------------------------------------------------------+ 1 row in set (0.00 sec) 45 平成 18 年 9 月 5 日

テーブルに関する情報 (1/3) データベースに存在するテーブル名をリストする mysql> SHOW TABLES FROM mydb; +----------------+ Tables_in_mydb +----------------+ mytable01 +----------------+ 1 row in set (0.00 sec) もしくは mysql> use mydb mysql> SHOW TABLES; あるテーブルの構造を確認する mysql> DESC mytable01; +------------+----------+------+-----+---------+----------------+ Field Type Null Key Default Extra +------------+----------+------+-----+---------+----------------+ id int(11) NO PRI NULL auto_increment last_name char(30) NO first_name char(30) NO +------------+----------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) 46 平成 18 年 9 月 5 日

テーブルに関する情報 (2/3) あるテーブルがどのように作成されたかを確認する mysql> SHOW CREATE TABLE mytable01 G *************************** 1. row *************************** Table: mytable01 Create Table: CREATE TABLE `mytable01` ( `id` int(11) NOT NULL auto_increment, `last_name` char(30) NOT NULL, `first_name` char(30) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=cp932 1 row in set (0.00 sec) 47 平成 18 年 9 月 5 日

テーブルに関する情報 (3/3) データベース内のすべてのすべてのテーブル ( 及びビュー ) に関する更に詳細な情報を表示する mysql> SHOW TABLE STATUS G *************************** 1. row *************************** Name: mytable01 Engine: MyISAM Version: 10 Row_format: Fixed Rows: 3 Avg_row_length: 125 Data_length: 375 Max_data_length: 35184372088831999 Index_length: 2048 Data_free: 0 Auto_increment: 4 Create_time: 2006-08-09 09:59:26 Update_time: 2006-08-09 10:00:24 Check_time: NULL Collation: cp932_japanese_ci Checksum: NULL Create_options: Comment: 1 row in set (0.01 sec) 48 平成 18 年 9 月 5 日

インデックスに関する情報 あるテーブルに張られているインデックスの情報を表示する mysql> SHOW INDEX FROM mytable01 G *************************** 1. row *************************** Table: mytable01 Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 3 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: 1 row in set (0.00 sec) 49 平成 18 年 9 月 5 日

InnoDB に関する情報 InnoDB ストレージエンジンに関する詳細な情報を表示する mysql> SHOW ENGINE INNODB STATUS G *************************** 1. row *************************** Status: ===================================== 060815 16:20:33 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 56 seconds ---------- SEMAPHORES ----------... ------------ TRANSACTIONS ------------... -------- FILE I/O --------... ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX -------------------------------------... --- LOG ---... ---------------------- BUFFER POOL AND MEMORY ----------------------... -------------- ROW OPERATIONS --------------... ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set (0.00 sec) 50 平成 18 年 9 月 5 日

目次 MySQLのインストール ユーザデータベースの作成 MySQLのディレクトリ構成 サーバ変数とmy.cnf( オプション設定ファイル ) ログファイル MySQLのステータス確認方法 MySQL での日本語処理 MySQLに関する情報源 付録 バイナリログ MySQL サーバの起動スクリプト 51 平成 18 年 9 月 5 日

MySQL での日本語処理 そもそも MySQL で日本語が使えるの? もちろん使えます 正しい設定が必要です MySQL 4.1 以降では サーバ側 クライアント側の両側での設定が必要 デフォルト設定は latin1 --> 日本語非対応なので注意 MySQL 4.0 以前と MySQL 4.1 以降では実装や設定方法が異なる 始めから MySQL 4.1 以上を使用する場合は特に問題なし MySQL 4.0 以前から MySQL 4.1 以降へアップグレードする場合には注意が必要 52 平成 18 年 9 月 5 日

MySQL サーバで使用可能な 文字エンコーディング 文字エンコーディング cp932 概要 IANA 登録名 Windows-31Jに相当 MySQL5.0 以降 MySQL4.1 MySQL 4.0 以前 文字集合はsjisに加え NEC 特殊文字 (4.1.12 以降 ) NEC 選定 IBM 拡張文字 IBM 拡張文字 日本語EUC 系sjis IANA 登録名 Shift_JISに相当 eucjpms eucjp-msに相当(tog/jvc ( オープン グ 文字集合はASCII 文字 ひらがな カタカ ナ JIS 第一 第二水準 ループ / 日本ベンダ協議会 ) CDE/Motif 技術検討 WG が定めたコードセット名 ) 文字集合はujisに加え NEC 特殊文字 NEC 選定 IBM 拡張文字 IBM 拡張文字 ujis utf8 IANA 登録名 EUC-JP に相当 文字集合はsjisに加え 補助漢字 UTF-8 Unicode 多国言語を扱いたい場合に使用 Unicode 系 53 平成 18 年 9 月 5 日

文字エンコーディングの設定方法 (MySQL4.1 以降 ) サーバ側 方法 (1) my.cnf [mysqld] character_set_server=cp932 方法 (2) コマンドラインオプション # cd /usr/local/mysql # bin/mysqld_safe --user=mysql --character_set_server=cp932 & 上記いずれかのいずれかの設定設定で MySQL サーバを起動起動するとすると MySQL サーバのインスタンス (mysqld) のデフォルトデフォルトの文字文字エンコーディングエンコーディングが設定設定されます ( ここでは cp932) つまり CREATE DATABASEを実行実行してして作成作成したしたデータベースデータベースのデフォルトの文字文字エンコーディングエンコーディングが cp932 になります 54 平成 18 年 9 月 5 日

文字エンコーディングの設定方法 (MySQL4.1 以降 ) クライアント側 : mysql ツール 方法 (1) my.cnf [mysql] default-character-set=cp932 方法 (2) コマンドラインオプション # cd /usr/local/mysql # bin/mysql --user=mysql -p --default-character-set=cp932 55 平成 18 年 9 月 5 日

文字エンコーディングの設定方法 (MySQL4.1 以降 ) クライアント側 :Java/JDBC Connector/J(MySQL 用 JDBC ドライバ ) を使用して MySQL サーバに接続する際に JDBC の次のプロパティに Java 側の文字エンコーディング名を設定する characterencoding=<javaの文字文字エンコーディングエンコーディング名 > Java と MySQL サーバの文字エンコーディング名の対応 Java Windows-31J もしくは MP932 Shift_JIS もしくは SJIS EUC_JP_Solaris ( 注 1) EUC_JP UTF-8 MySQL サーバ cp932 sjis eucjpms ujis utf8 ( 注 1) eucjpms と完全互換ではありません " ", " ", "-", " ", " ", " ", " " に互換性がありません 56 平成 18 年 9 月 5 日

文字エンコーディングの設定方法 (MySQL4.1 以降 ) クライアント側 :C クライアントライブラリ PHP 等のアプリケーションが利用する C クライアントライブラリの文字エンコーディングは "latin1" でコンパイルされてるため そのまま利用すると文字化けが発生します 対策は次の 3 つのうちいずれか (1) my.cnf : skip-character-set-client-handshake サーバ変数 [mysqld] character_set_server=cp932 skip-character-set-client-handshake (2) アプリケーションが MySQL サーバに接続後に SET NAME を実行 SET NAME cp932 (3) ソースからクライアントライブラリをコンパイル $./configure --with-charset=cp932 57 平成 18 年 9 月 5 日

MySQL の日本語処理に関する 推奨及び注意事項 MySQL 4.0 以前と MySQL 4.1 以降では日本語処理に関する実装や設定方法が異なるため 新規に MySQL を導入する場合は 極力 MySQL 4.1 以降 特に 5.0 以降をご利用ください MySQL サーバ側とクライアント側では 極力同じ文字エンコーディングを使用した方がトラブルが発生し難くなります いわゆるシフト JIS 系を使用する場合は cp932 をお使いください ("( 株 )" などの機種依存文字が使用可能なため ) データベースメタデータ ( データベース名 テーブル名 ) は MySQL 5.1 が正式にリリースされるまで日本語を使用しないでください MySQL の eucjpms と Java の EUC_JP_Solaris は完全互換ではありません 58 平成 18 年 9 月 5 日

目次 MySQLのインストール ユーザデータベースの作成 MySQLのディレクトリ構成 サーバ変数とmy.cnf( オプション設定ファイル ) ログファイル MySQLのステータス確認方法 MySQLでの日本語処理 MySQL に関する情報源 付録 バイナリログ MySQL サーバの起動スクリプト 59 平成 18 年 9 月 5 日

MySQL の情報 MySQL 社提供のオンラインマニュアル MySQL 製品ドキュメントのトップページ 60 平成 18 年 9 月 5 日 http://dev.mysql.com/doc/ MySQL サーバのマニュアル [ 日本語 v4.1] http://dev.mysql.com/doc/refman/4.1/ja/index.html [ 英語 v5.0] http://dev.mysql.com/doc/refman/5.0/en/index.html バグ検索方法 Google MySQL 社のバグトラッキング MySQL Bugs : http://bugs.mysql.com/ MySQL 社のフォーラム MySQL Forums : http://forums.mysql.com/ 日本 MySQL ユーザ会 http://www.mysql.gr.jp/ メーリングリストやメーリングリストログなど有り

付録 バイナリログ MySQL サーバの起動スクリプト 61 平成 18 年 9 月 5 日

バイナリログ (1/2) 1 2 1 クライアントから INSERT INTO atable VALUES(1, name1 ) MySQL サーバ name1 データ : 2 更新 SQL 文 更新 SQL 文 バイナリログ INSERT INTO atable VALUES(1, name1 ) 記録されるもの MySQLサーバに対して行った更新系 SQL 文だけだけをバイナリ形式で記録するファイル データを修正しないSQL 文は記録しない バイナリログファイルの切替 MySQLサーバの起動時 特定の大きさになると次のファイルに切替 FLUSH LOGS コマンドによる切替 トランザクション単位で記録される 用途 データベース障害復旧時のロールフォーワードリカバリ 増分バックアップとして MySQL レプリケーション マスターのデータをスレーブに複製 62 平成 18 年 9 月 5 日

バイナリログ (2/2) バイナリログの有効化有効化と実際実際のファイル 次のシステム変数にて バイナリログの有効化とファイル名や設定 log-log[=file_name] [=file_name] を指定しない場合 : データディレクトリ /<host_name>bin.nnnnnn nnnnnnnn は整数 (ex. 000001, 000002) 今回の例では /usr/local/mysql/data/<host_name>.log その他 バイナリログを有効にすると バイナリログインデックスも作成される 現在使用されているバイナリログのリストを保持 バイナリログインデックスのファイル名 デフォルト : < バイナリログのファイル名 ( 整数無し )>.index log-bin-index=file_name サーバ変数で変更可能 バイナリログはバイナリ形式のファイルなため mysqlbinlog コマンドでテキスト形式に変換する 63 平成 18 年 9 月 5 日

MySQL サーバ用起動スクリプト OS 起動時に MySQL サーバを起動させには? 起動スクリプトのサンプルが用意されている /usr/local/mysql/support-files/mysql.server これを /etc/rc.d/init.d/mysql などにコピー / カスタマイズして使用する 64 平成 18 年 9 月 5 日

65 平成 18 年 9 月 5 日