PHPでDBをなんちゃってレプリケーションする例

PHP

2021/02/17 (更新日：2021/02/17)

昨日は「データベースの同期処理（レプリケーション）に関する昔話」と題しておっさんの昔話に花を咲かせてしまいましたが、今回の記事が本題です。

前回は東京～大阪間でデータベースを同期処理（レプリケーション）する例を出しましたが、今回はもう少し身近に「ローカルにあるDBをレンタルサーバーのDBへ同期する」という例で解説していきたいと思います。

PHPやDBの初学者向けの情報はそこら中に転がっていますし、書籍もたくさん出ています。そして上級者向けの本格的な業務システムともなれば通常は会社に所属して開発に従事しているので、社内のナレッジベースや諸先輩方から教えを請うこともできるでしょう。

一方で初級から中級に上がる…………いや中級ってどのくらいか知らんけどｗ…………よちよち歩きから自分の足で立って自分の頭で考えられるレベルになったくらいの段階で必要になるプログラミングテクニックってなかなか見当たらないのではないかな、と。

なので、ぼくの勝手な定義ですが、基本的には初級から中級に上がるぞーくらいのプログラマーを対象としています。

ローカルとリモートでDBを同期したい理由

東京～大阪間でDBを同期する、というならその理由もイメージしやすいと思いますが、ローカルとリモートでDBを同期したくなるケースってどんなことがあるでしょうか。

例えばAmazon API

当サイトでも使っていますし、AmazonアソシエイトのAPIを使って商品情報を取得している方ってたくさんいらっしゃいますよね。

独自の商品データベースが手軽に作れるし、仮にタブレットPC向けサイトを運営しているならAmazonからタブレットPCの商品データだけを大量に取得して便利なデータベースサイトを作ることだってできます。

でもそれを共有のレンタルサーバーでやるのか？っていう話です。

Amazon APIは登録者の売上に応じてAPIのリクエスト回数に上限があります。そのため、WebサーバーにアクセスがくるたびにリアルタイムでAmazon APIを叩いていればすぐに上限に達してしまうWebサイトも少なくありません。
（売上よりも情報発信に主軸を置いている優良サイトほどその傾向は強くなります）

それを回避するためにCronジョブで一定時間ごとにAmazon APIを使うようにするのも良いですが、それにしたってWebサーバーに多少の負荷は与えます。

特に大して売上もあがらない個人サイトにおいてレンタルサーバーの月額費用はできるだけ抑えたいもの。そして格安サーバーは1台あたりに詰め込まれるユーザー数もすごく多い。数百サイトが1台に詰め込まれていることもめずらしくありません。

そんな悲鳴をあげているレンタルサーバーにCronジョブで重い仕事をさせるのかって話です。

だったら、Amazon APIによる商品データ取得はローカルにある自分のPCで実行させて、綺麗に整えてからリモート側（Webサーバー）へ同期しよう、と考えるのも不思議ではないでしょう。

例えばアクセス解析

個人／法人含め、今や誰もがGoogle Analyticsを使っていると思いますが、だからといってWebサーバーのログ（Apacheログ）を解析しなくて良い、ということにはなりません。

Google AnalyticsはJavaScriptで動作しているため、アクセス状況が確認できるのは通常のブラウザに限られます。それのなにがいけないのか。

botや不正アクセスが検出できない点です。

そこそこアクセス数のあるWebサーバーのログを一度でも見たことがある方ならピンとくるかと思いますが、個人運営レベルのサイトでも毎日がんがん不正アクセスが行われています。例えば https://example.com/wp-admin/ でアクセスを試み、WordPressを使っているサイトかどうか確認してくるbotがあります。もしwp-adminがあったらお次はWordPressの脆弱性を付いた攻撃に切り替えてきます。

あるいはURLの末尾にシングルクォーテーションとA=0といった文字列を付加してアクセスしてくるbot。これはSQLインジェクションの脆弱性がないか確認してくるbotです。ここで脆弱性が確認できたらお次はSQL文を送ってきてDBの中身を読み取ろうとします。

こうした不正アクセスを検知してブロックするために、日々Webサーバーのログチェックは必須といえます。

しかしサイトへのアクセス数が膨大になるとログの量も膨れ上がり、プログラムでログのチェックをしてもそこそこCPUに負荷がかかる状態に。それを数百人で共有しているレンタルサーバー上で実行するだなんてとんでもないことです。

ですから、レンタルサーバー上にあるログを圧縮してダウンロードし、ローカル環境で展開→解析→不正アクセスのIPアドレスを抽出→その結果をレンタルサーバーへ戻す、みたいなケースもあります。

SQLiteならDBファイルのアップロードだけでも同期になる

ここからは具体例に入っていきます。

まず、何よりわかりやすい例として、DBファイルのコピーだけで済ませる例です。

具体的には「PHPでFTP/FTPS/SFTPを使ってアップロードする例」で解説していますが、FTPを使ってDBファイルをアップロードするだけ。

uploadFTPS('sqlite.db', '/db/sqlite.db');

function uploadFTPS($local_filename, $remote_filename)
{
	$conn = ftp_ssl_connect('[接続先サーバー]');
	if (!ftp_login($conn, '[FTPユーザーID]', '[FTPパスワード]')) {
		echo 'Login Failed';
		return;
	}
	ftp_pasv($conn, true);
	ftp_put($conn, $remote_filename, $local_filename, FTP_BINARY);
	ftp_close($conn);
}

DB全体が巨大だと、せっかくWebサーバーの負荷を軽減するための措置が意味なくなってしまいますから、main.dbとsync.dbのように通常使うDBとは別にリモートと同期したいテーブルだけを集めたdbを別途用意するのも良いでしょう。

main.dbとsync.dbでテーブルを分けてしまうとJOINが使えないので困る！というのであれば同期したいテーブルをmain.dbとsync.dbの両方に持てば良い話です。そして同期したいときだけsync.dbへテーブルをコピーして、sync.dbをリモートへアップロード。

いくらなんでも力技すぎますか？

自分でもお粗末だと思いますが、これはぼくが過去に運営していたWebサイトで実際にやっていたことです。だって実装が簡単なんだものｗ

うちの場合はWebサイトごとに使うDBシステムが違ったりするので（というより、趣味なのでそのときの気分で使うDBが違う）、SQLiteのようにファイルコピーで済むDBばかりではありませんが、MySQLならmysqldumpコマンドで、PostgreSQLならpg_dumpコマンドでCSVへエクスポートできるので、大した違いはありません。

DBやテーブル全体のコピーには限界がある

さて、前述のファイルコピー（あるいはテーブルコピーでも良い）の例は実際にWebサイトで使っていたと話しましたが、なぜ過去形なのか。

それはsync.dbの量が数MBから数GBに膨れ上がってきたからです。

最初は少ない量だからdbまるごと同期すれば良いやー（鼻ほじ）状態でも、長年運営していると想定外にdbが膨れ上がるのも珍しくないこと。

さすがに数GBもあると1時間に1回リモートと動機するなんて迷惑なことしたくありません。

仮に1GBでも毎時アップロードしていたら1日で24GB、30日で720GBです。

ぼくは過去にxreaという無料レンタルサーバー（ドメイン代だけは払うので完全無料とは言えませんが）を使っていて月間の転送量が120GBを超えたあたりで、サーバーから出ていってくれとやんわりとお願いされたことがありますｗ

さすがに今どきはそこまで厳しい条件のところはないと思いますが、一般的な…そうですね、例えば月額980円くらいの格安サーバーの場合、月間の転送量の目安は10TB前後かなと思います。

もしsync.dbのサイズが10GBまで膨れ上がったとしたら、10GB×24時間×30日＝7.2TB。

同期処理だけで月間転送量の目安に達してしまうかもしれない。そんなアホなことやってられません。

更新した行だけ抽出してリモートへ送る例

ということで、テーブル全体ではなく、更新された行だけを送る例を考えてみたいと思います。

テーブル構造

CREATE TABLE sync_table (
	seq	INTEGER,
	column1 TEXT,
	column2 TEXT,
	column3 TEXT,
	column4 TEXT,
	column5 TEXT,
	upload_date	DATETIME,
	PRIMARY KEY(seq)
);

column1～5はどうでも良いのでテキトーです。大事なのはupload_date。INSERT以外も必要な場合はseqも重要かな。

既に運用中でテーブルが作成済の場合はDATETIME型の項目upload_dateを追加します。

ALTER TABLE sync_table ADD COLUMN upload_date DATETIME;

upload_dateがNULLなら未アップロードと判断します。

送信側（ローカル）のPHP例

/************************************************
 * 主処理
 ************************************************/
//ローカルDBへ接続
$pdo = new PDO('sqlite:sync.db');
//トランザクション開始
$pdo->beginTransaction();
//更新データの抽出
$data = Array();
prepareData($pdo, 'sync_table', $data);
//データをリモートへPOST
upload('https://example.com/receive.php', $data);
//コミット
$pdo->commit();

/************************************************
 * 更新データの抽出
 ************************************************/
function prepareData($pdo, $table, &$data)
{
	//アップロードしていない行を抽出
	$st = $pdo->query('SELECT * FROM '.$table.' WHERE upload_date is null');
	while ($row = $st->fetch()) {
		//連想配列へ行を挿入
		$data[$table][] = $row;
	}
	//upload_dateに現在の日時をセット
	$pdo->query('UPDATE '.$table.' SET upload_date=CURRENT_TIMESTAMP WHERE upload_date is null');
	return $data;
}
/************************************************
 * データをリモートへPOST
 ************************************************/
function upload($url, $data)
{
	$json_text = json_encode($data, JSON_UNESCAPED_UNICODE);
	$json_zip = gzcompress($json_text);
	$base64 = base64_encode($json_zip);

	$param = array();
	$param['base64'] = $base64;
	$content = http_build_query($param, "", "&");

	$header = "Content-Type:application/x-www-form-urlencoded";
	$opts = array(
		'http' => array(
			'method' => 'POST',
			'header' => $header,
			'content' => $content
		)
	);
	return file_get_contents($url, false, stream_context_create($opts));
}

送信側の解説

まず、実際に似たようなプログラムは動かしていますが、自分のサイトに特化しているため、上述のコードはサンプル用に簡略化しています。もし誤字等で動かなかったらごめん！あくまで、参考程度にってことでひとつ。

コンセプトを一言で表すと「更新した行を連想配列に格納してシリアライズして圧縮してPOSTで送る」だけです。

更新されたかどうか判断しているのはupload_dateがNULLかそうじゃないかだけ。

ローカル側で新規登録されたレコード（行）はupload_dateがNULLになるため、何も変更する必要はありません。

レコードの更新をした際だけupload_dateにNULLをセットするのを忘れなければOK。

連想配列のキーをテーブル名にしているのは複数テーブルを送信する可能性を考えてのことです。

同期したいテーブルが増えたとき、

//更新データの抽出
$data = Array();
prepareData($pdo, 'sync_table1', $data);
prepareData($pdo, 'sync_table2', $data);
prepareData($pdo, 'sync_table3', $data);

こんなふうに並べて記述すればsync_table1、sync_table2、sync_table3の更新行がテーブル名と共に連想配列$dataへセットされるという仕組み。

upload部分については連想配列$dataをJSONエンコードでシリアライズしてgzcompressで圧縮してbase64でエンコードしてリモート側のreceive.phpへPOSTしているだけです。

ここがわからない方は「PHPで圧縮したバイナリデータをPOSTする方法」を参照してみてください。

パッと見は面倒に思えるかもですが、データ量でいうと250分の1くらいになります。うちみたいに1時間ごとにローカルとリモートを同期している環境ではこれはとても大きいのです。

それから、データの送信方法がPOSTであることに不安を覚える方もいるかもですが、POSTの送信先は

https://[ユーザーID]:[パスワード]@example.com/receive.php

のように記述することもできます。BASIC認証ですね。

BASIC認証は平文送信だから危ないとか言われることもありますが、SSL下でなら暗号化されていますし、FTPSで送信するのと何ら変わりません。

受信側（リモート）のPHP例

/************************************************
 * 主処理
 ************************************************/
//POSTされたbase64データを取得
$json_zip = base64_decode($_POST['base64']);
//圧縮データを解凍
$json_text = gzuncompress($json_zip);
//jsonテキストを連想配列へ変換
$data = json_decode($json_text, true);
//DB接続
$pdo = new PDO('sqlite:sync.db');
//トランザクション開始
$pdo->beginTransaction();
foreach ($data as $table => $rows) { //テーブルの数だけループ
	foreach ($rows as $row) { //更新行をUPSERTする
		upsert($pdo, $table, $row, 'seq');
	}
}
$pdo->commit();

/************************************************
 * UPSERT（あれば更新なければ追加）
 ************************************************/
function upsert($pdo, $table, $row, $key_column)
{
	//既存データか確認
	$st = $pdo->prepare('SELECT * FROM '.$table.' WHERE '.$key_column.'=?');
	$st->execute(array($row[$key_column]));
	if ($st->fetch()) {
		//UPDATE処理
		$query = 'UPDATE '.$table.' SET ';
		foreach ($row as $column => $value) {
			if ($column != $key_column) {
				$query .= $column.'=:'.$column.',';
			}
		}
		$query = substr($query, 0, strlen($query) - 1);
		$query .= ' WHERE '.$key_column.'=:'.$key_column;
		$st = $pdo->prepare($query);
	} else {
		//INSERT処理
		$keys = array_keys($row);
		$columns = implode(',', $keys);
		$values = ':'.implode(',:', $keys);
		$st = $pdo->prepare('INSERT INTO '.$table.' ('.$columns.') VALUES ('.$values.')');
	}
	$st->execute($row);
}

受信側の解説

毎行prepareするのは無駄だとか、キー項目が1個限定だとか、ツッコミどころはたくさんあるのですが、コードの読みやすさを優先してみました。

やっていることは単純で、

受信したBASE64をデコードしてGZIPデータに戻す
GZIPデータを展開してJSONテキストに戻す
JSONテキストを連想配列に戻す
連想配列から更新対象のテーブル名とレコードを抽出して更新

という感じです。

ぼくが運用するサイトでは仕組み上insertだけで済むので、このコードとはだいぶ違うんですけど、まぁ一般的にはUPSERT（あれば更新なければ追加）のほうが汎用性高いよなぁーということで、今回書き換えました。前述どおり、ここのコード類は記事に直書きしていて脳内コンパイルしているだけなので頭から信じてはいけない！でもたぶんやりたいことは伝わるでしょ。

削除処理？　実運用においてリアルタイムにレコードの削除なんて滅多にしないでしょ（鼻ほじ）というスタンスです。

普通はdelete_date DATETIME;みたいな項目作って、削除日時をレコードにセットして、削除したことにしますよね。それなら更新処理でローカル－リモート同期するだけでOKじゃん、という考え方です。

トランザクションレプリケーションについて

レプリケーションなんて大風呂敷広げたわりにはショボいコードでがっかりしたでしょうか。

本来、SQL Server等の商用データベースのレプリケーション処理では更新系のINSERT文やUPDATE文をリモート側に送信して実行させるような仕組みが主流です。いわゆるトランザクションレプリケーション。

これをはじめて知ったときは、あぁーかしこいなぁー、大量更新した場合はSQL文のほうがコンパクトだもんなぁーなどと感心したものです。

例えば100万行ある商品データのうち、食品カテゴリの商品だけ消費税率を8％にセットする例を考えてみましょうか。

このとき、100万個の商品のうち、50万個が食品だったとしても、更新するSQL文はたったの1行です。

UPDATE products SET tax_ratio=8 WHERE category='food';

これをリモート側に送ってあげれば1行で50万行分の更新ができちゃう。当たり前ですね。

ところが、先述のぼくのコード例では更新された50万行分送ることになります。無駄だなぁ～。

な・の・で・す・が

実運用においてはこんなのケースバイケースなんですよ。

同期の頻度がリアルタイムではなく、1日に1回とか1時間に1回の場合、その間に何度も同じレコードが更新されると、その回数分INSERT文やUPDATE文が生成されます。

でも、行データをコピーする方式なら、最後に更新されたレコードだけ送信されるのでデータ量が少なくて済む。

ですから、DBの同期処理において更新系のSQL文を送るのか、レコード自体を送るのか、そこはシステムの性格次第で検討すると良いでしょう。

尚、ぼくの場合はちょっと後ろ向きの理由でして、既存の更新系のコードを書き換えたくないのと、メンテナンス性を重視したからです。

…だって、何らかの理由で同期処理が正常に動かなかったとき、SQL文のキューを見るより、upload_dateがどうなっているのか見るだけのほうが楽でしょ？ｗ

もしトランザクションレプリケーションみたいなことを自前で実装したいなら、ローカルで実行している$pdo->queryや$pdo->prepareの部分をすべて自前の関数でラップして、INSERTかUPDATEだった場合、キューテーブルに保存する、という処理が必要になります。

でも逆にいえばそれだけすれば、あとはキューテーブルから$data連想配列を作って、POSTで送信＆受信側で連想配列内のSQL文を実行、というのも簡単でしょう。

まとめ

前回の「データベースの同期処理（レプリケーション）に関する昔話」では昔話だけで5000文字近くになってしまったので、今度こそシンプルに！と思っていたら、なんと9000文字超えちゃった…。どうしてこうなった。

概念やコンセプトの解説とコードは別にしたほうが良かったのかなぁ。でも、うちのブログって検索エンジンからのアクセスが9割を超えているので、だいたい1ページ見て帰っていくんですよね。元々実験用の雑記ブログだから、プログラミング専門でもないし。

ですから、必要な情報はできるだけページ内にまとめておきたい、ということで毎回長くなってしまいます。

もう少し短くて読みやすい記事を目指さないとなぁー。

では、そんな感じで、拙い知識とコードなので間違っている点も多々あると思いますが、この記事がどこかの技術者のお役に立てば幸いです。

PHPでDBをなんちゃってレプリケーションする例

ローカルとリモートでDBを同期したい理由

例えばAmazon API

例えばアクセス解析

SQLiteならDBファイルのアップロードだけでも同期になる

DBやテーブル全体のコピーには限界がある

更新した行だけ抽出してリモートへ送る例

テーブル構造

送信側（ローカル）のPHP例

送信側の解説

受信側（リモート）のPHP例

受信側の解説

トランザクションレプリケーションについて

まとめ

関連記事

PHPでCookieを保持しつつWebスクレイピングする例

INSERT文を一括処理して5万件のデータ追加を12倍高速化する

PHPのjson_encodeで空白が返ってきてしまう場合のエラー確認方法

Amazon Product Advertising APIが簡単に使えるようになっていて驚いた

PHPのcurlやfile_get_contentsでリダイレクトに対応する方法

PHPでWhois情報を取得する一番簡単な方法（PEARライブラリ不要）

コメント

新しいコメントを投稿する