タグ別アーカイブ: MySQL

複数人開発の時にデータベースの同期をうまく取る方法

最近「意外に若かったんだね」と言われるようになりました、その言葉の指すところについては考えないようにしています岡田洋一ですコンニチハ。

さて、僕はしばらくチームで開発を行っています。大人数というわけでも無く、2〜3人での小規模プロジェクトチームです。少人数制のチームではありがちで、僕以外のメンバーもプログラム〜インフラ周りも修正することが多く、データベースのテーブル構造を修正することが各自にありえます。

さてそんなときにデータベースの同期を取る方法について考えてみました。

現状はテーブルのCREATE分を出力させ、プロジェクトのwikiに書き込んでいます。

wikiは変更情報を保存しますので、ある程度のバージョン管理ができました。が、いかんせん「出力、編集、保存、確認、伝達、適用」と、修正内容をチーム内で共有するためのステップが多いのです。少人数チームでステップが多いのは良くないですね。素早さこそがウリですから。

次に「テーブルの差分になるALTERのみを書く」ということについて勘案しました。が、こちらも開発を兼ねているため、テーブルの変更となるALTER分はそこそこの数が出てしまい、すべてのALTERを記録して適用することは面倒となりました。素早さがなくなってしまいます。

 

というわけで、現在のテーブル情報を書き出しつつバージョン管理システムで共有、という手段にしました。

現在プロジェクトはSVNで管理されていたので、テーブル情報のみをファイルに書き出してコミット、それを手元へ適用という手段をとりました。ちなみにデータベースはMySQLです。

テーブル情報のみを書き出す為に必要なコードは


shell> mysqldump -h mysqlhost -u user -ppassword --no-data target_schema | sed 's/ AUTO_INCREMENT=[0-9]*//' > tables.sql

shell> svn commit tables.sql -m 'update table sql.'

となりました。mysqlのホスト名、ユーザ名、パスワード、対象スキーマは読み替えて下さい。パスワードはナシにするとプロンプトで聞いてきます。

パイプでsedに渡してAUTO_INCREMENTを消しています。

最後にこの出力をファイルに書き出して、コミットして共有します。

 

これだと差分をうまく見ることができるので便利ですね!

MySQL接続で起こった怪奇現象

今日も引き続いてzabbixの設定をしてました。そのなかでmysqlコマンドを利用してzabbixに接続をさせてあげたかったんです。で、その時のテスト時に不可解な現象が、理解不能な現象が起きました。

MySQL上でzabbixという名前のユーザを作り、ローカルホストのログインのみパスワード無しで接続可能にしたんですね。つまりTCP/IPとかの接続はダメでローカルのsock通信のみOK、ローカルからのmysqlコマンドでつなげばOKと。

ユーザを追加したので鼻歌でも歌いながら接続試験をしてみました。

mysqlhost> sudo -u zabbix mysql
ERROR 1045 (28000): Access denied for user 'hogetan'@'localhost' (using password: NO)

が、繋がらない。見てみるとaccess deniedしてる対象ユーザがsudo元のhogetanになってる!

これじゃ繋がらないよね。でもおかしい。通常sudoすればバイナリを実行するユーザは切り替わるはずなのに切り替わってないご様子。環境変数にその辺りが入ってるのかと思いチェックしてみるも…。

mysqlhost> sudo -u zabbix env| grep hogetan
SUDO_USER=hogetan

うーんむ、怪しい感じではないなぁ。USERとかはちゃんとzabbixになってたし。しょうがないのでnologinを解除してsuしてログインしてやってみることに。

mysqlhost> sudo su - zabbix; env | grep zabbix;
mysqlhost> mysql
ERROR 1045 (28000): Access denied for user 'hogetan'@'localhost' (using password: NO)
mysqlhost> env | grep hogetan

うーむ……。最後のenvはhogetanにヒットするものが無かった。一体ドコの情報を参照しながら元のsudoユーザ名でmysqlコマンドをつなごうとしてるのだろうか。

結局、結局結局、mysqlコマンドに-uでつながせることで落着はしそうなのですが、何とも腑に落ちない結末でした。

ちなみにですがsudoをするユーザをfugatanとかにすると確かに今度はfugatanでrejectされてるんですね。なのでmysqlコマンドがかならずhogetanユーザになる、というわけでもなさそうです。またmysqlホスト、sudoを行う場所をlinuxで試してみたらこれはちゃんと所望の結果が得られました。

FreeBSD特有の挙動なのかなぁと思いながら今夜も寝れなさそうです。

解決やお心当たりのある方は是非教えて下さい。

MySQLのvarchar型をintegerにcastしながらorderとかするとどうなるか

1. 始めに
MySQLを使っていてとある問題に当たった。文字列と数字列が混在するカラムで、数字のみの比較を取りたくなった。比較というか厳密にはmax()を取りたかった。
varchar型のカラムなのでmax()をするとそのカラムの中で9から始まる列が最も大きなカラムとして認識されてしまった。
例えば、1234 と 99 という二つの数字(カラムはvarcharなのでMySQL的には文字列)があり、そのカラムに対してmax()を取ると99がmaxとして出てきてしまった。
これは想像の通り、1234の1という文字と99の9という文字では9の方が文字として大きいとして認識されてしまったからだ。
そこでintegerにcastしたときにどんな動作になるか試してみた。

続きを読む MySQLのvarchar型をintegerにcastしながらorderとかするとどうなるか

wordpressでリモートにある環境にデータベースを転送したい

はめじに

wordpressでリモートにある環境にデータベースを転送したい。転送というか同期というか。

通常の環境であれば管理者メニューからエクスポート > インポートとすればすむ話なのだけれど、今回はちょっと変わった事情があった。

その条件ってのは下記の通り

  • 手元のサーバにあるワードプレスのデータベースの一部(コメントとかは上書きしないようにする)をリモートサーバに転送したい
  • リモートサーバにはsshでログイン可能
  • テータベースの一部のうち、post_metaにあるmeta_keyがviewsは上書きしたくない
  • 転送するのは一日に一回ぐらいのそこそこのペースで転送したい

リモートホストにsshでログインできるのでsshとパイプを使ったワンライナーでやってしまおう的な。

またpost_metaのテーブルはちょっとやっかい。今回利用しているプラグインでWP-PostViewsという閲覧数を保管するプラグインがある。これは閲覧数をpost_metaにmeta_key = viewsで管理している。だからそこをハズしたい。

キャプチャ4

というわけでさっくり


mysqldump –h localdbhost –u localuser –plocaldbpassword  --add-drop-table wpdbname wp_options wp_posts wp_postmeta wp_term_relationships wp_term_taxonomy wp_terms wp_users wp_usermeta | ssh remotesshuser@remotehost.example.com mysql -f -h remotedbhost -u remotedbuser –premotedbpassword  remotewpdb

これでひとまずpost_meta以外はいけた。

データがちょっと複雑なpost_meta

post_metaに関してはmysqldumpにwhere句が使えるらしいので使ってみる。

次にpost_metaをうまく通してやるためにオプションを調整してやる。


mysqldump –h localdbhost –u localuser –plocaldbpassword   --add-drop-table=false --extended-insert=false --where="meta_key!='views'" mysqldump –h localdbhost –u localuser –plocaldbpassword wp_postmeta  |  ssh remotesshuser@remotehost.example.com mysql -f -h remotedbhost -u remotedbuser –premotedbpassword remotewpdb

キャプチャ

--add-drop-table=false

これは対象テーブルのdrop table – if exists文を出力に含めないようにするためのオプション。

基本的にはテーブルがあるっていう前提なのでcreate-infoごといらないかもしれない。(create文ごとなくすには—no-create-infoを付けるといい)

今回は安全に動作(運用)するために付けておくことにする。ここで与えたオプションはlong形式の—add-drop-tableなんだけど、実は引数でtrue falseが与えられるらしい。よくありがちなのは—no-add-drop-tableみたいに対応してる気がしてたんだけどそうじゃないみたい。

公式には書いてないので気がつかなかった。っていうか引数ありって本当意外。

  • --add-drop-table

DROP TABLEステートメントをCREATE TABLEステートメントの前に追加します。
MySQL ::   MySQL 5.1 リファレンスマニュアル :: 7.12 mysqldump — データベースバックアッププログラム

--where=”meta_key!=’views'”

これはmeta_keyで上書きしたくない条件を指定。今回は手元のデータのうち閲覧数は上書きしたくなかった。sqlだったらすんなりwhere句書いちゃえば楽なのになーって思ったらmysqldumpでもwhereが書けるらしい。

  • --where='where_condition', -w 'where_condition'

あるWHERE状態に選択された行のみダンプします。ユーザのコマンドインタープリタにとって特別なキャラクタ、もしくはスペースを含んでいる場合、状態の周りをクオートで囲まなければいけません。

MySQL :: MySQL 5.1 リファレンスマニュアル :: 7.12 mysqldump — データベースバックアッププログラム

–extended-insert=false

キャプチャ2

このオプションも引数を取るらしい。このオプションは

  • --extended-insert, -e

複数のVALUESリストを含む、複数行INSERT構文を使用してください。これにより、ダンプファイルサイズを小さくし、ファイルが再ロードされる際の挿入スピードがあがります。

MySQL :: MySQL 5.1 リファレンスマニュアル :: 7.12 mysqldump — データベースバックアッププログラム

となっている。いわゆるbulk insertとかmultiple-row insertsとか言われてるあれにするようなやつですね。


insert hoge (id, hoge) values (1, hoge), (2, hoge1), (3, hoge), (4, hoge);

で、コレって結構早くする上では重要なものなのです。このオプションがonでないと、bulk insertが有効になっていないと、かなり遅くなります。なのですが後述するmysql -fと組み合わさって今回の運用上はoffにしてないとうまく目的を達成できません。

今回の運用上、というところはどういったところなのか。

さっきの例だとid, hogeという二つのカラムに対して四つの行を一つのinsert文でinsertしようとしてる。で、カラムの名前から分かるように、idというのはpkeyなのでunique制約がある。となるとき、上のinsertだとduplicateでinsertできないことがある。もともとid=1な行があるときとか。で、そういうのは無視しながら必要な行だけをinsertしたいので、これらのinsert文を分けて書く必要がある。なのでこのオプションを有効にしてやる。するとこんな感じのinsert文になる。


insert hoge (id, hoge) values (1, hoge);

insert hoge (id, hoge) values (2, hoge1);

insert hoge (id, hoge) values  (3, hoge);

insert hoge (id, hoge) values (4, hoge);

これでid=1は通らなくても他のinsert文は通ってくれるので期待通りの結果になるかな!

mysql –f

受け側のオプション。エラーがあっても処理を続行する。先のcreate文ですでにtableが存在しても続けたり、insert文でduplicateな行だったとしても続けてくれる。おかげさまでイケるようになります。

おわりに

これでcronに入れとけばテキトウに同期してくれそう。sshでパスワードのプロンプトをなくすために公開鍵をやりとりする、とかやる必要はあるけれど。

でもこれできっと便利。しらばらくは手でやってみるけど。

LDAPを利用したFreeBSDでMySQLが起動しない

LDAPを利用したFreeBSDでMySQLが起動しない。


shell> /usr/local/etc/rc.d/mysql-server start
Starting mysql.
UID must be greater than 1000
su: Sorry

エラーメッセージの通り、ldapに登録されているmysqlユーザがuidNumberが88で1000以下になっている。なのでうまくうごかないみたい。


shell> more /usr/local/etc/ldap.conf


pam_min_uid 1000

となっていたので pam_min_uid をコメントアウト。うまくいった。

Debianでtritonnをインストールしようとしたらコケた

debianでtritonn, senna, mysqlをインストールしようとコンパイルしたらコケた。

バージョンは tritonn-1.0.12-mysql-5.0.87

このバージョンだとFreeBSDでもコンパイルが通らなくて、linuxならいけるでしょ!って思ったら無理だった。

しょんぼりしながら湯バード先生に泣きついて、ググってもらって情報が出てきたり。

ありがとう!湯バード先生!

続きを読む Debianでtritonnをインストールしようとしたらコケた

Ubuntu 11.10でmysql-workbenchがインストールできない

Ubuntu 11.10にmysql-workbench 5.2.35を入れようと思ったら11.04のパッケージしか無い。まぁ入るだろうと思って

sudo dpkg-i mysql-workbench-gpl-5.2.35-1ubu1104-amd64.deb

して、要求されるパッケージを入れたけど、ダメ。
ソースから入れようと思ってconfigureで要求されるライブラリを一つ一つ入れて行ったけど、Cの構文エラーは出るし宣言エラーは出るし全然通らない。

で、数時間の探し回ったら パッチ
コンパイル方法の解説を発見
手順どおりにソースを展開したディレクトリで

% sudo apt-get install build-essential autoconf automake libtool libzip-dev libxml2-dev libsigc++-2.0-dev libglade2-dev libgtkmm-2.4-dev libglu1-mesa-dev libmysqlclient15-dev uuid-dev liblua5.1-dev libpcre3-dev g++ libglade2-dev libgnome2-dev python-pexpect libboost-dev libsqlite3-dev python-dev libgnome-keyring-dev libctemplate-dev
% patch -p0 < ../5.2.35-ubuntu_11_10_compile_fix.patch
% ./autogen.sh -prefix=/usr/local
make

パッチを当てないとコンパイルが出来ても
起動スプラッシュでフリーズするらしい
makeはCore2Quad 2.4GHzのメモリ4GBで30-40分かかるらしい。

freebsdでsmartctlをssdに対して試してみた。

freebsdでsmartctlをssdに対して試してみた。

対象のSSDはintelのX25-M。SSDSA2M080G2GC

mysqlのスレーブでホストを二台渡り歩いた子。zfs。

参考のサイトがいろいろと細かく説明してくれて他のでそこを見ながら自分のところでも試してみた。

はてなにおける SSD の実績 - mura日記 (halfrack)

root shell> portinstall -c sysutils/smartmontools

root shell> smartctl -a /dev/ad8
smartctl 5.40 2010-10-16 r3189 [FreeBSD 8.1-RELEASE amd64] (local build)
Copyright (C) 2002-10 by Bruce Allen, http://smartmontools.sourceforge.net

=== START OF INFORMATION SECTION ===
Model Family:     Intel X18-M/X25-M/X25-V G2 SSDs
Device Model:     INTEL SSDSA2M080G2GC
Serial Number:    serial
Firmware Version: 2CV102HD
User Capacity:    80,026,361,856 bytes
Device is:        In smartctl database [for details use: -P show]
ATA Version is:   7
ATA Standard is:  ATA/ATAPI-7 T13 1532D revision 1
Local Time is:    Wed Feb  9 14:16:36 2011 JST
SMART support is: Available - device has SMART capability.
SMART support is: Enabled

=== START OF READ SMART DATA SECTION ===
SMART overall-health self-assessment test result: PASSED

General SMART Values:
Offline data collection status:  (0x00) Offline data collection activity
was never started.
Auto Offline Data Collection: Disabled.
Self-test execution status:      (   0) The previous self-test routine completed
without error or no self-test has ever
been run.
Total time to complete Offline
data collection:                 (   1) seconds.
Offline data collection
capabilities:                    (0x75) SMART execute Offline immediate.
No Auto Offline data collection support.
Abort Offline collection upon new
command.
No Offline surface scan supported.
Self-test supported.
Conveyance Self-test supported.
Selective Self-test supported.
SMART capabilities:            (0x0003) Saves SMART data before entering
power-saving mode.
Supports SMART auto save timer.
Error logging capability:        (0x01) Error logging supported.
General Purpose Logging supported.
Short self-test routine
recommended polling time:        (   1) minutes.
Extended self-test routine
recommended polling time:        (   1) minutes.
Conveyance self-test routine
recommended polling time:        (   1) minutes.

SMART Attributes Data Structure revision number: 5
Vendor Specific SMART Attributes with Thresholds:
ID# ATTRIBUTE_NAME          FLAG     VALUE WORST THRESH TYPE      UPDATED  WHEN_FAILED RAW_VALUE
3 Spin_Up_Time            0x0020   100   100   000    Old_age   Offline      -       0
4 Start_Stop_Count        0x0030   100   100   000    Old_age   Offline      -       0
5 Reallocated_Sector_Ct   0x0032   100   100   000    Old_age   Always       -       10
9 Power_On_Hours          0x0032   100   100   000    Old_age   Always       -       5942
12 Power_Cycle_Count       0x0032   100   100   000    Old_age   Always       -       35
192 Unsafe_Shutdown_Count   0x0032   100   100   000    Old_age   Always       -       20
225 Host_Writes_32MiB       0x0030   200   200   000    Old_age   Offline      -       1472403
226 Workld_Media_Wear_Indic 0x0032   100   100   000    Old_age   Always       -       4952
227 Workld_Host_Reads_Perc  0x0032   100   100   000    Old_age   Always       -       0
228 Workload_Minutes        0x0032   100   100   000    Old_age   Always       -       3426303219
232 Available_Reservd_Space 0x0033   099   099   010    Pre-fail  Always       -       0
233 Media_Wearout_Indicator 0x0032   072   072   000    Old_age   Always       -       0
184 End-to-End_Error        0x0033   100   100   099    Pre-fail  Always       -       0

SMART Error Log Version: 1
No Errors Logged

SMART Self-test log structure revision number 1
No self-tests have been logged.  [To run self-tests, use: smartctl -t]

Note: selective self-test log revision number (0) not 1 implies that no selective self-test has ever been run
SMART Selective self-test log data structure revision number 0
Note: revision number not 1 implies that no selective self-test has ever been run
SPAN  MIN_LBA  MAX_LBA  CURRENT_TEST_STATUS
1        0        0  Not_testing
2        0        0  Not_testing
3        0        0  Not_testing
4        0        0  Not_testing
5        0        0  Not_testing
Selective self-test flags (0x0):
After scanning selected spans, do NOT read-scan remainder of disk.
If Selective self-test is pending on power-up, resume after 0 minute delay.

参考にしたサイトによると、どうやらSMART Attributsというのが見るべきデータらしい。

ここの233 Media_Wearout_Indicatorというのがメディア摩耗度、って意味なのかな、重要らしい。
media wearout indicatorは100%から始まって0%でオシマイらしいです。
となるとメディア摩耗耐久度、というのが正しいのかな。

この辺の解説をしてあったサイトを一つ。
8th way to explore the world: Intel SSD Toolbox(予定)

で、うちの値は
9 Power_On_Hours 5942 hours
233 Media_Wearout_Indicator 72%
なので、5942hours = 247 days = 0.6783 years
2/3年で72%になったようです。あと4倍ぐらいいけそうなので後二年ぐらいは持ちそう、なのかな。

このホストの運用はinnodbをメインにつかってる。file_per_table(だっけ)をonにして、innodbのデータファイルだけを置くようにしてます。今のところ50GB~60GBぐらいで何とか収まってる。
ログファイルとかtmpテーブルとかは他のraidに逃がすように。できるだけ読み込みばっかりをさせるように。
あと二年持てば容量的な寿命もきそうな希ガス。

他のスレーブに比べてイマイチ速度も実感できず、追加購入もしておらず・・・。
crutialのc300が最近評判よくて気になってたり。

FreeBSD8.1にtritonn-1.0.12-mysql-5.0.87を入れようとしたらエラーがでた。

FreeBSD8.1にtritonn-1.0.12-mysql-5.0.87を入れようとしたらエラーがでた。

ソースが二種類あって、linuxの方を間違えて落としたら中にlinuxのバイナリがあったので利用できなかった。

下記はそのエラーメッセージ。


shell>./configure
 NOTE: This is a MySQL binary distribution. It's ready to run, you don't
 need to configure it!

To help you a bit, I am now going to create the needed MySQL databases
 and start the MySQL server for you.  If you run into any trouble, please
 consult the MySQL manual, that you can find in the Docs directory.

ELF binary type "0" not known.
 ELF: not found
 ./bin/my_print_defaults: 1: Syntax error: word unexpected (expecting ")")
 ./bin/my_print_defaults: 103: Syntax error: Error in command substitution
 ELF binary type "0" not known.
 ELF binary type "0" not known.
 Neither host 'fillmore.l3tp.org' nor 'localhost' could be looked up with
 ./bin/resolveip
 Please configure the 'hostname' command to return a correct
 hostname.
 If you want to solve this at a later stage, restart this script
 with the --force option

このときに拾ってしまったソース(?)はtritonn-1.0.12-mysql-5.0.87-linux-x86_64.tar.gz。

ソースのみのはtritonn-1.0.12-mysql-5.0.87.tar.gz。

でもこれだとyaccが見つからないorオプションが違う、となってコンパイル通らない。

しょうがないので5.0.67で。