MySQLの監視はCacti+Percona Monitoring Pluginsがおすすめ(監視サーバ構築編)
MySQLをリソース監視する仕組みにはいくつかあるが、対象のMySQLサーバが5台以上ある場合はCactiがおすすめ。導入のしやすさだけでMuninを選ぶ人が多い気がするが、その選択基準は間違っている！
Cactiのいいとこわるいとこ
- 多数のグラフを見やすく並べられる
- muninと比べて多数のサーバから軽快に情報を収集・表示できる
- 監視対象には、MySQLのユーザを追加するだけでかなりの項目数を監視できる
- データの保存にデータベースが必須だったりしてセットアップがやや面倒
- 慣れるまで監視プラグインを書くのに手間取る
Muninのいいとこわるいとこ
- 監視プラグインを書くのが簡単
- 監視サーバにデータベースなどが必要なく、セットアップが簡単
- グラフの並び方などが固定で、比較などがしにくい
- 監視対象にプラグインを入れなければならず、台数が多いとそれだけ手間が増える
- 監視対象が増えると監視サーバの負荷が急激にあがるので、あまり多数のサーバは監視できない
そんなわけで、CentOS 6.2上にCactiの監視サーバをセットアップし、Percona Monitoring Plugins for Cactiを使ってMySQLサーバ(CentOSまたはUbuntu)のリソース監視を始めるまでの手順。その1は監視サーバのセットアップ編。
RPMforgeのリポジトリ追加
RPMforgeリポジトリには最新のCactiが含まれているため、これを導入する。
$ wget http://pkgs.repoforge.org/rpmforge-release/rpmforge-release-0.5.2-2.el6.rf.x86_64.rpm $ sudo rpm -ihv rpmforge-release-0.5.2-2.el6.rf.x86_64.rpm $ sudo yum -y update rpmforge-release
/etc/yum.repos.d/rpmforge.repo ファイルをエディタで開き、 enabled = 0 にする。これで、オプションを付けないとRPMforgeはリポジトリとして有効にならないようになる。
Cactiのインストール
Cactiと、あわせて必要なnet-snmpやhttpd、MySQLなどをインストール。
$ sudo yum install --enablerepo=rpmforge cacti net-snmp-utils mysql-server
指定したもの以外に、依存関係上httpd, mysql, perl, php, rrdtoolなどもインストールされる。
MySQLの設定
Cactiが取得した情報を格納するためのMySQLを設定する。ここでは最低限必要な設定だけを記述するが、CentOS 6.2のMySQLの/etc/my.cnfは非常にシンプルなので、必要に応じてパラメータを指定すること。
[mysqld]セクションに以下を追加
default-character-set = utf8 skip-character-set-client-handshake character-set-server = utf8 collation-server = utf8_general_ci init-connect = SET NAMES utf8
ここでMySQLを再起動する。
$ sudo /etc/init.d/mysqld restart
statusコマンドで、charactersetがutf8であることを確認。
mysql> status; (略) Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 (略)
Cactiのドキュメントにしたがって、Cactiで使用するMySQLの設定を行う。
$ mysqladmin --user=root create cacti $ mysql cacti < cacti.sql $ mysql --user=root mysql mysql> grant all on cacti.* to ユーザ名@localhost identified by 'パスワード'; mysql> flush privileges;
Apache/PHPの設定
/var/www/cacti/include/config.php を上の項で作成したユーザに合わせて編集する。
$database_type = "mysql"; $database_default = "cacti"; $database_hostname = "localhost"; # DBサーバのホスト名 $database_username = "cactiuser"; # 上の項で作成したユーザ $database_password = "cactipassword"; # 上の項で設定したパスワード
/etc/httpd/conf.d/cacti.php が存在することを確認する。他のホストからCactiにアクセスする際は、以下の行を追加してhttpdを再起動する。
allow from 127.0.0.1 allow from IPアドレス # 追記
Cactiのドキュメントを参考に、/etc/php.ini および /etc/php.d/*.ini の記述を確認。設定を変更したら、Apacheを再起動する。
$ sudo /etc/init.d/httpd restart
iptablesの設定
CentOS 6.2ではデフォルトでpingやSSHしか応答できないようにされているようなので、/etc/sysconfig/iptables に以下を追加してhttpのアクセスが可能なようにする。
-A INPUT -m state --state NEW -m tcp -p tcp --dport 80 -j ACCEPT
iptablesを再起動
$ sudo /etc/init.d/iptables restart
設定完了
Cactiをインストールした際に /etc/cron.d/cacti が既に作成されているので、この時点で5分おきに情報収集が始まっている。
http://CactiサーバのIPアドレス/cacti を開くと、まずインストールの確認ウィザードが開始される。
ここまでの設定を改めて確認されるだけなので、基本的にはNextを押していけばいい。各種ツールのパスを確認される画面が最後にあるが、ここでパスが見つからない場合は手動でパスを入力するか、不足しているツールをインストールして再度試すべし。
この後ログイン画面が表示され、ユーザ名 admin 、 パスワード admin でログイン可能で、すぐにパスワードの変更を促されるので画面の指示に従って変更する。
Percona Monitoring Plugin for Cactiの導入
PerconaからPercona monitoring pluginsをダウンロードし、ファイルを展開する。
$ tar zxvf percona-monitoring-plugins-1.0.0.tar.gz $ cd percona-monitoring-plugins-1.0.0/cacti/scripts $ sudo cp ss_get_by_ssh.php ss_get_mysql_stats.php /var/www/cacti/scripts/
監視対象のMySQLにログインする際のアカウント情報を ss_get_mysql_stats.php に書き込む。
$mysql_user = 'cactiuser'; # 監視対象にログインする際のMySQLユーザ $mysql_pass = 'cactiuser'; # 監視対象にログインする際のパスワード
Cacti管理画面からImport/Export → Import templatesを選択し、Import Template from Local Fileで以下を指定してImportボタンを押し、テンプレートファイルを読み込ませる。
percona-monitoring-plugins-1.0.0/cacti/templates/cacti_host_template_percona_mysql_server_ht_0.8.6i-sver1.0.0.xml
成功すると、それぞれの監視項目に対応したテンプレートごとに[success]と表示される。
これでMySQLサーバを監視するための監視サーバの準備は完了。
MyISAMを使っている時key_buffer_sizeは大きくし過ぎてもいけない
MyISAMのみを使っているMySQLサーバで、key_buffer_sizeのサイズは大きくても小さくてもダメですよ、という例。
その前にちょっと復習。MySQLの主なストレージエンジンといえばMyISAMとInnoDBだが、データやインデックスのキャッシュの仕組みには、
- InnoDB : インデックス、データともMySQLがキャッシュ管理する(innodb_buffer_pool_sizeで設定)
- MyISAM : インデックスはMySQLがキャッシュ管理する(key_buffer_sizeで大きさを設定)。データはOSのキャッシュ機構におまかせ
という違いがある。
ものすごく簡単に言えば、InnoDBの場合はなるべく大きな innodb_buffer_pool_size を設定してやれば、インデックスかデータかに関わらずメモリ上にキャッシュされて高速化が図れる可能性がある。一方MyISAMの場合、key_buffer_size を大きくしてもインデックスしかキャッシュされないので、OSがデータ部分をキャッシュするメモリ(つまりMySQLに割り当ててないメモリ)もある程度確保しておく必要がある。この辺りのことはMySQLのドキュメントには以下のように書かれている。
innodb_buffer_pool_size
専用サーバの場合物理メモリの80%。
MySQL 5.1 リファレンスマニュアル 13.5.4. InnoDB 起動オプションとシステム変数
key_buffer_size
「一般的には、マシンのメモリ使用率 25 % の値であることが好ましい」一方で
「Key_reads/Key_read_requests の比率は、0.01 より小さいことが望ましい」つまり
99.9%がキャッシュされていることが望ましいということ。
MySQL 5.1 リファレンスマニュアル 4.2.3. システム変数
とある参照用のスレーブ(MyISAMのみ使用)で、アクセス数とともにデータ量も増加して、インデックスがメモリに乗りきらず、Key_reads/Key_read_requests (以下key bufferヒット率という)が80%台まで落ちてしまっていた。
メモリを増設してkey_buffer_sizeを調子に乗ってでっかく取ってみたが、key bufferヒット率は99.9%になったものの、相変わらずディスクからの読み出しは多い。ここで、上に書いた仕組みに思い当たり、データ部分をキャッシュするOS用のメモリが不足しているのでは、と思って一旦key_buffer_sizeを少し減らしてみた。
- インデックスのサイズ : 22GB
- 従来 key_buffer_size : 4GB (OS物理メモリ12GB)
- 増強後 key_buffer_size : 12GB (OS物理メモリ24GB)
- その後 key_buffer_size : 8GB (OS物理メモリ24GB)
すると一目瞭然、以下のグラフの青枠部のようにディスクからの読み出しは半分以下に減った(赤線が設定を変更した時点)。
key_buffer_sizeを大きくしたからといってパフォーマンスが上がるというわけではないのである。上の例から分かるように、全インデックスがメモリに乗らなくても、よく使うインデックスがほぼメモリに乗っていれば問題ない。常にkey bufferヒット率が最大になり、ディスク読み出しが最小になる key_buffer_size を探ることが必要ということだ。サイズはオンラインでも変更できる。ただし、コマンドを実行するとkey bufferの中身は一旦空になり、改めてキャッシュが構築されるため一時的にディスクIOが大量に発生するので注意(停止できるサーバならキャッシュウォームアップをした方がよい)。
mysql> set global key_buffer_size = バイトでのサイズ(1GBなら1073741824);
確認は show variables で。以下は4GBの例。
mysql> show variables like 'key_buffer_size'; +-----------------+------------+ | Variable_name | Value | +-----------------+------------+ | key_buffer_size | 4294967296 | +-----------------+------------+ 1 row in set (0.00 sec)
そもそもインデックスがでか過ぎるのでシェイプアップすべきとか、テーブル分割を考えるべきといった別の解決方法も考えられるのだが、わかりやすいグラフが取れたのでひとつの例として。
MySQLエラーログに出てきたエラーコードのとっかかり
MySQLのエラーログに記録されるエラーコードは、MySQL自体が判断して表示するものと、OSが返したエラーコードをMySQLが表示しているものとの2種類に分けられる。それぞれまずどう調べ始めるか、という話。MySQL Performance Blogのエントリに、後で自分の役に立つように情報を加えてみた。
例えば、レプリケーションがうまくいかない時に表示される
Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position'
というエラーの「1236」は、MySQL自体が判断した結果なので、以下のサイトで一覧が見られる。というか、表示されているままで、それ以上の情報はエラーログの前後や他のログ(OSログなど)を確認するしかない。
4.1
MySQL 4.1 リファレンスマニュアル 12.1. 返されるエラー
5.0/5.1/5.5 (バージョン部分を変えるだけで全部URLは同じ)
MySQL 5.0 Reference Manual C.3. Server Error Codes and Messages
MySQL 5.0 Reference Manual C.4. Client Error Codes and Messages
一方、以下のようなエラーの場合、
120326 16:56:45 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_21b2_0.MYI'; try to repair it 120326 16:56:45 [ERROR] Got an error from unknown thread, storage/myisam/mi_write.c:223 120326 16:56:45 [ERROR] /usr/sbin/mysqld: Sort aborted: Error writing file '/tmp/MYK74Kpi' (Errcode: 28)
エラーコードを調べないままだと、ファイルへの書き出しで失敗したことまでは分かっても、その先何が起きているのかは分からない。ここで、「Errcode: 28」は、OSが返しているエラーを表示している。これがどのような意味かは、perrorコマンドで分かる。
$ perror 28 OS error code 28: No space left on device
エラーログだけでは判断がつかないが、エラーコード28の意味が分かると、単純な問題であることが判明する。MyISAMはディスクがいっぱいであるというエラーを正しく扱わないという問題があるので、このようなことになる。
perrorコマンド、MySQLに付属している小ネタ的コマンドだが、OSエラーコードをそのまま表示する他のソフトウェアのエラーログを見る時にも役立ちそうだ。
MySQL 5.6.5の新機能GTIDを試してみる
4月10日にリリースされたMySQL 5.6.5 DMRに、GTID(Global Transaction ID)という機能が搭載された。これは、トランザクションにID(GTID)を持たせることによって、レプリケーションの進行具合を、従来の「マスタのbinlogファイル名 + ポジション」という情報ではなく、GTIDで管理できるようになるという機能である。従来のバージョンと比べた時に一番分かりやすい点としては、「change masterする時にポジションを指定しなくてよくなる」ということだろう。
MySQLのレプリケーション機能開発者Luis Soares氏のブログを参考に、GTIDの機能を試してみた。
MySQL 5.6.5のインストール
MySQLのダウンロードサイトからDevelopment Releaseを選択し、5.6.5 m8をダウンロードする。自分はUbuntu 11.04を使ったので、debファイルをダウンロードし、以下のコマンドを実行したところ、/opt/mysql以下にインストールされた。
dpkg -i mysql-5.6.5-m8-ubuntu10.04-x86_64.deb
ちなみに自分はマシンを2台用意できなかったので、/opt/mysql以下をコピーしてからmysql_install_dbコマンドを実行し、それぞれmy.cnfを作成して、1台の検証用サーバにポートを分けて2つのMySQLを起動した。
GTIDを使うのに必要な設定
GTIDを使用するには、レプリケーションを組むサーバ全体でGTIDを有効にする必要があり、それぞれのサーバのmy.cnfで以下のオプションを指定する。
log-bin log-slave-updates gtid-mode=ON disable-gtid-unsafe-statements
はじめの2つはレプリケーションを設定したことのある人なら見慣れたオプションだが、後半の2つが新しく追加されたもの。gtid-modeは単純にGTIDの機能を使用可能にするオプションで、disable-gtid-unsafe-statementsは、GTIDと互換性のない一部のSQLの実行を無効にするものである。
これらの設定を行ってMySQLを起動すると、GTIDが有効になる。
mysql> show variables like '%gtid%'; +--------------------------------+-----------+ | Variable_name | Value | +--------------------------------+-----------+ | disable_gtid_unsafe_statements | ON | | gtid_done | | | gtid_lost | | | gtid_mode | ON | | gtid_next | AUTOMATIC | | gtid_owned | | +--------------------------------+-----------+ 6 rows in set (0.00 sec)
レプリケーションの設定
GTIDを設定するとトランザクションごとにGTIDを持つと書いたが、まず簡単なcreate文を書いてみる。その後show binlog eventsでbinlogの中身を見てみると、クエリの情報の他にGTIDがセットされていることが分かる。
mysql-master> use test; Database changed mysql-master> create table t1 (a INT); Query OK, 0 rows affected (0.13 sec) mysql-master> show binlog events; +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.5-m8-log, Binlog ver: 4 | | mysql-bin.000001 | 120 | Previous_gtids | 1 | 147 | | | mysql-bin.000001 | 147 | Gtid | 1 | 191 | SET @@SESSION.GTID_NEXT= '7C3125E9-8C6D-11E1-8BE6-2C4138876DB7:1' | | mysql-bin.000001 | 191 | Query | 1 | 284 | use `test`; create table t1 (a INT) | +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ 4 rows in set (0.00 sec)
この例で表示されている、’7C3125E9-8C6D-11E1-8BE6-2C4138876DB7:1′が、直後のcreate tableに割り当てられたGTIDである。さらにinsert文を発行してみる。
mysql-master> insert into t1 values (1); Query OK, 1 row affected (0.13 sec) mysql-master> show binlog events; +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.5-m8-log, Binlog ver: 4 | | mysql-bin.000001 | 120 | Previous_gtids | 1 | 147 | | | mysql-bin.000001 | 147 | Gtid | 1 | 191 | SET @@SESSION.GTID_NEXT= '7C3125E9-8C6D-11E1-8BE6-2C4138876DB7:1' | | mysql-bin.000001 | 191 | Query | 1 | 284 | use `test`; create table t1 (a INT) | | mysql-bin.000001 | 284 | Gtid | 1 | 328 | SET @@SESSION.GTID_NEXT= '7C3125E9-8C6D-11E1-8BE6-2C4138876DB7:2' | | mysql-bin.000001 | 328 | Query | 1 | 403 | BEGIN | | mysql-bin.000001 | 403 | Query | 1 | 498 | use `test`; insert into t1 values (1) | | mysql-bin.000001 | 498 | Xid | 1 | 525 | COMMIT /* xid=10 */ | +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ 8 rows in set (0.00 sec)
insert文には、’7C3125E9-8C6D-11E1-8BE6-2C4138876DB7:2′というGUIDが振られたことが見て取れる。GUIDは、コロンの前のサーバのUUID(server_uuid変数)と、コロンの後の単純なトランザクションの通し番号からなっているのである。
この時点で、マスタ上には1行だけ値を持つテーブルt1があり、スレーブ側には何も入っていない状態になっているはず。これまでのMySQLの場合スレーブサーバを作るには、
- マスタを停止してファイルを取るかダンプしてデータをスレーブにコピー
- スレーブで、マスタの停止あるいはダンプ時点のbinlogファイル名とポジションを指定してchange master
- start slaveしてレプリケーションを開始し、マスタとスレーブの一致を確認
という手順が必要だった。一方5.6.5では、以下の文をスレーブで実行するだけで、マスタと同じ状態までレプリケーションが行われる。
mysql-slave> change master to master_host = 'マスタのIP', master_port=マスタのMySQLポート番号, master_user='レプリ用ユーザ', master_password='レプリ用ユーザパスワード', master_auto_position=1; mysql-slave> start slave;
最後のmaster_auto_positionによって、GTIDを使って自動的にポジションが決まるので、ポジションを指定する必要がない。レプリケーションを始めた状態でshow slave status\Gを実行すると、手元の環境では以下のようになった(長いので一部の行を省略)。
mysql-slave> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 525
Relay_Log_File: ubuntu-relay-bin.000002
Relay_Log_Pos: 727
Relay_Master_Log_File: mysql-bin.000001
Exec_Master_Log_Pos: 525
Relay_Log_Space: 924
Seconds_Behind_Master: 0
Master_Server_Id: 1
Master_UUID: 7c3125e9-8c6d-11e1-8be6-2c4138876db7
Master_Info_File: /opt/mysql3307/server-5.6/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Retrieved_Gtid_Set: 7C3125E9-8C6D-11E1-8BE6-2C4138876DB7:1-2
Executed_Gtid_Set: 7C3125E9-8C6D-11E1-8BE6-2C4138876DB7:1-2
1 row in set (0.00 sec)
最後の2行、Retrieved_Gtid_Setはマスタから転送してきたトランザクションのGTIDで、Executed_Gtid_Setはスレーブで実行されたトランザクションのGTIDである。ここでスレーブ側のbinlogの中身を見てみると、マスタで実行された文がそのまま実行されており、マスタのGTIDも保持されていることが分かる。
mysql-slave> show binlog events; +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 13307 | 120 | Server ver: 5.6.5-m8-log, Binlog ver: 4 | | mysql-bin.000001 | 120 | Previous_gtids | 13307 | 147 | | | mysql-bin.000001 | 147 | Gtid | 1 | 191 | SET @@SESSION.GTID_NEXT= '7C3125E9-8C6D-11E1-8BE6-2C4138876DB7:1' | | mysql-bin.000001 | 191 | Query | 1 | 284 | use `test`; create table t1 (a INT) | | mysql-bin.000001 | 284 | Gtid | 1 | 328 | SET @@SESSION.GTID_NEXT= '7C3125E9-8C6D-11E1-8BE6-2C4138876DB7:2' | | mysql-bin.000001 | 328 | Query | 1 | 403 | BEGIN | | mysql-bin.000001 | 403 | Query | 1 | 498 | use `test`; insert into t1 values (1) | | mysql-bin.000001 | 498 | Xid | 1 | 525 | COMMIT /* xid=18 */ | +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ 8 rows in set (0.00 sec)
なお、以下のselect文で、最後に実行された文のGTIDが参照できる。この結果がマスタとスレーブで一致していれば、データは同一であるということになる。
mysql-master> SELECT @@GLOBAL.GTID_DONE; +------------------------------------------+ | @@GLOBAL.GTID_DONE | +------------------------------------------+ | 7C3125E9-8C6D-11E1-8BE6-2C4138876DB7:1-2 | +------------------------------------------+ 1 row in set (0.00 sec)
ここまでで確認できたのは、基本的なGTIDの機能だけだが、この他にも自動フェイルオーバーが可能なmysqlfailoverや、レプリケーションの状態を詳細に確認できるmysqlrpladminなど、興味深いツールもあるので、試してみたい。5.6ではレプリケーション関連の色々な機能追加があるので、これからも便利な機能が出てくるのではと楽しみである。
TCP/IPの送信用ポート範囲を変更する
Linuxで、TCP/IPの送信時に使用するポートの範囲を決めるのは、net.ipv4.ip_local_port_range というカーネルパラメータ。手元のCentOSでは以下がデフォルト値になっていた。
# sysctl net.ipv4.ip_local_port_range net.ipv4.ip_local_port_range = 32768 61000
これだと、32768 から 61000 までの 28232 個の中からランダムに選ばれたポートを使って、サーバ側にアクセスする。このポートが足りなくなった場合、/etc/sysctl.conf に設定を書き込んでOSを再起動することで、範囲を変えられる。
# /etc/sysctl.conf に以下を追記 net.ipv4.ip_local_port_range = 1024 65000
再起動
# sysctl net.ipv4.ip_local_port_range net.ipv4.ip_local_port_range = 1024 65000
これで1024から65000までのポートを使用できるようになる。
MySQLスレーブのmy.cnfにはreport-hostを必ず書こう
世の中では当たり前のことなのかもしれないが、なぜか今勤めてる会社では一般的ではないのでメモしておく。
MySQLでレプリケーション構成を取っている時、スレーブとなるマシンのmy.cnfには、server-idと一緒にreport-hostを必ず書いておこう。
server-id = 一意のID slave-host = ホスト名
これで、マスターでshow slave hostsコマンドを打つだけで、スレーブの一覧が表示される。
> show slave hosts +-----------+----------------+------+-----------+ | Server_id | Host | Port | Master_id | +-----------+----------------+------+-----------+ | 16800111 | nanikano-dbs01 | 3306 | 16800101 | | 16800112 | nanikano-dbs02 | 3306 | 16800101 | | 16800113 | nanikano-dbs03 | 3306 | 16800101 | +-----------+----------------+------+-----------+
report-hostを書いていない時にマスタからスレーブの一覧を取得しようと思うと、マスターに接続しているプロセスの一覧から、スレーブへログを渡しているものを抜き出して知るしかない。以下のように、show processlistの結果から、ユーザがレプリケーション用のもの(change masterした時に指定したユーザ)で、CommandがBinlog Dumpとなっているものがスレーブとのレプリケーション用のプロセスだと分かる。
> show processlist; +---------+-----------+---------------------+----------+-------------+--------+----------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +---------+-----------+---------------------+----------+-------------+--------+----------------------------------------------------------------+------------------+ | 1592850 | user00001 | 192.168.0.203:38102 | nantoka | Sleep | 1 | | NULL | | 1592854 | user00001 | 192.168.0.203:38106 | hogefuga | Sleep | 1 | | NULL | | 1592856 | user00001 | 192.168.0.203:38108 | hogefuga | Sleep | 1 | | NULL | | 1596335 | user00001 | 192.168.0.203:38328 | hogefuga | Sleep | 27 | | NULL | | 1596337 | user00001 | 192.168.0.203:38331 | nantoka | Sleep | 15 | | NULL | (中略) | 1596701 | repluser | 192.168.0.72:40783 | NULL | Binlog Dump | 80289 | Has sent all binlog to slave; waiting for binlog to be updated | NULL | | 1596702 | repluser | 192.168.0.73:49388 | NULL | Binlog Dump | 80289 | Has sent all binlog to slave; waiting for binlog to be updated | NULL | | 1596706 | repluser | 192.168.0.55:47014 | NULL | Binlog Dump | 80289 | Has sent all binlog to slave; waiting for binlog to be updated | NULL | | 1596707 | repluser | 192.168.1.81:54804 | NULL | Binlog Dump | 80289 | Has sent all binlog to slave; waiting for binlog to be updated | NULL | +---------+-----------+---------------------+----------+-------------+--------+----------------------------------------------------------------+------------------+
多数のアプリケーションサーバからの接続があったりしてコネクション数が大量で、show processlistが多くの行を返す場合などにはこれでは見づらく、show slave hostコマンドできれいに見られるのがうれしい。細かいことだけど、あとで見やすいようにしておくのは結構重要だと思う。
ちなみに、report-hostは「set report-host = hoge」などとオンラインで変更することはできないので、レプリケーションのセットアップ時などセットアップの初期段階で設定しておくべし。
2012年4月18日追記
MySQL 5.5.3よりも新しいバージョンの場合、スレーブでreport-hostを設定しなくてもマスタでshow slave hostsを実行すると一覧が表示されるようになっているようだ(出典)。5.5.3以前の場合、report-hostを書かないとそのスレーブは表示されてこない。
mysql> show slave hosts; +-----------+------+------+-----------+ | Server_id | Host | Port | Master_id | +-----------+------+------+-----------+ | 111 | | 3306 | 101 | | 112 | | 3306 | 101 | +-----------+------+------+-----------+ 2 rows in set (0.00 sec)
ただしこの場合Hostの欄は上のように空になり、report-hostを設定するとその値がHost列に表示されるようになっている。
cygwinのbashでclearを使いたい
masudakさんのエントリ を読んで、そういえば作業用PCにCygwin入れてないなと思ってインストールしてみたら、clearコマンドがなかった。
学生時代からの癖で事あるごとに「clear;ls」と入力するのが好きな自分は、clearコマンドがないと生きていけない。
どうやらclearコマンドが欲しい場合は、ncursesを入れると使えるようになるようだ。Cygwinのsetup.exeで、「Utils → ncurses」をインストールするようにチェックを入れれば、めでたくclearできるようになる。
lsコマンドのソートオプションまとめ
lsコマンドにはソートオプションがいくつかあるけど、いつもどれがどれだか分からなくなるのでメモ。
時刻でソートするもの
-c
-ltと一緒に指定すると、ctime(最終ステータス変更時刻)を表示してそれでソート
-lと一緒に指定すると、ctimeを表示するがファイル名でソート
-u
-ltと一緒に指定すると、atime(最終アクセス時刻)を表示してそれでソート
-lと一緒に指定すると、atimeを表示するがファイル名でソート
-t
タイムスタンプでソート
名前でソートするもの
-X
拡張子のアルファベット順にソート
-v
バージョン順にソート
サイズでソートするもの
-S
ファイルサイズでソート(大きいものが上)
その他
-U
ソートせずにディレクトリに置かれている順に表示
-f
ソートしない
-r
指定されたオプションの逆順でソート
ソート以外の便利なオプション
-m
コンマ区切り
-Q
ダブルクォーテーションでくくられる
MySQL ユーザのホストをワイルドカードで指定してもlocalhostは含まれない
MySQLをインストールしたので、とりあえずワイルドカードでホスト名を指定してテスト用のユーザを作成。
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hoge | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> grant select on hoge.* to testuser@'%' identified by 'password'; Query OK, 0 rows affected (0.07 sec) mysql> select host,user from mysql.user; +-----------+----------+ | host | user | +-----------+----------+ | % | testuser | | 127.0.0.1 | root | | localhost | | | localhost | root | +-----------+----------+ 7 rows in set (0.00 sec) mysql> quit
この状態で、MySQLがインストールされているサーバ自体からそのままtestuserでログインしようとすると、アクセスが拒否される。
testserver$ mysql -utestuser -p Enter password: ERROR 1045 (28000): Access denied for user 'testuser'@'localhost' (using password: YES)
しかし、他のサーバからアクセスしてみると、接続は成功して、hogeデータベースへのアクセスもできる。
anotherserver$ mysql -utestuser -p -h testserver hoge Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.5.17 MySQL Community Server (GPL) Copyright (c) 2000, 2011, 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 | | hoge | | test | +--------------------+ 3 rows in set (0.00 sec)
「testuser@’%'」の「%」はワイルドカードなので、localhostも含めた全てのホストを指すものかと思ったら、どうやらそれは間違いのようだ。
調べてみたところ、以下のような仕組み。
4.8.2 MySQL への新規ユーザの追加
http://dev.mysql.com/doc/refman/5.1/ja/adding-users.html
monty というユーザ名と some_pass というパスワードのアカウントが 2つ存在します。どちらもフル権限を持つスーパーユーザのアカウントです。’monty’@'localhost’) というアカウントは、ローカル ホストから接続するときにだけ使用できます。一方の ‘monty’@'%’ というアカウントは、どのホストからでも接続できます。注意： monty というアカウントは両方とも、monty としてどこからでも接続できる必要があります。この localhost でアカウントを持っていない場合、monty でローカル ホストから接続したときに、mysql_install_db で作成している localhost のエントリで、匿名ユーザのアカウントとして優先になります。つまり、 monty が匿名ユーザとして扱われます。この理由は、’monty’@'%’ よりも、匿名ユーザの方が具体的な Host カラム値にあるため、匿名の方が、user テーブルのソート順で先にきます。. (user テーブルのソートに関しては、項4.7.5. 「アクセス制御の段階 1： 接続確認」 を参照してください。)
分かったような、わからないような。参照先を見てみるとより詳しい説明がある。
4.7.5. アクセス制御の段階 1： 接続確認
http://dev.mysql.com/doc/refman/5.1/ja/connection-access.html
長いので引用は避けるが、MySQLがuserテーブルに書かれた情報をどのようにソートして使用しているかが影響しているようだ。上のページの説明によると、以下の順でソートを行っている。
- 最も具体的なホスト名(つまり、%のように明示的でない指定は後回し)
- 最も具体的なユーザ名
つまり、はじめの方に出てきたユーザのテーブルは、実際には以下のように並んでいることになる。
+-----------+----------+ | host | user | +-----------+----------+ | localhost | root | | localhost | | | 127.0.0.1 | root | | % | testuser | +-----------+----------+
ログイン時のユーザ名とホスト名の組み合わせは、このようにソートしたテーブルを上から順に検索していき、最初に一致したもので認証が行われる。
ここで、「testuser」というユーザがlocalhostからアクセスしてくると、2行目の「localhostの匿名ユーザ(userが空白 = 匿名ユーザ)」に先に一致してしまうのである。
この裏付けに、パスワードなしでtestuserでログインできるようにしてみると、確かに実際には匿名ユーザとしてログインしてしまっていることがわかる。
$ mysql -utestuser mysql> select current_user(); +----------------+ | current_user() | +----------------+ | @localhost | +----------------+ 1 row in set (0.00 sec)
従って、「testuser@localhost」としてログインしたいなら、明示的に
mysql> grant select on hoge.* to testuser@'localhost' identified by 'password';
とも実行して、「localhostのtestuser」を作成する必要がある。
注 : セキュリティ上、ホストを「%」でワイルドカード指定するのはよろしくないので、やらないようにしましょう(汗)
MySQLでテーブルの行数を数える
テーブル内の行数を数えるのに一番簡単なのは、
select count(*) from 'テーブル名';
と実行することだが、これだと最悪そのテーブルを総なめすることになる。
こういう時はinformation schemaから情報を引く。information_schema.tablesテーブルのtable_rowsカラムが行数。
mysql> select table_schema, table_name, table_rows from information_schema.tables
-> where table_schema = 'hoge';
+--------------------+---------------------------------------+------------+
| table_schema | table_name | table_rows |
+--------------------+---------------------------------------+------------+
| hoge | wp_FollowMe_Links | 2 |
| hoge | wp_commentmeta | 180 |
| hoge | wp_comments | 60 |
| hoge | wp_ktaisession | 0 |
| hoge | wp_links | 7 |
| hoge | wp_options | 236 |
| hoge | wp_postmeta | 509 |
| hoge | wp_posts | 323 |
| hoge | wp_term_relationships | 39 |
| hoge | wp_term_taxonomy | 9 |
| hoge | wp_terms | 9 |
| hoge | wp_usermeta | 31 |
| hoge | wp_users | 2 |
+--------------------+---------------------------------------+------------+
showを使っても行数を引くことができるが、showの性質上表示させるカラムなどを細かく指定できず使いづらいこともあるので、information_schemaを引く方が好き。
show table status;
なおこれらのコマンドで引ける行数は、MyISAMなどのストレージエンジンでは正確な値だが、InnoDBの場合は概算値となる。なので、InnoDBの場合はselect count(*)するしかない。
Twitter