PHPで、MySQLデータベーステーブルのバックアップをSQLiteに取得するスクリプトを書いてみました

---

今回、とあるサイトのサーバー移転に伴ってMySQLデータベースを引越しさせる必要があった。
膨大なデータのうち、全てのテーブル、すべてのレコードを丸ごと引っ越す必要があるのかないのか、未だハッキリしない。


MySQLデータベースのバックアップをとる方法は色々ある。
CREATE文やINSERT文などのSQLを取得したり、各種ツールを使ってJSON配列を取得したり。


丸ごと引っ越して、不要なデータを削げばいい？
うーん、色々考えて、一番使い勝手のいい方法はSQLiteのデータベースファイルにしてしまった方がいい様な気がしてきたので、MySQLをテーブル毎にSQLiteのファイルとして保存するスクリプトを書いてみた。

MySQL→SQLiteのバックアップスクリプトの仕様・要件

• テーブル毎にSQLiteファイルを保存
• テーブル毎にMySQLのCREATE TABLE文を保存
• ビューはCREATE文のみ保存
• SQLiteではプライマリーキー以外のインデックスは持たない
• 指定時間経過で中断、次回再開を可能に

※ テーブルのプライマリーキーがAUTOINCREMENTに準じたカラムでないと、中断・再開がうまくいかない（PKEYでソートして、1000件ずつ処理するため）。

MySQL→SQLiteの処理後のイメージ

処理後、次の様なファイル・フォルダが生成される。

• dbbackup.php は当該スクリプトファイル
• table1,2...はテーブルの名前
• tablename.mysql.sqlはMySQLのCREATE TABLE文SQL
• tablename.sqlite.sqlはSQLiteのCREATE TABLE文SQL（不要？）
• tablename.sqlite.dbはSQLiteのデータベースファイル

※ table1,2...、view1,2...はテーブル・VIEWの名前

/dbbackup.php

/dbbackup/table1/table1.mysql.sql
/dbbackup/table1/table1.sqlite.sql
/dbbackup/table1/table1.sqlite.db

/dbbackup/table2/table2.mysql.sql
/dbbackup/table2/table2.sqlite.sql
/dbbackup/table2/table2.sqlite.db
…
/dbbackup/view1.mysql.sql
/dbbackup/view2.mysql.sql

MySQL→SQLite 準備：タイムアップ設定

スクリプトのファイル名は、dbbackup.phpとして紹介していく。

MySQLが自前のサーバーでなく、レンタルサーバーなどを使っている場合は、いつまでたっても処理が終わらない…といったスクリプトを実行するべきでない。
また、処理にかかる時間も制限されている場合がほとんどだ。

そこで、小刻みにバックアップをとっていく。
例えば、この例では15秒で処理を抜けるようにする。サーバーにアップロードした後、cronでまわしていく作戦をとる。

<?php
// MySQL→SQLite dbbackup.php

echo(date("Y-m-d H:i:s | ")); //処理開始時間出力


//中断するタイムスタンプ設定
$timeout = time() + 15; //15秒でタイムアウトする場合
// $timeout = false; //無制限の場合はfalse
$timeout_flag = 0;


// １秒余分にもらう
if($timeout){$timeout--;}

※ 時間制限で処理を直ちに中断させるわけではないので、中断時間を１秒減らしている。

また、$timeoutを0又はfalseに設定した場合は、処理を中断しない。
データ量にもよるが、自前のサーバーであればfalseの設定でいいかもしれない。

MySQL→SQLite 準備：MySQLのPDO設定

バックアップ対象のMySQLデータベースへアクセスするためのPDOを作成する。

MySQLサーバーのホスト名、データベース名、文字コード、ユーザーID・パスワードを設定する。

// MySQLのPDO作成 --------------------
$host    = "localhost"; //MySQLのホスト名
$dbname  = "dbname"; //MySQLのデータベース名
$charset = "UTF8"; //文字コード
$user    = "username"; //MySQLのユーザー名
$pass    = "password"; //MySQLのパスワード
$MYSQL   = new PDO("mysql:host={$host};dbname={$dbname};charset={$charset};",$user,$pass);

MySQL→SQLite 準備：対象テーブル・作業フォルダの設定

バックアップを取得する対象のテーブルを$tablesに取得する。
ここでは「SHOW tables」で、すべてのビューとテーブルを対象にしている。

// 処理対象テーブル名の一覧配列 -----------------------
$tables = array();
$rs = $MYSQL->query("SHOW tables");
foreach($rs as $x){
	$clms = $MYSQL->query("SHOW COLUMNS FROM {$x[0]}")->fetchAll(PDO::FETCH_ASSOC);
	$tables[] = array("name"=>$x[0], "clms"=>$clms);
}

バックアップを保存するディレクトリを指定する。
なお、自分の場合はブラウザからもサーバーのターミナルからも操作したりするので、当該スクリプト内で使用・作成するディレクトリは、全てどのユーザーでも読み書き可能になるように、パーミッションを「707」に設定している。

// 作業用ディレクトリ。無ければ作成 --------------------
$base_dir = dirname(__FILE__)."/dbbackup";
if(!is_dir($base_dir)){
	mkdir($tbl_dir,0707);
}

MySQL→SQLite CREATE文のSQLを書き出し

ここから、テーブル毎の処理ループを開始する。

まず、「SHOW CREATE TABLE `tablename`」として、テーブル・ビューのCREATE文を取得する。

テーブルでない場合はSQL文のみ「ViewName.mysql.sql」という名前でファイルに書きだして、「continue」として次のテーブルの処理に移る。

テーブルの場合はテーブル名のディレクトリを作成し、「TableName.mysql.sql」というファイル名でCREATE文を書きだす。

// テーブル毎に処理 =======================================================================
foreach($tables as $tbl){
	$tbl_name = $tbl["name"];
	$mysql_res = $MYSQL->query("SHOW CREATE TABLE {$tbl_name}")->fetch();
	$p_key = false;
	
	// VIEWは作成用SQLのみメモ。
	if(!preg_match('/^\s*CREATE TABLE /i',$mysql_res[1])){
		file_put_contents("{$base_dir}/{$tbl_name}.view.sql",$mysql_res[1]);
		continue;
	}
	
	// テーブル用ディレクトリチェック。無ければ作成
	$tbl_dir = "{$base_dir}/{$tbl_name}";
	if(!is_dir($tbl_dir)){
		mkdir($tbl_dir,0707);
	}
	
	// MySQLテーブル作成用sqlファイル。無ければ作成
	$tbl_mysql = "{$tbl_dir}/{$tbl_name}.mysql.sql";
	if(!is_file($tbl_mysql)){
		file_put_contents($tbl_mysql,$mysql_res[1]);
	}

MySQL→SQLite SQLiteデータベース（PDO）の作成

SQLiteのデータベース作成用のSQLを取得する。
なお、ここでは今後復元処理などにかけたり作業がしやすいように、作業用フラグカラムを「sagyou_flag」という名前で追加している。

※ MySQLテーブルのカラム情報を調べて、Typeに「int」を含む場合はINTに、それ以外をTEXTとしてSQLITEテーブルを作成する。また、プライマリーキー以外のインデックスはSQLiteテーブルでは定義しない。

※ MySQLのテーブル情報を取得した際に、各カラムの情報一覧を $clm_type に、プライマリーキーのカラム名を $p_key に控えておく。

	// SQLITEテーブル作成用sqlファイル。無ければ作成
	$tbl_sqlite = "{$tbl_dir}/{$tbl_name}.sqlite.sql";
	$clm_type = array();
	$sqlite_create = "CREATE TABLE {$tbl_name} (";
	foreach($tbl["clms"] as $x){
		$sqlite_create .= "{$x['Field']} ";
		if(preg_match('/int/i',$x["Type"])){
			$sqlite_create .= "INT ";
			$clm_type[$x["Field"]] = "int"; // ついでにカラム名をキーにしてデータ型を控えておく
		}else{
			$sqlite_create .= "TEXT ";
			$clm_type[$x["Field"]] = "text"; // ついでにカラム名をキーにしてデータ型を控えておく
		}
		if(strtoupper($x["Key"]) == "PRI"){
			$sqlite_create .= "PRIMARY KEY ";
			// ついでにプライマリーキーを控えておく
			$p_key = $x["Field"];
		}
		$sqlite_create .= ",";
	}
	// 作業フラグカラムを追加
	$sqlite_create .= "sagyou_flag INT DEFAULT 0)";
	if(!is_file($tbl_sqlite)){
		file_put_contents($tbl_sqlite,$sqlite_create);
	}

SQLiteのデータベースファイル名を「TableName.sqlite.db」と確定して、SQLiteのPDOを作成する。

データベースファイルが無い場合は、0からのバックアップとして先に作成したCREATE文でテーブルを作成し、さらに作業メモに使えるようにmemoテーブルも作成する。

既にデータベースファイルがある場合は、前回作業が中断されたものとしてSQLiteのPDOのみを作成する。

	//SQLiteのDBファイル名確定
	$db_name = "{$tbl_dir}/{$tbl_name}.sqlite.db";
	
	// SQLiteのPDOを作成
	if(is_file($db_name)){
		$SQLITE = new PDO('sqlite:'.$db_name);
		$SQLITE->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
	}else{
		$SQLITE = new PDO('sqlite:'.$db_name);
		$SQLITE->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
		chmod($db_name,0707);
		$SQLITE->exec($sqlite_create);
		// 作業メモ用テーブル作成
		$sql = "CREATE TABLE memo (key TEXT PRIMARY KEY, val TEXT)";
		$SQLITE->exec($sql);
	}

※ 基本的に管理者の作業になるはずなので、$SQLITE->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING); としてエラーが出力されるようにしておく。

MySQL→SQLite 作業対象レコード数取得

MySQLテーブルのレコード総数と、SQLiteテーブルのレコード総数を取得する。

	$mysql_total = 0;
	// MySQLテーブルのレコード総数取得
	$sql = "SELECT COUNT({$p_key}) FROM {$tbl_name}";
	$mysql_res = $MYSQL->query($sql)->fetch();
	$mysql_total = $mysql_res[0];

	
	// 処理を終えたSQLITEレコード数を取得
	$last_count  = 0;
	$sql = "SELECT COUNT({$p_key}) FROM {$tbl_name}";
	$sqlite_res = $SQLITE->query($sql);
	if($sqlite_res){
		$sqlite_res = $sqlite_res->fetch();
		$last_count = $sqlite_res[0];
	}
	
	// テーブルの作業開始ログ
	echo("{$tbl_name}({$last_count}/{$mysql_total}):".date("H:i:s")." ");

SQLiteテーブルのレコード総数が、MySQLテーブルのレコード総数に満たない場合は、バックアップ処理を開始する。

MySQL→SQLite バックアップ開始

MySQLテーブルのレコードをSQLiteテーブルにコピー（INSERT）していく。

この時、「ORDER BY primary_key 」LIMIT [SQLiteのレコード総数]として、MySQLテーブルからレコードを抽出するため、プライマリーキーがAUTOINCREMENTに準ずる使用になっていない場合で、前回作業が中断されている場合は、前回処理からの差分をうまく取得する事ができないので注意する。

また、タイムアウトを設定した場合はタイムスタンプをチェックして、超過した場合はループを抜ける。

	//作業件数メモ
	$tmp_count = 0;
	if($last_count < $mysql_total){
		//1000件ずつレコード抜出し
		$add_count = 1000;
		for($i = $last_count; $i < $mysql_total; $i += $add_count){
			$sql = "SELECT * FROM {$tbl_name} ORDER BY {$p_key} LIMIT {$i},{$add_count}";
			$mysql_res = $MYSQL->query($sql);
			$mysql_res = $mysql_res->fetchAll(PDO::FETCH_ASSOC);
			if(is_array($mysql_res) && !empty($mysql_res)){
				while(($x = array_shift($mysql_res)) !== NULL){
					foreach($x as $k => $v){
						$int_flag = false;
						//カラムのデータ型チェック
						if(is_null($v)){
							$x[$k] = 'NULL';
						}elseif(!isset($clm_type[$k]) || $clm_type[$k] != "int"){
							$x[$k] = $SQLITE->quote($v);
						}
					}
					// SQLiteテーブルにレコード追加
					$sql  = "INSERT INTO {$tbl_name}(" . implode(",", array_keys($x)) . ") VALUES(" . implode(",", $x) . ")";
					$SQLITE->beginTransaction();
					$SQLITE->exec($sql);
					$SQLITE->commit();
					$tmp_count++;
					if(($tmp_count % 1000) == 0){
						echo($tmp_count.",");
					}
					//時間切れの場合、処理を抜ける
					if($timeout && $timeout <= time()){
						$timeout_flag++;
						break;
					}
				}
			}
			//時間切れの場合、処理を抜ける
			if($timeout && $timeout <= time()){
				$timeout_flag++;
				break;
			}
		}
	}

テーブル毎に処理が完了した場合は、SQLiteテーブルの総レコード数を再取得して、テーブル名・作業完了時刻とともにログを出力する。

	if($tmp_count){
		echo($tmp_count);
		// SQLiteテーブルのレコード数
		$sql = "SELECT COUNT({$p_key}) FROM {$tbl_name}";
		$sqlite_res = $SQLITE->query($sql);
		if($sqlite_res){
			$sqlite_res = $sqlite_res->fetch();
			$last_count = $sqlite_res[0];
		}
	}
	echo(" | {$tbl_name}({$last_count}/{$mysql_total}):".date("H:i:s")." | ");
	// 時間切れの場合は処理を抜ける
	if($timeout && $timeout <= time()){
		$timeout_flag++;
		break;
	}
}

タイムアウトした場合は timeout、最後まで処理を終えた場合は finishのログを出力する。

if($timeout_flag){
	echo("timeout:".date("Y-m-d H:i:s"));
}else{
	echo("finish:".date("Y-m-d H:i:s"));
}

exit();

MySQL→SQLiteバックアップ ： サンプルコード

この流れを続けて書くと、次のようになる。

<?php
//MySQL → SQLite バックアップ

echo(date("Y-m-d H:i:s | ")); //処理開始時間出力


//中断するタイムスタンプ設定
$timeout = time() + 15; //15秒でタイムアウトする場合
//$timeout = false; //無制限の場合はfalse
$timeout_flag = 0;

// MySQLのPDO作成 --------------------
$host    = "localhost"; //MySQLのホスト名
$dbname  = "dbname"; //MySQLのデータベース名
$charset = "UTF8"; //文字コード
$user    = "username"; //MySQLのユーザー名
$pass    = "password"; //MySQLのパスワード
$MYSQL   = new PDO("mysql:host={$host};dbname={$dbname};charset={$charset};",$user,$pass);


// 処理対象テーブル名の一覧配列 -----------------------
$tables = array();
$rs = $MYSQL->query("SHOW tables");
foreach($rs as $x){
	$clms = $MYSQL->query("SHOW COLUMNS FROM {$x[0]}")->fetchAll(PDO::FETCH_ASSOC);
	$tables[] = array("name"=>$x[0], "clms"=>$clms);
}


// 作業用ディレクトリ。無ければ作成 --------------------
$base_dir = dirname(__FILE__)."/dbbackup";
if(!is_dir($base_dir)){
	mkdir($tbl_dir,0707);
}
//print_r($tables);
//exit;


// １秒余分にもらう
if($timeout){$timeout--;}

// テーブル毎に処理 =======================================================================
foreach($tables as $tbl){
	$tbl_name = $tbl["name"];
	$mysql_res = $MYSQL->query("SHOW CREATE TABLE {$tbl_name}")->fetch();
	$p_key = false;
	
	// VIEWは作成用SQLのみメモ。
	if(!preg_match('/^\s*CREATE TABLE /i',$mysql_res[1])){
		file_put_contents("{$base_dir}/{$tbl_name}.view.sql",$mysql_res[1]);
		continue;
	}
	
	// テーブル用ディレクトリチェック。無ければ作成
	$tbl_dir = "{$base_dir}/{$tbl_name}";
	if(!is_dir($tbl_dir)){
		mkdir($tbl_dir,0707);
	}
	
	// MySQLテーブル作成用sqlファイル。無ければ作成
	$tbl_mysql = "{$tbl_dir}/{$tbl_name}.mysql.sql";
	if(!is_file($tbl_mysql)){
		file_put_contents($tbl_mysql,$mysql_res[1]);
	}
	
	// SQLITEテーブル作成用sqlファイル。無ければ作成
	$tbl_sqlite = "{$tbl_dir}/{$tbl_name}.sqlite.sql";
	$clm_type = array();
	$sqlite_create = "CREATE TABLE {$tbl_name} (";
	foreach($tbl["clms"] as $x){
		$sqlite_create .= "{$x['Field']} ";
		if(preg_match('/int/i',$x["Type"])){
			$sqlite_create .= "INT ";
			$clm_type[$x["Field"]] = "int"; // ついでにカラム名をキーにしてデータ型を控えておく
		}else{
			$sqlite_create .= "TEXT ";
			$clm_type[$x["Field"]] = "text"; // ついでにカラム名をキーにしてデータ型を控えておく
		}
		if(strtoupper($x["Key"]) == "PRI"){
			$sqlite_create .= "PRIMARY KEY ";
			// ついでにプライマリーキーを控えておく
			$p_key = $x["Field"];
		}
		$sqlite_create .= ",";
	}
	// 作業フラグカラムを追加
	$sqlite_create .= "sagyou_flag INT DEFAULT 0)";
	if(!is_file($tbl_sqlite)){
		file_put_contents($tbl_sqlite,$sqlite_create);
	}
	
	
	//SQLiteのDBファイル名確定
	$db_name = "{$tbl_dir}/{$tbl_name}.sqlite.db";
	
	// SQLiteのPDOを作成
	if(is_file($db_name)){
		$SQLITE = new PDO('sqlite:'.$db_name);
		$SQLITE->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
	}else{
		$SQLITE = new PDO('sqlite:'.$db_name);
		$SQLITE->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
		chmod($db_name,0707);
		$SQLITE->exec($sqlite_create);
		// 作業メモ用テーブル作成
		$sql = "CREATE TABLE memo (key TEXT PRIMARY KEY, val TEXT)";
		$SQLITE->exec($sql);
	}

	$mysql_total = 0;
	// MySQLテーブルのレコード総数取得
	$sql = "SELECT COUNT({$p_key}) FROM {$tbl_name}";
	$mysql_res = $MYSQL->query($sql)->fetch();
	$mysql_total = $mysql_res[0];

	
	// 処理を終えたSQLITEレコード数を取得
	$last_count  = 0;
	$sql = "SELECT COUNT({$p_key}) FROM {$tbl_name}";
	$sqlite_res = $SQLITE->query($sql);
	if($sqlite_res){
		$sqlite_res = $sqlite_res->fetch();
		$last_count = $sqlite_res[0];
	}
	
	echo("{$tbl_name}({$last_count}/{$mysql_total}):".date("H:i:s")." ");
//exit;
	
	//作業件数メモ
	$tmp_count = 0;
	if($last_count < $mysql_total){
		//1000件ずつレコード抜出し
		$add_count = 1000;
		for($i = $last_count; $i < $mysql_total; $i += $add_count){
			$sql = "SELECT * FROM {$tbl_name} ORDER BY {$p_key} LIMIT {$i},{$add_count}";
			$mysql_res = $MYSQL->query($sql);
			$mysql_res = $mysql_res->fetchAll(PDO::FETCH_ASSOC);
			if(is_array($mysql_res) && !empty($mysql_res)){
				while(($x = array_shift($mysql_res)) !== NULL){
					foreach($x as $k => $v){
						$int_flag = false;
						//カラムのデータ型チェック
						if(is_null($v)){
							$x[$k] = 'NULL';
						}elseif(!isset($clm_type[$k]) || $clm_type[$k] != "int"){
							$x[$k] = $SQLITE->quote($v);
						}
					}
					// SQLiteテーブルにレコード追加
					$sql  = "INSERT INTO {$tbl_name}(" . implode(",", array_keys($x)) . ") VALUES(" . implode(",", $x) . ")";
					$SQLITE->beginTransaction();
					$SQLITE->exec($sql);
					$SQLITE->commit();
					$tmp_count++;
					if(($tmp_count % 1000) == 0){
						echo($tmp_count.",");
					}
					//時間切れの場合、処理を抜ける
					if($timeout && $timeout <= time()){
						$timeout_flag++;
						break;
					}
				}
			}
			//時間切れの場合、処理を抜ける
			if($timeout && $timeout <= time()){
				$timeout_flag++;
				break;
			}
		}
	}
	if($tmp_count){
		echo($tmp_count);
		// SQLiteテーブルのレコード数
		$sql = "SELECT COUNT({$p_key}) FROM {$tbl_name}";
		$sqlite_res = $SQLITE->query($sql);
		if($sqlite_res){
			$sqlite_res = $sqlite_res->fetch();
			$last_count = $sqlite_res[0];
		}
	}
	echo(" | {$tbl_name}({$last_count}/{$mysql_total}):".date("H:i:s")." | ");
	// 時間切れの場合は処理を抜ける
	if($timeout && $timeout <= time()){
		$timeout_flag++;
		break;
	}
//exit;
}

if($timeout_flag){
	echo("timeout:".date("Y-m-d H:i:s"));
}else{
	echo("finish:".date("Y-m-d H:i:s"));
}

exit();

また日をあらためて、今回バックアップを取得したSQLiteテーブルをMySQLに復元するスクリプトも紹介する予定。

---

---