Contents
- 1 MySQLについて
- 2 環境
- 3 サーバの構築
- 4 MySQLの設定
- 5 クイックインストール
- 6 クエリー
- 7 オプション設定
- 8 ツール
- 9 ダウンロードツール
- 10 定常運用
- 11 非定常運用
- 12 障害対応
- 13 パフォーマンスチューニング
MySQLについて
用語
照合順序
照合順序とは文字列の比較を行う際に、どの文字を一致するとみなすかのルールである。
例えば、「か」、「が」、「カ」、「ｶ」をそれぞれ一致するとしたり、しないとしたり出来る。
ページ
InnoDBが扱うデータの単位。
デフォルトでは16KBでコンパイル前に変更することで8KB から64KB の範囲の値に設定する事ができる。
変更する場合、univ.i ソース ファイル内で UNIV_PAGE_SIZE と UNIV_PAGE_SIZE_SHIFT の値を更新する。
なお、各ページは圧縮して保持される。
外部キー
テーブルのある列に、別のテーブルの特定の列に含まれる項目しか入力できないようにする制約を課した列のこと
セカンダリインデックス
主キー、ユニークキーでないインデックス。
実レコード順にソートされていないので、インデックスを使用して実レコードに連続してアクセスする場合でもランダムアクセスになってしまう。
ただし、これを最適化するMMR(Multi-Range Read)最適化というものがある。
ストレージエンジン
ストレージエンジンとはデータを保存する際の保存形式のことである。
ストレージエンジンの選択によってファイルのフォーマットや、ディスク上に保存せずにメモリ上にのみ保存したりなどが選択できる。
InnoDB
- 特徴
- トランザクションをサポートする
- 行レベルのロックをサポートする
- ただし、範囲を指定して参照する場合、実際に参照する行の次の行もロックされる（ネクストキーロック）
- ファイル
テーブル定義ファイルをfrmファイルに保存する
innodb_file_per_table変数を有効にした場合、データ・インデックスは各テーブルごとに.ibdファイルに保存される。
そうでない場合は、まとめてibdataファイルに保存される。
いずれの場合でも一部のログ等はibdataファイルに保存される。
トランザクションログはib_logfileファイルに保存される。
- ページ
データを固定長サイズのページ単位で扱う。
ただし、ページは圧縮されるので可変長となる。
データはページ格納時にページサイズ以下に分割される。
MyISAM
- 特徴
- 全文インデックスをサポートする
- 圧縮をサポートする
- 各テーブルをデータファイル(.MYD)とインデックスファイル(.MYI)、テーブル定義ファイル(.frm)に保存する
- テーブルデータを１ファイルで保存する関係上、OSが作成可能なファイルサイズ上限にデータサイズが制限される
- キャッシュ機能がない。ただし、OSレベルではキャッシュされる。
- ロック
- テーブルレベルのみ行える（lock tablesコマンドで明示的にロックする必要がある）
- 読み取り時は共有ロックを取得し、書き込み時は排他ロックを取得する
- SQLに優先度オプションを記述することで、読み取りを優先させることができる
FEDERATED
ストレージエンジンの一種で、リモートサーバのテーブルへの参照を作成し、ローカルにあるかのように扱うことができる。
参考:https://dev.mysql.com/doc/refman/5.1/ja/federated-storage-engine.html
- 特徴
- クエリキャッシュが使用できない
- トランザクションをサポートしない
- 単純な行取得は問題ないが、GROUP BYやJOINでは全ての行を取得してくるので、効率が悪い
- テーブル定義ファイルをfrmファイルに保存する
- 使用方法
デフォルトでは使用できないので、設定ファイルに以下を記述する。[mysqld]
federated
- 作成方法
通常のcreate tableステートメントにengineにfederatedを指定し、connectionオプションを使用する。
create table <テーブル名> (<テーブル内容>) engine=federated connection='mysql//<ユーザ名>:<パスワード>@<ホスト名>[:<ポート番号>]/<DB名>/<テーブル名>';
MEMORY
- 特徴
- テーブル定義ファイルをfrmファイルに保存する
- BLOB、TEXT型が使用できない
- インデックスのデフォルトがHashであり、Btreeを使用する場合、明示的に指定が必要
BLACKHOLE
データを保存せずに破棄する。
データベースを使用する製品上、テーブル定義されているが、実際は使用しなかったり、キャッシュデータで保存の必要がなかったりする場合に使用できる。
ARCHIVE
CSV
CSV形式でデータを保存する。
MySQLの種類
MySQL公式ページではソースコードやパッケージが提供されている。
ダウンロードページはこちら。
何種類かのソフトウェアが用意されているので、必要なものをダウンロードする。
2013/9/13時点では以下のものがある。
なお、今回は、「MySQL Community Server」の「MySQL Server」と「Client Utilities」を利用する。
MySQL Community Server
Linux上でMySQLサーバを構築するためのソースコード、パッケージ。
MySQLサーバを使用するには最低限こちらが必要。
PlatformでOSを選択するが、Cent OSにインストールする場合、
「Oracle & Red Hat Linux」と「Linux – Generic」のどちらでも使用可能である。
通常は「Oracle & Red Hat Linux」を選択しておくとよい。
「MySQL Community Server 5.6.15」時点では以下が提供されている。
※全てx86, 64-bitのもの
- RPM Bundle (MySQL-5.6.15-1.el6.x86_64.rpm-bundle.tar)
以下の全てのパッケージを含む圧縮ファイル - Development Libraries (MySQL-devel-5.6.15-1.el6.x86_64.rpm)
MySQLを他のプログラムを開発してそこから利用する際のライブラリ。
ヘッダファイルなどが含まれる。 - Embedded (MySQL-embedded-5.6.15-1.el6.x86_64.rpm)
組み込みMySQLサーバを使用する場合のライブラリ - MySQL Server (MySQL-server-5.6.15-1.el6.x86_64.rpm)
サーバ本体 - Shared components (MySQL-shared-5.6.15-1.el6.x86_64.rpm)
共有ライブラリパッケージ（「libmysqlclient.so」）。
動的にリンクされる共有ライブラリを別のプログラムから利用する時に必要となる。 - Compatibility Libraries (MySQL-shared-compat-5.6.15-1.el6.x86_64.rpm)
前のバージョンのライブラリ「libmysqlclient.so」を使用する場合に
Shared componentsの代わりに使用する。 - Client Utilities (MySQL-client-5.6.15-1.el6.x86_64.rpm)
mysqlコマンドなどのクライアントツール - Test Suite (MySQL-test-5.6.15-1.el6.x86_64.rpm)
テストツール
MySQL Cluster
MySQL Workbench
MySQL Utilities
MySQL Proxy
MySQL Connectors
MySQL on Windows (Installer & Tools)
Windows上でMySQLサーバを構築するためのインストーラ。
MySQL Cluster CGE
MySQL Enterprise Edition
業務用途にサポート付ライセンスのもの。
オープンソース版を再開発したものなので、オープンソースライセンスを引き継がず、ソースコードは非公開。
環境
- OS
CentOS release 6.4 (64bit) - MySQL Version
5.6.15
サーバの構築
OSの設定
NTP
時刻調整のためにNTPをインストールする。
- インストール
yum install ntp
- log
[root@mysql1 ~]# yum install ntp Loaded plugins: fastestmirror base | 3.7 kB 00:00 base/primary_db | 3.5 MB 00:00 extras | 3.4 kB 00:00 extras/primary_db | 18 kB 00:00 updates | 3.4 kB 00:00 updates/primary_db | 765 kB 00:01 Setting up Install Process Resolving Dependencies --> Running transaction check ---> Package ntp.i686 0:4.2.6p5-1.el6.centos will be installed --> Processing Dependency: ntpdate = 4.2.6p5-1.el6.centos for package: ntp-4.2.6p5-1.el6.centos.i686 --> Processing Dependency: libcrypto.so.10(libcrypto.so.10) for package: ntp-4.2.6p5-1.el6.centos.i686 --> Processing Dependency: libcrypto.so.10(OPENSSL_1.0.1) for package: ntp-4.2.6p5-1.el6.centos.i686 --> Running transaction check ---> Package ntpdate.i686 0:4.2.6p5-1.el6.centos will be installed ---> Package openssl.i686 0:1.0.0-27.el6 will be updated ---> Package openssl.i686 0:1.0.1e-16.el6_5.1 will be an update --> Processing Dependency: make for package: openssl-1.0.1e-16.el6_5.1.i686 --> Running transaction check ---> Package make.i686 1:3.81-20.el6 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: ntp i686 4.2.6p5-1.el6.centos base 586 k Installing for dependencies: make i686 1:3.81-20.el6 base 386 k ntpdate i686 4.2.6p5-1.el6.centos base 74 k Updating for dependencies: openssl i686 1.0.1e-16.el6_5.1 updates 1.5 M Transaction Summary ================================================================================ Install 3 Package(s) Upgrade 1 Package(s) Total download size: 2.5 M Is this ok [y/N]: y Downloading Packages: (1/4): make-3.81-20.el6.i686.rpm | 386 kB 00:00 (2/4): ntp-4.2.6p5-1.el6.centos.i686.rpm | 586 kB 00:00 (3/4): ntpdate-4.2.6p5-1.el6.centos.i686.rpm | 74 kB 00:00 (4/4): openssl-1.0.1e-16.el6_5.1.i686.rpm | 1.5 MB 00:04 -------------------------------------------------------------------------------- Total 520 kB/s | 2.5 MB 00:04 warning: rpmts_HdrFromFdno: Header V3 RSA/SHA1 Signature, key ID c105b9de: NOKEY Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6 Importing GPG key 0xC105B9DE: Userid : CentOS-6 Key (CentOS 6 Official Signing Key) <centos-6-key@centos.org> Package: centos-release-6-4.el6.centos.10.i686 (@anaconda-CentOS-201303020136.i386/6.4) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6 Is this ok [y/N]: y Running rpm_check_debug Running Transaction Test Transaction Test Succeeded Running Transaction Installing : 1:make-3.81-20.el6.i686 1/5 Updating : openssl-1.0.1e-16.el6_5.1.i686 2/5 Installing : ntpdate-4.2.6p5-1.el6.centos.i686 3/5 Installing : ntp-4.2.6p5-1.el6.centos.i686 4/5 Cleanup : openssl-1.0.0-27.el6.i686 5/5 Verifying : openssl-1.0.1e-16.el6_5.1.i686 1/5 Verifying : ntp-4.2.6p5-1.el6.centos.i686 2/5 Verifying : 1:make-3.81-20.el6.i686 3/5 Verifying : ntpdate-4.2.6p5-1.el6.centos.i686 4/5 Verifying : openssl-1.0.0-27.el6.i686 5/5 Installed: ntp.i686 0:4.2.6p5-1.el6.centos Dependency Installed: make.i686 1:3.81-20.el6 ntpdate.i686 0:4.2.6p5-1.el6.centos Dependency Updated: openssl.i686 0:1.0.1e-16.el6_5.1 Complete!
- NTPの設定
ここでは仮に公開ntpサーバを利用する設定を以下のように行う。
独立行政法人情報通信研究機構 (NICT)のNTPサーバ：ntp.nict.jp と
インターネットマルチフィードのNTPサーバ：ntp.jst.mfeed.ad.jpの２つを設定する。
vi /etc/ntp.conf
- 変更内容
初期状態の「*.centos.pool.ntp.org」をコメントアウトし、
#server 0.centos.pool.ntp.org #server 1.centos.pool.ntp.org #server 2.centos.pool.ntp.org server ntp.nict.jp minpoll 6 maxpoll 6 server ntp.jst.mfeed.ad.jp minpoll 6 maxpoll 6 tinker panic 0
ここで、「minpoll 6 maxpoll 6」とは同期タイミングの設定で、2^6秒、つまり64秒ごとに同期を行う。
更にデフォルトではNTPサーバとクライアントの時刻差が1000秒以上あると正常に同期できないので、
その差を無視するよう設定する。
常に起動し続ける本番環境であれば、これらは不要である。
- ntpサービス起動
ntpサービスは停止しているので、起動させる。service ntpd start
更にntpdサービスをOS起動時に起動するように設定する。
- 現在の設定
[root@mysql1 ~]# chkconfig --list ntpd ntpd 0:off 1:off 2:off 3:off 4:off 5:off 6:off
- Level3で有効化
chkconfig --level 3 ntpd on
- 結果
[root@mysql1 ~]# chkconfig --list ntpd ntpd 0:off 1:off 2:off 3:on 4:off 5:off 6:off
- 時刻確認
dateコマンドで現在時刻が正しく同期されているかを確認する。
サービス
不要なサービスがあれば終了し、必要なサービスがあれば導入する。
追加？
irqbalance
rawdevices
I/Oスケジューラ
I/Oスケジューラとは
データベースにはdeadlineが良いので変更する。
RAWデバイス
MySQLのインストール
バイナリパッケージからインストール
パッケージからインストール（mysql-libsを上書き）
パッケージからインストールすることで、簡単にインストールが行える。
Cent OSには標準で「mysql-libs.x86_64」が入っているが、これが存在すると競合が発生し、MySQL-serverをパッケージからインストールできない。
mysql-libs.x86_64はcronなどで使用しているため削除できない。
そのため、強制的に「mysql-libs」を上書きすることでインストールを行う。
- MySQL-server-5.6.15-1.el6.x86_64.rpmとMySQL-client-5.6.15-1.el6.x86_64.rpmをダウンロードする（wgetやscpなどを使用する）
※wgetは最小構成Cent OSには入っていないので、インストールする必要がある。（yum install wget）
wget http://dev.mysql.com/get/Downloads/MySQL-5.6/MySQL-server-5.6.15-1.el6.x86_64.rpm wget http://dev.mysql.com/get/Downloads/MySQL-5.6/MySQL-client-5.6.15-1.rhel5.x86_64.rpm
- 「MySQL-server」パッケージをrpmでインストールする
- 入力コマンド
rpm -ivh --force MySQL-server-5.6.15-1.el6.x86_64.rpm
error: Failed dependencies:が出た場合、こちらを行う。
rpm -ivh --force --nodeps MySQL-server-5.6.15-1.el6.x86_64.rpm
- 入力コマンド
- 上記エラーが出た場合は依存関係にあるパッケージをインストールする
- 入力コマンド
yum deplist MySQL-server-5.6.15-1.el6.x86_64.rpm
- 出力
[root@main1 ~]# yum deplist MySQL-server-5.6.15-1.el6.x86_64.rpm Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: ftp.iij.ad.jp * extras: ftp.iij.ad.jp * updates: ftp.iij.ad.jp Finding dependencies: package: MySQL-server.x86_64 5.6.15-1.el6 dependency: libc.so.6(GLIBC_2.3.4)(64bit) provider: glibc.x86_64 2.12-1.107.el6 provider: glibc.x86_64 2.12-1.107.el6_4.4 provider: glibc.x86_64 2.12-1.107.el6_4.2 dependency: grep provider: grep.x86_64 2.6.3-3.el6 dependency: libc.so.6(GLIBC_2.8)(64bit) provider: glibc.x86_64 2.12-1.107.el6 provider: glibc.x86_64 2.12-1.107.el6_4.4 provider: glibc.x86_64 2.12-1.107.el6_4.2 dependency: libstdc++.so.6()(64bit) provider: libstdc++.x86_64 4.4.7-3.el6 dependency: libcrypt.so.1(GLIBC_2.2.5)(64bit) provider: glibc.x86_64 2.12-1.107.el6 provider: glibc.x86_64 2.12-1.107.el6_4.4 provider: glibc.x86_64 2.12-1.107.el6_4.2 dependency: libc.so.6(GLIBC_2.4)(64bit) provider: glibc.x86_64 2.12-1.107.el6 provider: glibc.x86_64 2.12-1.107.el6_4.4 provider: glibc.x86_64 2.12-1.107.el6_4.2 dependency: libc.so.6()(64bit) provider: glibc.x86_64 2.12-1.107.el6 provider: glibc.x86_64 2.12-1.107.el6_4.4 provider: glibc.x86_64 2.12-1.107.el6_4.2 dependency: rtld(GNU_HASH) provider: glibc.i686 2.12-1.107.el6 provider: glibc.x86_64 2.12-1.107.el6 provider: glibc.x86_64 2.12-1.107.el6_4.4 provider: glibc.i686 2.12-1.107.el6_4.2 provider: glibc.x86_64 2.12-1.107.el6_4.2 provider: glibc.i686 2.12-1.107.el6_4.4 dependency: libstdc++.so.6(GLIBCXX_3.4)(64bit) provider: libstdc++.x86_64 4.4.7-3.el6 dependency: net-tools provider: net-tools.x86_64 1.60-110.el6_2 dependency: libpthread.so.0(GLIBC_2.3.2)(64bit) provider: glibc.x86_64 2.12-1.107.el6 provider: glibc.x86_64 2.12-1.107.el6_4.4 provider: glibc.x86_64 2.12-1.107.el6_4.2 dependency: libcrypt.so.1()(64bit) provider: glibc.x86_64 2.12-1.107.el6 provider: glibc.x86_64 2.12-1.107.el6_4.4 provider: glibc.x86_64 2.12-1.107.el6_4.2 dependency: libpthread.so.0()(64bit) provider: glibc.x86_64 2.12-1.107.el6 provider: glibc.x86_64 2.12-1.107.el6_4.4 provider: glibc.x86_64 2.12-1.107.el6_4.2 dependency: libc.so.6(GLIBC_2.2.5)(64bit) provider: glibc.x86_64 2.12-1.107.el6 provider: glibc.x86_64 2.12-1.107.el6_4.4 provider: glibc.x86_64 2.12-1.107.el6_4.2 dependency: libaio.so.1()(64bit) provider: libaio.x86_64 0.3.107-10.el6 dependency: libm.so.6(GLIBC_2.2.5)(64bit) provider: glibc.x86_64 2.12-1.107.el6 provider: glibc.x86_64 2.12-1.107.el6_4.4 provider: glibc.x86_64 2.12-1.107.el6_4.2 dependency: libc.so.6(GLIBC_2.10)(64bit) provider: glibc.x86_64 2.12-1.107.el6 provider: glibc.x86_64 2.12-1.107.el6_4.4 provider: glibc.x86_64 2.12-1.107.el6_4.2 dependency: coreutils provider: coreutils.x86_64 8.4-19.el6 provider: coreutils.x86_64 8.4-19.el6_4.1 provider: coreutils.x86_64 8.4-19.el6_4.2 dependency: libpthread.so.0(GLIBC_2.2.5)(64bit) provider: glibc.x86_64 2.12-1.107.el6 provider: glibc.x86_64 2.12-1.107.el6_4.4 provider: glibc.x86_64 2.12-1.107.el6_4.2 dependency: libstdc++.so.6(GLIBCXX_3.4.11)(64bit) provider: libstdc++.x86_64 4.4.7-3.el6 dependency: libaio.so.1(LIBAIO_0.4)(64bit) provider: libaio.x86_64 0.3.107-10.el6 dependency: libstdc++.so.6(CXXABI_1.3)(64bit) provider: libstdc++.x86_64 4.4.7-3.el6 dependency: libc.so.6(GLIBC_2.3)(64bit) provider: glibc.x86_64 2.12-1.107.el6 provider: glibc.x86_64 2.12-1.107.el6_4.4 provider: glibc.x86_64 2.12-1.107.el6_4.2 dependency: libm.so.6()(64bit) provider: glibc.x86_64 2.12-1.107.el6 provider: glibc.x86_64 2.12-1.107.el6_4.4 provider: glibc.x86_64 2.12-1.107.el6_4.2 dependency: shadow-utils provider: shadow-utils.x86_64 2:4.1.4.2-13.el6 dependency: librt.so.1(GLIBC_2.2.5)(64bit) provider: glibc.x86_64 2.12-1.107.el6 provider: glibc.x86_64 2.12-1.107.el6_4.4 provider: glibc.x86_64 2.12-1.107.el6_4.2 dependency: libdl.so.2()(64bit) provider: glibc.x86_64 2.12-1.107.el6 provider: glibc.x86_64 2.12-1.107.el6_4.4 provider: glibc.x86_64 2.12-1.107.el6_4.2 dependency: libc.so.6(GLIBC_2.7)(64bit) provider: glibc.x86_64 2.12-1.107.el6 provider: glibc.x86_64 2.12-1.107.el6_4.4 provider: glibc.x86_64 2.12-1.107.el6_4.2 dependency: librt.so.1()(64bit) provider: glibc.x86_64 2.12-1.107.el6 provider: glibc.x86_64 2.12-1.107.el6_4.4 provider: glibc.x86_64 2.12-1.107.el6_4.2 dependency: libdl.so.2(GLIBC_2.2.5)(64bit) provider: glibc.x86_64 2.12-1.107.el6 provider: glibc.x86_64 2.12-1.107.el6_4.4 provider: glibc.x86_64 2.12-1.107.el6_4.2 dependency: /bin/sh provider: bash.x86_64 4.1.2-14.el6 provider: bash.x86_64 4.1.2-15.el6_4 dependency: config(MySQL-server) = 5.6.15-1.el6 Unsatisfied dependency dependency: libgcc_s.so.1()(64bit) provider: libgcc.x86_64 4.4.7-3.el6 dependency: libaio.so.1(LIBAIO_0.1)(64bit) provider: libaio.x86_64 0.3.107-10.el6 dependency: /usr/bin/perl provider: perl.x86_64 4:5.10.1-129.el6 provider: perl.x86_64 4:5.10.1-130.el6_4 provider: perl.x86_64 4:5.10.1-131.el6_4 dependency: procps provider: procps.x86_64 3.2.8-25.el6 provider: procps.i686 3.2.8-25.el6 dependency: libgcc_s.so.1(GCC_3.0)(64bit) provider: libgcc.x86_64 4.4.7-3.el6 dependency: chkconfig provider: chkconfig.x86_64 1.3.49.3-2.el6
この結果に対して、各dependencyパッケージがインストールされているか調べる。（dependencyの１つを使用して検索する）
yum list installed glibc.x86_64 ～ yum list installed chkconfig.x86_64
- 入力コマンド
-
インストールされていないものはインストールする。
yum install perl.x86_64
-
MySQL-clientをインストールする
yum localinstall MySQL-client-5.6.15-1.el6.x86_64.rpm
-
インストールされたことを確認する
- 入力コマンド
yum list installed | grep -i mys
- 出力
[root@main1 ~]# yum list installed | grep -i mys MySQL-client.x86_64 5.6.15-1.el6 @/MySQL-client-5.6.15-1.el6.x86_64 MySQL-server.x86_64 5.6.15-1.el6 installed mysql-libs.x86_64 5.1.66-2.el6_3 @anaconda-CentOS-201303020151.x86_64/6.4
- 入力コマンド
-
/etc/my.cnf を削除する。
mysql-libsを強制的に上書きしたため、設定ファイルは正常に削除されていない。
そのため、手動で削除する。
rm /etc/my.cnf
なお、/etc/my.cnfの中身は以下のようになっている。
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
パッケージからインストール（Compatibility Librariesをインストール）
パッケージからインストールすることで、簡単にインストールが行える。
Cent OSには標準で「mysql-libs.x86_64」が入っているが、これが存在すると競合が発生し、MySQL-serverをパッケージからインストールできない。
mysql-libs.x86_64はcronなどで使用しているため削除できない。
そのため、競合回避のためのパッケージを別途インストールする。
ただし、「Compatibility Libraries」をインストールすると、「Shared components」をインストールできない。
- MySQL-server-5.6.15-1.el6.x86_64.rpmとMySQL-client-5.6.15-1.el6.x86_64.rpm、
「Compatibility Libraries」をダウンロードする（wgetやscpなどを使用する）
※wgetは最小構成Cent OSには入っていないので、インストールする必要がある。（yum install wget）
wget http://dev.mysql.com/get/Downloads/MySQL-5.6/MySQL-server-5.6.15-1.el6.x86_64.rpm wget http://dev.mysql.com/get/Downloads/MySQL-5.6/MySQL-client-5.6.15-1.rhel5.x86_64.rpm wget http://dev.mysql.com/get/Downloads/MySQL-5.6/MySQL-shared-compat-5.6.15-1.rhel5.x86_64.rpm
- 「Compatibility Libraries」パッケージをインストールする。
- 入力コマンド
yum localinstall MySQL-shared-compat-5.6.15-1.rhel5.x86_64.rpm
- 出力
# インストール過程で競合の原因となる「mysql-libs」が削除される [root@main2 ~]# yum localinstall MySQL-shared-compat-5.6.15-1.rhel5.x86_64.rpm Loaded plugins: fastestmirror Setting up Local Package Process Examining MySQL-shared-compat-5.6.15-1.el6.x86_64.rpm: MySQL-shared-compat-5.6.15-1.el6.x86_64 Marking MySQL-shared-compat-5.6.15-1.el6.x86_64.rpm to be installed Loading mirror speeds from cached hostfile * base: ftp.iij.ad.jp * extras: ftp.iij.ad.jp * updates: ftp.iij.ad.jp Resolving Dependencies --> Running transaction check ---> Package MySQL-shared-compat.x86_64 0:5.6.15-1.el6 will be obsoleting ---> Package mysql-libs.x86_64 0:5.1.66-2.el6_3 will be obsoleted --> Finished Dependency Resolution Dependencies Resolved ===================================================================================================================================================================================== Package Arch Version Repository Size ===================================================================================================================================================================================== Installing: MySQL-shared-compat x86_64 5.6.15-1.el6 /MySQL-shared-compat-5.6.15-1.el6.x86_64 11 M replacing mysql-libs.x86_64 5.1.66-2.el6_3 Transaction Summary ===================================================================================================================================================================================== Install 1 Package(s) Total size: 11 M Is this ok [y/N]: y Downloading Packages: Running rpm_check_debug Running Transaction Test Transaction Test Succeeded Running Transaction Installing : MySQL-shared-compat-5.6.15-1.el6.x86_64 1/2 Erasing : mysql-libs-5.1.66-2.el6_3.x86_64 2/2 Verifying : MySQL-shared-compat-5.6.15-1.el6.x86_64 1/2 Verifying : mysql-libs-5.1.66-2.el6_3.x86_64 2/2 Installed: MySQL-shared-compat.x86_64 0:5.6.15-1.el6 Replaced: mysql-libs.x86_64 0:5.1.66-2.el6_3 Complete!
- 入力コマンド
- 「MySQL-server」パッケージをインストールする
-
- 入力コマンド
yum localinstall MySQL-server-5.6.15-1.el6.x86_64.rpm
- 入力コマンド
[root@main2 ~]# yum localinstall MySQL-server-5.6.15-1.el6.x86_64.rpm Loaded plugins: fastestmirror Setting up Local Package Process Examining MySQL-server-5.6.15-1.el6.x86_64.rpm: MySQL-server-5.6.15-1.el6.x86_64 Marking MySQL-server-5.6.15-1.el6.x86_64.rpm to be installed Loading mirror speeds from cached hostfile * base: ftp.iij.ad.jp * extras: ftp.iij.ad.jp * updates: ftp.iij.ad.jp Resolving Dependencies --> Running transaction check ---> Package MySQL-server.x86_64 0:5.6.15-1.el6 will be installed --> Processing Dependency: /usr/bin/perl for package: MySQL-server-5.6.15-1.el6.x86_64 --> Running transaction check ---> Package perl.x86_64 4:5.10.1-131.el6_4 will be installed --> Processing Dependency: perl-libs = 4:5.10.1-131.el6_4 for package: 4:perl-5.10.1-131.el6_4.x86_64 --> Processing Dependency: perl-libs for package: 4:perl-5.10.1-131.el6_4.x86_64 --> Processing Dependency: perl(version) for package: 4:perl-5.10.1-131.el6_4.x86_64 --> Processing Dependency: perl(Pod::Simple) for package: 4:perl-5.10.1-131.el6_4.x86_64 --> Processing Dependency: perl(Module::Pluggable) for package: 4:perl-5.10.1-131.el6_4.x86_64 --> Processing Dependency: libperl.so()(64bit) for package: 4:perl-5.10.1-131.el6_4.x86_64 --> Running transaction check ---> Package perl-Module-Pluggable.x86_64 1:3.90-131.el6_4 will be installed ---> Package perl-Pod-Simple.x86_64 1:3.13-131.el6_4 will be installed --> Processing Dependency: perl(Pod::Escapes) >= 1.04 for package: 1:perl-Pod-Simple-3.13-131.el6_4.x86_64 ---> Package perl-libs.x86_64 4:5.10.1-131.el6_4 will be installed ---> Package perl-version.x86_64 3:0.77-131.el6_4 will be installed --> Running transaction check ---> Package perl-Pod-Escapes.x86_64 1:1.04-131.el6_4 will be installed --> Finished Dependency Resolution Dependencies Resolved ===================================================================================================================================================================================== Package Arch Version Repository Size ===================================================================================================================================================================================== Installing: MySQL-server x86_64 5.6.15-1.el6 /MySQL-server-5.6.15-1.el6.x86_64 235 M Installing for dependencies: perl x86_64 4:5.10.1-131.el6_4 updates 10 M perl-Module-Pluggable x86_64 1:3.90-131.el6_4 updates 39 k perl-Pod-Escapes x86_64 1:1.04-131.el6_4 updates 31 k perl-Pod-Simple x86_64 1:3.13-131.el6_4 updates 211 k perl-libs x86_64 4:5.10.1-131.el6_4 updates 577 k perl-version x86_64 3:0.77-131.el6_4 updates 50 k Transaction Summary ===================================================================================================================================================================================== Install 7 Package(s) Total size: 246 M Total download size: 11 M Installed size: 270 M Is this ok [y/N]: y Downloading Packages: (1/6): perl-5.10.1-131.el6_4.x86_64.rpm | 10 MB 00:02 (2/6): perl-Module-Pluggable-3.90-131.el6_4.x86_64.rpm | 39 kB 00:00 (3/6): perl-Pod-Escapes-1.04-131.el6_4.x86_64.rpm | 31 kB 00:00 (4/6): perl-Pod-Simple-3.13-131.el6_4.x86_64.rpm | 211 kB 00:00 (5/6): perl-libs-5.10.1-131.el6_4.x86_64.rpm | 577 kB 00:00 (6/6): perl-version-0.77-131.el6_4.x86_64.rpm | 50 kB 00:00 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Total 2.3 MB/s | 11 MB 00:04 Running rpm_check_debug Running Transaction Test Transaction Test Succeeded Running Transaction Installing : 1:perl-Pod-Escapes-1.04-131.el6_4.x86_64 1/7 Installing : 4:perl-libs-5.10.1-131.el6_4.x86_64 2/7 Installing : 3:perl-version-0.77-131.el6_4.x86_64 3/7 Installing : 1:perl-Module-Pluggable-3.90-131.el6_4.x86_64 4/7 Installing : 1:perl-Pod-Simple-3.13-131.el6_4.x86_64 5/7 Installing : 4:perl-5.10.1-131.el6_4.x86_64 6/7 Installing : MySQL-server-5.6.15-1.el6.x86_64 7/7 2013-09-04 19:29:17 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2013-09-04 19:29:18 2668 [Note] InnoDB: The InnoDB memory heap is disabled 2013-09-04 19:29:18 2668 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2013-09-04 19:29:18 2668 [Note] InnoDB: Compressed tables use zlib 1.2.3 2013-09-04 19:29:18 2668 [Note] InnoDB: Using Linux native AIO 2013-09-04 19:29:18 2668 [Note] InnoDB: Using CPU crc32 instructions 2013-09-04 19:29:18 2668 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2013-09-04 19:29:19 2668 [Note] InnoDB: Completed initialization of buffer pool 2013-09-04 19:29:19 2668 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created! 2013-09-04 19:29:19 2668 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB 2013-09-04 19:29:19 2668 [Note] InnoDB: Database physically writes the file full: wait... 2013-09-04 19:29:20 2668 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB 2013-09-04 19:29:21 2668 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB 2013-09-04 19:29:22 2668 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 2013-09-04 19:29:22 2668 [Warning] InnoDB: New log files created, LSN=45781 2013-09-04 19:29:22 2668 [Note] InnoDB: Doublewrite buffer not found: creating new 2013-09-04 19:29:22 2668 [Note] InnoDB: Doublewrite buffer created 2013-09-04 19:29:22 2668 [Note] InnoDB: 128 rollback segment(s) are active. 2013-09-04 19:29:22 2668 [Warning] InnoDB: Creating foreign key constraint system tables. 2013-09-04 19:29:22 2668 [Note] InnoDB: Foreign key constraint system tables created 2013-09-04 19:29:22 2668 [Note] InnoDB: Creating tablespace and datafile system tables. 2013-09-04 19:29:22 2668 [Note] InnoDB: Tablespace and datafile system tables created. 2013-09-04 19:29:22 2668 [Note] InnoDB: Waiting for purge to start 2013-09-04 19:29:22 2668 [Note] InnoDB: 5.6.15 started; log sequence number 0 2013-09-04 19:29:24 2668 [Note] Binlog end 2013-09-04 19:29:24 2668 [Note] InnoDB: FTS optimize thread exiting. 2013-09-04 19:29:24 2668 [Note] InnoDB: Starting shutdown... 2013-09-04 19:29:25 2668 [Note] InnoDB: Shutdown completed; log sequence number 1625977 A random root password has been set. You will find it in '/root/.mysql_secret'. 2013-09-04 19:29:25 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2013-09-04 19:29:25 2691 [Note] InnoDB: The InnoDB memory heap is disabled 2013-09-04 19:29:25 2691 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2013-09-04 19:29:25 2691 [Note] InnoDB: Compressed tables use zlib 1.2.3 2013-09-04 19:29:25 2691 [Note] InnoDB: Using Linux native AIO 2013-09-04 19:29:25 2691 [Note] InnoDB: Using CPU crc32 instructions 2013-09-04 19:29:25 2691 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2013-09-04 19:29:25 2691 [Note] InnoDB: Completed initialization of buffer pool 2013-09-04 19:29:25 2691 [Note] InnoDB: Highest supported file format is Barracuda. 2013-09-04 19:29:25 2691 [Note] InnoDB: 128 rollback segment(s) are active. 2013-09-04 19:29:25 2691 [Note] InnoDB: Waiting for purge to start 2013-09-04 19:29:25 2691 [Note] InnoDB: 5.6.15 started; log sequence number 1625977 2013-09-04 19:29:26 2691 [Note] Binlog end 2013-09-04 19:29:26 2691 [Note] InnoDB: FTS optimize thread exiting. 2013-09-04 19:29:26 2691 [Note] InnoDB: Starting shutdown... 2013-09-04 19:29:27 2691 [Note] InnoDB: Shutdown completed; log sequence number 1625987 A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER ! You will find that password in '/root/.mysql_secret'. You must change that password on your first connect, no other statement but 'SET PASSWORD' will be accepted. See the manual for the semantics of the 'password expired' flag. Also, the account for the anonymous user has been removed. In addition, you can run: /usr/bin/mysql_secure_installation which will also give you the option of removing the test database. This is strongly recommended for production servers. See the manual for more instructions. Please report any problems with the /usr/bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at http://shop.mysql.com New default config file was created as /usr/my.cnf and will be used by default by the server when you start it. You may edit this file to change server settings Verifying : 1:perl-Pod-Escapes-1.04-131.el6_4.x86_64 1/7 Verifying : 1:perl-Pod-Simple-3.13-131.el6_4.x86_64 2/7 Verifying : 4:perl-5.10.1-131.el6_4.x86_64 3/7 Verifying : 4:perl-libs-5.10.1-131.el6_4.x86_64 4/7 Verifying : 3:perl-version-0.77-131.el6_4.x86_64 5/7 Verifying : MySQL-server-5.6.15-1.el6.x86_64 6/7 Verifying : 1:perl-Module-Pluggable-3.90-131.el6_4.x86_64 7/7 Installed: MySQL-server.x86_64 0:5.6.15-1.el6 Dependency Installed: perl.x86_64 4:5.10.1-131.el6_4 perl-Module-Pluggable.x86_64 1:3.90-131.el6_4 perl-Pod-Escapes.x86_64 1:1.04-131.el6_4 perl-Pod-Simple.x86_64 1:3.13-131.el6_4 perl-libs.x86_64 4:5.10.1-131.el6_4 perl-version.x86_64 3:0.77-131.el6_4 Complete!
-
- MySQL-clientをインストールする
yum localinstall MySQL-client-5.6.15-1.rhel5.x86_64.rpm
- インストールされたことを確認する
- 入力コマンド
yum list installed | grep -i mys
- 出力（rpmを使用して強制インストールした場合）
[root@main1 ~]# yum list installed | grep -i mys MySQL-client.x86_64 5.6.15-1.el6 @/MySQL-client-5.6.15-1.el6.x86_64 MySQL-server.x86_64 5.6.15-1.el6 installed mysql-libs.x86_64 5.1.66-2.el6_3 @anaconda-CentOS-201303020151.x86_64/6.4
- 出力（「Compatibility Libraries」をインストールした場合）
[root@main2 ~]# yum list installed | grep -i mys MySQL-client.x86_64 5.6.15-1.el6 @/MySQL-client-5.6.15-1.el6.x86_64 MySQL-server.x86_64 5.6.15-1.el6 @/MySQL-server-5.6.15-1.el6.x86_64 MySQL-shared-compat.x86_64 5.6.15-1.el6 @/MySQL-shared-compat-5.6.15-1.el6.x86_64
なお、初期設定ファイルは/usr/my.cnfに作成される。
内容は以下のとおりである。
# The random password set for the root user at Wed Sep 4 19:29:23 2013 (local time): qJdaNNPL [root@main2 ~]# cat /usr/my.cnf # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html [mysqld] # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. # basedir = ..... # datadir = ..... # port = ..... # server_id = ..... # socket = ..... # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [root@main2 ~]# cat /usr/my.cnf # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html [mysqld] # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. # basedir = ..... # datadir = ..... # port = ..... # server_id = ..... # socket = ..... # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [root@main2 ~]# cat /usr/my.cnf # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html [mysqld] # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. # basedir = ..... # datadir = ..... # port = ..... # server_id = ..... # socket = ..... # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
- 入力コマンド
ソースパッケージからインストール
ソースコードからインストール
ソースコードからインストールすることで、インストール先を変更したり、
不要な機能を削除したりさまざまなオプションを指定できる。
- rootになる
su -
- mysqlユーザを作成する
useradd --user-group --no-create-home --shell /sbin/nologin mysql
- 関連パッケージをインストールする
yum install gcc -y yum install gcc-c++ -y yum install cmake -y yum install perl -y yum install bison -y yum install ncurses-devel -y # 一括インストールの場合 yum install gcc gcc-c++ cmake perl bison ncurses-devel -y
- OSにデフォルトでインストールされているMySQLの設定ファイルを削除する
rm -f /etc/my.cnf
- scpやwgetを使用してソースコードをダウンロードする
wget http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.15.tar.gz
- 展開する
tar xzfv mysql-*.tar.gz
- MySQLディレクトリへ移動する
cd mysql-*
- コンパイル最適化設定を行う
export CFLAGS="-O3 -m64 -march=native" export CXXFLAGS=$CFLAGS # make時にinclude/mysqld_error.hでIllegal instruction include/mysqld_error.hエラーが出る場合は-march=nativeオプションは外す
- MySQLのインストール場所とデータ用ディレクトリを分ける場合は、データ用ディレクトリを作成する
# MySQLのデータ格納場所を/mysql-dataとする mkdir -p -m 774 /mysql-data chown mysql:mysql /mysql-data
- インストール設定を行う
# MySQLのインストールパスを/usr/local/mysql-5.6.15とする cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.6.15 -DMYSQL_DATADIR=/mysql-data 2>&1 | tee cmake.log # このコマンドの後、CMakeCache.txtファイルが生成されるので、全てのオプション値が確認できる
Buildオプションは以下
http://dev.mysql.com/doc/refman/5.6/en/source-configuration-options.html
デフォルト値は以下（./configure –helpで表示）
// Choose the type of build, options are: None(CMAKE_CXX_FLAGS or CMAKE_C_FLAGS used) Debug Release RelWithDebInfo MinSizeRel CMAKE_BUILD_TYPE:STRING= // install prefix CMAKE_INSTALL_PREFIX:PATH=/usr/local/mysql // Set to true if this is a community build COMMUNITY_BUILD:BOOL=ON // Enable profiling ENABLED_PROFILING:BOOL=ON // Enable debug sync (debug builds only) ENABLE_DEBUG_SYNC:BOOL=ON // Download and build 3rd party source code components, e.g. google mock ENABLE_DOWNLOADS:BOOL=OFF // Enable gcov (debug, Linux builds only) ENABLE_GCOV:BOOL=OFF // Enable gprof (optimized, Linux builds only) ENABLE_GPROF:BOOL=OFF // Enable SASL on InnoDB Memcached ENABLE_MEMCACHED_SASL:BOOL=OFF // Enable SASL on InnoDB Memcached ENABLE_MEMCACHED_SASL_PWDB:BOOL=OFF // Selection of features. Options are - xsmall : - small: embedded - classic: embedded + archive + federated + blackhole - large : embedded + archive + federated + blackhole + innodb - xlarge: embedded + archive + federated + blackhole + innodb + partition - community: all features (currently == xlarge) FEATURE_SET:STRING=community // Installation directory layout. Options are: STANDALONE (as in zip or tar.gz installer), RPM, DEB, SVR4 INSTALL_LAYOUT:STRING=STANDALONE // default MySQL data directory MYSQL_DATADIR:PATH=/usr/local/mysql/data // MySQL maintainer-specific development environment MYSQL_MAINTAINER_MODE:BOOL=OFF // Support tracing of Optimizer OPTIMIZER_TRACE:BOOL=ON // Link ARCHIVE statically to the server WITH_ARCHIVE_STORAGE_ENGINE:BOOL=ON // Enable address sanitizer WITH_ASAN:BOOL=OFF // Link BLACKHOLE statically to the server WITH_BLACKHOLE_STORAGE_ENGINE:BOOL=ON // Use dbug/safemutex WITH_DEBUG:BOOL=OFF // Use flags from cmake/build_configurations/compiler_options.cmake WITH_DEFAULT_COMPILER_OPTIONS:BOOL=ON // Use feature set in cmake/build_configurations/feature_set.cmake WITH_DEFAULT_FEATURE_SET:BOOL=ON // By default use bundled editline WITH_EDITLINE:STRING=bundled // Compile MySQL with embedded server WITH_EMBEDDED_SERVER:BOOL=ON // WITH_EXAMPLE_STORAGE_ENGINE:BOOL=OFF // Options are: none, complex, all WITH_EXTRA_CHARSETS:STRING=all // Link FEDERATED statically to the server WITH_FEDERATED_STORAGE_ENGINE:BOOL=ON // Link INNOBASE statically to the server WITH_INNOBASE_STORAGE_ENGINE:BOOL=ON // WITH_INNODB_MEMCACHED:BOOL=OFF // By default use bundled libevent on this platform WITH_LIBEVENT:STRING=bundled // Compile with tcp wrappers support WITH_LIBWRAP:BOOL=OFF // Link PARTITION statically to the server WITH_PARTITION_STORAGE_ENGINE:BOOL=ON // Link PERFSCHEMA statically to the server WITH_PERFSCHEMA_STORAGE_ENGINE:BOOL=ON // Generate PIC objects WITH_PIC:BOOL=OFF // bundled (use yassl), yes (prefer os library if present, otherwise use bundled), system (use os library), </path/to/custom/installation> WITH_SSL:STRING=bundled // Compile MySQL with unit tests WITH_UNIT_TESTS:BOOL=ON // Valgrind instrumentation WITH_VALGRIND:BOOL=OFF // WITH_ZLIB:STRING=bundled
- インストールする
make 2>&1 | tee make.log make install 2>&1 | tee make_install.log
- 起動スクリプトを設定
cp support-files/mysql.server /etc/init.d/mysqld chmod 554 /etc/init.d/mysqld chkconfig --add mysqld # 登録を確認する chkconfig --list mysqld
- インストールに使用したパッケージを削除する
cd ../ rm -rf mysql-*
- MySQLのシンボリックリンクを作成する
ln -s /usr/local/mysql-5.6.15 /usr/local/mysql
これによって、複数のバージョンのApacheを同居させ、リンクを切り替えることで、バージョン変更が可能 - 共通した環境とするために環境変数を設定する
export MYSQL_HOME=/usr/local/mysql export MYSQL_DATA=/mysql-data
- ツール類にパスを通す
export PATH=$MYSQL_HOME/bin:$PATH
- 環境変数を再起動後も有効にする
vi /etc/bashrc # 以下を追記する export MYSQL_HOME=/usr/local/mysql export MYSQL_DATA=/mysql-data export PATH=$MYSQL_HOME/bin:$PATH
- 空の設定ファイルを作成する
echo "[mysqld]" > $MYSQL_HOME/my.cnf
MySQL初期作業
初期データベースの作成
MySQLは管理情報自体も自身のデータベース内で管理するが、
インストール直後にはそのデータベースが作成されていないため、別途作成する。
cd $MYSQL_HOME scripts/mysql_install_db --datadir=$MYSQL_DATA --user=mysql
完了後、カレントディレクトリに設定ファイルが生成されるので、削除する。
rm -f my.cnf
所有者の変更を行う。
chown mysql:mysql $MYSQL_DATA chown mysql:mysql $MYSQL_DATA/* chown mysql:mysql $MYSQL_DATA/*/*
- ログ
[root@mysql2 mysql]# $MYSQL_HOME/scripts/mysql_install_db --datadir=$MYSQL_DATA --user=mysql WARNING: The host 'mysql2.local' could not be looked up with ./bin/resolveip. This probably means that your libc libraries are not 100 % compatible with this binary MySQL version. The MySQL daemon, mysqld, should work normally with the exception that host name resolving will not work. This means that you should use IP addresses instead of hostnames when specifying MySQL privileges ! Installing MySQL system tables...2014-01-21 21:21:35 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2014-01-21 21:21:35 7384 [Note] InnoDB: The InnoDB memory heap is disabled 2014-01-21 21:21:35 7384 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2014-01-21 21:21:35 7384 [Note] InnoDB: Compressed tables use zlib 1.2.3 2014-01-21 21:21:35 7384 [Note] InnoDB: Using CPU crc32 instructions 2014-01-21 21:21:35 7384 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2014-01-21 21:21:35 7384 [Note] InnoDB: Completed initialization of buffer pool 2014-01-21 21:21:35 7384 [Note] InnoDB: Highest supported file format is Barracuda. 2014-01-21 21:21:35 7384 [Note] InnoDB: Log scan progressed past the checkpoint lsn 49463 2014-01-21 21:21:35 7384 [Note] InnoDB: Database was not shutdown normally! 2014-01-21 21:21:35 7384 [Note] InnoDB: Starting crash recovery. 2014-01-21 21:21:35 7384 [Note] InnoDB: Reading tablespace information from the .ibd files... 2014-01-21 21:21:35 7384 [Note] InnoDB: Restoring possible half-written data pages 2014-01-21 21:21:35 7384 [Note] InnoDB: from the doublewrite buffer... InnoDB: Doing recovery: scanned up to log sequence number 1600617 2014-01-21 21:21:35 7384 [Note] InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percent: 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 2014-01-21 21:21:35 7384 [Note] InnoDB: 128 rollback segment(s) are active. 2014-01-21 21:21:35 7384 [Note] InnoDB: Waiting for purge to start 2014-01-21 21:21:36 7384 [Note] InnoDB: 5.6.15 started; log sequence number 1600617 2014-01-21 21:21:36 7384 [Note] Binlog end 2014-01-21 21:21:36 7384 [Note] InnoDB: FTS optimize thread exiting. 2014-01-21 21:21:36 7384 [Note] InnoDB: Starting shutdown... 2014-01-21 21:21:37 7384 [Note] InnoDB: Shutdown completed; log sequence number 1625997 OK Filling help tables...2014-01-21 21:21:37 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2014-01-21 21:21:37 7409 [Note] InnoDB: The InnoDB memory heap is disabled 2014-01-21 21:21:37 7409 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2014-01-21 21:21:37 7409 [Note] InnoDB: Compressed tables use zlib 1.2.3 2014-01-21 21:21:37 7409 [Note] InnoDB: Using CPU crc32 instructions 2014-01-21 21:21:37 7409 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2014-01-21 21:21:37 7409 [Note] InnoDB: Completed initialization of buffer pool 2014-01-21 21:21:37 7409 [Note] InnoDB: Highest supported file format is Barracuda. 2014-01-21 21:21:37 7409 [Note] InnoDB: 128 rollback segment(s) are active. 2014-01-21 21:21:37 7409 [Note] InnoDB: Waiting for purge to start 2014-01-21 21:21:37 7409 [Note] InnoDB: 5.6.15 started; log sequence number 1625997 2014-01-21 21:21:37 7409 [Note] Binlog end 2014-01-21 21:21:37 7409 [Note] InnoDB: FTS optimize thread exiting. 2014-01-21 21:21:37 7409 [Note] InnoDB: Starting shutdown... 2014-01-21 21:21:38 7409 [Note] InnoDB: Shutdown completed; log sequence number 1626007 OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h mysql2.local password 'new-password' Alternatively you can run: ./bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd . ; ./bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd mysql-test ; perl mysql-test-run.pl Please report any problems with the ./bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at http://shop.mysql.com New default config file was created as ./my.cnf and will be used by default by the server when you start it. You may edit this file to change server settings
MySQLの起動
インストールが完了したら、MySQLが動作するかどうか確認のため、MySQLを起動する。
初期設定ファイルは存在しないはずなので、デフォルトで起動する。
mysqld_safe &
起動オプションはプロセスを出力することで確認できる。
ps aux | grep mysql | grep -v grep
次のように表示される。
mysql 7567 0.9 43.8 1007536 447544 pts/0 Sl 21:22 0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/mysql-data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/mysql-data/mysql2.local.err --pid-file=/mysql-data/mysql2.local.pid
セキュア化
インストール直後のデフォルト状態では様々なセキュリティ上のリスクが存在する。
それらをまとめて修正できるのが、mysql_secure_installationスクリプトである。
MySQL Serverをインストールしたら、MySQL Serverを起動し、最初に実行するのが良い。
このスクリプトでは以下を行う。
- rootユーザにパスワードを設定する
- rootユーザのリモートログインを禁止する（localhost、127.0.0.1、::1からのみアクセスできるようにする）
- 匿名ユーザを削除する
- testデータベースを削除する
rootユーザの初期パスワードが/root/.mysql_secretに記載されているので、コピーしておく。
上記がない場合、パスワードは空文字列。
以下スクリプトを実行する。対話的に実行されるが、パスワードの入力以外は Y を入力しておけばよい。
mysql_secure_installation
- ログ
[root@main2 ~]# /usr/bin/mysql_secure_installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MySQL to secure it, we'll need the current password for the root user. If you've just installed MySQL, and you haven't set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none): OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MySQL root user without the proper authorisation. You already have a root password set, so you can safely answer 'n'. Change the root password? [Y/n] Y New password: Re-enter new password: Password updated successfully! Reloading privilege tables.. ... Success! By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? [Y/n] Y ... Success! Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? [Y/n] Y ... Success! By default, MySQL comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? [Y/n] Y - Dropping test database... ... Success! - Removing privileges on test database... ... Success! Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? [Y/n] Y ... Success! All done! If you've completed all of the above steps, your MySQL installation should now be secure. Thanks for using MySQL! Cleaning up...
接続確認
rootユーザでログインできることを確認する。
mysql -p
- ログ
[root@main2 ~]# mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 16 Server version: 5.6.15 MySQL Community Server (GPL) Copyright (c) 2000, 2013, 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. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.05 sec) mysql> exit Bye
削除されたtestデータベース以外に「information_schema」、「mysql」、「performance_schema」データベースが
初期状態で作成されているが、いずれも必要なのでおいておく。
MySQLの設定
設定方法
my.confについて
MySQLの設定はmy.confファイルに記述する。
ここにはMySQLサーバの設定のほかMySQLクライアントなどMySQLに関する全ての設定を記述する。
ただし、サーバの設定はmysqldセクションというようにセクションで区切られる。
MySQL起動オプションで明示的に上記設定ファイルのパスを指定することもできるが、
指定しなかった場合は、以下のファイルなどから設定が自動的に読み込まれる。
- /etc/my.cnf
- /etc/mysql/my.cnf
- SYSCONFDIR/my.cnf (SYSCONFDIRはMySQLビルド時に指定したパス。デフォルトではインストール先のディレクトリ)
- $MYSQL_HOME/my.cnf
- –dafaults-extra-file (MySQL起動オプションで設定ファイルのパスを指定する)
- ~/my.cnf
- コマンドラインオプション (MySQL起動オプションで設定値を指定する)
上記の順に設定が読み込まれ、重複した場合は後から設定された（読み込まれた）ものが有効になる。
なお、–dafaults-fileオプションを使用してファイルを指定した場合、そのファイル以外の設定ファイルは読み込まれなくなる。
設定ファイルによる設定を反映させる場合、MySQLの再起動が必要となる。
- 書式
my.confは [文字列] と記載するセクションの下に 変数=値 もしくは 変数 の形式で記入する。
変数名のアンダースコア（_）はハイフン（-）に置換可能。
サーバの設定は [mysqld] セクションに記述する。
変数について
設定ファイルによる設定のほか、起動時にオプションとして渡したり、
mysqlコマンドによって動作中のMySQLに設定を行うことができる。
各設定値は変数として表され、変数の値を変更することで、設定を変更できる。
各変数の意味はこちらを参照。
- 起動時に設定する
mysqld_safe --<変数>[=値] &
- 起動中に変更する
ログイン後以下を実行
set global <変数>[=値] ;
なお、変数名のアンダースコア（_）はハイフン（-）に置換可能。
現在の変数の一覧はmysqladminコマンドで確認できる。
mysqladmin variables -p
変数の初期値は以下のとおりである。
+--------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +--------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | | autocommit | ON | | automatic_sp_privileges | ON | | back_log | 80 | | basedir | /usr/local/mysql | | big_tables | OFF | | bind_address | * | | binlog_cache_size | 32768 | | binlog_checksum | CRC32 | | binlog_direct_non_transactional_updates | OFF | | binlog_format | STATEMENT | | binlog_max_flush_queue_time | 0 | | binlog_order_commits | ON | | binlog_row_image | FULL | | binlog_rows_query_log_events | OFF | | binlog_stmt_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql-5.6.15/share/charsets/ | | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | | completion_type | NO_CHAIN | | concurrent_insert | AUTO | | connect_timeout | 10 | | core_file | OFF | | datadir | /mysql-data/ | | date_format | %Y-%m-%d | | datetime_format | %Y-%m-%d %H:%i:%s | | default_storage_engine | InnoDB | | default_tmp_storage_engine | InnoDB | | default_week_format | 0 | | delay_key_write | ON | | delayed_insert_limit | 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | disconnect_on_expired_password | ON | | div_precision_increment | 4 | | end_markers_in_json | OFF | | enforce_gtid_consistency | OFF | | eq_range_index_dive_limit | 10 | | event_scheduler | OFF | | expire_logs_days | 0 | | explicit_defaults_for_timestamp | OFF | | flush | OFF | | flush_time | 0 | | foreign_key_checks | ON | | ft_boolean_syntax | + -><()~*:""&| | | ft_max_word_len | 84 | | ft_min_word_len | 4 | | ft_query_expansion_limit | 20 | | ft_stopword_file | (built-in) | | general_log | OFF | | general_log_file | /mysql-data/mysql2.log | | group_concat_max_len | 1024 | | gtid_executed | | | gtid_mode | OFF | | gtid_owned | | | gtid_purged | | | have_compress | YES | | have_crypt | YES | | have_dynamic_loading | YES | | have_geometry | YES | | have_openssl | DISABLED | | have_profiling | YES | | have_query_cache | YES | | have_rtree_keys | YES | | have_ssl | DISABLED | | have_symlink | YES | | host_cache_size | 279 | | hostname | mysql2.local | | ignore_builtin_innodb | OFF | | ignore_db_dirs | | | init_connect | | | init_file | | | init_slave | | | innodb_adaptive_flushing | ON | | innodb_adaptive_flushing_lwm | 10 | | innodb_adaptive_hash_index | ON | | innodb_adaptive_max_sleep_delay | 150000 | | innodb_additional_mem_pool_size | 8388608 | | innodb_api_bk_commit_interval | 5 | | innodb_api_disable_rowlock | OFF | | innodb_api_enable_binlog | OFF | | innodb_api_enable_mdl | OFF | | innodb_api_trx_level | 0 | | innodb_autoextend_increment | 64 | | innodb_autoinc_lock_mode | 1 | | innodb_buffer_pool_dump_at_shutdown | OFF | | innodb_buffer_pool_dump_now | OFF | | innodb_buffer_pool_filename | ib_buffer_pool | | innodb_buffer_pool_instances | 8 | | innodb_buffer_pool_load_abort | OFF | | innodb_buffer_pool_load_at_startup | OFF | | innodb_buffer_pool_load_now | OFF | | innodb_buffer_pool_size | 134217728 | | innodb_change_buffer_max_size | 25 | | innodb_change_buffering | all | | innodb_checksum_algorithm | innodb | | innodb_checksums | ON | | innodb_cmp_per_index_enabled | OFF | | innodb_commit_concurrency | 0 | | innodb_compression_failure_threshold_pct | 5 | | innodb_compression_level | 6 | | innodb_compression_pad_pct_max | 50 | | innodb_concurrency_tickets | 5000 | | innodb_data_file_path | ibdata1:12M:autoextend | | innodb_data_home_dir | | | innodb_disable_sort_file_cache | OFF | | innodb_doublewrite | ON | | innodb_fast_shutdown | 1 | | innodb_file_format | Antelope | | innodb_file_format_check | ON | | innodb_file_format_max | Antelope | | innodb_file_per_table | ON | | innodb_flush_log_at_timeout | 1 | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_flush_neighbors | 1 | | innodb_flushing_avg_loops | 30 | | innodb_force_load_corrupted | OFF | | innodb_force_recovery | 0 | | innodb_ft_aux_table | | | innodb_ft_cache_size | 8000000 | | innodb_ft_enable_diag_print | OFF | | innodb_ft_enable_stopword | ON | | innodb_ft_max_token_size | 84 | | innodb_ft_min_token_size | 3 | | innodb_ft_num_word_optimize | 2000 | | innodb_ft_result_cache_limit | 2000000000 | | innodb_ft_server_stopword_table | | | innodb_ft_sort_pll_degree | 2 | | innodb_ft_total_cache_size | 640000000 | | innodb_ft_user_stopword_table | | | innodb_io_capacity | 200 | | innodb_io_capacity_max | 2000 | | innodb_large_prefix | OFF | | innodb_lock_wait_timeout | 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_buffer_size | 8388608 | | innodb_log_compressed_pages | ON | | innodb_log_file_size | 50331648 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_lru_scan_depth | 1024 | | innodb_max_dirty_pages_pct | 75 | | innodb_max_dirty_pages_pct_lwm | 0 | | innodb_max_purge_lag | 0 | | innodb_max_purge_lag_delay | 0 | | innodb_mirrored_log_groups | 1 | | innodb_monitor_disable | | | innodb_monitor_enable | | | innodb_monitor_reset | | | innodb_monitor_reset_all | | | innodb_old_blocks_pct | 37 | | innodb_old_blocks_time | 1000 | | innodb_online_alter_log_max_size | 134217728 | | innodb_open_files | 2000 | | innodb_optimize_fulltext_only | OFF | | innodb_page_size | 16384 | | innodb_print_all_deadlocks | OFF | | innodb_purge_batch_size | 300 | | innodb_purge_threads | 1 | | innodb_random_read_ahead | OFF | | innodb_read_ahead_threshold | 56 | | innodb_read_io_threads | 4 | | innodb_read_only | OFF | | innodb_replication_delay | 0 | | innodb_rollback_on_timeout | OFF | | innodb_rollback_segments | 128 | | innodb_sort_buffer_size | 1048576 | | innodb_spin_wait_delay | 6 | | innodb_stats_auto_recalc | ON | | innodb_stats_method | nulls_equal | | innodb_stats_on_metadata | OFF | | innodb_stats_persistent | ON | | innodb_stats_persistent_sample_pages | 20 | | innodb_stats_sample_pages | 8 | | innodb_stats_transient_sample_pages | 8 | | innodb_strict_mode | OFF | | innodb_support_xa | ON | | innodb_sync_array_size | 1 | | innodb_sync_spin_loops | 30 | | innodb_table_locks | ON | | innodb_thread_concurrency | 0 | | innodb_thread_sleep_delay | 10000 | | innodb_undo_directory | . | | innodb_undo_logs | 128 | | innodb_undo_tablespaces | 0 | | innodb_use_native_aio | OFF | | innodb_use_sys_malloc | ON | | innodb_version | 5.6.15 | | innodb_write_io_threads | 4 | | interactive_timeout | 28800 | | join_buffer_size | 262144 | | keep_files_on_create | OFF | | key_buffer_size | 8388608 | | key_cache_age_threshold | 300 | | key_cache_block_size | 1024 | | key_cache_division_limit | 100 | | large_files_support | ON | | large_page_size | 0 | | large_pages | OFF | | lc_messages | en_US | | lc_messages_dir | /usr/local/mysql-5.6.15/share/ | | lc_time_names | en_US | | license | GPL | | local_infile | ON | | lock_wait_timeout | 31536000 | | locked_in_memory | OFF | | log_bin | OFF | | log_bin_basename | | | log_bin_index | | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | log_error | /mysql-data/mysql2.local.err | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_admin_statements | OFF | | log_slow_slave_statements | OFF | | log_throttle_queries_not_using_indexes | 0 | | log_warnings | 1 | | long_query_time | 10.000000 | | low_priority_updates | OFF | | lower_case_file_system | OFF | | lower_case_table_names | 0 | | master_info_repository | FILE | | master_verify_checksum | OFF | | max_allowed_packet | 4194304 | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | max_connect_errors | 100 | | max_connections | 151 | | max_delayed_threads | 20 | | max_error_count | 64 | | max_heap_table_size | 16777216 | | max_insert_delayed_threads | 20 | | max_join_size | 18446744073709551615 | | max_length_for_sort_data | 1024 | | max_prepared_stmt_count | 16382 | | max_relay_log_size | 0 | | max_seeks_for_key | 18446744073709551615 | | max_sort_length | 1024 | | max_sp_recursion_depth | 0 | | max_tmp_tables | 32 | | max_user_connections | 0 | | max_write_lock_count | 18446744073709551615 | | metadata_locks_cache_size | 1024 | | metadata_locks_hash_instances | 8 | | min_examined_row_limit | 0 | | multi_range_count | 256 | | myisam_data_pointer_size | 6 | | myisam_max_sort_file_size | 9223372036853727232 | | myisam_mmap_size | 18446744073709551615 | | myisam_recover_options | OFF | | myisam_repair_threads | 1 | | myisam_sort_buffer_size | 8388608 | | myisam_stats_method | nulls_unequal | | myisam_use_mmap | OFF | | net_buffer_length | 16384 | | net_read_timeout | 30 | | net_retry_count | 10 | | net_write_timeout | 60 | | new | OFF | | old | OFF | | old_alter_table | OFF | | old_passwords | 0 | | open_files_limit | 5000 | | optimizer_prune_level | 1 | | optimizer_search_depth | 62 | | optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on | | optimizer_trace | enabled=off,one_line=off | | optimizer_trace_features | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on | | optimizer_trace_limit | 1 | | optimizer_trace_max_mem_size | 16384 | | optimizer_trace_offset | -1 | | performance_schema | ON | | performance_schema_accounts_size | 100 | | performance_schema_digests_size | 10000 | | performance_schema_events_stages_history_long_size | 10000 | | performance_schema_events_stages_history_size | 10 | | performance_schema_events_statements_history_long_size | 10000 | | performance_schema_events_statements_history_size | 10 | | performance_schema_events_waits_history_long_size | 10000 | | performance_schema_events_waits_history_size | 10 | | performance_schema_hosts_size | 100 | | performance_schema_max_cond_classes | 80 | | performance_schema_max_cond_instances | 3504 | | performance_schema_max_file_classes | 50 | | performance_schema_max_file_handles | 32768 | | performance_schema_max_file_instances | 7693 | | performance_schema_max_mutex_classes | 200 | | performance_schema_max_mutex_instances | 15906 | | performance_schema_max_rwlock_classes | 40 | | performance_schema_max_rwlock_instances | 9102 | | performance_schema_max_socket_classes | 10 | | performance_schema_max_socket_instances | 322 | | performance_schema_max_stage_classes | 150 | | performance_schema_max_statement_classes | 168 | | performance_schema_max_table_handles | 4000 | | performance_schema_max_table_instances | 12500 | | performance_schema_max_thread_classes | 50 | | performance_schema_max_thread_instances | 402 | | performance_schema_session_connect_attrs_size | 512 | | performance_schema_setup_actors_size | 100 | | performance_schema_setup_objects_size | 100 | | performance_schema_users_size | 100 | | pid_file | /mysql-data/mysql2.local.pid | | plugin_dir | /usr/local/mysql/lib/plugin/ | | port | 3306 | | preload_buffer_size | 32768 | | profiling | OFF | | profiling_history_size | 15 | | protocol_version | 10 | | query_alloc_block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 1048576 | | query_cache_type | OFF | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | | range_alloc_block_size | 4096 | | read_buffer_size | 131072 | | read_only | OFF | | read_rnd_buffer_size | 262144 | | relay_log | | | relay_log_basename | | | relay_log_index | | | relay_log_info_file | relay-log.info | | relay_log_info_repository | FILE | | relay_log_purge | ON | | relay_log_recovery | OFF | | relay_log_space_limit | 0 | | report_host | | | report_password | | | report_port | 3306 | | report_user | | | rpl_stop_slave_timeout | 31536000 | | secure_auth | ON | | secure_file_priv | | | server_id | 0 | | server_id_bits | 32 | | server_uuid | f25c1f2b-8295-11e3-9663-000c29033885 | | skip_external_locking | ON | | skip_name_resolve | OFF | | skip_networking | OFF | | skip_show_database | OFF | | slave_allow_batching | OFF | | slave_checkpoint_group | 512 | | slave_checkpoint_period | 300 | | slave_compressed_protocol | OFF | | slave_exec_mode | STRICT | | slave_load_tmpdir | /tmp | | slave_max_allowed_packet | 1073741824 | | slave_net_timeout | 3600 | | slave_parallel_workers | 0 | | slave_pending_jobs_size_max | 16777216 | | slave_rows_search_algorithms | TABLE_SCAN,INDEX_SCAN | | slave_skip_errors | OFF | | slave_sql_verify_checksum | ON | | slave_transaction_retries | 10 | | slave_type_conversions | | | slow_launch_time | 2 | | slow_query_log | OFF | | slow_query_log_file | /mysql-data/mysql2-slow.log | | socket | /tmp/mysql.sock | | sort_buffer_size | 262144 | | sql_auto_is_null | OFF | | sql_big_selects | ON | | sql_buffer_result | OFF | | sql_log_bin | ON | | sql_log_off | OFF | | sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | | sql_notes | ON | | sql_quote_show_create | ON | | sql_safe_updates | OFF | | sql_select_limit | 18446744073709551615 | | sql_slave_skip_counter | 0 | | sql_warnings | OFF | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | | | storage_engine | InnoDB | | stored_program_cache | 256 | | sync_binlog | 0 | | sync_frm | ON | | sync_master_info | 10000 | | sync_relay_log | 10000 | | sync_relay_log_info | 10000 | | system_time_zone | JST | | table_definition_cache | 1400 | | table_open_cache | 2000 | | table_open_cache_instances | 1 | | thread_cache_size | 9 | | thread_concurrency | 10 | | thread_handling | one-thread-per-connection | | thread_stack | 262144 | | time_format | %H:%i:%s | | time_zone | SYSTEM | | timed_mutexes | OFF | | tmp_table_size | 16777216 | | tmpdir | /tmp | | transaction_alloc_block_size | 8192 | | transaction_prealloc_size | 4096 | | tx_isolation | REPEATABLE-READ | | tx_read_only | OFF | | unique_checks | ON | | updatable_views_with_limit | YES | | version | 5.6.15 | | version_comment | Source distribution | | version_compile_machine | x86_64 | | version_compile_os | Linux | | wait_timeout | 28800 | +--------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
設定ファイルの作成
my.cnfが存在しないはずなので、新しく作成する。
touch $MYSQL_HOME/my.cnf chown mysql:mysql $MYSQL_HOME/my.cnf chmod 664 $MYSQL_HOME/my.cnf
基本設定
MySQL情報
version=5.6.15 version_comment=Source distribution version_compile_machine=x86_64 version_compile_os=Linux license=GPL protocol_version=10
パス
MySQLで使用する各種パスを指定する。
パスは絶対パスで指定し、basedirからの相対パスで指定することはできない。
- 書式
- basedir=<パス>
- character_sets_dir=<パス>
- datadir=<パス>
データを保存するディレクトリのパスを指定する。
このディレクトリは初回起動前にmysql_install_dbスクリプトを使用して初期化しておく必要がある。 - socket=<パス>
UNIXソケットファイルのパスを指定する - pid_file=<パス>
- plugin_dir=<パス>
- ignore_db_dirs=<パス>
- tmpdir=<パス>
内部一時ファイル作成パス。
メモリ上に作成できなかったテンポラリテーブルもこのディレクトリ内で作成される。
- デフォルト
basedir=/usr/local/mysql character_sets_dir=/usr/local/mysql-5.6.15/share/charsets/ datadir=/mysql-data/ socket=/tmp/mysql.sock pid_file=/mysql-data/mysql2.local.pid plugin_dir=/usr/local/mysql/lib/plugin/ ignore_db_dirs= tmpdir=/tmp
- 設定方法
- basedir=<パス>
- character_sets_dir=<パス>
- datadir=<パス>
データを保存するディレクトリのパスを指定する - socket=<パス>
UNIXソケットファイルのパスを指定する - pid_file=<パス>
- plugin_dir=<パス>
- ignore_db_dirs=<パス>
- tmpdir
作成場所のデフォルトでは /tmp で特に変更は不要。
HDDでない、高速に動作できるディスクをHDDと併用している場合は、そちらに切り替えると高速化できる。
ただし、tmp_table_sizeを調整してできるだけメモリ内に納めたほうが良い。
時間
- 書式
- デフォルト
- 設定方法
- time_format=%H:%i:%s
- time_zone=SYSTEM
- date_format=%Y-%m-%d
- datetime_format=%Y-%m-%d %H:%i:%s
- system_time_zone=JST
- default_week_format=0
ファイル
- 書式
- デフォルト
- 設定方法
- open_files_limit=<ファイル数>
MySQLが同時に開くことができるファイル数を指定する
- open_files_limit=<ファイル数>
- open_files_limit
- open_files_limit=5000
- open_files_limit
この変数の最適な値は以下の式で求められる値のうち、最も大きなものである。
- 10 + max_connections + (table_open_cache * 2)
- max_connections * 5
- open_files_limitの初期状態値（5000）
サーバー
- 書式
- デフォルト
- 設定方法
- server_id=0
- server_id_bits=32
- server_uuid=f25c1f2b-8295-11e3-9663-000c29033885
文字コード
- 書式
- skip_character_set_client_handshake
クライアントで指定した文字コードを無視し、サーバ側で設定した文字コードが使用される。
- skip_character_set_client_handshake
- デフォルト
- 設定方法
- skip_character_set_client_handshake
- collation_connection=latin1_swedish_ci
- collation_database=latin1_swedish_ci
- collation_server=latin1_swedish_ci
MySQLがデフォルトで使用する文字コードはlatin1であり、日本語は扱えない。
そのため、日本語を扱う場合は設定が必要となる。
- デフォルト
- character_set_client=latin1
- character_set_connection=latin1
- character_set_database=latin1
- character_set_filesystem=binary
- character_set_results=latin1
- character_set_server=latin1
- character_set_system=utf8
- 各値について
- character_set_client
クライアントがサーバと通信するときに使用する文字コード。
接続先サーバと同じにするのがよい。
（サーバマシンについてはローカルに接続するので、通常character_set_resultsと同じでよい） - character_set_connection
クライアントから受信した通信内容を解釈するのに使用する文字コード - character_set_database
データベースで情報を保持するときに使用される文字コード。 - character_set_filesystem
ファイルシステムが使用する文字コード。
localeコマンドで確認可能であるが、通常変更の必要はない。 - character_set_results
MySQLサーバからクライアントへ結果を送信するときに使用される文字コード。 - character_set_server
サーバで使用される文字コード。 - character_set_system
テーブルやカラム名などの定義情報で使用される文字コード。
デフォルトのutf8のままで、変更は不可。
- character_set_client
文字コードが異なる間でデータのやり取りが行われる場合、MySQLで自動で変換が行われる。
ただし、変換の負荷を考えると文字コードはクライアント環境も含めて統一したほうがよい。
全体で使用する文字コードを設定してもテーブル毎に個別に文字コードを設定することは可能で、
マルチバイト文字を使用しない場合は”latin1″を使用したほうがデータ量は少なくできる。
ただし、やはり変換負荷が発生するので、ストレージ容量より処理速度を優先して文字コードは統一したほうがよい。
マルチバイト文字を一切使用しないのであれば、デフォルトのままで、
使用する、もしくは今後使用する可能性があるのであれば、character_set_serverをutf8にするのがよい。
接続元がWindows環境であれば、”cp932″（Windowsのshift-jis）、
UTF-8で表現できない特殊な文字を使用する場合は”utf8mb4″（4byteのUTF8）を設定する。
なお、character_set_serverを変更すると自動でcharacter_set_databaseなど関連する変数も変更される。
また、初期状態で作成されるinformation_schema、mysql、performance_schemaのデータベースの文字コードはutf8となっている。
- クライアントの設定
クライアントとして利用するホスト内で使用する設定ファイルで
クライアントで使用する文字コードを記載できる。
[mysql]セクションにdefault_character_set=utf8のように記載する。
これによってサーバと通信時に文字コードの不一致が発生しても
サーバ側でクライアントの文字コードのものに変換して結果を返してくれる。
ただし、やはりこれによって変換の負荷が発生するため、
サーバ側に以下の設定を入れることで、サーバ側で設定されている文字コードを
クライアント側でも使用するよう強制することができる。
skip-character-set-client-handshake
これによって意図せず文字コードの変換が行われてしまうことを防ぐことができる。
エラーメッセージ
- 書式
- デフォルト
- 設定方法
max_error_count=64
エラーメッセージは通常英語で出力されるが、日本語を含むほかの言語で出力することができる。
- 書式
- lc_messages
- lc_messages=<言語タイプ>
- lc_messages_dir
使用する言語のエラーメッセージ集があるディレクトリのパス
- lc_messages_dir=<言語ファイルパス>
- lc_time_names
時間を出力する際に月と日をどの言語で表示するか。
- lc_time_names=<言語タイプ>
- 言語タイプ
http://dev.mysql.com/doc/refman/5.6/en/locale-support.html
日本語は「ja_JP」
- デフォルト
lc_messages=en_US lc_messages_dir=<インストール場所>/share/ lc_time_names=en_US
ネットワーク
hostname=mysql2.local max_connect_errors=100 skip_networking=OFF
- max_user_connections
アカウントごとの同時接続可能数。
0は無制限。 - 書式
- max_user_connections=<接続可能数>
- デフォルト
max_user_connections=0
アドレス・ポート
MySQLサーバデーモンが使用するアドレス・ポート番号。
これらは起動時にのみ指定可能で、起動後の変数の変更は不可。
- bind_address
アクセスを受け付けるIPアドレス。IPアドレスを複数持つ場合、１つのアドレスのみに限定できる。
- 書式
bind_address=<IPアドレス>
- デフォルト
# どのアドレス宛てでもアクセス可能 bind_address=*
- 設定方法
アクセス制限を行う要件がある場合、設定を行う。
- port
- 書式
port=<ポート番号>
- デフォルト
port=3306
- 設定方法
ポート番号はインターネットなど不特定多数からアクセス可能なサーバであれば、
セキュリティのためにデフォルトから変更したほうがよいが、
そもそも公開しない、もしくはローカルからのアクセスのみ許可するとしたほうがよい。
クライアント間通信
クライアント間通信の際に使用されるパラメータを設定する。
ネットワークの品質が悪い場合は調整が必要。
- 書式
- net_buffer_length
クライアント間通信の際に使用されるバッファの初期サイズ。
受信・送信データがこのバッファサイズより大きければ動的に拡張される。
ただし、max_allowed_packetを超えることはない。
バイト単位で指定する。
- net_buffer_length
- net_buffer_length=<送受信データサイズ(byte)>
- net_write_timeout
クライアントにデータの送信を行う際、ここで設定した値の時間以上かかる場合は送信が中止される。
- net_write_timeout=<タイムアウト時間（秒）>
- net_read_timeout
クライアントからデータの受信を行う際、ここで設定した値の時間以上かかる場合は受信が中止される。
- net_read_timeout=<タイムアウト時間（秒）>
- net_retry_count
通信が途中で途切れてしまった場合にリトライする回数
- net_retry_count=<リトライ回数>
- max_allowed_packet
クライアントから１度に受け取ることができるクエリサイズ。バイト単位で指定する。
- max_allowed_packet=<受信データサイズ(byte)>
- デフォルト
# 16KB net_buffer_length=16384 net_write_timeout=60 net_read_timeout=30 net_retry_count=10 # 4MB max_allowed_packet=4194304
- 設定方法
- net_buffer_length
バッファは動的に拡張されるので、通常は変更不要。
メモリが不足している場合に小さくして節約することは可能。 - net_write_timeout
データベースから巨大なデータを読み出す場合や、通信品質が悪い場合などは値を大きくする。 - net_read_timeout
データベースへ巨大なデータを送る場合や、通信品質が悪い場合などは値を大きくする。 - net_retry_count
何度も途切れてしまう場合は値を大きくする。 - max_allowed_packet
バイナリデータ(BLOB)を送るなど、大きなサイズのクエリを発行する場合は変更が必要。
- net_buffer_length
タイムアウト
connect_timeout
接続開始時に接続が完了するまでのタイムアウト時間。
- 書式
connect_timeout=<タイムアウト時間（秒）>
- デフォルト
connect_timeout=10
- 設定方法
通常はデフォルトでよい。
ネットワーク品質が悪く、再送が頻発する場合や、
max_connect_errors変数により、接続待ちが発生する場合は長く設定すると良い。
wait_timeout
対話式ではないコネクションで、通信がないコネクションを強制的に閉じるまでのタイムアウト時間（秒）
- 書式
wait_timeout=<タイムアウト時間（秒）> - デフォルト
# 8時間 wait_timeout=28800
- 設定方法
プログラム側で閉じずに空けておく必要がある場合はその時間分を設定し、
不要であれば数十秒程度でよい。
interactive_timeout
対話式のコネクションで、通信がないコネクションを強制的に閉じるまでのタイムアウト時間（秒）
- 書式
interactive_timeout=<タイムアウト時間（秒）> - デフォルト
# 8時間 interactive_timeout=28800
- 設定方法
通常は設定変更不要。
最大接続数
MySQL Serverへの最大接続数。
１接続につき１つのファイルのオープン（ファイルディスクリプタ）が必要になるので、
多数の接続を行う場合は、open_files_limit値も合わせて変更する必要がある。
- 書式
- max_connections=<最大接続数>
- デフォルト
max_connections=300
- 設定方法
最大接続待ち数
TCP接続処理を開始し、実際にMySQLへ接続を完了するまでの接続待ち（リッスン）キューのサイズ。
- 書式
- back_log=<最大接続待ち数>
- デフォルト
back_log=80
SSL
- 書式
- デフォルト
have_ssl=DISABLED ssl_ca= ssl_capath= ssl_cert= ssl_cipher= ssl_crl= ssl_crlpath= ssl_key= have_openssl=DISABLED
クエリー・コマンド
- 書式
- auto_increment_increment=1
- auto_increment_offset=1
- flush=OFF
- flush_time=0
- max_join_size=<SELECT可能行数>
select文で処理することができる最大のテーブル行数。
この変数はJOIN時に限らず、あらゆるselectステートメントで使用される。
この変数値以上を扱う場合はselect文が中断される。
ただし、sql_big_selects変数がOFFの場合のみ、この変数は有効になる。 - sql_auto_is_null=(ON | OFF)
この変数がONの時、AUTO_INCREMENTを使用したテーブルにinsertし、それが成功した場合に、
AUTO_INCREMENTを使用する列を指定したis null検索するselect文でその追加した行を取得できる。
つまり、そのテーブルで最後に追加した行を取得することができる。 - sql_big_selects=(ON | OFF)
OFFの場合、max_join_size変数で指定した行以上のテーブルを使用するselect文は中断される。 - sql_buffer_result=(ON | OFF)
ONの場合、一時テーブルにselect分の結果を格納する。
格納した時点でロックが解除されるので、結果の送出に時間がかかる場合にメリットがある。 - sql_mode=<SQLモード>
SQLのモードを選択する。 , 区切りで複数選択可能。
選択可能な値は以下。
STRICTモード
- STRICT_ALL_TABLES
行を挿入・更新しようとした時、特定の列に値が不正で格納できなかった場合、
トランザクション対応テーブルならばその挿入・更新は失敗しトランザクションはロールバックされる。
トランザクション非対応テーブルならば、その挿入・更新は失敗し、移行のSQLは実行されない。 - STRICT_TRANS_TABLES
行を挿入・更新しようとした時、特定の列に値が不正で格納できなかった場合、
トランザクション対応テーブルならばその挿入・更新は失敗しトランザクションはロールバックされ、
トランザクション非対応テーブルならば、単行のSQLまたは複数行の最初のSQLに限りその挿入・更新は失敗する。
日付関連
- ALLOW_INVALID_DATES
日付型(DATE、DATETIME。TIMESTAMPは適応外。)で不正な日付の値の代入を許可する。
例えば、2/30の様な日付である。このモードでない場合、不正な日付は0000-00-00に警告と共に変換される。
ただし、日付として使用することが無い値（13月以降、32日以降）はエラーとなり、代入できない。
※0000-00-00のみ例外的に代入できる。 - NO_ZERO_DATE
STRICTモードの場合、’0000-00-00’がエラーとなる - NO_ZERO_IN_DATE
STRICTモードの場合、月や日に0が代入されるとエラーとなる。
STRICTモードでない場合、’0000-00-00’に変換される。
処理
- ERROR_FOR_DIVISION_BY_ZERO
0除算、0剰余を行ったときの挙動を設定する。
STRICT MODE(STRICT_ALL_TABLES/STRICT_TRANS_TABLES)の状態との組み合わせによってUPDATE、INSERTの挙動が変化する。
SELECTの場合は常にNULLが返される。このパラメータが有効の場合は更に警告が出力される。
UPDATE、INSERTの挙動は以下。
・ERROR_FOR_DIVISION_BY_ZERO、STRICT MODEが共に有効
エラーが発生し、実行が中断される。
IGNORE句を付けることで、NULLを代入することが可能。
・ERROR_FOR_DIVISION_BY_ZEROが有効、STRICT MODEが無効
警告が出され、NULLが代入される。
・ERROR_FOR_DIVISION_BY_ZEROが無効、STRICT MODEは有効または無効
NULLが代入される。警告などは出力されない。 - NO_AUTO_VALUE_ON_ZERO
auto_increment列に0を代入した場合、auto_incrementの値とならずに0となる。
演算子とデータ型
- HIGH_NOT_PRECEDENCE
NOTの優先度を高く設定する。
例えば、「NOT a BETWEEN b AND c」において、HIGH_NOT_PRECEDENCEが有効な場合は、
「(NOT a) BETWEEN b AND c」と解釈され（b-c間が複数ある場合は常に真(1)を返す）、
無効な場合は、「NOT (a BETWEEN b AND c)」と解釈される（aがb-c間にないなら真(1)を返す）。 - PIPES_AS_CONCAT
演算子||を論理輪でなく文字列連結演算子として扱う
（CONCAT()関数と同じ効果となる） - REAL_AS_FLOAT
real型をfloat型として扱う
SQL分
- NO_AUTO_CREATE_USER
パスワードなしのユーザ作成を禁止する - NO_UNSIGNED_SUBTRACTION
整数の減算時に結果が0か正の数となってもunsignedを追加しない - ONLY_FULL_GROUP_BY
group by句を使用時に全ての列がmax()などの集約関数を使用していないとエラーになる
互換性
- ANSI_QUOTES
ダブルクォーテーションをシングルクォーテーションとして扱う。 - IGNORE_SPACE
関数名と関数の”(“の間の空白を無視するかどうかを設定する。
このパラメータが無効の場合は空白があるとエラーになる。 - NO_BACKSLASH_ESCAPES
バックスラッシュ”\”によるエスケープを無効化する - NO_DIR_IN_CREATE
テーブル作成時にindex directoryとdata directoryを無視する。
スレーブサーバで通常使用する。 - NO_FIELD_OPTIONS
MySQL独自の列オプションをshow create tableで出力しない。
mysqldumpのポータビリティモードで使用されている。 - NO_KEY_OPTIONS
MySQL独自のインデックスオプションをshow create tableで出力しない。
mysqldumpのポータビリティモードで使用されている。 - NO_TABLE_OPTIONS
MySQL独自のテーブルオプションをshow create tableで出力しない。 - NO_ENGINE_SUBSTITUTION
create tableやalter table文でテーブルのストレージエンジンを指定した際に、
無効なストレージエンジン名を指定した場合にエラーになり、そのSQLはエラーとなる。
このモードを設定していない場合は、default_storage_engine変数で指定したストレージエンジンに変換される。
- PAD_CHAR_TO_FULL_LENGTH
セット
- ANSI
REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE
を指定したのと同じ - DB2
PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS
を指定したのと同じ - MAXDB
PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER
を指定したのと同じ - MSSQL
PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS
を指定したのと同じ - MYSQL323
NO_FIELD_OPTIONS,HIGH_NOT_PRECEDENCE
を指定したのと同じ - MYSQL40
NO_FIELD_OPTIONS,HIGH_NOT_PRECEDENCE
を指定したのと同じ - ORACLE
PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER
を指定したのと同じ - POSTGRESQL
PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS
を指定したのと同じ - TRADITIONAL
STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
を指定したのと同じ
- sql_notes=(ON | OFF)
- sql_quote_show_create=(ON | OFF)
- sql_safe_updates=(ON | OFF)
- sql_select_limit=18446744073709551615
- sql_warnings=(ON | OFF)
- delayed_insert_limit=100
- delayed_insert_timeout=300
- delayed_queue_size=1000
- eq_range_index_dive_limit=10
- event_scheduler=(ON | OFF)
- explicit_defaults_for_timestamp=(ON | OFF)
- group_concat_max_len=1024
- have_symlink=YES
- local_infile=(ON | OFF)
- lock_wait_timeout=31536000
- low_priority_updates=(ON | OFF)
- max_delayed_threads=20
- max_insert_delayed_threads=20
- max_length_for_sort_data=1024
- max_seeks_for_key=18446744073709551615
- max_sort_length=1024
- max_write_lock_count=18446744073709551615
- metadata_locks_cache_size=1024
- metadata_locks_hash_instances=8
- old_alter_table=OFF
- range_alloc_block_size=4096
- skip_show_database=(ON | OFF)
- updatable_views_with_limit=YES
- completion_type=(NO_CHAIN | CHAIN | RELEASE)
トランザクション中にcommit、rollbackクエリ発行時の挙動を設定する。
続けて新たなトランザクションを開始する、コネクションを切断するなどが行える。
- NO_CHAIN
commit、rollbackに影響を与えない - CHAIN
commitはcommit and chain、rollbackはrollback and chainとして扱われ、
クエリ発行後に次のトランザクションが始まる - RELEASE
commit、rollback後に接続が切断される。
div_precision_increment=4
/（除算）時の小数点以下を求める桁数
end_markers_in_json=(ON | OFF)
explainの結果をjson形式で出力する際に閉じ括弧（}）の後にどのブロックであったかを表記するかどうかを設定する。
長いブロックを出力する際には読みやすくなる。
出力例(ON)
set end_markers_in_json=on; explain format=json select * from mysql.user; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "table": { "table_name": "user", "access_type": "ALL", "rows": 3, "filtered": 100 } /* table */ } /* query_block */ } | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 1 warning (0.03 sec)
- NO_CHAIN
出力例(OFF)
set end_markers_in_json=off; explain format=json select * from mysql.user; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "table": { "table_name": "user", "access_type": "ALL", "rows": 3, "filtered": 100 } } } | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 1 warning (0.00 sec)
- デフォルト
auto_increment_increment=1 auto_increment_offset=1 flush=OFF flush_time=0 max_join_size=18446744073709551615 sql_auto_is_null=OFF sql_big_selects=ON sql_buffer_result=OFF sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION sql_notes=ON sql_quote_show_create=ON sql_safe_updates=OFF sql_select_limit=18446744073709551615 sql_warnings=OFF delayed_insert_limit=100 delayed_insert_timeout=300 delayed_queue_size=1000 eq_range_index_dive_limit=10 event_scheduler=OFF explicit_defaults_for_timestamp=OFF group_concat_max_len=1024 have_symlink=YES local_infile=ON lock_wait_timeout=31536000 low_priority_updates=OFF max_delayed_threads=20 max_insert_delayed_threads=20 max_length_for_sort_data=1024 max_seeks_for_key=18446744073709551615 max_sort_length=1024 max_write_lock_count=18446744073709551615 metadata_locks_cache_size=1024 metadata_locks_hash_instances=8 old_alter_table=OFF range_alloc_block_size=4096 skip_show_database=OFF updatable_views_with_limit=YES completion_type=NO_CHAIN div_precision_increment=4 end_markers_in_json=OFF
バッファ
レコードの読み出しやSQLを実行する際に各種バッファ
query_alloc_block_size=8192
query_prealloc_size=8192
- 書式
- join_buffer_size
インデックスを使用せずにテーブルのJOINを行う際に使用されるバッファのサイズ。
- join_buffer_size
- join_buffer_size=
- preload_buffer_size
- read_buffer_size
テーブルをシーケンシャルに（インデックスを使わずに）読み込むときに一度に読み取る量。
4096(4KB)の倍数で指定する必要がある。指定しなかった場合は、指定した値より小さい、最も近い4096の倍数になる。
また他にも、一時ファイル中にorder by句で使用するインデックスをキャッシュする時、
パーティションに対してbulk insert（複数レコードを１クエリでinsert）する時、
入れ子のクエリーで結果をキャッシュする時、
更に、MOMORYテーブルのブロックサイズにも用いられる。 - read_rnd_buffer_size
インデックスを使用したソートを行う際に使用する、データ読み込みのバッファ。 - sort_buffer_size
ファイルソートを実行する時に使用するメモリのサイズを指定する。
不足する場合はテンポラリファイルを作成する。 - sql_buffer_result=(ON | OFF)
SELECTクエリの結果を返す際、一旦バッファに格納してから結果を送信する。
これによって結果を送信しきるのに時間がかかるクエリはロック解除を早く行うことができる。
- デフォルト
# 256 KB join_buffer_size=262144 preload_buffer_size=32768 # 128 KB read_buffer_size=131072 # 256 KB read_rnd_buffer_size=262144 # 256 KB sort_buffer_size=262144 sql_buffer_result=OFF
- 設定方法
トランザクション
transaction_alloc_block_size=8192
transaction_prealloc_size=4096
tx_isolation=REPEATABLE-READ
tx_read_only=OFF
クエリーオプティマイザー
optimizer_prune_level=1
optimizer_search_depth=62
optimizer_switch=index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
optimizer_trace=enabled=off,one_line=off
optimizer_trace_features=greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
optimizer_trace_limit=1
optimizer_trace_max_mem_size=16384
optimizer_trace_offset=-1
キャッシュ
テーブルキャッシュ
テーブルキャッシュとは、MySQLがデータを保存するファイルを開いたときに作成する
ファイルへのポインタを格納するキャッシュのことである。
テーブルキャッシュはテーブルキャッシュインスタンスによって管理される。
テーブルキャッシュインスタンスはテーブルキャッシュ数÷テーブルキャッシュインスタンス数で
算出される数のキャッシュを扱う。
なお、table_open_cacheの値は最低でもmax_connectionsと同数にする必要がある。
これは同一のテーブルに対しても各スレッドはそれぞれでファイルを開くためである。
- 書式
- table_open_cache=<キャッシュ数>
全スレッド合計のキャッシュ数 - table_open_cache_instances=<インスタンス数>
テーブルキャッシュインスタンス数 - table_definition_cache=<キャッシュ数>
テーブルの定義自体のキャッシュ。
新たにテーブルをオーブンする際にキャッシュがあれば利用できる。
- table_open_cache=<キャッシュ数>
- デフォルト
table_open_cache=2000 table_open_cache_instances=1 # 400 + (table_open_cache / 2) table_definition_cache=-1
- 設定方法
- table_open_cache
max_connections数×１コネクションで扱うテーブルの数 - table_open_cache_instances
CPUコア数が16以上では8か16が良い。それ以外はデフォルト。 - table_definition_cache
サーバ上のテーブル数 + max_connections + α
- table_open_cache
クエリキャッシュ
クエリキャッシュは発行されたSELECTクエリの結果をキャッシュする機能である。
キャッシュされるクエリは空白等も含めて完全に一致する場合のみ利用される。
キャッシュはクエリで利用されるテーブルに変更があった場合に破棄されるので、
必ずしも影響のある行に変更があったとは限らないが、変更検知のオーバーヘッドが少ないため、この方法が採用されている。
- 書式
- have_query_cache=(YES | NO)
このMySQLでクエリキャッシュを利用できるかどうか。
この値は変更できない。 - query_cache_type=(OFF | ON | DEMAND)
クエリキャッシュの有効/向こうを設定でき、OFF、ON、DEMANDの値をとる。
DEMANDの場合はSQLにSQL_CACHEが含まれる場合のみキャッシュする。 - query_cache_size=<キャッシュサイズ(byte)>
キャッシュに使用するメモリ量。1024の倍数で指定する必要がある。
起動中にこの値を変更すると、キャッシュは全て破棄される。
このパラメータで割り当てたメモリは起動時にすぐに確保される。 - query_cache_min_res_unit=<キャッシュサイズ(byte)>
キャッシュを保存するために使用される、メモリ上で分割されるブロックの最小サイズ。 - query_cache_limit=<キャッシュサイズ(byte)>
キャッシュを保存する結果の最大サイズ。
この値を上回った場合はキャッシュされない。 - query_cache_wlock_invalidate
テーブルがロックされているキャッシュが読み取れるかどうか。
OFFとONの値を設定でき、OFFの場合にキャッシュが読み取れ、ONの場合に読み取れなくなる。
そのため、ONにするとロック待ちが増える可能性がある。
通常はデフォルトのOFFで良い。
- have_query_cache=(YES | NO)
- デフォルト
query_cache_type=OFF # 1MB query_cache_size=1048576 # 4KB query_cache_min_res_unit=4096 # 1MB query_cache_limit=1048576 query_cache_wlock_invalidate=OFF
- 設定方法
- query_cache_type
参照が多い場合や、複雑な演算処理を行う参照を行う場合はキャッシュが有効なので、Onにする。
更新が頻発してあまりキャッシュが有効でない場合はOffにするか、
キャッシュが有効になりそうな一部のクエリを明示的にキャッシュするDemandにする。
最初に有効にしてキャッシュヒット率を見て変更しても良い。 - query_cache_size
キャッシュヒット数が想定より低い場合は
キャッシュサイズが不足している可能性があるので上げる。
- query_cache_type
スレッド
- 書式
- thread_cache_size=<スレッド数>
使用済みのスレッドはキャッシュして次回接続時に再利用できる。
この変数によってそのキャッシュの数を設定する。 - thread_stack=<スタック領域サイズ(byte)>
１つのスレッドで使用可能なスタックのサイズ。 - max_delayed_threads=<スレッド数>
insert delayedを同時に扱うことができるスレッド数を指定する。 - max_insert_delayed_threads=<スレッド数>
max_delayed_threadsの別名 - thread_concurrency=<スレッド数>
Soralis8以前のSoralis OSのみで有効な、同時稼動可能なスレッド数。 - thread_handling=(no-threads | one-thread-per-connection | dynamically-loaded)
スレッドの扱い方について指定する。通常変更は不要。
- no-threads
デバッグ用途なので使用しない - one-thread-per-connection
１コネクションに対して１スレッドを割り当てる。 - dynamically-loaded
スレッドプールプラグインが有効になっていると、この値になる。
- no-threads
- slow_launch_time=<秒数>
スレッド作成にここで指定した秒数以上かかった場合、Slow_launch_threadsステータス値を1増やす。
- thread_cache_size=<スレッド数>
- デフォルト
thread_cache_size=9 # 256 KB thread_stack=262144 max_delayed_threads=20 max_insert_delayed_threads=20 thread_concurrency=10 thread_handling=one-thread-per-connection slow_launch_time=2
- 設定方法
- thread_cache_size
同時接続数や、タイムアウト時間などを考慮して決定する。
キャッシュされたスレッドは再利用されない限りキャッシュからなくなることはないので、
この値で使用したメモリは基本的に全て使用されてもよいと考える。
max_connectionsより大きな設定する必要はない。
休眠スレッドは数百KBのメモリを消費する程度なので、初めは大きめの値に設定しても通常問題ない。
ステータスのThreads_created値が急増している場合は大きくするとよい。
- thread_cache_size
ストレージエンジン
default_storage_engine=InnoDB
storage_engine=InnoDB
MEMORYストレージエンジン
- 書式
- max_heap_table_size
MEMORYストレージエンジン使用して作成できるテーブルの最大サイズ。
- max_heap_table_size
- デフォルト
# 16MB max_heap_table_size=16777216
- 設定方法
- max_heap_table_size
通常はデフォルトで変更不要。
大きなサイズのMOMORYテーブルを使用する場合は大きな値に変更する。
- max_heap_table_size
MyISAMストレージエンジン
myisam_data_pointer_size=6
myisam_max_sort_file_size=9223372036853727232
myisam_mmap_size=18446744073709551615
myisam_recover_options=OFF
myisam_repair_threads=1
myisam_sort_buffer_size=8388608
myisam_stats_method=nulls_unequal
myisam_use_mmap=OFF
key_cache_age_threshold=300
key_cache_block_size=1024
key_cache_division_limit=100
key_buffer_size=8388608
ステータスのKey_read_requests/Key_reads、Key_write_requests/Key_writesでチューニング
concurrent_insert=AUTO
delay_key_write=ON
have_rtree_keys=YES
keep_files_on_create=OFF
read_rnd_buffer_size=262144
skip_external_locking=ON
key_buffer_size=8388608
- 書式
- bulk_insert_buffer_size
- bulk_insert_buffer_size
- デフォルト
- bulk_insert_buffer_size=8388608
- key_buffer_size
インデックス用のバッファサイズを指定する
- key_buffer_size=
InnoDB
バッファ
- 書式
- innodb_buffer_pool_size=<キャッシュサイズ(byte)>
インデックスとレコードの両方で使用するキャッシュのサイズ。 - innodb_buffer_pool_instances=<インスタンス数>
バッファをいくつのインスタンスに分割して扱うかを指定する。
バッファサイズが巨大な場合、分割することで、並列処理性能が向上する。 - innodb_buffer_pool_dump_now=(ON | OFF)
この変数の値がOFFからONに変更されたタイミングでバッファプールのダンプを保存する。 - innodb_buffer_pool_load_now=(ON | OFF)
この変数の値がOFFからONに変更されたタイミングで保存したバッファプールのダンプを読み込む。 - innodb_buffer_pool_dump_at_shutdown=(ON | OFF)
MySQLのシャットダウン時にバッファを保存しておくかどうかを指定する。
起動時に保存したバッファを読み込むかどうかはinnodb_buffer_pool_load_at_startupで指定する。 - innodb_buffer_pool_load_at_startup=(ON | OFF)
MySQLの起動時に保存したバッファを読み込むかどうかを指定する。
終了時にバッファを保存するどうかはinnodb_buffer_pool_dump_at_shutdownで指定する。 - innodb_buffer_pool_filename=<パス>
innodb_buffer_pool_dump_at_shutdownやinnodb_buffer_pool_dump_nowによって保存される
バッファプールの保存場所を指定する。 - innodb_buffer_pool_load_abort=(ON | OFF)
innodb_buffer_pool_load_at_startupやinnodb_buffer_pool_load_nowによって行われている
バッファの読み込みを中断する。 - innodb_change_buffer_max_size=<使用割合>
バッファプール中でチェンジバッファとして使用する割合を使用する。 - innodb_change_buffering=(inserts | deletes | purges | changes | all | none)
チェンジバッファ（セカンダリーインデックスのディスク書き込みをバッファしてテーブル書き込みとまとめて行う）を使用するクエリーを選択する。
チェンジバッファによってメモリが一定量確保されるので、セカンダリーインデックスをあまり使用しない場合はチェンジバッファを使用しなければメモリを節約できる。
参考：http://dev.mysql.com/doc/refman/5.5/en/innodb-performance.html#innodb-performance-change_buffering
以下の値が設定可能である。なお、updateはdeleteとinsertと分割するものとして扱われる。
inserts : insertのみバッファを使用する。
deletes : delete（インデックスが仕様不可となったマークをつける処理）でバッファを使用する。
purges : purge（インデックスを実際にディスクから削除する処理）でバッファを使用する。
changes : insertとdeleteでバッファを使用する。
all : 全ての処理でバッファを使用する。
none : バッファは使用しない
- innodb_buffer_pool_size=<キャッシュサイズ(byte)>
- デフォルト
innodb_buffer_pool_dump_at_shutdown=OFF innodb_buffer_pool_dump_now=OFF innodb_buffer_pool_filename=ib_buffer_pool innodb_buffer_pool_instances=8 innodb_buffer_pool_load_abort=OFF innodb_buffer_pool_load_at_startup=OFF innodb_buffer_pool_load_now=OFF innodb_buffer_pool_size=134217728 innodb_change_buffer_max_size=25 innodb_change_buffering=all
- 設定方法
- innodb_buffer_pool_size
InnoDBを中心に使用する場合はできるだけ多くのメモリを割り当てる。
MySQLで使用するメモリの６～８割程度でも良い。 - innodb_buffer_pool_instances
基本的に値が大きい方がパフォーマンスは向上する。
ただし、innodb_buffer_pool_sizeの数をこの設定値で割った値が少なくとも1GBを超えるように設定する。
参考：http://dev.mysql.com/doc/innodb/1.1/en/innodb-multiple-buffer-pools.html - innodb_buffer_pool_dump_at_shutdown
起動直後でも終了前と同じパフォーマンスを保てるので、ONが良い。 - innodb_buffer_pool_load_at_startup
- innodb_buffer_pool_dump_at_shutdownと同じにする。
- innodb_buffer_pool_dump_now
初期状態ではOFFにしておく。 - innodb_buffer_pool_load_now
初期状態ではOFFにしておく。 - innodb_buffer_pool_load_abort
初期状態ではOFFにしておく。 - innodb_change_buffer_max_size
- innodb_change_buffering
- innodb_buffer_pool_size
ログ
Redoログ（クラッシュリカバリ用）の各種設定を行う
- 書式
- innodb_log_buffer_size=<バッファサイズ(byte)>
InnoDBがログのディスク書き込みに使用するバッファのサイズ。
commitまでに長いトランザクションや多数の行の変更を実行する場合は相応のバッファサイズが必要。 - innodb_log_compressed_pages=(ON|OFF)
InnoDBのRedoログに圧縮した状態のページを書き出すかどうかを指定する。
これによってzlibのバージョンが変更されてもredo（クラッシュリカバリ）が可能となる。
OFFにした場合はログ量を減らせるが、zlibのバージョン変更時にリカバリができない可能性がある。
innodb_log_file_size=<ファイルサイズ(byte)>
各ログファイルのサイズを指定する。
なお、innodb_log_file_size*innodb_log_files_in_groupの値が512GBを超えることはできない。 - innodb_log_files_in_group=(2..100)
作成されるログファイルの数を指定する。各ファイルはib_logfileNの名前で作成される。
各ログファイルはib_logfile1から順にinnodb_log_file_sizeで指定したサイズに達するまで書き込まれ、
ib_logfile2、～、ib_logfileN、ib_logfile1とローテーションする。 - innodb_log_group_home_dir=<ログファイルパス>
ログファイルを保存するパス
- innodb_log_buffer_size=<バッファサイズ(byte)>
- デフォルト
# 8MB innodb_log_buffer_size=8388608 innodb_log_compressed_pages=ON # 48MB innodb_log_file_size=50331648 innodb_log_files_in_group=2 innodb_log_group_home_dir=./
- 設定方法
- innodb_log_buffer_size
commitまでに長いトランザクションや多数の行の変更を実行する場合はバッファサイズの追加を行う。 - innodb_log_compressed_pages
zlibのバージョンアップをしない場合はOFFにした方がログ書き込みの負荷が減る。 - innodb_log_file_size
必要量はステータスで確認できる、(Log sequence numberの値 – Last checkpoint atの値)の値である。
ピーク時のこの値にある程度余裕を持たせれば、突発的な負荷増大にも十分に耐えうる。
ただし、ログファイルが限界値まで書き込まれた後に次のログファイルへと移る処理は
オーバーヘッドとなるため、可能な限り大きくし、ログファイルの切り替え処理回数を減らしたほうがよい。
参考:http://nippondanji.blogspot.jp/2009/01/innodb.html - innodb_log_files_in_group
通常はデフォルトの2で問題ない。これによってinnodb_log_file_sizeで指定した2倍の容量が確保される。 - innodb_log_group_home_dir
デフォルトで問題ない
- innodb_log_buffer_size
ダーティページ
ダーティページのディスクへの書き込み方式は旧来では以下のようなものであった。
・バッファ中のダーティページの割合を毎秒計測し、一定以上にならないようにディスクに書き込む
・redo logは決まった容量で、最後まで書き込むと先頭に戻って再度上書きしていくがredo logの残量がなくなり、上書きを行うタイミングで全てディスクへ書き込む
後者が発生すると、ディスクへ一度に全て書き込むため瞬間的に書き込み負荷が高まってしまう(sharp checkpoint)。
新方式では上記に加え、redo logの残量を計測して徐々にディスクに書き込む(adaptive flushing)。
新方式によって負荷の高まりに影響なくディスク書き込みの量が平準化されるので、スループットが上昇する。
- 書式
- innodb_max_dirty_pages_pct=<ダーティページ割合（％）>
バッファ中のダーティページの割合が、ここで設定した値を超えないように
ダーティページの書き込みが行われる。 - innodb_max_dirty_pages_pct_lwm=<ダーティページ割合（％）>
バッファ中のダーティページの割合が、ここで設定した値を超えなければ
ダーティページの書き込みは行われない。
つまり、innodb_max_dirty_pages_pct_lwm ～ innodb_max_dirty_pages_pctの間で
ダーティページの書き込みが行われる。 - innodb_adaptive_flushing=(ON | OFF)
adaptive flushingを使用するかどうか。 - innodb_adaptive_flushing_lwm=<redo log残量（％）>
使用しているredo logの容量が指定した割合を下回った場合にadaptive flushingを開始する。
- innodb_max_dirty_pages_pct=<ダーティページ割合（％）>
- デフォルト
innodb_max_dirty_pages_pct=75 innodb_max_dirty_pages_pct_lwm=0 innodb_adaptive_flushing=ON innodb_adaptive_flushing_lwm=10
- 設定方法
innodbのステータスを以下コマンドで出力する。
- show engine innodb status\G
LOGセクションに表示されるLog sequence numberの値とLast checkpoint atの値、
innodb_log_file_size変数とinnodb_log_files_in_group変数を使用してredo logの残量を観察できる。
<redo logサイズ(byte)> = innodb_log_file_size値 * innodb_log_files_in_group値 - (innodbが安全のために自動で確保するマージン(少量)) <redo log残量(byte)> = <redo logサイズ> - (<Log sequence number値> - <Last checkpoint at値>) <redo log残量(%)> = <redo log残量(byte)> / <redo logサイズ(byte)> * 100
上記の<redo log残量(%)>がinnodb_adaptive_flushing_lwmを下回った場合にadaptive flushingを開始する。
innodb_adaptive_flushing_lwmを下回ってから0%になるまですぐの場合は、sharp checkpointが発生してしまうので、
早めににadaptive flushingを開始するように調整したほうが良い。
ただし、redo logのスペース消化速度が一定の場合はこれらの変数を調整するより、innodb_log_file_size及びinnodb_log_files_in_groupを大きくしたほうが良い。
スレッド
- 書式
- innodb_thread_concurrency=<スレッド数>
InnoDBを同時に使用できるスレッドの数を指定する。
0の場合は無限。 - innodb_concurrency_tickets=<スレッド数>
InnoDBを使用開始したスレッドが処理できる数を指定する。
ここで指定した処理を終えても行うべき処理が残っている場合は、再度InnoDBを使用するキューで待機する必要がある。 - innodb_thread_sleep_delay=<スリープ時間（マイクロ秒）>
innodb_concurrency_ticketsで指定した回数処理を行ったスレッドが、再度処理待ちのキューに入れられる時に、一旦スリープする時間。
ただし、innodb_adaptive_max_sleep_delayで設定した値を上限として、現在のスレッドの動作状況を見て自動である程度変動する。 - innodb_adaptive_max_sleep_delay=<最大スリープ時間（マイクロ秒）>
innodb_thread_sleep_delayを自動で変動させる際に使用する最大値。 - innodb_commit_concurrency=<スレッド数>
同時にコミット処理ができるスレッド数を指定する。
0の場合、制限はない。
- innodb_thread_concurrency=<スレッド数>
- デフォルト
innodb_thread_concurrency=0 innodb_concurrency_tickets=5000 innodb_adaptive_max_sleep_delay=150000 innodb_commit_concurrency=0
- 設定方法
- innodb_thread_concurrency
（CPUの数＋ディスクの数）×2が推奨されている - innodb_concurrency_tickets
- innodb_adaptive_max_sleep_delay
- innodb_commit_concurrency
- innodb_thread_concurrency
圧縮
InnoDBはテーブルデータやインデックスをディスク上のみでなく、メモリ上でも圧縮して保持する。
これは透過的に行われ、ユーザーは特に意識せずに圧縮を使用できる。
圧縮はページ単位で行われ、データ追加や変更に伴う圧縮後のデータサイズ増加に対応するために各ページは予備の領域（パディング）を持つ。
パディングは不足した場合に一定量まで増量できるが、その全領域を使い果たしたときページは分割され、２つのページになる。
この事象はINFORMATION_SCHEMA.INNODB_CMPテーブルのCOMPRESS_OPSの値がCOMPRESS_OPS_OKを超えているかで確認できる。
参考：http://dev.mysql.com/doc/refman/5.6/en/innodb-compression.html
- 書式
- innodb_compression_level=<圧縮レベル>
zlibによる圧縮を行う際にzlibで使用する圧縮レベルを指定する
0～9を指定可能で、0が圧縮無効、1から数値が大きくなるにつれて圧縮率が高まるが、速度が遅くなる。 - innodb_compression_failure_threshold_pct=<>
圧縮失敗（領域の不足）と判断し、パディング領域の増量処理を開始する閾値。
0の場合は、パディング領域の増量処理を行わず、常にページ分割を行う。 - innodb_compression_pad_pct_max=<圧縮後のページサイズに対する最大割合>
パディングとして確保できるサイズを指定する。指定は圧縮後のページサイズに対する割合で行う。
つまり、圧縮後が100KBのページは、この値が50の時、パディングとして50KB確保可能である。
innodb_compression_failure_threshold_pctが0の場合は無効となる。 - innodb_cmp_per_index_enabled=(ON | OFF)
INFORMATION_SCHEMAでINNODB_CMP_PER_INDEXテーブルの統計値を記録するかどうかを指定する。
この統計値の算出はリソースを多く使うため、機能のオンオフが指定できるようになっている。
なお、INNODB_CMP_PER_INDEXテーブルには各テーブルの圧縮試行回数や圧縮失敗率などが保存される。
- innodb_compression_level=<圧縮レベル>
- デフォルト
innodb_compression_failure_threshold_pct=5 innodb_compression_level=6 innodb_compression_pad_pct_max=50 innodb_cmp_per_index_enabled=OFF
- 設定方法
- innodb_compression_failure_threshold_pct
- innodb_compression_level
- innodb_compression_pad_pct_max
- innodb_cmp_per_index_enabled
memcachedインタフェース
- innodb_api_bk_commit_interval=<コミット間隔（秒）>
- InnoDB memcachedインタフェースにおいてアイドル状態にあるコネクションで自動でコミットを行う間隔を指定する。
- innodb_api_disable_rowlock=<>
- innodb_api_enable_binlog=<>
- innodb_api_enable_mdl=<>
- innodb_api_trx_level=<>
テーブルフォーマット
- 書式
- innodb_file_format=(Antelope | Barracuda)
新しくテーブルを作成するときに指定できるテーブル形式を設定する。
指定はROW_FORMATオプションをcreate tableステートメントで使用する。
既存のテーブルに新しい形式を反映させたい場合はALTER TABLEを実行する必要がある。
このオプションはinnodb_file_per_table変数がONの時のみ有効である。
- Antelope
redundant row formatとcompact row formatをサポートする。
redundant row formatとは、MySQLの初期に使用されていたフォーマットで、現在では互換目的以外で特に使用するメリットはない。
compact row formatとは、null値や可変長の値をとる列で、よりデータサイズを小さく保持できるフォーマットである。
Antelopeではインデックスに最大で各列の最初の768byteしか格納できない。 - Barracuda
compressed row formatとdynamic row formatをサポートする。
compressed row formatとはデータとインデックスの圧縮を行うことができるフォーマットである。
dynamic row formatとは圧縮は行わないが、BLOBや大きなサイズのテキストの保持方法が最適化される（行データとは別のページで別途管理される）フォーマットである。
- Antelope
- innodb_file_format_check=<>
- innodb_file_format_max=(Antelope | Barracuda)
system tablespace（共用のtablespace）で使用するテーブルのフォーマット。
また、フォーマット名の頭文字をアルファベット順に順序化し、使用できるフォーマットをここで使用したもの以下に限定する。
つまり、Barracudaを指定した場合は、AntelopeとBarracudaが使用でき、Antelopeを指定した場合はAntelopeのみが使用できる。 - innodb_file_per_table=(ON | OFF)
File-Per-Tableモードの有効・無効を設定する。
有効にすると、テーブルデータ・インデックスが１テーブル毎にファイル保存され、
テーブルスペース（テーブルのデータディクショナリー）も１テーブル毎に作成される。
参考:http://dev.mysql.com/doc/refman/5.6/en/innodb-multiple-tablespaces.html
有効であると以下のメリットがある。
・テーブルの破棄が高速になる
・テーブルのファイルパスを指定して作成ができる（CREATE TABLE … DATA DIRECTORY = absolute_path_to_directory）
・OPTIMIZE TABLEステートメントが使用できる
・テーブルファイルのコピーや移動ができる。また各テーブルのファイルサイズが分かる。
・Barracudaファイルフォーマットが使用できる
・dynamic row formatが使用できる
・クラッシュ時にバイナリログから復旧できない場合でもデータ復旧できる可能性が高まる
・innodb_flush_methodをO_DIRECTに設定している場合は１ファイルへの同時書き込みができないが、各テーブルを別ファイルに分けるのでパフォーマンスが向上する
・データサイズの上限が、データベース全体でなく、各テーブルで64TBとなる
また、デメリットは以下である
・使用されないテーブルにもテーブルスペースが用意されるため、リソースが無駄になる
・fsync処理が各ファイルごとに行う必要があり、I/O処理の回数が増える
・ファイルハンドラの数が多くなり、パフォーマンスに影響を与える
・フラグメンテーションによるパフォーマンスへの影響がより大きいので、フラグメンテーションの適切な対処が必要となる
・大きなサイズのテーブルスペースの破棄に時間がかかる
・innodb_autoextend_increment変数を設定しても個別のテーブルスペースには適用されない
- innodb_file_format=(Antelope | Barracuda)
- デフォルト
innodb_file_format=Antelope innodb_file_format_check=ON innodb_file_format_max=Antelope innodb_file_per_table=ON
- 設定方法
- innodb_file_format
- innodb_file_format_check
- innodb_file_format_max
- innodb_file_per_table
ファイル
- 書式
- innodb_open_files=<テーブルファイル数>
一度に開けるInnoDBのテーブルファイルの数を指定する。
innodb_file_per_table変数がONである場合にのみ、この変数は有用である。
なお、open_files_limit変数によって影響を受けない。 - innodb_data_file_path=<ファイル設定>[;<ファイル設定>…]
InnoDBで使用するテーブルスペースファイルのパスやサイズ等を指定する。
次の書式でファイル設定を指定する。
<ファイルパス>:<ファイルサイズ>[:autoextend[:max:<最大ファイルサイズ>]]
ファイルパスはデータファイルの保存パスを記述する。
ファイルサイズはそのファイルのサイズを指定する。単位として、K、M、Gを使用できる。
autoextend指定すると、サイズが不足すると自動拡張。ただし最大は2Gである。
max指定すると、autoextendで自動拡張される際の最大サイズを指定できる。
ファイル設定は ; 区切りで複数記述することができる。
ただし、autoextend、maxは最後のファイル設定でしか指定できない。 - innodb_data_home_dir=<パス>
system tablespaceで使用するデータファイルのパスの共通部分を指定する。
この変数で指定した値とinnodb_data_file_path変数で指定した値でパスが決定するが、
この変数に空文字を指定することで、innodb_data_file_pathのみでパスを決定することができる。
- innodb_open_files=<テーブルファイル数>
- デフォルト
innodb_open_files=2000 innodb_data_file_path=ibdata1:12M:autoextend innodb_data_home_dir=
- 設定方法
- innodb_open_files
- innodb_data_file_path
- innodb_data_home_dir
チェックサム
- innodb_checksum_algorithm=(innodb | crc32 | none | strict_innodb | strict_crc32 | strict_none)
テーブルスペース内のデータのチェックサムの生成・検証方法を指定する。
参考:http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html#innodb-crc32-checksum
・innodb
innodb独自の計算方法を用いる。
・crc32
crc32アルゴリズムを使用する。
ハードウェアによるサポートが得られる場合があり、innodbより高速である。
MySQL Enterprise Backupに対応していない。
・none
チェックサム計算は行わない
・strict_*
計算方式は上記と同じであるが、読み取り時にデータ書き込み時からアルゴリズムの変更が行われているとデータ読み込みに失敗する。
そのため、strict_*はサーバインストール直後に設定し、以降変更するべきではない。
バージョン互換のための旧バージョンでのチェックサムの算出を行わないため、strict_*の方が高速である。 - innodb_checksums=(ON | OFF)
チェックサムを用いたデータ破損のチェックを行うかどうかを指定する。
チェックサムアルゴリズムを指定する場合（innodb_checksum_algorithm）はこの変数は指定しない。
この変数がONの時、innodb_checksum_algorithmはinnodbを指定したものとされ、OFFの時はnoneを指定したものとされる。
- デフォルト
innodb_checksum_algorithm=innodb innodb_checksums=ON
- 設定方法
- innodb_checksum_algorithm
- innodb_checksums
- 書式
autocommit=(OFF | ON)
autocommit=ON
各SQL実行毎に自動でCOMMITするかどうか。
OFFにした場合は明示的にトランザクションの開始と終了を実行する必要がある。
- innodb_adaptive_hash_index=(ON | OFF)
適応ハッシュインデックスを有効にするかどうかを指定する。
適応ハッシュインデックスでは作成されたB木インデックスのうち、頻繁に使うものをメモリ上のハッシュインデックスに保存し、
より高速にデータへアクセスできるようにする機能である。 - innodb_additional_mem_pool_size=<プールサイズ(byte)>
データ定義用のプールサイズが不足した際にOSから追加するメモリの量。
この追加が発生したとき、エラーログにメッセージが出力される。 - innodb_autoextend_increment=<増加サイズ（MByte）>
InnoDBのシステム用テーブル領域がいっぱいになり、サイズを拡張するときに一度に拡張されるサイズ。
システム用テーブル領域はメタデータやundo logなどで使用される、各innodbテーブルごとに用意される領域である。
初期のサイズはinnodb_file_per_tableで決まる。 - innodb_autoinc_lock_mode=(0 | 1 | 2)
AUTO_INCREMENTを使用するときはAUTO-INCロックと呼ばれる特殊なテーブルレベルロックを使用する。
このロックは通常、トランザクションが終了するまでではなくステートメントが終了するまで保持される。
この変数はそのロックの挙動を設定する。0、1、2が設定可能で、それぞれ以下の意味を持つ。
参考：http://dev.mysql.com/doc/refman/5.1-olh/ja/innodb-auto-increment-configurable.html
・0(“traditional” lock mode) : REPLACE … SELECT、LOAD DATAを含む全てのINSERT系処理ででAUTO-INCロックを取得する。AUTO_INCREMENT値の一意性も連続して付与されることも保証される。
・1(“consecutive” lock mode) : INSERT … SELECTのような複数行のINSERTを行う可能性があるINSERTではない、単純な１行のみを挿入するINSERT以外の全てのINSERT系処理ででAUTO-INCロックを取得する。
単純な１行のみを挿入するINSERTでは行ロックを使用するが、挿入するテーブルでAUTO-INCロックがされていた場合は、自身もAUTO-INCテーブルロックを行い、ロック待ちをする。
AUTO_INCREMENT値の一意性は保証されるが、連続しているかどうかは一部の例外では保証されない。0(“traditional” lock mode)よりも高速であるが、
複数行のINSERTでAUTO_INCREMENTを指定した列に値を指定して挿入しようとした場合は値が連続にならない。これはAUTO_INCREMENTによる値を使用するかどうかにかかわらず、値が生成されるためである。
・2(“interleaved” lock mode) : AUTO-INCロックは使用されないため高速である。AUTO_INCREMENT値の一意性は保証されるが、連続しているかどうかは保証されない。
- innodb_disable_sort_file_cache=<>
- innodb_doublewrite=<>
- innodb_fast_shutdown=<>
- innodb_flush_log_at_timeout=<>
- innodb_flush_log_at_trx_commit=(0 | 1 | 2)
コミット時にログに書き込む方法を指定する。
・0:１秒間に一回ログへの書き込みを行う。
つまり、クラッシュした場合は最大１秒間のデータが喪失する。
その分パフォーマンスは良い。
・1:コミット時は毎回ログに書き出す。
・2:コミット時は毎回ログに書き出す。
ただし、Flush（ファイルシステムからディスクへの物理的な書き込み）は１秒間に１回行う。
つまり、ディスクがクラッシュした場合は最大１秒間のデータが喪失する。
MySQLプロセスがクラッシュした場合は問題ない。
- innodb_flush_method=<>
- innodb_flush_neighbors=<>
- innodb_flushing_avg_loops=<>
- innodb_force_load_corrupted=<>
- innodb_force_recovery=<>
- innodb_ft_aux_table=<>
- innodb_ft_cache_size=<>
- innodb_ft_enable_diag_print=<>
- innodb_ft_enable_stopword=<>
- innodb_ft_max_token_size=<>
- innodb_ft_min_token_size=<>
- innodb_ft_num_word_optimize=<>
- innodb_ft_result_cache_limit=<>
- innodb_ft_server_stopword_table=<>
- innodb_ft_sort_pll_degree=<>
- innodb_ft_total_cache_size=<>
- innodb_ft_user_stopword_table=<>
- innodb_io_capacity=<>
- innodb_io_capacity_max=<>
- innodb_large_prefix=<>
- innodb_lock_wait_timeout=<>
- innodb_locks_unsafe_for_binlog=<>
- innodb_lru_scan_depth=<>
- innodb_max_purge_lag=<>
- innodb_max_purge_lag_delay=<>
- innodb_mirrored_log_groups=<>
- innodb_monitor_disable=<>
- innodb_monitor_enable=<>
- innodb_monitor_reset=<>
- innodb_monitor_reset_all=<>
- innodb_old_blocks_pct=<>
- innodb_old_blocks_time=<>
- innodb_online_alter_log_max_size=<>
- innodb_optimize_fulltext_only=<>
- innodb_page_size=<>
- innodb_print_all_deadlocks=<>
- innodb_purge_batch_size=<>
- innodb_purge_threads=<>
- innodb_random_read_ahead=<>
- innodb_read_ahead_threshold=<>
- innodb_read_io_threads=<>
- innodb_read_only=<>
- innodb_replication_delay=<>
- innodb_rollback_on_timeout=<>
- innodb_rollback_segments=<>
- innodb_sort_buffer_size=<>
- innodb_spin_wait_delay=<>
- innodb_stats_auto_recalc=<>
- innodb_stats_method=<>
- innodb_stats_on_metadata=<>
- innodb_stats_persistent=<>
- innodb_stats_persistent_sample_pages=<>
- innodb_stats_sample_pages=<>
- innodb_stats_transient_sample_pages=<>
- innodb_strict_mode=<>
- innodb_support_xa=<>
- innodb_sync_array_size=<>
- innodb_sync_spin_loops=<>
- innodb_table_locks=<>
- innodb_undo_directory=<>
- innodb_undo_logs=<>
- innodb_undo_tablespaces=<>
- innodb_use_native_aio=<>
- innodb_use_sys_malloc=<>
- innodb_version=<>
- innodb_write_io_threads=<>
- ignore_builtin_innodb=<>
- stored_program_cache=<>
- foreign_key_checks=<>
- timed_mutexes=<>
使用すべきではない。
Controls whether innodb mutexes are timed out.
- unique_checks
- デフォルト
innodb_adaptive_hash_index=ON
innodb_additional_mem_pool_size=8388608
innodb_api_bk_commit_interval=5
innodb_api_disable_rowlock=OFF
innodb_api_enable_binlog=OFF
innodb_api_enable_mdl=OFF
innodb_api_trx_level=0
innodb_autoextend_increment=64
innodb_autoinc_lock_mode=1
innodb_disable_sort_file_cache=OFF
innodb_doublewrite=ON
innodb_fast_shutdown=1
innodb_flush_log_at_timeout=1
innodb_flush_log_at_trx_commit=1
innodb_flush_method=
innodb_flush_neighbors=1
innodb_flushing_avg_loops=30
innodb_force_load_corrupted=OFF
innodb_force_recovery=0
innodb_ft_aux_table=
innodb_ft_cache_size=8000000
innodb_ft_enable_diag_print=OFF
innodb_ft_enable_stopword=ON
innodb_ft_max_token_size=84
innodb_ft_min_token_size=3
innodb_ft_num_word_optimize=2000
innodb_ft_result_cache_limit=2000000000
innodb_ft_server_stopword_table=
innodb_ft_sort_pll_degree=2
innodb_ft_total_cache_size=640000000
innodb_ft_user_stopword_table=
innodb_io_capacity=200
innodb_io_capacity_max=2000
innodb_large_prefix=OFF
innodb_lock_wait_timeout=50
innodb_locks_unsafe_for_binlog=OFF
innodb_lru_scan_depth=1024
innodb_max_purge_lag=0
innodb_max_purge_lag_delay=0
innodb_mirrored_log_groups=1
innodb_monitor_disable=
innodb_monitor_enable=
innodb_monitor_reset=
innodb_monitor_reset_all=
innodb_old_blocks_pct=37
innodb_old_blocks_time=1000
innodb_online_alter_log_max_size=134217728
innodb_optimize_fulltext_only=OFF
innodb_page_size=16384
innodb_print_all_deadlocks=OFF
innodb_purge_batch_size=300
innodb_purge_threads=1
innodb_random_read_ahead=OFF
innodb_read_ahead_threshold=56
innodb_read_io_threads=4
innodb_read_only=OFF
innodb_replication_delay=0
innodb_rollback_on_timeout=OFF
innodb_rollback_segments=128
innodb_sort_buffer_size=1048576
innodb_spin_wait_delay=6
innodb_stats_auto_recalc=ON
innodb_stats_method=nulls_equal
innodb_stats_on_metadata=OFF
innodb_stats_persistent=ON
innodb_stats_persistent_sample_pages=20
innodb_stats_sample_pages=8
innodb_stats_transient_sample_pages=8
innodb_strict_mode=OFF
innodb_support_xa=ON
innodb_sync_array_size=1
innodb_sync_spin_loops=30
innodb_table_locks=ON
innodb_thread_sleep_delay=10000
innodb_undo_directory=.
innodb_undo_logs=128
innodb_undo_tablespaces=0
innodb_use_native_aio=OFF
innodb_use_sys_malloc=ON
innodb_version=5.6.15
innodb_write_io_threads=4
ignore_builtin_innodb=OFF
stored_program_cache=256
foreign_key_checks=ON
timed_mutexes=OFF
使用すべきではない。Controls whether innodb mutexes are timed out.
unique_checks=ON
- 設定方法
- innodb_adaptive_flushing
- innodb_adaptive_flushing_lwm
- innodb_adaptive_hash_index
- innodb_additional_mem_pool_size
- innodb_api_bk_commit_interval
- innodb_api_disable_rowlock
- innodb_api_enable_binlog
- innodb_api_enable_mdl
- innodb_api_trx_level
- innodb_autoextend_increment
- innodb_autoinc_lock_mode
- innodb_disable_sort_file_cache
- innodb_doublewrite
- innodb_fast_shutdown
- innodb_flush_log_at_timeout
- innodb_flush_log_at_trx_commit
通常は安定性を考慮して1とすべきであるが、スレーブサーバなど安定性が重要でない場合は
パフォーマンスを選択して値を変更するとよい。 - innodb_flush_method
- innodb_flush_neighbors
- innodb_flushing_avg_loops
- innodb_force_load_corrupted
- innodb_force_recovery
- innodb_ft_aux_table
- innodb_ft_cache_size
- innodb_ft_enable_diag_print
- innodb_ft_enable_stopword
- innodb_ft_max_token_size
- innodb_ft_min_token_size
- innodb_ft_num_word_optimize
- innodb_ft_result_cache_limit
- innodb_ft_server_stopword_table
- innodb_ft_sort_pll_degree
- innodb_ft_total_cache_size
- innodb_ft_user_stopword_table
- innodb_io_capacity
- innodb_io_capacity_max
- innodb_large_prefix
- innodb_lock_wait_timeout
- innodb_locks_unsafe_for_binlog
- innodb_lru_scan_depth
- innodb_max_dirty_pages_pct
- innodb_max_dirty_pages_pct_lwm
- innodb_max_purge_lag
- innodb_max_purge_lag_delay
- innodb_mirrored_log_groups
- innodb_monitor_disable
- innodb_monitor_enable
- innodb_monitor_reset
- innodb_monitor_reset_all
- innodb_old_blocks_pct
- innodb_old_blocks_time
- innodb_online_alter_log_max_size
- innodb_optimize_fulltext_only
- innodb_page_size
- innodb_print_all_deadlocks
- innodb_purge_batch_size
- innodb_purge_threads
- innodb_random_read_ahead
- innodb_read_ahead_threshold
- innodb_read_io_threads
- innodb_read_only
- innodb_replication_delay
- innodb_rollback_on_timeout
- innodb_rollback_segments
- innodb_sort_buffer_size
- innodb_spin_wait_delay
- innodb_stats_auto_recalc
- innodb_stats_method
- innodb_stats_on_metadata
- innodb_stats_persistent
- innodb_stats_persistent_sample_pages
- innodb_stats_sample_pages
- innodb_stats_transient_sample_pages
- innodb_strict_mode
- innodb_support_xa
- innodb_sync_array_size
- innodb_sync_spin_loops
- innodb_table_locks
- innodb_thread_concurrency
- innodb_thread_sleep_delay
- innodb_undo_directory
- innodb_undo_logs
- innodb_undo_tablespaces
- innodb_use_native_aio
- innodb_use_sys_malloc
- innodb_version
- innodb_write_io_threads
- ignore_builtin_innodb
- stored_program_cache
- foreign_key_checks
- timed_mutexes
- unique_checks
ログ
SQL発行状況など各種ログを保存するための設定。
デフォルトでは大抵のログは保存されないので、ログが必要な場合は設定が必要である。
一般ログ
全てのSQLを記録する。
全てログを保存するとI/O負荷が高まるため、一時的にしか使用しない方がよい。
- 書式
- general_log=<ON | OFF>
- general_log_file=<ログファイルパス>
- log_output=<TABLE | FILE | NONE>[,<TABLE | FILE>]
ログの保存先を指定する。
,区切りでテーブルとファイルと両方を指定することができる。
NONEの場合は保存されない。
なお、この設定はスロークエリログにも影響する。
- デフォルト
- general_log=OFF
- general_log_file=<datadir/ホスト名.log>
- log_output=FILE
エラーログ
エラーログにはMySQLの起動・終了、エラーメッセージなどが出力される。
- 書式
- log_error[=<ログファイルパス>]
エラーログの出力先を指定する。
パスを省略した場合は標準出力に出力される。 - log_warnings=<0 | 1 | 2>
warningレベルのログの出力の設定を行う。
設定は以下の値から行う。
- 0
warningの出力を無効にする。 - 1
warningの出力を有効にする。
コネクションの中断、アクセスの拒否は出力しない。 - 2以上
warningの出力を有効にする。
コネクションの中断、アクセスの拒否も出力する。
- 0
- log_error[=<ログファイルパス>]
- デフォルト
log_error=<datadir/ホスト名.err> log_warnings=1
スロークエリログ
実行に一定以上の時間がかかったクエリを記録する。
- 書式
- slow_query_log=<ON | OFF>
- slow_query_log_file=<ログファイルパス>
- long_query_time=<処理時間(秒)>
- min_examined_row_limit=<最低行数>
スロークエリログに記録する際に、ここで設定した値より少ない行しか実際にテーブルから
読み込まなかったクエリーは、ログへの出力を行わない。 - log_output=<TABLE | FILE | NONE>[,<TABLE | FILE>]
ログの保存先を指定する。
,区切りでテーブルとファイルと両方を指定することができる。
NONEの場合は保存されない。
なお、この設定は一般ログにも影響する。 - log_queries_not_using_indexes=<ON | OFF>
long_query_time変数で指定した時間まで実行時間がかからなかったが、
インデックスを使用していないクエリーをスロークエリログに記録するかどうかを設定する。 - log_slow_admin_statements=<ON | OFF>
管理用のSQL(optimize table、alter tableなど)もスロークエリログに記録するかどうかを指定する。 - log_throttle_queries_not_using_indexes=<ログ記録上限数>
log_queries_not_using_indexes変数がONの場合、１分間にログに出力可能な、
log_queries_not_using_indexes変数によって記録されるログの数の上限を指定する。
0の場合は上限はない。
- デフォルト
slow_query_log=OFF slow_query_log_file=<datadir/ホスト名-slow.log> long_query_time=10.000000 min_examined_row_limit=0 log_output=FILE log_queries_not_using_indexes=OFF log_slow_admin_statements=OFF log_throttle_queries_not_using_indexes=0
- 設定方法
- slow_query_log
SQLのチューニングやインデックスの漏れの検出などで役に立つので、有効にした方が良い。
開発環境で十分に試験を行えるのなら、本番環境に限り無効にしても良い。 - slow_query_log_file
デフォルトで特に問題ない。
ただし、ログ収集ツールなどを使用する場合は、他のDBサーバと共通にした方が良い。 - long_query_time
1秒でも遅いので、0.2秒などミリ秒レベルで設定するのが良い。 - min_examined_row_limit
意図的にインデックスの不備に限ってスロークエリログに記録するといった場合で無い限り、デフォルトの0のままでよい。
そうでなければ、読み取る行数は少ないが、実行に時間がかかるクエリは記録されない。
そういったクエリでもテーブル設計の改善などの余地がある。 - log_queries_not_using_indexes
実行に時間がかからなければ、特に出力しなくても良い。
ただし、サービス開始時でデータが少ないので全体的に実行時間が早いうちは有効にしても良い。 - log_slow_admin_statements
通常記録する必要は無い。
- slow_query_log
バイナリログ
バイナリログは更新系クエリのログである。
バックアップからのロールフォワードに使用される。
- 書式
- binlog_format=<STATEMENT | ROW | MIXED>
ログの保存形式を指定する
STATEMENT、ROW、MIXEDの３種類の値を指定できる。
- STATEMENT
SQL文をそのまま保存する。
人間が見ても分かりやすいが、データベースに反映させるとき、SQLの解釈が必要となるので遅くなる。
また、SQLを発効した状況によって結果が変わるようなSQLでは
ログから反映させた場合に完全に同じ状態に復元できない。 - ROW
変更されるデータそのものを保持する。
人間が見ると非常に分かりづらいが、データベースに反映させるとき、高速にリストア可能。
また、STATEMENTと比較してログデータのサイズが大きくなりがちである。 - MIXED
通常はSTATEMENTで保存し、以下の場合はROWで保存する。
・ストレージエンジンにNDBを使用した場合
・以下関数を使用した場合：UUID、FOUND_ROWS、ROW_COUNT、
USER、CURRENT_USER、LOAD_FILE
・ストアドプロシージャやトリガで変更があった場合
・AUTO_INCREMENT カラムを伴う 2 つ以上のテーブルを更新する場合
・INSERT DELAYED を実行する場合
- STATEMENT
- binlog_cache_size=32768
- binlog_checksum=CRC32
- binlog_direct_non_transactional_updates=<ON | OFF>
- binlog_max_flush_queue_time=0
- binlog_order_commits=<ON | OFF>
- binlog_row_image=FULL
- binlog_rows_query_log_events=<ON | OFF>
- binlog_stmt_cache_size=32768
- max_binlog_cache_size=18446744073709547520
- max_binlog_size=1073741824
- max_binlog_stmt_cache_size=18446744073709547520
- log_bin=OFF
- log_bin_basename=
- log_bin_index=
- log_bin_trust_function_creators=OFF
- log_bin_use_v1_row_events=OFF
- binlog_format=<STATEMENT | ROW | MIXED>
- sql_log_bin=ON
- sql_log_off=OFF
- expire_logs_days=0
- デフォルト
- log_bin=OFF
- log_bin_basename=
- log_bin_index=
- log_bin_trust_function_creators=OFF
- log_bin_use_v1_row_events=OFF
- binlog_format=STATEMENT
- binlog_cache_size=32768
- binlog_checksum=CRC32
- binlog_direct_non_transactional_updates=OFF
- binlog_max_flush_queue_time=0
- binlog_order_commits=ON
- binlog_row_image=FULL
- binlog_rows_query_log_events=OFF
- binlog_stmt_cache_size=32768
- max_binlog_cache_size=18446744073709547520
- max_binlog_size=1073741824
- max_binlog_stmt_cache_size=18446744073709547520
- sql_log_bin=ON
- sql_log_off=OFF
- expire_logs_days=0
- 設定方法
- binlog_format
通常はMIXEDにするのがデータ量を抑えつつ、復元を完全にするために最適である。
ただし、次の場合はSTATEMENTを検討する必要がある。
・完全なログを監査で使用する
・バージョンが異なるMySQL間でバイナリログを使用する
- binlog_format
デバッグログ
冗長
max_relay_log_size=0
relay_log=
relay_log_basename=
relay_log_index=
relay_log_info_file=relay-log.info
relay_log_info_repository=FILE
relay_log_purge=ON
relay_log_recovery=OFF
relay_log_space_limit=0
sync_binlog=0
バイナリログのディスク同期設定。
1の場合、ログの確実性。0の場合、パフォーマンスが向上。
サーバマシンの安定性を考慮して決定
sync_frm=ON
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000
init_slave=
rpl_stop_slave_timeout=31536000
slave_allow_batching=OFF
slave_checkpoint_group=512
slave_checkpoint_period=300
slave_compressed_protocol=OFF
slave_exec_mode=STRICT
slave_load_tmpdir=/tmp
slave_max_allowed_packet=1073741824
slave_net_timeout=3600
slave_parallel_workers=0
slave_pending_jobs_size_max=16777216
slave_rows_search_algorithms=TABLE_SCAN,INDEX_SCAN
slave_skip_errors=OFF
slave_sql_verify_checksum=ON
slave_transaction_retries=10
slave_type_conversions=
sql_slave_skip_counter=0
report_host=
report_password=
report_port=3306
report_user=
gtid_executed=
gtid_mode=OFF
gtid_owned=
gtid_purged=
master_info_repository=FILE
master_verify_checksum=OFF
multi_range_count=256
log_slave_updates=OFF
log_slow_slave_statements=OFF
enforce_gtid_consistency=OFF
この変数は起動時に設定する必要があり、起動後は読み取り専用の変数。
設定する場合は、 –enforce-gtid-consistency オプションをつける。
ON状態にあるとき、トランザクションを保てないクエリーは使用不可となる。
具体的には以下である。
CREATE TABLE … SELECT statements
CREATE TEMPORARY TABLE statements inside transactions
Transactions or statements that update both transactional and nontransactional tables.
準同期レプリケーション
have_dynamic_loading=YES
準同期レプリケーションに必要なdynamic loadingをサポートしているかどうか。
この変数は変更不可能。
その他
内部一時テーブル
内部一時テーブルファイルの設定を行う。
内部一時テーブルのストレージエンジンはMyISAMである。
内部一時テーブルはユーザが作成するTEMPORARYとは別のもので、
UNIONによる結合などMySQLの内部で利用される。
内部一時テーブルは通常メモリ上に作成されるが、領域が不足すると
tmpdirで指定した場所に一時ファイルが作成される。
参考：How MySQL Uses Internal Temporary Tables
- 書式
- tmp_table_size
一時テーブルのサイズ
max_heap_table_sizeの方が小さければ、実際はその値となる。単位はバイト
- tmp_table_size
- tmp_table_size=<サイズ(バイト)>
- max_tmp_tables
使用されない変数 - big_tables=(ON | OFF)
ONにすると、一時テーブルをメモリ上に作成せずに、全てディスク上に作成する。
- デフォルト
tmp_table_size=16777216
big_tables=OFF
- 設定方法
- tmp_table_size
通常はデフォルトでよい。
- tmp_table_size
TEMPORARYテーブル
CREATE TEMPORARY TABLEによって作成されるTEMPORARYテーブルの設定。
TEMPORARYテーブルはMEMORYストレージエンジンのデータベースとは異なる。
- 書式
- default_tmp_storage_engine
一時テーブルで使用するストレージエンジン
- default_tmp_storage_engine
- default_tmp_storage_engine=<ストレージエンジン>
- デフォルト
default_tmp_storage_engine=InnoDB
ホスト名解決
host_cache_size=279
GRANTで作成したユーザのアクセス元制限で、ホスト名を指定することができる。
指定した場合、アクセス毎にアクセス元のIPの逆引きでホスト名を取得し、比較を行う。
更にそのホスト名を正引きし、取得したIPとアクセス元IPの比較を行う。
大量にMySQLにアクセスする場合、この処理に非常に負荷がかかる。
これを避けるため、以下の設定を追加してDNSによる確認を止めることが可能である。
skip-name-resolve
ただし、localhostを含めてドメイン名を指定したユーザ制限ができないくなるので、
代わりにIPアドレスを直接指定する必要がある。(localhostの場合は127.0.0.1)
なお、/etc/hostsファイルに静的に名前情報を記述することでも
DNS処理を解決できるが、漏れが合った場合はDNS処理が行われてします。
いずれの方法でもIPアドレスの変更があった場合は、
設定の修正が必要となってしまう。
- デフォルト
記載なし（有効）
- 書式
- skip-name-resolve
パフォーマンススキーマ
performance_schema=ON
performance_schema_accounts_size=100
performance_schema_digests_size=10000
performance_schema_events_stages_history_long_size=10000
performance_schema_events_stages_history_size=10
performance_schema_events_statements_history_long_size 10000
performance_schema_events_statements_history_size=10
performance_schema_events_waits_history_long_size=10000
performance_schema_events_waits_history_size=10
performance_schema_hosts_size=100
performance_schema_max_cond_classes=80
performance_schema_max_cond_instances=3504
performance_schema_max_file_classes=50
performance_schema_max_file_handles=32768
performance_schema_max_file_instances=7693
performance_schema_max_mutex_classes=200
performance_schema_max_mutex_instances=15906
performance_schema_max_rwlock_classes=40
performance_schema_max_rwlock_instances=9102
performance_schema_max_socket_classes=10
performance_schema_max_socket_instances=322
performance_schema_max_stage_classes=150
performance_schema_max_statement_classes=168
performance_schema_max_table_handles=4000
performance_schema_max_table_instances=12500
performance_schema_max_thread_classes=50
performance_schema_max_thread_instances=402
performance_schema_session_connect_attrs_size=512
performance_schema_setup_actors_size=100
performance_schema_setup_objects_size=100
performance_schema_users_size=100
全文検索
ft_boolean_syntax=+ -><()~*:””&|
ft_max_word_len=84
ft_min_word_len=4
ft_query_expansion_limit=20
ft_stopword_file=(built-in)
コアファイル（ダンプファイル）
エラー終了時にコアファイルを生成するかどうか。
コアファイルを取得する場合、MySQLでの設定のほか、次のカーネルパラメータを設定する必要がある。
- core_pattern
- suid_dumpable
- core_file=OFF
他
automatic_sp_privileges=ON
stored routineの作成者に自動的にEXECUTEとALTER ROUTINEを実行する権限を与えるかどうか。
disconnect_on_expired_password=ON
期限切れパスワードをどう扱うか。
have_compress=YES
zlibによる圧縮・展開をサポートしているかどうか。
この変数は変更不可能。
have_crypt=YES
暗号化（crypt()）をサポートしているかどうか。
この変数は変更不可能。
have_geometry=YES
Geometry機能をサポートしているかどうか。
この変数は変更不可能。
init_connect=
クライアントが接続されるたびにサーバで実行されるコマンド・クエリーを指定する
init_file=
サーバ起動時に実行するコマンド・クエリーが記述されたファイルのパスを指定する
large_files_support=ON
ラージファイル(2GB以上のサイズのファイル)をサポートしているかどうか。
この変数は変更不可能。
large_page_size=0
InnoDBでラージページ(4KB以上のページ)として扱えるページのサイズが設定される。
0の場合はサポートしていない。
この変数は変更不可能。
large_pages=OFF
InnoDBでラージページ(4KB以上のページ)をサポートしているかどうか。
この変数は変更不可能。
locked_in_memory=OFF
lower_case_file_system=OFF
lower_case_table_names=0
テーブルやデータベースがファイルシステム上でファイルやディレクトリとして保存される際、
ファイル名やディレクトリ名を小文字に変換するか指定する。
- 0
変換を行わない。
ただし、SQLで指定した文字でファイルを特定するので、OSが大文字と小文字を区別できる必要がある。 - 1
小文字に変換する。
SQLで大文字を指定しても小文字として扱われる。
InnoDBを使用する場合、この値にする必要がある。 - 2
変換を行わない。
SQLで指定した大文字小文字は無視されるので、OSで大文字と小文字の区別が行われない必要がある。
- max_prepared_stmt_count=16382
- max_sp_recursion_depth=0
- new=OFF
- old=OFF
- old_passwords=0
- have_profiling=YES
profiling=OFF
profiling_history_size=15
read_only=OFF
secure_auth=ON
secure_file_priv=
お薦めのデフォルトmy.cnf
[mysqld]
#BASIC CONFIGURATIONS
port=3306
character_set_server=utf8
skip-character-set-client-handshake
datadir=
skip-name-resolve
query_cache_type=ON
query_cache_wlock_invalidate=OFF
#PERFORMANCE CONFIGRATIONS
query_cache_size=1048576
query_cache_min_res_unit=4096
query_cache_limit=1048576
max_connections=300
table_open_cache
table_definition_cache
key_buffer_size
myisam_max_sort_file_size
myisam_recover
read_buffer_size
read_rnd_buffer_size
join_buffer_size
max_binlog_size
expire_logs_days
server_id
slow_query_log
long_query_time
log_queries_not_using_indexes
open_files_limit
skip_innodb
table_open_cache
table_definition_cache
innodb_buffer_pool_size
innodb_additional_mem_pool_size
innodb_data_file_path
innodb_data_home_dir
innodb_file_per_table
innodb_autoextend_increment
innodb_log_group_home_dir
innodb_log_files_in_group
innodb_log_file_size
innodb_log_buffer_size
innodb_flush_log_at_trx_commit
innodb_flush_method
innodb_thread_concurrency
innodb_commit_concurrency
innodb_concurrency_ticlets
innodb_sync_spin_loops
innodb_thread_sleep_delay
innodb_max_dirty_pages_pct
クライアント設定
クライアント設定を設定ファイルに記述する場合は[client]セクションか[mysql]セクションに記述する。
[client]セクションはmysqlを使用するクライアントソフト共通の設定を記載し、
[mysql]セクションにはmysqlコマンドでのみ使用する設定を記載する。
ログイン情報
- 書式
- user=<ユーザ名>
- password=<パスワード>
ページャ
ページャを使用する場合、使用するページャを指定する
- 書式
pager=(less|more)
警告の表示
クエリを実行した際に警告が発生すると、自動的にshow warningコマンドを実行する
- 書式
show-warnings
文字コード
接続に使用する文字コードを指定する。
ターミナルの文字コードと合わせる必要がある。
- 書式
default-character-set=<文字コード>
全件更新・削除の禁止
キーを指定しないupdate、deleteクエリの実行を禁止する。
これによってレコード全件を更新・削除してしまうことがなくなる。
- 書式
safe-updates
プロンプトの変更
デフォルトのプロンプト mysql> を変更する
- 書式
prompt=<プロンプト文>
- 使用可能な特殊記号
- \c
コマンドの実行回数 - \D
日時 - \d
利用中のデフォルトデータベース - \h
接続先ホスト - \l
デリミタ - \m
分 - \n
改行 - \O
月（文字） - \o
月（数字） - \P
am/pm - \p
TCPポート番号、またはソケットファイル名 - \R
時（24時間） - \r
時（12時間） - \S
セミコロン - \s
秒 - \t
タブ - \U
ユーザ名とホスト名 - \u
ユーザ名 - \v
MySQLのバージョン - \w
曜日 - \Y
年（4桁） - \y
年（2桁） - \_
空白 - \
空白 - \’
シングルクォート - \”
ダブルクォート - \\
バックスラッシュ
- \c
クイックインストール
OSインストール直後からMySQLの初期設定までを行う方法を以下に記載する。
以下をコピーペーストすれば良い。
クエリー
クエリーの最後を;でなく\Gとすることで、表形式ではなく、
項目とその値の対応として表示させることができる。
一覧表示
データベース一覧
show databases;
パラメータ確認
-
全て表示
show global variables;
-
特定のもののみ表示
show global variables like '%';
ステータス確認
サーバステータス
- セッションステータス
現在接続中のセッションのステータスを表示する。
show [session] status;
- グローバルステータス
サーバ起動時からの全セッションの統計ステータスを表示する。
グローバルステータス出力時にステータス変数がロックされるため、
ステータスを更新しようとする他のクエリーの処理が一瞬行えなくなるので注意。
show global status;
- 実行例
+-----------------------------------------------+-------------+
| Variable_name | Value |
+-----------------------------------------------+-------------+
| Aborted_clients | 384 |
| Aborted_connects | 20029 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 0 |
| Bytes_received | 340 |
| Bytes_sent | 15698 |
| Com_admin_commands | 0 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
| Com_alter_procedure | 0 |
| Com_alter_server | 0 |
| Com_alter_table | 0 |
| Com_alter_tablespace | 0 |
| Com_alter_user | 0 |
| Com_analyze | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 0 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_event | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_procedure | 0 |
| Com_create_server | 0 |
| Com_create_table | 0 |
| Com_create_trigger | 0 |
| Com_create_udf | 0 |
| Com_create_user | 0 |
| Com_create_view | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 0 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_event | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_procedure | 0 |
| Com_drop_server | 0 |
| Com_drop_table | 0 |
| Com_drop_trigger | 0 |
| Com_drop_user | 0 |
| Com_drop_view | 0 |
| Com_empty_query | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_get_diagnostics | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 0 |
| Com_insert_select | 0 |
| Com_install_plugin | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_release_savepoint | 0 |
| Com_rename_table | 0 |
| Com_rename_user | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_resignal | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_rollback_to_savepoint | 0 |
| Com_savepoint | 0 |
| Com_select | 1 |
| Com_set_option | 0 |
| Com_signal | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_create_db | 0 |
| Com_show_create_event | 0 |
| Com_show_create_func | 0 |
| Com_show_create_proc | 0 |
| Com_show_create_table | 0 |
| Com_show_create_trigger | 0 |
| Com_show_databases | 0 |
| Com_show_engine_logs | 0 |
| Com_show_engine_mutex | 0 |
| Com_show_engine_status | 4 |
| Com_show_events | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_function_code | 0 |
| Com_show_function_status | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_master_status | 0 |
| Com_show_open_tables | 0 |
| Com_show_plugins | 0 |
| Com_show_privileges | 0 |
| Com_show_procedure_code | 0 |
| Com_show_procedure_status | 0 |
| Com_show_processlist | 0 |
| Com_show_profile | 0 |
| Com_show_profiles | 0 |
| Com_show_relaylog_events | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_storage_engines | 0 |
| Com_show_table_status | 0 |
| Com_show_tables | 0 |
| Com_show_triggers | 0 |
| Com_show_variables | 0 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reprepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_uninstall_plugin | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connection_errors_accept | 0 |
| Connection_errors_internal | 0 |
| Connection_errors_max_connections | 16886 |
| Connection_errors_peer_address | 0 |
| Connection_errors_select | 0 |
| Connection_errors_tcpwrap | 0 |
| Connections | 51525 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 790 |
| Created_tmp_tables | 0 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 0 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
| Innodb_buffer_pool_dump_status | not started |
| Innodb_buffer_pool_load_status | not started |
| Innodb_buffer_pool_pages_data | 7166 |
| Innodb_buffer_pool_bytes_data | 117407744 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_bytes_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 1236791 |
| Innodb_buffer_pool_pages_free | 1024 |
| Innodb_buffer_pool_pages_misc | 1 |
| Innodb_buffer_pool_pages_total | 8191 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 384 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 390231568 |
| Innodb_buffer_pool_reads | 418780 |
| Innodb_buffer_pool_wait_free | 1132 |
| Innodb_buffer_pool_write_requests | 73595693 |
| Innodb_data_fsyncs | 4378013 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 8678395904 |
| Innodb_data_reads | 496835 |
| Innodb_data_writes | 4849865 |
| Innodb_data_written | 48996829184 |
| Innodb_dblwr_pages_written | 1237923 |
| Innodb_dblwr_writes | 24759 |
| Innodb_have_atomic_builtins | ON |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 15081412 |
| Innodb_log_writes | 4065255 |
| Innodb_os_log_fsyncs | 4068937 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 8431348224 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 325432 |
| Innodb_pages_read | 529553 |
| Innodb_pages_written | 1237923 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 409004 |
| Innodb_rows_inserted | 1185998 |
| Innodb_rows_read | 573632779 |
| Innodb_rows_updated | 4000004 |
| Innodb_num_open_files | 9 |
| Innodb_truncated_status_writes | 0 |
| Innodb_available_undo_logs | 128 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 6698 |
| Key_blocks_used | 4 |
| Key_read_requests | 36 |
| Key_reads | 4 |
| Key_write_requests | 27 |
| Key_writes | 12 |
| Last_query_cost | 0.000000 |
| Last_query_partial_plans | 0 |
| Max_used_connections | 152 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 46 |
| Open_streams | 0 |
| Open_table_definitions | 81 |
| Open_tables | 90 |
| Opened_files | 132390 |
| Opened_table_definitions | 0 |
| Opened_tables | 0 |
| Performance_schema_accounts_lost | 0 |
| Performance_schema_cond_classes_lost | 0 |
| Performance_schema_cond_instances_lost | 0 |
| Performance_schema_digest_lost | 0 |
| Performance_schema_file_classes_lost | 0 |
| Performance_schema_file_handles_lost | 0 |
| Performance_schema_file_instances_lost | 41976 |
| Performance_schema_hosts_lost | 0 |
| Performance_schema_locker_lost | 0 |
| Performance_schema_mutex_classes_lost | 0 |
| Performance_schema_mutex_instances_lost | 0 |
| Performance_schema_rwlock_classes_lost | 0 |
| Performance_schema_rwlock_instances_lost | 0 |
| Performance_schema_session_connect_attrs_lost | 0 |
| Performance_schema_socket_classes_lost | 0 |
| Performance_schema_socket_instances_lost | 0 |
| Performance_schema_stage_classes_lost | 0 |
| Performance_schema_statement_classes_lost | 0 |
| Performance_schema_table_handles_lost | 0 |
| Performance_schema_table_instances_lost | 0 |
| Performance_schema_thread_classes_lost | 0 |
| Performance_schema_thread_instances_lost | 0 |
| Performance_schema_users_lost | 0 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031360 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2269633 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
| Queries | 3450957 |
| Questions | 6 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 0 |
| Slave_heartbeat_period |
| | Slave_last_heartbeat |
| | Slave_open_temp_tables | 0 |
| Slave_received_heartbeats |
| | Slave_retried_transactions |
| | Slave_running | OFF |
| Slow_launch_threads | 6 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher ||
| Ssl_cipher_list ||
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_server_not_after ||
| Ssl_server_not_before ||
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 3112087 |
| Table_locks_waited | 0 |
| Table_open_cache_hits | 0 |
| Table_open_cache_misses | 0 |
| Table_open_cache_overflows | 0 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 9 |
| Threads_connected | 7 |
| Threads_created | 1088 |
| Threads_running | 1 |
| Uptime | 703703 |
| Uptime_since_flush_status | 703703 |
+-----------------------------------------------+-------------+
データベース使用量確認
show table status;
OR
select table_schema, sum(data_length+index_length) /1024 /1024 as MB from information_schema.tables group by table_schema order by sum(data_length+index_length) desc;
テーブル使用量確認
select table_name, engine, table_rows as tbl_rows, avg_row_length as rlen, floor((data_length+index_length)/1024/1024) as allMB, floor((data_length)/1024/1024) as dMB, floor((index_length)/1024/1024) as iMB from information_schema.tables where table_schema=database() order by (data_length+index_length) desc;
Innodbのステータス
show engine innodb status;
- 実行例
+--------+------+----------------------------------------------------------------------------------- | Type | Name | Status +--------+------+----------------------------------------------------------------------------------- | InnoDB | | ===================================== 2014-01-30 17:51:37 7f6911898700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 26 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 15249 srv_active, 0 srv_shutdown, 262158 srv_idle srv_master_thread log flush and writes: 277407 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 1278141 OS WAIT ARRAY INFO: signal count 1226784 Mutex spin waits 1063192, rounds 31958120, OS waits 1060896 RW-shared spins 161488, rounds 5002921, OS waits 157962 RW-excl spins 31095, rounds 1778537, OS waits 55628 Spin rounds per wait: 30.06 mutex, 30.98 RW-shared, 57.20 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 6805589 Purge done for trx's n:o < 6805587 undo n:o < 0 state: running but idle History list length 1551 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 51524, OS thread handle 0x7f6911898700, query id 3450930 localhost root init show engine innodb status -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (read thread) I/O thread 4 state: waiting for i/o request (read thread) I/O thread 5 state: waiting for i/o request (read thread) I/O thread 6 state: waiting for i/o request (write thread) I/O thread 7 state: waiting for i/o request (write thread) I/O thread 8 state: waiting for i/o request (write thread) I/O thread 9 state: waiting for i/o request (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 496835 OS file reads, 4849865 OS file writes, 4378013 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 2508, seg size 2510, 223697 merges merged operations: insert 818574, delete mark 1173912, delete 540989 discarded operations: insert 0, delete mark 2734, delete 616 Hash table size 276671, node heap has 1 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 6351627995 Log flushed up to 6351627995 Pages flushed up to 6351627995 Last checkpoint at 6351627995 0 pending log writes, 0 pending chkp writes 4067643 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137363456; in additional pool allocated 0 Dictionary memory allocated 67836 Buffer pool size 8191 Free buffers 1024 Database pages 7166 Old database pages 2625 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 1550487, not young 4767512 0.00 youngs/s, 0.00 non-youngs/s Pages read 529553, created 325432, written 1237923 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 7166, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Main thread process no. 20081, id 140089328961280, state: sleeping Number of rows inserted 1185998, updated 4000004, deleted 409004, read 573632779 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ | +--------+------+-----------------------------------------------------------------------------------
テーブルステータス
テーブルに関する情報を表示する
- 書式
show table status [from <データベース名>] [like <テーブル名>] \G
- 出力例
mysql> show table status from mysql like 'user'\G *************************** 1. row *************************** Name: user Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 3 Avg_row_length: 53 Data_length: 328 Max_data_length: 281474976710655 Index_length: 2048 Data_free: 168 Auto_increment: NULL Create_time: 2014-01-21 21:21:36 Update_time: 2014-01-21 21:31:58 Check_time: NULL Collation: utf8_bin Checksum: NULL Create_options: Comment: Users and global privileges 1 row in set (0.00 sec)
- 出力項目
- Name
テーブル名 - Engine
テーブルのストレージ エンジン - Version
テーブルの.frmファイルのバージョン番号 - Row_format
行のストレージフォーマット
Fixed、Dynamic、Compressed、Redundant、Compactのいずれか。
InnoDBテーブルのフォーマットは Redundant か Compactである。 - Rows
行数
トランザクションをサポートしていないMyISAMでは、正確な値を取得できるが、
トランザクションをサポートしているInnoDBでは、この値はおおよその値で実際とは異なる。
正確な値が必要な場合は SELECT COUNT(*) を利用する必要がある。
INFORMATION_SCHEMAデータベース内のテーブルではNULLとなる。 - Avg_row_length
平均的な行あたりのバイト数 - Data_length
テーブル全体のデータ量（バイト数） - Max_data_length
テーブル内に格納できるデータの総バイト数 - Index_length
インデックスファイルのデータ量（バイト数） - Data_free
割り当てられたが使用されていないバイト数。
既に削除された領域も含む。 - Auto_increment
次のAUTO_INCREMENT値。 - Create_time
テーブルが作成された時間 - Update_time
データファイルが最後に更新された時間
InnoDBでは、この値は NULL - Check_time
checktable、myisamchkを使用してテーブルが最後に確認された時間
サポートしないストレージ エンジンでは常にNULLとなる。 - Collation
テーブルの文字セットと照合順序 - Checksum
テーブル全体のリアルタイムのチェックサム値（有効な場合）。 - Create_options
CREATE TABLE 時に指定されたオプション。 - Comment
テーブルを作成する時に記述されたコメント。
またはなぜ MySQL がテーブル情報にアクセスできなかったのかに関する情報。
- Name
プロセス
実行中のクエリ一覧表示
show processlist;
※見切れる場合
show full processlist;
各項目について
参考:https://dev.mysql.com/doc/refman/5.6/ja/show-processlist.html
- Time
プロセス(スレッド)が現在のステータスになってからの経過時間 - State
現在のプロセスの状態 - Progress
プロセスの進捗状況 (0～100%)
プロセスの検索
プロセスはinformation_schemaテーブルより参照できるので、下記のようにwhere条件を指定できる。
select * from information_schema.PROCESSLIST where XXX order by XXX\G
- 10分以上かかっているプロセスを実行経過時間でソート
select * from information_schema.PROCESSLIST where TIME>=600 order by TIME desc\G
実行中のクエリの強制終了
kill <プロセスID>;
ファイルからロード
load data
サーバ設定
設定ファイルや起動オプションの他、サーバ稼働中にコマンドによる設定変更が可能である。
セッション変数設定
set [session] <変数>[=<値>];
セッション変数確認
show [session] variables;
グローバル変数設定
set lobal <変数>=<値>;
グローバル変数確認
全ての変数を表示する。
show global variables;
一部の変数のみを表示する。
show global variables where Variable_name like '%<検索変数名>%';
ユーザ変数
ユーザ変数という、SQL文中に使用することができる変数を定義することができる。
ユーザ変数は変数名の前に @ を付けることで、定義、設定、参照が可能。
set @<変数>=<値>;
データベース操作
データベース作成
create database <DB名> [default charset=<文字コード>];
データベース削除
drop database <DB名>;
ユーザ操作
ユーザ作成
- 書式
create user <ユーザ名>[@<接続元ホスト>] [identified by '<パスワード>'];
or
grant <権限> on <DB名>.<テーブル名> to <ユーザ名>@<接続元ホスト> identified by "<パスワード>";
- DB名
ワイルドカード(*)指定が可能 - テーブル名
ワイルドカード(*)指定が可能 - 権限一覧
全ての権限はALL。
個別に指定する場合、createやselectなどクエリ種別を指定する。
コンマ区切りで複数指定可能
- DB名
権限の確認
show grants for <ユーザ名>@<接続元ホスト>;
権限削除
revoke <権限> on <DB名>.<テーブル名> from <ユーザ名>@<接続元ホスト>;
テーブル操作
テーブル作成
create table <テーブル名> (<列名> <列型> [オプション] [, ...]);
テーブルデータ削除
truncate table <テーブル名>;
テーブル定義確認
- 書式
show create table <テーブル名>\G
- 実行例
mysql> show create table mysql.user\G *************************** 1. row *************************** Table: user Create Table: CREATE TABLE `user` ( `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '', `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '', `ssl_cipher` blob NOT NULL, `x509_issuer` blob NOT NULL, `x509_subject` blob NOT NULL, `max_questions` int(11) unsigned NOT NULL DEFAULT '0', `max_updates` int(11) unsigned NOT NULL DEFAULT '0', `max_connections` int(11) unsigned NOT NULL DEFAULT '0', `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0', `plugin` char(64) COLLATE utf8_bin DEFAULT '', `authentication_string` text COLLATE utf8_bin, `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', PRIMARY KEY (`Host`,`User`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges' 1 row in set (0.00 sec)
テーブル情報確認
- 書式
以下は全て同じ。
desc <テーブル名>;
or
describe <テーブル名>;
or
explain <テーブル名>;
- 実行例
mysql> describe mysql.user; +------------------------+-----------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+-----------------------------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Password | char(41) | NO | | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | | Create_priv | enum('N','Y') | NO | | N | | | Drop_priv | enum('N','Y') | NO | | N | | | Reload_priv | enum('N','Y') | NO | | N | | | Shutdown_priv | enum('N','Y') | NO | | N | | | Process_priv | enum('N','Y') | NO | | N | | | File_priv | enum('N','Y') | NO | | N | | | Grant_priv | enum('N','Y') | NO | | N | | | References_priv | enum('N','Y') | NO | | N | | | Index_priv | enum('N','Y') | NO | | N | | | Alter_priv | enum('N','Y') | NO | | N | | | Show_db_priv | enum('N','Y') | NO | | N | | | Super_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Repl_slave_priv | enum('N','Y') | NO | | N | | | Repl_client_priv | enum('N','Y') | NO | | N | | | Create_view_priv | enum('N','Y') | NO | | N | | | Show_view_priv | enum('N','Y') | NO | | N | | | Create_routine_priv | enum('N','Y') | NO | | N | | | Alter_routine_priv | enum('N','Y') | NO | | N | | | Create_user_priv | enum('N','Y') | NO | | N | | | Event_priv | enum('N','Y') | NO | | N | | | Trigger_priv | enum('N','Y') | NO | | N | | | Create_tablespace_priv | enum('N','Y') | NO | | N | | | ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | | | ssl_cipher | blob | NO | | NULL | | | x509_issuer | blob | NO | | NULL | | | x509_subject | blob | NO | | NULL | | | max_questions | int(11) unsigned | NO | | 0 | | | max_updates | int(11) unsigned | NO | | 0 | | | max_connections | int(11) unsigned | NO | | 0 | | | max_user_connections | int(11) unsigned | NO | | 0 | | | plugin | char(64) | YES | | | | | authentication_string | text | YES | | NULL | | | password_expired | enum('N','Y') | NO | | N | | +------------------------+-----------------------------------+------+-----+---------+-------+ 43 rows in set (0.00 sec)
主キーの追加
alter table <テーブル名> add primary key(<列名> [, ...]);
インデックスの作成
create [unique] index <インデックス名> on <テーブル名>(<列名> [, ...]);
インデックスの削除
drop index <インデックス名> on <テーブル名>;
インデックスの修正
alter table <テーブル名> drop index <インデックス名>(<列名> [, ...]), add index <インデックス名>(<列名> [, ...]);
インデックスの確認
show index from <テーブル名>;
インデックスのカーディナリティがNULLの場合、次のコマンドを先に実行する。
analyze table <テーブル名>;
※analyze tableはMyISAMでは全行をリードロック、InnoDBではランダムに10行更新ロックされるので注意
テーブルの変更
alter table <テーブル名> add <列名> <列型> [(first | after) <列名>]; alter table <テーブル名> modify <列名> <列型> [(first | after) <列名>]; alter table <テーブル名> engine = <ストレージエンジン名>; alter table <テーブル名> drop primary key;
列名の変更
alter table <テーブル名> change <列名> <新列名> <データ型>;
列の型の変更
列の型を変更する。文字列型の文字列長を変更することも可能。
alter table <テーブル名> modify <列名> <データ型>;
テーブルの削除
drop table <テーブル名>;
断片化の確認
show table status from <テーブル名>;
Data_free値を確認する。値が大きいほど断片化している。
断片化の解消
optimize table <テーブル名>;
パーティション
パーティションを作成した場合、パーティション条件に一致しないデータを挿入しようとするとエラーが出る。
パーティションの作成
パーティション列には列名のほか、関数(YEAR()等)や四則演算など使用できる。
例えばDate型はRangeパーティションで使用できないので、TO_DAYS()関数で数値に直す必要がある。
パーティション列は主キーに含まれる必要がある。
- Rangeパーティション
create table <テーブル名> (テーブル定義) partition by range(<パーティション列>) ( partition <パーティション名> values less than (<比較定数>), partition <パーティション名> values less than (<比較定数>), ... partition <パーティション名> values less than MAXVALUE );
- Hash、Key
create table <テーブル名> (テーブル定義) partition by <パーティションタイプ>(<パーティション列>) partitions <パーティション数>;
- List
create table <テーブル名> (テーブル定義) partition by list(<パーティション列>) ( partition <パーティション名> values in (<比較定数>,<比較定数>, ... ,<比較定数>), partition <パーティション名> values in (<比較定数>,<比較定数>, ... ,<比較定数>), ... partition <パーティション名> values in (<比較定数>,<比較定数>, ... ,<比較定数>) );
パーティションを後から作成
- Rangeパーティション
alter table <テーブル名> partition by range(<パーティション列>) ( partition <パーティション名> values less than (<比較定数>), partition <パーティション名> values less than (<比較定数>), ... partition <パーティション名> values less than MAXVALUE );
- Hash、List、Key
alter table <テーブル名> partition by <パーティションタイプ>(<パーティション列>) partitions <パーティション数>;
- List
alter table <テーブル名> partition by list(<パーティション列>) ( partition <パーティション名> values in (<比較定数>,<比較定数>, ... ,<比較定数>), partition <パーティション名> values in (<比較定数>,<比較定数>, ... ,<比較定数>), ... partition <パーティション名> values in (<比較定数>,<比較定数>, ... ,<比較定数>) );
パーティションの追加
※データは再配置される
- Rangeパーティション
alter table <テーブル名> reorganize partition <元パーティション名> into ( partition <新パーティション名> values less than (<比較定数>), partition <新パーティション名> values less than (<比較定数>) );
- Listパーティション
alter table <テーブル名> reorganize partition <元パーティション名> into ( partition <新パーティション名> values in (<比較定数>,<比較定数>, ... ,<比較定数>), partition <新パーティション名> values in (<比較定数>,<比較定数>, ... ,<比較定数>) );
パーティションの削除
- Range、Listパーティション
※パーティション内の値は再配置されずに削除される
alter table <テーブル名> drop partition <パーティション名>;
- Hash、Key
※パーティション内の値は再配置され、結合される
alter table <テーブル名> coalesce partition <パーティション数>;
パーティションの停止
alter table <テーブル名> remove partitioning;
パーティションのメンテナンス
- 固定長の行を持つテーブルのデフラグを行う
alter table <テーブル名> reuild partition <パーティション名>,<パーティション名>, ... ,<パーティション名>;
- 可変長の行を持つテーブルのデフラグを行う
alter table <テーブル名> optimize partition <パーティション名>,<パーティション名>, ... ,<パーティション名>;
- キーの再読み込みを行う
alter table <テーブル名> analyze partition <パーティション名>,<パーティション名>, ... ,<パーティション名>;
- パーティションの修復
alter table <テーブル名> repair partition <パーティション名>,<パーティション名>, ... ,<パーティション名>;
- パーティションのチェック
alter table <テーブル名> check partition <パーティション名>,<パーティション名>, ... ,<パーティション名>;
SELECT
ファイルへ書き出し
select * into outfile '<パス>' [fields <オプション>] from <テーブル>;
INSERT
バルクインサート
ファイルからロード
load data infile
SELECT
他
デフォルトデータベースの変更
use <DB名>
サーバ設定
setコマンドはレプリケーションされない
set session set global
実行計画（EXPLAIN）
参考:http://dev.mysql.com/doc/refman/5.1/ja/explain.html
- 書式
explain <SQL>;
- 実行例
mysql> explain select * from mysql.user; +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 3 | NULL | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec)
- 出力項目
- id
実行したクエリーの識別番号 - select_type
selectステートメントの種類。以下の値をとる。
SIMPLE 単純なSELECT (UNIONやサブクエリを使用しない)。 PRIMARY 最外部のSELECT。 UNION 内の第２およびそれ以降のSELECTステートメント。 DEPENDENT UNION UNION内の第2およびそれ以降のSELECTステートメント内のUNION、外側のサブクエリに依存する。 UNION RESULT UNIONの結果。 SUBQUERY サブクエリ内の第一SELECT。 DEPENDENT SUBQUERY 第１SELECT、外側のサブクエリに依存する。 DERIVED 派生テーブルSELECT （FROM節内のサブクエリ) UNCACHEABLE SUBQUERY 結果がキャッシュされず、外側のクエリの各行ごとに再評価されるサブクエリ。 - table
参照したテーブル名 - type
テーブルへのアクセス方法を表す。
以下の値をとり、上から順に良い。
- system
１行しか存在しないテーブルへのアクセス - const
最大で１行のみを読み取り、それを結果とする場合。
主キー、UNIQUEキーのインデックスを使用して単独の表を絞り込んだ場合はこちらの値となる。 - eq_ref
複数のテーブルを使用するクエリで、各テーブルにおいて最大１行しか読み取られなかった場合の値。 - ref
単独の行を特定できないインデックスを使用したクエリを実行する場合。
主キー、UNIQUEキーのインデックス（ユニークなインデックス）を使用しない、その他のインデックスを使用した場合や、
ユニークな列のインデックスであるが、単独の行を特定できない先頭から数文字のみを使用している場合はこちらの値となる。 - ref_or_null
refと基本的に同じであるが、”where ～ or XXX is NULL”も併せて条件に指定した場合にはこちらの値となる。 - index_merge
複数種類のインデックスを使用して検索し、最終的に結果をマージする場合はこちらの値となる - unique_subquery
ユニークなインデックスを使用したサブクエリを使用することにより最大１行の結果をそのサブクエリが返す場合、こちらの値となる - index_subquery
ユニークではないインデックスを使用したサブクエリを使用する場合、こちらの値となる - range
インデックスを使用している列の値を定数と比較する条件検索を行う場合はこちらの値となる。
この場合で使用される条件識別子は、=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、INである。 - index
インデックスを使用している場合はこちらの値となる。 - ALL
フルテーブルスキャンを行う場合の値
- system
- possible_keys
検索で使用可能なインデックスの一覧を表す。 - key
検索で実際に使用するインデックスの一覧を表す。
インデックスを使用していない場合はNULLになる。 - key_len
実際に使用したインデックスの、使用した先頭からの文字数を表す。
インデックスを使用していない場合はNULLになる。 - ref
インデックスと比較する値の種類。定数の場合はconst、列を使用する場合は列名が表示される。 - rows
検索で調べた行数を表す。 - Extra
追加情報が表示される。以下の値がとられる。
- Distinct
マッチした最初のレコードを検出時に、以降の検索を行わない。 - const row not found
select文において検索したテーブルが空であった。 - Deleting all rows
delete文で全件削除を行った。
これは全件削除を最適化できるストレージエンジンでのみ出力される。 - FirstMatch(<テーブル名>)
セミジョイン( select ～ in (select ～) )において、処理対象行についてサブクエリ内で条件に合致すれものが一件見つかれば、
サブクエリのテーブルの末尾まで検索は行わずに、その時点で処理対象行のサブクエリに対する検索はそこで中断されることを表す。 - Full scan on NULL key
サブクエリ内での検索においてNULL比較をフルテーブルスキャンにて行った。
→メインのクエリでNULLを排除できるなら除く - Impossible HAVING
- Distinct
- id
- Impossible WHERE
- Impossible WHERE noticed after reading const tables
- LooseScan(m..n)
- No matching min/max row
- no matching row in const table
- No matching rows after partition pruning
- No tables used
- Not exists
例えば、次のようなSQLで表示される。
SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
上記のようなLEFT JOIN検索において、NOT NULL列に対してis NULL検索を行うなどレコードを発見し得ない場合に表示される。
この場合においてもJOINされるテーブルをt1.id=でマッチする１件目のレコードの検索までは行われるが、以降は中断される。
→そもそもクエリーに合致する行は無いので、select文を確認する - Plan isn’t ready yet
- Range checked for each record (index map: N)
JOIN検索において、片方のテーブル内の検索においてrange、またはindex_mergeタイプのインデックスが使用されたことを表す。
インデックスは部分的には使用されているものの、あまり高速ではない状態。
→使用しているインデックスが無い列にインデックスを作成する - Scanned N databases
- Select tables optimized away
インデックスを使用してMIN()、MAX()、COUNT()などの集約関数を使用したことを表す。
ただし、group byは使用されておらず、一行の列のみを返している必要がある。 - Skip_open_table, Open_frm_only, Open_trigger_only, Open_full_table
- Start temporary, End temporary
- unique row not found
- Using filesort
- Using index
インデックスを使用して検索したことを表す。
InnoDBでは主キーやUNIQUEキーで検索した場合では、これは表示されない。 - Using index condition
ICP(Index Condition Pushdown)最適化が行われたことを表す。
ICP最適化とはインデックスの存在する列を使用した検索において、ストレージエンジンから値を取り出して評価せずに、
ストレージエンジンに比較する値を渡し、ストレージエンジン内で評価して合致する行のみを渡すというものである。
この方式の方がICP最適化を行わない場合より高速である。 - Using index for group-by
GROUP BYまたはDISTINCTをインデックスの存在する列に対して使用した検索を行ったことを表す。 - Using join buffer (Block Nested Loop)
BNL(Block Nested Loop)アルゴリズムによるJOINバッファ使用が行われたことを示す。
BNL(Block Nested Loop)アルゴリズムとは、JOINを行う内部表でインデックスが使用できない際に、
内部表との比較評価を一行ずつ行わずにバッファに貯めてからまとめて内部表との比較評価を行うことで、高速化する方法。
→内部表でインデックスを使用する - Using join buffer (Batched Key Access)
BKA(Batched Key Access)アルゴリズムによるJOINバッファ使用が行われたことを示す。
BKA(Batched Key Access)アルゴリズムとは、JOINで内部表にインデックスによるアクセスを行える場合に、
対象行の比較列をバッファに貯めてから、それをシーケンシャルに
→内部表でインデックスを使用する - Using MRR
MMR(Multi-Range Read)最適化が行われたことを表す。
MMR最適化とはセカンダリインデックスはソートされていないので、
実レコードの読み取りはランダムアクセスになるが、実レコード（主キー）の並び順にインデックスをソートした後に
実レコードの読み取りを行うことで、シーケンシャルに読み取りを行うことである。 - Using sort_union(…), Using union(…), Using intersect(…)
インデックスマージが行われたことを表す - Using temporary
テンポラリテーブルを使用したことを表す。
→使用する各列に対してインデックスが存在するか確認する - Using where
where節を使用した条件検索を行ったことを表す。検索にインデックスを使用したかどうかは、”Using index”が共に表示されているかどうかで分かる。
→”Using index”が無い場合はwhere節内で使用する列にインデックスを作成できないか、もしくはインデックスを使用できる条件式か(=、in等)確認する - Using where with pushed condition
NDB Clusterテーブルで、インデックスのない列と定数の比較を効率化して行ったことを表す。
→インデックスを使用できるのであれば、使用する
オプション設定
以下では必須ではない、用件毎に応じた設定について記載する
セキュリティ
セキュリティを高める設定を記載する
mysqlユーザのログイン不可
MySQLインストール時に勝手にMySQL起動用の”mysql”ユーザを作成してくれる。
ただし、デフォルトでは”mysql”ユーザはログイン可能となっているが、
ログインする必要はないので、ログインできないように変更する。
- 現在の設定値
[root@main1 ~]# cat /etc/passwd | grep mysql mysql:x:498:498:MySQL server:/var/lib/mysql:/bin/bash
- 設定変更
usermod -s /sbin/nologin mysql
- 変更後
[root@main1 ~]# cat /etc/passwd | grep mysql mysql:x:498:498:MySQL server:/var/lib/mysql:/sbin/nologin
chroot
selinux
ツール
mysql
MySQLに同梱される標準的なCLI対話クライアント。
直接クエリを実行するとこともできる。
使用方法
- 書式
- MySQLサーバに接続する
mysql -h <ホスト名> -u <ユーザ名> -p<パスワード>
- クエリを実行する
mysql <DB名> -e "<クエリ>"
- MySQLサーバに接続する
組み込みコマンド
- ?
helpと同じ - clear
現在の入力を破棄する - connect
サーバに再接続する - delimiter
デリミタを指定する - edit
エディタを使用してコマンドを編集する - ego
サーバにコマンドを送信し、結果を垂直に表示する - exit
サーバとの接続を切断する - go
サーバにコマンドを送信する - help
このヘルプを表示する - pager
ページャを指定する - nopager
ページャを使用しない - tee
出力結果をファイルに保存する - notee
teeを中止する - print
現在入力中のコマンドを表示する - prompt
プロンプトを表示する - quit
サーバとの接続を切断する - rehash
入力補完のためのハッシュを構築する - source
ファイルからコマンドを読み込んで実行する - status
サーバのステータスを表示する - system
シェルコマンドを実行する - use
デフォルトデータベースを変更する - charset
利用する文字コードを変更する。
再接続しても継続される。 - warnings
クエリ実行時に警告があると、自動的に警告の内容を表示する。 - nowarning
warningsを無効化する。
mysqladmin
MySQLの管理操作を行うことができるクライアントツール
使用方法
- 書式
mysqladmin -h <ホスト名> -u <ユーザ名> -p<パスワード> [オプション] <コマンド> [コマンド引数]
- オプション
- -i <間隔(秒)>
指定の間隔でコマンドを繰り返し実行する - -P <ポート番号>
ポート番号を指定する - -S <ソケットパス>
UNIXソケットのパスを指定する - -v
詳細表示を行う - -V
バージョン情報を表示する - -E
垂直に表示を行う - -w <リトライ回数>
接続に失敗した場合、指定の回数リトライする
- -i <間隔(秒)>
- コマンド
- create <データベース名>
データベースを作成する - drop <データベース名>
データベースを削除する
強制実行オプションの-fを加えることで、確認無しに削除ができる - debug
デバッグ情報を出力する
SUPER権限が必要 - extended-status
サーバのステータスを表示する - flush-hosts
ホスト情報のキャッシュを削除する - flush-logs
未反映のログデータを書き込む - flush-privileges
ユーザの権限情報を再読み込みする - flush-status
ステータス情報を初期化する - flush-tables
テーブルのデータをディスクにフラッシュする - flush-threads
スレッドキャッシュを削除する - kill <スレッドID>[,<スレッドID>…]
指定したスレッドを終了する - password <パスワード>
パスワードを指定したものに変更する - ping
死活情報を取得する.
０が稼働中で、１が停止している。
MySQLによりアクセス制限がされているホストからでも確認は可能。 - processlist
SHOW PROCESSLISTの結果を返す。
詳細オプションの–verboseを加えることで、SHOW FULL PROCESSLISTを代わりに実行することができる。 - reload
grantテーブルをリロードする - refresh
全てのテーブルをフラッシュし、ログファイルを一旦閉じ、再度開く。 - shutdown
サーバを停止する - start-slave
スレーブサーバとして動作を開始させる - stop-slave
スレーブサーバとしての動作を停止させる - variables
サーバ変数を表示する - version
バージョン情報を表示する
- create <データベース名>
perror
MySQLに同梱されているエラーコードの辞書ツール。
引数にエラーコードの数字を与えることで、そのエラーの意味を表示することができる。
エラーコードは複数同時に与えることができる。
- 実行例
[root@mysql1 ~]# perror 1 10 35 OS error code 1: Operation not permitted OS error code 10: No child processes OS error code 35: Resource deadlock avoided
mysqlslap
検査にはmysqlslapデータベースが必要だが、オプションにより検査時に作成することも可能。
- 作る場合
mysql create database mysqlslap;
オプション
参考：http://dev.mysql.com/doc/refman/5.1-olh/ja/mysqlslap.html
- 基本オプション
- -h, –host=name
接続するホスト名を指定する。
デフォルトではlocalhost。 - -P, –port=#
接続に使用するポート番号を指定する。 - -S, –socket=name
ソケットファイルのパスを指定する。 - -u, –user=name
ユーザ名を指定する。
指定しなかった場合は、現在のLinuxユーザでログインする。 - -p, –password[=name]
パスワードを使用する。
パスワードを指定しなかった場合は開始時に入力する。 - –protocol=name
接続に使用するプロトコルを指定する。
以下が使用可能である。
tcp, socket, pipe, memory
- -h, –host=name
- よく使うオプション
- -a, –auto-generate-sql
自動生成したSQLで試験を行う。 - –auto-generate-sql-execute-number=#
auto-generate-sqlオプションを使用するときに、
各々のクライアントに実行させる自動的に生成するクエリーの数を指定する。
number-of-queriesオプションとは同時に使用できない。 - –number-of-queries=<クエリー数>
各クライアントが実行するクエリーの合計数を指定する。 - -c, –concurrency=<クライアント数>
同時に負荷をかけるクライアント数を指定する。 - –auto-generate-sql-load-type=name
auto-generate-sqlオプションを使用するときに、
テストの内容を指定する。以下が使用可能。
- read
テーブルのスキャン - write
テーブルへの挿入 - mixed（デフォルト）
readとwriteを半々 - key
主キーの読み取り - update
更新処理
- read
- -e, –engine=name
テーブル作成時に使用するストレージエンジンを指定する。 - -v, –verbose
詳細データを出力する。
複数回指定でき、２回指定することで、試験の進行状況、
３回指定することで、実行中のSQLを出力することができる。 - -i, –iterations=#
テストを繰り返す回数を指定する。 - -q, –query=name
実行するクエリを指定するか、クエリが記載されたファイルのパスを指定する。
デフォルトであるmysqlslapデータベースを作成しておくか、create-schemaによってとりあえずデータベースを作成する必要がある。 - –create-schema=name
試験時に使用するデータベース名を指定する。
存在しない場合は作成も行う。
- -a, –auto-generate-sql
- その他のオプション
- –print-defaults
このオプションは最初に指定する必要がある。
Print the program argument list and exit. - –no-defaults
このオプションは最初に指定する必要がある。
Don’t read default options from any option file, except for login file. - –defaults-file=#
このオプションは最初に指定する必要がある。
Only read default options from the given file #. - –defaults-extra-file=#
このオプションは最初に指定する必要がある。
Read this file after the global files are read. - –defaults-group-suffix=#
このオプションは最初に指定する必要がある。
Also read groups with concat(group, suffix) - –login-path=#
このオプションは最初に指定する必要がある。
Read this path from the login file. - –auto-generate-sql-add-autoincrement
auto-generate-sqlオプションを使用するときに、
自動生成したテーブルにAUTO_INCREMENTを設定した列を作る。 - –auto-generate-sql-guid-primary
auto-generate-sqlオプションを使用するときに、
GUIDベースの主キーを自動生成されたテーブルに追加する。 - –auto-generate-sql-secondary-indexes=#
auto-generate-sqlオプションを使用するときに、
自動生成されたテーブルに追加するセカンダリーインデックスの数を指定する。 - –auto-generate-sql-unique-query-number=#
auto-generate-sqlオプションを使用するときに、
自動生成するクエリーの種類数を指定する。
デフォルトは10。 - –auto-generate-sql-unique-write-number=#
auto-generate-sqlオプションを使用するときに、
insert処理場合に使用するクエリーの種類数を指定する。
デフォルトは10 - –auto-generate-sql-write-number=#
auto-generate-sqlオプションを使用するときに、insert処理する回数を指定する。
デフォルトは100。 - –commit=<クエリー数>
指定した数のクエリー処理後にcommitを行う。 - -C, –compress
サーバ・クライアント間で転送の圧縮処理を行う。 - –create=name
試験中に作成するテーブル名かテーブル名が記載されたファイルのパスを指定する。 - –csv[=name]
結果をCSV出力する。
パスを指定した場合、そのファイルに結果を書き込む。
指定しなかった場合は、標準出力に出力する。 - -#, –debug[=#]
このオプションを指定すると実行せずに終了する。
デバッグのログを書き込む。 - –debug-check
終了時にメモリと開いているファイルの情報を出力する。 - -T, –debug-info
終了時にデバッグ情報を出力する。 - –default-auth=name
Default authentication client-side plugin to use. - -F, –delimiter=name
ファイルかコマンドラインで実行するSQLを指定した場合のデリミタを指定する。 - –detach=#
指定した数のクエリーごとにコネクションを一旦閉じ、再接続する。 - –enable-cleartext-plugin
クリアテキストによる認証プラグインを有効にする。 - –no-drop
試験終了後にデータを削除しない。 - -x, –number-char-cols=name
auto-generate-sqlオプションを使用するときに、使用するVARCHAR列の数を指定する。 - -y, –number-int-cols=name
auto-generate-sqlオプションを使用するときに、使用するINT列の数を指定する。 - –only-print
試験は実際には実施せず、実施したように出力を表示する。 - –plugin-dir=name
プラグインが設置されているディレクトリのパスを指定する。 - –post-query=name
試験終了後に実行するクエリを指定するか、クエリが記載されたファイルのパスを指定する。 - –post-system=name
終了時に実行するLinuxコマンドを指定する。 - –pre-query=name
試験開始時に実行するクエリを指定するか、クエリが記載されたファイルのパスを指定する。 - –pre-system=name
開始時に実行するLinuxコマンドを指定する。 - -s, –silent
試験結果の出力を行わない。 - –ssl
SSLを使用する。 - –ssl-ca=name
CA file in PEM format (check OpenSSL docs, implies –ssl). - –ssl-capath=name
CA directory (check OpenSSL docs, implies –ssl). - –ssl-cert=name
X509 cert in PEM format (implies –ssl). - –ssl-cipher=name
SSL cipher to use (implies –ssl). - –ssl-key=name
X509 key in PEM format (implies –ssl). - –ssl-crl=name
Certificate revocation list (implies –ssl). - –ssl-crlpath=name
Certificate revocation list path (implies –ssl). - –ssl-verify-server-cert
Verify server’s “Common Name” in its cert against hostname used when connecting. This option is disabled by default. - -V, –version
バージョンのみ表示する。
- –print-defaults
実行例
- 自動テスト
- 実行内容
mysqlslap -h XXX -u XXX --password=XXX -v -v -c 10 -a --auto-generate-sql-execute-number=100
- 出力
Warning: Using a password on the command line interface can be insecure. Building Create Statements for Auto Building Query Statements for Auto Parsing engines to use. Starting Concurrency Test Loading Pre-data Generating primary key list Generating stats Benchmark Average number of seconds to run all queries: 0.790 seconds Minimum number of seconds to run all queries: 0.790 seconds Maximum number of seconds to run all queries: 0.790 seconds Number of clients running queries: 10 Average number of queries per client: 100
- SQLを指定する
- 実行内容
mysqlslap -h XXX -u XXX --password=XXX -v -v -v -c 10 -q "select * from DB1.TABLE1;" --create-schema=DB1
sql-bench
MySQLインストール時に同時にインストールされるベンチマーク用のツール
使用時にカレントディレクトリを以下に変更する必要がある。
cd /usr/local/mysql/sql-bench/
試験では作成済みのデータベースを使用するので、テスト用のデータベースがなければ、事前に作成する必要がある。
テーブルは不要。デフォルトではtestデータベースが使用されるが、任意のものに変更可能。
参考:http://dev.mysql.com/doc/refman/5.1/ja/mysql-benchmarks.html
関連パッケージのインストール
yum install perl-DBI yum install perl-DBD-MySQL
全てのベンチマーク実行
run-all-testsが全てのベンチマークを実行するツールである。
perl run-all-tests [オプション]
- オプション
オプションは次の形式で指定する。
--<オプション名>=<値>
- –database
使用するデータベース名を指定する。
デフォルトはtest。 - –log
テスト結果を–dirオプションで指定したパスに出力する - –dir
テスト結果を出力するディレクトリのパス - –machine=”machine or os_name”
マシン名やOS名を出力ファイル名に付加する。デフォルトではOS名+バージョンとなる。
hw=’some comments’
テスト結果にハードウェアに関する情報を付加する。 - –host=’host name’
接続するホスト名。デフォルトはlocalhost。 - –user=’user_name’
ユーザ名を指定する。 - –password=’password’
パスワードを指定する - –socket=’socket’
UNIXソケットのパスを指定する - –tcpip
TCP/IPを使用した試験を行う。 - –connect-options=’some connect options’
DBIモジュールに関するオプションを指定する。
MySQLの設定ファイルを指定したい場合、以下のように記述する。
- –connect-options=mysql_read_default_file=/etc/my.cnf
- –time-limit
ループ処理を行う試験時間の制限。
デフォルトは600秒。 - –small-test
小規模テストを実施する。 - –small-tables
試験中で扱うテーブルの行数を少なくして実行する - –random
試験で使用する値をランダムにする - –skip-test=test1[,test2,…]
実行しないテスト名を指定する。 - –silent
テスト起動時の情報を非表示とする。 - –server=’server name’
DBMSの種類を指定する。デフォルトではMySQL。
次のものが指定可能。
Access, Adabas, AdabasD, Empress, Oracle, Informix, DB2, mSQL, MS-SQL, MySQL, Pg, Solid, Sybase - –old-headers
以前実行した結果からヘッダを取得する。 - –use-old-results
テストを実施せずに、前回の実行結果を表示する - –optimization=’some comments’
optimizationに関するコメントを付加する。 - –only-missing-tests
前回試験に失敗した項目のみ再試験を行う。 - –odbc
試験にODBCを使用する。 - –lock-tables
テーブルをロックすることで、試験を高速に実行できるようになる。 - –fast-insert
“insert into table_name (….) values(…)”構文の代わりに”insert into table_name values(…)”を使用することで、
試験を高速に実行できるようになる。 - –fast
ANSI標準でないSQLを使用して試験を高速に実行できるようになる。 - –create-options
create分を実行時に付加するオプション。
例えば、create ～ ENGINE=InnoDB としたい場合は、以下のように指定する。
- –create-options=ENGINE=InnoDB
- –comments
実行結果にコメントを出力する
- 実行例
[root@mysql1 sql-bench]# perl run-all-tests --database=test1 Benchmark DBD suite: 2.15 Date of test: 2014-01-28 11:44:47 Running tests on: Linux 2.6.32-358.el6.x86_64 x86_64 Arguments: Comments: Limits from: Server version: MySQL 5.6.15 Optimization: None Hardware: alter-table: Total time: 80 wallclock secs ( 0.05 usr 0.04 sys + 0.00 cusr 0.00 csys = 0.09 CPU) ATIS: Total time: 32 wallclock secs ( 2.12 usr 0.15 sys + 0.00 cusr 0.00 csys = 2.27 CPU) big-tables: Total time: 12 wallclock secs ( 0.96 usr 0.19 sys + 0.00 cusr 0.00 csys = 1.15 CPU) connect: Total time: 54 wallclock secs (14.01 usr 6.95 sys + 0.00 cusr 0.00 csys = 20.96 CPU) create: Total time: 9372 wallclock secs ( 6.00 usr 1.39 sys + 0.00 cusr 0.00 csys = 7.39 CPU) insert: Total time: 6469 wallclock secs (137.47 usr 20.25 sys + 0.00 cusr 0.00 csys = 157.72 CPU) select: Total time: 177 wallclock secs (12.28 usr 1.64 sys + 0.00 cusr 0.00 csys = 13.92 CPU) transactions: Test skipped because the database doesn't support transactions wisconsin: Total time: 85 wallclock secs ( 1.74 usr 0.36 sys + 0.00 cusr 0.00 csys = 2.10 CPU) All 9 test executed successfully Totals per operation: Operation seconds usr sys cpu tests alter_table_add 47.00 0.02 0.00 0.02 100 alter_table_drop 26.00 0.01 0.00 0.01 91 connect 4.00 1.48 0.64 2.12 10000 connect+select_1_row 5.00 1.67 0.75 2.42 10000 connect+select_simple 4.00 1.60 0.73 2.33 10000 count 6.00 0.01 0.00 0.01 100 count_distinct 3.00 0.08 0.01 0.09 1000 count_distinct_2 7.00 0.11 0.01 0.12 1000 count_distinct_big 7.00 1.43 0.00 1.43 120 count_distinct_group 6.00 0.23 0.02 0.25 1000 count_distinct_group_on_key 5.00 0.10 0.02 0.12 1000 count_distinct_group_on_key_parts 5.00 0.24 0.01 0.25 1000 count_distinct_key_prefix 2.00 0.08 0.00 0.08 1000 count_group_on_key_parts 4.00 0.21 0.02 0.23 1000 count_on_key 38.00 2.98 0.56 3.54 50100 create+drop 496.00 1.70 0.38 2.08 10000 create_MANY_tables 431.00 1.15 0.25 1.40 10000 create_index 1.00 0.00 0.00 0.00 8 create_key+drop 8076.00 1.96 0.38 2.34 10000 create_table 1.00 0.01 0.00 0.01 31 delete_all_many_keys 1103.00 0.01 0.00 0.01 1 delete_big 0.00 0.00 0.00 0.00 1 delete_big_many_keys 1102.00 0.01 0.00 0.01 128 delete_key 18.00 0.26 0.05 0.31 10000 delete_range 3.00 0.00 0.00 0.00 12 drop_index 0.00 0.00 0.00 0.00 8 drop_table 1.00 0.00 0.00 0.00 28 drop_table_when_MANY_tables 280.00 0.72 0.16 0.88 10000 insert 962.00 8.88 5.00 13.88 350768 insert_duplicates 9.00 2.19 0.33 2.52 100000 insert_key 867.00 5.43 1.03 6.46 100000 insert_many_fields 8.00 0.11 0.03 0.14 2000 insert_select_1_key 3.00 0.00 0.00 0.00 1 insert_select_2_keys 3.00 0.00 0.00 0.00 1 min_max 3.00 0.01 0.00 0.01 60 min_max_on_key 11.00 4.17 0.77 4.94 85000 multiple_value_insert 3.00 0.12 0.01 0.13 100000 once_prepared_select 12.00 2.36 0.86 3.22 100000 order_by_big 5.00 4.06 0.00 4.06 10 order_by_big_key 5.00 4.53 0.00 4.53 10 order_by_big_key2 5.00 4.23 0.00 4.23 10 order_by_big_key_desc 6.00 4.43 0.00 4.43 10 order_by_big_key_diff 5.00 4.11 0.00 4.11 10 order_by_big_key_prefix 5.00 4.26 0.00 4.26 10 order_by_key2_diff 0.00 0.38 0.00 0.38 500 order_by_key_prefix 0.00 0.20 0.00 0.20 500 order_by_range 1.00 0.20 0.00 0.20 500 outer_join 8.00 0.00 0.00 0.00 10 outer_join_found 8.00 0.00 0.00 0.00 10 outer_join_not_found 7.00 0.00 0.00 0.00 500 outer_join_on_key 9.00 0.00 0.00 0.00 10 prepared_select 13.00 5.87 0.00 5.87 100000 select_1_row 8.00 1.58 0.77 2.35 100000 select_1_row_cache 7.00 1.46 0.81 2.27 100000 select_2_rows 8.00 1.57 0.82 2.39 100000 select_big 5.00 4.69 0.01 4.70 80 select_big_str 2.00 0.61 0.26 0.87 10000 select_cache 29.00 0.72 0.02 0.74 10000 select_cache2 29.00 0.71 0.08 0.79 10000 select_column+column 8.00 1.51 0.78 2.29 100000 select_diff_key 1.00 0.03 0.00 0.03 500 select_distinct 1.00 0.32 0.00 0.32 800 select_group 6.00 0.29 0.01 0.30 2911 select_group_when_MANY_tables 89.00 0.47 0.18 0.65 10000 select_join 0.00 0.14 0.00 0.14 100 select_key 26.00 10.64 1.78 12.42 200000 select_key2 28.00 10.25 1.99 12.24 200000 select_key2_return_key 27.00 10.09 1.90 11.99 200000 select_key2_return_prim 29.00 10.32 1.81 12.13 200000 select_key_prefix 29.00 10.25 1.15 11.40 200000 select_key_prefix_join 2.00 0.85 0.00 0.85 100 select_key_return_key 25.00 10.32 1.89 12.21 200000 select_many_fields 4.00 0.85 0.16 1.01 2000 select_range 11.00 1.66 0.01 1.67 410 select_range_key2 3.00 1.08 0.09 1.17 25010 select_range_prefix 2.00 1.03 0.10 1.13 25010 select_simple 4.00 1.28 0.71 1.99 100000 select_simple_cache 4.00 1.25 0.68 1.93 100000 select_simple_join 0.00 0.16 0.00 0.16 500 update_big 30.00 0.01 0.00 0.01 10 update_of_key 164.00 1.70 0.28 1.98 50000 update_of_key_big 12.00 0.02 0.01 0.03 501 update_of_primary_key_many_keys 1938.00 0.00 0.02 0.02 256 update_with_key 881.00 9.34 1.67 11.01 300000 update_with_key_prefix 303.00 5.01 0.83 5.84 100000 wisc_benchmark 1.00 0.82 0.00 0.82 114 TOTALS 17355.00 172.64 30.84 203.48 3425950
mysqldump
MySQLのデータベース・テーブルのバックアップツール。
標準出力に出力するので、ファイルへリダイレクトする
マニュアル
使用方法
- 書式
mysqldump [オプション] (<データベース名> [テーブル名] | --databases) > <ファイル名>
- リストア
mysql -u <ユーザ名> -p<パスワード> <データベース名> < <ファイル名>
※データベースは事前に作成しておく。
※データベース全体をリストアする場合はデータベース名の指定は不要。
※先にテーブル作成をしておく場合はエラーを無視する-fオプションを使用してリストアする。
- オプション
- –print-defaults
引数のみを表示して終了する - –no-defaults
Don’t read default options from any option file, except for login file. - –defaults-file=#
Only read default options from the given file #. - –defaults-extra-file=#
Read this file after the global files are read. - –defaults-group-suffix=#
Also read groups with concat(group, suffix) - –login-path=#
Read this path from the login file. - -A, –all-databases
すべてのデータベース内のすべてのテーブルをダンプする - -Y, –all-tablespaces
テーブルダンプに、NDB Clusterテーブルに使用されるテーブルスペース作成に必要なSQLステートメントを追加する - -y, –no-tablespaces
Do not dump any tablespace information. - –add-drop-database
DROP DATABASEステートメントをCREATE DATABASEステートメントの前に追加します。 - –add-drop-table
DROP TABLEステートメントをCREATE TABLEステートメントの前に追加します。 - –add-drop-trigger
Add a DROP TRIGGER before each create. - –add-locks
テーブルをリストアする前後にロックの取得・解除を行う。
高速にリストアできる。 - –allow-keywords
Allow creation of column names that are keywords. - –apply-slave-statements
Adds ‘STOP SLAVE’ prior to ‘CHANGE MASTER’ and ‘START
SLAVE’ to bottom of dump. - –bind-address=name
IP address to bind to. - –character-sets-dir=name
Directory for character set files. - -i, –comments
バージョンやホスト名といった追加情報をダンプ中に出力する。
デフォルトで有効であるので、無効にする場合は–skip-commentsオプションを使用する。 - –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 –skip-add-locks
skip-comments –skip-disable-keys –skip-set-charset.
- –compact
- -c, –complete-insert
insertステートメントに列名を記述する。 - -C, –compress
双方が圧縮をサポートしている場合、クライアント・サーバ間で行きかう情報を全て圧縮する - -a, –create-options
CREATE TABLEステートメントにおいて、MySQLでのみ使用可能なSQLオプションを使用することを許可する。
デフォルトで有効であるので、無効にする場合は–skip-create-optionsオプションを使用する。 - -B, –databases <データベース名>
データベース全体をダンプする。
このオプションを指定した場合はテーブル名は指定できない。 - -#, –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. - –delayed-insert
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 */; '/*!40000 ALTER TABLE tb_name ENABLE KEYS */;
をダンプ内に出力する。
デフォルトで有効になっている。 - –dump-slave[=#] This causes the binary log position and filename of the
master to be appended to the dumped data output. Setting
the value to 1, will printit as a CHANGE MASTER command
in the dumped data output; 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. - -E, –events
ダンプされたデータベースからイベントをダンプする - -e, –extended-insert
BULK INSERTステートメントを使用して、１つのINSERTステートメントで全てのレコードを入力する。
デフォルトで有効であるので、無効にする場合は–skip-extended-insertオプションを使用する。 - –fields-terminated-by=name
Fields in the output file are terminated by the given
string. - –fields-enclosed-by=name
Fields in the output file are enclosed by the given
character. - –fields-optionally-enclosed-by=name
Fields in the output file are optionally enclosed by the
given character. - –fields-escaped-by=name
Fields in the output file are escaped by the given
character. - -F, –flush-logs
ダンプを始める前にMySQLサーバログファイルをフラッシュする - –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
何らかのエラーが発生しても無視して進める - -?, –help
オプション一覧、変数一覧を表示する。 - –hex-blob Dump binary strings (BINARY, VARBINARY, BLOB) in
hexadecimal format. - -h <ホスト名>, –host=<ホスト名>
接続するホスト名を指定する。 - –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. - –include-master-host-port
Adds ‘MASTER_HOST=<host>, MASTER_PORT=<port>’ to ‘CHANGE
MASTER TO..’ in dump produced with –dump-slave. - –insert-ignore Insert rows with INSERT IGNORE.
- –lines-terminated-by=name
Lines in the output file are terminated by the given
string. - -x, –lock-all-tables
データベース全体をロックする。
デフォルトでは無効になっている。
このオプションを有効にすると自動的にsingle-transactionオプションとlock-tablesオプションが無効になる。 - -l, –lock-tables
テーブル読み込み時にロックする。
デフォルトで有効であるので、無効にする場合は–skip-lock-tablesオプションを使用する。 - –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. - –max-allowed-packet=<データサイズ(byte)>
サーバ間の通信で送受信可能なデータのサイズを指定する。 - –net-buffer-length=#
The buffer size for TCP/IP and socket communication. - –no-autocommit Wrap tables with autocommit/commit statements.
- -n, –no-create-db
CREATE DATABASE … IF EXISTSステートメントの出力をしない。
CREATE DATABASEは–all-databasesもしくは–databasesオプションを指定したときに出力されるので、
これらのオプションと共に使用する。 - -t, –no-create-info
create文を出力しない。 - -d, –no-data No row information.
- -N, –no-set-names Same as –skip-set-charset.
- –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[=<パスワード>]
パスワードを使用する。
パスワードの値を与えなかった場合、実行時に入力を求められる。 - -P, –port=<ポート番号>
ポート番号を指定する。 - –protocol=name
使用するプロトコルを指定する。
使用できる値はtcp、socket、pipe、memoryである。 - -q, –quick
クエリをバッファせずに直接標準出力に出力する。
デフォルトで有効になっており、無効にする場合は、–skip-quickオプションを使用する。 - -Q, –quote-names
テーブル名や列名などをバッククォート（`）で囲んで出力する。
デフォルトで有効であるので、無効にする場合は–skip-quote-namesオプションを使用する。 - –replace
INSERT INTOの変わりにREPLACE INTOを使用したダンプを生成する - -r, –result-file=name
Direct output to a given file. This option should be used
in systems (e.g., DOS, Windows) that use carriage-return
linefeed pairs (\r\n) to separate text lines. This option
ensures that only a single newline is used. - -R, –routines
Dump stored routines (functions and procedures). - –set-charset
SET NAMES default_character_setステートメントを出力する。
デフォルトで有効であるので、無効にする場合は–skip-set-charsetオプションを使用する。 - –set-gtid-purged[=name]
Add ‘SET @@GLOBAL.GTID_PURGED’ to the output. Possible
values for this option are ON, OFF and AUTO. If ON is
used and GTIDs are not enabled on the server, an error is
generated. If OFF is used, this option does nothing. If
AUTO is used and GTIDs are enabled on the server, ‘SET
@@GLOBAL.GTID_PURGED’ is added to the output. If GTIDs
are disabled, AUTO does nothing. Default is AUTO. - –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. - –dump-date
ダンプの末尾にダンプを取得した日時を出力する。
デフォルトで有効であるので、無効にする場合は–skip-dump-dateオプションを使用する。 - –skip-opt
Disable –opt. Disables –add-drop-table, –add-locks,
create-options, –quick, –extended-insert,
lock-tables, –set-charset, and –disable-keys. - -S, –socket=<ソケットパス>
UNIXソケットのパスを指定する - –ssl
SSLを有効にする。
このオプションを指定しなくても、SSLの設定を行うほかのオプションを設定すれば、自動的に有効になる。 - –ssl-ca=name
CA file in PEM format (check OpenSSL docs, implies
ssl). - –ssl-capath=name
CA directory (check OpenSSL docs, implies –ssl). - –ssl-cert=name
X509 cert in PEM format (implies –ssl). - –ssl-cipher=name
SSL cipher to use (implies –ssl). - –ssl-key=name
X509 key in PEM format (implies –ssl). - –ssl-crl=name
Certificate revocation list (implies –ssl). - –ssl-crlpath=name
Certificate revocation list path (implies –ssl). - –ssl-verify-server-cert
Verify server’s “Common Name” in its cert against
hostname used when connecting. This option is disabled by
default. - -T, –tab=name
Create tab-separated textfile for each table to given
path. (Create .sql and .txt files.) NOTE: This only works
if mysqldump is run on the same machine as the mysqld
server. - –tables
Overrides option –databases (-B). - –triggers
Dump triggers for each dumped table.
デフォルトで有効であるので、無効にする場合は–skip-triggersオプションを使用する。 - –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.
デフォルトで有効であるので、無効にする場合は–skip-tz-utcオプションを使用する。 - -u, –user=name
ユーザ名を指定する。
デフォルトは現在のOSのユーザ。 - -v, –verbose
Print info about the various stages. - -V, –version
Output version information and exit. - -w, –where=name
Dump only selected records. Quotes are mandatory. - -X, –xml
Dump a database as well formed XML. - –plugin-dir=name
Directory for client-side plugins. - –default-auth=name
Default authentication client-side plugin to use. - –<変数>=<値>
MySQLクライアントで使用できる変数を設定する。
使用できる変数、デフォルト値は以下
変数名 デフォルト値 all-databases FALSE all-tablespaces FALSE no-tablespaces FALSE add-drop-database FALSE add-drop-table TRUE add-drop-trigger FALSE add-locks TRUE allow-keywords FALSE apply-slave-statements FALSE bind-address (No default value) 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 dump-slave 0 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) flush-logs FALSE flush-privileges FALSE force FALSE hex-blob FALSE host (No default value) include-master-host-port FALSE 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 25165824 net-buffer-length 1046528 no-autocommit FALSE no-create-db FALSE no-create-info FALSE no-data FALSE order-by-primary FALSE port 0 quick TRUE quote-names TRUE replace FALSE routines FALSE set-charset TRUE single-transaction FALSE dump-date TRUE socket (No default value) ssl FALSE ssl-ca (No default value) ssl-capath (No default value) ssl-cert (No default value) ssl-cipher (No default value) ssl-key (No default value) ssl-crl (No default value) ssl-crlpath (No default value) ssl-verify-server-cert FALSE tab (No default value) triggers TRUE tz-utc TRUE user (No default value) verbose FALSE where (No default value) plugin-dir (No default value) default-auth (No default value)
- –print-defaults
mysqlshow
データベースやテーブル、インデックスなどの定義情報を出力する
mysqlcheck
テーブルデータの検査や修復、最適化を行う。
ダウンロードツール
MySQL Administrator
MySQL Workbenchに置き換わった、旧公式の管理系GUIツール。
MySQL Query Browser
MySQL Workbenchに置き換わった、旧公式のSQL操作系GUIツール。
MySQL Workbench
MySQL WorkbenchとはMySQL公式の管理ツールでPC上で動作するGUIツールである。
実行中のSQLの監視やSQLのExplainの実行など、パフォーマンスチューニングに使用できる。
- 公式ページ
http://www-jp.mysql.com/products/workbench/ - マニュアル
http://dev.mysql.com/doc/workbench/en/index.html - ダウンロード
Windowsの最新版はこちら
MySQLサーバへPCは直接接続可能か、SSH接続可能なサーバから直接接続可能な必要がある。
アカウント作成
リモートから接続可能な管理者アカウントがない場合は作成する。
ユーザ作成
# 参考 grant all on *.* to admin identified by "password";
管理データベース追加
ホーム画面からMySQL Connectionsを作成する
mMeasure
mMeasureは、MySQLの状態を常時測定し、MySQLのチューニングポイントをアドバイスする、MySQL専用モニタリングソフトです。 MySQLの主要なサーバー変数やステータスは、時／日／週／月／年の単位でビジュアルにグラフ化され、ブラウザで参照することができます。 「クエリーキャッシュ使用率」や「接続数」といった測定値が、あらかじめ設定されたしきい値を超えた場合、 MySQLをチューニングするためのアドバイスである「チューニングアドバイス」を表示します。 同時に、チューニングアドバイスは「アラートメール」で管理者宛てにメール送信されますので、 MySQLのチューニングが必要なタイミングが自動的に分かるという特徴を持っています。
http://mmeasure.sourceforge.jp/
MySQL5.1以降はサポートしておらず、動作させるのに何らかの手を加える必要があるらしい。
mytop
mytopはtopコマンドに似た表示形式を持つツールで、
MySQLのスレッドの状態を出力する。
http://jeremy.zawodny.com/mysql/mytop/
※MySQL 4.Xとなっているが、5系も動作する
なお、AWSではパッケージが提供されている。
インストール
- 関連パッケージをインストールする
yum install perl-TermReadKey perl-DBI perl-DBD-MySQL perl-ExtUtils-MakeMaker
- ソースコードをダウンロードする
wget http://jeremy.zawodny.com/mysql/mytop/mytop-1.6.tar.gz
- 展開する
tar xvzf mytop-*.tar.gz cd mytop-*
- インストールする
perl Makefile.PL make make install
- そのままでは動作しないので、修正する
chmod u+w `which mytop` vi `which mytop`
次の行を#コメントアウトする
"long|!" => \$config{long_nums},権限を戻す。
chmod 555 `which mytop`
- 接続確認する
mytop -h <ホスト名> -d <データベース名> -u <ユーザ名> -p <パスワード>
ローカルホスト宛の場合は、以下。
mytop -S <ソケットファイルパス> -d <データベース名> -u <ユーザ名> -p <パスワード>
- 不要なファイルを削除する
cd ../ rm -rf mytop-*
使用方法
- 書式
mytop [オプション]
- オプション
- -h
宛先ホスト
デフォルトではlocalhost。 - -d
接続データベース。
デフォルトではtest。 - -P
接続ポート番号。
デフォルトでは3306。 - -S
ソケットファイルのパス。
デフォルトでは/var/lib/mysql/mysql.sock。 - -u
接続ユーザ名。
デフォルトではroot。 - -p
接続ユーザのパスワード。
デフォルトでは空文字列。 - -s
リフレッシュ間隔。
デフォルトでは5秒。 - -b
バッチモードとして動作し、一度のみ出力する。 - -noidle
idle（sleep）状態のスレッドを表示しない - -prompt
プロンプトでパスワードを入力する - -resolve
skip-resolve変数をMySQLに設定して、接続元ホストの名前逆引きを行ってない場合、
mytopの表示を接続もとの名前を逆引きして表示させる。
- -h
設定ファイル
~/.mytop
上記のパスにファイルを置くことで、設定を実行時に読み込ませることができる。
書式は以下のとおり。
user=root
pass=
host=localhost
db=test
delay=5
port=3306
socket=
batchmode=0
header=1
color=1
idle=1
実行結果例
Copyright (C) 2000-2001, Jeremy D. Zawodny. MySQL on localhost (5.6.15) up 6+20:20:35 [10:45:10] Queries: 9.0 qps: 0 Slow: 0.0 Se/In/Up/De(%): 00/00/00/00 qps now: 0 Slow qps: 0.0 Threads: 9 ( 1/ 0) 00/00/00/00 Key Efficiency: 88.9% Bps in/out: 0.0/ 0.1 Now in/out: 7.0/ 1.6k Id User Host/IP DB Time Cmd Query or State -- ---- ------- -- ---- --- ---------- 30080 root localhost mysql 0 Query show full processlist 30078 root localhost:49955 8 Sleep 30079 root localhost:49956 8 Sleep 30076 root localhost:49953 1210 Sleep 30077 root localhost:49954 1210 Sleep 30054 root localhost:49945 57548 Sleep 30055 root localhost:49946 57548 Sleep 30061 root localhost:49952 58608 Sleep 30056 root localhost:49947 70138 Sleep
Maatkit
いくつかの管理ツールが含まれるツールキット
mysqltuner
チューニングすべきポイントを検出するツール
以下からダウンロードするかそのままコピーペーストするだけで動作する。
https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
実行結果例
[root@mysql1 ~]# perl mysqltuner.pl >> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [!!] Successfully authenticated with no password - SECURITY RISK! -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.6.15 [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52) [!!] InnoDB is enabled but isn't being used [OK] Total fragmented tables: 0 -------- Security Recommendations ------------------------------------------- [!!] User 'root@127.0.0.1' has no password set. [!!] User 'root@::1' has no password set. [!!] User 'root@localhost' has no password set. -------- Performance Metrics ------------------------------------------------- [--] Up for: 6d 3h 18m 58s (3M q [6.382 qps], 30K conn, TX: 1B, RX: 217M) [--] Reads / Writes: 68% / 32% [--] Total buffers: 169.0M global + 1.1M per thread (151 max threads) [OK] Maximum possible memory usage: 338.9M (34% of installed RAM) [OK] Slow queries: 0% (153/3M) [OK] Highest usage of available connections: 4% (7/151) [OK] Key buffer size / total MyISAM indexes: 8.0M/97.0K [!!] Key buffer hit rate: 88.9% (36 cached / 4 reads) [!!] Query cache efficiency: 0.0% (0 cached / 2M selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 3% (200 temp sorts / 5K sorts) [OK] Temporary tables created on disk: 0% (2 on disk / 13K total) [OK] Thread cache hit rate: 99% (7 created / 30K connections) [OK] Open file limit used: 0% (0/5K) [OK] Table locks acquired immediately: 100% (3M immediate / 3M locks) -------- Recommendations ----------------------------------------------------- General recommendations: Add skip-innodb to MySQL configuration to disable InnoDB Variables to adjust: query_cache_limit (> 1M, or use smaller result sets)
sysbench
ベンチマークツール
http://sysbench.sourceforge.net/docs/
インストール
MySQLのソースコードによるインストールを行う
最新版のソースコードのURLを調べる
http://dev.mysql.com/downloads/benchmarks.html
- ダウンロードする
wget http://downloads.mysql.com/source/sysbench-0.4.12.5.tar.gz
- 展開する
gunzip -c sysbench-*.tar.gz | tar zxv cd sysbench-*
- コンパイル設定を行う
./configure --with-mysql-libs=/usr/local/mysql/lib --prefix=/usr/local/sysbench-0.4.12.5
- インストールする
make make install
- 共有ライブラリの変更を行う
- 現在のライブラリを確認する
ldd `which sysbench` # 以下の場合変更が必要 # libmysqlclient.so.18 => /usr/lib64/mysql/libmysqlclient.so.18
- 設定ファイルを確認する
ls /etc/ld.so.conf.d/mysql*
- 全てリネームする
mv /etc/ld.so.conf.d/mysqlXXX.conf /etc/ld.so.conf.d/mysqlXXX.conf.unused
- 新しいconfを作成する
echo /usr/local/mysql/lib > /etc/ld.so.conf.d/mysql.conf
- 更新する
ldconfig
- 変更されていることを確認する
ldd `which sysbench` # libmysqlclient.so.18 => /usr/local/mysql/lib/libmysqlclient.so.18
- 現在のライブラリを確認する
- リンクを作成する
ln -s /usr/local/sysbench-0.4.12.5 /usr/local/sysbench ln -s /usr/local/sysbench/bin/sysbench /usr/local/bin/sysbench
- パスがない場合、追加する
which sysbench export PATH=$PATH:/usr/local/bin vi /etc/bashrc # 以下を追記する export PATH=$PATH:/usr/local/bin
オプション
- 基本オプション
- –mysql-host=<ホスト名>
- –mysql-socket=<ソケットパス>
- –mysql-port=<ポート番号>
- –mysql-db=<試験用データベース>
デフォルトはsbtest - –mysql-user=<ユーザ名>
- –mysql-password=<パスワード>
- 共通オプション
- –mysql-table-engine=<ストレージエンジン>
myisam, innodb, heap, ndbcluster, bdb, maria, falcon, pbxt - –myisam-max-rows=<行数>
- –num-threads
The total number of worker threads to create
デフォルトは1 - –max-requests=<リクエスト数>
実行するリクエスト数。
0は無限。
デフォルトは10000。 - –max-time=<実行時間（秒）>
実行時間
0は無限。
デフォルトは0 - –forced-shutdown
Amount of time to wait after –max-time before forcing shutdown. The value can be either an absolute number of seconds or as a percentage of the –max-time value by specifying a number of percents followed by the ‘%’ sign.
“off” (the default value) means that no forced shutdown will be performed.
デフォルトはoff。 - –thread-stack-size
各スレッドで使用するスタックサイズ。
デフォルトは32K - –init-rng
Specifies if random numbers generator should be initialized from timer before the test start off - –report-interval
Periodically report intermediate statistics with a specified interval in seconds. Note that statistics produced by this option is per-interval rather than cumulative. 0 disables intermediate reports 0 - –test=<テストモード>
テストモードを指定する。
デフォルトはなし。必須オプション。
cpu
threads
mutex
memory
fileio
oltp - –debug
デバッグ情報を出力する。
デフォルトはoff - –validate
試験結果の検証を行う。
Perform validation of test results where possible
デフォルトはoff - –verbosity=<詳細度レベル>
詳細表示を行う。0～5を指定可能で、数値が大きいほど詳細な情報が出力される。
デフォルトは4 - –percentile
SysBench measures execution times for all processed requests to display statistical information like minimal, average and maximum execution time. For most benchmarks it is also useful to know a request execution time value matching some percentile (e.g. 95% percentile means we should drop 5% of the most long requests and choose the maximal value from the remaining ones).
This option allows to specify a percentile rank of query execution times to count
デフォルトは95 - –validate
Perform validation of test results where possible
- –mysql-table-engine=<ストレージエンジン>
- cpuテストモード
- threadsテストモード
- mutexテストモード
- memoryテストモード
- fileioテストモード
- oltpテストモード
- oltp-test-mode
試験内容
以下を指定可能
デフォルトcomplex
- simpe
以下を実行
SELECT XXX FROM sbtest WHERE id=XXX
- complex
トランザクションを毎回作成し、トランザクション内で、
単純なSELECTからORDER BYを使用したもの、UPDATE、INSET、DELETE等の複合的な方法を行う。 - nontrx
トランザクションを使用せずに、SELECT、UPDATE、INSET、DELETEをそれぞれ行う。
- simpe
- oltp-read-only
リードオンリーモードにする。
UPDATE、DELETE、INSERTは実行されない。
デフォルトoff - oltp-skip-trx
BEGIN/COMMITを省略する。
デフォルトoff - oltp-reconnect-mode
試験中にコネクションの再接続を含めるかどうかを指定する。
以下の値が指定できる。
デフォルトはsession。
- session
試験中は再接続を行わない。 - query
SQL毎に再接続を行う。 - transaction
トランザクション毎に再接続を行う。
（トランザクションを使用するかどうかは試験内容による。） - random
ランダムなタイミングで再接続を行う。
- session
- oltp-range-size
rangeを使用するクエリーで使用する範囲。
デフォルトは100 - oltp-point-selects
１トランザクション中で実行する単純な１つの値を取得するselectの実行回数。
デフォルトは10 - oltp-simple-ranges
１トランザクション中のrangeを使用するクエリーの実行回数。
デフォルトは1 - oltp-sum-ranges
１トランザクション中のsum()を使用するクエリーの実行回数。
デフォルトは1 - oltp-order-ranges
１トランザクション中のorder byを使用するクエリーの実行回数。
デフォルトは1 - oltp-distinct-ranges
１トランザクション中のdistinctを使用するクエリーの実行回数。
デフォルトは1 - oltp-index-updates
１トランザクション中のインデックスを使用するupdateクエリーの実行回数。
デフォルトは1 - oltp-non-index-updates
１トランザクション中のインデックスを使用しないupdateクエリーの実行回数。
デフォルトは1 - oltp-nontrx-mode
oltp-test-modeをnontrxで実行するときの、クエリーの種類。
select, update_key, update_nokey, insert, deleteが指定できる。
デフォルトはselect - oltp-connect-delay=<停止時間（マイクロ秒）>
各コネクション接続後に処理を一時停止する時間
デフォルトは10000 - oltp-user-delay-min=<停止時間（マイクロ秒）>
各リクエスト処理後に処理を一時停止する最低時間
デフォルトは0 - oltp-user-delay-max=<停止時間（マイクロ秒）>
各リクエスト処理後に処理を一時停止する最大時間
デフォルトは0 - oltp-table-name
試験で使用するテーブルの名前。
デフォルトはsbtest - oltp-table-size=<テーブル行数>
試験で使用するテーブルのサイズ。
デフォルトは10000 - oltp-dist-type
乱数生成の方式。
uniform (一様乱数)、gauss (ガウス分布乱数)、special（特殊分布乱数）が、選択可能。
specialの場合はoltp-dist-pct、oltp-dist-resで、値を操作可能。
デフォルトはspecial - oltp-dist-pct
oltp-dist-typeでspecialを指定したときの均一分布乱数の生成範囲を指定する
デフォルトは1 - oltp-dist-res
oltp-dist-typeでspecialを指定したときの均一分布乱数の発生確率を指定する
デフォルトは75 - db-ps-mode
データベースがPrepared StatementのAPIをサポートしていた場合、使用するかどうか。
disable, autoが指定可能。
autoの場合、SysBenchは可能であれば、サーバサイドで使用する。
サーバサイドで使用できない場合は可能であればクライアントサイドで使用する。
デフォルトはauto
- oltp-test-mode
出力例
sysbench 0.4.13: multi-threaded system evaluation benchmark Running the test with following options: Number of threads: 50 Additional request validation enabled. Random number generator seed is 0 and will be ignored Doing OLTP test. Running mixed OLTP test Doing read-only test Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases) Using "BEGIN" for starting transactions Using auto_inc on the id column Validation mode enabled Using 1 test tables Threads started! Time limit exceeded, exiting... (last message repeated 49 times) Done. OLTP test statistics: queries performed: read: 479612 write: 0 other: 68516 total: 548128 transactions: 34258 (570.37 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 479612 (7985.12 per sec.) other operations: 68516 (1140.73 per sec.) Test execution summary: total time: 60.0632s total number of events: 34258 total time taken by event execution: 3001.8914 per-request statistics: min: 49.77ms avg: 87.63ms max: 184.05ms approx. 95 percentile: 107.49ms Threads fairness: events (avg/stddev): 685.1600/7.31 execution time (avg/stddev): 60.0378/0.02
- 出力項目
- Test execution summary
- total number of events
実行数（トランザクション数などテストの種類によって単位は異なる） - total time taken by event execution
全スレッドの合計実行時間（およそtotal time×スレッド数） - approx. 95 percentile
上位5％を除いた中のmax時間
- total number of events
- Threads fairness
- events (avg/stddev)
- execution time (avg/stddev)
- Test execution summary
OLTPベンチマークの実施例
特定時間の間のクエリー実行回数を調べる
- 試験用のデータベースを作成する（２回目以降は不要）
mysql -h <接続ホスト> -u <ユーザ> -p # mysql内で以下を実行 create database sbtest; exit;
- 試験用データを作成する
sysbench --test=oltp --db-driver=mysql --mysql-host=<接続ホスト> --mysql-user=<ユーザ> --mysql-password=<パスワード> \ --oltp-test-mode=complex --oltp-table-size=150000 --oltp-connect-delay=0 \ --mysql-table-engine=innodb --num-threads=20 --max-time=60 --max-requests=0 prepare
- キャッシュにデータを読み込む
sysbench --test=oltp --db-driver=mysql --mysql-host=<接続ホスト> --mysql-user=<ユーザ> --mysql-password=<パスワード> \ --oltp-test-mode=complex --oltp-table-size=150000 --oltp-connect-delay=0 \ --mysql-table-engine=innodb --num-threads=20 --max-time=60 --max-requests=0 --oltp-read-only run
- 試験を実施する
sysbench --test=oltp --db-driver=mysql --mysql-host=<接続ホスト> --mysql-user=<ユーザ> --mysql-password=<パスワード> \ --oltp-test-mode=complex --oltp-table-size=150000 --oltp-connect-delay=0 \ --mysql-table-engine=innodb --num-threads=20 --max-time=60 --max-requests=0 --num-threads=<スレッド数> run
- 試験データを削除する
sysbench --test=oltp --db-driver=mysql --mysql-host=<接続ホスト> --mysql-user=<ユーザ> --mysql-password=<パスワード> \ --oltp-test-mode=complex --oltp-table-size=150000 --oltp-connect-delay=0 \ --db-ps-mode=disable --mysql-table-engine=innodb --num-threads=20 --max-time=60 --max-requests=0 cleanup
１クライアントホストのスレッド数を400以上にする場合、–db-ps-mode=disableオプションをつけないとエラーが出る可能性がある。
Database Test Suite
Super Smack
tpcc-mysql
innotop
インストール
- 関連パッケージをインストールする
yum install perl-DBI perl-DBD-mysql perl-ExtUtils-MakeMaker perl-Time-HiRes perl-TermReadKey -y
- 最新版のソースコードのURLを調べる
http://code.google.com/p/innotop/downloads/list - ダウンロードする
wget http://innotop.googlecode.com/files/innotop-1.9.1.tar.gz
- 展開する
tar zfvx innotop-* cd innotop-*
- インストールする
perl Makefile.PL make install
- インストールされたことを確認する
which innotop
※見つからない場合、/usr/local/binをPATHに追加する。
使用方法
- 書式
innotop -u <username> -p <password> -h <hostname> [オプション]
- オプション
- –color
Enable or disable terminal coloring. Corresponds to the “color” config file setting.
- –color
- –config
Specifies a configuration file to read. This option is non-sticky, that is to say it does not persist to the configuration file itself.
- –count
Refresh only the specified number of times (ticks) before exiting. Each refresh is a pause for “interval” seconds, followed by requesting data from
MySQL connections and printing it to the terminal.
- –delay
Specifies the amount of time to pause between ticks (refreshes). Corresponds to the configuration option “interval”.
- –help
Print a summary of command-line usage and exit.
- –host <ホスト名>
接続ホスト
- –inc
Specifies whether innotop should display absolute numbers or relative numbers (offsets from their previous values). Corresponds to the configuration
option “status_inc”.
- –mode
Specifies the mode in which innotop should start. Corresponds to the configuration option “mode”.
- –nonint
Enable non-interactive operation. See “NON-INTERACTIVE OPERATION” for more.
- –password <パスワード>
パスワード
- –port <ポート番号>
接続ポート
- –skipcentral
Don’t read the central configuration file.
- –timestamp
In -n mode, write a timestamp either before every screenful of output, or if the option is given twice, at the start of every line. The format is
controlled by the timeformat config variable.
- –user <ユーザ名>
接続ユーザ
- –version
Output version information and exit.
- –write
Sets the configuration option “readonly” to 0, making innotop write the running configuration to ~/.innotop/innotop.conf on exit, if no configuration
file was loaded at start-up.
InnoDB Hot Backup
Xtra Backup
その他
md5sum
md5sumコマンドはファイルのハッシュ値を計算することができる。
テーブルデータファイルのハッシュ値を控えておくことで、更新があった場合に検地できる。
md5sum <ファイル>
定常運用
作業手順の基本は「準備」→「確認１」→「変更１」→「確認１」→「確認２」→「変更２」→「確認２」→…（必要な作業分だけ）…→「最終確認」→「事後作業」である。
管理系
ユーザ作成
- 準備
# ログインする mysql -h <ホスト名> -u <ユーザ名> -p # 現在のユーザの確認 select Host,User from mysql.user;
- 確認
# 追加するユーザが存在しないことを確認する show grants for <new-user>;
- ユーザ作成
grant <authority> on *.* to <new-user> identified by "<password>"; # 接続元を制限する場合 # grant <authority> on *.* to <new-user>@<srcIP> identified by "<password>"; # <srcIP>にネットワークアドレスを指定する場合、@"192.168.0.0/255.255.255.0" もしくは @192.168.128. のように指定する。
- 確認
# 追加するユーザが存在することを確認する # 設定した権限が付与されていることを確認する show grants for <new-user>;
ユーザ削除
- 準備
# 現在のユーザの確認 select Host,User from mysql.user;
- 確認
# 削除するユーザが存在することを確認する show grants for <new-user>;
- ユーザ削除
drop user <user>;
- 確認
# 削除したユーザが存在しないことを確認する show grants for <new-user>;
データベース系
データベースの作成
- 準備
# ログインする mysql -h <ホスト名> -u <ユーザ名> -p
# 現在のデータベースを確認する show databases;
- 確認
# 作成するデータベースが存在しないことを確認する show create database <new-database>;
- データベース作成
create database <params>;
- 確認
# 作成するデータベースが存在することを確認する show create database <new-table>;
テーブルの作成
- 準備
# ログインする mysql -h <ホスト名> -u <ユーザ名> -p
# テーブル作成するデータベースに接続する use <database>;
# 現在のテーブルを確認する show tables;
- 確認
# 現在接続しているデータベースを確認する select database();
# 作成するテーブルが存在しないことを確認する show create table <new-table>;
- テーブル作成
create table <params>;
- 確認
# 作成するテーブルが存在することを確認する show create table <new-table>;
テーブルの削除
- 準備
# ログインする mysql -h <ホスト名> -u <ユーザ名> -p
# テーブル削除するデータベースに接続する use <database>;
# 現在のテーブルを確認する show tables;
- 確認
# 現在接続しているデータベースを確認する select database();
# 削除するテーブルが存在することを確認する show create table <new-table>;
- テーブル作成
drop table <params>;
- 確認
# 削除するテーブルが存在しないことを確認する show create table <new-table>;
非定常運用
サーバ系
レプリケーションの開始
現在レプリケーションを行っていないサーバでレプリケーションを開始する。
InnoDBのリストア
MySQLを停止させた状態で、データファイルをクラッシュ前のものに置き換える。
MySQLを起動させると、自動でバイナリログを読み込んで、未適用のものが反映される。
オンラインバックアップ
mysqldumpを使用する
オフラインバックアップ
データファイルをコピーする
データ系
文字コードの変更
データベースとテーブル、照合順序、データの文字コードを変更する。
InnoDBでは767byteがサポートするインデックスの最大長であり、変更する再に制限を越える可能性があるので、注意する。
なお、utf8mb4を使用する場合、191文字まで。
- 準備
# 作業中、DBへのアクセスを行ってはいけない
# ログインする mysql -h <ホスト名> -u <ユーザ名> -p
# データベースが変更前の文字コードで定義されていることを確認する show create database <データベース名>; # 必要に応じて以下も実施する show create table <データベース名>.<テーブル名>\G
# ログアウトする quit
- データベースデータの取得
# データベースのバックアップを取得する mysqldump -h <ホスト名> -u <ユーザ名> -p <データベース名> > mysql_dump.old
文字コードの変更
sed "s/<旧文字コード>/<新文字コード>/g" mysql_dump.old > mysql_dump.new # utf8からutf8mb4に変更する場合は正規表現の否定先読みが必要なので、perlで行う # perl -e 'open(F,"mysql_dump.old");while(my$line=<F>){$line=~s/utf8(?!mb4)/utf8mb4/g;print("$line");}' > mysql_dump.new
- データベースの削除
mysqladmin -h <ホスト名> -u <ユーザ名> -p drop <データベース名> # プロンプトに y と答える
- データベースの再作成
mysqladminでは文字コードを指定して作成できないのでmysqlコマンドで行う
# データベースの作成 echo "create database <データベース名> character set <新文字コード>;" | mysql -h <ホスト名> -u <ユーザ名> -p
# データの投入 mysql -h <ホスト名> -u <ユーザ名> -p venusr_guild1 < mysql_dump.new
- 確認
- 差分の確認
投入したデータをダンプし、元のダンプファイルと差分を確認する
- 差分の確認
mysqldump -h <ホスト名> -u <ユーザ名> -p <データベース名> > mysql_dump.result diff mysql_dump.new mysql_dump.result # コメント行の差分は無視する # 万が一、insertステートメント中に差分が発生してしまった場合は、 # --skip-extended-insertオプションを使用してダンプを取得しなおし、 # どこで差分が出たのか確認する
- 文字コードの変更を確認する
# ログインする mysql -h <ホスト名> -u <ユーザ名> -p # データベースが新しい文字コードで定義されていることを確認する show create database <データベース名>; # ログアウトする quit
- 作業ファイルの削除
rm -f mysql_dump.old rm -f mysql_dump.new rm -f mysql_dump.result
障害対応
パフォーマンスチューニング
テーブルキャッシュ
下記コマンドでステータスを確認する。
show status like 'open%tables';
実行すると以下の２つの値が確認できる。それぞれ次の意味である。
- Open_tables：現在開いているテーブルの数。
- Opened_tables：MySQLサーバが起動してから開いたテーブルの総計。
一定期間上記を観察し、以下の場合、table_open_cacheの値を大きくするとパフォーマンスが改善され得る。
- Open_tablesがテーブルキャッシュの上限に達している（table_open_cache変数の値） → 新しくテーブルを開いてもキャッシュできない
show global variables like 'table_open_cache'; - Opened_tablesの増加幅が大きい → テーブルキャッシュできずに何度も開きなおしている
なお、table_open_cacheの値は最低でもmax_connectionsと同数にする必要がある。
show global variables like 'max_connections';
クエリキャッシュ
コミットの早期化
クエリキャッシュはテーブル変更後に破棄されるが、そのテーブルをコミットするまではキャッシュが再開されることはない。
そのため、長期にわたるトランザクションを避け、早期にコミットするようにしたほうがキャッシュをより有効に活用できる。
キャッシュの無効化
クエリキャッシュを有効にするとキャッシュの参照・格納・破棄等で多少オーバーヘッドが発生するので、
更新が多く参照が少ないなどキャッシュヒット率が著しく低い場合は無効にしたほうがよい。
大きな結果のキャッシュの無効化
クエリキャッシュを格納するためにMySQLはキャッシュ格納用のメモリブロックを走査しなければならない。
そのため、サイズの大きな結果は小さなものに比べると時間がかかる。また、キャッシュの破棄に関しても同様である。
そこで、結果が大きくなり、かつそれを再利用する可能性が極めて低い場合はそのクエリのキャッシュを無効化するのが良い。
キャッシュサイズの増量
show status;で確認できるQcache_hitsの値が想定より増加しない場合は
キャッシュサイズが不足して破棄されている可能性があるので、
キャッシュサイズを増加させる。
内部一時テーブルのサイズ変更
ステータスを取得し（”show global status;”）、Created_tmp_disk_tablesの値が増大していれば、tmp_table_sizeの値を増やしたほうが良い。
どうしてもメモリが割り当てられなければ、可能であればtmpdirのパスをSSD上にする。
クライアントプログラム
prepared statementの使用
一部の変数部分のみ異なる大量のSQLを発行する場合、prepared statementを使用することで、
通信量や、SQLパースの負荷を削減でき、高速化できる。
ストアドプロシージャ / ストアドファンクションの利用
メリット
- ひとつの要求で複数のSQL文を実行できる（ネットワークに対する負荷を軽減できる）
- あらかじめ構文解析や内部中間コードへの変換をすませるため、処理時間が軽減される
- データベーストリガと組み合わせることで、複雑なルールによるデータの参照整合性保持が可能になる。簡単に言えば、アプリケーション側にロジックを持たずとも、データベース内のデータのつじつまが合うようにすることができる。
- JAVA等のホスト言語とSQL文が確実に分離されソースコードの見通しが良くなること、また、Webサイトなど運用中でもストアドプロシージャの入れ替えによる修正が可能なため保守性に優れる。
デメリット
- データベース製品ごとに、記述する構文の規約がSQL/PSM規格との互換性が低いため、コード資産としての再利用性が悪い。
- ビジネスロジックの一部として利用する場合、業務の仕様変更に際して、外部のアプリケーションとともにストアドプロシージャの定義を変更する必要がある。このとき、よけいな手間や変更ミスによる障害を発生させる可能性がある。
- データベースにデータストアとして以外の責任を持たせることとなり、ロジック実装部の境界が分かりにくくなる
ストアドファンクションはストアドプロシージャと異なり、値を返すことができるので、他のSQLなどに組み込みその値を利用するといったことができる。
トリガの利用
何らかの処理をデータベースで行った際に別の処理を行いたいとき、トリガを利用できる。
例えば、あるテーブルに変更が発生した際にその変更箇所をログとしてログテーブルに追加するといったことができる。
リードオンリー化
トランザクションをリードオンリーとすることで、高速処理が可能となる。
次のコマンドをトランザクションで最初に実行すると良い。
START TRANSACTION READ ONLY
トランザクションではないコネクションでは使用できない。
遅延INSERT
INSERTを非同期で実行することで、ブロックされることなくプログラム側に処理を戻すことができる。
構文はINSERT DELAYEDである。
ただし、遅延INSERTはMyISAMなどの一部のストレージエンジンでしか対応しておらず、InnoDBでは使用できない。
型変換の不使用
数値型に対して、数値をシングルクォーテーションなどで囲った値と比較すると、文字列との比較になるので、一方の型変換が発生する。
型変換によるオーバーヘッドが発生するため、意図的にしろ、型変換は使用すべきではない。
つまり、
select ～ where num = '100';
は
select ～ where num = 100;
とする。
SQL
インデックスの不使用
バッチ処理などで大量のデータにアクセスする際にはインデックスを使用せずに
テーブルをフルスキャンしたほうが高速な場合がある。
その場合は、ignore indexを指定してインデックスを使用しないようにする。
select * from table1 ignore index(index1) where xxx = n;
テーブル設計
固定長・可変長
可変長のデータを扱う場合、データの断片化が起こり、テーブル容量が大きくなる。
そのため、可変長のデータサイズのバラつきが小さい場合、固定長にすることで断片化が防げる。
また、固定長のほうが処理が高速である。
可変長のデータサイズのバラつきが大きい場合は、可変長のほうが無駄なディスクI/Oが防げるので可変長のほうがよい。
インデックス
インデックス列の並び替え
複数の列からなるインデックスを使用する場合、よりカーディナリティが大きい（より少数に絞り込める）列を
前に持ってくるほうが検索効率が高くなる。