Redmine Database Migration

Often times it happens that we need to migrate from one Redmine installation to another Redmine. Specially when upgrading to latest version. But we want to keep our database intact and want to migrate all the data to new setup. There are lot of guides on internet about redmine database migration, this blog post talks about traditional datbase migration along with case where projects in redmine instance needs to be merged with projects running in production environment.

Step 1: BACKUP the existing database

First of all take a backup of existing database.Backup is necessary in both cases .Whether we are installing new redmine on same system or if it is on new system . Use following command to create a backup in form of .gz file .

/usr/bin/mysqldump   --no-tablespaces   -u   mysql_user   -p'password'  database | gzip > /var/data/redmine/backup/redmine_date +%Y-%m-%d.gz

you can change username , password ,database name , and location where backup is created .

Note : You can use scp command to copygenerated backup file from one server to another . Follow below syntax.

scp username@ip:filepath/mysqlbackup.sql    path_to_new_location

scp local_path/mysqlbackup.sql  username@ip:filepath/

First is used when want to import database , second one is used when want to send database to new server.

step 2: Setup of Migration Plugin

Redmine Merge plugin can be found Here.
You can follow installation steps from github repository README file .

Step 3 : Executing Migration

Before executing migration make sure you have both databases on same system and you have added source database entry in database.yml file . Then cd to root of redmine and run following command to get details of database migration.

rake redmine:data_report RAILS_ENV=production

Output should look like this .

User: 109
CustomField: 93
Tracker: 40
IssueStatus: 48
Role: 22
Project: 35
Version: 47
News: 1
IssueCategory: 56
Enumeration: 11
Issue: 1102
IssueRelation: 217
Journal: 1455
JournalDetail: 2288
TimeEntry: 47
Document: 4
Wiki: 25
WikiPage: 0
WikiContent: 0
Attachment: 25

Run this commands to start migrating .

rake redmine:merge_redmine RAILS_ENV=production

Note : If you have stopped using old redmine and if the new redmine DB is completely empty then migration will have no errors at all. But if you have used new and old redmine and then want to migrate , it becomes a little tricky , because tables from both databases will have entries with same id . Which is not allowed as table ID is a primary key . So we have to manually fix in that case .

Step 4 : Manually fixing DB issues ( optional )

This step is only necessary if your migration did not complete successfully . You can manually open mysql console as follows .

1- Note down the entry which has the same ID .

2-remove entry with that ID from source table and add it to bottom to table with new ID .

OR we can use REPLACE INTO and INSERT INTO to merge tables from both databases one by one.

mysql> REPLACE INTO redmine.issues SELECT * FROM redmine_mig.issues;
ERROR 1136 (21S01): Column count doesn't match value count at row 1


mysql> USE redmine_mig;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> ALTER TABLE issues ADD sprint_id int;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE issues ADD position int;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> REPLACE INTO redmine.issues SELECT * FROM redmine_mig.issues;
Query OK, 1431 rows affected (0.09 sec)
Records: 1129  Duplicates: 302  Warnings: 0

mysql> DELETE FROM redmine_mig.trackers  WHERE id=31;
Query OK, 1 row affected (0.16 sec)

mysql> INSERT INTO `trackers` (`id`, `name`, `description`,`position`, `fields_bits`, `default_status_id`) VALUES (46, 'Star of Ethosh-May 2021', NULL ,29, 255, 1)
    -> ;
Query OK, 1 row affected (0.11 sec)

mysql> REPLACE INTO redmine.news SELECT * FROM redmine_mig.news;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> REPLACE INTO redmine.journal_details SELECT * FROM redmine_mig.journal_details;
Query OK, 2695 rows affected (0.04 sec)
Records: 2314  Duplicates: 381  Warnings: 0

mysql> REPLACE INTO redmine.documents SELECT * FROM redmine_mig.documents;
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> REPLACE INTO redmine.attachments SELECT * FROM redmine_mig.attachments;
Query OK, 32 rows affected (0.01 sec)
Records: 26  Duplicates: 6  Warnings: 0

Your old Database is now merged to new database. Please login to new rdemine instance to confirm if old data is there in projects along with the new data.

Tagged database plugins