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; // 1秒余分にもらう if($timeout){$timeout--;}
※ 時間制限で処理を直ちに中断させるわけではないので、中断時間を1秒減らしている。
また、$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; // 1秒余分にもらう 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に復元するスクリプトも紹介する予定。
