LoginSignup
4

More than 5 years have passed since last update.

SQLiteからMySQLへのデータ移行

Last updated at Posted at 2017-05-24

[17/6/1] 改行コード(char(10)/char(13))を含むデータに対応できるよう修正

WebアプリのバックエンドをSQLiteからMySQLに移行するための備忘録です。

SQLiteでダンプしたものを何も考えずにMySQLに突っ込むとFOREIGN KEYなどの制約が抜けてしまう状況だったので、migrationはアプリ付属の初期化スクリプトを使い、データだけをSQLiteから移行するという方針です。

動作環境
sh: FreeBSD 11の/bin/sh
sqlite3: 3.18.0
MySQL: 5.6

データ抽出スクリプト

SQLiteのDBからMySQL投入用のSQLを生成するスクリプトです。こちらはstack overflowから拝借してきたものを少し修正しました。
元のスクリプトの

  • '\nINSERT INTO table'という文字列を含むデータがあると上手くいかない

という制約は残っていますが、

  • カラム名に特殊な文字があるとダメ

という制約の方は対策してあります。

sqlite2mysql.sh
#!/bin/sh

SQLITE=sqlite3

if  [ -z "$1" ] ; then
        echo usage: $0  sqlite3.db
        exit
fi

DB="$1"

TABLES=$("$SQLITE" "$DB" .tables)
#echo "-- $TABLES" 
echo 'BEGIN;'
echo 'SET FOREIGN_KEY_CHECKS = 0;'

for TABLE in $TABLES ; do
        echo 
        echo "-- $TABLE:";
        echo "TRUNCATE TABLE $TABLE;"
        COLS=$("$SQLITE" "$DB" "pragma table_info($TABLE)" |
          cut -d'|' -f2 )
        COLS_CS=$(echo $COLS | sed 's/ /","/g')
        COLS_CS2=$(echo $COLS | sed 's/ /`,`/g')

        echo -e ".mode insert\nselect \"$COLS_CS\" from $TABLE;\n" |
          "$SQLITE" "$DB" |
          sed "s/^INSERT INTO \"table\"/INSERT INTO $TABLE (\`$COLS_CS2\`)/" |
          sed "s/'||//g" | sed "s/||'//g" | sed 's/char(13)|\{0,2\}/\\r/g' | sed 's/char(10)|\{0,2\}/\\n/g'
done
echo 'SET FOREIGN_KEY_CHECKS = 1;'
echo 'COMMIT;';

データ抽出

例として、Roundcubeのデータの移行手順を記述します 1

データ抽出
% sh sqlite2mysql.sh roundcube.db > rc_data.sql

で、rc_data.sqlの内容は以下のようになります。

rc_data.sql
BEGIN;
SET FOREIGN_KEY_CHECKS = 0;
-- cache:
TRUNCATE TABLE cache;
-- contactgroupmembers:
TRUNCATE TABLE contactgroupmembers;
INSERT INTO contactgroupmembers (`contactgroup_id`,`contact_id`,`created`) VALUES(1,10,'2017-05-24 07:00:00');
 :
INSERT INTO identities (`identity_id`,`user_id`,`changed`,`del`,`standard`,`name`,`organization`,`email`,`reply-to`,`bcc`,`signature`,`html_signature`) VALUES(...);
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;

データ投入

出来上がったrc_data.sqlをMySQLに投入します。[DB名]のデータベースの初期化は済ませてあるものとします。

データ投入
% mysql -u [DBユーザ] -p [DB名] < rc_data.sql

途中でエラーが出たらロールバックするので、1行ずつコツコツと修正します。

各テーブルのINSERT直前にTRUNCATEしているので、やり直しもできます。

以上。


  1. これが目的だったんですが(^^; 

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
4