システム構築に関する覚書

MySQLのデータベースをmysqlhotcopyでバックアップしようとしたときに以下のエラーが発生しました。

DBI::db=HASH(0xa1e63a8)->disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting) at /usr/bin/mysqlhotcopy line 537.

参考サイトを元に設定したところ正常に動作するようになりました。

参考サイト

• Cronでmysql-backup.shのエラー

自動でキーを設定させている場合、そのキーを取得するにはどうすれば？とういことで mysql_insert_id() を使ったAUTO_INCREMENTカラムの値の取得の覚書。

$sql = 'insert into テーブル set a = 'test';

$result = mysql_query($query);

$id = mysql_insert_id();

直前のinsert処理を行ったAUTO_INCREMENTの値を取得することができます。

PerlからMySQLを操作できるようにするためにDBD-MySQLをインストールしたときの覚書。

CPANでインストールすれば簡単に設定できるらしいのですが、
今回の環境ではエラーが多発したので、手動でインストールします。

DBIのインストール

# yum install perl-DBI

共有ライブラリの設定

# vi /etc/ld.so.conf

最終行に追加

/usr/local/mysql/lib/mysql

設定の反映

# ldconfig

mysql_config にシンボリックリンクを張る

# ln -s /usr/local/mysql/bin/mysql_config /usr/local/bin/mysql_config

DBD::mysqlのインストール

# cd /usr/local/src

# wget http://ftp.yz.yamagata-u.ac.jp/pub/lang/cpan/authors/id/C/CA/CAPTTOFU/DBD-mysql-3.0004.tar.gz

# perl Makefile.PL

# make

# make install

発生したエラー

mysql_configが見つからない

# perl Makefile.PL

Can't exec "mysql_config": No such file or directory at Makefile.PL line 76.

 

Cannot find the file 'mysql_config'! Your execution PATH doesn't seem not contain the path to mysql_config. Resorting to guessed values!

Can't exec "mysql_config": No such file or directory at Makefile.PL line 466.

Can't exec "mysql_config": No such file or directory at Makefile.PL line 466.

Can't exec "mysql_config": No such file or directory at Makefile.PL line 466.

Can't exec "mysql_config": No such file or directory at Makefile.PL line 466.

Can't exec "mysql_config": No such file or directory at Makefile.PL line 466.

Can't exec "mysql_config": No such file or directory at Makefile.PL line 466.

Failed to determine directory of mysql.h. Use

 

perl Makefile.PL --cflags=-I<dir> 

 

to set this directory. For details see the INSTALL.html file,

section "C Compiler flags" or type

 

perl Makefile.PL --help

上記エラーを回避するために

# ln -s /usr/local/mysql/bin/mysql_config /usr/local/bin/mysql_config

を設定しました。


DBD-mysql-4.005.tar.gz (最新)をインストールするとエラーが多発して使えなかったので、DBD-mysql-3.0004.tar.gzを使いました。

参考サイト

• CPAN経由でLinuxにモジュールを組み込む
• CentOS 4.6 - DBD::mysql インストールトラブル
• PerlDBI/DBDインターフェイス
• cpan2rpmでDBD::mysqlでエラー
• ダウンロードサイト

MySQLをソースからインストールした場合、mysqlコマンドを実行するのに

# /usr/local/mysql/bin/mysql

を実行しないといけません。

これを

# mysql

で実行できるように MySQLのbinディレクトリにパスを通す設定。

# vi /etc/profile

最終行に以下を追加

export PATH=/usr/local/mysql/bin:$PATH

これで今後は フルパスでコマンドを実行しなくてもよくなりました。

また、一時的にパスを通したい場合は、

# export PATH=/usr/local/mysql/bin

でログアウトするまでパスが通った状態になります。

Windows版MySQLは、デフォルトではテーブル名を自動的に小文字に変換してしまいます。

MySQLの設定を変更することで大文字のままテーブルを作成することが可能になります。

my.cnfの[mysqld]項目に

lower_case_table_names=0

を記述し、MySQLを再起動してください。

MySQLでLimit を使って取得する情報に制限をつける場合、Limitを指定しない場合は何件取得できるのかという情報がほしいときがあります。

SQL_CALC_FOUND_ROWSをクエリ文につけることで、全件数の取得が可能になります。

LIMITを使用したクエリ

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM テーブル LIMIT 0,10;

直前のSQL_CALC_FOUND_ROWSを指定したクエリの全件数を取得

mysql> SELECT FOUND_ROWS();

　+--------------+

　| FOUND_ROWS() |

　+--------------+

　|        12345 |

　+--------------+

mysql> SELECT COUNT(*) FROM テーブル;

上記クエリー分で全件数を取得するよりはFOUND_ROWS()を使用したほうが高速に取得することが可能です。

参考サイト

• 人力検索はてな
• tekTechめも

テーブル内のデータで重複しているレコードを表示させるためのSQL


重複しているレコードを１行ずつ表示

SELECT * FROM テーブル GROUP BY 対象フィールド HAVING COUNT(*) > 1;

重複しているレコードを全行表示

SELECT * FROM テーブル

WHERE 対象フィールド in

(SELECT 列A FROM テーブル GROUP BY 対象フィールド HAVING COUNT(*) > 1);

動作環境

• Centos 5
• MySQL 5.0.22 (Master,Slave)

マスターサーバーにレプリケーション用ユーザ作成

通常レプリケーションを行う場合、レプリケーション専用のユーザーを作成します。このユーザーはスレーブがマスタに接続するためのユーザーになります。最低限、与えなければならないのはREPLICATION SLAVE権限です。

mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'192.168.1.0/255.255.255.0' IDENTIFIED BY 'パスワード';

マスタサーバで更新ログを有効にする

# vi /etc/my.cnf

[mysqld]

#replication

# バイナリログを有効

log-bin=mysql-bin

# MySQL識別番号の設定

server-id = 1

設定修正後にmysqlを再起動しておきましょう。

# service mysqld restart

マスタデータのスナップショット作成

テーブル情報が更新されないようにマスタをロックします。

mysql> FLUSH TABLES WITH READ LOCK;

マスタデータのスナップショットの作成

# cd /var/lib/mysql

# tar cpf /var/tmp/mysql-snapshot.tar .

マスタのロック解除

mysql> UNLOCK TABLES;

マスタのバイナリログの位置情報を確認

mysql> SHOW MASTER STATUS;

| File             | Position | Binlog_do_db | Binlog_ignore_db |

| mysql-bin.000001 | 1234     |              |                  |

Positionの数字を確認しておきましょう。スレーブでレプリケーションを開始するためにどの状態からの同期かを設定するのに必要になります。

スレーブにマスタデータのスナップショットを設定

まずスレーブサーバーが起動しているようであれば、停止させてください。

# service mysqld stop

マスタからコピーしたtarファイルをMySQLのデータディレクトリと入れ替えるように展開します。

# cd /var/lib/mysql

# rm -fr *

# tar xpf /usr/local/src/mysql-snapshot.tar

スレーブサーバーの設定変更

# vi /etc/my.conf

[mysqld]

server-id=2

master-host=マスタサーバーIP

master-user=repl

master-password=パスワード

レプリケーションの開始

マスタのサーバーを起動した後に、スレーブのサーバーを起動してください。自動でスレーブが開始されるようになっている場合は、

mysql> stop slave;

でレプリケーションの処理をとめて、以下の処理を実行します。

mysql> CHANGE MASTER TO

   -> MASTER_HOST = 'マスタサーバーIP',

   -> MASTER_USER = 'repl',

   -> MASTER_PASSWORD = 'パスワード',

   -> MASTER_LOG_FILE = 'mysql-bin.000001',

   -> MASTER_LOG_POS = 1234;

MASTER_LOG_POS には Position の番号を指定してください。
処理実行後に

mysql> start slave;

mysql> SHOW MASTER STATUS;

上記処理実行結果画面にてエラーが発生していない場合はレプリケーションが正常に開始されているので、マスタサーバーのデータを追加・修正して、リアルタイムに情報がスレーブに反映されるか確認してください。

レプリケーションについて参考サイト

• 現場指向のレプリケーション詳説
• FedoraCore6でMySQL5.0のレプリケーション設定
• MySQL レプリケーションの設定
• レプリケーションでスレーブ(Slave)エラーの対処
• レプリケーションを使う
• mysqlのレプリケーション : アシアルブログ
• レプリケーション設定 - phpspot
• MySQLでレプリケーション＆フェイルオーバー

1192368596 といったtimestamp表記の日付では何年の何月なのかさっぱりわかりません。

システム上ではphpなどで変換をかけて見やすい書式に変更していると思うのですが、データベースを直接見ているときにこのtimestampって日付いつなんだろうと思うことがあります。

MySQLでtimestampを変更

SELECT FROM_UNIXTIME( 1192368596 );

結果 2007-10-14 22:29:56 が返ってきます。

phpでスクリプトを書かなくても、MySQLのクエリで確認可能です。

理想としてはphpなどでフォーム入力することで、日付を変換して表示してくれるプログラムを持っておくのが一番いいですが、場合によっては使えない場合もあるので、状況にあった使い方をしてください。

PHP5では，従来から含まれているmysql拡張モジュールに加えて，MySQL 4.1以降で定義される新しいAPIをサポートする拡張モジュールmysqliが追加されています。

実際mysqliって何がすぐれているの？ということなのですが、

1. オブジェクトAPIと関数APIの2種類のAPIを使用可能
2. 実行処理を効率化・高速化できる。
   • PDO、PEAR::DB、MySQL関数の速度比較
3. MySQLクライアント・ライブラリの高度な接続オプションを指定可能

全てのファイルを修正していくのは非常に時間のかかる作業です。そこで簡単にアップグレードできるスクリプトがないかと探してみたらやっぱりありました。

機能について

ディレクトリ内のファイルの一括置換
特定のファイルのみの置換
置換作業時の自動バックアップファイル作成機能

手動で更新作業をするより短時間で、しかもミスすることなく修正することが可能になります。

mysql から mysqli に移行を考えている人はこのスクリプトを使ってみるといいかもしれません。

参考サイト

• MySQLはMySQLi変換ツールをリリースします
• ThinkIT Part3：MySQL拡張サポート
• MySQLのクライアントエンコーディング
• C. MySQL 改良版拡張サポート（mysqli）

mMeasureは、MySQLの状態を常時測定し、MySQLのチューニングポイントをアドバイスする、MySQL専用モニタリングソフトです。

• mMeasure公式サイト

設定環境

• CentOS5
• php apache mysql パッケージインストール済み

rpmforgeリポジトリの設定

ダウンロード

# wget http://dag.wieers.com/rpm/packages/rpmforge-release/rpmforge-release-0.3.6-1.el5.rf.i386.rpm

インストール

# rpm -Uvh rpmforge-release-0.3.6-1.el5.rf.i386.rpm

基本リポジトリとのパッケージ競合を避けるため、デフォルトは無効にする

# sed -i 's/enabled = 1/enabled = 0/g' /etc/yum.repos.d/rpmforge.repo

RRDtoolインストール

# yum -y --enablerepo=rpmforge install rrdtool

Jcodeパッケージのインストール

# cpan2rpm --install Jcode

mMeasureのインストール

# wget http://keihanna.dl.sourceforge.jp/mmeasure/18557/mmeasure-1.0.7.tar.gz

# tar vzfx mmeasure-1.0.7.tar.gz

# mv mmeasure /usr/local/

Apacheの設定

# vi /etc/httpd/conf.d/mmeasure.conf

Alias /mmeasure/ "/usr/local/mmeasure/web/"

<Directory /usr/local/mmeasure/web/>

order deny,allow

deny from all

allow from 127.0.0.1

allow from 192.168.0.0

</Directory>

mMeasureの設定

# cd /usr/local/mmeasure/

# cp mmeasure_template.conf mmeasure.conf

設定ファイルの編集

# vi mmeasure.conf

※最低限変更・確認が必要な箇所

#MySQL関連部分

#------------------------------------------------------

# MySQL 

#------------------------------------------------------ 

MYSQL_HOST=localhost 

MYSQL_USER=mmeasure 

MYSQL_PASSWORD=**** 

  

#-------------------------------------------------------

#       Mail

#-------------------------------------------------------

MAIL_HOST=mail.com

MAIL_FROM=info@mail.com

MAIL_TO=info@mail.com

MAIL_CC=info@mail.com

MAIL_BCC=

 

# RRDToolのパス

#---------------------------------------------------------- 

# Pathes 

#---------------------------------------------------------- 

 

PATH_RRDTOOL="/usr/bin/rrdtool"

 

WEBDIR="$PATH_MMEASURE/web/images/graphs"

WIDTH="400"

WIDTH_SMALL="400"

WIDTH_LARGE="400"

HEIGHT="100"

HEIGHT_SMALL="100"

HEIGHT_LARGE="100"

MySQLにmMeasureユーザー作成

# mysql -u root -p

Enter password:

mysql> grant all on *.* to mmeasure@localhost identified by 'パスワード';

Query OK, 0 rows affected (0.03 sec)

 

mysql> flush privileges;

Query OK, 0 rows affected (0.02 sec)

mMeasure 起動

# cd /usr/local/mmeasure/daemon/

# ./mmeasure.sh start

Starting mMeasure daemon...

Checking

Configurations: '/usr/local/mmeasure/daemon/../mmeasure.conf'...

Evaluating arguments...

Entering daemon mode...

起動できなかった場合

Can't locate DBI.pmと出力された場合DBI.pmをインストール

yum -y install perl-DBI

インストール後に再度mMeasureを起動してみる

参考サイト

• MySQLのチューニングソフト「mMeasure」をインストール

現在実行中の mysqld サーバがある場合は、次のステートメントで変数に実際に使用されている値を調べることができます。

mysql> SHOW VARIABLES;

スロークエリをログに出力する

/etc/my.cnfの[mysqld]セクションに記述

long_query_time=2

log-slow-queries=/var/log/slow.log

設定例

2秒以上処理に時間がかかったクエリは指定したログファイルにクエリを出力する

MySQLのクエリキャッシュを有効にする

/etc/my.cnfの[mysqld]セクションに記述

query_cache_limit=1M

query_cache_min_res_unit=4k

query_cache_size=32M

query_cache_type=1

設定の確認

SHOW STATUS LIKE 'Qcache%'

設定確認時の項目の説明

Qcache_free_blocks：空きメモリブロック数

Qcache_free_memory：空きメモリ。

Qcache_hits：キャッシュにヒットしたクエリ数。

Qcache_inserts：キャッシュに保存したクエリ数。

Qcache_lowmem_prunes：メモリ不足で削除されたクエリ数。

Qcache_not_cached：キャッシュされなかったクエリ数。 

Qcache_queries_in_cache：キャッシュに存在するクエリ数。

Qcache_total_blocks：合計ブロック数

クエリキャッシュのヒット率算出

query cache hit 率 = Qcache_hits / ( Qcache_hits + Qcache_inserts + Qcache_not_cached ) * 100

クエリキャッシュの動作

クエリは解析前に比較されるため、

SELECT * FROM table_name

と

Select * from table_name

は、クエリキャッシュで別のクエリとみなされます。完全に一致する（各バイトが）クエリ以外、同一とはみなされません。

参考サイト

• 5分でできる、MySQLのメモリ関係のチューニング！
• MySQL AB :: MySQL 4.1 リファレンスマニュアル :: 5 MySQL の最適化
• 5.5.2. サーバパラメータのチューニング
• MySQLのクエリキャッシュを有効にする