2007/11/22 木曜日

MySQL リプリケーションとストアド

Filed under: mysql — admin @ 16:59:25

ストアドプロシージャと、リプリケーションを両方使う場合はすんなりうまく行かない。

まず、リプリケーションをする場合は定義文に
DETERMINISTIC or NOT DETERMINISTICが必要となる
DETERMINISTIC とは引数が同じであれば、返値が同じ場合。
NOT DETERMINISTICの場合は、ランダム関数が入るとかで入力値に対して出力値が毎回違う場合。

ストアドファンクションを定義したあとに、バイナリログをオンにすると、このDETERMINISTICが
定義されてないからおかしなことになる。

そういう場合はDETERMINISTICであるとみなして動かすこともできる。

SET GLOBAL log_bin_trust_function_creators = 1;

また、マスター側でストアドプロシージャのダンプを流すと、
スレーブが止まってしまう致命的なバグがあるようだ。

リプリケーションを使うならストアド系を使わない方が無難かもしれない。

■ 追記 ストアドプロシージャをDUMPしたものがDELIMITERと大文字になっててこれでは動かなかった。
DELIMITER → delimiter としたら動いた。なんでだろう。

No Tags

MySQLのレプリケーション機能解説

Filed under: mysql — admin @ 16:27:24

■ マスター側でリプリケーションユーザー作成

GRANT REPLICATION SLAVE ON *.* TO   repl@host IDENTIFIED BY 'password';

■ 参考リンク

No Tags

2007/11/21 水曜日

MySQL リプリケーションサーバーの作り直し

Filed under: mysql — admin @ 14:56:48

まず、SLAVEを停止

STOP SLAVE;

作り直す対象データベースを削除。
※ 要サーバーの確認!!。
マスターを消したら目も当てられない。><

drop database [DataBase];  

リプリケーション用dumpデータをインポート

mysql 対象テーブル -uユーザー -pパスワード < dump.sql

ストアドファンクションをインポート

mysql 対象テーブル -uユーザー -pパスワード < proc.sql

スレーブを復活させる。

START SLAVE;

スレーブの状態を確認する。

SHOW SLAVE STATUS;
No Tags

MySQL ストアドプロシージャのdump

Filed under: mysql — admin @ 14:17:05

mysqldumpのマニュアルによると –riutines -Rオプションを使う。
version 5.0.20より前は使えないので要確認
タイムスタンプはリロード時のものになるので
mysqldumpではタイムスタンプも同じにしたければ
ストアドプロシージャが格納されている
mysql/var/mysql/procテーブル
を直接コピーしろと書いてある。見も蓋もない。

リプリケーションしてる場合は忘れずに実行!!

例) ストアドプロシージャ・ファンクションだけ抜き出す場合 -n -t -dがポイント

mysql/bin/mysqldump [Table] -n -t -d -u[User] -p[Pass] -R -Q --opt 
  -r/var/dump/proc.sql --single-transaction --lock-tables=false
No Tags

2007/11/16 金曜日

ブラウザベースの高性能プロジェクト管理「Epiware Document Management」

Filed under: mysql, php — admin @ 11:17:15

MOONGIFT: » ブラウザベースの高性能プロジェクト管理「Epiware Document Management」:オープンソースを毎日紹介

No Tags

2007/11/14 水曜日

phpでmysql UNIXソケットの設定をするには

Filed under: mysql, php — admin @ 4:41:16

まず、デフォルトのmysqlのUNIXソケットの場所は

/tmp/mysql.sock

rpmの場合は

/var/lib/mysql/mysql.sock

デフォルトの場合は、何も設定しなくても動くが
デフォルト値が異なる場合はphp.ini等で設定してあげる必要がある。

ちなみに、PDOの場合はまた別なのでDSNで設定してあげる必要あり。

unix_socket=/var/lib/mysql/mysql.sock
No Tags

2007/11/1 木曜日

MySQLで自然数の連番を持つ集合を作成

Filed under: mysql — admin @ 19:06:16

ポイントは

■ MySQLのユーザー変数でSETを使わずSQL中でユーザー変数の型を定義する

ユーザー変数は使用する前にSETで初期化しないと、デフォルトでは文字列型で初期値NULLになってしまい初期状態からいきなり加算処理を行ってもうまく動かない。さらにSQL中の同一行でユーザー変数の初期化と加算処理は同時にできない。
なのでUNIONで初期値代入と加算処理を分けることでこれを実現することができる。

適当なテーブルは、作成しようとしている擬似行以上の行が保証されているテーブルであればなんでもよい。

SELECT  @f:=1
UNION ALL
SELECT @f:=@f +1 FROM 適当なテーブル WHERE @f<8

ちなみにオラクルだと

select RowNum from dual connect by Level <= 10;

Oracle SQLパズルを参照のこと

No Tags

2007/10/31 水曜日

SQLで今月のカレンダーを作る(MySQL)

Filed under: mysql, SQL — admin @ 2:40:18

UNION ALL にWHEREを使うサンプルとして作ってみました。
SQLの無駄を省く為にMySQLのユーザー変数も使っています。

■ SQLを短く
月初・月末の週の空白の条件判断を毎行毎に行っているので効率が悪い。SQLは短い。

SELECT
  IFNULL(NULLIF((n - (
    @f:=DAYOFWEEK(CONCAT(DATE_FORMAT(CURDATE(),'%Y/%m'),'/01')))
                                 +1)*((n- @f +1)<=(
    @e:=DATE_FORMAT(LAST_DAY( CURDATE( ) ),'%e'))
                                           ),0),' ') "日",
  IFNULL(NULLIF((n - @f +2)*((n- @f +2)<=@e),0),' ') "月",
  IFNULL(NULLIF((n - @f +3)*((n- @f +3)<=@e),0),' ') "火",
  IFNULL(NULLIF((n - @f +4)*((n- @f +4)<=@e),0),' ') "水",
  IFNULL(NULLIF((n - @f +5)*((n- @f +5)<=@e),0),' ') "木",
  IFNULL(NULLIF((n - @f +6)*((n- @f +6)<=@e),0),' ') "金",
  IFNULL(NULLIF((n - @f +7)*((n- @f +7)<=@e),0),' ') "土"
FROM (
  SELECT  1 n UNION ALL
  SELECT  8   UNION ALL
  SELECT 15   UNION ALL
  SELECT 22   UNION ALL
  SELECT 29 ) AS t1

■ 高速最適化
月初・月末の週の空白の条件判断をそれぞれ1回づつしか行わない。

SELECT
  IFNULL(NULLIF(2 - (
    @f:=DAYOFWEEK(CONCAT(DATE_FORMAT(CURDATE(),'%Y/%m'),'/01'))
                     ),0),' ') "日",
  IFNULL(NULLIF(3 - @f,0),' ') "月",
  IFNULL(NULLIF(4 - @f,0),' ') "火",
  IFNULL(NULLIF(5 - @f,0),' ') "水",
  IFNULL(NULLIF(6 - @f,0),' ') "木",
  IFNULL(NULLIF(7 - @f,0),' ') "金",
  IFNULL(NULLIF(8 - @f,0),' ') "土"
UNION ALL
SELECT
  n-@f,n-@f+1,n-@f+2,n-@f+3,n-@f+4,n-@f+5,n-@f+6
FROM (
  SELECT 9 n UNION ALL SELECT 16 UNION ALL SELECT 23
) as t1
UNION ALL
SELECT
  IFNULL(NULLIF((30 - @f)*((30- @f)<=(
    @e:=DATE_FORMAT(LAST_DAY( CURDATE( ) ),'%e'))
                                       ),0),' '),
  IFNULL(NULLIF((31 - @f)*((31- @f)<=@e),0),' '),
  IFNULL(NULLIF((32 - @f)*((32- @f)<=@e),0),' '),
  IFNULL(NULLIF((33 - @f)*((33- @f)<=@e),0),' '),
  IFNULL(NULLIF((34 - @f)*((34- @f)<=@e),0),' '),
  IFNULL(NULLIF((35 - @f)*((35- @f)<=@e),0),' '),
  IFNULL(NULLIF((36 - @f)*((36- @f)<=@e),0),' ')

■ 解説

    @f:=DAYOFWEEK(CONCAT(DATE_FORMAT(CURDATE(),'%Y/%m'),'/01'))

は今月の1日が何曜日か曜日の位置(日曜1…土曜7)を取ってきてユーザー変数@fに代入します。

    @e:=DATE_FORMAT(LAST_DAY( CURDATE( ) ),'%e'))

は今月の最終日が何日か取ってきてユーザー変数eに代入します。

ちなみに、ユーザー変数に代入しないで、ここの部分をすべてこちらに置き換えても動きます。
但し、SQLが冗長かつ、処理が大変無駄です。

No Tags

2007/10/30 火曜日

UNIONでWHERE

Filed under: mysql, SQL — admin @ 21:56:21

UNIONでORDERは使えるけど
WHEREは直接使えない
なんで結合するSQLごとに それぞれ同じWHEREを指定する

SELECT * FORM A WHERE なんとか
UNION ALL
SELECT * FORM B WHERE なんとか

方法、でもこれだとマージした際のWHEREとかはできないので

SELECT * FROM ( 
SELECT * FORM A
UNION ALL
SELECT * FORM B
)
WHERE なんとか

 という風にサブクエリーにする。

オラクルのカレンダー表示がすばらしい

No Tags

MySQLのSQLチューニング(IN 演算子)

Filed under: mysql — admin @ 19:57:47
SELECT A.*
FROM   TBL A
WHERE A.COL0 = "hoge"
  AND A.COL1 IN ('A', 'B', 'C');

より

SELECT A.*
FROM   TBL A
JOIN (
   'A' COL1  UNION ALL
  SELECT 'B' COL1   UNION ALL
  SELECT 'C' COL1
) B
ON A.COL1 = B.COL1
WHERE A.COL0 = "hoge";

の方が早いそうな。へぇ~。

■ 参考
MySQLのSQLチューニング(IN 演算子)

No Tags
« 前のページ