Redmine 資料庫備份、SQLite 轉換成 MySQL

Redmine 資料庫備份、轉換 (SQLite 轉成 MySQL), 要如何做?

環境: Debian、Ubuntu Linux # 使用內建的 apt-get install redmina

Redmine DB 備份 (SQLite Dump backup)

• sqlite3 /var/lib/dbconfig-common/sqlite3/redmine/instances/default/redmine_default .dump > ~/redmine-backup.sql

Redmine DB 轉換, 由 SQLite3 轉換到 MySQL 的步驟

下述只是將蒐集來的紀錄於此, 尚未測試過是否正常.

• SQLite DB 檔案位置: /var/lib/dbconfig-common/sqlite3/redmine/instances/default/redmine_default

下述步驟取自: Migrating from sqlite3 to mysql

1. 下載 sqlite3_mysql.zip, 並解壓縮
2. mysql -u user -p -e "create database redmine character set utf8;"
3. sqlite3 production.db .dump | sqlite3_mysql | mysql -u user -p redmine
4. 到此就完成囉~

註: sqlite3_mysql 做的事情如下:

1. Strip out PRAGMA lines
2. Strip out BEGIN TRANSACTION; lines
3. Strip out COMMIT; lines
4. Strip out DELETE FROM and INSERT INTO the sqlite_sequence table
5. Replace AUTOINCREMENT with AUTO_INCREMENT
6. Replace DEFAULT 't' and DEFAULT 'f' with DEFAULT '1' and DEFAULT '0'
7. Replace ,'t' and ,'f' with ,'1' and ,'0'
8. Replace " with ` except in string values (otherwise it replaces all quotes in your text)