mysqlでソートしたデータをヘッダーつきcsvに保存する方法

mysqlでorder byでソートしたデータをヘッダーつきのcsvファイルとして保存する方法です。

以下のようなsqlを実行する感じです。

select 'head1','head2','head3'
union
select * from (
select col1,col2,col3 from tbl
order by col1
) as tmp
INTO OUTFILE '/tmp/sort_data.csv'
FIELDS TERMINATED BY ',';

これで

head1,head2,head3
1,bbb,ccc
2,aaa,bbb
3,ccc,aaa

みたいなcsvファイルが出力されます。

ポイントは、
・ヘッダーは、unionの前に記載
・ソートするsql文を、select * from (    ) as tmp で囲う
です。

以下を参考にしました。
https://stackoverflow.com/questions/3175312/select-union-and-order-by-in-mysql-how-to

mysqlで監査ログを設定する

MySQLでもデータベースの操作ログを取ったほうがよいのかなぁと思ってしまう今日この頃です。

というわけで、どのようにすれば監査ログを残せるのかを調べました。

Enterprise Editionには、監査の機能があるようですが、今回はEnterprise Editionのことは対象外です。

他にMcAfeeがMySQL用の監査ツールを提供しているようなので、こちらの使い方になります。


以下はすべてrootユーザで行うものとします。

まずはインストールです。

https://bintray.com/mcafee/mysql-audit-plugin/release
から自分の環境にあったものをダウンロードします。
今回の環境は、64bitCentOS6上のMySQL5.6とします。

ファイルを取得して、zip解凍してプラグイン用のディレクトリにコピーをします。
wget http://dl.bintray.com/mcafee/mysql-audit-plugin/1.0.5/audit-plugin-mysql-5.6-1.0.5-479-linux-x86_64.zip
unzip audit-plugin-mysql-5.6-1.0.5-479-linux-x86_64.zip

cp audit-plugin-mysql-5.6/lib/libaudit_plugin.so /usr/lib64/mysql/plugin/

そして設定です。

/etc/my.cnf
の

[mysqld]セクションに以下を追加します。

------------------------------------------------

# McAfee Audit-Plugin

plugin-load=AUDIT=libaudit_plugin.so

audit_json_file = On

------------------------------------------------

他にも設定できる項目はあるのですが、とりあえず最低限動くようにするには上記でよさそうです。

他の項目に関しては、以下を参照してください。

https://github.com/mcafee/mysql-audit/wiki/Configuration

設定が完了したらmysqlを再起動です。

service mysqld restart

プラグインが設定されているかの確認は、以下を実行してaudit系の設定が表示されればOKです。

mysql> SHOW VARIABLES LIKE 'audit_%';

これで監査ログが

/var/lib/mysql/mysql-audit.json

に出力されることになります。

出力されるログの内容に関しては以下が参考になります。

http://blog.1000k.net/2014/05/13/mcafee-mysql-audit-plugin-%E3%81%A7-mysql-%E3%81%AE%E7%9B%A3%E6%9F%BB%E3%83%AD%E3%82%B0%E3%82%92%E6%8E%A1%E5%8F%96%E3%81%99%E3%82%8B/

監査ログは、いっぱい出力されるのでファイルサイズが大きくなっていきます。

なので以下の設定をして必要なものだけに絞るのがよいかと思います。

audit_record_cmds：ロギング操作を限定する

audit_record_objs：ロギングしたいオブジェクトを限定する

audit_whitelist_users：ログキングしないユーザを限定する

それでも大きくなり続けるのは、なんとなくいやだったので、ログローテートを設定します。

以下のようにしてみました。
とりあえず毎日ローテートして30日分残すことにしてみました。

vi /etc/logrotate.d/mysql-audit-log-rotate

-----------------------------------
/var/lib/mysql/mysql-audit.json {
  daily
  rotate 30
  missingok
  compress
  delaycompress
  sharedscripts
  postrotate
    if test -x /usr/bin/mysqladmin && \
      /usr/bin/mysqladmin ping &>/dev/null
    then
      /usr/bin/mysql --defaults-extra-file=/root/.my.cnf -e "set global audit_json_file_flush=ON"
    fi
  endscript
}
-----------------------------------

上記で利用する.my.cnfも作成します。
vi /root/.my.cnf

-----------------------------------

[mysql]

password = xxxxxxxx

user = root

-----------------------------------

chmod 600 /root/.my.cnf

ログローテートの確認です。2回目のlsでログが増えていることを確認します。

ls -l /var/lib/mysql/mysql-audit.json*

logrotate -f /etc/logrotate.d/mysql-audit-log-rotate

ls -l /var/lib/mysql/mysql-audit.json*

監査ログはこんな感じで残せるようにできたけど、監査ログを確認して異常を検知する方法がよくわからないので、監査ログを活用する方法をちゃんと理解したいところです。

remiリポジトリからyumでインストールしたmysql5.5を5.6にyum updateする

yumは依存関係をいい感じで解決してくれるので便利ですね。

ただmysqlとかはバージョンが古めのものがインストールされちゃう傾向があります。

なのでちょっと前まではmysqlの新しいバージョンを利用するためにremiリポジトリを使ったりしていました。
でもこちらも5.6には対応していないようです。

と思っていたらmysql公式のリポジトリができていたようです。
こちらを追加するとremiでインストールした5.5のmysqlを5.6にアップデートできます。

手順は以下のような感じです。

・rootになる
rootで作業します。
su -

・バックアップの作成
設定ファイルとデータベースを念のためバックアップします。
cp /etc/my.cnf .
mysqldump -u root --all-databases > mysql_dump

・mysql公式のリポジトリを追加
CentOS6では以下のような感じで追加します。
yum install -y http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm

CentOS5の場合は
http://dev.mysql.com/downloads/repo/
から以下のrpmをダウンロードします。
mysql-community-release-el5-5.noarch.rpm
インストールの前にmysqlレポジトリ用のgpgキーというものが必要になります。
http://dev.mysql.com/doc/refman/5.5/en//checking-gpg-signature.html
に記載されている
-----BEGIN PGP PUBLIC KEY BLOCK-----
から
-----END PGP PUBLIC KEY BLOCK-----
までの内容をmysql-gpg.txtみたいなファイル名のテキストファイルに保存して
rpm --import mysql-gpg.txt
とすることでキーの登録ができます。
その上でrpmでインストールします。
yum install -y mysql-community-release-el5-5.noarch.rpm

これ以降は、CentOS5でもCentOS6でも変わりません。

・mysql停止
mysqlをとめます。
service mysqld stop

・mysqlのアップデート
mysqlをアップデートします。
yum -y update mysql-server mysql-devel

・mysql起動
mysqlを起動します。起動に失敗する場合は/var/log/mysql.logなどのlogファイルを確認します。
mysql5.6になって利用できなくなった設定を/etc/my.cnfに書いていると起動に失敗したりします。
service mysqld start

・既存DBのアップデート
既存DBに更新処理をかけます。
mysql_upgrade


これで、もともとは入っていた
mysql-libs-5.5.31-1.el6.remi.x86_64
mysql-server-5.5.31-1.el6.remi.x86_64
mysql-devel-5.5.31-1.el6.remi.x86_64
mysql-5.5.31-1.el6.remi.x86_64
みたいなものが以下のように更新されました。
mysql-community-common-5.6.15-1.el6.x86_64
mysql-community-server-5.6.15-1.el6.x86_64
mysql-community-release-el6-5.noarch
mysql-community-client-5.6.15-1.el6.x86_64
mysql-community-libs-5.6.15-1.el6.x86_64
mysql-community-devel-5.6.15-1.el6.x86_64


参考にさせていただいたサイトは以下です。
http://d.hatena.ne.jp/akishin999/20131029/1383050569

今更CentOS5にMySQL5.1を入れる

古い環境のCentOS5のmysqlをアップデートするための検証環境を作るためにmysql5.1を入れようと思ってみました。

昔はremiレポジトリを使ってインストールしていたのですが、今remiを使うと5.5が入ってしまいます。
そして通常のレポジトリの方は5.0だったりします。

で調べてみたところ以下の手順でMySQL5.1をインストールすることができました。

wget http://dl.iuscommunity.org/pub/ius/stable/Redhat/5/i386/ius-release-1.0-10.ius.el5.noarch.rpm

wget http://dl.iuscommunity.org/pub/ius/stable/Redhat/5/i386/epel-release-5-4.noarch.rpm

rpm -Uvh epel-release-5-4.noarch.rpm

rpm -Uvh ius-release-1.0-10.ius.el5.noarch.rpm

yum -y install mysql51-server

yum -y install mysql51-devel

無事に5.1がインストールできてよかったです。

でも、すぐ5.5にアップデートしてしまったのですけどね。

railsで利用しているmysqlテーブルでパーティショニングを試してみた

あるrailsアプリで利用しているmysqlテーブルでパーティショニングを試してみました。

パーティショニングとは、テーブルのデータ格納領域を分割することで大量データを保持するテーブルのパフォーマンスを改善する手段の一つです。

以下が参考になります。
http://www.s-quad.com/wordpress/?p=62

パーティショニングをするには、分割に利用するカラムを決定する必要があります。
利用状況に応じた適切なカラムを選択しないと帰ってパフォーマンスが落ちることがあります。

今回、試したmysqlのバージョンは5.1で
以下のような毎月一括でデータを登録するようなテーブルです。

テーブル名：hoges
カラム：
id：int(PK)AUTO INCREMENT
nengetu：varchar
その他カラム多数

このテーブルには、毎月一度一括で大量のデータを登録し、検索する際にもnengetuが多くのケースで利用されています。
なのでnengetuで分割することを目指します。

調べてみたところ以下のことがわかりました。
・分割に利用するカラムは、プライマリキーの一部である必要がある
今回のhogesはrailsで普通に利用しているテーブルなのでプライマリーキーはidだけで構成されています。
なのでnengetuもプライマリーキーに含めて、idとnengetuの二つのカラムで構成されるプライマリキーにします。
ユニークインデックスの一部になっていれば、よいかとも思ったのですが、プライマリーキーの一部でないとダメでした。

・文字列カラムで利用できる分割はKEYパーティショニングのみ
パーティショニングの方法にはRANGE,LIST,HASH,KEYとあるのですが、文字列カラムで分割できるのはKEYパーティショニングだけのようです。
mysql 5.5だとRANGEでも文字列カラムを利用できるっぽいです。
なので今回はKEYパーティショニングを利用します。
分割数は、毎月分割して10年分も格納できれば文句もなかろうという安易な考えから128とします。


以上のことから以下のようなmigrationを用意しました。

class AddPartitionToHoge < ActiveRecord::Migration
  def self.up
    execute("ALTER TABLE hoges DROP PRIMARY KEY, ADD PRIMARY KEY (id, nengetu);")
    execute("alter table hoges PARTITION BY LINEAR KEY (nengetu) PARTITIONS 128;")
  end

  def self.down
    execute("ALTER TABLE hoges REMOVE PARTITIONING;")
    execute("ALTER TABLE hoges DROP PRIMARY KEY, ADD PRIMARY KEY (id);")
  end
end

以下を参考にさせていただきました。
http://d.hatena.ne.jp/d2mr/20080522/1211447409

今回利用しているLINEAR KEYに関しては、以下が参考になります。
http://nippondanji.blogspot.jp/2009/05/linear-hash.html


これでアプリには変更なしで動きました。

ただテストの方に問題が出てしまいました。
test用dbのhogesの主キーがうまく設定されずにidがAUTO INCREMENTされなくなり
このため多くのテストが失敗するようになってしまいました。

確かに
db/schema.rb
を見るとidの設定がおかしな感じになっていました。
どうも複合プライマリキーが設定されているテーブルだとこんなことになるようです。

これを改善するには、

config/environment.rb
に
config.active_record.schema_format = :sql
と記載してテスト用db作成に
db/schema.rb
を利用しないようにすればOKな感じです。

参考までに35万行程度のテーブルでのパフォーマンスの変化です。
時間はrailsのログから判別したのと、それぞれ一回しか試していないので本当に参考までです。

nengetu単月での検索(7万件ぐらい)
　前：Completed in 277404ms (View: 34, DB: 142744)
　後：Completed in 154913ms (View: 29, DB: 27970)
nengetu複数月での検索（20万件ぐらい）
　前：Completed in 353752ms (View: 67, DB: 21555)
　後：Completed in 411954ms (View: 139, DB: 66896)
単月のデータ一括登録（3万件ぐらい）
　前：Completed in 2016712ms (DB: 205944)
　後：Completed in 2131911ms (DB: 161670)

単月の検索はかなり早くなった感じです。
他は、それほどでもないのですがもっとデータがあれば違う結果になりそうな気がしています。

MySQLでUnknown table engine 'InnoDB'ってエラーが出たよ

MySQLでInnoDBのテーブルにクエリーを発行したら

Unknown table engine 'InnoDB'
ってエラーが出ました。

ログにメモリ割り当て失敗のメッセージが出ていたので
my.cnf
の
innodb_buffer_pool_size
のサイズを小さくすることで解決しました。

今まで別に問題なかったのですが、ある日突然エラーになりました。

私のところでは、毎日1回mysqlを止めてコールドバックアップを取っているのですが、ある日再起動の際にメモリが割り当てられなくなったようです。

どうやらmysqlとはまったく関係ない実験的に回していた別のプログラムでメモリリークしてしまったようで、そのプロセスが大量にメモリを確保してしまったせいでMySQLがメモリが確保できなかったようなのでした。

プロセスをkillした後は、無事にinnodb_buffer_pool_sizeを元のサイズに戻すことができました。

Mysql Clusterを使っていてalter tableしたらerrno: 136が出た

Mysql Clusterを利用しているテーブルに変更を加えようとしたら、

Can't create table 'xxx.#sql-47_29' (errno: 136)

とエラーが出て変更ができませんでした。

エラーが出た直後に

show warnings

とすると

Got error 904 'Out of fragment records (increase MaxNoOfOrderedIndexes)' from NDB
Can't create table 'xxx.#sql-47_29' (errno: 136)

と言われましたので、MaxNoOfOrderedIndexesを増やすことにしました。

MaxNoOfOrderedIndexesを記述する場所は、config.iniです。

私の環境では
/var/lib/mysql-cluster/config.ini
にあります。

[NDBD default]エントリに
MaxNoOfOrderedIndexes=1024
を追加しました。

その後、ndb_mgmdを再起動するわけですが、以下のように--initialをつけないと設定が反映されませんでした。

service ndb_mgmd stop
ndb_mgmd -f /var/lib/mysql-cluster/config.ini --initial

念のためndbdも以下のような感じで再起動しておきました。

service ndbd stop
service ndbd start

これでエラーがちゃんと消えましたのです。

rake db:migrateは成功しているのにindexが作られない

DBがmysqlで以下のような感じのmigrationを作りました。

create_table :hogehoge_mogemoges do |t|
  t.column :ahaha_id, :integer
  t.column :ihihi_id, :integer
  t.column :uhuhu_id, :integer
  t.column :ehehe_id, :integer
  t.column :ohoho_id, :integer
  t.column :valval,   :string
end
add_index :hogehoge_mogemoges,[:ahaha_id,:uhuhu_id,:ohoho_id],:unique => true

rake db:migrate
は普通に成功したのですが、indexが作成されていませんでした。

indexが作成されない理由は、作成されるindex名に原因があります。
この場合は、
index_hogehoge_mogemmoges_on_ahaha_id_and_ihihi_id_and_uhuhu_id_and_ehehe_id_and_ohoho_id
という名前のインデックスを作成をすることになります。

mysqlに直接、このインデックスを作成しようとすると

ERROR 1059 (42000): Identifier name 'index_hogehoge_mogemmoges_on_ahaha_id_and_ihihi_id_and_uhuhu_id_and_ehehe_id_and_ohoho_id' is too long
とエラーが出てて作れません。

たしかにtoo longです。
mysqlでは、インデックスに使えるのは最大64文字のようです。

でこれを解決するには、以下のようにadd_indexで名前を渡せばよいだけです。
add_index :hogehoge_mogemoges,[:ahaha_id,:uhuhu_id,:ohoho_id],{:unique => true, :name =>
"ind_hogemoge_aiueo"}

可変長プレフィックで重複が発生しないようにチェックするためのSQL

題名がよくわからない感じになってしまいましたが、内容としては以下のような感じです。

あるコード体系が
XXXXYYYY
のようになっていて、XXXXが親コードをあらわしてYYYYがその枝番を表しているようなコードがあり、さらにXXXX部分が固定長でないという既存コードをそのまま使いたいというケースです。
例えば、
親コードが
101
2001
とかになっていて
枝番も付与すると
10100001
20010001
みたい担っているコードです。
このような親コード部分をプレフィックスと今回、呼ばせてもらったのですが、このプレフィックス部分は、上記の例の場合
200(この枝番に2001が含まれる可能性があるため)---ケース１
も
1011(101の枝番とかぶる可能性があるため)---ケース２
も登録したくありません。

これをチェックする方法です。
MySQLのテーブルにプレフィックスが登録していあるとします。
テーブル名：pre

code

101

2001

まずは、ケース１をチェックします。
こちらは簡単。
select * from pre where code like '200%'
これで1件でも引っかかれば登録できないものとします。

ケース２の場合は以下のような感じです。
select * from pre where '1011' like concat(code,'%')
これで1件でも引っかかれば登録できないものとします。
こちらはデータ件数が多くなると遅くなるような気がするので注意が必要かもです。

上記２つをorで結合して以下のような感じで一気にやるのがよいかもしれないです。
select * from pre where code like '1011%' or '1011' like concat(code,'%')

mysqlで4月始まりの年度情報などを得るためのsql

mysqlで4月始まりの年度情報を得るsqlです。
2010年3月でも2009が欲しいという場合のときに使います。

select case when month(now()) > 4 then year(now()) else year(now())-1 end;

now()のところを適宜書き換えてください。

また、指定日が第何四半期にあたるかを得るsqlです。
4月～6月を第１四半期、1月～3月を第４四半期としたい場合です。

select quarter(now() -interval 1 quarter)

now()のところを適宜書き換えてください。

InfiniDBを使ってみる

InfiniDBは列指向型DBと言われるもので、データウェアハウスなどの大量データの扱いにすぐれているようです。

特長は、mysqlのストレージエンジンとして実装されていて各種sqlでデータ操作ができるということです。
なのでアプリケーションは作りやすいそうな感じです。

詳しくは、
http://www.infinidb.org/
をご覧ください。

というわけで試してみました。

CentOS5.4(32bit版)へインストールしてみました。
手順は、以下のような感じです。

ルートで行います。
su

boostというものが別途必要な感じです。
yum install boost

ダウンロードしてインストールします。
wget http://infinidb.org/downloads/doc_download/203-105-final-32-bit-rpms
tar xvzf 203-105-final-32-bit-rpms
rpm -ivh calpont*.rpm

すでにmysqlが動いている環境にインストールしたので利用ポートの変更とutf-8の設定を行います。
vi /usr/local/Calpont/mysql/my.cnf
3306としている部分を3366なりに変更します。
また、[mysqld]と[mysql]のセクションに
default-character-set=utf8
を追加します。

なんかスクリプトを流します。
/usr/local/Calpont/bin/install-infinidb.sh

InfiniDB用のmysqlクライアントを用意します。
vi /usr/local/bin/idbmysql
------------------------------
#!/bin/bash
/usr/local/Calpont/mysql/bin/mysql --defaults-file=/usr/local/Calpont/
mysql/my.cnf $*
------------------------------
chmod 755 /usr/local/bin/idbmysql

これで完了です。
起動終了は、
/etc/init.d/infinidb start
/etc/init.d/infinidb stop
になります。

データベースの作成は通常のmysqlと変わりません。
idbmysql -u root
create database ttt;
use ttt;

テーブルもストレージエンジンにInfiniDBと指定することで作成できます。
ただしindexをつけることができませんし、PKを含めNOT NULLなどの制約も利用できません。
indexは実質不要でよいようなのですが、制約がかけられないのはちょっと面倒かもしれません。

とりあえず性能を比較してみました。
同じマシンで通常のmysql(InnoDB)とInfiniDBの両方を動かして
以下のようなテーブルを用意します。
TABLE名(tttt)
id:int　InnoDBではPKにします。
code:varchar(16)　InnoDBではindexを張ります。
amount:int
val:varchar(255)
このテーブルに40万件ほどデータを入れておきます。

それぞれに対して同じsqlを発行した結果です。
(1)select count(*) from tttt;
　InnoDB:4.78 sec
　InfiniDB:0.73 sec

(2)select sum(amount) from tttt;
　InnoDB:3.06 sec
　InfiniDB:0.38 sec

(3)select code,sum(amount) from tttt group by code;
　InnoDB:6.72 sec
　InfiniDB:1.07 sec

(4)select count(*) from tttt where code='AAAA';
　InnoDB:0.29 sec
　InfiniDB:0.47 sec

(5)select * from tttt where code='AAAA' limit 1;
　InnoDB:0.00 sec
　InfiniDB:6.91 sec

(6)select count(*) from tttt where val='test';
　InnoDB:2.73 sec
　InfiniDB:0.43 sec

(7)select count(*) from tttt where val like '%es%';
　InnoDB:4.08 sec
　InfiniDB:0.48 sec

確かにselectは、速い感じで確かにデータウェアハウスとかに適していそうな感じです。
そのうち使ってみてもよいかもと思ってみたりしてみました。

MySQLでslow_query_logを有効にするようにmy.cnfに書く

MySQLで時間がかかるSQLをログ出力するためには
slow_query_logをONにする必要があります。
でも
my.cnfには
slow_query_log = ON
ではなく
slow_query_log = 1
とかかなくてはいけないのでした。

参考にさせていただいたのは、
http://twitter.com/hirose31/status/2948103962
です。