【MySQL】バックアップ＆レストアツール作ってみた。

ちょっと前からブログのネタにしてたMySQLだけど、結局MySQL附属の「mysqldump」とかではうまくいかなかったんだよね。
で、しょうがないからMySQLのバックアップとレストアツールを作ってみたよ。

まぁ、大規模なデータベースのバックアップはちょっとムリかもしれないけど、数百MBくらいのテーブルなら何とかなるかもしれない。

●仕様
・最大１つのデータベースのみバックアップ・レストアできる。
・必ずしも元のデータに復元できないことに注意すること！！
※複数のデータベースをバックアップしたいときは、このプログラムを複数の場所に入れればOK。

●バックアップの処理内容
全てのテーブル名を取得し、データを全件SELECTしてbase64でエンコードし保存するだけ。
テーブル１つに対しファイル２つが出来る。「CREATE文が入るファイル」と「データが入るファイル」となる。
たとえば「hoge」というテーブルがあるのであれば、「hoge_create.dat」と「hoge_data.dat」というファイルが出来る。
各レコードを各１行に収めるために改行文字を「￥ｒ￥ｎ」などに変換していることに注意。

●レストア処理の内容
ファイル名から「CREATE文」か「データ」かを区別しCREATE文から処理を始める。フラグを設定すれば「DROP TABLE IF EXISTS文」も入れられる。
データはbase64デコードしINSERT文を作って入れるだけ。データ項目に「'(シングルクォート)」が入ってる場合は二重化して回避。

●使い方
▲ダウンロードする
http://www.tagajo.tv/backuptool_ver01.zip
↑からツールをダウンロードして、展開する。このときディレクトリ構造をいじらないこと。


▲設定を環境に合わせる
「ダウンロードする」で展開したファイルの中に「config.php」というファイルがあるので、その中身を変更する。
具体的には以下の部分のみを変更すればOK。

---引用開始---
 // バックアップするデータベースの設定
 define( 'DATABASE_HOST',  'xxxxxxxxxxxxx' );
 define( 'DATABASE_USER',  'xxxxxxx' );
 define( 'DATABASE_PASS',  'xxxxxxx' );
 define( 'DATABASE_NAME',  'xxxxxxx' );

 // レストアするデータベースの設定
 define( 'RESTORE_HOST',   'xxxxxxxxxxxxx' );
 define( 'RESTORE_USER',   'xxxxxxx' );
 define( 'RESTORE_PASS',   'xxxxxxx' );
 define( 'RESTORE_NAME',   'xxxxxxx' );

 ～中略～

 // MySQLのクライアント文字コード(お使いのデータ本体の文字コードへ変更してください)
 // SET NAMES *** に使用されます。
 define( 'DATA_CHARACTER_CODE',  'sjis' );
---引用終了---

※注意
「DATABASE_XXXX」はバックアップするデータベースの設定
「RESTORE_XXXX」はレストアするデータベースの設定
なのを間違わないこと。

▲バックアップするサーバへアップロードする
設定を環境に合わせたら、バックアップ対象のデータベースがあるサーバへアップロードする。
アップロードする場所はどこでもOKだけど、Webで公開されているディレクトリはやめること。
セキュリティなんぞこのプログラムは考慮してないので。
アップロードするときも、ディレクトリ構造をいじらないこと。
アップロード先の「dat」ディレクトリには「700」等の書き込みできるパーミッションを設定しておくこと。

▲実行方法
PHPで記述されているのでPHPのインタプリタで実行する。
以下、PHPが「/usr/local/bin/」の下にあるとする。

・バックアップ実行コマンド
$/usr/local/bin/php -f /アップロードしたディレクトリ/backup.php

・レストア実行コマンド
$/usr/local/bin/php -f /アップロードしたディレクトリ/restore.php

以上。

【MySQL】異なるサーバ間でのデータ移行方法について

Kagoyaのサーバで運営していたサイトを、さくらインターネットのレンタルサーバへ移動したときの作業メモです。
MySQLを使用してサイトを運営しているのですが、基本的にMySQL附属のダンプツールは文字化けなどの問題がたくさんあってあまり使えないですね。
さらにKagoyaサーバのmysqldumpは文字コードの指定を受け付けないようになってるみたいだし。

しょうがないので自分でダンプツールとレストアツールを作成し移行してみることにしました。

---
○サイトの仕様
・データベース数：1つ
・データベース容量：100MB程度
・レコード総数：26万件程度

○テーブルの移行方法
・テーブル自体の構造はphpMyAdminで構造だけ取り出し、移行する

○ダンプツールの仕様
・テーブル1つごとにダンプファイル1つで、テーブル名をファイル名とする
・ファイル内容は1行ごとに1レコード
・文字コードはデータに合わせる
・1行ごとに1レコードなので改行文字は「\r」や「\n」と表記し無理やり1行に収める

○レストアツールの仕様
・ダンプファイルから1行を読み出しINSERT文を作成しテーブルへINSERTする
・文字化けしないように、PHPの「mysql_escape_string関数」をかませる
・mysql_escape_stringを使用すると、ダンプツールで変換した「\n」が「\\n」と変換されてしまうので「\n」へ戻す
---

といった感じにしました。

---
○問題点と回避方法
・ダンプツールで大容量のテーブルをファイルに吐き出そうとするとメモリが足りない、とPHPに怒られる
→mysql_query関数を使用していたため、メモリに結果をバッファしようとして大量のメモリを使用していたことが分かり、メモリを極力使用しない「mysql_unbufferd_query関数」を使用することで回避。

・CSVでデータを吐き出すとデータ内のカンマなどで不整合がおきメンドイ
→「mysql_fetch_assoc関数」を使用し、「連想配列」で結果を返してもらうことにする。それを「serialize関数」で直列化して保存しておけば、読み出し側で「unserialize関数」で元に戻せる。
---

これでデータが全てダンプファイルに落とされたので、移動先のサーバへ持っていってレストアツールでレストアすれば作業完了。文字化けもしないはず。
あと、オマケとしてツールのソースコードを以下に掲載しておきますね。



○ダンプツールのソース
---
  function nl_2_char( &$item, $key )
  {
    $item = preg_replace( '/\r\n/i', '\r\n', $item );
    $item = preg_replace( '/\r/i', '\r', $item );
    $item = preg_replace( '/\n/i', '\n', $item );
  }
 
  define( 'DATABASE_HOST',    'ホスト名' );
  define( 'DATABASE_USER',    'ユーザ名' );
  define( 'DATABASE_PASS',    'パスワード' );
  define( 'DATABASE_NAME',    'データベース名' );
 
  $con = @mysql_connect( DATABASE_HOST, DATABASE_USER, DATABASE_PASS, false );
  mysql_select_db( DATABASE_NAME );
 
  $tables = array( テーブル名の配列 );
 
  foreach( $tables AS $tab    le ) {
    $file_name = dirname( __FILE__ ) . "/dat/" . $table . '.dat';
    @unlink( $file_name );
   
    $strSql  = "SELECT * FROM " . $table;
    $resource = mysql_unbuffered_query( $strSql, $con );
    if( !$resource ) {
      print( "ERROR!" );
      exit();
    }
    $table_data = array();
   
    print( $table . " START!\n " );
   
    while( $row = mysql_fetch_assoc( $resource ) ) {
      array_walk( $row, 'nl_2_char' );
      $buff = serialize( $row ) . "\n";
      file_put_contents( $file_name, $buff, FILE_APPEND );
    }
    unset( $resource );
   
    print( $table . " END!\n " );
  }
?>
---

○レストアツールのソース
---
  define( 'DATA_PATH',    dirname( __FILE__ ) . '/dat/' );
 
  define( 'DATABASE_HOST',    'ホスト名' );
  define( 'DATABASE_USER',    'ユーザ名' );
  define( 'DATABASE_PASS',    'パスワード' );
  define( 'DATABASE_NAME',    'データベース名' );

  // ファイルネーム取得
  $file_names = array();
  $d = dir( DATA_PATH );
  while ( false !== ($entry = $d->read()) ) {
    if( preg_match( '/[a-z0-9\_]+/', $entry ) ) {
      $file_names[] = $entry;
    }
  }
  $d->close();
 
  $con = @mysql_connect( DATABASE_HOST, DATABASE_USER, DATABASE_PASS, false );
  mysql_select_db( DATABASE_NAME );
  mysql_query( 'SET NAMES sjis' );    // データベースの文字コードにあわせて変更すること！
 
  foreach( $file_names AS $file_name ) {
    $table_name = basename( $file_name, '.dat' );
   
    // $rows = file( DATA_PATH . $file_name );
    $fp = fopen( DATA_PATH . $file_name, 'r' );
    if( !$fp ) {
      die( "DONT OPEN!\n" );
    }
   
    while( !feof( $fp ) ) {
      $row = fgets( $fp );
      if( trim( $row ) == "" ) {
        continue;
      }
      $fields = unserialize( trim( $row ) );
     
      $srcSql  = "INSERT INTO %s (%s) VALUES (%s)";
      $field_names = array();
      $field_values = array();
      foreach( $fields AS $field_name => $value ) {
        $field_names[]  = $field_name;
        $value = mysql_escape_string( $value );
        $value = str_replace( '\n', 'n', $value );
        $value = str_replace( '\r', 'r', $value );
        $field_values[]  = sprintf( ' %s ', $value );
      }
      $field_list = implode( ", ", $field_names );
      $value_list = sprintf( "'%s'", implode( "', '", $field_values ) );
      $strSql = sprintf( $srcSql, $table_name, $field_list, $value_list );
     
      $ret = @mysql_query( $strSql, $con );
      if( !$ret ) {
        print( $strSql . "\n" );
        print( mysql_error() );
        exit();
      }
    }
  }
?>
---