タグ「MySQL」が付けられているもの

サーバのバックアップ

先日自宅サーバの障害復旧で面倒な目に遭ってしまったので、色々と見直し。。。


まずサーバのバックアップ

反省。定期的に取りましょう(^^; 少なくともバージョンアップ前には・・・

  • ディザスタリカバリ:無し→Acronisでイメージバックアップ(外付けHDDにバックアップ)
LVMだったのでスナップショットに頼り切っていて、バックアップを怠っていたので
  • ファイルシステム:LVM→Ext3
Acronisでイメージバックアップが取れるようにExt3を使用することに
  • スナップショット:LVMスナップショット→cronとdumpとtarで地味にバックアップ
ということで、スナップショットは便利だけど捨てて、地味に昔ながらのファイルバックアップを行う事に。

Acronis True Image Home (面倒臭くて2009しかインストールしてなかったのだけど、Home2010のライセンスは買ってあったので)なんて久しぶりに使ったのだけど、簡単にUSBブート出来るようになってたりしていて、地味に便利になっている。

あと、スナップショットは便利だけど、スクリプト組んでファイルバックアップを行う方法は昔ながらのノウハウで色々出来て楽っちゃ楽。

バックアップだけをみると、MacOSXのTime MachineはGUIで簡単にスナップショットから好きなファイルだけ抜いてこれる上、ディザスタリカバリにも対応できる点が良いかな。Windows7の標準バックアップも賢いようだけどまだ使ってみてない。地味にWindows2008のServer BackupはディザスタリカバリにDVDからの復旧が使えるようになってたり良くなってる。何にせよ問題になるのがデータベースのバックアップ。スナップショットだろうとファイルでバックアップを行う方法だろうと、データベースの不整合から逃れる術は無くて、結局データベースの停止を行ってダンプしてファイルにバックアップするか、オープン中のファイルをバックアップできるソリューションを使う必要が有るのだけど。


データベース(MySQL)のバックアップ

結局完全なバックアップは難しいのでファイルへのダンプと、データベースのクラスタを併用することに。
  • ファイルへのダンプ
mysqldumpでフルダンプ。テキストファイルに吐き出しておく。

/usr/bin/mysqldump -x -A -u データベースのユーザ --password='パスワード' --opt > /var/db-backup/DB-dump.`/bin/date +'%Y%M%d%k%M'`
gzip /var/db-backup/DB-dump.`/bin/date +'%Y%M%d%k%M'`

とか、そんな感じのシェルスクリプトを作ってcronで定時実行する。

  • MySQLのレプリケート
まずマスターサーバ側でネットワークソケットを空けたり、bin-log取ったりと、クラスタ化の準備。

/etc/mysql/my.cnfに
[server]
server-id        = 1
log-bin
を追記。

データベースに設定を反映させるために再起動する
(Slave)# sudo service mysql restart

レプリケート用にREPLICATION SLAVE権限を与えたユーザを作っておく。
mysql(Master)> GRANT REPLICATION SLAVE ON *.* TO repl@10.0.0.2 IDENTIFIED BY 'password';
データベースの更新をストップして
mysql(Master)> FLUSH TABLES WITH READ LOCK;
バイナリログを確認(スレーブで同期させるときに必要な情報)
mysql(Master)> SHOW MASTER STATUS;
+-----------------+----------+--------------+------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------+----------+--------------+------------------+
| dbsv-bin.000001 |       79 |              |                  |
+-----------------+----------+--------------+------------------+
1 row in set (0.00 sec)

スレーブ側にコピーする為にデータベースのダンプを取る
(Master)# tar zcf backupfile.tar.gz /var/lib/mysql/
(取ったファイルはスレーブ機にコピーする)
ファイルが取れたのでデータベースの更新を許可
mysql(Master)> UNLOCK TABLES;

別のホストでスレーブサーバを立てて、レプリケートを行う。

データベースを停止して
(Slave)# sudo service mysql stop

スレーブの設定を記述する
/etc/mysql/my.cnfに
[server]
server-id        = 2
を追記。

データベースに設定を反映させるために再起動する
(Slave)# sudo service mysql restart

マスターで採取したファイルを解凍&展開
(Slave)# cd /
(Slave)# tar zxf backupfile.tar.gz

データベースを動作させて
(Slave)# sudo service mysql start

マスターとの同期情報を設定して
mysql(Slave)> CHANGE MASTER TO
MASTER_HOST='10.0.0.1',  ← マスターのホスト名/IPアドレス
MASTER_USER='repl', ← マスター接続に使用するユーザー名 MASTER_PASSWORD='password', ← パスワード MASTER_LOG_FILE='dbsv-bin.000001', ← さっきマスターで確認したファイル MASTER_LOG_POS=79; ← さっきマスターで確認した位置

同期を開始させる
mysql(Slave)> START SLAVE;

こんな感じでMySQLのファイルダンプと、MySQLのレプリケーションを設定した。
レプリケーションは冗長目的なので、あくまで受け身の同期でしかない。selectとかの検索系のクエリは使えるけど、insertとかupdateとかの更新系のクエリは使えない(というかマスターに反映しない)。けど、必要が有ればスレーブ側のデータベースを停止させればデータベースのフルバックアップが取れるし、スレーブ側のデータベースを再開させれば、またマスターとの同期が行われる。
検索系のクエリを分散させれば、一応データベースの負荷分散にもなるのだけど、そもそも負荷の問題は無いし、目的が違うので気にしない。
MySQLのレプリケーションについては公式サイトにある情報(http://dev.mysql.com/doc/refman/5.1/ja/replication.html)で必要な情報は揃えられる。SQL ServerであればMSDN(http://msdn.microsoft.com/ja-jp/library/ms187048.aspx)か?

という事で、やっとデータのバックアップとかするようになりました。

Win32::ODBCを弄ってみる

  • 投稿日:
  • by
  • カテゴリ:
ActivePerlからMySQLを使いたかったんですが・・・以前Linux/UNIX環境ではDBIとDBD::MySQL使ってやったのですが、今回は別の方法を採ることにしました。ActivePerl用のDBD::MySQLのPPMパッケージも存在するのですが、今回は既存の環境があってその上で動かさなきゃならないという制約もあったし、インターネットへの接続も出来ない。その上、インストール作業自体も出来るだけ簡単に行えるようにする必要があったので、Windowsへインストールするものはインストーラが用意されているものを中心に選ぶことにしました。幸いMySQLのODBCコネクタならmsi形式のインストーラも準備されているし、ActivePerlの配布パッケージにもWin32::ODBCが含まれているので、ODBCコネクタ、MySQLとActivePerlをGUIからインストールするだけで環境を整える事が出来そうです。そんな訳でMySQLのODBCコネクタをインストールして、PerlからWin32::ODBC経由でMySQLを使うことにしました。使いかた自体にさほど差はないのですが、データベースとの接続部分はODBC側の設定になるのでPerl上で設定すべき項目が少ないのは便利かも知れません。

バックアップの仕込み

先日のトラブルでデータベースのバックアップが必要という事に気づいて、バックアップを取ることにしました。

#!/bin/bash
DATE=`/usr/bin/date '+%Y%m%d%H'`
DBPASSWORD=MySQLパスワード
BACKUPDIR=~/バックアップディレクトリ
BACKUPDB=$BACKUPDIR/mysql.dump.$DATE
BACKUPCN=$BACKUPDIR/コンテンツ.$DATE.tar.gz

/usr/local/mysql/bin/mysqldump --all-databases --password=$DBPASSWORD > $BACKUPDB
/bin/tar zcf $BACKUPCN ~/public_html

こんな感じでシェルスクリプトを書いて、cronでバックアップをしてみることにしました。
MovableTypeのエクスポート機能がコマンドラインから呼び出せるともっと良いのですが。

早とちり

データベース不調と思ってデータベース全削除&リストアしてみたり、テストしてみたり・・・色々試行錯誤してみたものの結局「IPアドレス変更したときにプログラム書き換えていなかった」だけ・・・orz


----

PostgreSQLはデータベースのバックアップはコマンドラインから取れます。

$ pg_dumpall > ファイル

リストアはこの通り

$ psql template1 < ファイル

ちなみにMySQLの場合のバックアップ

$ mysqldump --user=ADMINUSER --password=PASSWORD -x --all-databases --add-drop-database --add-drop-table > ファイル

リストア

$ mysql -u root -p < ファイル

MySQLのDATETIME型の演算

MySQLのDATETIME型のデータを演算させると、思いもよらない形になってしまった。
やりたかったことはdata型のデータフィールドと現在日時(now())を比較して、経過時間がどれだけか調べること。普通にUNIXTIMEに変換して処理できるものと思いこんでいたことが原因。

普通に

sql> SELECT (now() - datetime) FROM table;
とやると、どうも不思議な値が返ってきてしまう。
例えば、現在時刻を2007-07-22 10:05:00として、
datetimeが2007-07-22 10:03:00とすると、返り値は200になる。
datetimeが2007-07-22 09:55:00とすると、返り値は5000になる。
datetimeが2007-07-22 10:00:00とすると、返り値は500になる。
どういう事か、1分=100、1時間=10000になってしまうようだ。
このままでは、現在時刻によって経過時間が変わってしまう。

仕方ないのでTIMEDIFF()を使った。

SELECT timediff(now(), datetime) FROM table;
こうすると、返り値は'00:00:00'という形になる。
その為、返り値を再度「:(コロン)」で分割して、HH:MM:SSをS=SS+MM*60+HH*3600という形で利用することにした。

もっとスマートな方法がありそうな気がするのですが・・・。う〜む。

結局バージョンアップ

面倒だけど・・・見つけちゃったモノはしょうがないしなぁ・・・という事で
MySQLをバージョンアップ

念のためMySQLのデータベースをdump

# mysqldump --user=DBUSER \
--password=DBPASS \
--all-databases \
--add-drop-database \
--add-drop-table > ~/mydb.dmp

MySQLを止めて

# mysqladmin shutdown -u DBADMINUSER -p

コンパイル&インストール

# ./configure
# make
# make install

後はMySQLを再起動

# ./mysqld_safe -u DBADMINUSER &

CPUがショボイのもあって、これだけで1時間以上掛かっちゃうんですよね(^^;

ApacheをPerlスクリプト経由でデータベースに吐き出す方法だが
パフォーマンスの問題が大丈夫か?という不安があった。

実際やってみると、毎回起動している訳ではないためある程度
大丈夫そうという事が判った。

http://httpd.apache.org/docs/2.2/ja/logs.html#piped

Apacheのドキュメントにあるのだが、
>Apache はパイプ経由のログ用のプロセスをサーバの起動時に実行し、 サーバの実行中にそのプログラムがクラッシュしたときはそれを再び 実行します。(この最後の機能がこの技術が「信頼性のあるパイプ経由のロギング」 と呼ばれている理由です。)

という記述があり、Perlを利用してもパフォーマンス的には問題ないだろうと思う。

最初は行毎にスクリプトが呼び出されるのかと思い、
my line = <>
と記述していたが上手くいかず上記のドキュメントを発見して
while (my $line = <>) {
とすることでログを取得することが出来た。


別の方法としてmod_log_sqlを利用する方法もあるが、DBとしてMySQLしかサポートしていないようである。Perl+DBIで書き出す場合はDBIがサポートする他のDBを利用できることが利点となる。

勉強も兼ねてApacheのログをデータベースに出力してみる。

何で?わざわざDBに?という事だが、
メリット)
・複数Webサーバのログを集約することが出来る
・ログから条件で抜き出す事が容易かつ高速

デメリット)
・DBのメンテナンスが必要
・ログの記録に関してはパフォーマンス的にどうか?

実際に出力する方法としては
httpd.confのCustomLog行でパイプが使える(rotatelogもパイプ使いますよね・・・)ので、「標準入力から入力されたアクセスログをデータベースに書き出す」プログラムがあれば実現可能となる。

CustomLog "|/usr/local/bin/PROGRAM" common

勉強も兼ねてPerlで書いてみた。
PerlからDBを操作する方法としてはDBIを利用するのが簡単そうである。

テストで書いてみたので、置いてみる。テストで作ってみたPerlソースファイル

多分自分が一番見るんだけど。

標準入力(<>)から入力された行を区切って項目別に分けて、データを準備して、それをSQL文(INSERT)に渡すだけである。

ちょっと考えたけど、この方法が簡単。

出力CSVファイルはデータベースの格納されているフォルダに出力される。
CSVなので区切り文字は ,(カンマ)、囲み文字は "(ダブルクォーテーション)が良いだろう。

select * from テーブル名 into outfile "出力CSVファイル" fields terminated by '区切り文字' optionally enclosed by '囲み文字';

逆にCSVを取り込むときは

load data infile "入力CSVファイル" into table テーブル名 fields terminated by '区切り文字' optionally enclosed by '囲み文字';

となる。


これで容易にデータベースからCSVで書き出したり、戻したり出来ますね。

PerlからMySQLを使う。

そもそも、ApacheのログをMySQLで管理したいってのが目的。ApacheのログをMySQLに吐き出すには、mod_log_sqlを使う方法がある。それに問題は無いのだけど、どうせなら今までのログもSQLに取り込んでおきたい。という事で、Perlを使ってログをDBに吐き出す事にした。PHPでデータベースを使う例は良く見るんだけど、Perlでの例はあまり見たことがなかったので、メモしておく。


前準備として、CPANからDBIとDBD:mysqlをインストールしておく。

# perl -MCPAN -e shell
cpan[1]< install DBI
cpan[2]< install DBD:mysql

これでPerlでMySQLは簡単に使える。

前準備としてDBIを使う宣言

use DBI:

データベースへの接続

$dbh=DBI->connect("DBI:mysql:データベース名:サーバ", "ユーザ名", "パスワード");

SQL文の準備

$sql="INSERT INTO テーブル (...) VALUE (...);"
$sth=$dbh->prepare($sql);

SQL文の実行

$sth->execute;

ステートメントハンドルオブジェクトを閉じる

$sth->finish;

データベースハンドルオブジェクトを閉じる

$dbh->disconnect;

手順はこれだけ。DBIの作者に感謝してありがたく使わせてもらいます。