SQLite のバックアップ方法色々(DB丸ごと、dumpしてSQLに、データのみ抜き出し)

SQLiteのバックアップ方法が色々あるのでそのまとめ記事。単純な方法からコマンドラインを活用したものまで。
目次
SQLiteの基本
SQLiteにはユーザーの概念が無く、DB自体も単一ファイルです。
というわけでDBに接続するのも簡単で、ファイル名を指定するだけです。
% sqlite3 db-filename
これで接続できるので後は直接SQLを叩けます。RubyとかPHPといったプログラムから接続する場合もそれぞれの書式に則る必要があるものの、基本的にはファイル名を指定するだけです。
単純簡単なバックアップとリストア方法
SQLiteはバックアップも簡単です。先ほど書いた通り「DB自体も単一ファイル」なので、バックアップは「そのDBファイルをコピーするだけ」です。非常に簡単ですね。
他のDBだとバックアップやリストアはそれなりに難しかったりするので嬉しい所です。例えば毎日cronでDBファイルをコピーするだけで日次バックアップが取れます。また前日のファイルをコピーするだけで最悪前日まではリストアできます(前日に既に壊れていなければ)。
SQLiteの.backup/.resotreコマンドでのバックアップとリストア
ファイルコピー以外にコマンドでのバックアップも可能です。さっきの要領でSQLiteに接続し、sqlite> のプロンプトで 「.backup」 コマンドを実行します。
sqlite> .backup backup-filename
リストアは逆に「.restore」コマンドを使うだけです。こちらはバックアップとリストアの一連の流れの例です。
sqlite> .backup test-backup sqlite> .quit % sqlite3 test SQLite version 3.8.10.2 2015-05-20 18:17:19 Enter <span style="color: #8AE234;">".help"</span> for usage hints. sqlite> .tables sqlite> sqlite> .restore test-backup tbl_test sqlite> drop table tbl_test; sqlite> .tables sqlite> sqlite> .restore test-backup sqlite> .tables tbl_test sqlite>
DBの内容をsql文としてdumpとリストア
ここまでのやり方はバイナリファイルを扱うものでした。これ以外の方法としてDBの内容をSQLにdumpするというバックアップ方法もあります。これにはSQLiteの「.dump」コマンドを利用します。
SQL文でダンプすればMySQLなど他のDBにインポートしたりもできます。
コマンドラインから実行することも可能なのでシェルスクリプト化してcronで自動処理させることも可能です。
% sqlite3 testdb .dump > testdb-bak.sql
リストアする場合は「.read」コマンドを使います。
sqlite3> .read test-bak.sql
コマンドラインからも実行可能です。この場合は、「.read とファイル名をクオートで囲う」必要があります。
% sqlite3 testrest2 '.read test-bak.sql'
データのみバックアップ
ここまではDBをバックアップする方法でしたが、データのみを抜き出すことも可能です。使うコマンドは、「.output」、「.mode」、「select (SQL文)」です。
こういうのは実際に流れをみてもらった方がわかりやすいのでサンプルとして試してみます。
% sqlite3 testdb SQLite version 3.8.10.2 2015-05-20 18:17:19 Enter <span style="color: #8AE234;">".help"</span> for usage hints. sqlite> .mode csv sqlite> .output test-output-data.csv sqlite> select * from tbl_test order by id; sqlite> .quit % cat test-output-data.csv 1,<span style="color: #8AE234;">"recipe A"</span>,1000 2,<span style="color: #8AE234;">"recipe B"</span>,2000 3,<span style="color: #8AE234;">"recipe C"</span>,1700 4,<span style="color: #8AE234;">"日本建築の本"</span>,1000 5,<span style="color: #8AE234;">"海外の工芸品"</span>,2000 6,<span style="color: #8AE234;">"論理的思考ほにゃらら"</span>,3000
DBに接続した後
- 「.mode」で出力形式をCSVに指定
- 「.output」でSQLの出力結果をファイルに
- 「select」で任意のSQLを実行
という流れです。
「おまけ」SQLite v2 から v3 へのDB変換
古いバージョンのSQLiteのDBをSQLite3形式に変換する方法です。Ver.2 から Ver.3に変換します。
% sqlite db-name .dump | sqlite3 new-db-name
これでOK!簡単です。