mroongaをインストール

どうも、俺＠残業中です。
今日はMySQLの全文検索エンジンmroonga(v2.03)をインストールする方法をメモメモ。
超簡単です。
公式サイトはこちら。

MySQL(v5.5.25)はインストール済みとします。（今度こっちのインストール方法もメモメモしとかな）

まずはgroongaインストール。（yumインストール）

# rpm -ivh http://packages.groonga.org/centos/groonga-release-1.1.0-0.noarch.rpm
# yum install groonga-devel

ちなみにgroongaをソースからインストールする場合は

# yum install mecab-devel

しておいて

# wget http://packages.groonga.org/source/groonga/groonga-2.0.3.tar.gz
# tar zxvf groonga-2.0.3.tar.gz
# cd groonga-2.0.3
# ./configure
# make -j$(grep '^processor' /proc/cpuinfo | wc -l)
# mak install

とします。
※未検証です。ここを参考にしてみてください。



次に辞書をインストール。(MeCabの辞書）ソースインストールした場合はmecab-develの中に入ってるかも？

# yum install mecab-ipadic

ここまででとりあえずgroongaはインストールされました。
/usr/lib以下にgroongaというディレクトリが出来てると思います。




いよいよmroongaインストール。

# wget 'https://github.com/downloads/mroonga/mroonga/mroonga-2.03.ta.gz'
# tar zxvf mroonga-2.03.ta.gz
# cd mroonga-2.03

configureしてmakeしてmake installします。

# ./configure --with-mysql-source=/path/to/mysql --with-mysql-config=/path/to/mysql/bin/mysql_config --with-default-parser=TokenMecab
# make
# make install

これでMySQLのプラグインディレクトリ以下にha_mroonga.soが生成されます。
例）/usr/local/mysql-5.5.25/lib/plugin/ha_groonga.so



mysqldを起動または再起動してmysqlにログインします。

# /path/to/mysql/bin/mysql
mysql> INSTALL PLUGIN mroonga SONAME 'ha_mroonga.so';
mysql> SHOW ENGINES;

+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| CSV                | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                         | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                      | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
| mroonga            | YES     | CJK-ready fulltext search, column store                    | NO           | NO   | NO         |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
7 rows in set (0.02 sec)

Yes！インストールでけた！


あとはCREATE TABLEする時に

 ENGINE=mroonga;

をつけてね！






以上でぇぇぇえぇす。

innotopを使う

どうも、オレ＠仕事中です。

オデの日記::mytopを使うに続きまして、innotopの利用です。

今回参考にさせてもらったサイトはThe Programmer’s Guide to the Parallel World::MySQL: innotopとサンプリングです。ありがとうございます。

まずinnotop(source forge)から本体をDLします。

# wget 'http://downloads.sourceforge.net/project/innotop/innotop/1.6.0/innotop-1.6.0.zip?r=http%3A%2F%2Fsourceforge.net%2Fprojects%2Finnotop%2F&ts=1297835567&use_mirror=jaist'

解凍し、インストールします。

# unzip innotop-1.6.0.zip
 # cd innotop-1.6.0
 # perl Makefile.PL
 # make install

ちなみに、僕の環境ではここでまたperlモジュールのエラーが出ました。
なのでまたもやcpan利用します。

# cpan install Time::HiRes
 # make install

これでOK。

初回実行時に色々設定を質問されます。

# innotop
You have not defined any database connections.
Choose a name for the connection.  It cannot contain whitespace, colons or semicolons.

Enter a name:  ここは何でもOK
----------------------------------------------------------------------
Typical DSN strings look like
   DBI:mysql:;host=hostname;port=port
The db and port are optional and can usually be omitted.
If you specify 'mysql_read_default_group=mysql' many options can be read
from your mysql options files (~/.my.cnf, /etc/my.cnf).

Enter a DSN string:  DBI:mysql:database=DatabaseName;host=hostname;port=portのような形で入力
何も指定しない場合はDBI:mysql:;; かな

失敗したらユーザのホームディレクトリに~/.innotop/というディレクトリがあるので
丸ごと削除で再度設定できる＆~/.innotop/innotop.iniを書き換える事で修正可能です。

# innotop -d 秒間

のようにして利用できます。
mytopよりinnotopの方が見やすいと思いました。

以上でぇぇぇえぇす。

mytopを使う

どうも、オレ＠仕事中です。

今日はMySQLの実行状況を確認できるmytopというツールの紹介します。
また後でinnotopについても書く（予定）です。
Unixコマンドのtopをmysql版にしたようなものです。

まず上記のサイトからtarボールをDLし解凍します。

# wget 'http://jeremy.zawodny.com/mysql/mytop/mytop-1.6.tar.gz'
# tar zxvf mytop-1.6.tar.gz
# cd mytop-1.6

次にインストールです。

# perl Makefile.pl

ここで以下のようなエラーでる場合

Warning: prerequisite DBD::mysql 1 not found.
Warning: prerequisite Term::ReadKey 2.1.not found.

cpanモジュールでインストールしましょう。

# cpan install DBD::mysql # 該当サーバでMySQLサービスを停止させておいてね！
 # cpan install Term::ReadKey

これでOK！
ではインストール。ドキュメント通りにやります。

# make
 # make test
 # make install

これでインストール完了です。

あとは実行するのみ。

# mytop -s1 -hlocalhost -uUserName -pPassword

-s は表示間隔（秒）です。

ちなみに、MySQLバージョン5以上の場合はmytopソース内の「SHOW STATUS」を「SHOW GLOBAL STATUS」に書き換える必要があります。

MySQL on localhost (5.0.41)                                                                                                                                                                                                         up 0+00:14:17 [14:33:46]
 Queries: 81.0   qps:    0 Slow:     0.0         Se/In/Up/De(%):    00/00/00/00 
             qps now:    0 Slow qps: 0.0  Threads:    1 (   1/   0) 00/00/00/00 
 Key Efficiency: 100.0%  Bps in/out:   0.2/ 14.4   Now in/out:   8.4/ 1.1k

      Id      User         Host/IP         DB      Time    Cmd Query or State                                                                                                                                                                 
      --      ----         -------         --      ----    --- ----------                                                                                                                                                                               
       5      root       localhost       test         0  Query show full processlist

実行中スレッドが黄色、接続のみのスレッドが白、システムに関するスレッドが緑色です。

内容は

Queries: 発行されたクエリの総数
 qps: 秒間の平均クエリ数
 Slow: スロークエリ総数
 Se/In/Up/De: select、Insert、Update、Delete分の比率
 qps now: 現在の秒間平均クエリ数
 Slow qps: 現在の秒間平均スロークエリ数
 Threads: スレッド数（アクティブスレッド数/総スレッドキャッシュ数）
 Key Efficiency: ky_bufferヒット数
 Bps in/out: 合計のデータ転送速度（bytes/sec）
 Now in/out: 現在のデータ転送量（bits/sec）

FLATzブログ::mytopの使い方を参考にさせてもらいました。

以上どぇぇぇえす。

mysql + senna インストール

どうも、僕です。

MySQL+sennaで全文検索対応させるためインストールした時にトラブったのでメモ。
あ、原因はわからないままなのですが、、、
環境はKVM上でのCentOS5.5です。（KVMはLDAP認証でそれが怪しいと思っているのですが、、）


まずmecabをインスコ。（今回はmecab-0.98.tar.gz/mecab-ipadic-2.7.9-20070801.tar.gz）
http://sourceforge.net/project/showfiles.php?group_id=177856からmecab（本体）とmecab-ipadic（辞書）を落としてきます。
mecabは

# ./configure --prefix=/usr --with-charset=utf8

でmecab-ipadicは

# ./configure --prefix=/usr --with-charset=utf8

でmake & make install します。

同様にsennaも（今回はsenna-1.1.5.tar.gz）
http://sourceforge.jp/projects/senna/files/からソースを落としてきて

# ./configure --prefix=/usr

でインスコします。

最後にtrittonを（今回はtritonn-1.0.12-mysql-5.0.87.tar.gz）
http://sourceforge.jp/projects/tritonn/releases/からソースGETし

# ./configure  --prefix=/usr/local/mysql --with-charset=sjis --with-extra-charsets=complex --with-mysql-user=mysql --without-bench --enable-assembler --with-mysqld-ldflags=-all-static --without-docs --with-senna --with-mecab CFLAGS="-O3 -mpentiumpro" CXX=gcc CXXFLAGS="-O3 -mpentiumpro -felide-constructors -fno-exceptions -fno-rtti"

でインストールしました。

ここから通常のmysqlインストールと同じ作業を行います。

# groupadd mysql
# useradd -d /usr/local/mysql -g mysql mysql
# chown -R mysql:mysql /usr/local/mysql
# /usr/local/mysql/bin/mysql_install_db --user=mysql

ここでコケました。
内容は

   1 101019 20:51:13  mysqld started
   2 101019 20:51:13 - mysqld got signal 11 ;
   3 This could be because you hit a bug. It is also possible that this binary
   4 or one of the libraries it was linked against is corrupt, improperly built,
   5 or misconfigured. This error can also be caused by malfunctioning hardware.
   6 We will try our best to scrape up some info that will hopefully help diagnose
   7 the problem, but since we have already crashed, something is definitely wrong
   8 and this may fail.
   9 
  10 key_buffer_size=0
  11 read_buffer_size=262144
  12 max_used_connections=0
  13 max_connections=100
  14 threads_connected=0
  15 It is possible that mysqld could use up to
  16 key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 76800 K
  17 bytes of memory
  18 Hope that's ok; if not, decrease some variables in the equation.
  19 
  20 thd=(nil)
  21 Attempting backtrace. You can use the following information to find out
  22 where mysqld died. If you see no messages after this, something went
  23 terribly wrong...
  24 Cannot determine thread, fp=0xbf908e38, backtrace may not be correct.
  25 Stack range sanity check OK, backtrace follows:
  26 0x80eab85
  27 0x85fdd5
  28 0x854f70
  29 0x856cc0
  30 0x85e67d0  31 0x85e69b6
  32 0x80e9bef
  33 0x80ef2f0
  34 0x85a2398
  35 0x8048131
  36 New value of fp=(nil) failed sanity check, terminating stack trace!
  37 Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved
  38 stack trace is much more helpful in diagnosing the problem, so please do
  39 resolve it
  40 The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
  41 information that should help you find out what is causing the crash.
  42 
  43 This crash occured while the server was calling initgroups(). This is
  44 often due to the use of a mysqld that is statically linked against glibc
  45 and configured to use LDAP in /etc/nsswitch.conf. You will need to either
  46 upgrade to a version of glibc that does not have this problem (2.3.4 or
  47 later when used with nscd), disable LDAP in your nsswitch.conf, or use a
  48 mysqld that is not statically linked.
  49 101019 20:51:13  mysqld ended

といったもの。
/etc/my.cnfを作ってメモリ少なくして実行してもダメ、出力されたログをトレースしても意味不明。「何かがクラッシュしたぜ」としか出てこない。
気になるのは最後の文章

  43 This crash occured while the server was calling initgroups(). This is
  44 often due to the use of a mysqld that is statically linked against glibc
  45 and configured to use LDAP in /etc/nsswitch.conf. You will need to either
  46 upgrade to a version of glibc that does not have this problem (2.3.4 or
  47 later when used with nscd), disable LDAP in your nsswitch.conf, or use a
  48 mysqld that is not statically linked.

なにやらLDAPを使わないようにしてみたら？的なアドバイスが出る。
ただしサーバ管理者は俺じゃないのであきらめる、、、。



とりあえず解決方法は、mysqlユーザになってやる！です。

# su mysql -
$ /usr/local/mysql/bin/mysql_install_db

これで難なくできちゃいました。
またその後mysqlデーモンをrootで立ち上げようとすると

# service mysql start
Starting MySQL.Manager of pid-file quit without updating file [失敗]

とか出ちゃう。

# /usr/local/mysql/bin/mysqld_safe &
Starting mysqld daemon with databases from /usr/local/mysql/var
STOPPING server from pid file /usr/local/mysql/var/xxxxx.mysql.pid
101020 10:45:20 mysqld ended

[1] +done   ./bin/mysqld_safe

となぜか起動しない。
起動スクリプトなど確認したが、そのスクリプトがうまく解読できない、、、。ソース読んでると意味不明の実装がされてたりするし、、

これも

# service mysql start --user=root
または
# /usr/local/mysql/bin/mysqld_safe --user=root &

とやると起動できる。



sennaのバグなのか、KVM上でのOSの問題なのか、LDAP認証の問題なのか引き続き調査しまぁぁぁぁぁす。

mysqlでログインできなくなった

どうも、俺＠仕事中です。
今日は開発環境のmysqlサーバへログインできなくなってしまった場合の対処法です。

rootログインも一般アカウントログインもできません。

# mysql -u root
Access denied for user: 'root@localhost' (Using password: NO)
# mysql -u mysql_user
Access denied for user: 'mysql_user@localhost' (Using password: NO)

もちろんパスワードの設定などもしていませんでした。

権限テーブルで何か異常が発生してるのだろう、、と確認したいのですが、mysqlデータベースにすら接続できません。

１）無理やりログインできるようにする
mysqlを立ち上げる際に

# mysqld_safe --skip-grant-tables & 

とやれば権限テーブルを無視してログインできます。

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

あじゃぱー！！
userテーブルが空になっちょる！誰やー！？

この状態で

mysql> grant all on *.* to root@localhost;

と打っても「--skip-grant-tablesオプション付けて起動してるからGRANTできひんでー」と怒られます。

２）他のサーバからuserテーブルデータを持ってきた
仕方がないので、他サーバで稼働中のuserテーブルデータをそのまま持ってきてdataディレクトリへ置きました。

# cp user.MYD user.MYI user.frm /usr/local/mysql/data/mysql/

これで通常起動すれば普通に接続できるようになりました。


うむむ、--skip-grant-tablesオプションでログインできるようになったはいいけど、その後GRANT構文を放り込みたい場合はどうしたら良いのでしょう、、？


以上でぇぇぇえぇす。

MySQLのデータをCSVで出力/入力

どうも、俺＠始業しましたです。

ググればすぐ出てきますが、MySQLのデータを簡単にcsv（またはtsv）出力する方法のめもです。
出力

mysql> SELECT * FROM table_name
INTO OUTFILE "/tmp/out.csv"
FIELDS TERMINATED BY ',';

これで、/tmp/out.csvというファイルに`table_name`テーブルのデータをカンマ区切りで出力できます。
タブ区切りで、各項目を`"（ダブルクォート）`で囲む場合は、

SELECT * FROM table_name
INTO OUTFILE "/tmp/out.csv"
FIELDS TERMINATED BY '\t'
ENCLOSED BY '"';

と、ENCLOSED BY '"' を追加します。
テーブルを結合した結果をcsv出力し、改行コードは`\r\n`にするには

SELECT * FROM table_name
INNER JOIN join_table ON table_name.f_key = join_table.id
INTO OUTFILE "/tmp/out.csv"
FIELDS TERMINATED BY '\t'
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

と、LINES TERMINATED BY '\r\n'を追加します。

次にデータを入力する方法です。
これはmysqldumpで取得したデータをリストアするより高速に動作します。（たぶん）

mysql> LOAD DATA INFILE "/tmp/in.csv"
FIELDS TERMINATED BY ',';

もちろんタブ区切りの場合は、

LOAD DATA INFILE "/tmp/in.csv"
FIELDS TERMINATED BY '\t';

になります。
ダブルクォートや改行コードを指定してインポートする場合も、INTO OUTFILE構文のと同様です。

ただ、クライアントマシンとMySQLマシンが別サーバで接続されている場合は、

mysql> LOAD DATA LOCAL INFILE "/tmp/in.csv"
FIELDS TERMINATED BY ',';

と、`LOCAL`キーワードを指定します。


出力する方法
入力する方法


以上でぇぇぇえぇす。

MySQL 実行中のスレッドをリアルタイム表示

どうも、俺＠仕事中です。

今日はMySQLで実行中のスレッドを表示するコマンドの紹介です。

mysql> show processlist;
+---------+-------+-------------------+---------+---------+------+-------+------------------+
| Id      | User  | Host              | db      | Command | Time | State | Info             |
+---------+-------+-------------------+---------+---------+------+-------+------------------+
| 4416652 | mysql | xx.xxx.x.xx:54924 | dbname  | Sleep   |  361 |       | NULL             | 
| 4470098 | mysql | xx.xxx.x.xx:35214 | dbname  | Sleep   |    6 |       | NULL             | 
| 4470608 | root  | localhost         | NULL    | Query   |    0 | NULL  | show processlist | 
+---------+-------+-------------------+---------+---------+------+-------+------------------+

です。

詳しくは、My SQL 5.1 リファレンスマニュアル

MySQLで「Too many connections」のエラー

どうも、俺＠週明け仕事中です。

週末、とあるシステムでMySQLの「Too many connections」が多発する障害が発生しました。
その時調べたメモです。

このエラーは、「コネクションの数が多すぎてもう接続できないよー」というものです。
MySQLの設定は

mysql> show global variables like '%max_connection%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   | 
+-----------------+-------+

という状態です。
デフォルト値が「100」なのだそうで、まず単純にコネクション最大数を増やします。

mysql> set global max_connections = 256;

コネクション数が増えるので、CPU負荷とメモリ使用量に注意が必要です。

他に注目すべきは、

mysql> show status like '%threads_%';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| Threads_cached         | 6      | 
| Threads_connected      | 4      | 
| Threads_created        | 140909 | 
| Threads_running        | 3      | 
+------------------------+--------+

うわー！エラいことなっとる！
ここの項目の見方は、
Threads_cached ... スレッドキャッシュでキャッシュされているスレッドの数。
Threads_connected ... 現在生成されているスレッドの数。
Threads_created ... 起動してから生成されたスレッドの数。この値が大きい場合はthread_cache_sizeの値を大きくした方が良いかも。
Threads_running ... 現在動作中のスレッド数。

と言うわけで、今回の件ではthread_cache_sizeの値を増やしておきます。

mysql> set global thread_cache_size = 64;

と、共にwait_timeoutの状況をチェック。

mysql> show global variables like 'wait_timeout%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+ 
| wait_timeout             | 28800 | 
+--------------------------+-------+

28800秒、つまり8時間に設定されているので、もう少し縮めて起きます。

mysql> set global wait_timeout = 3600;

1時間にしました。
wait_timeoutは接続があってスレッドが生成され待機状態（アイドル状態）になって○秒経過したら接続を切るというもの。
mysql_pconnect()やPDOでの持続的接続とかでも、wait_timeout秒数経過すると切断されます。（と、思う、、）
ちなみに、wait_timeouをmy.cnfに設定する場合は、

# vim /etc/my.cnf
set-variable = wait_timeout = 3600

のようにするらしい。


また経過監視します。以上どぇぇす。

MySQLで最初に見るべきチューニング設定項目

どうも、俺＠仕事中です。
わが社ではMySQL、PostgreSQLを良く使っている（というかほとんど）のですが、よくアクセス過多によるサーバチューニングをやっています。
今回はMySQLサーバで負荷対策を行う際の「まず最初にココの設定だけは要Check It Now」というのをメモします。

# vim /etc/my.cnf
または
# mysqladmin variables
または
mysql> show variables;

で、現在の設定の確認ができます。

-- key_buffer_size

mysql> show variables like '%key_buffer%';

MyISAMエンジンテーブルのインデックスを保存するメモリバッファのサイズ。
Key_read/Key_read_requestsでキーバッファミス率を算出し、0.01以下になるように。

-- max_allowed_packet

mysql> show variables like '%max_allowed%';

通信時の1パケット最大サイズ。画像データなど大容量のデータをMySQLへ保存する際には大きくする。

-- table_cache

mysql> show variables like '%table_cache%';

一度開いたMySQLのデータファイルポインタを保存しておくためのキャッシュ。二度目以降のテーブルへのアクセスを高速にする。つまりはサーバのディスクI/Oを減らせる。

mysql> show global status like '%Open_tables%'

の値が大きい場合はテーブルキャッシュの値を増やした方が良い場合がある。
※バージョン5.1.xからはtable_open_sizeに変わってるかも！？

-- sort_buffer_size

mysql> show variables like '%sort_buffer%';

ソートに利用されるメモリサイズ。Order ByとかGroup Byとかで使われる。もしsort_buffer_sizeが1Mでmax_connectionsが1000だった場合、メモリは最低でも1GB必要という事ですよ。

-- read_buffer_size

mysql> show variables like '%read_buffer%';

テーブル読み込み時に利用されるメモリサイズ。INDEXを使わずフルスキャンされる場合に利用。そもそもINDEXを見直すべきでは？！

-- read_rnd_buffer_size

mysql> show variables like '%read_rnd_buffer%';

テーブルをランダムに読み込む場合に使われるメモリサイズ。使用可能メモリより大きなテーブルでGroup By/Order Byをする際は大きめにしておく。ソート後のレコード読み取り速度を上げるかも。要はディスクI/Oの軽減。

-- myisam_sort_buffer_size

mysql> show variables like '%myisam_sort_buffer%';

REPAIR TABLE / CREATE INDEX / ALTER INDEX 時に利用されるメモリサイズ。かなり頻度は低い。

-- thread_cache_size

mysql> show variables like '%thread_cache%';

MySQLコネクションスレッドをプールする数。スレッドを破棄＆生成するコストを下げる。

-- tmp_table_size

mysql> show variables like '%tmp_table_size%';

サブクエリなどで一時的に実行結果をテンポラリテーブルに保存されたときのメモリサイズ。このサイズを超えた場合、MySQLはディスクへ一時テーブルへ保存しディスクI/Oが発生してしまう。このサイズを指定した場合はmax_heap_table_sizeも同じ値に設定する。

-- query_cache_size

mysql> show variables like '%query_cache%';

一度発行したクエリ実行結果をキャッシュしておき、二回目以降はクエリを発行することなくキャッシュから結果を返す。もちろん更新クエリなどが走った後はキャッシュはクリアされる。ちなみに

mysql> FLUSH QUERY CACHE;

でキャッシュを強制的にクリアできる。

-- skip-name-resolve
これは有名すぎますがDNS逆引きをしない。この設定をした場合、GRANT構文などでユーザを登録する際にもIP指定にする必要がある。

my.cnfの設定は基本的に上記のものを見直します。
もちろん細かい設定をする場合は、もっと詳細な設定を見直す必要があるのであくまで参考程度に。

あとは、スローログを取得させる。

slow_query_log=mysql-slow　// スローログを取得
logn_query_time=1　// 1秒以上かかったクエリを取得

OPTIMIZE TABLE [table_name]を定期的にかける。

mysql> OPTIMIZE TABLE table_name;

更新や削除などが頻繁に行われているテーブルに対してやると効果絶大。デフラグを解消させる。postgreSQLで言うところのvacuumみたいなもの。


あとは、サーバのメモリを増やすだとかサーバそのもののスペックを上げるだとか、アプリケーション側でのチューニングは必ず必要です。
特にスローログに出ているクエリは見直す余地がかなりあると思うので、そちらのチューニングも大切に！


以上でぇぇす。

PostgreSQLでコマンドラインから直接クエリ実行

おはようございます、俺＠朝礼後です。

今日はPostgreSQLでコマンドラインで直接クエリを実行する方法をメモります。
説明が難しいのですが、「コマンドラインから直接」というのは

$ psql foo_database
psql> SELECT * FROM bar;

というのではなく、
postgresqlサーバへログインせずにクエリ発行、結果取得という意味です。
（※内部的にはpostgresqlログインしてるのかもやけど、、、）


やり方は、

$ psql -c 'SELECT * FROM bar' foo_database

です。
ちなみに

$ psql -c 'SELECT * FROM bar' -o output_file foo_database

とやると、
output_fileへクエリ結果を出力します。

ついでにMySQLでは

$ mysql -e 'SELECT * FROM bar' foo_database

で直接クエリを実行できます。



以上どぇぇぇぇぇす。

MySQLのALTER TABLE構文のまとめ

こんばんわ、俺＠家でまったり中です。
今日は、プログラマがよく使うであろうMySQL(ver5.x)のALTER TABLE構文についてメモします。
ALTER TABLE構文はMySQLのテーブルの構造を変更する時に使います。

1)カラムを追加

mysql> ALTER TABLE [テーブル名] ADD COLUMN [カラム名] [型] [制約] [FIRST | AFTER カラム名]

最後の[FIRST|AFTER カラム名]はなくてもOK。
例：

msql> ALTER TABLE table_name ADD COLUMN name VARCHAR(64) NOT NULL DEFAULT ''

2)カラムを削除

mysql> ALTER TABLE [テーブル名] DROP COLUMN [カラム名]

3)テーブル名変更

mysql> ALTER TABLE [テーブル名] RENAME TO [新テーブル名]

4)カラムの型を変更

mysql> ALTER TABLE [テーブル名] MODIFY [カラム名] [型] [制約]
または
mysql> ALTER TABLE [テーブル名] CHANGE COLUMN [カラム名] [新カラム名] [型] [制約]

5)カラム名を変更

mysql> ALTER TABLE [テーブル名] CHANGE COLUMN [カラム名] [新カラム名]

6)インデックスを追加

mysql> ALTER TABLE [テーブル名] ADD INDEX [インデックス名] ([カラム名,...])
または
mysql> CREATE INDEX [インデックス名] ON [テーブル名] ([カラム名,...])

7)インデックスを削除

mysql> ALTER TABLE [テーブル名] DROP INDEX [インデックス名]
または
mysql> DROP INDEX [インデックス名] ON [テーブル名]

8)AUTO_INCREMENTの値を変更

mysql> ALTER TABLE [テーブル名] AUTO_INCREMENT = [値]

9)テーブルの使用しているエンジン(ENGINE)を変更

mysql> ALTER TABLE [テーブル名] ENGINE = [ENGINE名]

10)テーブルのデフォルト文字セットを変更

mysql> ALTER TABLE [テーブル名] DEFAULT CHARSET = [文字コード名]

11)デフォルト値を変更

mysql> ALTER TABLE [テーブル名] ALTER COLUMN [カラム名] SET DEFAULT [デフォルト値]

12)デフォルト値を削除

mysql> ALTER TABLE [テーブル名] ALTER COLUMN [カラム名] DROP DEFAULT

詳しくはMySQL::5.1マニュアル ALTER TABLE構文をば。

以上どぇぇぇぇーす。

mmeasureのインストール

どうも、俺＠仕事中、2度目の投稿です。
決して暇な訳ではありませんよ。

mmeasureのインストールについて覚書き。
インストールは至って簡単シンプルです。
まずはmmeasure projectからソースをダウンロード。2009年9月9日時点でのバージョンは1.0.7でした。

次に解凍

# tar zxvf mmeasure-1.0.7.tar.gz

出てきたディレクトリを/usr/local下（デフォルト）へ移動

# mv mmeasure/ /usr/local
# cd /usr/local/mmeasure

perlのモジュールをインストールします。
cpanについてはオデの日記＠cpanモジュールを見てね。

# cpan install Socket
# cpan install DBI // ←MySQLを起動させてないとダメっぽい
# cpan install Jcode
# cpan install DBD::mysql // ←ココでずっこける

RRDsモジュールはrrdtoolをインストールすればOKの模様。

# wget 'http://oss.oetiker.ch/rrdtool/pub/rrdtool-1.0.x/rrdtool-1.0.49.tar.gz'
# tar zxvf rrdtool-1.0.49.tar.gz
# rrdtool-1.0.49
# ./configure
# make
# make install // ←ついでにrrdtoolインスト
# cd /usr/local/src/rrdtool-1.0.x/perl-shared
# perl Makefile.PL
# make && make install

で、DBD::mysqlですが、何をどう頑張ってもインストールできませんでした。
同じくインストールできなかったけど、頑張ればインストできた人がいれば解決方法を教えて下さい。
とりえずyumで

# yum install perl-DBD-MySQL

で何とかインスト。

設定ファイルを修正

# cp mmeasure_template.conf mmeasure.conf
# vim mmeasure.conf

で、この設定ファイルを色々変更します。デフォルトの設定ファイルには間違いもあるので必ず修正します。

#------------------
#   MySQL
#------------------
MYSQL_HOST=[MySQLのIP]
MYSQL_USER=mmeasure # あとでmmeasureユーザを追加します。
MYSQL_PASSWORD=password # 接続パスワード
MYSQL_PORT=3306
MYSQL_SOCKET=/tmp/mysql.sock
MYSQL_SLOW_QUERY_LOG="/usr/local/mysql/var/mysql-slow" # スローログのPATH。ない場合はコメントアウトで
#------------------
#    Mail
#------------------
MAIL_HOST=foo.com # 自サーバホスト名
MAIL_FROM=info@foo.com # 実在するアカウントで！
MAIL_TO=bar@tohost.com
#MAIL_CC= # いらん！
#MAIL_BCC= # いらん！
MAIL_SUBJECT="alert" # メールの件名を適当に。
#------------------
#    Pathes
#------------------
PATH_RRDTOOL="/usr/local/rrdtool-1.0.49/bin/rrdtool" # パスが違う場合は修正
#------------------
#    Web setting
#------------------
WEBDIR="$PATH_MEASURE/web/images/graphs" # /imagesを追加！
WIDTH_SMALL="200"   # 追加！
HEIGHT_SMALL="100"  # 追加！
WIDHT_LARGE="400"   # 追加！
HEIGHT_LARGE="100"  # 追加！

次にMySQLサーバにmmeasureユーザの追加です。僕の環境ではローカル接続の別サーバにMySQLがあったので

# mysql -h 192.168.0.2 -u mysql
mysql> GRANT ALL ON *.* to mmeasure@192.168.0.1 indentified by 'password';
mysql> SET password FOR mmeasure@192.168.0.1 = old_password('password'); // ←MySQL4.1以前の互換
mysql> FLUSH PRIVILEGES;

でOK。一応接続テストもしておく。

# mysql -h 192.168.0.2 -u mmeasure -p
Enter password: パスワード入力

次に、MySQLのデータを取るために./mmeasure/daemon/data-mysql/read-data.plを修正します。ココはだいぶハマったのですが、クエリ回数、クエリ比率などを表示するために変更するべきです。

# vim ./mmeasure/daemon/data-mysql/read-data.pl
 50     my %status = select_2column( $db, "show global status" ); # globalを追加

あと、僕の場合ですが、mmeasure.errでアラートメール送信時のエラーが出まくっていたのでメール送信処理部分をコメントアウトしました。アラートはNAGIOSとかで監視してるので、、、。

# vim ./mmeasure/daemon/daemon-mmeasure.pl
 224 #                   alerter_sendmail( $conf{ 'MAIL_HOST' }, 
 225 #                       $conf{ 'MAIL_TO' }, $conf{ 'MAIL_FROM' }, 
 226 #                       $conf{ 'MAIL_SUBJECT' }, $alert_message, 
 227 #                       $conf{ 'MAIL_CC' }, $conf{ 'MAIL_BCC' } );

なんでエラーが出るのか謎です。時間があるときに調査します。分かる方いたら教えてください＞＜
エラー内容

# vim ./mmeasure/daemon/log/mmeasure.err
 52 RCPT command failure(Bad file descriptor)... at ../library/mailer.pl line 93.
 53 print() on closed filehandle SH at ../library/mailer.pl line 127 (#1)
 54     (W closed) The filehandle you're printing on got itself closed sometime
 55     before now.  Check your control flow.
 56
 57 readline() on closed filehandle SH at ../library/mailer.pl line 128 (#2)
 58     (W closed) The filehandle you're reading from got itself closed sometime
 59     before now.  Check your control flow.
 60
 61 Use of uninitialized value in substitution (s///) at ../library/mailer.pl line
 62     205 (#3)
 63     (W uninitialized) An undefined value was used as if it were already
 64     defined.  It was interpreted as a "" or a 0, but maybe it was a mistake.
 65     To suppress this warning assign a defined value to your variables.
 66
 67     To help you figure out what was undefined, perl tells you what operation
 68     you used the undefined value in.  Note, however, that perl optimizes your
 69     program and the operation displayed in the warning may not necessarily
 70     appear literally in your program.  For example, "that $foo" is
 71     usually optimized into "that " . $foo, and the warning will refer to
 72     the concatenation (.) operator, even though there is no . in your
 73     program.
 74
 75 Use of uninitialized value in pattern match (m//) at ../library/mailer.pl line
 76     130 (#3)
 77 print() on closed filehandle SH at ../library/mailer.pl line 132 (#1)
 78 DATA command failure(Bad file descriptor)... at ../library/mailer.pl line 134.
 79 print() on closed filehandle SH at ../library/mailer.pl line 143 (#1)
 80 readline() on closed filehandle SH at ../library/mailer.pl line 144 (#2)
 81 Use of uninitialized value in pattern match (m//) at ../library/mailer.pl line
 82     146 (#3)
 83 print() on closed filehandle SH at ../library/mailer.pl line 148 (#1)
 84 Body & header send failure(Bad file descriptor)... at ../library/mailer.pl line 150.
 85 print() on closed filehandle SH at ../library/mailer.pl line 156 (#1)

次はApacheにAliasを作ります。

# vim /usr/local/apache2/conf/httpd.conf
Alias /mmeasure "/usr/local/mmeasure/web"

# /usr/local/apache2/bin/apachectl restart

ではデーモンの起動！

# ./mmeasure/daemon/mmeasure.sh start

以上～！あとはチューニングアドバイスを信じるも信じないもアナタ次第！

MySQLで再起動せずに設定変更

こんちわ、俺＠仕事中です。
周知の事実すぎるかもしれませんが、MySQLの設定（通常はmy.cnfに書かれてあるもの）を再起動せずに変更する方法について。

mysql> SET GLOBAL <変数名>=<値>
または
mysql> SET @@global.<変数名>=<値>

で設定を変更できます。
例えば、query_cache_sizeを1MBに変更する場合

mysql> SET GLOBAL query_cache_size=1048576;

で変更できます。

変更後は必ずmy.cnfの値を変更する事を忘れずに！じゃないと、MySQLがいつか再起動した後元に戻っちゃいますよー。



以上どぇ～す。

MySQLのCASE構文

どうも、俺＠残業中です。
忘れないために備忘録。

MySQLはCASE構文が使えるようになっています。

mysql> SELECT CASE WHEN 1=1 THEN 'true' ELSE 'FALSE' end;
+--------------------------------------------+
| case when 1=1 then 'true' else 'false' end |
+--------------------------------------------+
| true                                       |
+--------------------------------------------+
1 row in set (0.00 sec)

となります。

構文は

CASE WHEN <条件式> THEN <真の場合> ELSE <偽の場合> END;

です。

応用して、例えば別テーブル（B）に自テーブル（A）を参照しているカラム（a_id）があり、BテーブルがAテーブルを参照しているデータを`refered`として取得する場合は

mysql> SELECT
  CASE WHEN a_id>0 THEN 'refered' ELSE 'no refered' END
FROM A INNER JOIN B ON A.id=B.a_id;

で取得できます。

さらに拡張して、AテーブルのデータがBテーブルに参照されていた場合に、Aテーブルの`refered`カラムを`refered`、そうでない場合を`no refered`と更新したい場合は

mysql> UPDATE A INNER JOIN B ON A.id=B.a_id
  SET refered= CASE WHEN a_id > 0 THEN 'refered' ELSE 'no refered' END;

のように、MySQLのUPDATE文＋JOIN句でできます。

以上どえす。

MySQL5.1でfederatedエンジンを有効にする

こんにちわ、俺＠仕事中です。
MySQL5.1.3のFederatedエンジンでハマりました（汗

MySQL5.1でfederatedエンジンを有効にするには、configureオプションで

# ./configure --with-plugins=max
または
# ./configure --with-plugins=federated
または
# ./configure --with-plugin-federated

としなければなりません。
詳しくは

# ./configure --help

で。

上記でインストールしたのですが、

# mysql
mysql> show engines;
+------------+---------+--------------+------+------------+
| Engine     | Support | Transactions | XA   | Savepoints |
+------------+---------+--------------+------+------------+
| MRG_MYISAM | YES     | NO           | NO   | NO         | 
| CSV        | YES     | NO           | NO   | NO         | 
| FEDERATED  | NO      | NULL         | NULL | NULL       | 
| MyISAM     | DEFAULT | NO           | NO   | NO         | 
| MEMORY     | YES     | NO           | NO   | NO         | 
+------------+---------+--------------+------+------------+
5 rows in set (0.00 sec)

あれ？

mysql> show plugins;
+------------+----------+----------------+---------+---------+
| Name       | Status   | Type           | Library | License |
+------------+----------+----------------+---------+---------+
| binlog     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     | 
| partition  | ACTIVE   | STORAGE ENGINE | NULL    | GPL     | 
| CSV        | ACTIVE   | STORAGE ENGINE | NULL    | GPL     | 
| FEDERATED  | DISABLED | STORAGE ENGINE | NULL    | GPL     | 
| MEMORY     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     | 
| MyISAM     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     | 
| MRG_MYISAM | ACTIVE   | STORAGE ENGINE | NULL    | GPL     | 
+------------+----------+----------------+---------+---------+
7 rows in set (0.00 sec)

と、うんこな結果に。

configureオプションを色々と変えて試してみてもダメ。さらにMySQLはmakeに結構な時間がかかるので、いちいち試してもいられない。

という事で「ウンコー！」と叫びながら、mysqldを起動する際に

# service mysqld start --federated

とやると、federatedエンジンが有効になりました。

configureオプションは

# ./configure --with-plugins=max
または
# ./configure --with-plugins=federated
または
# ./configure --with-plugin-federated

でOKでし。

以上でええす。

MySQLでレプリケーション

Webアプリケーションを作成していて、MySQLサーバの負荷が高くなってきた場合、
・サーバスペックUP
・アプリケーションプログラム修正
・クエリ見直し
など色々対処方法はあるかと思います。
ま、一番言いのはサーバスペックUPだと思うのですが、どうしようもないくらい負荷が高くなった場合はMySQLサーバを複数台用意して、負荷を分散させましょう！
という事で、今日はMySQLの機能としてあるレプリケーション（Master-Slave）のやり方を簡単にメモ。

MySQLのレプリケーションはMaster側でバイナリログを取得していないといけないので

[Master]
# vim /etc/my.cnf
[mysqld]
server-id = 1　←サーバIDはSlave側と重複しないように！
log-bin=mysql-bin　←バイナリログ取得
expire_logs_days=15　←バイナリログ保存期間（任意）
binlog-do-db=[DB名] ←特定のデータベースのみバイナリログ取得（任意）

とします。
次にMasterに対しSlave側からアクセスできる新規ユーザを作成します。

[Master]
mysql> GRANT REPLICATION SLAVE ON *.* TO slave_user@192.168.0.2(Slave_IP);

では、Master側のデータをSlaveへ移動させます。

[Master]
mysql> FLUSH TABLES WITH READ LOCK;

でDBが更新されないようロックします。
Master側のバイナリログ状態を確認

[Master]
mysql> SHOW MASTER STATUS;
+-----------------+----------+--------------+------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------+----------+--------------+------------------+
| mysql-bin.000001|      112 |              |                  |
+-----------------+----------+--------------+------------------+
1 row in set (0.00 sec)

この「File」と「Position」の値はメモっておきます。コピー中にデータの更新があった場合にこのPositionからの同期をさせるためです。
MySQLのデータコピーをします。

[Master]
# tar cvf master.tar /usr/local/mysql/data/[DB名dir]

MySQLのロックを解除します。

[Master]
mysql> UNLOCK TABLES;

次にSlave側の設定です。
まず先ほど圧縮したMySQLのデータを展開します。

[Slave]
# cd /usr/local/mysql/data
# tar xvf master.tar

次にSlaveのmy.cnfの設定です。

[Slave]
# vim /etc/my.cnf
[mysqld]
server-id = 2 ←Master側と重複しないように！
read-only ←Slave側は更新しないように設定。SUPER権限を持つユーザのみが更新クエリを実行できる。
replicate-do-db = [DB名] ←指定したDBだけレプリケートする場合（任意）

Masterへ接続する設定を書いてもOK（？）です。

[Slave]
# vim /etc/my.cnf
master-host = 192.168.0.1 ←MasterIP
master-user = slave_user ←REPLICATION SLAVE権限を持つユーザ

と、my.cnfに書いても良いか、直接DBをイジった方が早い。

[Slave]
mysql> CHANGE MASTER TO
        MASTER_HOST = '192.168.0.1',
        MASTER_USER = 'slave_user',
        MASTER_LOG_FILE = 'mysql-bin.000001', ←バイナリログ名
         MASTER_LOG_POST = 112; ←先ほどメモったPositionの値

でOKです。
ではレプリケーション開始します。

[Slave]
mysql> SLAVE START;

ちなみに終了のときは

[Slave]
mysql> SLAVE STOP;

です。
めっちゃ簡単ですけど、もしSlaveが落ちて自動再起動とかなった場合にSLAVE STARTってっ誰がやるんだろ。。

MySQLを同一サーバ上に複数起動

どうも、俺です。
今日はMySQLを一つのサーバ内に複数起動する方法を超簡単にメモします。
OSはLinuxです。
そして、すでにデフォルトポート3306でのMySQLは起動しているものとします。

# netstat -tpln | grep mysql
tcp   0 0.0.0.0:3306   0.0.0.0:*   LISTEN   30029/mysqld

まず、新しいmy.cnfを作成します。

# vim /etc/my.3307.cnf
[client]
port     = 3307
socket   = /tmp/mysql.3307.sock
[mysqld]
pid-file = /usr/local/mysql/var.3307/mysqld.3307.pid
datadir  = /usr/local/mysql/var.3307
port     = 3307
socket   = /tmp/mysql.3307.sock

もう一つのMySQLはポート3307で起動するものとします。
次にmysql_install_dbを打ちます。

# mysql_install_db --defaults-file=/etc/my.3307.cnf --user=mysql

で/etc/my.3307.cnfを指定します。

では、MySQL起動します。

# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.3307.cnf & 

で起動できます。
このポート3307のMySQLへログインするには

# mysql --socket=/tmp/mysql.3307.sock

とソケットを指定すればOKです。

以上！！

MySQLで Host '...' is blocked エラー

どうも俺です。
先日とあるサーバのログにMySQLが吐き出したとおぼしき

Host '...' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

というエラーが出ていました。

これはMySQLが接続エラーを数回繰り返し出すと、そのホストからの接続を遮断するという機能によるものらしいです。
参照：http://dev.mysql.com/doc/refman/4.1/ja/blocked-host.html

この場合はログに書いてある通り

# mysqladmin flush-hosts

で解決できます。
デフォルトでは10回の接続エラーでなるようですが、変更する場合は

# mysqld_safe -O max_connect_errors=10000 &

を実行して変更できます。

パスワードなどの認証エラーも考えられますが、分かりにくいのがネットワーク関連のエラーで接続できない場合などです。
その場合は、値を増やしておく方が良いかもしれません。

mysqldump でスキーマ（テーブル構成）のみ取得

どうも僕です。
忘れやすいのでメモ。

mysqldumpで中のデータは要らないけどテーブル構成のみ取得したい場合。

# mysqldump DATABASE_NAME -d > schema.sql
または
# mysqldump DATABASE_NAME --no-data > schema.sql

です。

以上、終わり( ´艸｀)